How to run profiler from cmd

Hi,

We have a requirement to run profiler continuously to find out the
misbehaving applications/SQL in the dataserver. And we are not
interested in running it from an individuals local machine. Also we
want the profiler to start automatically whenever there are SQL Server/
Machine restarts.

Is there a way to invoke the profiler instance from cmd prompt or is
there a way to script the profiler?

Thanks in advance,
Thyagu.Here are the steps that can help you accomplish what you need:

1. Define your SQL Profiler Trace and script it. A good idea to place
restrictive filters to minimize the load on the production system. Make sure
the trace target is a file, not a table as it will have significant
performance impact. To script the trace use:
- SQL Server 2000 -File -Script Trace
- SQL Server 2005 -File -Export -Script Trace Definition

2. Modify the trace script and wrap it in a stored procedure. Add a couple
parameters to pass the target trace file, source database. etc. One of the
first statements in the generated trace script is calling sp_trace_create,
where you need to replace the target trace file as a parameter. And you can
add a call to sp_trace_setfilter (for example, EXEC sp_trace_setfilter
@.TraceId, 3, 0, 0, @.DbId) to set a filter for a specific database based on
database id that you can pass to the SP (or do not set that filter if you
want to trace for all databases). Also, the database can be one of the
preset filters in step 1 above.

3. Inside the stored procedure you can record the trace id to a table, so
later on if needed you can use the SP sp_trace_setstatus to stop the trace.

4. Create a job to schedule the stored procedure. In the schedule specify to
run automatically when SQL Server Agent starts.

BTW, this is the recommended way to run traces on production systems. If you
run a trace via the GUI you are actually running two traces: one that send
the trace to the target file, and another that sends the trace data to the
client running Profiler.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

How to retieve data using cellset

Dim cs as cellset
cmd = con.CreateCommand()
cmd.CommandText = "Select [Geography].[Geography].[Country].&[United States] ON ROWS, [Date].[Calender Year].[Calender Year] ON COLUMNS FROM [Adventure Works]"
cs = cmd.ExecuteCellSet()
Dim tuplesOnColumns As TupleCollection
tuplesOnColumns = cs.Axes(0).Set.Tuples
Dim column As Tuple
For Each column In tuplesOnColumns
result.Append(column.Members(0).Caption, "/t")
Next column
Response.Write(result.ToString)

i cannot get anydata when i run this , my connection with the database is good can anyone help me how to get the data using cellset

hello Aravind,

i think an example below should work:

Dim cs As CellSet
Dim cmd As AdomdCommand

cmd = con.CreateCommand()
cmd.CommandText = "Select [Geography].[Geography].[Country].&[United States] ON ROWS, [Date].[Calendar Year].members ON COLUMNS FROM [Adventure Works]"

cs = cmd.ExecuteCellSet()

Dim result As StringBuilder
result = New StringBuilder()

Dim tuplesOnColumns As TupleCollection
tuplesOnColumns = cs.Axes(0).Set.Tuples

Dim column As Tuple

For Each column In tuplesOnColumns

result.Append(column.Members(0).Caption)
result.Append("\t")

Next column

System.Diagnostics.Debug.WriteLine(result.ToString())

hope this helps some,