Post reply

Creating Link Server between MS SQL Server and ASE (Sybase)

  • March 18, 2008 at 3:35 am

    #71287

    I have created a DSN (ODBC). However I am unable to perform the same.

    Below is the query

    INSERT OPENQUERY (mysybase, 'SELECT name FROM mytestdb.table1')

    VALUES ('abc')

    Whenever I run this query the system lets me add a single transaction but if I run t he query a second time I get an out of memory exception. To resolve the same I need to restart the MS SQL Server before I can insert another row. Please suggest where am I going wrong.

    Below is a trace of the tasks that I am performing.

    SELECT @@version

    --Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38

    --Copyright (c) 1988-2003 Microsoft Corporation Enterprise Evaluation Edition on

    --Windows NT 5.1 (Build 2600: Service Pack 2)

    GO

    -- Adding linked server:

    exec sp_addlinkedserver @server = 'Linked' ,

    @srvproduct = 'Sybase',

    @provider = 'MSDASQL',

    @datasrc = 'sybase' -- dsn (Control Panel)

    GO

    -- Adding linked server login:

    sp_addlinkedsrvlogin @useself='f alse ', @rmtsrvname = 'Linked',

    @rmtuser = 'sa',

    @rmtpassword = ''

    GO

    EXEC sp_linkedservers

    GO

    SELECT *

    FROM OPENQUERY(Linked, 'Select * from mysybase..emp')

    GO

    INSERT OPENQUERY (Linked, 'SELECT eno,ename FROM mysybase..emp WHERE 1=2') VALUES (4,'Tom')

    (Both tables have the same structure)

    Please do needful.

  • March 25, 2008 at 8:19 am

    #793515

    Try playing around with SET XACT_ABORT { ON | OFF }

    I had to do this when I was using a linked server to AS400 a long time ago. I can't remember whether I turned it ON or OFF, but I was having the same problem as you.

    Norman Kelm

    DTS Package Search

    http://www.dtspackagesearch.com/

  • March 25, 2008 at 11:28 pm

    #793857

    Thanks Norman Kelm

    But i am facing problem after

    SET XACT_ABORT OFF

    or

    SET XACT_ABORT ON

    Error remains the same:

    OLE DB provider 'MSDASQL' reported an error. The provider ran out of memory.

    [OLE/DB provider returned message: Not enough storage is available to complete this operation.]

    OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x8007000e: The provider ran out of memory.].

    Please anyone help me.

    Regards,

    Abhishek

  • March 27, 2008 at 8:32 am

    #794627

    Your Linked Server configuration seems to be configured correctly.

    There is a similar error when using SQL Server to an Oracle datasource at:

    BUG: Out of Memory Error Message Occurs When You Query an ORACLE Linked Server that has a Numeric Column

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

    You may need to use a different version of the Sybase ODBC driver, or a Sybase ODBC driver from another vendor/source.

    Hope This Helps,

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

  • March 27, 2008 at 10:11 pm

    #794980

    Hi Damon Wilson,

    Thanks i ll try the same

    regards,

    Abhishek

  • March 28, 2008 at 7:36 am

    #795165

    Abhishek,

    Please keep us posted if you do or do not find a solution. Hopefully you can get a working set of drivers so that you can access your data and get to use your SQL skills.

    Regards,

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

Viewing 6 posts - 1 through 5 (of 5 total)

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