move data to another server temp table

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 2000 Forums

SQL Server Development (2000)

move data to another server temp table

Author

Topic

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-05-23 : 06:07:05

i have 2 servers ( A,B) i pull the data from B to A .. without linked server need to pull and insert in to temp table of A server.

instead of linked server we can use open data source .. but not sure about this ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 07:39:42

You can use OPENROWSET also.

http://doc.ddart.net/mssql/sql70/oa-oz_5.htm

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-05-23 : 07:53:08

ok fine .. by using OPENROWSET we can retrieve .. but in same openrowset statement can we use insert into teamptable statement

SELECT insert into #temp
FROM OPENROWSET('MSDASQL',

'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',

pubs.dbo.authors) AS a

ORDER BY a.au_lname, a.au_fname

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 08:22:34

quote:
Originally posted by niranjankumark

ok fine .. by using OPENROWSET we can retrieve .. but in same openrowset statement can we use insert into teamptable statement

insert into #temp
SELECT * ~~insert into #temp~~FROM OPENROWSET('MSDASQL',

'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',

pubs.dbo.authors) AS a

ORDER BY a.au_lname, a.au_fname

You can

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-05-23 : 08:29:13

OK .. in same scenario i dont have sa id .. only windows authentication .. so i written qry like here

SELECT *
FROM OPENROWSET
('SQLOLEDB','Server=tld2mis1;TRUSTED_CONNECTION=YES;','select * from northwind.dbo.qa')
but it says access denied ...

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-23 : 22:24:05

Did you create sql login for your windows account on remote server?

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-05-24 : 04:34:42

ss i created .. and tried login in windows authentication

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-24 : 14:00:40

May need try with sql authentication.