I am currently working on a project that has a significant amount of implementation completed. I recently learned of a new requirement (scope creap, imagine that?).

I need to create an audit of the resulting T-SQL code eventually gets called agaist the target DB. I'm assuming the finalized version of the query to be executed gets assembled and executed in the DQE or some other black box assembly. Is this correct?

I am currently leverage in the auditing DI mechanism to meet other audit requirements. Is there some way to extend this in a way to using a template pattern or something to gain access to the resulting T-SQL? Or maybe you have some other means to get this, at least I hope so. If not, I'm screwed aren't I.

Regards, Bob

Additional Info: LLBLGen v2.6 (Adapter pattern) .NET Framework 3.5 Windows Server 2008 SQL Server 2008.

There are many threads on the forum about this kind of requirement. Here are a few to get you started...

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5832&HighLight=1 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14974&HighLight=1 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=2408&HighLight=1

or search for "generated sql" in the message subejct only.

Matt

MTrinder wrote:

There are many threads on the forum about this kind of requirement.

Thanks for your response Matt. I don't need to see the resulting T-SQL in a debugging trace but instead have the means to insert the resulting query into a logging table.

Got any ideas?

Thanks in advance.

Hi Bob,

Some questions:

• Why do you want this T-SQL audit? I ask this because the solution may vary depending on the answer. If you want to log all the queries sent to the server, maybe the best option would be a sql profiler tool.

• Where would you grab the queries? (tables? file?)

• Do you plan to audit ALL queries, or just for certain operations (what operations? over what objects?)

Here is a Miha's post about customizing tracing. I know you don't want tracing, but it could give you an idea on how to trap the generated sql string and manipulate it to do something.

Hi daelmo, thanks for your help.

daelmo wrote:

Why do you want this T-SQL audit?.

The project is a government project. They have a ton of rules and regulations on audition with respect to privacy act junk. Basically the cust has a requirement to log the specific SQL query that was called to access certain personal information such as SSN, address , etc. Personally, I think it's silly. So what if I logged the query used to access data, there is a chance the query results may differ from the time the original request was made. Pain in the a$$ if you ask me. But, as you know as a developer, you've got to give the customer what they want.

daelmo wrote:

If you want to log all the queries sent to the server, maybe the best option would be a sql profiler tool.

Does there exist such a tool? I know there exists a SQL Server profile tool, but I wouldn't want the customer to have to have it running all the time on the server. Maybe I'm missing something here. I'm by no means an expert w.r.t. profiling. If you can offer suggestions regarding this potential route, I'm all ears.

daelmo wrote:

**Where would you grab the queries? **(tables? file?)

My original plan was to some how capture the sql before it get's created/executed by the DAL. Kind of like the how the audit DI works. It would be cool if I could override some method and simply aquire the final assembled sql which has executed on the server. You know what I mean?

daelmo wrote:

Do you plan to audit ALL queries, or just for certain operations (what operations? over what objects?)

As it stands now it's only tables that have personal information in them. Like name, address, SSN, etc. Given the scheme this means only one table. But I'm thinking, what if a query is made involving a join of this personal info table? I'm thinking I have to log the sql query as well.

Thanks again for your help. Hopefully this will shed some light.

Bob

IMHO, Tracing is the best option here, because you can define in the config file where the trace information is send to: event log, file, or your own trace listener.

So you can use your own trace listener that saves the SQL trace anywhere you want.