Dynamic Query Problem

I have a sproc that runs as a job every day. Since the first of the year, it hasn't been running properly (it errors out). It builds an SQL statement dynamically, and then executes it.

If I try to run it with QA, I get the following message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FCST'.
UPDATE OPSPLAN SET Jan_07=Jan_fcst, Feb_07=Feb_fcst, ...

However, if I just copy the entire Update statement contained in the error message into the QA window, and execute it, it runs just fine.

What could I be missing?

UPDATE OPSPLAN SET Jan_07=Jan_fcst, Feb_07=Feb_fcst, Mar_07=Mar_fcst,
Apr_07=Apr_fcst, May_07=May_fcst, Jun_07=Jun_fcst, Jul_07=Jul_fcst,
Aug_07=Aug_fcst, Sep_07=Sep_fcst, Oct_07=Oct_fcst, Nov_07=Nov_fcst,
Dec_07=Dec_fcst
FROM (SELECT [YEAR], PLAN_SHIP.BOD_INDEX, BOD_HEADER.PRODUCT,
Jan_fcst, Feb_fcst, Mar_fcst, Apr_fcst, May_fcst, Jun_fcst, Jul_fcst,
Aug_fcst, Sep_fcst, Oct_fcst, Nov_fcst, Dec_fcst
FROM PLAN_SHIP INNER JOIN BOD_HEADER
ON PLAN_SHIP.BOD_INDEX = BOD_HEADER.BOD_INDEX
WHERE (SCEN_ID = 1) AND ([Year] = 2007)
) PS INNER JOIN OPSPLAN ON PS.BOD_INDEX = OPSPLAN.BOD_INDEX
WHERE OPSPLAN.SRCPLAN = 'SHIP'I'd sic the SQL Profiler on this fella. My suspicion is that the UPDATE is being mis-parsed, possibly because of a syntax error within the previous SQL statement. Profiler ought to give you some clues if that is the case. No outright answers, just clues, but that's more than you have now.

-PatP|||OK, I read up on it in BOL, and figured out how to get profiler running.
I found the line where that particular statement is executing.
I'm looking at StmtStarting and StmtCompleted. Is there something in
particular that I should be looking for?|||Since the string "FCST" does not appear independently in your code, but only in conjuction with a month and an underscore character, I'd say that somewhere and underscore character is being dropped.

Set the dynamic sql statement to print rather than execute, and bump up the Max characters setting in the Query Analyzer Options. Then see what code is actually being executed.|||Yeah, that "FCST" was throwing me, too. All of the instances in my string
are "fcst" not "FCST". Anyway, after pouring through the sproc over and over, I found a PRINT statement that was causing the completely valild statement to display under the error message. It wasn't the cause at all,
it was a previous statement. After I eliminated that, it was easy to narrow it down.

Thanks

Dynamic columnName

Howdy all,
I'm trying to create a sproc that has the column name and search text as input parameters with dynamic SQL... what I have is:
-- Create a variable @.SQLStatement
DECLARE @.SQLStatement varchar(255)
SELECT @.SQLStatement = 'SELECT * FROM myTable WHERE (''' + @.ColumnName + ''' = ''' + @.SearchText + ''')'
-- Execute the SQL statement
EXEC(@.SQLStatement)
The procedure works (at least in Query Analyzer, once I declare/set @.columnname and @.searchtext) in that I don't get any errors. But it returns a blank table with "0 rows affected". However, if I hardcode the variables, it returns the proper data. What's wrong with my select statement??
Thanks!!
JP

DECLARE @.SQLStatement varchar(255)
SET @.SQLStatement = 'SELECT * FROM myTable WHERE (' + @.ColumnName + ' = ''' + @.SearchText + ''')'
-- Execute the SQL statement
EXEC(@.SQLStatement)|||That's it! Thanks, ndinakar. Those darn quotes...

dynamic (xml text fields) for update sproc

hi folks
anyone have a link or sproc for doing (simple table) updates where the field
list for the update is dynamically created from the inbound XMLtext (could
either validate against an XMLSchema or sysColumns)?
for instance (this is out of Pubs):
CREATE PROCEDURE dbo.sp_update_employee
@.xmltext ntext
AS
-- sample
-- '<update><Employee emp_id="GHT50241M" lname="Thomas II"
fname="Larry"/></update>'
DECLARE @.hDoc int
exec sp_xml_preparedocument @.hDoc OUTPUT,@.xmltext
UPDATE Employee
SET
Employee.fname = XMLEmployee.fname,
Employee.lname = XMLEmployee.lname
FROM OPENXML(@.hDoc, 'update/Employee')
WITH Employee XMLEmployee
WHERE Employee.emp_id = XMLEmployee.emp_id
EXEC sp_xml_removedocument @.hDoc
--SELECT * from Employee WHERE emp_id='GHT50241M' FOR XML AUTO, elements
GO
I'd like to have the updating happen only to those elements/fields existing
in the XML. (In a time crunch, or I'd start writing my own.)
Rob
A couple of options:
Check out SqlXml updategrams, it will do this for you automatically.
Probably the easiest way to do with OpenXml is shred the document into edge
table format and then build up the sql yourself.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad

dynamic (xml text fields) for update sproc

hi folks
anyone have a link or sproc for doing (simple table) updates where the field
list for the update is dynamically created from the inbound XMLtext (could
either validate against an XMLSchema or sysColumns)?
for instance (this is out of Pubs):
CREATE PROCEDURE dbo.sp_update_employee
@.xmltext ntext
AS
-- sample
-- '<update><Employee emp_id="GHT50241M" lname="Thomas II"
fname="Larry"/></update>'
--
DECLARE @.hDoc int
exec sp_xml_preparedocument @.hDoc OUTPUT,@.xmltext
UPDATE Employee
SET
Employee.fname = XMLEmployee.fname,
Employee.lname = XMLEmployee.lname
FROM OPENXML(@.hDoc, 'update/Employee')
WITH Employee XMLEmployee
WHERE Employee.emp_id = XMLEmployee.emp_id
EXEC sp_xml_removedocument @.hDoc
--SELECT * from Employee WHERE emp_id='GHT50241M' FOR XML AUTO, elements
GO
I'd like to have the updating happen only to those elements/fields existing
in the XML. (In a time crunch, or I'd start writing my own.)
RobA couple of options:
Check out SqlXml updategrams, it will do this for you automatically.
Probably the easiest way to do with OpenXml is shred the document into edge
table format and then build up the sql yourself.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad