Sql server reporting services connected to SQL Anywhere database (ASA or ASE)

Question

Nube question:

Is there any way to connect Sql server 2008 reporting services to a sybase SQL Anywhere database (ASA or ASE)? (Maybe ODBC ...)

If so, have you done it? kindly share experiences and any notable considerations. thanks

Answer 1

I have had success setting up the Data Source Type (DPE) as OLEDB and using the ASA 9.0 OLEDB provider. The connection string editor in VS2008 does not allow me to create a connection string that works, so I just type in the entire connection string as follows:

Provider=ASAProv;Eng=[ASAInstanceName];Dbn=[ASADatabase];Uid=[ASAUser];Pwd=[ASAPwd];Links=tcpip(host=[servername])

Replace the the portions with square brackets with the values that are appropriate for your installation. The square brackets should NOT be in your final connection string and quotes are not need either. The links statement at the end is probabaly not necessary if the database is on your local machine. Even though I included the database username and password, it didn't seem to be used. I also had to set them in the DataSource Credentials. When deployed to SSRS, I also set them in the DataSource to be saved on the server.

I've noticed that performance is VERY slow when previewing the report in local mode in VS 2008. Instead, I have started deploying my report everytime I want to "preview" it so I can look at it on the SSRS server through a browser. Performance is much better that way.

I am looking for a BETTER solution than what I've described as I've quickly found that multiple-value parameters are not supported with this type of connection to ASA. If you find a better solution, perhaps using the ASA ADO.NET Provider, please post. From what I've found though, there is no DPE that exists for ASA.

Answer 2

thanks for the info, ill look into the ASA ADO.NET Provider and see what i can find.