Connecting to another server through T-SQL

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

Transact-SQL (2000)

Connecting to another server through T-SQL

Author

Topic

hnomani
Starting Member

35 Posts

Posted - 2004-12-27 : 17:49:20

I am trying to establish connection from Server_A to Server_B using T-SQL. My process is currently connected to Server_A and I would like to connect/disconnect to Server_B from my T-SQL Script. Is it possible to do such a thing, please advice me if you have any other suggestions?

LINK server does not fulfill all my reqirements.

Thanks
Haseeb

chadmat
The Chadinator

1974 Posts

Posted - 2004-12-27 : 18:01:41

What requirements are not fulfilled by a linked server?

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-12-27 : 18:02:05

Haseeb, you will need to use a linked server or OPENROWSET. You can find out the syntax and how to setup both in Books Online. Just go to the index tab. Type in linked server or OPENROWSET. If you need help after the initial try, let us know. Books Online is included with SQL Server free. Start/All Programs/Microsoft SQL Server/Books Online.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

hnomani
Starting Member

35 Posts

Posted - 2004-12-27 : 18:10:57

Thanks, I am trying to Create a table (Push) on Server_B from Server_A and the link server gives me the following error message,

Connected: Server_A

select *
into Server_B.pubs.dbo.table1
from pubs.dbo.table1
go

The object name 'Server_B.pubs.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-12-27 : 18:42:50

You can't use a linked server for a SELECT INTO. You would need to run that command on the other server.

select *
into pubs.dbo.table1
from server_a.dbo.table1
go

You could also do a regular insert.

INSERT server_b.pubs.dbo.table(col1)
select col1
from pubs.dbo.table1

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

vsanthosaraj
Starting Member

2 Posts

Posted - 2005-01-10 : 09:21:21

Hi

use sp_addlinkedserver with ipaddress instead of name
ex. sp_addlinkedserver [132.147.160.100]

select * from [132.147.160.100].db.dbo.table

-Victor santhosaraj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources