SQL linked server using ASE ODBC connection
Posted: Tue 27 Sep 2016 14:17
I'm having some issues using the ASE ODBC driver as a linked server in SQL 2014.
I created and tested the 64 bit ODBC, which works fine.
I created the linked server using the Microsoft OLE DB provider for ODBC drivers.
In the linked server view, i can see the Sybase catalogs and tables correctly.
Queries like this work for certain tables
select * from openquery(sybase, 'select * from claims_detail ')
For some tables, i get the following
OLE DB provider "MSDASQL" for linked server "sybase" returned message "[Devart][ODBC][Adaptive Server Enterprise]Unknown Sql type 0".
Msg 7350, Level 16, State 2, Line 4
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "sybase".
This seems to occur when the sybase table has a column of type tinyint.
I cannot select any single column from a table that has a type of tinyint, regardless of type
The sybase table I am selecting from looks as follows
CREATE TABLE dbo.coil_status
(
coil_status CHAR (2),
description VARCHAR (40),
coil_level TINYINT,
coil_a TINYINT,
coil_b TINYINT,
coil_c TINYINT,
add_datetime DATETIME,
add_user_id INT,
change_datetime DATETIME,
change_user_id INT,
class CHAR (10),
CONSTRAINT coil_status PRIMARY KEY (coil_status)
);
if I try the following
select * from openquery(sybase, 'select coil_status from coil_status ')
I get the following error
OLE DB provider "MSDASQL" for linked server "sybase" returned message "[Devart][ODBC][Adaptive Server Enterprise]Unknown Token 45".
Msg 7320, Level 16, State 2, Line 4
Cannot execute the query "select coil_status from coil_status " against OLE DB provider "MSDASQL" for linked server "sybase".
But this works
select * from openquery(sybase, 'select cast(coil_status as varchar) from coil_status ')
I experience this behavior whenever there is a column of type tinyint in the table being selected. The only way to select from a table with a tinyint column is to cast each column to its specific type.
I created and tested the 64 bit ODBC, which works fine.
I created the linked server using the Microsoft OLE DB provider for ODBC drivers.
In the linked server view, i can see the Sybase catalogs and tables correctly.
Queries like this work for certain tables
select * from openquery(sybase, 'select * from claims_detail ')
For some tables, i get the following
OLE DB provider "MSDASQL" for linked server "sybase" returned message "[Devart][ODBC][Adaptive Server Enterprise]Unknown Sql type 0".
Msg 7350, Level 16, State 2, Line 4
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "sybase".
This seems to occur when the sybase table has a column of type tinyint.
I cannot select any single column from a table that has a type of tinyint, regardless of type
The sybase table I am selecting from looks as follows
CREATE TABLE dbo.coil_status
(
coil_status CHAR (2),
description VARCHAR (40),
coil_level TINYINT,
coil_a TINYINT,
coil_b TINYINT,
coil_c TINYINT,
add_datetime DATETIME,
add_user_id INT,
change_datetime DATETIME,
change_user_id INT,
class CHAR (10),
CONSTRAINT coil_status PRIMARY KEY (coil_status)
);
if I try the following
select * from openquery(sybase, 'select coil_status from coil_status ')
I get the following error
OLE DB provider "MSDASQL" for linked server "sybase" returned message "[Devart][ODBC][Adaptive Server Enterprise]Unknown Token 45".
Msg 7320, Level 16, State 2, Line 4
Cannot execute the query "select coil_status from coil_status " against OLE DB provider "MSDASQL" for linked server "sybase".
But this works
select * from openquery(sybase, 'select cast(coil_status as varchar) from coil_status ')
I experience this behavior whenever there is a column of type tinyint in the table being selected. The only way to select from a table with a tinyint column is to cast each column to its specific type.