Mimicking Commands of Database Application.

[NormRumac]

Hi.

I work with an database application that uses MS SQL server (actually MSDE in my case) to manage records for its database. Like any other application, it has a GUI for users to enter transactions - the app then takes these transactions and translates them into SQL commands that are sent to SQL server.

For certain easy tasks, I find the user interface too cumbersome to work with, and instead, I would like to write my own program to automatically perform these tasks in one step. In other words, I would like to mimick the same SQL commands that the application currently does when it performs one of these tasks. For instance, when the application adds an entry to the database, it might issue a command like "INSERT INTO table (a,b,c) values (1,2,3)".
I want to be able to write my own program which itself issues this same command to SQL server.

I haven't done anything like this before, so I am wondering if this sounds reasonable to accomplish, or am I in over my head?

First, I need to figure out how my app currently writes to its database tables when a user of the application performs particular task. For this, I assume I will need to get my hands on some sort of SQL database auditing tool that can log all commands issued to SQL server, when I use the app to perform that task. When I get that information, I will write a program that just copies the syntax of those commands.

I searched around the web, and found this software called "Log Explorer" from Lumigent. Does anyone know if this can do what I want? (i.e. get the exact commands issued to SQL server when I use the application?)

 

[TomKane]

You could use SQl Server's own prfiler you could switch it on of a day or so and see what commands are issued to the database. I got the stuf below from the online help, but you should probably take a look at msdn.microsoft.com for further details.



What Is SQL Server Profiler?
SQL Server Profiler is a graphical tool that allows you to monitor and collect server events such as:

Server connects and disconnects
Transact-SQL batches
The execution of statements within stored procedures
Deadlocks
Errors written to the SQL Server error log
This information can be displayed in SQL Server Profiler, stored in trace files, or saved in a Microsoft® SQL Server™ table. SQL Server Profiler can read trace files created in SQL Server Profiler or SQL Trace, as well as Transact-SQL scripts.

Examples of event data captured by SQL Server Profiler include:

The types (classes) of events being traced (e.g. SQLBatchCompleted).
The SQL Server name of the users performing activity in SQL Server.
The text of Transact-SQL statements and stored procedures being executed.
The duration of Transact-SQL and other types of events.
The severity of errors.
Event data can be filtered so that only a subset of the event data is collected. For instance, a filter can be defined to trace only those SQL:BatchCompleted events with a duration greater than one second.

Additionally, SQL Server Profiler allows captured event data to be replayed against SQL Server, thereby effectively re-executing the saved events as they occurred originally.

SQL Server Profiler can also be used to:

Monitor the performance of SQL Server.
Debug Transact-SQL statements and stored procedures.
Identify slow-executing queries.
Capture error information in the context of the queries being executed.
Identify scans of large tables.
To help you get started with SQL Server Profiler, you can use the Create Trace Wizard or the sample traces supplied to begin monitoring SQL Server performance quickly.

 

[NormRumac]

Thanks for the info. Do I need to get a fully functional version of SQL Server to use SQL profiler? I am currently using MSDE (Desktop Edition) because SQL server is too expensive for me.

 

[TomKane]

To tell you the truth - I don't know