0

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?

0

NOTE: I work with Sybase ASE, including working with proxy tables and interserver communications/queries; I do not work with MSSQL or SSRS; so fwiw ...

If the MSSQL data is driving the main query then I'm guessing 1 of 2 scenarios are possible with normal interserver communications:

  1. from MSSQL: for each MSSQL row of interest, perform an interserver join with the remote Sybase tables; the overhead for performing a large number of interserver calls could add up quickly

  2. from Sybase: pull all MSSQL rows of interest in one interserver request, then with the MSSQL data set residing in Sybase conduct the joins with the local ASE tables; the overhead for a single interserver join is going to be much less than the overhead for scenario #1, definitely much faster than the current method of using Access as an intermediary stopping point, and likely faster than any bcp-based operation)

NOTE: While newer versions of ASE have the ability to push/pull parts of interserver queries to/from remote servers, I'm not sure if/how this feature works when the remote server is a non-ASE RDBMS (eg, MSSQL).

For scenario #2 you have a couple options, all based on having a proxy table defined in ASE that references the remote MSSQL table:

  1. perform a standalone query to pull the MSSQL data into a local ASE table (could be a permanent or #temp table), eg, select/into or insert/select, then perform your local query using this MSSQL-populated table to drive the query

  2. reference the proxy table in your multi-table query, and if the optimizer has enough info to properly cost the join it will hopefully perform an initial/single pull from the proxy table (most likely into a local worktable) and then use that data to drive the rest of the local joins

4
  • If I'm understanding correctly your saying perform the MSSQL query in Sybase and save the results locally in Sybase to then do my join. This will definitely be better as the Sybase side is dealing with significantly more data. Can you direct me to an example of hitting MSSQL from Sybase I've only every gone the other way with OpenQuery. – S.G. Jun 23 '17 at 14:50
  • ah, apologies, from your original post - I am able to make a linked server from SQL to Sybase or vise versa - I thought this meant you had an active connection from Sybase to MSSQL; as I haven't worked with MSSQL I can only suggest: 1) Sybase ASE/CIS User Guide which mentions needing to setup a DirectConnect server to act as intermediary from ASE to MSSQL or 2) running some google/SE searches for instructions on setting up a proxy from Sybase ASE to MSSQL – markp-fuso Jun 23 '17 at 15:36
  • if you can push data from MSSQL to Sybase ASE (eg, write to a permanent table in tempdb), you could then log out of MSSQL, log into Sybase ASE and run your query(s) in ASE; this'll still be faster (and less moving parts) than using Access, it's basically the same thing as bcp out/in but without the extra overhead of using bcp, and of course it also eliminates any excessive overhead for interserver joins; not perfect, but probably the next easiest to implement if your ASE DBA has issues/concerns with setting up the proxy from ASE to MSSQL – markp-fuso Jun 23 '17 at 17:44
  • I ended up having to use MSSQL to Sybase ASE link because our Sybase DBA's did not want to include a link from Sybase (I wasn't able to find the "how to do this" anyhow). I query in SQL then push to a Sybase temp table and then just query the Sybase tables after. Not quite your solution 1 but it eliminates the cross server join and bcp's – S.G. Jun 30 '17 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.