Displaying SQL Statement as output on report

The requirements of Sarbanes-Oxley are getting downright outrageous.
Everytime we run a report, we need to verify that the SQL statement has not
been modified since it was last verified as accurate. To combat the constant
requests to show the SQL behind the scenes, I would like to show the SQL as
part of the report.
Is this possible and how would it be done?
I am guessing that it would require custom code that captures the SQL in a
variable and then outputs that variable into the report.Could you get away with driving each report by a seperate stored procedure
( this is what we do anyway ).
Tell them the name of the stored procedure for each report.
If they want to go the extra paranoid step :
Write a custom assembly which uses sp_helptext on the stored proc name to
get the text of the storedproc ( a line at a time )
Generate a SHA1 hash of the text ( you might want to ignore whitespace and
non alphanumeric characters )
Write to some custom reportlog file, reportname, who ran, when, on what
machine, the hash of the procedure name
( Of course you need to record somewhere the hash of the stored procedure
when its verified )
You could even throw an exception if hash doesnt match the last verified
hash.
If thats not good enough ( !! ) you could encrypt the hash with a private
key, i.e. give it a full digital signature.
Or you could just generate a hash of the entire db schema !!
> Everytime we run a report, we need to verify that the SQL statement has
> not
> been modified since it was last verified as accurate.|||I would think the best thing to do is to put everything in SP. Then you have
the date the SP was created and can compare that against when it was
verified. If you keep the date of verification in a table you can then run a
report that shows any unverified SP based on the creation date. This seems
to me to be a much better solution. Straight forward and verifiable (which
is usually what you need for Sarbanes-Oxley).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David" <David@.discussions.microsoft.com> wrote in message
news:4C2307F6-5002-4713-8B92-DC9A468FFEF2@.microsoft.com...
> The requirements of Sarbanes-Oxley are getting downright outrageous.
> Everytime we run a report, we need to verify that the SQL statement has
not
> been modified since it was last verified as accurate. To combat the
constant
> requests to show the SQL behind the scenes, I would like to show the SQL
as
> part of the report.
> Is this possible and how would it be done?
> I am guessing that it would require custom code that captures the SQL in a
> variable and then outputs that variable into the report.