Is anyone using MS SQL Reporting Services to connect to SQL Anywhere?

[former_SQLA_member1694868]

We have a customer that wants to do this but is having trouble getting data across.

I'm not sure of the specifics of the problem yet, just checking if anyone is doing this successfully.

===============Edit==================== Still looking for anyone who has done this successfully, but the problem seems to be that when the MS Reporting tries to use a stored procedure it lists not only the actual parameters for the procedure but also all of the column headings as parameters and thus returns the error "Wrong number of parameters" even when these fake-parameters are set to null.

[VolkerBarth]

I haven't done so - but is this different from using MS SQL to access SQL Anywhere data (by means of SQL Server "Linked servers")? Cf. http://dcx.sybase.com/index.html#1200en/dbprogramming/pg-oledb-linked-servers.html. - Though Breck and me would claim that's faaaar easier to access MS SQL via SQL Anywhere than vice versa:(

[former_SQLA_member1694868]

I think so. It seems that the MS Report Builder will connect to the SQL Anywhere database correctly using ODBC, but that calls to the procedures fail with wrong number of parameters. Probably a quick fix for someone who has done it before, but they have not yet.

[VolkerBarth]

@Siger: IIRC, there have been fixes to MS Linked Server and their "EXEC" syntax in very current builds (don't know exactly which ones).

[VolkerBarth]

@Siger: Linked Servers use OLE DB, not ODBC, so I'm not sure whether this applies here.

[former_SQLA_member1694868]

Well done sir! Changing the connection to OLE DB made the procedure only ask for the 1 correct parameter and stopped asking for all of the column headings as parameters. Weird.

Thank you!

[former_SQLA_member1694868 · Accepted solution]

ODBC connection did not work in this case. When using the ODBC connection, the ReportBuilder incorrectly pulled in each column heading as a parameter which resulted in either "Parameter cannot be blank" errors from report builder or "Wrong Number of Parameters" from SQL Anywhere.

After changing the connection to OLE DB, the ReportBuilder pulled in only the valid procedure parameter.

[former_SQLA_member1694991 · Accepted solution]

I know it's five years later but THANK YOU!!!!! Switching to OLE DB solved it!

[Former Member]

You might have already found an answer but you can create reports in using SQL Server Reporting Services. I have been able to connect to SQL Anywhere 16 and retrieve data from the contacts table in the demo using the Report Builder 3. There is no problem. I am planning to post the details to my blog (http://hodentekmsss.blogspot.com).

Actually I had a post in my blog which I found http://hodentekmsss.blogspot.com/2013/08/report-builder-report-using-data-from.htmllink text

Unfortunately the image upload here is not working although it is reaching out to the file. I am using IE 11.