SQL Server Performance Forum – Threads Archive

Error with Linked Server Dynamic Query

Hi All What I am missing here <pre id="code"> Declare @d_StartDate DateTime Declare @d_EndDate DateTime Declare @dt_start varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Declare @dt_end varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> DECLARE @openquery nvarchar(4000) set @dt_start = convert(varchar (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,@d_StartDate,112) set @dt_end = convert(varchar (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,@d_EndDate,112) SELECT @openquery = ‘SELECT * FROM HRD WHERE LastUpdatedstate between ‘+ @dt_start + ‘ and ‘+ @dt_end print @openquery print @dt_start print @dt_end SELECT * FROM OPENQUERY(MYSQL_DBCON, ‘ + @openquery + ‘) Error Server: Msg 7357, Level 16, State 2, Line 13 Could not process object ‘ + @openquery + ‘. The OLE DB provider ‘MSDASQL’ indicates that the object has no columns. OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName=’MSDASQL’, Query= + @openquery + ‘]. </pre id="code"> Thanks in Advance

Is this for SQL 2000 too? If you search for KBAs thenhttp://support.microsoft.com/default.aspx?scid=kb;en-us;270119 is best one to go. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Look at the second parameter here – which is the literal query statement: OPENQUERY(MYSQL_DBCON, ‘ + @openquery + ‘) Imagine you have your QA window up, you’re connected to the remote server, and you run that exact query statement – + @openquery + – what results do you expect to see? The problem with OPENQUERY is that you can only spell out the full query – you cannot use a variable, and you cannot concatenate variables into the statement. You have to concatenate the complete statement into a string, including OPENQUERY, and execute that as a whole. Also note that you have to put single quotes around dates that you spell out as criteria.

I have sort it out and it is like this… Declare @d_StartDate DateTime Declare @d_EndDate DateTime Declare @dt_start varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Declare @dt_end varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> DECLARE @openquery nvarchar(4000) DECLARE @runquery nvarchar(4000) set @dt_start = convert(varchar (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,@d_StartDate,112) set @dt_end = convert(varchar (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,@d_EndDate,112) SET @openquery = ””+’SELECT * FROM HRD WHERE LastUpdatedstate between ‘ + @dt_start + ‘ and ‘ + @dt_end+”” SET @runquery = ‘SELECT * from openquery(MYSVR,’ + @openquery + ‘)' EXEC(@runquery) Thanks again sonny

When you dont pass object names as parameters, why do you do it in dynamic sql? Madhivanan Failing to plan is Planning to fail

]]>