SQL query output to Sybase temp table
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