Linked Server shows table but does not retrieve data

Question

Post reply

Linked Server shows table but does not retrieve data

naqib_bs

Old Hand

Points: 366
More actions
October 26, 2012 at 6:47 am

#264646

Hello everyone,
I have two database server each X(SQL Server 2000) and Y(SQL Server 2008), I created a link server in Y server and I can see and navigate the
between databases and tables. However, I cannot execute query it generates this error:
---------------------------------------------------------------------------------------------
OLE DB provider "SQLNCLI10" for linked server "MY_LINKED_SERVER" returned message "Errore non specificato.".
OLE DB provider "SQLNCLI10" for linked server "MY_LINKED_SERVER" 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 "MY_LINKED_SERVER". The provider supports the interface, but returns a failure code when it is used.
---------------------------------------------------------------------------------------------
I have googled and found these links:
1. http://blogs.msdn.com/b/dataaccesstechnologies/archive/2012/08/09/error-while-creating-linked-server-quot-cannot-obtain-the-schema-rowset-quot-dbschema-tables-info-quot-for-ole-db-provider-quot-sqlncli10-quot-for-linked-server-linkedservername-quot.aspx
2. http://saveadba.blogspot.it/2012/02/dbschematablesinfo-cannot-obtain-schema.html
however, I am scared and not sure about these steps, because, I am really new to Microsoft apart of this both servers are live servers.
so please help me in a very chronological way please
thanks

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

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

Chrissy321 SSCoach Points: 15763 More actions · Answer 1

Whats the service pack level of the SQL Server 2000?

naqib_bs Old Hand Points: 366 More actions · Answer 2

it is SQL Server 2000 Enterprise 8. SP1 I don't know what is level?

Chrissy321 SSCoach Points: 15763 More actions · Answer 3

Did you get this information by running @@VERSION in SSMS/Query Analyzer?

If you are on SP1 you will need to apply the latest service pack, SP1 I believe, and run the instcat.sql script to get the linked server to work.

Is there a DBA involved? Or are you it?

naqib_bs Old Hand Points: 366 More actions · Answer 4

not at all as I am new to I have checked it in help option and there was SP1.

Chrissy321 SSCoach Points: 15763 More actions · Answer 5

Refer to this and let us know the results.

http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html

naqib_bs Old Hand Points: 366 More actions · Answer 6

thanks a lot , I am outside of office, and I don't have remote access to my server. I will do operation tomorrow morning, could you please give me some suggestion in advance please so that I will be ready for tomorrow morning?

thanks

Chrissy321 SSCoach Points: 15763 More actions · Answer 7

If you are really on SP1 you will have to decide whether you are going to install the latest service pack or not.

If you want to proceed search 'SQL Server Best Practices Installing Service Packs' and start reading.

If you don't want to install the service pack you won't be able to use the linked server. If you do not want to install the service pack then we can explore other options which would allow you to meet you goals.

For example, if your need for data integration is not real or near real-time you could possibly export the data out and then import into your 2008 server.

naqib_bs Old Hand Points: 366 More actions · Answer 8

No I want realtime, even I export it in xml but it is so slow because in a single table there are more than Milion records

can you please provide me how to upgrade step by step

naqib_bs Old Hand Points: 366 More actions · Answer 9

The results:

1. The result of first query

8.00.2039

2. The second query seems to be SP4:

SQL Server 8.00.2039 - SP4 (Enterprise Edition)

3. this is the version:

I did not understand what is going on in one place it is SP1 and in another it is SP4?

TomThomson SSC Guru Points: 104773 More actions · Answer 10

naqib_bs (10/27/2012)
3. this is the version:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 6.1 (Build 7601: Service Pack 1)
I did not understand what is going on in one place it is SP1 and in another it is SP4?

Your SQL Server has SQL Server 2000 SP4 applied.

The Windows operating system (NT6.1, which could be either Windows 7 or Windows Server 2008 R2) you are running SQL Server on has either Windows 7 SP1 or Windows Server 2008 R2 SP1 applied.

So no conflict, not a problem.

Tom