Link Server

Transact-SQL
1

I have successfully setup a Link Server to connect to a vendor's database. Because they only gave us "data reader" access, I cannot create SPs on their database. Therefore, the Link Server was created.

This syntax worked:
SELECT * FROM [LinkServer].[Database].dbo.Table

Is there a recommended approach for this in both performance, efficiency and syntax? Will I have to write out the entire [LinkServer].[Database].dbo.Table every time?

I think the alternative is something called OpenQuery and OpenDatasource.

Thanks

2

As far as typing in the four part name goes, you could create a Synonym which would reduce the typing. As far a efficiency goes, be careful when joining tables from a linked server, It can be very inefficient.

1 Like
3

OpenQuery:

SELECT o.* 
FORM OpenQuery([LinkServer],'SELECT * FROM [Database].dbo.Table') o;

OpenRowSet:

SELECT o.*
FROM OpenRowset('SQLOLEDB', 'Server=ServerName;Trusted_Connection=yes;','SELECT * FROM [Database].dbo.Table') o;
1 Like