Sybase Linked Server

Question

Sybase Linked Server

runelords

Ten Centuries

Points: 1275
More actions
December 27, 2005 at 11:20 am

#115793

I need to setup a linked server to a Adaptive Server Anywhere 8.0, does any one have an example of how to set this up in a SQL Server 2000 environment?

Arthur Lorenzini

Viewing 12 posts - 1 through 11 (of 11 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

See if this helps: http://support.microsoft.com/default.aspx?scid=kb;EN-US;280102

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

runelords Ten Centuries Points: 1275 More actions · Answer 2

I went through the MS article and performed the following operations:

exec sp_addlinkedserver @server = 'HC90' ,

@srvproduct = 'Sybase',

@provider = 'MSDASQL',

@datasrc = 'HC90CentralProd'

GO

-- Adding linked server login:

sp_addlinkedsrvlogin @rmtsrvname = 'HC90',@useself='false ',

@rmtuser = 'dba',

@rmtpassword = 'sql'

This part seemed to work. When I click on the linked server name in Enterprise Manager I see the tables and views from the Sybase server.

but when I try to run a Select statement against it:

SELECT * FROM HC90.HC90_Central_Prod.DBA.dbe_contractor

I get the following error:

Server: Msg 7399, Level 16, State 1, Line 1

OLE DB provider 'MSDASQL' reported an error. The provider does not support the necessary method.

[OLE/DB provider returned message: Method is not supported by this provider.]

[OLE/DB provider returned message: [Sybase][ODBC Driver]Driver not capable]

OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBSchemaRowset::GetRowset returned 0x80040e53: The provider does not support the necessary method.].

I am not sure what to do now.

Arthur Lorenzini

RandyFerris Old Hand Points: 373 More actions · Answer 3

I have not done this recently but I believe that I had to install the sybase ODBC driver on my SQL Server machine. Some information might be helpful at:

http://www.sybase.com/detail?id=1008930

runelords Ten Centuries Points: 1275 More actions · Answer 4

I have already installed the Sybase client on the SQL Server. that was how I was able to created DSN using Adaptive Server Anywhere 8.0 provider.

Arthur Lorenzini

Hayduke Old Hand Points: 303 More actions · Answer 5

How was this issue resolved? I'm having the exact same issue, where I've created the linked server for Sybase, can see the Sybase tables and views via Enterprise Manager, but get the same error as listed above when executing a SELECT statement on the linked server via Query Analyzer. I already have the Sybase client installed on the MSSQL server.

RandyFerris Old Hand Points: 373 More actions · Answer 6

I don't know if he solved the problem, but I would try OpenQuery such as;

DBCC TRACEON(8765)

select * from openquery(MySQLLocal, 'Select * from authors;')

This is an example for a MySQL linked server. Check BOL for more details.

Hayduke Old Hand Points: 303 More actions · Answer 7

I found that I can get a SELECT statement to execute if I don't specify the catalog:

SELECT * FROM MySybase..dba.Vendors

I don't quite understand why that works, but I'm glad it does...

Alexis Lopez SSC Enthusiast Points: 112 More actions · Answer 8

Hi

I´ve a question, i need to connect Pervasive V8.5 with SQL Server 2000 using adlinkserver

in my Server installed the Pervasive and created an ODBC and my configuration from SQL server is:

Provider Name = Pervasive.SQL V8 OLE DB Provider

Product Name = Pervasive

Data Source = Name_ODBC

String Provider =

Server = NameServer

Catalog =

and then when i want to open a table show me an error:

ERROR 7311: No se puede obtener el conjunto de filas de esquema para el proveedor OLE DB 'UNKNOWN'. El proveedor es compatible con la interface pero devuelve en codigo de error cuando se utiliza

Traza de error OLE DB [OLE/DB Provider 'UNKNOWN' IDBScjemaRowSet::GetRowset returned 0x80040155: ].

Wath can i Doo????

Help mi Pls..

😀

manu k s SSC Rookie Points: 35 More actions · Answer 9

Steps to Connect Pervasive from SQL

(1) Make The DSN for pervasive DB

(2) Run the below SP ‘AddPervasiveDB’ in sql by providing DSN name

create proc AddPervasiveDB

@DSN varchar(15)

as

if not exists (select '*' from sys.servers where data_source = @DSN)

begin

exec sp_addlinkedserver @server = 'Pervasive' ,

@srvproduct = 'Pervasive',

@provider = 'MSDASQL',

@datasrc = @DSN

end

Else

Print 'The Connection already exists'

Eg : EXEC AddPervasiveDB ‘test’

jim.gaull Newbie Points: 5 More actions · Answer 10

Not sure if this helps but I can't directly query any Sybase tables that have an underscore in their name but I found a work around.

I read a blog that mentioned that you can use OpenQuery. Here's a link:

Good luck to all having this issue!

bluepaperbags SSCommitted Points: 1738 More actions · Answer 11

bluepaperbags

SSCommitted

Points: 1738

June 11, 2010 at 10:07 am

#1179296

anyone resolved this? i neeed help