linked server issue(2008 to 2000)

Question

Post reply

linked server issue(2008 to 2000)

balaji.ganga

SSCrazy

Points: 2280
More actions
September 24, 2010 at 2:07 am

#240831

Hi,
I created a linked server from sqlserver 2008 to sqlserver 2000. i checked it all tables are coming properly. when i'm going to execute a select query thru query analyser.. its shows the below error
OLE DB provider "SQLNCLI10" for linked server "SYDSQLD04\SQLDEV_CRM" returned message "Unspecified error".
OLE DB provider "SQLNCLI10" for linked server "SYDSQLD04\SQLDEV_CRM" 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 "SYDSQLD04\SQLDEV_CRM". The provider supports the interface, but returns a failure code when it is used.
I would appreciate, if any body helps me at the earliest..
Thanks & Regards
Balaji.G

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

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

crazy4sql SSCoach Points: 19590 More actions · Answer 1

use 'sql server native client 10.0' as provider.

----------
Ashish

balaji.ganga SSCrazy Points: 2280 More actions · Answer 2

Hi,

could you pls shared me, where can i use this....

use 'sql server native client 10.0' as provider.

Regards

Balaji.G

crazy4sql SSCoach Points: 19590 More actions · Answer 3

right click on your linked server, go to peroperties, go to general tab--you will see provider option.

Change the provider to 'sql server native client 10.0'

----------
Ashish

crazy4sql SSCoach Points: 19590 More actions · Answer 4

any update???

is that resolved your problem???

----------
Ashish

balaji.ganga SSCrazy Points: 2280 More actions · Answer 5

Hi Ashish,

I checked there.. but there is no sqlserver native client option..

Meanwhile

i tried to create from 2000 to 2008.. It shows the following error

like

[DBNETLIB][ConnectionOpen(Connect()).] Sql Server does not exist or access denied..

Could you pls advice me.. how to fix it?

Thanks & Regards

Balaji.G

crazy4sql SSCoach Points: 19590 More actions · Answer 6

I checked there.. but there is no sqlserver native client option..

if you doing this in sql server 2008 and selecting 'server type' as other data source then this provider will be there.

i tried to create from 2000 to 2008.. It shows the following error
like
[DBNETLIB][ConnectionOpen(Connect()).] Sql Server does not exist or access denied..

what kind of secuirty you configuring in security tab of linked server?

----------
Ashish

balaji.ganga SSCrazy Points: 2280 More actions · Answer 7

final option.(not to be made)

its asking remote username and password

i given the correct credentials details..

but its shows error

thanks

balaji.g

crazy4sql SSCoach Points: 19590 More actions · Answer 8

did you tried with third option, 'be made using the login's current.......'?

Also the account you configured with password, does that user have enough permission to configure the linked server?

----------
Ashish

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 9

Have a look at

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/MSSQLServerAdmin/sql-server-linked-server-between-2005-64

For cannot obtain rowset you'll have to create sp_tables_info_rowset_64

I can't find the original MS KB for it at the moment

Found it:

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

balaji.ganga SSCrazy Points: 2280 More actions · Answer 10

learningforearning (9/24/2010)
did you tried with third option, 'be made using the login's current.......'?
Also the account you configured with password, does that user have enough permission to configure the linked server?

How do i check... if a user having enough permission..

Regards

Balaji.G

balaji.ganga SSCrazy Points: 2280 More actions · Answer 11

Hi Ashish,

How do i check whether the user having enough permission..

Regards

Balaji.G

crazy4sql SSCoach Points: 19590 More actions · Answer 12

How do i check whether the user having enough permission..

the account you configuring in security tab, you need to be make sure that account have requried permission on remote server which you configuring in linked server.

Also have you looked into Jo suggestion?

----------
Ashish

mayank_tyagi Old Hand Points: 325 More actions · Answer 13

Hi friend,

Do you have 64 bit sql server 2008 and sql server 2000 on 32 Bit ?

Then you need to create a stored procedure in master database of sql server 2000-- sp_tables_info_rowset_64 (To make it compat. to 64 Bit version)

Regards

Mayank tyagi

balaji.ganga SSCrazy Points: 2280 More actions · Answer 14

crazy4sql (9/27/2010)
How do i check whether the user having enough permission..
the account you configuring in security tab, you need to be make sure that account have requried permission on remote server which you configuring in linked server.
Also have you looked into Jo suggestion?

I just try to create an odbc for SQL 2000 box and configure into sql2008 database..

it shows error " Sql Server does not exist" and i ping the sql2008 servername from sql 2000 box..

its shows "unknown hostname"....

The above method which i tried to configure the linked server for other datasources option

But its failed due to sqlserver doesnot exist error..

I feel that both sqlserver 2000 box and 2008 box are in different segments..

Is it possible to create a linked server? if its possible.. pls give suggestion..

Thanks

Balaji.G