OPENDATASOURCE not working in SQL2008 x64

Question

Post reply

OPENDATASOURCE not working in SQL2008 x64

Adam Bean

One Orange Chip

Points: 26631
More actions
March 20, 2009 at 10:58 am

#129969

Hey all,
I can run OPENDATASOURCE queries just fine on SQL2008 32bit, but when trying on a 64bit box, I get
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Unspecified error".
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used.
I'm looking for answers online, but was hoping someone came across this ... Need to get this working ASAP.
Thanks in advance
(also, I realize I'm posting in a 2005 forum, but much more activity here ...)

Viewing 12 posts - 1 through 11 (of 11 total)

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

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

Are you running it against sql 2000/2005 server instances?

MJ

Gift Peddie SSC Guru Points: 73570 More actions · Answer 2

It depends on what you are doing with OPENDATASOURCE if you are using it with x64 RDBMS server like Oracle, DB2 or PostgreSQL then you can use the 64bits OLE DB driver in Win2008 and in 2003 you need to download and install. But if you are using it with any Office application you need to use the x86 ODBC in the WOW64 directory.

Kind regards,
Gift Peddie

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

MANU (3/20/2009)
Are you running it against sql 2000/2005 server instances?
MJ

Yep. I remember having a similar issue with 2005 to 2000 in by where I had to re-apply instacat.sql (from 2000 SP4).

Adam Bean One Orange Chip Points: 26631 More actions · Answer 4

Adam Bean

One Orange Chip

Points: 26631

March 23, 2009 at 2:10 pm

#964755

Still haven't had much luck on this one ...

Adam Bean One Orange Chip Points: 26631 More actions · Answer 5

Hate to keep bumping this topic, but still haven't found resolution.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 6

You have not posted what you are using the opendatasource to connect to I also need to know what x64 operating system you are running.

Kind regards,
Gift Peddie

Adam Bean One Orange Chip Points: 26631 More actions · Answer 7

Gift Peddie (3/26/2009)
You have not posted what you are using the opendatasource to connect to I also need to know what x64 operating system you are running.

I am attempting to use opendatasource against SQL2000 and 2005 instances.

I am running on Windows 2008. It works fine locally on XP x86.

Any thoughts?

Gift Peddie SSC Guru Points: 73570 More actions · Answer 8

I think you have not installed the Native Client driver so download install it and reference the x86 verson of it so you can connect to x86 2000 and 2005.

http://www.microsoft.com/downloads/details.aspx?familyid=c6c3e9ef-ba29-4a43-8d69-a2bed18fe73c&displaylang=en&tm

Kind regards,
Gift Peddie

Adam Bean One Orange Chip Points: 26631 More actions · Answer 9

SQL Native Client is indeed installed. I attempted to download and install the x86 version and was given the error about "not supported for this operating system." Downloaded the x64 version and did a repair install. Now it appears that I can hit 2005 instances just fine, but 2000 instances are still erroring:

OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Unspecified error".

OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used.

Any thoughts?

Gift Peddie SSC Guru Points: 73570 More actions · Answer 10

There is no schema in 2000 but check the thread below in another forum for what you need to install in 2000 for the operation to work. Remember to restart SQL Server.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105660

Kind regards,
Gift Peddie

Adam Bean One Orange Chip Points: 26631 More actions · Answer 11

Yeah I've had to re-apply instacat.sql in the past before for 2005 to 2000 ... Hooray.

Well, no getting to that anytime soon, so I'll let ya know if that ends up fixing it when I can get to it.

Thanks