connection to miltiple server

i want to open query with server1 = 'server1.datasource'

in this query i want to connect to 'server2.datasource '

for copy record between the servers .

can i do it with sql server management studio?

You will want to define a Linked Server

http://msdn2.microsoft.com/en-us/library/ms190479.aspx

Or use OPENDATASOURCE

http://msdn2.microsoft.com/en-us/library/ms179856.aspx

Using the Linked Server you can then reference tables with the four-part name (server.database.schema.table)

|||

gabriel_333 wrote:

can i do it with sql server management studio?

I hope you can use the OPENROWSET.

See more on Books Online regarding OPENROWSET.

|||

I HAVE AN OLE DB SYSTEM DSN = SQLDATCON

WHEN I RUN :

SELECT *

FROM OPENDATASOURCE('SQLDATCON',

'Data Source=SQLDAT;USER ID = ?;PASSWORD =?')

.DBNAME.dbo.TB_ACID

I GET MESSAGE :

Msg 7403, Level 16, State 1, Line 6

The OLE DB provider "SQLDATCON" has not been registered.

|||

Try:

SELECT *

FROM

OPENDATASOURCE('SQLOLEDB', 'dsn=SQLDATCON;USER ID = ?;PASSWORD =?').DBNAME.dbo.TB_ACID

|||

thanks a lot!

i got possitive rspond from the server :' hd hook must have administrator permission '

this is the first life signal

when i need this query i will ask administrater to enable the surface area opendatasource