server with SQLNCLI and a default database.

I have a linked server from one 2005 server (server A) to another 2005 server (server B). The linked server is created like this:

EXEC sp_addlinkedserver

@.server = N'TEST',

@.srvproduct=N'',

@.provider=N'SQLNCLI',

@.datasrc=N'twx-webdev',

@.catalog=N'Hornet'

This query works fine:

SELECT * FROM TEST.Hornet.dbo.NotificationTrigger

But, I need to be able to make a query from server A to server B without specifying the database name. I need the default database of the linked server to be used when making the following query:

SELECT * FROM TEST...NotificationTrigger

This query does not work. The query returns the following error:

Msg 7313, Level 16, State 1, Line 1

An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server "TEST".

I also used the following syntax when creating the linked server hoping the Initial Catalog in the provider string would work.

EXEC sp_addlinkedserver

@.server = N'TEST',

@.srvproduct=N'',

@.provider=N'SQLNCLI',

@.datasrc=N'twx-webdev',

@.provstr=N'Initial Catalog=Hornet'

The query against this linked server returned this error:

Msg 7314, Level 16, State 1, Line 2

The OLE DB provider "SQLNCLI" for linked server "TEST" does not contain the table ""dbo"."NotificationTrigger"". The table either does not exist or the current user does not have permissions on that table.

Also, this query produces the same results:

SELECT * FROM TEST..dbo.NotificationTrigger

How can I force the linked server to actually use the default database and not have to specify the database in queries? The 2005 documentation for linked servers alludes to this being possible.

Any help you can provide is very appreciated.

Thank you,

Chris

http://msdn2.microsoft.com/en-us/ms188718.aspx

http://support.microsoft.com/kb/280106

http://sqlserver2000.databases.aspfaq.com/how-do-i-prevent-linked-server-errors.html - a good one as a whole to resolve the LS errors.

|||

The following quote is from your first link. Is this telling me that I cannot default the database in a query even if I have the default catalog set on the linked server?

"When you use four-part names, always specify the schema name. Not specifying a schema name in a distributed query prevents OLE DB from finding tables. When referencing local tables, SQL Server uses defaults if an owner name is not specified. The following SELECT statement would generate a 7314 error, even if the linked server login mapped to a dbo user in the AdventureWorks database on the linked server:"

Thanks,
Chris