Conversion failed using datetime variable in OpenQuery call

Question

I'm running a linked query and I'm having a hard time injecting a calculated variable into the OPENQUERY call.

I've found a number of solutions here on Stack Overflow that seem to point to a data type mismatch, but I can't replicate the error outside of the OPENQUERY call, so it's confusing to me.

Here's the query in question:

SELECT name
FROM OPENQUERY ( [OLTP\SQL2014],
'USE master; DECLARE @FROMDATE datetime2(7) = dateadd(dd, -10, Sysdatetime());     
DECLARE @ToDate datetime2(7) = sysdatetime();
EXEC (''SELECT name from sys.tables 
  INNER JOIN sys.partitions
       ON sys.tables.object_id = sys.partitions.object_id 
WHERE  sys.tables.create_date BETWEEN '''' + @FromDate + '''' AND '''' + @ToDate + 
  '''' AND sys.tables.name LIKE ''''%EXCEPTION%''''
   AND NOT sys.tables.name LIKE ''''%AUDIT''''
   AND sys.partitions.rows <> 0 '')'
)

Do I need to abandon the use of variables inside OPENQUERY?

Why are you using SYSDATETIME when you declare a variable with datetime datatype? — CPMunich
– CPMunich, Commented Sep 8, 2015 at 20:12
Habitually going to the more precise measurement (as opposed to GetDate()). Any reason I shouldn't? — Sean Long
– Sean Long, Commented Sep 8, 2015 at 20:14
Sure, but then you may want to use a datetime2(7) datatype instead of datetime — CPMunich
– CPMunich, Commented Sep 8, 2015 at 20:18
That makes sense to me. Otherwise I'm just pointlessly cutting off those digits. Thanks. — Sean Long
– Sean Long, Commented Sep 8, 2015 at 20:18

Lukasz Szozda · Accepted Answer · 2015-09-08 20:32:55Z

2

Instead of using ugly string concatenation and EXEC use parametrized EXEC sp_executesql like:

SELECT name
FROM OPENQUERY ( [OLTP\SQL2014],
  'USE master;
  DECLARE @FromDate DATETIME = DATEADD(dd, -10, SYSDATETIME());     
  DECLARE @ToDate   DATETIME = SYSDATETIME();
  DECLARE @sql NVARCHAR(MAX) = 
      N''SELECT name 
          FROM sys.tables 
          JOIN sys.partitions
            ON sys.tables.object_id = sys.partitions.object_id 
          WHERE sys.tables.create_date BETWEEN @FromDate AND @ToDate
            AND sys.tables.name LIKE ''''%EXCEPTION%''''
            AND NOT sys.tables.name LIKE ''''%AUDIT''''
            AND sys.partitions.rows <> 0'';

   EXEC [dbo].[sp_executesql]
       @sql
       ,N''@FromDate DATETIME, @ToDate DATETIME''
       ,@FromDate
       ,@ToDate;
');

EDIT:

SELECT name
FROM OPENQUERY ( [OLTP\SQL2014],
  'USE master;
  DECLARE @FromDate DATETIME = DATEADD(dd, -10, SYSDATETIME());     
  DECLARE @ToDate   DATETIME = SYSDATETIME();

  EXEC [dbo].[sp_executesql]
       N''SELECT name 
          FROM sys.tables 
          JOIN sys.partitions
            ON sys.tables.object_id = sys.partitions.object_id 
          WHERE sys.tables.create_date BETWEEN @FromDate AND @ToDate
            AND sys.tables.name LIKE ''''%EXCEPTION%''''
            AND NOT sys.tables.name LIKE ''''%AUDIT''''
            AND sys.partitions.rows <> 0''
       ,N''@FromDate DATETIME, @ToDate DATETIME''
       ,@FromDate
       ,@ToDate;
');

edited Sep 8, 2015 at 20:32

answered Sep 8, 2015 at 20:09

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

5 Comments

Sean Long Over a year ago

I like it. When I try to run it, it gives me a couple of errors though. Must declare the scalar variable "@sql". and `Incorrect syntax near ' AND sys.partitions.rows <> 0'

Lukasz Szozda Over a year ago

@SeanLong See my updated answer and check if first problem is gone

Sean Long Over a year ago

It is, but now there's the error `Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'. I'm gonna play with it. I doubt I could CAST datetype as one of those and still do the comparison.

Lukasz Szozda Over a year ago

@SeanLong Updated one more time, try this one

Sean Long Over a year ago

%EXCEPTION% and %AUDIT actually need quad-quotes, but it looks good aside from a permissions issue on my end.

Collectives™ on Stack Overflow

Conversion failed using datetime variable in OpenQuery call

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related