On Apr 22, 8:49 pm, "Plamen Ratchev" <Pla...@[EMAIL PROTECTED]
> wrote:
> You can add linked server and use it to copy the table data across
server.
> Creating linked server is done using sp_addlinkedserver:
>
> EXEC sp_addlinkedserver 'RemoteServer', N'SQL Server'
>
> Based on security settings you may need to map remote server logins.
This is
> done using sp_addlinkedsrvlogin:
>
> EXEC sp_addlinkedsrvlogin 'RemoteServer', 'false', 'LocalUser',
> 'RemoteUser', 'RemotePassword'
>
> Then you just run a normal query referencing the linked server table
with 4
> part name:
>
> INSERT INTO TargetTable
> SELECT <columns>
> FROM RemoteServer.RemoteDB.dbo.RemoteTable
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com
How if i need to : 'SET IDENTITY_INSERT ON' before execute insert
command ?
i have try it before :
SET IDENTITY_INSERT [remoteservername].Library2005.dbo.tblLanguages ON
--> will result error
[remoteservername].Library2005.dbo.sp_executesql N'SET IDENTITY_INSERT
dbo.tblLanguages ON' --> no error
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?
|
