CODEWITHSUNDEEP
sqlsql-serverlogging
N.G.
N.G.

Reputation: 3

Creating a text log of SQL transactions

I have script that is to delete an ID and its associated records across a number of tables. What I am looking for is a way to print out all the individual transactions. I haven't been able to figure that out so I am instead running a select statement to populate a temp table with the values that will be deleted. The problem I have is I cannot figure a way to display my new temp table.

For example, one delete is as follows:

DELETE FROM dbo.Attachments WHERE IdeaID IN (SELECT IdeaID FROM #Ideas)

With #Ideas being a table that holds the various IDs I want removed. The tables have 0..* records for each ID. So to see those records I can run this select:

SELECT * INTO #Attachments FROM dbo.Attachments WHERE IdeaID IN (SELECT IdeaID FROM #Ideas)

Now the problem is how do I display this?

I attempted to utilize PRINT:

DECLARE @PRINTATTEMPT varchar(max)
SET @PRINTATTEMPT='select * from #Attachments'
EXEC(@PRINTATTEMPT)
PRINT(@PRINTATTEMPT)

Unfortunately all this PRINTS is the SQL statement itself, not the values.

Next I attempted to get tricky with a cursor to iterate and print line by line:

DECLARE @DISPLAYVALUES nvarchar(128)

DECLARE attachmentscur CURSOR FOR
SELECT * FROM #Attachments

OPEN attachmentscur

FETCH NEXT FROM attachmentscur INTO @DISPLAYVALUES;
WHILE @@FETCH_STATUS = 0
BEGIN   
PRINT 'Attachment selected for removal: ' + @DISPLAYVALUES
FETCH NEXT FROM attachmentscur INTO @DISPLAYVALUES;
END

CLOSE attachmentscur;
DEALLOCATE attachmentscur;

Unfortunately with this I am then hit with the error "Cursorfetch: The number of variables declared in the INTO list must match that of selected columns." I think this is trying to tell me it doesn't know how to iterate through the table... but I'm not sure.

Again - so this makes some sense, I have two dozen or so tables that I need to delete records from. Each utilizes this ID filed and I simply want to create a log of what is removed (just a local txt file). The log cannot be saved in the database so backup tables are not a possibility.

Upvotes: 0

Views: 274

Answers (1)

Walter_Ritzel
Walter_Ritzel

Reputation: 1397

Going on the route that you have set, your last attempt is a good one, but you need to fix your FETCH INTO stateament: you need to provide one variable for each column or create a record type that will correspond to each column.

In complete different way to get this solved, you can use triggers BEFORE DELETE saving the needed information into a log table or something...

Upvotes: 1

Related Questions