Re: copying data from one server to another

On Apr 22, 8:49 pm, "Plamen Ratchev" <Pla...@xxxxxxxxxxxxx> 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?
.

Relevant Pages

  • Re: OT: security
    ... this remote file will be included into ... the script and executed. ... Note that the remote server would have to ... Mechanisms such as the above allow attackers to execute any code they ...
    (comp.lang.php)
  • INSERT takes ages...
    ... On a remote server I execute the following ... Now I wish to put the resultset ... Hans Brouwer ...
    (microsoft.public.sqlserver.programming)
  • Re: Send File to Remote Server
    ... It really helps me if i have the code sample on sending the file to ... This file should be sent to the remote server. ... then execute it on the fly. ... You can gain more control, often at the expense of maintainability, by using ...
    (microsoft.public.vb.controls)
  • RE: Access to a remote SQL server through ISA server
    ... Make a network trace on both sides (SQL1) and (SQL2). ... Use osql.exe connect to the remote server and execute sp_tables. ...
    (microsoft.public.sqlserver.security)
  • RE: dbcc against remote sql server
    ... SQLPERFwith a remote server as a linked server. ... If there is anything I misunderstood, ... create a stored procedure on the remote linked server first: ...
    (microsoft.public.sqlserver.programming)