April 1, 2008 at 9:31 pm
I am having troubles trying to copy some rows from a table on my local computer to a table on a remote SQL Server 2005 that is being hosted by one of thos web hosting companies. The problem is that the table has an identity column. I first tried using the the following command:
SET IDENTITY_INSERT [remoteservername].Library2005.dbo.tblLanguages ON
but that results in the error:
Msg 8103, Level 16, State 1, Line 1
Table 'remoteservername.Library2005.dbo.tblLanguages' does not exist or cannot be opened for SET operation.
I read on some other sites, that I should change this into the following:
EXECUTE [remoteservername].Library2005.dbo.sp_executesql N'SET IDENTITY_INSERT dbo.tblLanguages ON'
That command executes without error, but the problem is that I cannot perform the actual insert, because it is not within the execute statement. In other words, the following doesn't work:
EXECUTE [remoteservername].Library2005.dbo.sp_executesql N'SET IDENTITY_INSERT dbo.tblLanguages ON'
INSERT INTO [remoteservername].Library2005.dbo.tblLanguages
(colLangID, colEnglish, colGerman, colSpanish)
SELECT colLangID, colEnglish, colGerman, colSpanish FROM tblLanguages
This results in the error:
Msg 7344, Level 16, State 1, Line 2
OLE DB provider 'SQLOLEDB' could not INSERT INTO table '[remoteservername].[Library2005].[dbo].[tblLanguages]' because of column 'colLangID'. The user did not have permission to write to the column.
The remote server is linked correctly on my end via the sp_addlinkedserver and sp_addlinkedsrvlogin. Is there any way to force the remote server to turn IDENTITY_INSERT ON permanently and then let me execute as many INSERTS as I want and then turn it back OFF?
April 4, 2008 at 8:28 pm
Is this particular table only populated by copying rows from you local server to the remote server? If this is the case I would alter the table on the remote server an make the ID field a plain int without identity.
ANother option is to create a new table on the remote server without an identity column and insert your rows into that. Then create a stored procedure on the remote server that you can call that copies the rows into the existing table. Within the sp you can execute the Set Identity_Insert On statement and you will be fine. Set Identity_Insert is a session level setting so you can't set it across the linked server because each statement across the linked server is a new connection/session.
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
April 4, 2008 at 9:01 pm
If the table has an identity field with seed, why are you trying to insert a value for it? I do not see why you need to insert the id, unless you need the numbers to be exactly what you have in your table.
If you can exclude the id from the insert, your statement should look like this.
INSERT INTO [remoteservername].Library2005.dbo.tblLanguages
(colEnglish, colGerman, colSpanish)
SELECT colEnglish, colGerman, colSpanish FROM tblLanguages
April 5, 2008 at 12:11 pm
I don't believe that you can use the SET IDENTITY_INSERT outside of the database you're in. Sounds to me that you'd have to do all of this from within an OPENQUERY call that executes on the remote server in the context of the database you want to do that in.
As in something like:
select * from OPENQUERY([remoteservername],'use Library2005;
Set IDENTITY_INSERT tblLanguages ON;
Insert tblLanguages (etc) select etc from whatever;
Set IDENTITY_INSERT tblLanguages OFF; select 1'
) OP
FYI - the smiley is a closed parenthesis...
----------------------------------------------------------------------------------
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?
April 5, 2008 at 12:47 pm
Adam Haines (4/4/2008)
If the table has an identity field with seed, why are you trying to insert a value for it? I do not see why you need to insert the id, unless you need the numbers to be exactly what you have in your table.If you can exclude the id from the insert, your statement should look like this.
INSERT INTO [remoteservername].Library2005.dbo.tblLanguages
(colEnglish, colGerman, colSpanish)
SELECT colEnglish, colGerman, colSpanish FROM tblLanguages
I actually agree with Adam. The best solution is to not worry about inserting into the Identity field unless absolutely necessary.
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
April 5, 2008 at 1:36 pm
Jack Corbett (4/5/2008)
Adam Haines (4/4/2008)
If the table has an identity field with seed, why are you trying to insert a value for it? I do not see why you need to insert the id, unless you need the numbers to be exactly what you have in your table.If you can exclude the id from the insert, your statement should look like this.
INSERT INTO [remoteservername].Library2005.dbo.tblLanguages
(colEnglish, colGerman, colSpanish)
SELECT colEnglish, colGerman, colSpanish FROM tblLanguages
I actually agree with Adam. The best solution is to not worry about inserting into the Identity field unless absolutely necessary.
I'll "third" that motion. Avoid identity_insert if you can.
If you're talking to a 2005 remote server, perhaps look at leveraging the new OUTPUT clause which in this case would allow you to get back what the identity values were set to (instead of you trying to force them in).
----------------------------------------------------------------------------------
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?
October 2, 2017 at 2:32 pm
Matt Miller (4) - Saturday, April 5, 2008 1:36 PMJack Corbett (4/5/2008)
Adam Haines (4/4/2008)
If the table has an identity field with seed, why are you trying to insert a value for it? I do not see why you need to insert the id, unless you need the numbers to be exactly what you have in your table. If you can exclude the id from the insert, your statement should look like this.INSERT INTO [remoteservername].Library2005.dbo.tblLanguages(colEnglish, colGerman, colSpanish)SELECT colEnglish, colGerman, colSpanish FROM tblLanguagesI actually agree with Adam. The best solution is to not worry about inserting into the Identity field unless absolutely necessary.
I'll "third" that motion. Avoid identity_insert if you can.If you're talking to a 2005 remote server, perhaps look at leveraging the new OUTPUT clause which in this case would allow you to get back what the identity values were set to (instead of you trying to force them in).
Thanks for not answering this. I have a corporate firewall where they set the connection up unidirectionally from the old server to the new one instead of the other way around so I can't copy multiple tables from a script without having to go through the export wizard and specifying the criteria. What I need to do is script the tables using criteria by using SYS.OBJECTS to create the SQL that will export out the data I need.
And their response is like yours where it's "you don't need to do that." That wasn't the question. I welcome any of you to have to live by the rules you set on the corporate side. 🙂
I would suggest that you do what I ended up having to do..... Create a table on the old database server in a new blank database by scripting the tables or using SELECT INTO. From there, use the Export Data option in SSMS and set identity insert with the connection to both machines in source and destination servers when you do that.
Lauren
October 2, 2017 at 4:44 pm
lglenn1973 - Monday, October 2, 2017 2:32 PMMatt Miller (4) - Saturday, April 5, 2008 1:36 PMJack Corbett (4/5/2008)
Adam Haines (4/4/2008)
If the table has an identity field with seed, why are you trying to insert a value for it? I do not see why you need to insert the id, unless you need the numbers to be exactly what you have in your table. If you can exclude the id from the insert, your statement should look like this.INSERT INTO [remoteservername].Library2005.dbo.tblLanguages(colEnglish, colGerman, colSpanish)SELECT colEnglish, colGerman, colSpanish FROM tblLanguagesI actually agree with Adam. The best solution is to not worry about inserting into the Identity field unless absolutely necessary.
I'll "third" that motion. Avoid identity_insert if you can.If you're talking to a 2005 remote server, perhaps look at leveraging the new OUTPUT clause which in this case would allow you to get back what the identity values were set to (instead of you trying to force them in).
Thanks for not answering this. I have a corporate firewall where they set the connection up unidirectionally from the old server to the new one instead of the other way around so I can't copy multiple tables from a script without having to go through the export wizard and specifying the criteria. What I need to do is script the tables using criteria by using SYS.OBJECTS to create the SQL that will export out the data I need.
And their response is like yours where it's "you don't need to do that." That wasn't the question. I welcome any of you to have to live by the rules you set on the corporate side. 🙂
I would suggest that you do what I ended up having to do..... Create a table on the old database server in a new blank database by scripting the tables or using SELECT INTO. From there, use the Export Data option in SSMS and set identity insert with the connection to both machines in source and destination servers when you do that.
Lauren
Grats on reviving a 9 year old topic. Even Micheal Jackson was around when that post was made. O.o
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 2, 2017 at 9:01 pm
Thom A - Monday, October 2, 2017 4:44 PMlglenn1973 - Monday, October 2, 2017 2:32 PMMatt Miller (4) - Saturday, April 5, 2008 1:36 PMJack Corbett (4/5/2008)
Adam Haines (4/4/2008)
If the table has an identity field with seed, why are you trying to insert a value for it? I do not see why you need to insert the id, unless you need the numbers to be exactly what you have in your table. If you can exclude the id from the insert, your statement should look like this.INSERT INTO [remoteservername].Library2005.dbo.tblLanguages(colEnglish, colGerman, colSpanish)SELECT colEnglish, colGerman, colSpanish FROM tblLanguagesI actually agree with Adam. The best solution is to not worry about inserting into the Identity field unless absolutely necessary.
I'll "third" that motion. Avoid identity_insert if you can.If you're talking to a 2005 remote server, perhaps look at leveraging the new OUTPUT clause which in this case would allow you to get back what the identity values were set to (instead of you trying to force them in).
Thanks for not answering this. I have a corporate firewall where they set the connection up unidirectionally from the old server to the new one instead of the other way around so I can't copy multiple tables from a script without having to go through the export wizard and specifying the criteria. What I need to do is script the tables using criteria by using SYS.OBJECTS to create the SQL that will export out the data I need.
And their response is like yours where it's "you don't need to do that." That wasn't the question. I welcome any of you to have to live by the rules you set on the corporate side. 🙂
I would suggest that you do what I ended up having to do..... Create a table on the old database server in a new blank database by scripting the tables or using SELECT INTO. From there, use the Export Data option in SSMS and set identity insert with the connection to both machines in source and destination servers when you do that.
Lauren
Grats on reviving a 9 year old topic. Even Micheal Jackson was around when that post was made. O.o
I jumped on it because I was trying to find an actual usable answer to something that I ran into but turns out that I ran into the same kind of hassle on this site (which is usually good for tips) that I did from the people that put me in this situation.
Lauren
October 3, 2017 at 5:00 pm
Jack Corbett - Friday, April 4, 2008 8:28 PMIs this particular table only populated by copying rows from you local server to the remote server? If this is the case I would alter the table on the remote server an make the ID field a plain int without identity.ANother option is to create a new table on the remote server without an identity column and insert your rows into that. Then create a stored procedure on the remote server that you can call that copies the rows into the existing table. Within the sp you can execute the Set Identity_Insert On statement and you will be fine. Set Identity_Insert is a session level setting so you can't set it across the linked server because each statement across the linked server is a new connection/session.
Lauren,
I think the post quoted above does give a couple of options, they may not be the best options, but they were options.
-Jack
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply