Error on Linkserver from SQL2008 to SQL2000

Question

Error on Linkserver from SQL2008 to SQL2000

fjmorales

Default port

Points: 1451
More actions
August 2, 2010 at 10:44 am

#227367

I created an Linkserver from a sql2008 server to a 2000 and It works when I use an openquery bu t not when i refere to the server directly.
e.
select * from openquery(2000server,' select * from thistable') this works
but,
select * from 2000server.thidb.dbo.thistable - this doesnt work
I'm looking for info about it but haven't been able to find out the answer.
If anyone can point me to the right direction I'll appreciate it.
FJM

Viewing 15 posts - 1 through 15 (of 17 total)

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

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 1

Maybe the default DB for the linked server account is not the same as the DB you are using in the four-part name that fails?

Adiga One Orange Chip Points: 27284 More actions · Answer 2

select * from 2000server.thidb.dbo.thistable - this doesnt work
FJM

Is thidb the default database for the account which is used in linked server? Also does the login has access to that database?

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

fjmorales Default port Points: 1451 More actions · Answer 3

well, yes I supposed.

I'm using the Server Type option as SQL Server.

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 4

Can you post the error you are getting? Because it sure looks like the query that works is hitting a different database than the one that doesn't work, and that there isn't permission on the database that is failing. First one will use default DB, second will overwrite whatever that was for the named db.

fjmorales Default port Points: 1451 More actions · Answer 5

OLE DB provider "SQLNCLI10" for linked server "myserver" returned message "Unspecified error".

OLE DB provider "SQLNCLI10" for linked server "myserver" 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 "myserver". The provider supports the interface, but returns a failure code when it is used.

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 6

OK that helps. In 2005 I had almost the exact same thing happen to me, and this KB article solved it:

http://support.microsoft.com/kb/906954

Basically you have to reapply the SP4 procs on the 2000 box to get the linked server to work. Try that and see if that helps.

fjmorales Default port Points: 1451 More actions · Answer 7

Is it safe to run the instcat.sql?

it seems to touch a lot of stuff.

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 8

I had the same concern, so I backed everything up first (and I would still recommend that). Didn't cause a single problem that I ever found on my old 2000 box, and the linked server started working immediately.

fjmorales Default port Points: 1451 More actions · Answer 9

I, just ran the INSTCAT.SQL on the sql2000 server and it still gives me the same error.

OLE DB provider "SQLNCLI10" for linked server "myserver" returned message "Unspecified error".
OLE DB provider "SQLNCLI10" for linked server "myserver" 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 "myserver". The provider supports the interface, but returns a failure code when it is used.

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 10

Try running this on the master database of the SQL 2000 server (I did some digging and apparently this is what the sql script was supposed to produce):

create procedure sp_tables_info_rowset_64

@table_name sysname,

@table_schema sysname = null,

@table_type nvarchar(255) = null

as

declare @Result int set @Result = 0

exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type

go

fjmorales Default port Points: 1451 More actions · Answer 11

fjmorales

Default port

Points: 1451

August 4, 2010 at 8:02 am

#1202099

Dude!... that's it. Why?

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 12

Old SQL Server 2000 32 bit installs didn't know how to handle 64 bit linked servers. That proc just allows them to do it. It's standard on more recent servers.

fjmorales Default port Points: 1451 More actions · Answer 13

My problem with the SQL2000 was solved but now I have a problem with alinkserver to ORACLE.

Is giving me this error:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "myserver" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "myserver".

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 14

I don't have much Oracle experience so I have to drop at this point -- hopefully someone else will see this and jump in....