locked
Variable value contains a value with a single apostropher RRS feed

  • Question

  • I have a stored procedure that will assign the error number and message to variables when they occur and then update a table with those values. The problem that I'm running into is the error message is 

    • Invalid column name 'Last_Update_Date'.

    The single quotes are throwing an error when I attempt a update like below.

    Set @ErrNum = ERROR_NUMBER()
    Set @ErrMsg = ERROR_MESSAGE()

    Set @ProcSQLUpd = 'Update SSIS_Log_tbl Set End_Time =  CURRENT_TIMESTAMP,  
    Err_Num = ' + @ErrNum + ', Err_Desc = ''' + @ErrMsg + ''' Where Proc_ID = 1' + @ProcID

    If I manually enter a value for the "Err_Desc" then it works fine, it's the single quotes that are throwing it off. Any assistance would be appreciated.

    Wednesday, March 1, 2017 2:17 PM

Answers

  • Try this way:

    Set @ErrNum = ERROR_NUMBER()
    Set @ErrMsg = ERROR_MESSAGE()
    
    Set @ProcSQLUpd = 'Update SSIS_Log_tbl Set End_Time =  CURRENT_TIMESTAMP, 
    Err_Num = ' + @ErrNum + ', Err_Desc = ' + @ErrMsg + ' Where Proc_ID = 1'  
    
    EXEC sp_executesql @ProcSQLUpd, N'@ErrNum INT, @ErrMsg varchar(2000)', @ErrNum=@ErrNum,@ErrMsg=@ErrMsg;
    

    • Marked as answer by DanHaf Wednesday, March 1, 2017 6:19 PM
    Wednesday, March 1, 2017 4:40 PM

All replies

  • Try this:

    Set @ErrMsg = Replace(ERROR_MESSAGE(),''','''')

    Wednesday, March 1, 2017 2:21 PM
  • Thanks, but that is not working. I get an error using that syntax. If I add extra single quotes, it will save but still produces an error.
    Wednesday, March 1, 2017 2:47 PM
  • Set @ProcSQLUpd = 'Update SSIS_Log_tbl Set End_Time =  CURRENT_TIMESTAMP,  
    Err_Num = ' + @ErrNum + ', Err_Desc = ' + @ErrMsg + ' Where Proc_ID = ' + @ProcID

    If it does not work, can you print our this dynamic sql string and post here.

    Wednesday, March 1, 2017 2:55 PM
  • Your error message refers to 'Last_Update_Date'.  Is that in the Err_Desc?  If so REPLACE(@ErrMsg,'''','"') should fix that problem.

    Wednesday, March 1, 2017 3:26 PM
  • This is the error that it is throwing.

    Msg 102, Level 15, State 1, Line 216
    Incorrect syntax near 'Tickets_tmp1'.

    That's the part in the error message that has the single quotes around it.

    Wednesday, March 1, 2017 3:43 PM
  • What is your code now with this error message?
    Wednesday, March 1, 2017 4:13 PM
  • I posted it in the initial posting. I also tried the below.

    Set @ProcSQLUpd = 'Update SSIS_Log_tbl Set End_Time =  CURRENT_TIMESTAMP,  
    Err_Num = ' + @ErrNum + ', Err_Desc = ' +  REPLACE(@ErrMsg,'''','"')  + ' Where Proc_ID = 1' 

    Wednesday, March 1, 2017 4:18 PM
  • With your current code, you need to print it out without executing it and post here.

     

    Wednesday, March 1, 2017 4:22 PM
  • Try this way:

    Set @ErrNum = ERROR_NUMBER()
    Set @ErrMsg = ERROR_MESSAGE()
    
    Set @ProcSQLUpd = 'Update SSIS_Log_tbl Set End_Time =  CURRENT_TIMESTAMP, 
    Err_Num = ' + @ErrNum + ', Err_Desc = ' + @ErrMsg + ' Where Proc_ID = 1'  
    
    EXEC sp_executesql @ProcSQLUpd, N'@ErrNum INT, @ErrMsg varchar(2000)', @ErrNum=@ErrNum,@ErrMsg=@ErrMsg;
    

    • Marked as answer by DanHaf Wednesday, March 1, 2017 6:19 PM
    Wednesday, March 1, 2017 4:40 PM
  • This should fix it:

    Set @ErrNum = ERROR_NUMBER()
    Set @ErrMsg = REPLACE(ERROR_MESSAGE(),'''','"')
    Set @ProcSQLUpd = 'Update SSIS_Log_tbl Set End_Time =  CURRENT_TIMESTAMP,  
    Err_Num = ' + @ErrNum + ', Err_Desc = ''' + @ErrMsg + ''' Where Proc_ID = 1' + @ProcID

    Wednesday, March 1, 2017 5:46 PM
  • That did the trick. Thanks for the help.
    Wednesday, March 1, 2017 6:19 PM