Cross Server Table Data transfer

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)

Cross Server Table Data transfer

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2010-04-05 : 05:03:59

Dear All,

ANyone here know How to select * from TableA (ServerA) into TableA (Server B)

Please advise.

Thank you.

Regards,
Micheale

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-04-05 : 05:08:02

do u have any linked server between the two servers
if not get the insert statement scripts and run it on required server.

micnie_2020
Posting Yak Master

232 Posts

Posted - 2010-04-05 : 05:09:18

Dear bklr,

Can i have the script?

Thank you.

Regards,
Micheale

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 05:31:08

quote:
Originally posted by micnie_2020

Dear bklr,

Can i have the script?

Thank you.

Regards,
Micheale

You can use openrowset
if you dont ahve linked server connection


SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=yourservernamehere;Trusted_Connection=yes;',
     'SELECT fields...      FROM dbname.schema.table') AS a;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 05:32:35

and you need to enable adhoc distributed queries before running OPENROWSET if you've not done it yet

http://www.kodyaz.com/articles/enable-Ad-Hoc-Distributed-Queries.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-05 : 05:48:54

Or you can do with linked server

EXEC sp_addlinkedserver '<servername>', '<provider>'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'<servername>',@useself=N'False',@locallogin=NULL,@rmtuser=N'loginname',@rmtpassword='password'

select * into <newtablename > from openquery([<servername>], 'select * from dbname..yourtable')

Vaibhav T

micnie_2020
Posting Yak Master

232 Posts

Posted - 2010-04-05 : 05:51:55

Dear All,

Thank you for the asap reply. I will look into it now.

I have try below solution, but getting error:

sp_configure 'show advanced options', 1
reconfigure

declare @x int
SELECT @x = a.CountVal
FROM OPENROWSET('SQLNCLI', 'SERVERNAME';'USERNAME';'PWD',
'select count(*) AS CountVal
FROM SUPB_LVGITL.dbo.TBEMPLOYEE') AS a
select @x

sp_configure 'Ad Hoc Distributed Queries', 1

ERROR:
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].

Please Advise.

Thank you.

Regards,
Micheale

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 06:26:58

quote:
Originally posted by micnie_2020

Dear All,

Thank you for the asap reply. I will look into it now.

I have try below solution, but getting error:

sp_configure 'show advanced options', 1
reconfigure

declare @x int
SELECT @x = a.CountVal
FROM OPENROWSET('SQLNCLI', 'SERVERNAME';'USERNAME';'PWD',
'select count(*) AS CountVal
FROM SUPB_LVGITL.dbo.TBEMPLOYEE') AS a
select @x

sp_configure 'Ad Hoc Distributed Queries', 1

ERROR:
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].

Please Advise.

Thank you.

Regards,
Micheale

use it as suggested please. See how I've passed connectionstring info earlier

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

micnie_2020
Posting Yak Master

232 Posts

Posted - 2010-04-05 : 10:04:57

Dear All,

I have created

EXEC sp_addlinkedserver 'servername','SQL server'
EXEC sp_addlinkedsrvlogin 'servername', 'false', NULL, 'username','password'

SELECT * FROM servername.databasename.dbo.tablename

I still getting error:-

OLE DB provider "SQLNCLI10" for linked server "GANOEXCEL" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "GANOEXCEL" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].

Please Advise

Thank you

Regards,
Micheale

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 11:22:05

did you try like?


declare @x int
SELECT @x = a.CountVal
FROM OPENROWSET('SQLNCLI', 'Server= SERVERNAME;User ID=USERNAME;Password= PWD;Trusted_Connection=False;',
'select count(*) AS CountVal
FROM SUPB_LVGITL.dbo.TBEMPLOYEE') AS a
select @x

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

micnie_2020
Posting Yak Master

232 Posts

Posted - 2010-04-06 : 02:30:33

Dear All,

Thank you.

I have successful done the process by using:-

A) MS SQL 2008

sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

INSERT INTO OPENROWSET('SQLOLEDB',
'DRIVER={SQL Server};SERVER=ServerName;UID=UN;PWD=Pass',
databasename.dbo.tblname)
SELECT MEMBERCODE,GIVENNAME,DATEJOIN,COUNTRYLOOKUPID
FROM MEMBERSHIPS MS
INNER JOIN MEMBERS M ON M.MEMBERID=MS.MEMBERID
INNER JOIN MEMBERADDRESSES MAB ON MAB.MEMBERID=M.MEMBERID
INNER JOIN ADDRESSBOOK AB ON AB.ADDRESSID=MAB.ADDRESSID

B) MS SQL 2000

INSERT INTO OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=ServerName;UID=UN;PWD=Pass',
DatabaseName.dbo.Tblname)
SELECT MEMBERCODE,GIVENNAME,DATEJOIN,COUNTRYLOOKUPID
FROM MEMBERSHIPS MS
INNER JOIN MEMBERS M ON M.MEMBERID=MS.MEMBERID
INNER JOIN MEMBERADDRESSES MAB ON MAB.MEMBERID=M.MEMBERID
INNER JOIN ADDRESSBOOK AB ON AB.ADDRESSID=MAB.ADDRESSID

I Didn't add in LINKED SERVER.
IT'S WORK FINE.

Thank you for all effort, really appreciate it so much.
Thank you SQLTeam.com.

Regards,
Micheale

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 05:20:29

Cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-06 : 05:30:48

quote:
Originally posted by micnie_2020

Dear All,

ANyone here know How to select * from TableA (ServerA) into TableA (Server B)

Please advise.

Thank you.

Regards,
Micheale

Another option is export data to text file using bcp
Import data to other server using Bulk insert

Madhivanan

Failing to plan is Planning to fail

micnie_2020
Posting Yak Master

232 Posts

Posted - 2010-04-07 : 23:20:21

Dear All,

I have used this method and it's work fine for me.
But i encounter 1 problem is, how am i going to set insert into with columns name?

Please Advise.

My Code:
--------

INSERT INTO OPENROWSET('SQLOLEDB',
'DRIVER={SQL Server};SERVER=SrverName;UID=UN;PWD=Pass',
DBName.dbo.TblName)
SELECT MEMBERCODE,GIVENNAME,DATEJOIN,COUNTRYLOOKUPID,'MY'
FROM MEMBERSHIPS MS
INNER JOIN MEMBERS M ON M.MEMBERID=MS.MEMBERID
INNER JOIN MEMBERADDRESSES MAB ON MAB.MEMBERID=M.MEMBERID
INNER JOIN ADDRESSBOOK AB ON AB.ADDRESSID=MAB.ADDRESSID

something like
INSERT INTO TblName(MEMBERCODE,GIVENNAME,DATEJOIN,COUNTRYLOOKUPID,SOURCES)
select MEMBERCODE,GIVENNAME,DATEJOIN,COUNTRYLOOKUPID,'MY'
:
:

Thank you.

Regards,
Micheale

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 01:09:19

try


INSERT INTO OPENROWSET('SQLOLEDB',
'DRIVER={SQL Server};SERVER=SrverName;UID=UN;PWD=Pass',
DBName.dbo.TblName)
SELECT MEMBERCODE,GIVENNAME,DATEJOIN,COUNTRYLOOKUPID,'MY' AS SOURCES
FROM MEMBERSHIPS MS
INNER JOIN MEMBERS M ON M.MEMBERID=MS.MEMBERID
INNER JOIN MEMBERADDRESSES MAB ON MAB.MEMBERID=M.MEMBERID
INNER JOIN ADDRESSBOOK AB ON AB.ADDRESSID=MAB.ADDRESSID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

micnie_2020
Posting Yak Master

232 Posts

Posted - 2010-04-08 : 01:31:41

Dear All,

Thank you once again. Sorry for unclear subject.

I finally get the solution.

Here is the solution:
---------------------
INSERT INTO OPENROWSET('SQLOLEDB',
'DRIVER={SQL Server};SERVER=SrverName;UID=UN;PWD=Pass',
DBName.dbo.TblName)
(Code,Name,JoinDate,Countryid,sources)
SELECT MEMBERCODE,GIVENNAME,DATEJOIN,COUNTRYLOOKUPID,'MY'
FROM MEMBERSHIPS MS
INNER JOIN MEMBERS M ON M.MEMBERID=MS.MEMBERID
INNER JOIN MEMBERADDRESSES MAB ON MAB.MEMBERID=M.MEMBERID
INNER JOIN ADDRESSBOOK AB ON AB.ADDRESSID=MAB.ADDRESSID

Thank you very much.

Regards,
Micheale

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources