Ho to create custom trace events from TSQL?

Hi,

Is there a way to create custom trace events from TSQL code?

Because, ideally, I would like to be able to read those traces from the SQL Profiler tool (or the custom server side trace stored procedures). However, I can't find any documentation about that. Is it possible? There seem to be a User-Configurable trace category.

If this is not possible, what would be the best (easy, fast, ...) way to create an equivalent feature?

Thanks!

You could use sp_trace_create, sp_trace_setevent, sp_trace_setfilter stored procedures for it.|||

Hi Konstantin,

But, if I understand correctly, this is for *recording* a trace event, not for creating it?

|||

Oh, sorry, my english.

May be this will be helpful:

sp_trace_generateevent [ @.eventid = ] event_id [ , [ @.userinfo = ] 'user_info' ] [ , [ @.userdata = ] user_data ]|||

Ah I didn't see this one

thanks!

High Duration SQL 2005 Profiler

Hello everybody,

I have a very stranger problem that I need to understand...

In last days I executed a plan SQL 2005 Profiler to review TSQL Duration. When reviewing the results encounter that a SP displays a value of 4037312 in field DURATION which are not normal.

Could to help me to identify why passes this?

SQL Server 2005 has the ability to show duration in microseconds.

This can be changed in the options of Profiler.

Maybe this option is turned on?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||The Duration Field show in microseconds is turned off. The generate value in the profiler for duration field in miliseconds is very high and not real...|||

This comes out to approximately 67 minutes. This is not necessarily wrong. A couple of things I've experienced in regards to long durations:

The job being executed is being blocked by something else and is continuously waiting on something else to complete before it can finish execution. The calling application is leaving the recordset open after calling. For example, if you use MS Access to open a table and don't bother to close it then it will show an extremely long duration (until the table is closed). Other applications could do the same thing if the author didn't properly include the code to close the recordset after retrieving the data. The client that's retrieving the data is on a particularly slow connection. Again - Duration is not the time it takes SQL Server to fulfill the request but the time it takes for the client to retrieve the data from the request and close the resultset. We once learned about a misconfigured network connection because durations were high while CPU and READs were low.|||The profiler duration field is in microseconds by default?|||No, its optional for SQL Server 2005.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Hiding 2005 Instance via TSQL

Is there a way to set the "Hide Instance" property of a SQL 2005 instance programmatically?

Every reference I find is for using the Configuration Manager GUI, but we need to create and hide instances in an automated setup prgram.

I was hoping that sp_Config and RECONFIGURE would do the trick, but the options don't seem to include hiding the instance.

I would appreciate any hints at where to look next. Thanks!

Hi Ted,

This flag is stored in the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.N\MSSQLServer\SuperSocketNetLib\HideInstance

Set the decimal value to 1 to hide - if you need to do this from within MSSQL, you can use the registry xp's (xp_regread and xp_regwrite) - as these are undocumented, the usual warnings apply.

Cheers,

Rob

|||

Perfect, thank you Rob, that is exactly what I was looking for.

-Ted