I need to query a MSSQL 2008 db for 4 columns of data where one column (an ID) is used to join a number of tables from a Sybase ASE 15 database then output all 4 columns from the SQL query with a number of columns from 5 other tables in Sybase.
Currently we query SQL and output to a csv, then load that data to an Access db. Then use that to Access table to query the Sybase tables using a join on the ID from the SQL query. I need to take Access out of the equation and have all this run either in MSSQL or Sybase and have that triggered by a SSRS report to display the final Sybase query in a SSRS report.
I am able to make a linked server from SQL to Sybase or vise versa, and am able to use openquery from SQL to query Sybase. How do I use my results from the SQL side to then query the Sybase side, I need the small subset of IDs from the SQL query to pull from the Sybase tables but cannot seem to get the SQL results to the Sybase side in a temp table or anything to then query from Sybase with the openquery call.
Is my only option to use a SQL bcp command to output the query to a csv file then use a separate bcp (Sybase version) to load the SQL csv to Sybase then return the results? If so how can that be done from SSRS? What are my options here, am I missing an opportunity?!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.