Session capture for sqlcmd utility

Session capture for sqlcmd utility by Alex3117

Closed

as Won't Fix Help for as Won't Fix

Type:	Suggestion
ID:	766329
Opened:	10/5/2012 11:31:04 AM
Access Restriction:	Public

Workaround(s)

view

At our organization, we run a lot of ad-hoc DML to correct bugs or pick up the slack until functionality is added or fixed. As such, we like to keep records of these transactions for ourselves as well as auditors. We would like to use the sqlcmd utility to capture the commands and results as they are run by the DBAs. The most common approach is to use the "-o" command line option. However, doing this causes us to lose the interactive nature of the session.

For example: "sqlcmd -E -S <server> -e -W -o sql_out.txt" will send all output to the sql_out.txt file, but now I lose the ability to analyze the results from my DML as it is sent to a file, but not immediately written out (still in the buffer). For instance, a developer or BSA may send us a statement that is expected to affect 100 records and if we begin the transaction, run the update, and then it comes back affecting 1,000 records, we can issue a rollback transaction as opposed to commit transaction after this feedback is presented to us. This feedback is lost if I use the "-o" option. I thought about using the Get-Content cmdlet against the generated log, but the output is buffered and so I can't analyze the statements just executed, I must wait for the buffer to flush to the file. This is obviously unacceptable for our situation.

I tried using the Start-Transcript cmdlet in PowerShell as well, but once I'm inside sqlcmd, it overrides the PowerShell cmdlet and none of my SQL commands or results show up in the transcript.

Details (expand)

English

Tools (SSMS, Agent, Profiler, Migration, etc.)

Basically what we are looking for is behavior analogous to the SPOOL command in Oracle's SQL*Plus utility. My output file should look exactly like the command window I'm interacting with. I should see the commands I typed and any results.

Improved User Interface

This allows for accountability to external auditors regarding actions taken outside normal application functionality. It also allows these actions to be tracked, as well as the results of the actions and if they were rolled back or committed. If nothing else, it can help a DBA to see exactly what he did during a crisis for any postmortem research later on, since it is likely that during the emergency, we're trying to fix the problem as quickly as possible, and not noting every command we're running.

0 attachments

Thank you for your input and suggestion! We have discussed and triaged this and decided to not address this issue at this time because the impact and priority are not high enough compared to other items.

If you feel strongly about this please provide further feedback and we will follow up.