copying data from one server to another

[Guineapig1980]

Hi all

I am trying to copy data from one database server to another. I only
want to copy one table's data, not the entire database. The part that
I am having trouble with is connecting from one database server and
connect to another then telling it to insert into the second database
server.

Not sure if this is how it works.

any help is appreciated.
thanks
Harold

[Plamen Ratchev]

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 Ratchev
http://www.SQLStudio.com

[imorxr]

On Apr 22, 8:49 pm, "Plamen Ratchev" <[e-mail]:

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?

[Plamen Ratchev]

If you can set up a linked server the other way around (from your remote
server to the local server), then executing the query on the remote server
will have no problems setting IDENTITY_INSERT ON. The option cannot be set
permanently and it has to be set in the same session as the INSERT
statement.

Alternative is to use BCP or Bulk Insert to insert the data which have
option to keep identity.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[imorxr]

i have 2 server Server A and Server B.
On Server A, I have table Stock :

CREATE TABLE [dbo].[Stock] (
[Stock_Id] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]

On Server B, I have table Stock too:
CREATE TABLE [dbo].[Stock] (
[Stock_Id] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]

i want copy from server A to Server B, using this :

sp_dropserver 'mylink'
go

sp_addlinkedserver 'mylink', '', 'SQLOLEDB', NULL, NULL, 'DRIVER={SQL
Server};SERVER=B02;UID=sa;PWD=QWERTY;'
go

exec sp_serveroption @server='mylink', @optname='rpc out',
@optvalue='true'
go

EXECUTE [mylink].[serverB].[dbo].[sp_executesql] N'SET IDENTITY_INSERT
dbo.Stock ON
INSERT INTO Tr_Stock (Stock_Id) SELECT Stock_Id FROM ( ???? i have
active in remote server now ??? )Stock
SET IDENTITY_INSERT dbo.Stock OFF'
but i Still get error.

I think, the problem is when using : EXECUTE [mylink].[serverB].[dbo].
[sp_executesql] . How to execute insert ?

[imorxr]

On May 15, 1:18 pm, [e-mail] wrote:

i have 2 server Server A and Server B.
On Server A, I have table Stock :

CREATE TABLE [dbo].[Stock] (
[Stock_Id] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]

On Server B, I have table Stock too:
CREATE TABLE [dbo].[Stock] (
[Stock_Id] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]

i want copy from server A to Server B, using this :

sp_dropserver 'mylink'
go

sp_addlinkedserver 'mylink', '', 'SQLOLEDB', NULL, NULL, 'DRIVER={SQL
Server};SERVER=B02;UID=sa;PWD=QWERTY;'
go

exec sp_serveroption @server='mylink', @optname='rpc out',
@optvalue='true'
go

EXECUTE [mylink].[serverB].[dbo].[sp_executesql] N'SET IDENTITY_INSERT
dbo.Stock ON
INSERT INTO Tr_Stock (Stock_Id) SELECT Stock_Id FROM ( ???? i have
active in remote server now ??? )Stock
SET IDENTITY_INSERT dbo.Stock OFF'

but i Still get error.

I think, the problem is when using : EXECUTE [mylink].[serverB].[dbo].
[sp_executesql] . How to execute insert ?

Sory, i have to remote from local server to remote
server, so i must using set identity on, before execute insert
command.

regards

[imorxr]

On May 15, 12:40 pm, "Plamen Ratchev" <[e-mail]:

If you can set up a linked server the other way around (from your remote
server to the local server), then executing the query on the remote server
will have no problems setting IDENTITY_INSERT ON. The option cannot be set
permanently and it has to be set in the same session as the INSERT
statement.

Alternative is to use BCP or Bulk Insert to insert the data which have
option to keep identity.

HTH,

Plamen Ratchevhttp://www.SQLStudio.com

Dear Mr Plamen,

I can not set up a linked server the other way around (from your
remote
server to the local server).

Now, i want try using Bulk Insert the data which have option to keep
identity, but
my problem is, i can't access data from local server cause i have
ready come in to
remote server.

before, i just want execute :
INSERT INTO mylink.serverB.dbo.Tr_Stock (Stock_Id) SELECT Stock_Id
FROM Stock

coz i just 1 option to execute identity_insert using :
EXECUTE [mylink].[serverB].[dbo].[sp_executesql] N'SET IDENTITY_INSERT
dbo.Stock ON
, so now i cant using this again : INSERT INTO
mylink.serverB.dbo.Tr_Stock (Stock_Id) SELECT Stock_Id FROM Stock

Regards,
Thanks

[Plamen Ratchev]

One way to work around the issue is to create a staging table on the remote
server. The table will not have IDENTITY column, so you can transfer the
data. Then have a stored procedure to transfer between the staging table and
the production table. Inside the stored procedure you can set
IDENTITY_INSERT ON and complete the insert.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[imorxr]

On May 15, 8:01 pm, "Plamen Ratchev" <[e-mail]:

One way to work around the issue is to create a staging table on the remote
server. The table will not have IDENTITY column, so you can transfer the
data. Then have a stored procedure to transfer between the staging table and
the production table. Inside the stored procedure you can set
IDENTITY_INSERT ON and complete the insert.

HTH,

Plamen Ratchevhttp://www.SQLStudio.com

Ok, thanks for all. Regards.

[Erland Sommarskog]

(im****@gmail.com) writes:

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'

This is because the effect of a SET command is reverted when the
scope in which the SET command is executed exists. You would
need to have the SET command and the INSERT statement in the
same batch.

Overall, this is a good example how IDENTITY can buy you a load of
troubles. Had you rolled your own, you would have copied the data
long ago.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx