March 24, 2006 at 7:17 am
Hi,
I have a series of scripts that I would like to run on a daily basis to ensure the integrity of my database. I've put these scripts in a separate table called Discrepancies.
I would like to loop through these queries and if any of them return a result, to save that result into another table in the database, called Discrepancy_Results. My preference is to save the entire result set as XML into a BLOB field with a reference to the query that generated the result set. Something like: UID int, TIMESTAMP smalldatetime, DISCREPANCYID int, RESULTS text.
I tried creating a cursor within a cursor like this:
DECLARE @SQL varchar(8000)
DECLARE curDiscrepancy CURSOR FOR
Select Query from z_discrepancy
OPEN curDiscrepancy
FETCH NEXT FROM curDiscrepancy
INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL
EXEC ('DECLARE InnerCursor CURSOR FOR
' + @SQL + '
OPEN InnerCursor
FETCH NEXT FROM InnerCursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM InnerCursor
END
CLOSE InnerCursor
DEALLOCATE InnerCursor')
FETCH NEXT FROM curDiscrepancy
INTO @SQL
END
CLOSE curDiscrepancy
DEALLOCATE curDiscrepancy
I ran into problems. Each record in the inner query's results show as a separate query and I don't know how to process it so that it appends the results to the same record in the Discrepancy_Results table.
Is this an overly complicated way of going about it?? Red lights and alarm bells anyone?
Alternatively, I can do it as an ASP page, if only I could figure out how to run that from windows scheduler or something similar.
TIA
March 24, 2006 at 7:39 am
Why an inner cursor? Can't you just get the data and append it to a table or variable?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply