Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2015
    Posts
    2

    Unanswered: SQL Server 2008 Linked Server to Sybase 15.5

    Cannot create an instance of OLE DB provider "ASEOLEDB" for linked server "[SERVER_NAME]"

    The current setup:
    Our primary database is on Sybase, I needed to extract hierarchical (nested) XML from multiple tables on Sybase. Although Sybase ASE 15.5 has `FOR XML` query capability it does now allow nested XML format. So we used the 'FOR XML', XML functionality in SQL Server (as we had a SQL Server 2008 database available which is used for a smaller system) to query Sybase tables through a Linked Server connection.

    Our sample nested XML Format is similar to:

    <personCol>
    <person>
    <id>1111</id>
    <name>John</name>
    <age>21</age>
    <sex>M</sex>
    <addressCol>
    <address>
    <type>home</type>
    <address>XXXX</address>
    </address>
    <address>
    <type>work</type>
    <address>YYYYY</address>
    </address>
    <address>
    <type>delivery</type>
    <address>XXXXXX</address>
    </address>
    </addressCol>
    <status>single</status>
    <phoneCol>
    <phone>
    <type>home</type>
    <number>11111111</number>
    </phone>
    </phoneCol>
    <note>Note 1</note>
    </person>
    <person>
    <id>2222</id>
    <name>Mary</name>
    <age>45</age>
    <sex>F</sex>
    <addressCol>
    <address>
    <type>home</type>
    <address>XXXX</address>
    </address>
    </addressCol>
    <status>single</status>
    <phoneCol>
    <phone>
    <type>home</type>
    <number>22222222</number>
    </phone>
    <phone>
    <type>work</type>
    <number>3333333</number>
    </phone>
    </phoneCol>
    <note>Note 1</note>
    </person>
    </personCol>

    The Issue:

    Everything is working perfectly when extracting data to XML format from Sybase via SQL Server. The issue is when the SQL Server machine is rebooted, the connection to Sybase can not be re-established automatically after the reboot. I get the following error from the executable that calls the SQL Server query:
    Cannot create an instance of OLE DB provider "ASEOLEDB" for linked server "[SERVER_NAME]".

    Once I execute the following command on the SQL Server it re-establishes a connection and it then works again until the machine is rebooted again:

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'[SERVERNAME]', @locallogin = NULL , @useself = N'False', @rmtuser = N'DEV_IIS_USER', @rmtpassword = N'password'

    I've tried:

    - Ensuring the SQL Server services are set to Automatic start when the machine reboots.

    - Setup with the Server as following:


    EXEC master.dbo.sp_addlinkedserver @server = N'[SERVERNAME]', @srvproduct=N'[SERVERNAME]', @provider=N'ASEOLEDB', @datasrc=N'[SERVERNAME]'

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'[SERVERNAME]',@useself=N'False',@locallogin=NULL,@rmtuser=N'DE V_IIS_USER',@rmtpassword='########'

    EXEC master.dbo.sp_serveroption @server=N'[SERVERNAME]', @optname=N'collation compatible', @optvalue=N'false'

    EXEC master.dbo.sp_serveroption @server=N'[SERVERNAME]', @optname=N'data access', @optvalue=N'true'

    EXEC master.dbo.sp_serveroption @server=N'[SERVERNAME]', @optname=N'dist', @optvalue=N'false'

    EXEC master.dbo.sp_serveroption @server=N'[SERVERNAME]', @optname=N'pub', @optvalue=N'false'

    EXEC master.dbo.sp_serveroption @server=N'[SERVERNAME]', @optname=N'rpc', @optvalue=N'false'

    EXEC master.dbo.sp_serveroption @server=N'[SERVERNAME]', @optname=N'rpc out', @optvalue=N'false'

    EXEC master.dbo.sp_serveroption @server=N'[SERVERNAME]', @optname=N'sub', @optvalue=N'false'

    EXEC master.dbo.sp_serveroption @server=N'[SERVERNAME]', @optname=N'connect timeout', @optvalue=N'0'

    EXEC master.dbo.sp_serveroption @server=N'[SERVERNAME]', @optname=N'collation name', @optvalue=null

    EXEC master.dbo.sp_serveroption @server=N'[SERVERNAME]', @optname=N'lazy schema validation', @optvalue=N'false'

    EXEC master.dbo.sp_serveroption @server=N'[SERVERNAME]', @optname=N'query timeout', @optvalue=N'0'

    EXEC master.dbo.sp_serveroption @server=N'[SERVERNAME]', @optname=N'use remote collation', @optvalue=N'true'

    - Also tried:

    sp_configure 'remote login timeout', 300

    reconfigure with override

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The obvious (and highly unsatisfying) workaround is to set up a SQL Agent job to run the sp_add_linkedsrvlogin command at startup.

    I am assuming here that the login does not need to be dropped? It is highly unusual for SQL Server to lose any data like this due to a restart of the service. It is not like this is an in-flight transaction that should be rolled back.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Instead of involving SQL Agent look into a startup stored procedure formed from your script:

    https://msdn.microsoft.com/en-us/library/ms181720.aspx
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •