Thursday, September 19, 2013

[how to] Oracle Equivalent of PostgreSQL CLUSTER

[how to] Oracle Equivalent of PostgreSQL CLUSTER

Oracle Equivalent of PostgreSQL CLUSTER

Posted: 19 Sep 2013 04:27 PM PDT

PostgreSQL has the CLUSTER command to group rows physically on disk. By grouping on information where "neighboring" rows (for lack of a better term) are often accessed together, performance improves since fewer disk blocks need to be read in a given query. Does Oracle have anything similar? Would it even help performance on a large table that is almost never updated if there is such an option?

What does maintenanceMode -7 mean on a mongo primary?

Posted: 19 Sep 2013 03:06 PM PDT

I've recently promoted a new member to primary of a mongo replicaset. Things seem to be running smoothly for several days and am not seeing any application symptoms of any problems. However peeking at rs.status() today I noticed it's listing maintenanceMode -7. I didn't think this should be possible as it's the primary. I'm having trouble finding anything useful in the docs on what this means.

What does this mean?

    {          "_id" : 26,          "name" : "mongohost5:27032",          "health" : 1,          "state" : 1,          "stateStr" : "PRIMARY",          "uptime" : 106966,          "optime" : Timestamp(1379627877000, 77),          "optimeDate" : ISODate("2013-09-19T21:57:57Z"),          "maintenanceMode" : -7,          "self" : true      }

Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

Posted: 19 Sep 2013 07:55 PM PDT

I have a table of around 350m rows on a linked server in which I added an additional INT column to serve as a count(external_identification) of records as the result of a join on PACKAGE and DOC2. Since the tables are so large I'd like to process the update in batches, both so I can gauge progress and to avoid creating huge temp tables. Each column is indexed.

Would this be a good example where a CTE comes into play? Quite honestly they confuse me with the way they need to be written, it's hard to visualize...

The tables are structured as:

ServerA (utility SQL server)
Table: CLIP_IDs
Columns: Package_UUID nvarchar(255), MessageExtractState tinyint, [count] int (350m rows)

ServerB (main database server)
Table: PACKAGE
Columns: Package_UUID nvarchar(255), Package_id bigint (650m rows)
Table: DOC2
Columns: External_Identification nvarchar(255), Package_id bigint (2b rows)

Both SQL servers are linked both ways, if initiating the query from one is more efficient. I have a feeling issuing the query from ServerA will be, as it seems like the execution plan offers less remote queries.

I stopped the query below after 26 hours because I think I have a syntax logic error. Can someone explain what it is and offer any suggestions please?

Executed from ServerA:

DECLARE @rowsUpdated INT    SET @rowsUpdated = 1    WHILE (@rowsUpdated > 0)  BEGIN      UPDATE CLIP_IDs      SET [Count] = x.[count]      FROM (          SELECT TOP 50000 c.package_uuid              ,count(d.external_identification) AS [count]          FROM CLIP_IDs c          INNER JOIN ServerB.DATABASE.dbo.package p(NOLOCK) ON c.package_uuid = p.package_uuid          INNER JOIN ServerB.DATABASE.dbo.doc2 d(NOLOCK) ON p.package_id = d.package_id          WHERE c.messageextractstate = 1              AND c.[count] IS NULL          GROUP BY c.package_uuid          ) x        SET @rowsUpdated = @@rowcount        PRINT N'Finished set of rows: ' + convert(VARCHAR, getdate(), 120)  END

Index for ORDER BY on aggregate in MySQL

Posted: 19 Sep 2013 06:39 PM PDT

I'm working with the following query:

SELECT *, SUM(entries_count) AS entries_count FROM leads  WHERE promo_id IN (1, 2, 3)  GROUP BY user_id  ORDER BY entries_count DESC  LIMIT 25 OFFSET 100;

It's quite fast without the ORDER BY clause, but very slow with it. I have a few compound indexes, but it only seems to want to use the index on user_id and that's it. Here's the EXPLAIN output:

| 1 | SIMPLE | leads | index | index_leads_on_promo_id_and_user_id,index_leads_on_promo_id | index_leads_on_user_id | 4 | NULL | 219647 | Using where; Using temporary; Using filesort |

I find it a bit odd that it's using a key which isn't even listed under "possible keys". Anyways, is there any way to provide a better index to improve this query, or will I need to denormalize with pre-calculated rollups?

EDIT: Here's the CREATE TABLE statement:

CREATE TABLE `leads` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `user_id` int(11) NOT NULL,    `promo_id` int(11) NOT NULL,    `entries_count` int(11) NOT NULL DEFAULT '0',    PRIMARY KEY (`id`),    UNIQUE KEY `index_leads_on_promo_id_and_user_id` (`promo_id`,`user_id`),    UNIQUE KEY `index_leads_on_user_id_and_promo_id` (`user_id`,`promo_id`),    KEY `index_leads_on_user_id` (`user_id`),    KEY `index_leads_on_promo_id` (`promo_id`),    KEY `index_leads_on_user_id_and_entries_count` (`user_id`,`entries_count`),    KEY `index_leads_on_user_id_and_promo_id_and_entries_count` (`user_id`,`promo_id`,`entries_count`)  ) ENGINE=InnoDB AUTO_INCREMENT=413235 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

As you can see, I've added several indexes just to experiment and see if they'd improve the query's performance, but so far no dice.

Can I move the undo log outside of ibdata1 in MySQL 5.6 on an existing server?

Posted: 19 Sep 2013 02:00 PM PDT

I've been growing concerned about the large size of ibdata1 that can never shrink even when using file-per-table on innodb

Moving the undo log files outside seemed logical but this procedure seems rather complicated:

http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-undo-tablespace

Does anyone have hands-on experience accomplishing this on a live server?

This line makes it sound like it is impossible once mysql is already installed and running:

this feature can only be enabled when initializing a MySQL instance

So too late now?

how to fetch multiple rows with same field value from a mysql database using php [migrated]

Posted: 19 Sep 2013 11:56 AM PDT

|touser| fromuser |  msg  |  |  A   |    B     | hi    |  |  A   |    B     | hello |  |  C   |    D     | bye   |  |  C   |    E     | hey   |

when i use following query in mysql workbench it shows the desired result that is all the rows with given name:

select * from db.table1 where touser in ('A');

output:

|touser| fromuser |  msg  |  |  A   |    B     | hi    |  |  A   |    B     | hello |

but when i pass query from php commands the resultant array contains only first record

<?php   //connection establishing commands   $sql="select * from db.table1 where touser in ('A')";     $result=mysqli_query($link, $sql);     $data=mysqli_fetch_array($result,MYSQLI_NUM);     print_r($data);     //other stuff;   ?>

output:

    Array ( [0] => A [1] => B [2] => Hi )

am I missing something in the php commands?

Paging and grouping using CTE?

Posted: 19 Sep 2013 09:37 AM PDT

Given the following code, from the answer to this question, how can I adapt it for paging? I've read that Common Table Expression is a good way to do paging, but since I implemented CTE to solve a problem that was already over my head, I really have no idea where to start in merging the two solutions.

;with cte as  (    SELECT DISTINCT d.AppId,      d.AppName,      d.AppType,      Tags = STUFF((SELECT ', ' + t.TagName                    FROM AppTags t                    where d.AppID = t.AppID                    FOR XML PATH (''))                    , 1, 1, '')     FROM AppDetails d    WHERE d.AppID = '1'  )  select *  from cte  where tags like '%test1%'

Dynamic Joining Across Databases

Posted: 19 Sep 2013 06:38 PM PDT

I have a multi-database architecture with a single database for each client and a separate database to handle system-wide data. In this separate database, I have an 'Updates' table where a row is updated every time certain data from the client databases are updated. The 'Updates' table is setup somewhat like this:

DbName     CustomerId     ClientA    1             ClientB    1

I would like to join this data to a table in the database denoted in the 'DbName' column and end up with something like this:

DbName     CustomerId     CustomerName  ClientA    1              Bob Jones  ClientB    1              John Smith

What is the cleanest and most efficient solution for this problem?

RegEx with update query my sql

Posted: 19 Sep 2013 10:29 AM PDT

i need to use a mysql update query with regex so my example is 

update table_name set coulmn_name = replace(coulmn_name, 'regex', '');

i will explain a specified thing i need to do. my column is contained some text & some html tags i need to delete all img tags from this coulmn what can i do to achieve this ?

this is my ReGex

/</?(?i:img)(.|\n)*?>/g

but i don't know how to make this any help please

Can I reset the ibdata1 and ib_logfile files when innodb_file_per_table is already in place

Posted: 19 Sep 2013 10:39 AM PDT

I am running into an issue where sensitive data was not encrypted properly at the application level of our production system.

Even though the guilty records have been removed or corrected, scanning still reveal the records in the .ibd file, as well as the ibdata1 and ib_logfiles.

I have optimized the affected table and so the .ibd file is clean now; but I still have the issue of the ibdata1 and log files.

Doing a full mysqldump of all databases is a last resort as the data set if quite large. Since we are already using per table tablespaces, can I simply stop mysql, move the ib files and then restart and reset the files in that manner?

Scheduling duplication of two tables across instances

Posted: 19 Sep 2013 11:59 AM PDT

In my SQL Server 2012 database I have two tables:

  • [tbl_Customers] - about 30 rows
  • [tbl_Users] - about 10,000 rows

I need to schedule the two tables above to be copied to a new instance, say every 12 hours. I am considering setting up a SQL Azure account to act as the second instance.

[tbl_Users] is a popularly-accessed table, with CRUD operations being performed quite a lot, therefore the data changes often. It doesn't matter too much about those changes being replicated on the second instance immediately (but it would be nice if possible if the option allows).

Can anyone recommend which approach they might take to achieve this? I'm new to this type of thing so please don't hold back with the simplicity of your answers...!

I have SQL2012 Standard on my dev machine (but no SSIS at the moment although I could install it), but don't know if my existing SQL host and/or Microsoft have SSIS on their offerings.

Excessive replication snapshot size causing failure

Posted: 19 Sep 2013 02:42 PM PDT

I am searching for an explanation of a replication snapshot being in excess of 5 times what we're predicting

Following a replication failure we are attempting to reinitialise subscriptions with a new snapshot. The size of the database is 135GB, one table within the database is 60GB - we have excluded this article from the publication. This meant we had an expectation of snapshot size being ~75GB.

We've had several attempts at creating the snapshot and these have failed due to lack of disk space. Last night the snapshot filled a disk with 340GB of free space.

I 'd welcome any explanation for this huge size of snapshot.

In my examination of possible causes I note that the snapshot format is set to "Character - Required if a publisher or subscriber is not running SQL Server". This despite the fact that at both ends of this transnational replication is native SQL Server. Is there a different in size between formats?

Thanks in advance.

Native mode file Size:105 MB  C:>bcp IVM_ArchiveTest.dbo.Event out D:\NOBACKUP\UseOnce\EventNative.dat -T -n   218977 rows copied. Network packet size (bytes): 4096 Clock Time (ms.)   Total : 7878 Average : (27796.01 rows per sec.)     Character mode file Size:66 MB   C:\>bcp IVM_ArchiveTest.dbo.Event out D:\NOBACKUP\UseOnce\EventChar.dat -T -c   218977 rows copied. Network packet size (bytes): 4096 Clock Time (ms.)   Total : 1654 Average : (132392.38 rows per sec.)

Query getting periodically stuck in 'copying to tmp table' state, never completes

Posted: 19 Sep 2013 02:48 PM PDT

I am running Wordpress on a dedicated server with a MySQL backend. I have a query that usually takes <1 second to execute, but periodically, this query will get stuck in a 'copying to tmp table' state and stay that way indefinitely until it is either killed or until mysqld is restarted. After restarting mysqld the problem goes away, the (identical) query once again takes <1 second to execute. This leads me to believe this is a configuration problem.

How do I go about solving this problem? The query itself is not too intensive, and my server is not experiencing any sudden traffic spikes. The tables themselves are all InnoDB format.

Here is my my.cnf: http://pastebin.com/9UMPxfAr

The query:

SELECT ctt.term_id AS catid, p.ID, p.post_title AS title, GROUP_CONCAT(tt.term_id) as terms_id          FROM (SELECT * FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post' AND post_date > '2013-09-09 17:00:00' AND post_date < '2013-09-10 08:14:00') AS p                  JOIN wp_postmeta AS pm                          ON (p.ID = pm.post_id AND pm.meta_key = 'wpo_sourcepermalink')                  JOIN wp_term_relationships AS ctr                          ON (p.ID = ctr.object_id)                  JOIN wp_term_taxonomy AS ctt                          ON (ctr.term_taxonomy_id = ctt.term_taxonomy_id AND ctt.taxonomy = 'category' AND ctt.term_id IN ('8','314','6'))                  JOIN wp_term_relationships AS tr                          ON (p.ID = tr.object_id)                  JOIN wp_term_taxonomy AS tt                          ON (tr.term_taxonomy_id = tt.term_taxonomy_id AND (tt.taxonomy = 'post_tag' OR tt.taxonomy = 'post_author'))          GROUP BY tr.object_id          ORDER BY pm.meta_value ASC;

An EXPLAIN of the query: http://pastebin.com/m5ndBfVX

And the output of "SHOW ENGINE INNODB STATUS" when a query is stuck in the 'copying to tmp table' state: http://pastebin.com/h0xv4Sfa

Indices of some of the tables in the query:

http://pastebin.com/XtTBL7Xr

http://pastebin.com/NamVSu4F

SSRS 2008. subreport page breaking issue migrating SSRS 2005 reports over to 2008

Posted: 19 Sep 2013 07:03 PM PDT

I'm moving over several hundred reports that were created in SSRS 2005 over to an SSRS 2008 server, and most run fine when copying the .rpt files over, but the reports that have sub-reports don't page break correctly and the reports look terrible. Does anyone know of a way to correct this issue or do all of these reports need to actually be re-created from scratch in SSRS 2008?

Help with Oracle Pipelined function

Posted: 19 Sep 2013 08:22 PM PDT

I have a pivot where I have a contract with tank numbers separated by ; and I've been given this example to pivot them down into rows (which works):

SELECT oks_c.contract_number,          oks_l.line_id,         oks_l.tank_numbers, column_value AS TANK_NUMBER     FROM oks_contract_header oks_c   INNER JOIN oks_contract_lines oks_l       ON oks_l.contract_number = oks_c.contract_number AND          oks_l.item_name LIKE '%.55201'      , table(str2tbl(oks_l.tank_numbers,';')) acbb  ORDER BY oks_c.contract_number,            oks_l.line_id,            TANK_NUMBER

Here's the pipelined function:

CREATE OR REPLACE function DBRAJAH.str2tbl( p_str in varchar2, p_delim in varchar2 default ',' ) return   str2tblType  PIPELINED  as      l_str      long default p_str || p_delim;      l_n        number;  begin      loop          l_n := instr( l_str, p_delim );          exit when (nvl(l_n,0) = 0);          pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );          l_str := substr( l_str, l_n+1 );      end loop;      return;  end;

But I'm trying to understand why it works. Particularly how the pipelined function results are not cross-joined?

I'm familiar with outer apply and table-valued functions in SQL Server, but this seems to be quite different - or is this really an OUTER APPLY?

startup mount issues with Oracle

Posted: 19 Sep 2013 03:22 PM PDT

I am a newbie with Oracle so bear with me.

I am trying to simply shutdown and restart an Oracle db instance on a Windows Server 2008 machine (Oracle 11.2.0). Logged in via sqlplus, I issue the command shutdown immediate;. I get the

Database closed.    Database dismounted.    ORACLE instance shut down

reponses. I exit out of sqlplus, and try to log back in (ORACLE_SID is set correctly) so I can issue the startup mount; command, but I get the 

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

error. All of the Windows services involving my database dantest3 are started. Doing research, I found the oradim command, but while that starts my database and allowing me to login, that is the full startup, and I want to only be in "mount" mode.

I am logging in with the following command - 

sqlplus sys/[passwordforacct]@dantest3 as sysdba

If I try the simple sqlplus / as sydba, I get a 

ORA-12560: TNS:protocol adapter error

oradim command - 

oradim -start -sid dantest3

Requested variables -

TNS_ADMIN = C:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN  ORACLE_SID = dantest3  ORACLE_HOME_11gR2 = C:\Oracle\product\11.2.0\dbhome_1

Any advice or suggestions would be greatly appreciated!

Speeding up a NOT IN subquery

Posted: 19 Sep 2013 05:22 PM PDT

Good evening,

I'm currently up to try to improve the performance of some of my queries. As far as I know, statements like "IN" or "NOT IN" are even faster with a large amount of values if a subquery on an indexed field without conditions is used.

SELECT * FROM table1 WHERE field1 NOT IN (SELECT index_field FROM table2)

When it comes to use conditions at table2, the query becomes realy slow on a large amount of data.

SELECT *  FROM table1  WHERE      field1 NOT IN (          SELECT              index_field          FROM table2          WHERE              user_id = '2'      )

I was thinking about using LEFT Join but when I need to to the filtering for the user_id, it also becomes slow.

How may I solve this problem? Currently, I've no clue about that.

The original query looked sth. like this

SELECT      i.*  FROM stream_item si  LEFT JOIN user__publisher_item ui      ON ui.user_id = 2      && ui.item_id = si.item_id  INNER JOIN item i      ON i.id = si.item_id  WHERE       si.stream_id IN (5,7,8,9,19,24,29,42,43,44)      && ui.id IS NULL || (          ui.`read` = 0 || ui.saved = 1      )  GROUP BY      si.item_id  ORDER BY      si.`found` DESC  LIMIT 0,10  
id      select_type     table   type        possible_keys                                                                           key                 key_len     ref                 rows        Extra  1       SIMPLE          si      index       PRIMARY,stream_2_item,stream_id_found                                                   stream_2_item       4           \N                  663236      Using temporary; Using filesort  1       SIMPLE          ui      eq_ref      user_id_item_id,user_2_item,user_id_read_saved_hidden,user_id_saved,user_id_hidden      user_id_item_id     8           const,si.item_id    1           Using where  1       SIMPLE          i       eq_ref      PRIMARY                                                                                 PRIMARY             4           si.item_id          1

I have a table which represents the n:m relationship between stream and items. An unique item can be accessible via multiple streams. Based upon this i have a state-table for user dependent states of an item. Currently, the user-state-entry will only be generated, if the item was read or saved the first tim. Otherwise, there is no entry in the user_item table. In the beginning, this query was fine but now, it takes a lot of time. Even if I leave out the "INNER JOIN" it takes 7 seconds on the current tables with around 500k rows each.

The table-structure in the background is as follows:

  • stream
  • stream_item //n:m relation between stream and items
  • item //unique items
  • stream_group //user-defined groups of multiple streams
  • stream_group_streams //_n:m relation between stream_groups and streams_
  • user
  • user_item //state-table that holds the user-dependent states of an item - if there is no state, no row exists for an item

I already asked this question at Stackoverflow but added my 2 Edits with further information to late so that I haven't got an answer anymore.

Migration to databases Domain Users do not have access

Posted: 19 Sep 2013 04:22 PM PDT

I migrated databases to new servers, however the applications that were previously used with the databases are failing to load. I have changed the connections and etc. The jobs also seem to be failing. I have a domain account who is the job owner. However, when I try to execute the job under my User name i get the following error:

Executed as user: NT AUTHORITY\SYSTEM. Login failed for user.....[SQLSTATE 28000) (Error 18456).

Is this related to Domain Users not having appropriate read and write access to the database. Also how would I give All domain users permissions to execute stored procedures.

Creating a global temp table in MySQL

Posted: 19 Sep 2013 01:22 PM PDT

I am working on a MySQL stored procedure.

I need to create a temp table and I want to access this temp table whenever I execute this stored procedure.

I know we can't access the temp table in another request in MySQL.

Is there a way to create a temp table like globally, or how can I access the temp table across the multiple requests?

Which one to use? InnoDB Replication or MySQL Cluster?

Posted: 19 Sep 2013 10:22 AM PDT

I am building a url shortener and I use InnoDB as the storage engine for link data. I will start with a single VPS containing both application instance and MySQL database instance on the same Virtual Machine. I want the application to be easily scalable, I want to be able to add new nodes and make the system perform better as easily as possible when needed.

I have been reading about MySQL Cluster and MySQL Replication, but I haven't been able to decide on which one to use. You can imagine that (a popular) url shortener will be both write and read intensive. What is the structure that you would use in such a case? Would you go for cluster or replication?

Then based on the choice of cluster or replication, what is the infrastructure/configuration that I am supposed to have in order to be able to expand from a single innoDB engined Database to a cluster or replication structure? I want to start correctly, I don't want to be stucked in a situation where I can not expand/improve the database system when I need to.

Thanks A lot

sr.

MySQL server crashed.

Posted: 19 Sep 2013 07:22 PM PDT

Help! I managed to crash MySQL last night. I am on a Mac using the native version that came with Mountain Lion. I was upgrading from 5.5 to 5.6. I have followed instructions in this forum to delete the installation, but trying to re-install 5.5 says that there is a newer version and won't install. Trying to install 5.6 fails. I found this error in the console:

4/21/13 10:16:56.000 PM kernel[0]: exec of /Volumes/mysql-5.6.11-osx10.7-x86/mysql-5.6.11  osx10.7-x86.pkg/Contents/Resources/preflight denied since it was quarantined by TextEdit    and created without user consent, qtn-flags was 0x00000006

Help me please ?? I am stuck and in a world of hurt and despair.

Oracle: How to gather stats in a logical standby database?

Posted: 19 Sep 2013 12:22 PM PDT

I have a Primary and a (logical) Standby Database. The Schema 'APP' gets synced. Now a User has performance issues running a query against some tables in this 'APP' schema of the Standby Database. On the Primary the same query executed well after gathering table (DBMS_STATS.GATHER_TABLE_STATS) stats.

How to refresh statistics on the standby-side? The DBMS_STATS.GATHER_TABLE_STATS gives a ORA-16224.

With 

SELECT table_name,last_analyzed FROM dba_tables WHERE table_name IN ('TAB1','TAB2');

I can clearly see that the standby stats are a bit too old ;)

SELECT guard_status FROM v$database;    GUARD_STATUS  ------------  STANDBY

Running a TPC-C Benchmark Without sleep() i.e. key-in + think time

Posted: 19 Sep 2013 11:22 AM PDT

We are running a TPC-C benchmark against a PostgreSQL 9.2 server using JdbcRunner-1.2 implementation. During first few tests we were not getting a smooth (that is, without sudden spikes down to almost 0 from 300 that we got at times) TPS graph even in supposedly steady state. During these tests we had not specified the sleeptimes for transactions (there are different sleep times recommended for different transaction types). So, by default all agents (for example, 100) continuously ran a mix of five transaction types without any sleeps in between. Later, we found out that we could in fact specify the sleep times; and when we did we could see a smoother graph without much variations.

So, the question is, if not specifying the sleep could be the real reason behind the bad results in first few tests?

Deleting folders after Oracle upgrade

Posted: 19 Sep 2013 02:22 PM PDT

I upgraded from Oracle 10g to Oracle 11g in Windows Server 2003.

New Oracle home : C:\app\oracle\product\11.2.0\db_1

Old Oracle 10g was installed on: C:\oracle\product\10.2.0\

Questions

  • Is the 10g folder is still useful?
  • Could I delete the 10g folder without a problem?

How to optimize a log process in MySQL?

Posted: 19 Sep 2013 06:22 PM PDT

In my project, I have about 100.000 users and can't control their behavior. Now, what I would like to do is log their activity in a certain task. Every activity, is one record which includes columns like user_id and some tag_id's.

The problem I have, is that these tasks in some cases can go up to 1.000.000 per year per user. So if I would store all these activities in one table. that would obviously become very big (=slow).

What is best to do here? Create a single table per user (so I have 100.000 log tables) or put all these activities in one table? And what kind of engine should I use?

One important thing to note: Although i simplified the situation a bit the following doesn't look normal, but users can also change values in these tables (like tag_id's).

Cannot change the Delete Rule of a self referencing foreign-key-relationship to cascade delete

Posted: 19 Sep 2013 01:05 PM PDT

I have the following table

[dbo].[myObj]  (      [ID] [int] IDENTITY(1,1) NOT NULL,      [fk_ParentID] [int] NULL  )

and I have a relationship from fk_ParentID to ID.

I want to change the Delete Rule in SQL Server Management Studio under the Window "Foreign-Key Relationships"->Insert and Update Specifications->Delete Rule to ON Delete Cascade, but it is not possible.

Can you tell me why, and how I can make this possible?

Oracle listagg forces SORT (GROUP BY) execution plan

Posted: 19 Sep 2013 01:45 PM PDT

I have the following query

SELECT /*+ USE_HASH_AGGREGATION */ id  --, count(id)  , listagg(type, ', ') within group (order by null) types   FROM test  group by id

Both type and id are short strings. The execution plan for when I comment out listagg and leave count aggregation in is a HASH GROUP BY (even without the hint) and works fast. With the listagg aggregation Oracle always chooses SORT GROUP BY which is an order of magnitude slower. Is there any reason for that?

Is database normalization dead?

Posted: 19 Sep 2013 03:50 PM PDT

I've been brought up old school - where we learned to design the database schema BEFORE the application's business layer (or using OOAD for everything else). I've been pretty good with designing schemas (IMHO :) and normalized only to remove unnecessary redundancy but not where it impacted speed i.e. if joins were a performance hit, the redundancy was left in place. But mostly it wasn't.

With the advent of some ORM frameworks like Ruby's ActiveRecord or ActiveJDBC (and a few others I can't remember, but I'm sure there are plenty) it seems they prefer having a surrogate key for every table even if some have primary keys like 'email' - breaking 2NF outright. Okay, I understand not too much, but it gets on my nerves (almost) when some of these ORMs (or programmers) don't acknowledge 1-1 or 1-0|1 (i.e. 1 to 0 or 1). They stipulate that it's just better to have everything as one big table no matter if it has a ton of nulls "todays systems can handle it" is the comment I've heard more often.

I agree that memory constraints did bear a direct correlation to normalization (there are other benefits too :) but in today's time with cheap memory and quad-core machines is the concept of DB normalization just left to the texts? As DBAs do you still practice normalization to 3NF (if not BCNF :)? Does it matter? Is "dirty schema" design good for production systems? Just how should one make the case "for" normalization if it's still relevant.

(Note: I'm not talking about datawarehouse's star/snowflake schemas which have redundancy as a part/need of the design but commercial systems with a backend database like StackExchange for example)
Posted by at No comments:

[MS SQL Server] DB Size

[MS SQL Server] DB Size

DB Size

Posted: 19 Sep 2013 01:41 AM PDT

Why is the database size different than the physical file size? The general page of the database show 12000MB. If I browse to the location of the MDF, it is 8192000KB. Why don't these match? This is an example DB, the rest of my DB's are the same, but not quite as much of a discrepancy.Thanks,

wait stats precentage?

Posted: 26 Sep 2012 10:41 PM PDT

Hi,[code="sql"]WITH Waits AS(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rnFROM sys.dm_os_wait_stats WITH (NOLOCK)WHERE wait_type NOT IN (N'CLR_SEMAPHORE',N'LAZYWRITER_SLEEP',N'RESOURCE_QUEUE',N'SLEEP_TASK',N'SLEEP_SYSTEMTASK',N'SQLTRACE_BUFFER_FLUSH',N'WAITFOR', N'LOGMGR_QUEUE',N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',N'XE_TIMER_EVENT',N'BROKER_TO_FLUSH',N'BROKER_TASK_STOP',N'CLR_MANUAL_EVENT',N'CLR_AUTO_EVENT',N'DISPATCHER_QUEUE_SEMAPHORE', N'FT_IFTS_SCHEDULER_IDLE_WAIT',N'XE_DISPATCHER_WAIT', N'XE_DISPATCHER_JOIN', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',N'ONDEMAND_TASK_QUEUE', N'BROKER_EVENTHANDLER', N'SLEEP_BPOOL_FLUSH',-- DIRTY_PAGE_POLL, HADR_FILESTREAM_IOMGR_IOCOMPLETION, and SP_SERVER_DIAGNOSTICS_SLEEP are SQL 2012 onlyN'DIRTY_PAGE_POLL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'SP_SERVER_DIAGNOSTICS_SLEEP'))SELECT W1.wait_type, CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,CAST(W1.pct AS DECIMAL(12, 2)) AS pct,CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pctFROM Waits AS W1INNER JOIN Waits AS W2ON W2.rn <= W1.rnGROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pctHAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); -- percentage threshold[/code]output values for exapmle wait type is CXPACKET,Here which precentage should be consider? pct or running pct.wait type - CXPACKETWAITTIME - 305.65precentage - 1.85Running precentage - 91.39

how to insert powershell values into sql server table

Posted: 18 Sep 2013 09:44 PM PDT

Hi,I am looking to find way to hold values in my sql table. actually i have powershell script it is returning some values, but i want to save those values into my table.can anyone have any idea on it. please help me out.Thanks
Posted by at No comments:

[Articles] Opportunities Abound

[Articles] Opportunities Abound

Opportunities Abound

Posted: 18 Sep 2013 11:00 PM PDT

We have so many ways in which we can grow our SQL Server careers. Steve Jones shares a few today.
Posted by at No comments:

[SQL 2012] Disaster Recovery - SQL vs VM Disk Replication

[SQL 2012] Disaster Recovery - SQL vs VM Disk Replication

Disaster Recovery - SQL vs VM Disk Replication

Posted: 19 Sep 2013 02:22 AM PDT

We are evaluating approaches to DR. For SQL Server we have the usual log shipping, replication, and various 'AlwaysOn' methods. The other approach is to use one of the Hypervisor or SAN based disk level replication technologies.Certainly, one advantage of the SQL approaches is the the system at the remote DR site is 'warm' or even 'hot'.But I have not been able to find discussions on the relative efficiency of the two approaches. I would imagine that any Hypervisor or SAN based replication needs to replicate ALL the disk writes. That includes the MDF as well as the LDF writes etc., AND any Windows NTFS writes such as the system journal and file attribute structures if the file grows or shrinks. Whereas the SQL methods are really only replicating the resulting DB change, and letting the remote system apply the change.I would think that the SQL replication could therefore use significantly less network bandwidth between the primary and DR sites.Does anyone have any experience or opinions here???Thanks,Dave

SSRS 3.0 average

Posted: 19 Sep 2013 12:12 AM PDT

Hi all, I am trying to do an average of a calculated collumn in SSRS (matrix table), the column in question is the result of an expression =sum(Fields!Current_AR.Value) / sum(Fields!Total_AR.Value), now i want to do the average of this collumn but i cant seem to do it..Anyone got a suggestion?Best regards,Daniel

Question on certificate and asymmetric logins

Posted: 18 Sep 2013 01:42 PM PDT

Hello all,I have a classic question (probably for most of you), as I am new to dba. Why do we use certificate or asymmetric types of login?As far as I know, Windows and SQL server authenticated logins will do fine jobs. In what scenario does one use certificate or asymmetric logins? I can only see one benefit that you can set specific expiry date for certificate logins, but other than that i can not see anything significant. So based on that, why do we need them?Please your advise, correct me if I am wrong. Thanks.

Extended Events

Posted: 18 Sep 2013 04:02 PM PDT

Dear AllI am trying to find out why some procedures are getting recomiled. For this when i run following select,SELECT deqs.last_execution_time AS [Time], dest.text AS [Query],dest.dbidFROM sys.dm_exec_query_stats AS deqsCROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS destORDER BY deqs.last_execution_time DESCit gives out put as many lines of "Create procedure...."To find out the reason for "create procedure ...", I tried with extended event. I am capturing SQLStatementREcompile event. But when start the session and check the live data, it does not show same numbers of lines as shown by above select.Am i missing some thing?Regards

Accidental DBA - Where to Start? (General Question)

Posted: 18 Sep 2013 03:23 AM PDT

Hello,I will be taking over the role of a SQL Server DBA for my company. Our SQL Servers have always been maintained by our Server Admins. I will be going to training in about a week. I would like any suggestions on what I should begin looking at when I come back from training. I am familair with SQL Server Versions 2005, 2008, and 2008 R2 from a developers viewpoint. The DBA role will be new to me. What would be some of the first issues I should address as I take over the responsibility of these databases?Thanks!
Posted by at No comments:

[T-SQL] how to write a stored procedure for this

[T-SQL] how to write a stored procedure for this

how to write a stored procedure for this

Posted: 18 Sep 2013 09:44 PM PDT

i have a table emp with columns empname and highprority jobs.for example i have 3 emp and 10 high priority jobs(assume that high priority jobs are from h1- h10).I want to make sure to assign highpriority jobs in such a way that each employee must assign same no of jobs,( for example ,tsaliki has here 2 high priority jobs,sasi 2 and srinivas 1 .SO while inserting the next high priority job first it should assign the high priorty job h6 to srinivas since he has low high priorrity jobs compared to others.likewise everytime while assigning the jobs to each employee i want to make sure that all employess must have same no of high priority jobs or just 1 more .Becasue here there are 10 high priority jobs.So ultimately my goal is to assign 3 high priority jobs to each emp and the other can be assigned to any emp since all other 3 has same high proroty jobs.The records of emp table are as follows-empname highprorityjostsaliki h1sasi h2Srinivas h3 tsaliki h4sasi h5.

Help to get my desired output using stored procedure

Posted: 18 Sep 2013 09:13 PM PDT

i have a table emp with columns empname and highprority jobs.for example i have 3 emp and 10 high priority jobs(assume that high priority jobs are from h1- h10).I want to make sure to assign highpriority jobs in such a way that each employee must assign same no of jobs,( for example ,tsaliki has here 2 high priority jobs,sasi 2 and srinivas 1 .SO while inserting the next high priority job first it should assign the high priorty job h6 to srinivas since he has low high priorrity jobs compared to others.likewise everytime while assigning the jobs to each employee i want to make sure that all employess must have same no of high priority jobs or just 1 more .Becasue here there are 10 high priority jobs.So ultimately my goal is to assign 3 high priority jobs to each emp and the other can be assigned to any emp since all other 3 has same high proroty jobs.The records are as follows-empname highpriorityjobs tsaliki h1,h2sasi h3,h4 srinivas h5

How to retrieve integer and decimals from string

Posted: 18 Sep 2013 11:10 PM PDT

Hi,I would like to know how to retrieve integer,decimals from string in table format.Input string: < 2% annual < 0.16% monthlyInput string: < 5% annual < 0.41% monthlyInput string: > 3% annual > 0.25% monthlyInput string: > 3% annual > 25% monthlyExpected output:Num1 Num2-------------2 0.165 0.413 0.253 25Create table script:create table tbl_RetrieveNumbers( Samplestring varchar(100))insert into tbl_RetrieveNumbers values('< 2% annual < 0.16% monthly')insert into tbl_RetrieveNumbers values('< 5% annual < 0.41% monthly')insert into tbl_RetrieveNumbers values('> 3% annual > 0.25% monthly')insert into tbl_RetrieveNumbers values('> 3% annual > 25% monthly')Thanks,Kumar.

how to get the desired output correctly

Posted: 18 Sep 2013 11:29 PM PDT

i have a table emp with records as follows:Create table testemp(empname varchar(50),highprtjobs varchar(50))insert into testemp values ('tsaliki','h1')insert into testemp values ('tsaliki','h4')insert into testemp values ('sasi','h2')insert into testemp values ('sasi','h5')insert into testemp values ('srinivas','h3')select * from testempin this i wrote a select statement as follows:select empname,count(highprtjobs)as noofhighprtjobs from testemp group by empname. i got the result as empname noofhighprtjobssasi 2srinivas 1tsaliki 2In this now i am trying to get the one which is having min noofhighprtjobs( here srinivas result i should get it as output) So i wrote the above statement but it is throwing some error.So how do i get the empname whose noofhighprtjobs is minimum.select empname,count(highprtjobs)as noofhighprtjobs from testemp group by empnamehaving min(count(highprtjobs))

Get the Maximum Value from a related table based on date

Posted: 18 Sep 2013 10:12 PM PDT

Hi Folks,Can someone help with the following on SQL Server 2008...I have a table Customer and an Orders Table...I want to return back against each customer the Order number from the last order placed (highest OrderDate)...For example, if Customer X has 3 orders and Customer Y has 3 orders, I want X and Y customer names returned along with the the order number form each customer's last placed order.Setup script for the 2 tables plus data as follows...SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Customer]( [CustomerID] [int] NOT NULL, [CustomerName] [varchar](50) NULL, CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ( [CustomerID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Orders]( [OrderID] [int] NOT NULL, [OrderDate] [datetime] NULL, [OrderNumber] [varchar](50) NULL, [CustomerID] [int] NULL, CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [OrderID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOINSERT INTO [Customer] ([CustomerID],[CustomerName])VALUES (1,'David'),(2,'John'),(3,'Sue')GOINSERT INTO [Orders] ([OrderID],[OrderDate],[OrderNumber],[CustomerID])VALUES(1, '2013-01-01', '0001', 1),(2, '2013-01-03', '0002', 1),(3, '2013-02-12', '0003', 1),(4, '2013-01-17', '0004', 2),(5, '2013-01-18', '0005', 2),(6, '2013-02-01', '0006', 2),(7, '2013-01-14', '0007', 3),(8, '2013-01-21', '0008', 3)GOWhat I'd Like back is...'David', '0003''John', '0006''Sue', '0008'So far I've got...SELECT A.[CustomerName], ISNULL(X.[MaxOrderNo], '') AS [LastOrderNumber]FROM [Customer] ALEFT OUTER JOIN (SELECT A.[CustomerID], ISNULL(MAX(A.[OrderNumber]), '') AS [MaxOrderNo] FROM [Orders] A GROUP BY A.[CustomerID]) X ON X.[CustomerID] = A.[CustomerID]However, although this gives the correct result with the current data, I need to check that the value from the order returned = the last order date as I can't guarentee that the highest order number for a customer has the highest date - this can be demonstrated by swapping the dates round on the last 2 orders (my query would give the wrong result)...Many thanks for any help :-)

T-SQL help

Posted: 18 Sep 2013 03:19 AM PDT

Hi,MyTotUnits field should get updated from field Units based on the condition below : When Project ,Product ( UC only) and (LIKE) SSum (P-110 from ID = 1 IS CONTAINED IN ID = 2 FOR THE SAME PROJECT AND Product . ANd SNum for ID = 2 contains for characters than SNum for ID =1 ) then Units = 430 should be updated to MyTotUnits from ID =2 CREATE TABLE #temptable ( ID INT IDENTITY(1,1),Project VARCHAR(10),Product VARCHAR(20) ,SNum VARCHAR(20),Units FLOAT , MyTotUnis FLOAT)INSERT INTO #temptable (Project ,Product ,SNum ,Units , MyTotUnis)SELECT 'P1','xxx','P-110',20,0UNIONSELECT 'P1','xxx','P-110',30,0UNIONSELECT 'P1','UC','P-110',40,0UNIONSELECT 'P1','xxx','P-110_ABC',40,0UNIONSELECT 'P1','xxx','P-110_ABC',40,0UNIONSELECT 'P1','xxx','P-110_ABC',40,0UNIONSELECT 'P1','UC','P-110_ABC',430,0UNIONSELECT 'P2','xxx','P-123',40,0UNIONSELECT 'P2','xxx','P-123',70,0SELECT * FROM #temptableDROP TABLE #temptable

Sql Query Output Join with another table

Posted: 18 Sep 2013 09:36 AM PDT

I have a query which gives me the following output : [code="sql"]select PD.ProductId, TotalCalls = COUNT(DISTINCT PD.LogId), TrueCalls = COUNT(DISTINCT case when PD.ExceptionCode = ' ' then PD.LogId END), ErrorCalls =COUNT(DISTINCT case when PD.ExceptionCode != ' ' then PD.LogId END), PassPercentage = CONVERT(DECIMAL(10,1),100 - (CAST(COUNT(DISTINCT case when PD.ExceptionCode != ' ' then PD.LogId END) as float)/CAST(COUNT(PD.LogId) as float)*100)) from Log P INNER JOIN LogProduct PD ON P.LogId = PD.LogId WHERE (ResponseTime < '2013-09-28' and RequestTime > '2013-09-01') Group By PD.ProductId[/code]It gives me the following output : [code="other"]ProductId TotalCalls TrueCalls ErrorCalls PassPercentage1 6 6 0 100.02 1 0 1 85.73 33 15 18 92.2[/code]Now I have another Table : Levels :[code="other"]LevelId Min Max Bool ProductId1 100 100 0 22 80 99 0 23 60 79 0 24 40 59 0 25 1 39 1 26 0 0 0 27 -1 -1 0 21 100 100 0 12 80 99 0 13 60 79 1 14 40 59 0 15 1 39 0 16 0 0 0 17 -1 -1 0 1[/code]What I would like to do is compare the output of the first query and add a new LevelId column : example : I am looking for an output like this : [code="plain"]ProductId TotalCalls TrueCalls ErrorCalls PassPercentage LevelId1 6 6 0 100.0 12 1 0 1 85.7 2[/code]The logic here is that : I would like to compare the PassPercentage for each row for that particular product and find out which level it falls in . In the example above : PassPercentage is 85.7 for product 2 . If you check the Levels table above for ProductId 2 ,Level 2 should be chosen as 80 < 87.5 < 99I cannot figure out How I can do this..Please let me know how I go forward from here ... or give me ideas of what I ought to do ??

Adding column if not exist with value

Posted: 18 Sep 2013 08:15 AM PDT

Hi all, using the below, how do I set the DBVersion to 1 with this line, if the column DBVersion exist I don't want to change the number but if it doesn't exist I want to create it and have DBVersion set to 1if not exists (select * from syscolumns where id=object_id('ConfigTB') and name='DBVersion') alter table ConfigTB add DBVersion INTThanks

SQL - Find Week Nbr

Posted: 18 Sep 2013 02:42 AM PDT

Hello all, First let me say thank you for the tips/tricks/best practices that I have gleaned by reading the forums. I have learned so much!!!My issue is I need to determine a week number. Hopefully I'll explain it succintly. I have a jobtask table. Each job has a schedule start date. Using the schedule start date I need to find the week nbr (tnbr). Jobs scheduled in current week are T00, next week T01, etc. Weeks run from Sunday 00:00 to Saturday 23:59. I am doing this for a different report in a stored procedure using a cursor. After having been reading the forums it occurred to me that maybe I don't need to use a cursor. I've included pared-down table and query for brevity sake. --===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#jobtask','U') IS NOT NULL DROP TABLE #jobtask--===== Create the test table with CREATE TABLE #jobtask ( jobnbr varchar(8), tasknbr varchar(3), schstrdt datetime )INSERT into #jobtask (jobnbr, tasknbr, schstrdt)SELECT '12500173','500','2013-09-18 07:30:00.000' UNION ALLSELECT '13502889','500','2013-09-25 08:00:00.000' UNION ALLSELECT '13506023','0','2013-09-20 07:30:00.000' UNION ALLSELECT '13506779','0','2013-09-26 07:30:00.000' UNION ALLSELECT '13506780','0','2013-09-29 07:30:00.000' UNION ALLSELECT '13510071','500','2013-09-18 07:30:00.000' UNION ALLSELECT '13510263','500','2013-09-23 07:30:00.000' UNION ALLSELECT '13510321','500','2013-09-25 07:30:00.000' UNION ALLSELECT '13510331','500','2013-09-25 07:30:00.000' UNION ALLSELECT '13510527','500','2013-09-30 00:00:00.000' UNION ALLSELECT '13510578','500','2013-10-02 00:00:00.000' UNION ALLSELECT '13510733','500','2013-09-18 07:30:00.000' UNION ALLSELECT '13510887','500','2013-09-18 07:30:00.000' UNION ALLSELECT '13510945','500','2013-09-18 07:30:00.000' UNION ALLSELECT '13510983','500','2013-09-23 00:00:00.000' Here is what the data should look like.jobnbr task schstrdt tnbr12500173 500 09/18/2013 07:30 T0013502889 500 09/25/2013 08:00 T0113506023 0 09/20/2013 07:30 T0013506779 0 09/26/2013 07:30 T0113506780 0 09/29/2013 07:30 T0213510071 500 09/18/2013 07:30 T0013510263 500 09/23/2013 07:30 T0113510321 500 09/25/2013 07:30 T0113510331 500 09/25/2013 07:30 T0113510527 500 09/30/2013 00:00 T0213510578 500 10/02/2013 00:00 T0213510733 500 09/18/2013 07:30 T0013510887 500 09/18/2013 07:30 T0013510945 500 09/18/2013 07:30 T0013510983 500 09/23/2013 00:00 T01Thanks in advance and please let me know if something isn't clear.cmw
Posted by at No comments:

[SQL Server 2008 issues] trying to send database mail of sql server using gmail account

[SQL Server 2008 issues] trying to send database mail of sql server using gmail account

trying to send database mail of sql server using gmail account

Posted: 18 Sep 2013 06:17 PM PDT

Hi i am trying to send database mail and using gmail as an smtp server.i have used my emailid,and password for credentials.port use is either 587 or 456.but i am getting following error.i got all this from netThe mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2013-09-19T11:50:46). Exception Message: Could not connect to mail server. (No such host is known). ) can anybody tell what could be reason , why it is giving error "No such host is known"

Adding time intervals in minutes and display as csv

Posted: 17 Sep 2013 10:57 PM PDT

i have a table with three columns ascol1 col2 col311:30 13:30 1500:10 01:40 5the out put should be as follows11.30,11.45,12.00,12.15,12.30,12.45,13.00,13.15,13.3000.10,00.15,00.20,00.25.....01.40earlier help is highly appreciated

String Split

Posted: 17 Sep 2013 09:29 PM PDT

I want to write query which procures me result like below.General rule is if any word starts from number then it should split as word.Input-----Ørstedsvej 7BVolvo 25D104ND NokiaResult------Ørstedsvej 7 BVolvo 25 D104 ND NokiaOr the simplest way to do.

csv to rows

Posted: 18 Sep 2013 06:07 AM PDT

I have a table as followscol1 col2 col3 col4 col5A 11:30 13:30 15 11.30,11.45,12.00,12.15,12.30,12.45,13.00,13.15,13.30i need the following outputA 11.30A 11.45A 12.00A 12.15A 12.30A 12.45A 13.00A 13.15A 13.30earlier help is highly appreciated

Export data from corrupted table

Posted: 18 Sep 2013 06:58 AM PDT

I have a table that has inconsistency errors.There are around 13 million rows of data in it.1. If I truncate this table and run a dbcc, would the errors disappear?2. Is there a way I can copy the latest 1 million or half a million rows of data through export data wizard or through select * into?

dynamic filename in SSIS flat file source

Posted: 18 Sep 2013 07:14 AM PDT

How to specify for dynamic filename in SSIS flat file sourcefor file 20110818_abc_def.csvas the name abc_def does not changes only the date part changes. so how to make it dynamicaly and also the file source location does not change.Any clue on it.Shaun.

SQL profiler

Posted: 18 Sep 2013 01:52 AM PDT

Hello all, We are running profiler for one of our systems and the profiler shows that one specific query is executed 8000 times could this be the sql server or the packet are getting dropped by the network and it is trying again ? see belowTextData ApplicationName NTUserName LoginName CPU Reads Writes Duration ClientProcessID SPID StartTime EndTime BinaryDataexec sp_executesql selectXXXXXX .Net SqlClient Data Provider NULL DBName 0 2 0 132 2652 81 2013-09-18 10:43:57.973 2013-09-18 10:43:57.973

Create link server for Microsoft Access2000

Posted: 28 Aug 2013 06:00 AM PDT

Hello,Can some one help to create link server for Microsoft Access2000, here is what I did but it is not working:EXEC sp_addlinkedserver @server = N'fx', @provider = N'Microsoft.ACE.OLEDB.12.0', @srvproduct = N'OLE DB Provider for ACE', @datasrc = N'\\us-balt-san-1\inhouse$\shipping\FX\DATA\fx.mdb';GO

Backing up a Mirror

Posted: 18 Sep 2013 04:56 AM PDT

If you are mirroring a database, should you still run backups on the Mirror?

html file to sql server table

Posted: 18 Sep 2013 02:26 AM PDT

How can I get data out of this HTML table into a new SQL server table.This is a sample HTML code of a table, I have some HTML files with 400000 rows with 48 columns.I have never worked on a HTML file before.<html><head><META content="text/html;charset=Cp1252"></head><body><table border><tr><th>RESPrimaryId</th><th>ResultCode</th><th>Description</th><th>Abbreviation</th><th>Notes</th><th>LastUpdateTime</th><th>LastUpdateUser</th><th>CreatedTime</th><th>CreatedUser</th></tr><tr><td>1000000001</td><td>1</td><td>Cleaned / Lubricated</td><td>1</td><td></td><td>2013-09-09 19:00:00.139</td><td>DBA</td><td>2006-11-03 15:03:41.593</td><td>Installer</td></tr><tr><td>1000000002</td><td>2</td><td>Performance Test</td><td>2</td><td></td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2006-11-03 15:03:41.640</td><td>Installer</td></tr><tr><td>1000000003</td><td>3</td><td>Adjusted / Calibrated</td><td>3</td><td></td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2006-11-03 15:03:41.656</td><td>Installer</td></tr><tr><td>1000000004</td><td>4</td><td>Removed from Service</td><td>4</td><td></td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2006-11-03 15:03:41.656</td><td>Installer</td></tr><tr><td>1000000005</td><td>5</td><td>Repair/Tested/Return Svc</td><td>5</td><td></td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2006-11-03 15:03:41.671</td><td>Installer</td></tr><tr><td>1000000006</td><td>6</td><td>Performed Inservice</td><td>6</td><td></td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2006-11-03 15:03:41.671</td><td>Installer</td></tr><tr><td>1000000007</td><td>7</td><td>Completed as Requested</td><td>7</td><td></td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2006-11-03 15:03:41.671</td><td>Installer</td></tr><tr><td>1000000008</td><td>8</td><td>Serviced by Vendor</td><td>8</td><td></td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2006-11-03 15:03:41.671</td><td>Installer</td></tr><tr><td>1000000009</td><td>9</td><td>Transferred Job</td><td>9</td><td></td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2006-11-03 15:03:41.687</td><td>Installer</td></tr><tr><td>1000000010</td><td>10</td><td>Not Found/Unavailable</td><td>10</td><td></td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2006-11-03 15:03:41.687</td><td>Installer</td></tr><tr><td>1000000072</td><td>102</td><td>REPAIR FROM PM</td><td>RFP</td><td>REPAIR THAT IS COMPLETED FROM A FAULT FOUND DURING PM</td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2007-03-16 09:35:48.588</td><td>DBA</td></tr></table></body></html>

Anyone used SQL Trace (server side) as a permanent auditing solution?

Posted: 18 Sep 2013 03:21 AM PDT

I have some very strict auditing requirements on my current project, and SQL Server Audit has some serious flaws. CDC won't work in entirety, either. I'm considering setting up server side traces, as the data that SQL Trace is able to gather would be sufficient. I know that it has a much smaller performance impact than Profiler, but I'm still unsure about how it would hold up over the long term. Has anyone tried to use this as a permanent auditing solution? If so, what were your results? Pros/cons?

Check File exists and generate File

Posted: 18 Sep 2013 02:09 AM PDT

i need to create package,if file exists then copy the file to different location but with different name such as using today date.Shaun

Error message when changing location of share drive in SSIS package

Posted: 18 Sep 2013 03:56 AM PDT

Hi all,At work we have a job that does the backup of 3 tables to a sharedrive using a SSIS package delivering the data in a flat file.This week i had to change the location of the sharedrive in the package, after doing the changes the package is not working and the following message appears (see below), does anyone knows how to solve this one?Executed as user: (Database Name)\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.50.4279.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 9:46:26 AM Error: 2013-09-18 09:46:27.42 Code: 0xC020200E Source: Data Flow Task Flat File Destination [111] Description: Cannot open the datafile "ShareLocation\Filename.txt". End Error Error: 2013-09-18 09:46:27.42 Code: 0xC004701A Source: Data Flow Task SSIS.Pipeline Description: component "Flat File Destination" (111) failed the pre-execute phase and returned error code 0xC020200E. End Error Error: 2013-09-18 09:46:27.42 Code: 0xC020200E Source: Data Flow Task Flat File Destination [16] Description: Cannot open the datafile "ShareLocation\Filename.txt". End Error Error: 2013-09-18 09:46:27.42 Code: 0xC004701A Source: Data Flow Task SSIS.Pipeline Description: component "Flat File Destination" (16) failed the pre-execute phase and returned error code 0xC020200E. End Error Error: 2013-09-18 09:46:27.44 Code: 0xC020200E Source: Data Flow Task Flat File Destination [166] Description: Cannot open the datafile "ShareLocation\Filename.txt". End Error Error: 2013-09-18 09:46:27.44 Code: 0xC004701A Source: Data Flow Task SSIS.Pipeline Description: component "Flat File Destination" (166) failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:46:26 AM Finished: 9:46:27 AM Elapsed: 0.672 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,009/18/2013 09:00:00,Aurum - Backup to share,Error,0,(Database Name),Aurum - Backup to share,(Job outcome),,The job failed. The Job was invoked by Schedule 14 (daily). The last step to run was step 1 (*****).,00:00:00,0,0,,,,009/18/2013 09:00:00,Aurum - Backup to share,Error,1,(Database Name),Aurum - Backup to share,Aurum Share Backup,,Best regards,Daniel

Stored Procedure Execution Error Message

Posted: 17 Sep 2013 11:04 PM PDT

We have this stored procedure create that runs when i add any of the parameters to the section of the code to prompt me to enter values at the time of execution but ideally i want to run this for all sets of data and when i do that, i receive an error message of "Command text was not set for the command object" If under the Add parameters section, I add the following list of parameters @PCN_Key AS INT, @CostSetKey as INT, @Part_Type_MP AS VARCHAR(100) and then when I go to execute the sproc with the values of 156371 for the PCN_Key, 1609 for the CostSetKey and Bottle for the Part_Type_MP, the table gets populated. Since there are more then 1 PCN_Key and Part_Type_MP, i would like the SPROC to pull in all data. So when I remove the parameters section from the code and execute it, no parameters appear on the Execute Procedure box and when I click OK, the message then appears. USE [ConstarOLAP_PROPHIX_FactDb] GO /****** Object: StoredProcedure [dbo].[ProphixStdCostExport] Script Date: 09/16/2013 16:38:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- -- Author: <Peter Pearce, Baker Tilly> -- Create date: <September 9, 2013> -- Description: <Executes Remote Plex Sproc for Exporting Standard Cost> -- ALTER PROCEDURE [dbo].[ProphixStdCostExport] -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @PCN_Key AS INT = '', @CostSetKey AS INT = '1609', @Part_Type_MP AS VARCHAR(100), @Part_No AS VARCHAR(100) = '', @Cost_Date AS VARCHAR(20), @Building_No_MP AS VARCHAR(1000)='', @qq AS CHAR(1) = '''', @sproc AS VARCHAR(1000) SET @Cost_Date = CONVERT(VARCHAR,DATEADD(d,0,DATEDIFF(d,0,GETDATE() -1)), 120) SET @sproc = 'sproc166848_58075_1335485(' @qq @Part_No @qq ',' CAST(@PCN_KEY AS VARCHAR(20)) ',' @qq CONVERT(VARCHAR, @Cost_Date,120) @qq ',' @qq @Part_Type_MP @qq ',' @qq @Building_No_MP @qq ',' CAST(@CostSetKey AS VARCHAR(20)) ')' DELETE FROM [tblStdCost] INSERT INTO [tblStdCost] ( Part, Plant, Building, Date, Account, Currency, Version, Measure ) EXEC(@sproc) AT [PLEXREPORTSERVER] END

Convert date to a format of dd/mm/yyyy HH:mm:ss

Posted: 18 Sep 2013 01:56 AM PDT

Hi,I have the following sql[code="other"]Select '2013-09-17 21:01:00.000'[/code]I want this the date to show as:17/09/2013 21:01:00I have tried the following sql but does not change to do the format I require[code="other"]Select CONVERT(varchar,'2013-09-17 21:01:00.000',103)[/code]Thanks

Multiple splits

Posted: 17 Sep 2013 10:09 PM PDT

I have string of format with two delimiters '|' pipe and ',' comma '1,100,12345|2,345,433|3,23423,123|4,33,55'And have to insert into table columns as belowseq invoice amount1 100 123452 345 4333 23423 1234 33 55Please helpThanks & Regards,Prathibha

How to add date stamp and set expression or variable on ftp task?

Posted: 18 Sep 2013 01:56 AM PDT

Hi All,I have a file on local server say C:\fOLDER\Data.txtNow I need to move this file onto a FTP Server with date stamp attached to it and archive it on a daily basis.Example--/root/history/Data_09_18_2013.txt---remote Ftp serverThe next day again new file should be created in ftp server history directory as /root/history/Data_09_19_2013.txtI need to do this using SSIS nad run this package everyday as a job.Kindly help me with this issue.Thanks all in advance!

Unable to remove old transaction log backups.

Posted: 17 Sep 2013 09:32 PM PDT

Hi All,We have a DR server configured with logshipping. The transaction logs are taken from Production and copied to the DR server and restore job happens from there for logshipping. Our Production DB'ss full and txn logs are saved to tape drives on a timely basis, hence i need not worry about the txn logs copied to DR server. Now these txn logs on DR are occupying too much space as there are txn log files from past 3-4months. I want to all files older than 1 week for which i tried maintenance job but with no result.Could somebody help me out with this issue.Thanks,Hari Mindi

Help in Where Clause

Posted: 18 Sep 2013 12:13 AM PDT

Hi All, Im stuck and need some help, I have 3 out of 4 variables working in my Where clause and need help getting the 4th one to work. My variables are @startdate, @enddate, @class, @STdrp. The date range works fine and when I filter for @class that works as well, but the @STdrp when I choose something there it returns those results, but everything else as well. How can I just get back what was passed as @STdrp?Im sure the way I wrote this is pretty messy, my apologies Im not as good on the back end as I am the front end. :)[code="sql"] WHERE ( ct.AlertedDate between @startdate and @enddate OR ct.[Dispo] = @STdrp OR ct.[AcceptStatus] = @STdrp --OR -- @STdrp = '%' ) AND isnull(QC.Class,'BB') like @class AND @startdate <= ct.AlertedDate ORDER BY ct.[AlertedDate][/code]Thanks for your advice.

Read-Commited Snaphsot isolation causing deadlocks?

Posted: 18 Sep 2013 12:36 AM PDT

Our DBA has some type of process setup to monitor deadlocks and sends out an emails when a deadlocks occurs. We have been testing read committed snapshot isolation on our application for a couple months because the performance problems we have been having are definitely related to locking. Everything went very smoothly on our dev & test servers but when we went to apply it to our production database this morning we got about 1300 deadlock graph emails very quickly. Here is the contents of one of the emails:<EVENT_INSTANCE><EventType>DEADLOCK_GRAPH</EventType><PostTime>2013-09-18T06:59:19.993</PostTime><SPID>19</SPID><TextData><deadlock-list><deadlock victim="process6a1288"><process-list><process id="process75e748" taskpriority="0" logused="10000" waitresource="DATABASE: 8 " waittime="200" schedulerid="4" kpid="1984" status="background" spid="20" sbid="0" ecid="0" priority="0" trancount="0"> <executionStack/> <inputbuf/> </process> <process id="processae94c8" taskpriority="0" logused="10000" waitresource="DATABASE: 8 " waittime="301" schedulerid="5" kpid="7244" status="background" spid="34" sbid="0" ecid="0" priority="0" trancount="0"> <executionStack/> <inputbuf/> </process> <process id="processb1db88" taskpriority="0" logused="10000" waitresource="DATABASE: 8 " waittime="99" schedulerid="7" kpid="6428" status="background" spid="35" sbid="0" ecid="0" priority="0" trancount="0"> <executionStack/> <inputbuf/> </process> <process id="process6a1288" taskpriority="0" logused="10000" waitresource="DATABASE: 8 " waittime="402" schedulerid="1" kpid="912" status="background" spid="22" sbid="0" ecid="0" priority="0" trancount="0"> <executionStack/> <inputbuf/> </process> </process-list> <resource-list> <databaselock subresource="FULL" dbid="8" dbname="unknown" id="lock12137cf80" mode="U"> <owner-list> <owner id="process6a1288" mode="S"/> </owner-list> <waiter-list> <waiter id="process75e748" mode="X" requestType="wait"/> </waiter-list> </databaselock> <databaselock subresource="FULL" dbid="8" dbname="unknown" id="lock12137cf80" mode="U"> <owner-list> <owner id="process6a1288" mode="S"/> </owner-list> <waiter-list> <waiter id="processae94c8" mode="X" requestType="wait"/> </waiter-list> </databaselock> <databaselock subresource="FULL" dbid="8" dbname="unknown" id="lock12137cf80" mode="U"> <owner-list> <owner id="process6a1288" mode="S"/> </owner-list> <waiter-list> <waiter id="processb1db88" mode="X" requestType="wait"/> </waiter-list> </databaselock> <databaselock subresource="FULL" dbid="8" dbname="unknown" id="lock12137cf80" mode="U"> <owner-list> <owner id="processae94c8" mode="S"/> </owner-list> <waiter-list> <waiter id="process6a1288" mode="X" requestType="wait"/> </waiter-list> </databaselock> </resource-list> </deadlock> </deadlock-list> </TextData> <TransactionID/> <LoginName>sa</LoginName> <StartTime>2013-09-18T06:59:19.993</StartTime><ServerName>PROD-SQL01</ServerName><LoginSid>AQ==</LoginSid><EventSequence>462976693</EventSequence><IsSystem>1</IsSystem><SessionLoginName/></EVENT_INSTANCE>Anyone have any idea what could be going on here? We applied this early in the morning when we have very little database traffic. FYI: We do use idera software for auditing though.

How to port no on Sql server

Posted: 17 Sep 2013 07:59 PM PDT

Can any one know where to find port id in sql server.I am new to sql server dba ...please help.Thanks & Regardsshiva
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog