Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Archives: February 2013

"An item with the same key has already been added."

In SQL Server Reporting Services, when adding a new dataset to a report, you may see an error that looks like this:

"An item with the same key has already been added."




















Based on some quick googling, it looks like there are several potential causes for the error, but here's…

Read more

0 comments, 810 reads

Posted in SQL Tact on 28 February 2013

Script Out Foreign Keys With Multiple Keys

It's easy enough to use sys.foreign_keys and sys.foreign_key_columns to identify foreign keys.  But what if you want to script out your foreign keys (and only your foreign keys)... that have compound primary keys?

For example,
--Script 1
create table dbo.fktable1(
  id1 int not null
, id2 int not null
, id3 int…

Read more

0 comments, 433 reads

Posted in SQL Tact on 18 February 2013

Using Foreign Keys to Determine Table Insertion Order

Here's a script to determine, based on your database's foreign key relationships, what the insertion order would be for, say, a testing dataset.

with cteFK (pktable, fktable) as ( 
       select              
            pktable = o1.name 
       ,    fktable = isnull(o2.name, '')        
       from sysobjects o1       
       left join sysforeignkeys fk on o1.id = fk.fkeyid        
       left…

Read more

3 comments, 485 reads

Posted in SQL Tact on 17 February 2013

Adding the Clustered Key To Your Nonclustered Index? Part 2

Of course, a rather obvious answer presents itself the next day.  After reviewing the actual environment that my colleague was working in, it popped quickly into mind.

What if the table has a compound primary key?  See comments for a play-by-play.

drop table dbo.testclusteredinclude
go
create table dbo

Read more

0 comments, 245 reads

Posted in SQL Tact on 5 February 2013

Adding the Clustered Key To Your Nonclustered Index?

A colleague of mine was understandably confused when using the almost-perfect missing index engine in SQL 2008. The engine recommended he include the clustered index key in a new nonclustered index.  Read that again - you're right, it doesn't make a lot of sense.

From my colleague:
Why would you ever need…

Read more

1 comments, 275 reads

Posted in SQL Tact on 4 February 2013