linked server issue(2008 to 2000)

  • 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

  • use 'sql server native client 10.0' as provider.

    ----------
    Ashish

  • Hi,

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

    use 'sql server native client 10.0' as provider.

    Regards

    Balaji.G

  • 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

  • any update???

    is that resolved your problem???

    ----------
    Ashish

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • Hi Ashish,

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

    Regards

    Balaji.G

  • 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

  • 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

  • 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

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

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