Sql-server – SQL query output to Sybase temp table

sql-server-2008sybase-asetemporary-tables

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?

Best Answer

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:

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
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:

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
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

Related Solutions

Sql-server – Performance problem caused by joining with the result of a scalar function

Exactly as expected.

A scalar UDF is a black box to the optimiser: no indexes can be used, the cost can't be worked out correctly.

And if the scalar UDF has table access, then you have running a CURSOR (bad in SQL Server) to do a lookup per row: it isn't a set based operation

To fix it, don't use a UDF this way. It can be written as a JOIN. There is no hint or magic

Edit: remove the UDF. For each row in ratings your query Ableitungen. It's exponential

And I've move the filters into the JOINs

With ratings as
(
    select
        ROW_NUMBER() Over (ORDER BY SEDate) lfd, 
        SEDate, 
        BBCode 
    from  PPV
        join BB on BBRefnr = SEBBRefnr
    where SEHORefnr = @HORefnr 
    and SEBBRefnr > 0
)
Select 
    l.SEDate,
    l.BBCode,
    A.Code,
    D.DTRefnr
from 
    ratings l
    left join 
    ratings r on l.lfd = r.lfd + 1
    JOIN 
    Ableitungen A ON A.Value = l.BBCode
    join
    DT d on A.code = d.DTCode and d.DTDTKRefnr = A.DTZ
where
    A.DTZ = @fDtz
    AND 
   (l.BBCode <> r.BBCode or r.BBCode is null)
order by
    SEDATE;

Sql-server – Pull Access schema into a SQL Server table

The way we solved this was to use a template db to auto create views against the access linked tables. Each view was created as

CREATE VIEW AS 
SELECT *
  FROM LinkedServer...Table

Once these views had been created we could pull the column listing from sys.columns joined over to sys.views.

SELECT v.name, c.name
  FROM sys.columns c 
       JOIN sys.views v ON c.object_id = v.object_id 
 WHERE v.name = @viewname

We could then insert this into the table we needed to work with.

Best Answer

Related Solutions

Sql-server – Performance problem caused by joining with the result of a scalar function

Sql-server – Pull Access schema into a SQL Server table

Related Question