Subscribe to this blog
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…
"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…
0 comments, 190 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,
For example,
--Script 1 create table dbo.fktable1( id1 int not null , id2 int not null , id3 int…
0 comments, 169 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…
0 comments, 127 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.
What if the table has a compound primary key? See comments for a play-by-play.
drop table dbo.testclusteredinclude
go
create table dbo
0 comments, 136 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:
From my colleague:
Why would you ever need…
1 comments, 155 reads
Posted in SQL Tact on 4 February 2013