title: SQL SELECT * FROM OPENROWSET with Variable
body: <p>I am trying to pass a variable into a SELECT statement in OPENROWSET but I keep getting an error </p>
  
  <pre><code>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'')'`
  </code></pre>
  
  <p>What syntax do I use to apply to <code>@SQL</code>?</p>
  
  <p>The error I get is:</p>
  
  <blockquote>
    <p>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)' "</p>
  </blockquote>