Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Msg 7313, Level 16, State 1 Expand / Collapse
Author
Message
Posted Monday, June 12, 2006 2:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 8, 2010 8:02 AM
Points: 151, Visits: 15

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.




Post #286846
Posted Tuesday, June 13, 2006 9:36 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 9:53 AM
Points: 3,475, Visits: 584
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 Varshal

Post #287076
Posted Tuesday, June 13, 2006 11:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 9, 2006 12:07 PM
Points: 13, Visits: 1
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.
Post #287113
Posted Tuesday, June 13, 2006 1:28 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 9:53 AM
Points: 3,475, Visits: 584

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 Varshal

Post #287140
Posted Friday, July 7, 2006 1:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 8, 2010 8:02 AM
Points: 151, Visits: 15
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.


Post #292911
Posted Sunday, July 9, 2006 7:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 6:49 AM
Points: 344, Visits: 322

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)

 

 

Post #293008
Posted Sunday, July 9, 2006 7:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 6:49 AM
Points: 344, Visits: 322

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

 

Post #293009
Posted Thursday, March 27, 2014 6:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 6:49 AM
Points: 1, Visits: 104
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
Post #1555400
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse