Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
smithygreg
Starting Member
37 Posts |
 Posted - 2008-02-26 : 13:51:32
|
Hey there..I need to insert some data into a linked server where I need to insert the Identity field. When I try to turn IDENTITY INSERT on, I get this errorThe object name 'Server-SQL.MyDatabase.dbo.MyTable' contains more than the maximum number of prefixes. The maximum is 2.The line I try to execute is this...SET IDENTITY_INSERT [Server-SQL].MyDatabase.dbo.MyTable ON From my searching around about this error, the workaround seems to be aliasing the table name, but I can't really see how to use an alias in this situation.Thanks a bunch!Greg |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-26 : 14:25:08
|
| you dont ned table name here just useSET IDENTITY_INSERT ON before you execute the rest of the code |
 |
|
|
smithygreg
Starting Member
37 Posts |
Posted - 2008-02-26 : 14:28:44
|
| That didn't work..I changed it to SET IDENTITY_INSERT ONand I get this error now..."Incorrect syntax near the keyword 'ON'."Thanks though! Any other Ideas!? |
|
|
|
jhocutt
Constraint Violating Yak Guru
385 Posts |
Posted - 2008-02-26 : 14:37:54
|
| exec [Server-SQL].MyDatabase.dbo.sp_executesql N'set identity_insert MyDatabase.dbo.MyTable on ;insert into [Server-SQL].MyDatabase.dbo.MyTable (identity_clumn, other_columns, ... ) values (1, ''test'', ...) ;set identity_insert MyDatabase.dbo.MyTableoff ;'"God does not play dice" -- Albert Einstein"Not only does God play dice, but he sometimes throws them where they cannot be seen." -- Stephen Hawking |
|
|
|
smithygreg
Starting Member
37 Posts |
Posted - 2008-02-26 : 14:47:09
|
| Awesome! Thanks!I had to turn RPC on, but then it worked great! |
|
|
|
|
|
|
Identity Insert on a linked server