November 8, 2013 at 9:17 am
Hi,
Im trying to set idenity insert on on a table, however i cannot do this using the db server and database name as parameters.
declare @DestDB varchar(100)
declare @DestServer nvarchar(30)
declare @sqlstring nvarchar(max)
set @destdb = 'Databasename'
set @destserver = 'servername'
set @sqlstring = 'set identity_insert [' + @DestServer + '].[' + @DestDB + '].dbo.rdm_anlsregions on'
exec @sqlstring
print @sqlstring
When i print the statement it looks fine but i get this error.
The name 'set identity_insert [servername].[databasename].dbo.rdm_anlsregions on' is not a valid identifier
If i connect to the instance without using linked server, this works fine, as below.
set identity_insert [databsename].dbo.rdm_anlsregions on
November 8, 2013 at 9:33 am
jermaine.grant (11/8/2013)
Hi,Im trying to set idenity insert on on a table, however i cannot do this using the db server and database name as parameters.
declare @DestDB varchar(100)
declare @DestServer nvarchar(30)
declare @sqlstring nvarchar(max)
set @destdb = 'Databasename'
set @destserver = 'servername'
set @sqlstring = 'set identity_insert [' + @DestServer + '].[' + @DestDB + '].dbo.rdm_anlsregions on'
exec @sqlstring
print @sqlstring
When i print the statement it looks fine but i get this error.
The name 'set identity_insert [servername].[databasename].dbo.rdm_anlsregions on' is not a valid identifier
If i connect to the instance without using linked server, this works fine, as below.
set identity_insert [databsename].dbo.rdm_anlsregions on
Even if it did work it probably is not really what you are wanting. Any set statements execute via dynamic sql will revert to the previous setting at the end of execution.
http://technet.microsoft.com/en-us/library/ms190356.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 8, 2013 at 9:35 am
Thanks for getting back. I have a insert command which will follow within the same statement, so does this still apply?
Jermaine
November 8, 2013 at 9:46 am
jermaine.grant (11/8/2013)
Thanks for getting back. I have a insert command which will follow within the same statement, so does this still apply?Jermaine
I don't think your set commands will work properly on a link server, especially when using dynamic sql. I would instead create a proc on the remote server and execute it remotely. Then inside your proc you can set IDENTITY_INSERT and then back off at the end.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 8, 2013 at 10:08 am
I knew there would be limitations with sql, the problem is that i have over 400 DB servers so the users will use the para on the stored prodecure to select which DB server and which DB they want to do this on. ok thanks, maybe i'll try get around this with powershell, somehow.
November 8, 2013 at 8:12 pm
If linked server doesnt work, then try pass through batch via EXEC() AT <SERVER>.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 9, 2013 at 8:03 am
openQuery() should also allow you to push the execution onto the remote server (which would be registered as a linked server). This does then become a rather large dynamic SQL statement (openquery doesn't allow a dynamic query to be passed in as a variable, so the openquery itself then becomes part of the dynamic statement)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 12, 2013 at 2:28 am
I give up on this, I've had developers look at it at work but no one can get around thsi issue.
Jermaine
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply