Post reply

SQL Providers - SQLNCLI being used instead of SQLOLEDB

  • June 3, 2009 at 7:44 am

    #138189

    Hi All,

    Any assistance on the below issue would be helpful..

    I have 2 databases (both SQL 2005) and are trying to create a database link between them so we can access objects contained in the Remote Database 'ARSystem'. The Hostname of the Database is 'basARSystem'.

    The link has been created as below WITH 'SQLOLEDB' as the provider.

    [font="Arial Narrow"]EXEC sp_addlinkedserver @server = 'LINK_TO_AR_DB', @provider = 'SQLOLEDB', @srvproduct = 'SQLServer OLEDB Provider', @datasrc = basARSystem, @catalog = ARSystem[/font]

    Subsequently I have added a login to the created database link :

    [font="Arial Narrow"]EXEC sp_addlinkedsrvlogin @rmtsrvname = 'LINK_TO_AR_DB', @useself = 'false',

    @locallogin = NULL, @rmtuser = 'CCM_USER', @rmtpassword = 'CCM_USER'[/font]

    The Database link has been created successfully and also the above login has

    been added as well.

    Have been able to confirm the same by running the below SQL Statement.

    [font="Arial Narrow"]select * from master..sysservers where srvname = 'LINK_TO_AR_DB' [/font]

    Output:

    1 1184 LINK_TO_AR_DB SQLServer OLEDB Provider SQLOLEDB basARSystem

    However while trying to access remote database objects using the Database

    Link name, we are getting an error because it attempts to use the 'SQLNCLI'

    provider instead of using the 'SQLOLEDB' provider. (please see below)

    [font="Arial Narrow"]select count(*) from LINK_TO_AR_DB..CCM_USER.CCM_PDL_ProductDictionaryPatch [/font]

    Output:

    The OLE DB provider "SQLNCLI" for linked server "LINK_TO_AR_DB" does not contain the table ""CCM_USER"."CCM_PDL_ProductDictionaryPatch"". The table either does not exist or the current user does not have permissions on that table.

    However I have ensured that the CCM_USER has requisite permissions to read the "CCM_PDL_ProductDictionaryPatch" view and the same select statement works fine when executed directly on the database.

    Why is the incorrect provider 'SQLNCLI' being used inspite of the link being created to use the 'SQLOLEDB' provider? Any suggestions/pointers would be much appreciated.

    This was working fine just a day ago.

    Thanks in Advance,

    Sam

  • June 5, 2009 at 2:25 am

    #1005071

    Was able to figure this out.. We were using the Database User instead of the Database SID..

    Incorrect ::

    select count(*) from LINK_TO_AR_DB..CCM_USER.CCM_PDL_ProductDictionaryPatch

    Correct ::

    select count(*) from LINK_TO_AR_DB..ARSystem.CCM_PDL_ProductDictionaryPatch

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

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