SQL query output to Sybase temp table

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?

asked Jun 22 '17 at 16:30

S.G.

bumped to the homepage by Community♦ 4 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment |

asked Jun 22 '17 at 16:30

S.G.

bumped to the homepage by Community♦ 4 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment |

asked Jun 22 '17 at 16:30

S.G.

sql-server-2008 temp-tables sybasease

asked Jun 22 '17 at 16:30

S.G.

asked Jun 22 '17 at 16:30

S.G.

asked Jun 22 '17 at 16:30

S.G.

asked Jun 22 '17 at 16:30

S.G.

asked Jun 22 '17 at 16:30

S.G.

bumped to the homepage by Community♦ 4 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

bumped to the homepage by Community♦ 4 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment |

1 Answer
1

active

oldest

votes

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

answered Jun 22 '17 at 18:49

markp

2,0821214

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

add a comment |

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f177030%2fsql-query-output-to-sybase-temp-table%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

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)

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

answered Jun 22 '17 at 18:49

markp

2,0821214

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

add a comment |

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)

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

answered Jun 22 '17 at 18:49

markp

2,0821214

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

add a comment |

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)

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

answered Jun 22 '17 at 18:49

markp

2,0821214

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)

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

answered Jun 22 '17 at 18:49

markp

2,0821214

answered Jun 22 '17 at 18:49

markp

2,0821214

answered Jun 22 '17 at 18:49

markp

2,0821214

answered Jun 22 '17 at 18:49

markp

2,0821214

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

add a comment |

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

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

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Database Administrators Stack Exchange!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Vryjdfkk