Cursor save results as XML in table

[coffeecup1]

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

 

[mwolf00]

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

C:\PROGRA~1\INTERN~1\iexplore.exe

http://www.yourURL/yourASPpage.asp

Browse to find your IE executable then add the URL for your page...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook