openquery() problems - 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

openquery() problems

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2011-11-14 : 16:40:58

why will this work:


select id, log_docid, log_ip, log_datetime, log_user, log_browser, log_os 
from openquery(mytp,'select id, log_docid, log_ip, log_datetime, log_user, log_browser, log_os 
from jos_docman_log')

but this won't:


DECLARE @now datetime
SET @now = (select max(log_datetime) from jos_docman_log)
DECLARE @SQL nvarchar(1000)
SET @sql=
'select id, log_docid, log_ip, log_datetime, log_user, log_browser, log_os 
from openquery(mytp,''select id, log_docid, log_ip, log_datetime, log_user, log_browser, log_os 
from jos_docman_log
where log_datetime ='+@now+''')'

print(@sql)

What am I doing wrong?

This is the error I'm getting:

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

I've tried juggling apostrophes but I can't get it!

Craig Greenwood

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 00:01:57

whats the datatype of log_datetime field?

also the statement should be as follows


DECLARE @now datetime
SET @now = (select max(log_datetime) from jos_docman_log)
DECLARE @SQL nvarchar(1000)
SET @sql=
'select id, log_docid, log_ip, log_datetime, log_user, log_browser, log_os 
from openquery(mytp,''select id, log_docid, log_ip, log_datetime, log_user, log_browser, log_os 
from jos_docman_log
where log_datetime ='''''+convert(varchar(11),@now,121)+''''''')'

print(@sql)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

craigwg
Posting Yak Master

154 Posts

Posted - 2011-11-15 : 10:34:42

Your suggestion worked perfectly. 7 apostrophes in a row. Incredible! Thanks!

Craig Greenwood

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 10:36:54

see this to understand why you need it

http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources