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
Go to Top of Page

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.".

Go to Top of Page

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.
Go to Top of Page

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)
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -