Connect to Database on Diff server

[MattN]

Can someone please tell me the TSQL syntax used to connect to a database on a different server.

I am familiar with the USE command but this only works on the server you are connected to.

Thanks in advance.

Matt
:-|

 

[LFCfan]

It would be

[MYSERVER\MYINSTANCE].Database.Owner.Table

but first you need to link MYSERVER to the server you're calling from (in Enterprise manager, that's in Security/Linked Servers)

HTH

 

[praseeda]

I used sp_addlinkedserver 'ServerName'
And then can someone tell me on what syntax should I use to access a table within a database in the server.

Save the server 'ServerName' had a database called 'DatabaseName' and which has a table called 'TableName'

can I use

Select * from ServerName.DatabaseName.tableName

 

[JamesLean]

lfcfan gave the syntax in his previous post!

--James

 

[LFCfan]

her previous post, even!
)

 

[JamesLean]

Oops, very sorry!

--James

 

[praseeda]

I managed to link the SQL server and succesfully did it .
Thanks very much to you all.
But now how to add a linked access dataebase.
I tried the following :

USE master
GO
EXEC sp_addlinkedserver
'Test',
'OLE DB Provider for Jet',
'Microsoft.Jet.OLEDB.4.0',
'\\Proliant_2500\ads\Commission\Data\Cost Analysis.mdb'
GO

It does affect one row , but then when i go to the linked server and try to browse the table it comes up with an error. Any help here then please