Issue with Open Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Issue with Open Query

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2014-10-13 : 10:28:35

I am trying to query from a 2008 server (64 bit) to a 2000 server (32 bit)using linked server and have syntax issue with following code. It does not seem to pass the value of the variable correctly. Thank you.


declare @DateFrom datetime
set @DateFrom = '2014-9-1'

DECLARE @SQLSTR NVARCHAR(4000) 

 begin
 SET @SQLSTR = 
'SELECT * FROM OPENQUERY(SQL2, 
''select   Batch.Name as batch_name,
 from       [SQL2].Labor.dbo.Batch 
 where      (''''' + @DateFrom + ''''' IS Null or Batch.ReportDate >= ''''' + @DateFrom + ''''') 
            '')'
 end

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-13 : 10:39:18

quote:
Originally posted by snufse

I am trying to query from a 2008 server (64 bit) to a 2000 server (32 bit)using linked server and have syntax issue with following code. It does not seem to pass the value of the variable correctly. Thank you.

declare @DateFrom datetime
set @DateFrom = '2014-9-1'

DECLARE @SQLSTR NVARCHAR(4000)

begin
SET @SQLSTR =
'SELECT * FROM OPENQUERY(SQL2,
''select Batch.Name as batch_name,
from [SQL2].Labor.dbo.Batch
where (''''' + @DateFrom + ''''' IS Null or Batch.ReportDate >= ''''' + @DateFrom + ''''')
'')'
end

When you concatenate, either convert the @Datefrom values to VARCHAR, or declare them as VARCHAR to begin with. Also, the usual recommendation is to use the YYYYMMDD format for date literals.

declare @DateFrom VARCHAR(8);
set @DateFrom = '20140901'

DECLARE @SQLSTR NVARCHAR(4000) 

 begin
 SET @SQLSTR = 
'SELECT * FROM OPENQUERY(SQL2, 
''select   Batch.Name as batch_name,
 from       [SQL2].Labor.dbo.Batch 
 where      (''''' + @DateFrom + ''''' IS Null or Batch.ReportDate >= ''''' + @DateFrom + ''''') 
            '')'
 end

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2014-10-13 : 10:49:44

That seemed to pass the variable correctly, thank you.

Now I am getting another error, looks like an issue between 64 and 32 bit ? Is there a way to get around this?

quote:

SELECT * FROM OPENQUERY(SQL2,
'select Batch.Name as batch_name,
from [SQL2].Labor.dbo.Batch
where (''20140901'' IS Null or Batch.ReportDate >= ''20140901'')
')
OLE DB provider "SQLNCLI10" for linked server "SQL2" returned message "Deferred prepare could not be completed.".

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 10:53:20

Are you getting this error? (It's a good idea to post error messages!)

quote:

Msg 241, Level 16, State 1, Line 7
Conversion failed when converting date and/or time from character string.

That's because you're trying to concatenate a string with a datetime variable. sql won't implicitly convert dates in an expression like that. try explicit conversion.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2014-10-13 : 11:22:45

Tried this to convert date but having isues, can someone help me please?


declare @DateFrom datetime
 set @DateFrom = '2014-09-01'
 DECLARE @SQLSTR NVARCHAR(4000) 
 begin
 SET @SQLSTR = 
'SELECT * FROM OPENQUERY(SQL2, 
''select   Labor.dboBatch.Name as batch_name,
 from       [SQL2].goLabor30.dbo.Batch 
 where      (cast(datepart(yyyy-mm-dd, ' + @DateFrom + ')) as char(10) IS Null) 
          '')'
 end
 print @sqlstr
 EXEC (@SQLSTR)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-13 : 12:15:44

quote:

Originally posted by snufse

Tried this to convert date but having isues, can someone help me please?


declare @DateFrom datetime
 set @DateFrom = '2014-09-01'
 DECLARE @SQLSTR NVARCHAR(4000) 
 begin
 SET @SQLSTR = 
'SELECT * FROM OPENQUERY(SQL2, 
''select   Labor.dboBatch.Name as batch_name,
 from       [SQL2].goLabor30.dbo.Batch 
 where      (cast(datepart(yyyy-mm-dd, ' + @DateFrom + ')) as char(10) IS Null) 
          '')'
 end
 print @sqlstr
 EXEC (@SQLSTR)

Don't do this. Try your original query - it has an extra comma after the batch_name alias. I removed that in the following:

declare @DateFrom VARCHAR(8);
set @DateFrom = '20140901'

DECLARE @SQLSTR NVARCHAR(4000) 

 begin
 SET @SQLSTR = 
'SELECT * FROM OPENQUERY(SQL2, 
''select   Batch.Name as batch_name
 from       [SQL2].Labor.dbo.Batch 
 where      (''''' + @DateFrom + ''''' IS Null or Batch.ReportDate >= ''''' + @DateFrom + ''''') 
            '')'
 END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources