Querying a view that's not located on the same server (SQL Server 2005)

Question

Hi there !

I'm trying to query a database view that's not located on the same server as the stored procedure I'm running.

I heard about using "linked servers", but I have no access to the server's configuration at all ...

Thanks in advance !

Answer 1

tried <servername>.<databasename>.<schema>.<viewname>

e.g. servera.MyDatabase.dbo.vwCustomView ?

EDIT: Sorry I slightly misread the question. I've only ever done cross database queries on the same server, so not sure if this works.

Answer 2

You can do this, but it does require the DBA to set up the link. If you don't have access to the server's configuration and the DBA is not on board, you're out of luck.

Answer 3

Use OPENDATASOURCE:

SELECT   *
FROM      OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=ServerName;User ID=MyUID;Password=MyPass'
         ).Northwind.dbo.Categories