Inserting rows into remote server with identity

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Inserting rows into remote server with identity

Author

Topic

vgehts
Starting Member

1 Post

Posted - 2008-04-02 : 00:19:34

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 in another topic, 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?

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-02 : 02:16:35

The sp_executesql statement will be a seperate batch so the identity_insert will be lost when it finishes.
to do this you would need to execute it all in the same batch - to do it as you are trying it would mean having a linked server on the remote machine so you could do the insedrt in the dynamic sql.

Another option is to create a global temp table on the remot system, populate it then do the insert from that.

Probably the easiest is to do it in single insert statements

declare @colLangID int, @colEnglish varchar(20), ...
select @colLangID = 0
while @colLangID < (select max(colLangID ) from tblLanguages
begin
select @colLangID = colLangID, @colEnglish = colEnglish, ...
from (select top 1 * from tblLanguages where colLangID > @colLangID order by colLangID) a

declare @sql nvarchar(4000)
select @sql = insert tblLanguages (colLangID, colEnglish, ...) select ' + convert(varchar(20), @colLangID) + ', ' + case when @colEnglish is null then 'null' else '''' + @colEnglish + ''',' + ...

select @sql = 'SET IDENTITY_INSERT dbo.tblLanguages ON ' + @sql + ' SET IDENTITY_INSERT dbo.tblLanguages OFF'
EXECUTE [remoteservername].Library2005.dbo.sp_executesql @sql

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hari_
Starting Member

1 Post

Posted - 2008-05-15 : 05:00:25

but how to access record from local server ? i need access to local server to get value, to be inserted to remote server.
when using this :

select @sql = 'SET IDENTITY_INSERT dbo.tblLanguages ON ' + @sql + ' SET IDENTITY_INSERT dbo.tblLanguages OFF'
EXECUTE [remoteservername].Library2005.dbo.sp_executesql @sql

i can not access local server again.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources