working opensql query from a linked db

Question

Post reply

working opensql query from a linked db

mystrymaster

SSC-Addicted

Points: 405

June 28, 2006 at 12:36 pm

#173289

my mssql server has a linked table to an oracle db

I have this stored procedure

DECLARE @thisSQL varchar(3000)
SET @thisSQL = 'Select *
FROM OPENQUERY(MIDCAD, ''SELECT to_char(csp.SC_ID) as Call, to_char(csp.SC_PROB_CD) as Tcode,
to_char(css.SC_STS_RSN_CD) AS Reason, substr(crf.SVC_REGN_ID, 1, 1) AS center
FROM CAD.SC_PROBLEM csp  INNER JOIN CAD.SC_STATUS css ON csp.SC_DT = css.SC_DT AND csp.SC_ID = css.SC_ID
INNER JOIN CAD.REGION_FACL crf ON css.SC_FACL_ID = crf.SVC_FACL_ID
WHERE (csp.SC_PROB_CD=''''3'''' Or csp.SC_PROB_CD=''''3B'''' Or csp.SC_PROB_CD=''''6'''')
AND csp.SC_DT >=  TO_DATE(TO_CHAR(sysdate, ''''YYYY-MM-DD''''), ''''YYYY/MM/DD'''') AND 
csp.SC_DT <  TO_DATE(TO_CHAR(sysdate+1, ''''YYYY-MM-DD''''), ''''YYYY/MM/DD'''')
AND SC_PROB_NR=''''1''''  AND css.SC_STS_UPD_CD=''''CL'''' AND (css.SC_STS_RSN_CD=''''BS'''' OR css.SC_STS_RSN_CD=''''WB'''')
ORDER BY csp.SC_ID'')'
EXEC (@thisSQL)

I know that works and it pulls the results I need.

However I need to now take those results and store them locally for archiving.

I was thinking I would create a temp table do all my adding and averaging and then enter the aggregated data in my table on my local machine

However I can't get this data into a temp table..

Any help?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

Why not just bring the data over with DTS. It's much easier and you dont' have to worry about being in Single quote hell. Just create a connection onject to your Oracle Data, and one to the appropriate SQL server db. Then create a transform task to brign the data over. You could theoetically do all of your averaging and such in your query as you bring the data across before it ends up in your SQL db.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Luke L SSC-Dedicated Points: 35547 More actions · Answer 2

Alternantively if you really want to read through all of those signle quotes, you can still use your open query, but use a

Select * INTO #mytable FROM OPENQUERY(...

To help us help you read this[/url]For better help with performance problems please read this[/url]

mystrymaster SSC-Addicted Points: 405 More actions · Answer 3

Luke first off thanks for 2 different replies to solve my issue -

First your entire first post makes a little sense to me however I am not the admin over the db and do not have permission to change anything at all between the 2 servers so if what you are asking me to do can be done by someone who is only bound to one table in the db then please elaborate a bit for me

otherwise

the second post is what I found looking around however how I would I do that full process I tried just adding the

INTO #mytable

However I am being told Invalid object name '#mytable'

I try creating the table mytable like

CREATE Table #mytable(

Call int,

TCODE varchar(3),

REASON varchar(3),

center varchar(1))

andI get blank results when the full thing looks like this

DECLARE @thisSQL varchar(3000)

CREATE Table #mytable(

Call int,

TCODE varchar(3),

REASON varchar(3),

center varchar(1))

SET @thisSQL = 'Select * INTO #mytable

FROM OPENQUERY(MIDCAD, ''SELECT to_char(csp.SC_ID) as Call, to_char(csp.SC_PROB_CD) as Tcode,

to_char(css.SC_STS_RSN_CD) AS Reason, substr(crf.SVC_REGN_ID, 1, 1) AS center

FROM CAD.SC_PROBLEM csp INNER JOIN CAD.SC_STATUS css ON csp.SC_DT = css.SC_DT AND csp.SC_ID = css.SC_ID

INNER JOIN CAD.REGION_FACL crf ON css.SC_FACL_ID = crf.SVC_FACL_ID

WHERE (csp.SC_PROB_CD=''''3'''' Or csp.SC_PROB_CD=''''3B'''' Or csp.SC_PROB_CD=''''6'''')

AND csp.SC_DT >= TO_DATE(TO_CHAR(sysdate, ''''YYYY-MM-DD''''), ''''YYYY/MM/DD'''') AND

csp.SC_DT < TO_DATE(TO_CHAR(sysdate+1, ''''YYYY-MM-DD''''), ''''YYYY/MM/DD'''')

AND SC_PROB_NR=''''1'''' AND css.SC_STS_UPD_CD=''''CL'''' AND (css.SC_STS_RSN_CD=''''BS'''' OR css.SC_STS_RSN_CD=''''WB'''')

ORDER BY csp.SC_ID'')'

EXEC (@thisSQL)

SELECT * FROM #mytable

any help would be greatly appreciated.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 4

Theoretically the SELECT INTO Sytax should create the new table for you and then populate with whatever records you need. Since you already know what columns should be in your table you could use this synatax instead. I typically only use SELECT INTO when I'm being lazy...

DECLARE @thisSQL varchar(3000)

CREATE Table #mytable(

Call int,

TCODE varchar(3),

REASON varchar(3),

center varchar(1))

SET @thisSQL = 'INSERT INTO #mytable SELECT *