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!

Hilary and Paul

Thank you both for all your help. This board would be
sorely lacking without the two of you.
Hilary: Heres the deal. Your custom sync object was
working, mine wasn't.
@.sync_method = N'character'
That was the whole problem. I know you asked me to try
this a while back and I did, but I have no idea what else
I was doing back then that would have affected this
outcome. You're example on the 08/11 didnt specify (not
blaming you by any means) and by then I had forgot you're
mentioning it.
Paul: from you're reply @. 1:59 am. Do you ever sleep?
>
I personally would prefer to use indexed views.
I suppose simplistically the downside is that there is
increased overhead on
the publisher to maintain the materialized data.
I see the huge benifit being not needing to write the
ins,upd, and del procs with the Indexed View option. What
increased overhead are you refering too? Does the whole
Arithabort thing ever become a real pain?
Thanks again both of you.
ChrisR
ChrisR,
for an indexed view you've got 2 types of overhead:
a.. The disk space taken up by the view
a.. The cost of maintaining the view on disk as the base tables are
modified - this is conceptually like a set of invisible triggers.
BTW I'm not really such an SQL insomniac :-) I guess the post times are
Western US timezone times: UK - 7 hours.
Regards,
Paul Ibison
|||Ahhh. I forgot you were in the UK. Thanks.

>--Original Message--
>ChrisR,
>for an indexed view you've got 2 types of overhead:
>a.. The disk space taken up by the view
>a.. The cost of maintaining the view on disk as the base
tables are
>modified - this is conceptually like a set of invisible
triggers.
>BTW I'm not really such an SQL insomniac :-) I guess the
post times are
>Western US timezone times: UK - 7 hours.
>Regards,
>Paul Ibison
>
>.
>

Hiding a SP in Access under Query, but retain Execute on SP

Hello,

Access XP, SQL Server 2000

Is it possible to hide a SP under Queries in Access, yet still be able
to Execute it from Access?

We hooked up a custom form to accept the input parameters (MS Feature
Request!) for the Stored Procedure. We had two problems with MS's
'Input Parameter' dialog: 1) We could not customize, 2) We continually
received a message from Access stating, "The stored procedure executed
successfully but did not return records" ('SET NOCOUNT ON' gave no
joy).

Below is the sample code we are using.
TestInsert is the SP
txtName is a text box on the form
@.name is char(80)

Private Sub cmdAdd_Enter()

Dim cmd As New ADODB.Command
Dim par As Parameter

Set cmd.ActiveConnection = CurrentProject.Connection

cmd.CommandText = "TestInsert"
cmd.CommandType = adCmdStoredProc

Set par = cmd.CreateParameter("@.name", adVarChar, _
adParamInput, Len(txtName), txtName)

cmd.Parameters.Append par

cmd.Execute

Set cmd = Nothing

End Sub

Any help would be appreciated. We successfully hid our Tables, and
allowed access through Views. But we can't seem to find a good work
around here.

Thanks

Jeff
Jeffrey Walton
noloaderNoloader (noloader@.yahoo.com) writes:
> Is it possible to hide a SP under Queries in Access, yet still be able
> to Execute it from Access?
> We hooked up a custom form to accept the input parameters (MS Feature
> Request!) for the Stored Procedure. We had two problems with MS's
> 'Input Parameter' dialog: 1) We could not customize, 2) We continually
> received a message from Access stating, "The stored procedure executed
> successfully but did not return records" ('SET NOCOUNT ON' gave no
> joy).

I have to admit that I understand very little of this. But it could
be because I don't know Access. Probably you should put this question
to comp.databases.ms-access instead.

As for the procedures not returning result sets - did you expect them
to return result sets? Did you run them from Query Analyzer?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The most common advice is not to use the @. character in variable nemes with
the Jet engine. If this does not work then a specific cast of the input vale
to the parameter type might work.
Martin
"Noloader" <noloader@.yahoo.com> wrote in message
news:6b543aa7.0405061431.45bfa457@.posting.google.c om...
> Hello,
> Access XP, SQL Server 2000
> Is it possible to hide a SP under Queries in Access, yet still be able
> to Execute it from Access?
> We hooked up a custom form to accept the input parameters (MS Feature
> Request!) for the Stored Procedure. We had two problems with MS's
> 'Input Parameter' dialog: 1) We could not customize, 2) We continually
> received a message from Access stating, "The stored procedure executed
> successfully but did not return records" ('SET NOCOUNT ON' gave no
> joy).
> Below is the sample code we are using.
> TestInsert is the SP
> txtName is a text box on the form
> @.name is char(80)
> Private Sub cmdAdd_Enter()
> Dim cmd As New ADODB.Command
> Dim par As Parameter
> Set cmd.ActiveConnection = CurrentProject.Connection
> cmd.CommandText = "TestInsert"
> cmd.CommandType = adCmdStoredProc
> Set par = cmd.CreateParameter("@.name", adVarChar, _
> adParamInput, Len(txtName), txtName)
> cmd.Parameters.Append par
> cmd.Execute
> Set cmd = Nothing
> End Sub
> Any help would be appreciated. We successfully hid our Tables, and
> allowed access through Views. But we can't seem to find a good work
> around here.
> Thanks
> Jeff
> Jeffrey Walton
> noloader|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94E293F8A543Yazorman@.127.0.0.1>...
> Noloader (noloader@.yahoo.com) writes:
> > Is it possible to hide a SP under Queries in Access, yet still be able
> > to Execute it from Access?
> > We hooked up a custom form to accept the input parameters (MS Feature
> > Request!) for the Stored Procedure. We had two problems with MS's
> > 'Input Parameter' dialog: 1) We could not customize, 2) We continually
> > received a message from Access stating, "The stored procedure executed
> > successfully but did not return records" ('SET NOCOUNT ON' gave no
> > joy).
> I have to admit that I understand very little of this. But it could
> be because I don't know Access. Probably you should put this question
> to comp.databases.ms-access instead.
Thanks for the suggestion. Done.

> As for the procedures not returning result sets - did you expect them
> to return result sets? Did you run them from Query Analyzer?
The SP does not return any records (its a basic insert). I don't
understand why Access is throwing the dialog. I take a cockpit
approach - silent is good. Don't flash the lights and blow the whistle
until something goes wrong.

Thanks for the help Erland and Martin.

Jeff|||noloader@.yahoo.com (Noloader) wrote in message news:<6b543aa7.0405061431.45bfa457@.posting.google.com>...
> Hello,
> Access XP, SQL Server 2000
> Is it possible to hide a SP under Queries in Access, yet still be able
> to Execute it from Access?
> We hooked up a custom form to accept the input parameters (MS Feature
> Request!) for the Stored Procedure. We had two problems with MS's
> 'Input Parameter' dialog: 1) We could not customize, 2) We continually
> received a message from Access stating, "The stored procedure executed
> successfully but did not return records" ('SET NOCOUNT ON' gave no
> joy).
> Below is the sample code we are using.
> TestInsert is the SP
> txtName is a text box on the form
> @.name is char(80)
> Private Sub cmdAdd_Enter()
> Dim cmd As New ADODB.Command
> Dim par As Parameter
> Set cmd.ActiveConnection = CurrentProject.Connection
> cmd.CommandText = "TestInsert"
> cmd.CommandType = adCmdStoredProc
> Set par = cmd.CreateParameter("@.name", adVarChar, _
> adParamInput, Len(txtName), txtName)
> cmd.Parameters.Append par
> cmd.Execute
> Set cmd = Nothing
> End Sub
> Any help would be appreciated. We successfully hid our Tables, and
> allowed access through Views. But we can't seem to find a good work
> around here.
> Thanks
> Jeff
> Jeffrey Walton
> noloader

> 2) We continually received a message from Access
> stating, "The stored procedure executed successfully
> but did not return records" ('SET NOCOUNT ON' gave no joy).

We modified the Execute call:

Dim cmd As New ADODB.Command
Dim par As Parameter

Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "TestInsert"
cmd.CommandType = adCmdStoredProc

Set par = cmd.CreateParameter("@.name", adVarChar, _
adParamInput, Len(txtName), txtName)

cmd.Parameters.Append par

cmd.Execute , , adExecuteNoRecords

Set cmd = Nothing
Set par = Nothing

Jeff