SQL linked server, open query using variable

Question

SELECT * INTO #C_INFO FROM OPENQUERY(ORACLE_CMIDW1,' SELECT A.CID, A.ANO,
A.COMP_REVD_DATE, A.REVIEW_COMP_DATE, A.ISSUE, B.ENT_ID, A.TYPE_ID FROM XXX
B INNER JOIN YYY A ON B.ANO = A.ANO WHERE A.REVIEW_COMP_DATE Between
''2012-03-01'' And ''2013-10-31'' AND Not A.ISSUE = 110 AND A.TYPE_ID = 1
and B.ENT_ID In (2,3) ')

Above sql works perfect but i dont want to hard code the dates. That's the reason trying to use variable. date format (yyyy-mm-dd, '2013-03-01'), anything other than this date format, Oracle doesn't execute the sql.

Is it possible to do this, If so please re write the whole sql using variable so that i can see how it can be done.

Thanks!

I dont any error, i want to replace those hard coded date(s) using variables. If i can do it, shoot me the query so that can i see how it can be done. — user1810575
– user1810575, Commented Jul 1, 2013 at 18:40
Very closely related; possibly duplicate of: stackoverflow.com/questions/3378496 — NateJ
– NateJ, Commented Aug 26, 2016 at 22:13

Registered User · Accepted Answer · 2013-07-01 18:44:18Z

8

The below code should do the trick. You can't pass a variable to an OPENQUERY. However, you can dynamically generate the code that you will execute via OPENQUERY.

DECLARE @StartDate DATETIME = '2012-03-01';
DECLARE @EndDate DATETIME = '2013-10-31';

DECLARE @SqlCommand NVARCHAR(MAX) = N'SELECT * 
INTO #C_INFO 
FROM OPENQUERY(ORACLE_CMIDW1,
    ''SELECT A.CID, A.ANO, A.COMP_REVD_DATE, A.REVIEW_COMP_DATE, A.ISSUE, B.ENT_ID, A.TYPE_ID
    FROM XXX B
    INNER JOIN YYY A
        ON B.ANO = A.ANO
    WHERE A.REVIEW_COMP_DATE BETWEEN ''''' + CONVERT(CHAR(10), @StartDate, 120) + ''''' AND ''''' + CONVERT(CHAR(10), @EndDate, 120) + '''''
    AND Not A.ISSUE = 110 AND A.TYPE_ID = 1 and B.ENT_ID In (2,3)'');';

PRINT @SqlCommand

EXEC sp_ExecuteSQL @SqlCommand;

On a separate note, I would recommend looking into replacing your linked server query with a SSIS package that loads the data. Although the above code works, this is not the most efficient way to transfer data from an Oracle server to SQL Server.

answered Jul 1, 2013 at 18:44

Registered User

8,4238 gold badges54 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NateJ Over a year ago

Supported by a KB article from MS themselves: support.microsoft.com/en-us/kb/314520

Vlad G. · Accepted Answer · 2013-07-01 18:52:14Z

0

This should do it:

DECLARE @startDate DATETIME = '2013-06-01'
DECLARE @endDate DATETIME  = '2013-07-01'

INSERT INTO #C_INFO 
EXECUTE('SELECT A.CID, A.ANO,
A.COMP_REVD_DATE, A.REVIEW_COMP_DATE, A.ISSUE, B.ENT_ID, A.TYPE_ID FROM XXX
B INNER JOIN YYY A ON B.ANO = A.ANO WHERE A.REVIEW_COMP_DATE Between ? And ? 
AND Not A.ISSUE = 110 AND A.TYPE_ID = 1 AND B.ENT_ID In (2,3) ', @startDate , @endDate) AT ORACLE_CMIDW1

answered Jul 1, 2013 at 18:52

Vlad G.

2,16716 silver badges12 bronze badges

Collectives™ on Stack Overflow

SQL linked server, open query using variable

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related