SQL SELECT * FROM OPENROWSET with Variable

Question

I am trying to pass a variable into a SELECT statement in OPENROWSET but I keep getting an error

DECLARE @dDateTIME DATE

SET @dDateTIME = (SELECT SalesDate FROM dbo.SalesDate)

INSERT INTO dbo.tblSales
SELECT * FROM OPENROWSET('MSDASQL', 'dsn=mydsn;uid=myid;pwd=mypwd;',
    'SELECT 
        ID,
        TranDate,
        ProductID,
        CostValue,
        SalesValue,
        QtySold,
    FROM tblSales WHERE TranDate='' + @dDateTIME + ''')

DECLARE @dDateTIME DATE
SET @dDateTIME = (SELECT SalesDate FROM dbo.SalesDate)

DECLARE @SQL NVARCHAR(1024) = 
 'SELECT 
        ID,
        TranDate,
        ProductID,
        CostValue,
        SalesValue,
        QtySold,
    FROM tblSales WHERE TranDate=''' + CAST(@dDateTIME AS VARCHAR(64)) + ''''

DECLARE @RunSQL NVARCHAR(max) 
SET @RunSQL=
    'SELECT * FROM OPENROWSET (''MSDASQL'', ''dsn=mydsn;uid=myid;pwd=mypwd;'',''EXEC @SQL'')'`

What syntax do I use to apply to @SQL?

The error I get is:

The error is :OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Error in predicate: TranDate = '(SELECT @dDateTIME)' "

WHAT is the error you're getting !?!?!?!?!? We can't read your screen - nor your mind.... you're going to have to tell us .... and while you're at it - please also tell us what database you're using (and which version); SQL is just the query language - not a database product.... — marc_s
– marc_s, Commented May 17, 2013 at 11:07
The database I am using is SQL Server 2008 r2 The database the dsn is connected to is Pervasive I know the error is being caused by the syntax I am using when passing the varible. If I type the datevalue into the WHERE statement it works ok. The error is :OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Error in predicate: TranDate = '(SELECT @dDateTIME)' ". — user2393602
– user2393602, Commented May 17, 2013 at 11:21

Community · Accepted Answer · 2017-05-23 11:45:17Z

1

Your variable is not being concatenated to the string (its ''' to close a string with a ') to correct this (and perform the necessary type conversion):

DECLARE @SQL NVARCHAR(1024) = 
 'SELECT 
        ID,
        TranDate,
        ProductID,
        CostValue,
        SalesValue,
        QtySold,
    FROM tblSales WHERE TranDate=''' + CAST(@dDateTIME AS VARCHAR(64)) + ''''

Additionally you cannot use an expression or variable with OPENROWSET so you are going to need to call it via EXEC()/sp_executeSQL, see; Using a Variable in OPENROWSET Query

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered May 17, 2013 at 11:20

Alex K.

177k32 gold badges276 silver badges299 bronze badges

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

1 Comment

user2393602 Over a year ago

DECLARE @dDateTIME DATE SET @dDateTIME = (SELECT SalesDate FROM dbo.SalesDate)  DECLARE @SQL NVARCHAR(1024) = 'SELECT ID, TranDate, ProductID, CostValue, SalesValue, QtySold, FROM tblSales WHERE TranDate=''' + CAST(@dDateTIME AS VARCHAR(64)) + ''''  DECLARE @RunSQL NVARCHAR(max) SET @RunSQL= 'SELECT * FROM OPENROWSET (''MSDASQL'', ''dsn=mydsn;uid=myid;pwd=mypwd;'',''EXEC @SQL'')'

What syntax do I use to apply @SQL???

Oleksandr Fedorenko · Accepted Answer · 2013-05-17 19:55:28Z

1

You can dynamically create SQL statement and then run that command.

DECLARE @dDateTIME DATE,
        @RunSQL NVARCHAR(max) 

SET @dDateTIME = (SELECT SalesDate FROM dbo.SalesDate)

SELECT @RunSQL =
  'INSERT INTO dbo.tblSales
   SELECT * FROM OPENROWSET(''MSDASQL'', ''dsn=mydsn;uid=myid;pwd=mypwd;'',
    ''SELECT 
        ID,
        TranDate,
        ProductID,
        CostValue,
        SalesValue,
        QtySold,
    FROM tblSales WHERE TranDate=''''' + CONVERT(nvarchar, @dDateTIME, 112) + ''''''')'
--PRINT @RunSQL    
EXEC sp_executesql @RunSQL

answered May 17, 2013 at 19:55

Oleksandr Fedorenko

17k6 gold badges43 silver badges51 bronze badges

Collectives™ on Stack Overflow

SQL SELECT * FROM OPENROWSET with 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