February 25, 2008 at 11:10 am
I have a DB with 3 schema's. They are dbo, current and archive. When tables have the same name but in a different schema e.g. archive.table1, dbo.table1, current.table1 when a transfer sql objects is performed and I only want archive.table1 it transfers data from all three schema's. Therefore creating a 'Cannot insert duplicate key primary key contraint' error. Even though I tell it to only copy from the archive schema. Is this a known bug? Anybody know a way around it. i have run several test and I know the data I'm trying to copy is unique per row I still keep getting the error.
February 25, 2008 at 12:19 pm
I did a little test on my machine and by running profiler, it looks like it is getting the table definition for the table of that name in the user's default schema. Thus, if the user you are connecting with is a sysadmin and in the db_owner role the default schema is "dbo". So, I would call this a bug because you are specifying a specific table in the table collection, but behind the scenes the process is selecting anohter table no matter what you do. A work around is to create a user on the source who only has rights to the schema you want to copy from and use that in your connection.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply