2

I am trying to create a procedure that will generate an xml document of a given table whilst updating the information in said table. My procedure does not recognise the use of the temporary table unless I use a CREATE TABLE statement (I have not tried table var's yet). Ideally I would not use the CREATE TABLE statement in case I/another developer wanted to add another column(s) in the future.

Here is my code currently:

UPDATE Audit.ErrorLog SET ErrorReported = 0 OUTPUT inserted.* INTO ##temp

SELECT *
FROM ##temp 
FOR XML PATH('Error'), ROOT ('Errors')

DROP TABLE ##temp

And it fails with:

Msg 208, Level 16, State 0, Line 24

Invalid object name '##temp'.

It's not the end of the world if I have to use a CREATE TABLE statement, but I would prefer not to explicitly declared it.

Notes:

  1. I know there is no error handling at the moment, I want to get this section working first.
  2. I have also tried naming the columns instead of using Inserted.*
  3. I am purposely setting ErrorReported = 0 as I am in a test environment
2

You must create a temp table with Audit.ErrorLog Columns and then get output records.

Declare @temp Table (ErrorLogID Int, ErrorReported Int, Error NVarChar(100))

UPDATE Audit.ErrorLog 
SET ErrorReported = 0 
OUTPUT inserted.* 
INTO @temp  

SELECT * 
FROM @temp  
FOR XML PATH('Error'), ROOT ('Errors')  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.