Update on ##Temp Table - Syntax Failure? Help ;-)

[smedvid]

I am totally stumped! I have some T-SQL that is stored in a table that I loop through against a TEMP Table... And it appears I am getting a syntax error... Yet... When I run code manually it works A-Ok? Any Ideas are "greatly" appreciated! tia...


T-SQL and Error Messages:
UPDATE ##tblLDR_Temp SET closecode='1' WHERE closecode IS NULL
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '1'.

UPDATE ##tblLDR_Temp SET currf=origf WHERE (currf='000' OR currf IS NULL) AND (origf <>'000' AND currf IS NOT NULL) AND closedate >= CONVERT(Date,'08/01/2010') AND origf IS NOT NULL
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '000'.

Here is Temp Table Creation Process...
CREATE TABLE ##tblLDR_Temp
([Batch_Date] [date] NOT NULL,
[l_id] [nvarchar](10) NOT NULL,
[closecode] [nvarchar](8) NULL,
[closedate] [date] NULL,
[currf] [smallint] NULL,
[origf] [smallint] NULL,
...etc...)



Here is Loop Code:
--Build Cursor to Loop Through All T-SQL to Update tblData
SET @SQL = 'DECLARE CUR_TSQL CURSOR FAST_FORWARD READ_ONLY FOR ' +
'SELECT Update_Data_Source, Detail_Id, tblData_Field_Name, Update_TSQL_Code, ' +
'Update_Description, Update_Log_Text ' +
'FROM AP.tblVVL_TSQL_Calcs ' +
'WHERE Data_Source_Id = ' + CHAR(39) + @Data_Source_Id + CHAR(39) + ' ' +
'ORDER BY Update_Data_Source, Detail_Id'

EXEC sp_executesql @sql

OPEN CUR_TSQL
FETCH NEXT FROM CUR_TSQL INTO
@Update_Data_Source,
@Detail_ID,
@tblData_Field_Name,
@Update_TSQL_Code,
@Update_Description,
@Update_Log_Text

-- START Main Loop Through Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
SET @LineCnt = @LineCnt + 1
PRINT @Update_TSQL_Code
BEGIN TRY
EXEC sp_executesql @Update_TSQL_Code --Execute the UPDATE SQL
WAITFOR DELAY '00:00:02' -- Precautionary Delay to ensure T-SQL Clears Cache (Just added 10/21 to see if it helps?)

SET @Update_TSQL_Code='
SET @RowCnt = LTRIM(RTRIM(CONVERT(nvarchar(10), @@RowCount)))
SET @TempDescription = LTRIM(RTRIM(CONVERT(nvarchar(10), @LineCnt))) + ' ' +
LTRIM(RTRIM(@tblData_Field_Name)) + ' ' +
LTRIM(RTRIM(@Update_Data_Source)) + ' ' +
LTRIM(RTRIM(@Detail_ID)) + ' ' +
@RowCnt + ' Records Updated for ' + LTRIM(RTRIM(@Update_Log_Text))
EXEC sp_Insert_Log @LOAN_CAT , @User, @TempProcessName, @TempDescription
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
SET @RowCnt = -9999
SET @TempDescription = 'FATAL ERROR - ' + LTRIM(RTRIM(@tblData_Field_Name)) + ' for ' + LTRIM(RTRIM(@Update_Log_Text))
EXEC sp_Insert_Log @LOAN_CAT , @User, @TempProcessName, @TempDescription
END CATCH;

FETCH NEXT FROM CUR_TSQL INTO
@Update_Data_Source,
@Detail_ID,
@tblData_Field_Name,
@Update_TSQL_Code,
@Update_Description,
@Update_Log_Text
END
-- END Main Loop

CLOSE CUR_TSQL
DEALLOCATE CUR_TSQL

Steve Medvid
IT Consultant & Web Master

 

[drlex]

Probably just a minor nit-pick, but if
[currf] [smallint] and [origf] [smallint], then how can either of them be '000' ? Surely they should be nvarchar/varchar/char or the test would be for 0?

soi là, soi carré

 

[smedvid]

Good catch! I changed the data schema recently...
But, issue was a stupid one! Since I derive my TSQL Code from a table and loop through it... I had an extra single quote in the table within the TSQL. Took me over 4 hours to locate it since I was looking elsewhere.... Just a long week I guess and was burned out... And, here we are again Monday! Take Care!


Steve Medvid
IT Consultant & Web Master