SQLServer and SQL Anywhere Integration

[raydavis]

Relatively new to to SQL Anywhere here and a complete day of
searching hasn't led me to any useful answers so I pose this
one.

I need to setup a linked server in MSSQL to point to
SQLAnywhere Databases (versions 9, 10 and 11). The
documentation I found describe want to do but leave out some
very pertinent details, as in....

Product Name
Data Source
Provider String

For the SASAP 9.0 provider and SQL Anywhere OLE DB Provider
11

I'd prefer to use the Sybase providers but in a pinch I'd
use just the Microsoft providers for ODBC.

In short the settings for the general tab in the linked
server aren't documented any place I've found thus far.

Thanks in advance.

[Jeff Albion [Sybase iAnywhere]]

Ray,

This setup is also mentioned in our documentation for 11:

http://dcx.sybase.com/1100en/dbprogramming_en11/supported-interfaces-oledb.html

---

A brief warning regarding Calvin's doc:

...
@provider = 'MSDASQL',
...

The default Microsoft OLEDB provider (MADASQL) may not support all of
SQL Anywhere's syntax (Depending on what you're doing, you may see
errors like 'Syntax not supported'). You should specify the 'SAOLEDB'
provider instead. (See:
http://dcx.sybase.com/1100en/dbprogramming_en11/connecting-ado-newaspen.html
).

Regards,

--
Jeff Albion, Sybase iAnywhere

iAnywhere Developer Community :
http://www.sybase.com/developer/library/sql-anywhere-techcorner
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
SQL Anywhere Patches and EBFs :
http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere&timeframe=0

[raydavis]

I added a sa login to your sample database with all policies
added.

I create a linked server named

TEST
Other data source selected
Provider: SQL Anywhere OLE DB Provider 11
Product Name: Testing (my ODBC DSN)
Data Source: Testing (my ODBC DSN) (Can't find the database
without this despite your documentation).

This creates the linked server entry.
I then refresh the linked server, click on the + to see the
catalogs, click on that to see the 'system catalogs' and the
default database, click on the plus to expand the default
entry (i'm guessing this is the catalog entry for your
sample database).

Click to expand the tables and I recieve the following
error.
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for ole db
provider "SAOLEDB.11" for linked server test. The provider
supports the interface, but returns a failure code when
used.

Thus I can't see the tables in the catalog.

[Jeff Albion [Sybase iAnywhere]]

Ray,

Ray Davis wrote:
> Data Source: Testing (my ODBC DSN) (Can't find the database
> without this despite your documentation).

I agree; this looks like a typo in the documentation. I believe the two
fields should be switched: "Product Name" is optional, "Data Source" is
mandatory.

> Click to expand the tables and I recieve the following
> error.
> Cannot obtain the schema rowset "DBSCHEMA_TABLES" for ole db
> provider "SAOLEDB.11" for linked server test. The provider
> supports the interface, but returns a failure code when
> used.

Right - we support dbo.sp_tables() call, but we're returning an error?
Which exact version of Microsoft SQL Server is this? (x86 or x64?)

Can you add "-zr SQL+HOSTVARS -zo rll.txt" to the server start-line and
post the file here? (Specifically the SQL we're executing that returns
an error, and the error).

[raydavis]

I've made some headway at least.
In both SQL2005 and SQL2008 I've done the following:

Created a Linked Server with properties:
Name=SybaseDemo
ProductName=Sybase
DataSource=SybaseDemo
DSN=SybaseDemo

the database has had an sa user added

Server Options RPC/RPC Out set to true.

The Allow InProcess option in the Provider for ASAProv.90
has been set.

In SQL2005, when trying to browse the tables I recieve an
error "Cannot obtain the schema rowset "DBSCHEMA_CATALOGS".

In SQL2008 I can see the tables, though when I try

SELECT * FROM OPENQUERY(sybasedemo, 'select * from
sales_order')
the result is

OLE DB provider "ASAProv.90" for linked server "sybasedemo"
returned message "Table 'sales_order' not found".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select * from
sales_order" for execution against OLE DB provider
"ASAProv.90" for linked server "sybasedemo".

alternately:
SELECT [id]
,[cust_id]
,[order_date]
,[fin_code_id]
,[region]
,[sales_rep]
FROM [SYBASEDEMO]..[DBA].[sales_order]
yeilds
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT "Tbl1001"."id"
"Col1003","Tbl1001"."cust_id"
"Col1004","Tbl1001"."order_date"
"Col1005","Tbl1001"."fin_code_id"
"Col1006","Tbl1001"."region" "Col1007","Tbl1001"."sales_rep"
"Col1002" FROM "DBA"."sales_order" "Tbl1001"" against OLE DB
provider "ASAProv.90" for linked server "SYBASEDEMO".

I'm really at a loss to explain what is occurring here.

[raydavis]

Current status:

OPENQUERY works, the 4 part select does not may or may not
be any issue, I've converted over to client/server
connections with pretty much the same results but I've
eliminated the single connection instance for testing which
is a relief.

I've taken all of this and now have applied what I've
learned to try to get the same level of access to a real
client database and I'm now recieving "Cannot initilized the
data source object", I suspect this is due to the
credentials I have for the client database do not have DBA
authority. Though it manifests differently when I remove
the permissions from the created user in the sample
database.