Accessing a Linked Server - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Accessing a Linked Server

Author

Topic

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-09-02 : 14:03:34

Hi experts,

I have linked 2 2008 servers.

The linked server name is PNCLRMDB.

The runs OK when I run it locally. (Notice that the code does not reference any tables by name)

But when I try to run it against a Linked server, I get an error "Invalid object name PNCLRMDB".

Will I have to use the 4-part naming - pnclrmdb.master.dbo.TableName ?

Thanks, John

select getdate(),
cast(serverproperty('machinename') as varchar(20)) 'Server',
cast(serverproperty('productversion') as varchar(12)) 'Version',
cast(serverproperty('productlevel') as varchar(20)) 'SP',
cast(serverproperty('edition') as varchar(40)) 'Edition',
cast(serverproperty('IsClustered') as varchar(1)) 'Is Clustered?',
cast(serverproperty('LicenseType') as varchar(40)) 'LicenseType'
from PNCLRMDB;

Kristen
Test

22859 Posts

Posted - 2011-09-02 : 17:32:19

"Will I have to use the 4-part naming - pnclrmdb.master.dbo.TableName"

Yes - but I doubt it will be in MASTER - you want RemoteServerName.RemoteDatabaseName.dbo.RemoteTableName

"... from PNCLRMDB"

This will look in a table called "PNCLRMDB" in the current database (with default owner)

You might like to look at using OPENQUERY, instead of 4-part-naming. I find that that is more efficient, although the quoting can be a PITA at times.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-02 : 17:49:41

Even after you get rid of the error, that code is going to return you the information for your local system and not the remote system.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2011-09-03 : 01:01:08

[code]
SELECT *
FROM OPENQUERY(PNCLRMDB,
'select getdate(),
cast(serverproperty(''machinename'') as varchar(20)) ''Server'',
cast(serverproperty(''productversion'') as varchar(12)) ''Version'',
cast(serverproperty(''productlevel'') as varchar(20)) ''SP'',
cast(serverproperty(''edition'') as varchar(40)) ''Edition'',
cast(serverproperty(''IsClustered'') as varchar(1)) ''Is Clustered?'',
cast(serverproperty(''LicenseType'') as varchar(40)) ''LicenseType''
')
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources