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

Question

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

g.abhishek

SSC Veteran

Points: 257
More actions
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.

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

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

Norman Kelm SSCommitted Points: 1922 More actions · Answer 1

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/

g.abhishek SSC Veteran Points: 257 More actions · Answer 2

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

Key DBA SSCertifiable Points: 6029 More actions · Answer 3

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

g.abhishek SSC Veteran Points: 257 More actions · Answer 4

Hi Damon Wilson,

Thanks i ll try the same

regards,

Abhishek

Key DBA SSCertifiable Points: 6029 More actions · Answer 5

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