Linked server connectivity issue

Question

Linked server connectivity issue

praveen.gln1

SSC-Addicted

Points: 438
More actions
May 20, 2011 at 1:11 pm

#240643

I am having linnked server A on server B.Am trying to fetch data by querying from Linked server A.
Select top 1 * from A.Test(database name).dbo.query(table name),when i executedthis am getting error like
An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server "A"
Can some one help me ASAP as am almost done with my investigation but could not find the solution.
Thanks in Advance.

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

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

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 1

praveen.gln1 (5/20/2011)
I am having linnked server A on server B.Am trying to fetch data by querying from Linked server A.
Select top 1 * from A.Test(database name).dbo.query(table name),when i executedthis am getting error like
An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server "A"
Can some one help me ASAP as am almost done with my investigation but could not find the solution.
Thanks in Advance.

First thing is to make sure your statement is correct. Can it be run successfully on server B?

If it is correct you could try setting the "level zero only" box on the SQLNCLI provider. This link "http://www.sparkalyn.com/2008/12/invalid-schema-error/" has instructions on the MSDASQL provider. The instructions and the reasoning should be fairly similar.

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

Did this just start happening or is this a new linked server?

Is the user that is being used to login to the server A have access to the database and schema in question?

CEWII

praveen.gln1 SSC-Addicted Points: 438 More actions · Answer 3

Thank you,

But i dont find MSDASQL provider to check LeveZeroOnly option from serverobject-->linkedservers-->providers

praveen.gln1 SSC-Addicted Points: 438 More actions · Answer 4

YES ITS HAPPENING FROM THIS TIME.

Here the user is SYSADMIN,i guess preveliges may not be issue.

But here the query when run on Server B for other linked servers with same schema (dbo) we are able to get the result.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

Here the user is SYSADMIN,i guess preveliges may not be issue.

This was unclear so I want to explain some things.

The local login trying to run the query from the originating server may or may not be relevant to the discussion, if it is sysadmin then less so. However, depending on how you have the security configured for that login you can still have permissions issues on the destination side. So how is the sercurity on the linked server on the originating side configured?

CEWII