Msg 7313, Level 16, State 1

Question

Msg 7313, Level 16, State 1

vambati

SSCrazy

Points: 2769
More actions
June 12, 2006 at 2:18 pm

#61907

We are running SQL 2000 and have 2 sql servers linked. All databases are linked between both the servers. We get this weird error only when qrying 2 dbs:
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'SQLOLEDB'.
OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].
Unfortunately all the databases have identical table/schema/db objects. Only the database names are different. Why is this happening only with 2 db's where are all others are working find. Any ideas or has anyone seen this. Appreciate any help or thoughts on this.

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

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

I would check the security, logins, users etc. Does a user who is a remote login in linked server properties have permissions in these 2 databases? What is the default database for remote login user? We did have somewhat similar problem when linking tables from MS ACCESS when the SQL Server login that was used had a default database different from one we were trying to connect. The solution was to modify ODBC source to change the database when connecting. I do understand that your problem is for SQL Server Linked Server, but check permissions anyway.

Regards,Yelena Varsha

Christian Benvenuto-248082 SSC Enthusiast Points: 163 More actions · Answer 2

I've run into wierd security errors when using linked server. You know the security is correct in the DB's and within the linked server properties? Double check. I'm embarassed to write this but, I've wiggled around alot of linked server issues by just dropping and recreating the linked server. I'm not sure why this seems to work, but 80% of the time it fixes the security problems. Try it.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

I thought of something else: what are the names of bad-connecting databases? I had a case when the server name contained a dash - and I had to enclose the name in [] for the name to work. Same may be applicable to the database names based on the provider you use for the linked server. Check for spaces in database names. Also check that you spelled the database names correctly. And one more thing: make sure you are connecting to the correct server that contains these databases. No, it is not a joke, it does happen.

Regards,Yelena Varsha

vambati SSCrazy Points: 2769 More actions · Answer 4

Thanks for all your inputs, but we still have the same issue and error. The userid used to connect is the same for all the db's. The 2 db's in qns were created recently using the script of an existing db. The qry runs find if its run from the db itslef...but if i run it from the linked server it blows up. Have checked all the permissions. Its works fine from app end...but when we qry this db tables using linked server it fails. There are about 80 db's and rest all work fine using linked server. Thank you for any help.

Balmukund SSCertifiable Points: 6648 More actions · Answer 5

Looks interesting...

Lets run below query on destination server and post output here.

SELECT dbnamelength = DATALENGTH(name), name FROM master..sysdatabases WITH (NOLOCK)

GO

I am suspecting that you have blank space some where (either in DB or in object name)

Balmukund SSCertifiable Points: 6648 More actions · Answer 6

Have a quick look at create database command (you said you created by script) I am sure that there is something worng with that...

evanstarr Valued Member Points: 65 More actions · Answer 7

This worked for me! There was a "space" included at the end of the DB name I was trying to query. Once I included the space to my linked server query - success! The space is now removed from the db name. Thanks!

-Evan