Access SQL 2008 Linked Server without specifying default Database Name

Question

Post reply

Access SQL 2008 Linked Server without specifying default Database Name

mathieu.lapointe

Old Hand

Points: 352
More actions
January 12, 2010 at 9:26 am

#218208

Hello,
I am a newbie using Linked Server and I try to access the default database of a linked server without specifying it. I have made many search about this topics.
I create a linked server on the Server SERVER1 (SQL Server 2008) with the default database DB1.
The RPC out option is set to true and the authentication is right.
I try to access a table by the following method :
SELECT * FROM SERVER1...TABLENAME
and it is not working and i get the following error :
An invalid schema or catalog was specified for the provider "SQLNCLI10" for linked server "SERVER1".
It is working when I do the following query :
SELECT * FROM OPENQUERY(SERVER1, 'SELECT * FROM TABLENAME')
When I call a stored procedure (SELECT, INSERT, UPDATE OR DELETE) using the following method :
EXEC SERVER1...MYSPNAME
it is working.
But when I try to use OPENQUERY for executing a stored procedure it is not working well :
First the INSERT, UPDATE AND DELETE must returns columns to work and the action is not performed in my table I try to update.
Second when a stored procedure return multiple tables, it only returns the first one.
I try to find a way to do both (SELECT FROM table and execute stored procedure) with the same technique if it is possible.
I found something like that :
EXEC('SELECT * FROM TABLENAME') AT SERVER1
EXEC('EXEC MYSPNAME') AT SERVER1
and it is working for both. But if it is possible, it try to avoid dynamic SQL queries because we can pass parameters to the select queries or to the stored procedure.
For me the best solution will be to use the 4 parts naming without providing the database name to use the default database like this :
SELECT * FROM SERVER1...TABLENAME
EXEC SERVER1...MYSPNAME
But I cannot make the SELECT on the table working.
Regards

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

Try specificying the table as: SERVER.Dbname.dbo.Tablename

The probability of survival is inversely proportional to the angle of arrival.

mathieu.lapointe Old Hand Points: 352 More actions · Answer 2

Hello,

I know that specifying the database name will work for both (SELECT and SP), but I need to don't specify it because we don't know the database name in our queries.

The database is the default database of the linked server and we use the default catalog property of the linked server precisely to don't specify the database name.

Regards

sturner SSC-Insane Points: 22411 More actions · Answer 3

The 'default' database for a linked server connection would depend upon the connection credentials for the linked server.

The probability of survival is inversely proportional to the angle of arrival.

mathieu.lapointe Old Hand Points: 352 More actions · Answer 4

Hello,

The default database of the user used to login of the linked server is the same than the database specified in the catalog property of my Linked server.

My question is more why can I call a stored procedure using the following method :

EXEC SERVER1...MYSPNAME

and why it is not working when I try to access a table using the same method :

SELECT * FROM SERVER1...TABLENAME

Regards

Jitender Sharma SSC Enthusiast Points: 184 More actions · Answer 5

Try using Exec [Sql Statement] At [Server name] it should work e.g.

Exec 'Select * from tablename' At [MyServer]

Regards

Jitender

mathieu.lapointe Old Hand Points: 352 More actions · Answer 6

I knew this method is working for both SELECT on table and stored procedure, but I want to avoid this method because we don't want to use query in string variables.

Regards