script for trace form Query analyzer ?

I want to know which procs and triggers that are taking a lot of time to run. I cannot run the trace coz its slowing down. There are lot of procs and triggers written over several years.
TIA
Are you saying that Profiler is resulting in performance degradation? On the
client or server?
You might want to trace using system stored procedures, on the server side.
This is much better than running Profiler interactively.
For more info:
http://vyaskn.tripod.com/server_side...sql_server.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
news:DB0143FE-769C-486D-8473-EA2CD95DBE17@.microsoft.com...
I want to know which procs and triggers that are taking a lot of time to
run. I cannot run the trace coz its slowing down. There are lot of procs
and triggers written over several years.
TIA
|||Do you by any chance know which systables can I look up to get the same info as from the server procs.
or else by any chance do you know how to look at the script contained in the below procs
sp_trace_create
sp_trace_generateevent
sp_trace_setevent
sp_trace_setfilter
sp_trace_setstatus
fn_trace_getfilterinfo
fn_trace_getinfo Returns
fn_trace_gettable Returns
thanks !!
"Narayana Vyas Kondreddi" wrote:

> Are you saying that Profiler is resulting in performance degradation? On the
> client or server?
> You might want to trace using system stored procedures, on the server side.
> This is much better than running Profiler interactively.
> For more info:
> http://vyaskn.tripod.com/server_side...sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
> news:DB0143FE-769C-486D-8473-EA2CD95DBE17@.microsoft.com...
> I want to know which procs and triggers that are taking a lot of time to
> run. I cannot run the trace coz its slowing down. There are lot of procs
> and triggers written over several years.
> TIA
>
>
|||This info is not stored in any of the system tables.
I think the server side trace stored procs are implemented as extended
stored procs, so the source code is not available
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
news:AA76511D-FD1C-4670-B083-78FF5A3F65E7@.microsoft.com...
Do you by any chance know which systables can I look up to get the same info
as from the server procs.
or else by any chance do you know how to look at the script contained in the
below procs
sp_trace_create
sp_trace_generateevent
sp_trace_setevent
sp_trace_setfilter
sp_trace_setstatus
fn_trace_getfilterinfo
fn_trace_getinfo Returns
fn_trace_gettable Returns
thanks !!
"Narayana Vyas Kondreddi" wrote:

> Are you saying that Profiler is resulting in performance degradation? On
the
> client or server?
> You might want to trace using system stored procedures, on the server
side.
> This is much better than running Profiler interactively.
> For more info:
> http://vyaskn.tripod.com/server_side...sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
> news:DB0143FE-769C-486D-8473-EA2CD95DBE17@.microsoft.com...
> I want to know which procs and triggers that are taking a lot of time to
> run. I cannot run the trace coz its slowing down. There are lot of procs
> and triggers written over several years.
> TIA
>
>

script for trace form Query analyzer ?

I want to know which procs and triggers that are taking a lot of time to run
. I cannot run the trace coz its slowing down. There are lot of procs and t
riggers written over several years.
TIAAre you saying that Profiler is resulting in performance degradation? On the
client or server?
You might want to trace using system stored procedures, on the server side.
This is much better than running Profiler interactively.
For more info:
http://vyaskn.tripod.com/server_sid..._sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
news:DB0143FE-769C-486D-8473-EA2CD95DBE17@.microsoft.com...
I want to know which procs and triggers that are taking a lot of time to
run. I cannot run the trace coz its slowing down. There are lot of procs
and triggers written over several years.
TIA|||Do you by any chance know which systables can I look up to get the same info
as from the server procs.
or else by any chance do you know how to look at the script contained in the
below procs
sp_trace_create
sp_trace_generateevent
sp_trace_setevent
sp_trace_setfilter
sp_trace_setstatus
fn_trace_getfilterinfo
fn_trace_getinfo Returns
fn_trace_gettable Returns
thanks !!
"Narayana Vyas Kondreddi" wrote:

> Are you saying that Profiler is resulting in performance degradation? On t
he
> client or server?
> You might want to trace using system stored procedures, on the server side
.
> This is much better than running Profiler interactively.
> For more info:
> http://vyaskn.tripod.com/server_sid..._sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
> news:DB0143FE-769C-486D-8473-EA2CD95DBE17@.microsoft.com...
> I want to know which procs and triggers that are taking a lot of time to
> run. I cannot run the trace coz its slowing down. There are lot of procs
> and triggers written over several years.
> TIA
>
>|||This info is not stored in any of the system tables.
I think the server side trace stored procs are implemented as extended
stored procs, so the source code is not available
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
news:AA76511D-FD1C-4670-B083-78FF5A3F65E7@.microsoft.com...
Do you by any chance know which systables can I look up to get the same info
as from the server procs.
or else by any chance do you know how to look at the script contained in the
below procs
sp_trace_create
sp_trace_generateevent
sp_trace_setevent
sp_trace_setfilter
sp_trace_setstatus
fn_trace_getfilterinfo
fn_trace_getinfo Returns
fn_trace_gettable Returns
thanks !!
"Narayana Vyas Kondreddi" wrote:

> Are you saying that Profiler is resulting in performance degradation? On
the
> client or server?
> You might want to trace using system stored procedures, on the server
side.
> This is much better than running Profiler interactively.
> For more info:
> http://vyaskn.tripod.com/server_sid..._sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
> news:DB0143FE-769C-486D-8473-EA2CD95DBE17@.microsoft.com...
> I want to know which procs and triggers that are taking a lot of time to
> run. I cannot run the trace coz its slowing down. There are lot of procs
> and triggers written over several years.
> TIA
>
>

scope_identity()

I have an ASP front end on SQL 2000 database. I have a form that submits to
an insert query. The entry field is an "identity" and the primary key. I
have used scope_identity() to display the entry# of the record just entered
on the confirmation page. Now I need to insert the entry into another
table. This is my query:

SET NOCOUNT ON
INSERT wo_main
(site_id, customer, po_number)
VALUES ('::site_id::', '::customer::', '::po_number::')
SELECT scope_identity() AS entry
INSERT INTO wo_combo_body
(entry) VALUES ('::entry::')
SET nocount off

This query displays the entry number of the record just entered, but inserts
a 0 in to entry field of the 2nd table. Any help would be great.

Thanks,
Darren>SELECT scope_identity() AS entry
does not assign the identity to a variable named entry, it just
returns a recordset like any other select

either
declare @.Entry int
set @.Entry = (select scope_identity())
insert into table (field) values (@.Entry)

or
insert int table (field) values (select scope_identity())

also move the set nocount off to the top

On Fri, 20 Feb 2004 19:40:54 GMT, "Scrappy"
<celtics@.lan-specialist.com> wrote:

>I have an ASP front end on SQL 2000 database. I have a form that submits to
>an insert query. The entry field is an "identity" and the primary key. I
>have used scope_identity() to display the entry# of the record just entered
>on the confirmation page. Now I need to insert the entry into another
>table. This is my query:
>SET NOCOUNT ON
>INSERT wo_main
> (site_id, customer, po_number)
>VALUES ('::site_id::', '::customer::', '::po_number::')
>SELECT scope_identity() AS entry
>INSERT INTO wo_combo_body
>(entry) VALUES ('::entry::')
>SET nocount off
>This query displays the entry number of the record just entered, but inserts
>a 0 in to entry field of the 2nd table. Any help would be great.
>Thanks,
>Darren|||Hi

If you can use a local variable to hold what is returned by SCOPE_IDENTITY.
This variable can then be used in the second insert statement. You may also
want to add some error checking! Rather than returning a result set it may
also be better(faster) to return the value as a parameter

John

"Scrappy" <celtics@.lan-specialist.com> wrote in message
news:aptZb.32366$um1.10431@.twister.nyroc.rr.com...
> I have an ASP front end on SQL 2000 database. I have a form that submits
to
> an insert query. The entry field is an "identity" and the primary key. I
> have used scope_identity() to display the entry# of the record just
entered
> on the confirmation page. Now I need to insert the entry into another
> table. This is my query:
> SET NOCOUNT ON
> INSERT wo_main
> (site_id, customer, po_number)
> VALUES ('::site_id::', '::customer::', '::po_number::')
> SELECT scope_identity() AS entry
> INSERT INTO wo_combo_body
> (entry) VALUES ('::entry::')
> SET nocount off
> This query displays the entry number of the record just entered, but
inserts
> a 0 in to entry field of the 2nd table. Any help would be great.
> Thanks,
> Darren|||Thanks! I used a trigger to accomplish this. I am new to SQL. Are there
any pitfalls with doing it with a trigger?

Also...

On the same confirmation page I want to diplay links to a page for each
table. Basically I need to select the entry field from each table that I
have inserted to with the trigger. I can then use this as a hyperlink to
the each page. Any ideas on this?

"Bruce Loving" <BRUCE@.LOVINGSCENTS.COM> wrote in message
news:tbtc30lq99c2h3lvjgrh93nt9hmqk1hisc@.4ax.com...
> >SELECT scope_identity() AS entry
> does not assign the identity to a variable named entry, it just
> returns a recordset like any other select
> either
> declare @.Entry int
> set @.Entry = (select scope_identity())
> insert into table (field) values (@.Entry)
> or
> insert int table (field) values (select scope_identity())
> also move the set nocount off to the top
> On Fri, 20 Feb 2004 19:40:54 GMT, "Scrappy"
> <celtics@.lan-specialist.com> wrote:
> >I have an ASP front end on SQL 2000 database. I have a form that submits
to
> >an insert query. The entry field is an "identity" and the primary key.
I
> >have used scope_identity() to display the entry# of the record just
entered
> >on the confirmation page. Now I need to insert the entry into another
> >table. This is my query:
> >SET NOCOUNT ON
> >INSERT wo_main
> > (site_id, customer, po_number)
> >VALUES ('::site_id::', '::customer::', '::po_number::')
> >SELECT scope_identity() AS entry
> >INSERT INTO wo_combo_body
> >(entry) VALUES ('::entry::')
> >SET nocount off
> >This query displays the entry number of the record just entered, but
inserts
> >a 0 in to entry field of the 2nd table. Any help would be great.
> >Thanks,
> >Darren|||Hi

You have very little scope to insert new records in a secondary table if you
use a trigger, as you can not pass parameters to it. If there is only one
column in the second table it should be redundant. If there are other
columns then you should write a stored procedure and use a transaction to
maintain consistency see books online :
BEGIN TRANSACTION:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_ba-bz_96zy.htm
ROLLBACK TRANSACTION:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_ra-rz_471q.htm
COMMIT TRANSACTION:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_ca-co_7w6m.htm

You should be able to identify the values inserted in a session by including
identifying data in the table such as Username, Datatime, SessionId etc.

John

"Scrappy" <celtics@.lan-specialist.com> wrote in message
news:bnxZb.71491$n62.1826@.twister.nyroc.rr.com...
> Thanks! I used a trigger to accomplish this. I am new to SQL. Are there
> any pitfalls with doing it with a trigger?
> Also...
> On the same confirmation page I want to diplay links to a page for each
> table. Basically I need to select the entry field from each table that I
> have inserted to with the trigger. I can then use this as a hyperlink to
> the each page. Any ideas on this?
>
> "Bruce Loving" <BRUCE@.LOVINGSCENTS.COM> wrote in message
> news:tbtc30lq99c2h3lvjgrh93nt9hmqk1hisc@.4ax.com...
> > >SELECT scope_identity() AS entry
> > does not assign the identity to a variable named entry, it just
> > returns a recordset like any other select
> > either
> > declare @.Entry int
> > set @.Entry = (select scope_identity())
> > insert into table (field) values (@.Entry)
> > or
> > insert int table (field) values (select scope_identity())
> > also move the set nocount off to the top
> > On Fri, 20 Feb 2004 19:40:54 GMT, "Scrappy"
> > <celtics@.lan-specialist.com> wrote:
> > >I have an ASP front end on SQL 2000 database. I have a form that
submits
> to
> > >an insert query. The entry field is an "identity" and the primary key.
> I
> > >have used scope_identity() to display the entry# of the record just
> entered
> > >on the confirmation page. Now I need to insert the entry into another
> > >table. This is my query:
> > > >SET NOCOUNT ON
> > >INSERT wo_main
> > > (site_id, customer, po_number)
> > >VALUES ('::site_id::', '::customer::', '::po_number::')
> > >SELECT scope_identity() AS entry
> > >INSERT INTO wo_combo_body
> > >(entry) VALUES ('::entry::')
> > >SET nocount off
> > > >This query displays the entry number of the record just entered, but
> inserts
> > >a 0 in to entry field of the 2nd table. Any help would be great.
> > > >Thanks,
> > >Darren
>