Linking SYBASE with SQL Server 2000

[Steve Grose]

Hi Everyone,

I'm 7 hours into trying to get SYBASE linked into SQL Server 2000. Here's my
config and what I've done thus far.

I'm running Windows 2000 Advanced server and SQL Server 2000. I'm trying to
connnect to Sybase ASE 11.9.2 on an IBM RS/6000 AIX system.

I've installed and configured dsedit and the sybase ODBC driver from SYBASE.
These drivers are several years old. I downloaded Merant's datadirect
drivers, version 3.70. I created DSN to the sybase database. Both drivers
pass the DSN connection test.

From Query Analyzer using the Merant driver:

dbcc traceon (7300)
go
select count(*) from sybase.ZZZ.XX.policies

DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'.
A four-part name was supplied, but the provider does not expose the
necessary interfaces to use a catalog and/or schema.
OLE DB error trace [Non-interface error: OLE DB provider MSDASQL
returned an incorrect value for
DBPROP_QUOTEDIDENTIFIERCASE which should be one of
DBPROPVAL_IC_LOWER,DBPROPVAL_IC_MIXED,DBPROPVAL_IC_SENSITIVE, and
DBPROPVAL_IC_UPPER].
OLE DB error trace [Non-interface error].

This works:

dbcc traceon (7300)
go
select * from openquery(sybase,'select count(*) from ZZZ.XX.policies')

From query Analyzer using the sybase driver:

dbcc traceon (7300)
go
select count(*) from sybase2.ZZZ.XX.policies

DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: OLE DB provider MSDASQL returned
an incorrect value for DBPROP_QUOTEDIDENTIFIERCASE which should be one of
DBPROPVAL_IC_LOWER,DBPROPVAL_IC_MIXED,DBPROPVAL_IC_SENSITIVE, and
DBPROPVAL_IC_UPPER].
OLE DB error trace [Non-interface error: OLE DB provider MSDASQL returned
an incorrect value for DBPROP_QUOTEDIDENTIFIERCASE which should be one of
DBPROPVAL_IC_LOWER,DBPROPVAL_IC_MIXED,DBPROPVAL_IC_SENSITIVE, and
DBPROPVAL_IC_UPPER].
OLE DB error trace [Non-interface error: OLE DB provider MSDASQL returned
an incorrect value for DBPROP_QUOTEDIDENTIFIERCASE which should be one of
DBPROPVAL_IC_LOWER,DBPROPVAL_IC_MIXED,DBPROPVAL_IC_SENSITIVE, and
DBPROPVAL_IC_UPPER].
OLE DB error trace [Non-interface error: OLE DB provider MSDASQL returned
an incorrect value for DBPROP_QUOTEDIDENTIFIERCASE which should be one of
DBPROPVAL_IC_LOWER,DBPROPVAL_IC_MIXED,DBPROPVAL_IC_SENSITIVE, and
DBPROPVAL_IC_UPPER].
OLE DB error trace [Non-interface error: Column 'prior_benefits'
(compile-time ordinal 5) of object 'ZZZ.XX.policies' was reported to have a
DBTYPE of 131 at compile time and 6 at run time].

This works:
dbcc traceon (7300)
go
select * from openquery(sybase2,'select count(*) from site1.ff.policies')

Thanks for any help you may be able to provide.

Steve Grose
sgr...@ipsrx.com

[Umachandar Jayachandran]

Please make sure that the drivers support the required interfaces for
distributed queries to work. These are documented in BOL.
You can also try this with the existing setup:

-- Run this to find out the valid catalogs
exec sp_catalogs 'sybase'

If there are none listed, then you have to omit the catalog name from
the 4-part names else specify the valid one. You can also use the OLEDB sps
"sp_tables_ex" etc to determine the same for the tables. You can see these
in SQL Enterprise Manager also.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

[Steve Grose]

Well I found my answer to my questions on Merant's web site:

http://knowledgebase.merant.com/cgi-bin/webcgi.exe/,/?Session=530353,U=1,ST=
21,N=0021,K=21550,SXI=4,Case=obj(29278),Note=29278,VARSET=CASEID:29278

This the above link states that you can't use the most current version of
the product 3.70SP1. You have to revert back to 3.60. Which does alleviate
by problem with Invalid use of schema and/or catalog for OLE DB provider
'MSDASQL'.

But now I'm consistantly getting "OLE DB provider 'MSDASQL' supplied
inconsistent metadata for a column". Once again I found the answer on
Merant's web site:
http://knowledgebase.merant.com/cgi-bin/webcgi.exe/,/?Session=530353,U=1,ST=
21,N=0021,K=21550,SXI=4,Case=obj(31038),Note=31038,VARSET=CASEID:31038

The above linke states to use this method "SELECT * FROM OPENQUERY
(<LinkedServerName>, "SELECT * FROM <TableName>")"

Very disappointed that I can't link in Sybase and use it correctly. I was
hoping for as much success as I've had linking tables in Access 2000.

Hopes this helps someone else out.

"Steve Grose" <sgr...@ipsrx.com> wrote in message
news:ucLPtedIBHA.1668@tkmsftngp04...