How to make this simple SELECT query perform faster

Hi,

I have the following simple SELECT query:

SELECT * FROM TRX_LAPD

But the problem that table TRX_LAPD is very big. Althoug, I am using this query in a Network envirenment.

If I implement this query inside Microsoft SQL Server, the first time is somehow slow (40 sec), but becasue of the caching cabability the next time is very fast.

The thing that I am using this query in Visual Web Developer, so to be accessed by other users in the local network, but it seems that there is no caching features; each time I execute the query the implementation remains slow (40 sec) even from the from the server PC which have the SQL Server running.

I used also a simple Stored Procedure, but nothing changed

Below my code in Visual Web Developer with VB script:

Sub getmytable()
Dim sql As String
sql = "SELECT * FROM TRX_LAPD"
'or sql = "EXEC getTRX_LAPD" if I will use the Stored Procedure

MySqlDataSource.SelectCommand = sql
'where MySQLDataSource is an SqlDataSource control
End Sub

and the Stored Procedure that I tried also:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[getTRX_LAPD]
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM TRX_LAPD
END

The SqlDatasource is bounded to a GridView control
I really appreciate any help.
Thanks

There are two basic rules of querying a database table:

1. Query only the rows that you need.
2. Query only the columns that you need.

By doing "SELECT * FROM TRX_LAPD", you are retreiving all the rows and all the columns all the time. Is that really a necessity? If you are doing this on a "very big" table (as you as say) it is bound to take time. I suggest you should revisit the design of querying the entire table every time.

How to make a Trigger for more tan 1 table?

Hi,
I have to put a Trigger on +-100 tables, and the Trigger will perform the
same action. Is there away to Create jsut 1 trigger that will work for all
the tables?
This for:
- don't having to make 100 times the same trigger
- just having to do maintenance on only 1 trigger and not 100
Any help would be really aprpeciated,
Thansk a lot,
Pieter
What about sending the action inside the trigger to a common stored
procedure which could be maintained easily ?
(Therefore is must be coded very good to keep up SQL Server db health ;-) )
HTH, Jens Suessmeyer.
"DraguVaso" <pietercoucke@.hotmail.com> schrieb im Newsbeitrag
news:escuv1WSFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> I have to put a Trigger on +-100 tables, and the Trigger will perform the
> same action. Is there away to Create jsut 1 trigger that will work for all
> the tables?
> This for:
> - don't having to make 100 times the same trigger
> - just having to do maintenance on only 1 trigger and not 100
> Any help would be really aprpeciated,
> Thansk a lot,
> Pieter
>
|||Hi
No, a trigger can only be bound to a single table.
A Trigger can call a stored procedure, but the Inserted and Deleted tables
are not available to the SP. You could put all your logic in the SP. But you
still have to put 100 triggers in place.
What does your trigger do?
Regards
Mike
"DraguVaso" wrote:

> Hi,
> I have to put a Trigger on +-100 tables, and the Trigger will perform the
> same action. Is there away to Create jsut 1 trigger that will work for all
> the tables?
> This for:
> - don't having to make 100 times the same trigger
> - just having to do maintenance on only 1 trigger and not 100
> Any help would be really aprpeciated,
> Thansk a lot,
> Pieter
>
>
|||Thanks.
My Triggrer has to insert the SQL-Query that fired the trigger in another
table, and add if it was an update/insert/delete trigger.
So I guess I won't be able to put it in a seperate SP...
this is my trigger:
CREATE TRIGGER TriggerName
ON categories
FOR INSERT, UPDATE, DELETE AS
BEGIN
SET NOCOUNT ON
DECLARE @.ExecStr varchar(50), @.Qry nvarchar(1000),
@.QueryType as nvarchar(10)
CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
SET @.ExecStr = 'DBCC INPUTBUFFER(' + STR(@.@.SPID) + ')'
INSERT INTO #inputbuffer
EXEC (@.ExecStr)
SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
IF EXISTS (SELECT * FROM inserted)
BEGIN
SET @.QueryType = 'INSERT'
IF EXISTS (SELECT * FROM deleted)
BEGIN
SET @.QueryType = 'UPDATE'
END
END
ELSE
BEGIN
IF EXISTS (SELECT * FROM deleted)
BEGIN
SET @.QueryType = 'DELETE'
END
END
INSERT INTO tblTransactions
SELECT @.Qry AS Query,
SYSTEM_USER as LoginName,
USER AS UserName,
CURRENT_TIMESTAMP AS QueryDate,
@.QueryType AS QueryType
END
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:0B9DFD0C-BF8A-4B46-B0D9-C74115A706F6@.microsoft.com...
> Hi
> No, a trigger can only be bound to a single table.
> A Trigger can call a stored procedure, but the Inserted and Deleted tables
> are not available to the SP. You could put all your logic in the SP. But
you[vbcol=seagreen]
> still have to put 100 triggers in place.
> What does your trigger do?
> Regards
> Mike
> "DraguVaso" wrote:
the[vbcol=seagreen]
all[vbcol=seagreen]
|||Did you consider third party tools for this, tools that are using the transaction log and probably
have less overhead? I've listed some of those tools on my links page (the log reader tools).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:OOLuVFXSFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Thanks.
> My Triggrer has to insert the SQL-Query that fired the trigger in another
> table, and add if it was an update/insert/delete trigger.
> So I guess I won't be able to put it in a seperate SP...
> this is my trigger:
> CREATE TRIGGER TriggerName
> ON categories
> FOR INSERT, UPDATE, DELETE AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.ExecStr varchar(50), @.Qry nvarchar(1000),
> @.QueryType as nvarchar(10)
> CREATE TABLE #inputbuffer
> (
> EventType nvarchar(30),
> Parameters int,
> EventInfo nvarchar(255)
> )
> SET @.ExecStr = 'DBCC INPUTBUFFER(' + STR(@.@.SPID) + ')'
> INSERT INTO #inputbuffer
> EXEC (@.ExecStr)
> SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
> IF EXISTS (SELECT * FROM inserted)
> BEGIN
> SET @.QueryType = 'INSERT'
> IF EXISTS (SELECT * FROM deleted)
> BEGIN
> SET @.QueryType = 'UPDATE'
> END
> END
> ELSE
> BEGIN
> IF EXISTS (SELECT * FROM deleted)
> BEGIN
> SET @.QueryType = 'DELETE'
> END
> END
> INSERT INTO tblTransactions
> SELECT @.Qry AS Query,
> SYSTEM_USER as LoginName,
> USER AS UserName,
> CURRENT_TIMESTAMP AS QueryDate,
> @.QueryType AS QueryType
> END
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:0B9DFD0C-BF8A-4B46-B0D9-C74115A706F6@.microsoft.com...
> you
> the
> all
>
|||I did consider them, but they aren't free... :-(
do you know some opensource/free tools?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OU2C9VXSFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Did you consider third party tools for this, tools that are using the
transaction log and probably
> have less overhead? I've listed some of those tools on my links page (the
log reader tools).[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:OOLuVFXSFHA.2964@.TK2MSFTNGP15.phx.gbl...
another[vbcol=seagreen]
tables[vbcol=seagreen]
But[vbcol=seagreen]
for
>
|||> do you know some opensource/free tools?
I don't think that there are any free log reader tools out there. I recall some free tool that
generates triggers for you, but I didn't find it now, looking though my links. Might be worth
googling for, though...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:upWCEmXSFHA.2784@.TK2MSFTNGP12.phx.gbl...
>I did consider them, but they aren't free... :-(
> do you know some opensource/free tools?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OU2C9VXSFHA.3296@.TK2MSFTNGP15.phx.gbl...
> transaction log and probably
> log reader tools).
> another
> tables
> But
> for
>
|||ok thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OP9wqrXSFHA.1236@.TK2MSFTNGP14.phx.gbl...
> I don't think that there are any free log reader tools out there. I recall
some free tool that
> generates triggers for you, but I didn't find it now, looking though my
links. Might be worth[vbcol=seagreen]
> googling for, though...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:upWCEmXSFHA.2784@.TK2MSFTNGP12.phx.gbl...
in[vbcol=seagreen]
(the[vbcol=seagreen]
perform
>
|||Open source tools are not free. Like any other software (including
anything you develop) they have a cost of implementation and ownership.
Sorry to state the obvious but the fallacy that you implied is too
commonly repeated. The cost of acquiring software is typically only a
small fraction of the cost of owning and running it.
If you want to log SQL statements without any capital outlay then you
could use SQL Profiler. You pay for this in performance on your server
though, so you may well find the third-party tools are cheaper :-).
David Portas
SQL Server MVP

How to make a Trigger for more tan 1 table?

Hi,
I have to put a Trigger on +-100 tables, and the Trigger will perform the
same action. Is there away to Create jsut 1 trigger that will work for all
the tables?
This for:
- don't having to make 100 times the same trigger
- just having to do maintenance on only 1 trigger and not 100
Any help would be really aprpeciated,
Thansk a lot,
PieterWhat about sending the action inside the trigger to a common stored
procedure which could be maintained easily ?
(Therefore is must be coded very good to keep up SQL Server db health ;-) )
HTH, Jens Suessmeyer.
"DraguVaso" <pietercoucke@.hotmail.com> schrieb im Newsbeitrag
news:escuv1WSFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> I have to put a Trigger on +-100 tables, and the Trigger will perform the
> same action. Is there away to Create jsut 1 trigger that will work for all
> the tables?
> This for:
> - don't having to make 100 times the same trigger
> - just having to do maintenance on only 1 trigger and not 100
> Any help would be really aprpeciated,
> Thansk a lot,
> Pieter
>|||Hi
No, a trigger can only be bound to a single table.
A Trigger can call a stored procedure, but the Inserted and Deleted tables
are not available to the SP. You could put all your logic in the SP. But you
still have to put 100 triggers in place.
What does your trigger do?
Regards
Mike
"DraguVaso" wrote:
> Hi,
> I have to put a Trigger on +-100 tables, and the Trigger will perform the
> same action. Is there away to Create jsut 1 trigger that will work for all
> the tables?
> This for:
> - don't having to make 100 times the same trigger
> - just having to do maintenance on only 1 trigger and not 100
> Any help would be really aprpeciated,
> Thansk a lot,
> Pieter
>
>|||Thanks.
My Triggrer has to insert the SQL-Query that fired the trigger in another
table, and add if it was an update/insert/delete trigger.
So I guess I won't be able to put it in a seperate SP...
this is my trigger:
CREATE TRIGGER TriggerName
ON categories
FOR INSERT, UPDATE, DELETE AS
BEGIN
SET NOCOUNT ON
DECLARE @.ExecStr varchar(50), @.Qry nvarchar(1000),
@.QueryType as nvarchar(10)
CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
SET @.ExecStr = 'DBCC INPUTBUFFER(' + STR(@.@.SPID) + ')'
INSERT INTO #inputbuffer
EXEC (@.ExecStr)
SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
IF EXISTS (SELECT * FROM inserted)
BEGIN
SET @.QueryType = 'INSERT'
IF EXISTS (SELECT * FROM deleted)
BEGIN
SET @.QueryType = 'UPDATE'
END
END
ELSE
BEGIN
IF EXISTS (SELECT * FROM deleted)
BEGIN
SET @.QueryType = 'DELETE'
END
END
INSERT INTO tblTransactions
SELECT @.Qry AS Query,
SYSTEM_USER as LoginName,
USER AS UserName,
CURRENT_TIMESTAMP AS QueryDate,
@.QueryType AS QueryType
END
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:0B9DFD0C-BF8A-4B46-B0D9-C74115A706F6@.microsoft.com...
> Hi
> No, a trigger can only be bound to a single table.
> A Trigger can call a stored procedure, but the Inserted and Deleted tables
> are not available to the SP. You could put all your logic in the SP. But
you
> still have to put 100 triggers in place.
> What does your trigger do?
> Regards
> Mike
> "DraguVaso" wrote:
> > Hi,
> >
> > I have to put a Trigger on +-100 tables, and the Trigger will perform
the
> > same action. Is there away to Create jsut 1 trigger that will work for
all
> > the tables?
> > This for:
> > - don't having to make 100 times the same trigger
> > - just having to do maintenance on only 1 trigger and not 100
> >
> > Any help would be really aprpeciated,
> >
> > Thansk a lot,
> >
> > Pieter
> >
> >
> >|||Did you consider third party tools for this, tools that are using the transaction log and probably
have less overhead? I've listed some of those tools on my links page (the log reader tools).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:OOLuVFXSFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Thanks.
> My Triggrer has to insert the SQL-Query that fired the trigger in another
> table, and add if it was an update/insert/delete trigger.
> So I guess I won't be able to put it in a seperate SP...
> this is my trigger:
> CREATE TRIGGER TriggerName
> ON categories
> FOR INSERT, UPDATE, DELETE AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.ExecStr varchar(50), @.Qry nvarchar(1000),
> @.QueryType as nvarchar(10)
> CREATE TABLE #inputbuffer
> (
> EventType nvarchar(30),
> Parameters int,
> EventInfo nvarchar(255)
> )
> SET @.ExecStr = 'DBCC INPUTBUFFER(' + STR(@.@.SPID) + ')'
> INSERT INTO #inputbuffer
> EXEC (@.ExecStr)
> SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
> IF EXISTS (SELECT * FROM inserted)
> BEGIN
> SET @.QueryType = 'INSERT'
> IF EXISTS (SELECT * FROM deleted)
> BEGIN
> SET @.QueryType = 'UPDATE'
> END
> END
> ELSE
> BEGIN
> IF EXISTS (SELECT * FROM deleted)
> BEGIN
> SET @.QueryType = 'DELETE'
> END
> END
> INSERT INTO tblTransactions
> SELECT @.Qry AS Query,
> SYSTEM_USER as LoginName,
> USER AS UserName,
> CURRENT_TIMESTAMP AS QueryDate,
> @.QueryType AS QueryType
> END
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:0B9DFD0C-BF8A-4B46-B0D9-C74115A706F6@.microsoft.com...
>> Hi
>> No, a trigger can only be bound to a single table.
>> A Trigger can call a stored procedure, but the Inserted and Deleted tables
>> are not available to the SP. You could put all your logic in the SP. But
> you
>> still have to put 100 triggers in place.
>> What does your trigger do?
>> Regards
>> Mike
>> "DraguVaso" wrote:
>> > Hi,
>> >
>> > I have to put a Trigger on +-100 tables, and the Trigger will perform
> the
>> > same action. Is there away to Create jsut 1 trigger that will work for
> all
>> > the tables?
>> > This for:
>> > - don't having to make 100 times the same trigger
>> > - just having to do maintenance on only 1 trigger and not 100
>> >
>> > Any help would be really aprpeciated,
>> >
>> > Thansk a lot,
>> >
>> > Pieter
>> >
>> >
>> >
>|||I did consider them, but they aren't free... :-(
do you know some opensource/free tools?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OU2C9VXSFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Did you consider third party tools for this, tools that are using the
transaction log and probably
> have less overhead? I've listed some of those tools on my links page (the
log reader tools).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:OOLuVFXSFHA.2964@.TK2MSFTNGP15.phx.gbl...
> > Thanks.
> > My Triggrer has to insert the SQL-Query that fired the trigger in
another
> > table, and add if it was an update/insert/delete trigger.
> >
> > So I guess I won't be able to put it in a seperate SP...
> >
> > this is my trigger:
> >
> > CREATE TRIGGER TriggerName
> > ON categories
> > FOR INSERT, UPDATE, DELETE AS
> > BEGIN
> > SET NOCOUNT ON
> >
> > DECLARE @.ExecStr varchar(50), @.Qry nvarchar(1000),
> > @.QueryType as nvarchar(10)
> >
> > CREATE TABLE #inputbuffer
> > (
> > EventType nvarchar(30),
> > Parameters int,
> > EventInfo nvarchar(255)
> > )
> >
> > SET @.ExecStr = 'DBCC INPUTBUFFER(' + STR(@.@.SPID) + ')'
> >
> > INSERT INTO #inputbuffer
> > EXEC (@.ExecStr)
> >
> > SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
> >
> > IF EXISTS (SELECT * FROM inserted)
> > BEGIN
> > SET @.QueryType = 'INSERT'
> > IF EXISTS (SELECT * FROM deleted)
> > BEGIN
> > SET @.QueryType = 'UPDATE'
> > END
> > END
> > ELSE
> > BEGIN
> > IF EXISTS (SELECT * FROM deleted)
> > BEGIN
> > SET @.QueryType = 'DELETE'
> > END
> > END
> >
> > INSERT INTO tblTransactions
> > SELECT @.Qry AS Query,
> > SYSTEM_USER as LoginName,
> > USER AS UserName,
> > CURRENT_TIMESTAMP AS QueryDate,
> > @.QueryType AS QueryType
> > END
> >
> >
> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> > news:0B9DFD0C-BF8A-4B46-B0D9-C74115A706F6@.microsoft.com...
> >> Hi
> >>
> >> No, a trigger can only be bound to a single table.
> >>
> >> A Trigger can call a stored procedure, but the Inserted and Deleted
tables
> >> are not available to the SP. You could put all your logic in the SP.
But
> > you
> >> still have to put 100 triggers in place.
> >>
> >> What does your trigger do?
> >>
> >> Regards
> >> Mike
> >>
> >> "DraguVaso" wrote:
> >>
> >> > Hi,
> >> >
> >> > I have to put a Trigger on +-100 tables, and the Trigger will perform
> > the
> >> > same action. Is there away to Create jsut 1 trigger that will work
for
> > all
> >> > the tables?
> >> > This for:
> >> > - don't having to make 100 times the same trigger
> >> > - just having to do maintenance on only 1 trigger and not 100
> >> >
> >> > Any help would be really aprpeciated,
> >> >
> >> > Thansk a lot,
> >> >
> >> > Pieter
> >> >
> >> >
> >> >
> >
> >
>|||> do you know some opensource/free tools?
I don't think that there are any free log reader tools out there. I recall some free tool that
generates triggers for you, but I didn't find it now, looking though my links. Might be worth
googling for, though...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:upWCEmXSFHA.2784@.TK2MSFTNGP12.phx.gbl...
>I did consider them, but they aren't free... :-(
> do you know some opensource/free tools?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OU2C9VXSFHA.3296@.TK2MSFTNGP15.phx.gbl...
>> Did you consider third party tools for this, tools that are using the
> transaction log and probably
>> have less overhead? I've listed some of those tools on my links page (the
> log reader tools).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
>> news:OOLuVFXSFHA.2964@.TK2MSFTNGP15.phx.gbl...
>> > Thanks.
>> > My Triggrer has to insert the SQL-Query that fired the trigger in
> another
>> > table, and add if it was an update/insert/delete trigger.
>> >
>> > So I guess I won't be able to put it in a seperate SP...
>> >
>> > this is my trigger:
>> >
>> > CREATE TRIGGER TriggerName
>> > ON categories
>> > FOR INSERT, UPDATE, DELETE AS
>> > BEGIN
>> > SET NOCOUNT ON
>> >
>> > DECLARE @.ExecStr varchar(50), @.Qry nvarchar(1000),
>> > @.QueryType as nvarchar(10)
>> >
>> > CREATE TABLE #inputbuffer
>> > (
>> > EventType nvarchar(30),
>> > Parameters int,
>> > EventInfo nvarchar(255)
>> > )
>> >
>> > SET @.ExecStr = 'DBCC INPUTBUFFER(' + STR(@.@.SPID) + ')'
>> >
>> > INSERT INTO #inputbuffer
>> > EXEC (@.ExecStr)
>> >
>> > SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
>> >
>> > IF EXISTS (SELECT * FROM inserted)
>> > BEGIN
>> > SET @.QueryType = 'INSERT'
>> > IF EXISTS (SELECT * FROM deleted)
>> > BEGIN
>> > SET @.QueryType = 'UPDATE'
>> > END
>> > END
>> > ELSE
>> > BEGIN
>> > IF EXISTS (SELECT * FROM deleted)
>> > BEGIN
>> > SET @.QueryType = 'DELETE'
>> > END
>> > END
>> >
>> > INSERT INTO tblTransactions
>> > SELECT @.Qry AS Query,
>> > SYSTEM_USER as LoginName,
>> > USER AS UserName,
>> > CURRENT_TIMESTAMP AS QueryDate,
>> > @.QueryType AS QueryType
>> > END
>> >
>> >
>> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> > news:0B9DFD0C-BF8A-4B46-B0D9-C74115A706F6@.microsoft.com...
>> >> Hi
>> >>
>> >> No, a trigger can only be bound to a single table.
>> >>
>> >> A Trigger can call a stored procedure, but the Inserted and Deleted
> tables
>> >> are not available to the SP. You could put all your logic in the SP.
> But
>> > you
>> >> still have to put 100 triggers in place.
>> >>
>> >> What does your trigger do?
>> >>
>> >> Regards
>> >> Mike
>> >>
>> >> "DraguVaso" wrote:
>> >>
>> >> > Hi,
>> >> >
>> >> > I have to put a Trigger on +-100 tables, and the Trigger will perform
>> > the
>> >> > same action. Is there away to Create jsut 1 trigger that will work
> for
>> > all
>> >> > the tables?
>> >> > This for:
>> >> > - don't having to make 100 times the same trigger
>> >> > - just having to do maintenance on only 1 trigger and not 100
>> >> >
>> >> > Any help would be really aprpeciated,
>> >> >
>> >> > Thansk a lot,
>> >> >
>> >> > Pieter
>> >> >
>> >> >
>> >> >
>> >
>> >
>>
>|||ok thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OP9wqrXSFHA.1236@.TK2MSFTNGP14.phx.gbl...
> > do you know some opensource/free tools?
> I don't think that there are any free log reader tools out there. I recall
some free tool that
> generates triggers for you, but I didn't find it now, looking though my
links. Might be worth
> googling for, though...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:upWCEmXSFHA.2784@.TK2MSFTNGP12.phx.gbl...
> >I did consider them, but they aren't free... :-(
> > do you know some opensource/free tools?
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:OU2C9VXSFHA.3296@.TK2MSFTNGP15.phx.gbl...
> >> Did you consider third party tools for this, tools that are using the
> > transaction log and probably
> >> have less overhead? I've listed some of those tools on my links page
(the
> > log reader tools).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> >> news:OOLuVFXSFHA.2964@.TK2MSFTNGP15.phx.gbl...
> >> > Thanks.
> >> > My Triggrer has to insert the SQL-Query that fired the trigger in
> > another
> >> > table, and add if it was an update/insert/delete trigger.
> >> >
> >> > So I guess I won't be able to put it in a seperate SP...
> >> >
> >> > this is my trigger:
> >> >
> >> > CREATE TRIGGER TriggerName
> >> > ON categories
> >> > FOR INSERT, UPDATE, DELETE AS
> >> > BEGIN
> >> > SET NOCOUNT ON
> >> >
> >> > DECLARE @.ExecStr varchar(50), @.Qry nvarchar(1000),
> >> > @.QueryType as nvarchar(10)
> >> >
> >> > CREATE TABLE #inputbuffer
> >> > (
> >> > EventType nvarchar(30),
> >> > Parameters int,
> >> > EventInfo nvarchar(255)
> >> > )
> >> >
> >> > SET @.ExecStr = 'DBCC INPUTBUFFER(' + STR(@.@.SPID) + ')'
> >> >
> >> > INSERT INTO #inputbuffer
> >> > EXEC (@.ExecStr)
> >> >
> >> > SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
> >> >
> >> > IF EXISTS (SELECT * FROM inserted)
> >> > BEGIN
> >> > SET @.QueryType = 'INSERT'
> >> > IF EXISTS (SELECT * FROM deleted)
> >> > BEGIN
> >> > SET @.QueryType = 'UPDATE'
> >> > END
> >> > END
> >> > ELSE
> >> > BEGIN
> >> > IF EXISTS (SELECT * FROM deleted)
> >> > BEGIN
> >> > SET @.QueryType = 'DELETE'
> >> > END
> >> > END
> >> >
> >> > INSERT INTO tblTransactions
> >> > SELECT @.Qry AS Query,
> >> > SYSTEM_USER as LoginName,
> >> > USER AS UserName,
> >> > CURRENT_TIMESTAMP AS QueryDate,
> >> > @.QueryType AS QueryType
> >> > END
> >> >
> >> >
> >> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> >> > news:0B9DFD0C-BF8A-4B46-B0D9-C74115A706F6@.microsoft.com...
> >> >> Hi
> >> >>
> >> >> No, a trigger can only be bound to a single table.
> >> >>
> >> >> A Trigger can call a stored procedure, but the Inserted and Deleted
> > tables
> >> >> are not available to the SP. You could put all your logic in the SP.
> > But
> >> > you
> >> >> still have to put 100 triggers in place.
> >> >>
> >> >> What does your trigger do?
> >> >>
> >> >> Regards
> >> >> Mike
> >> >>
> >> >> "DraguVaso" wrote:
> >> >>
> >> >> > Hi,
> >> >> >
> >> >> > I have to put a Trigger on +-100 tables, and the Trigger will
perform
> >> > the
> >> >> > same action. Is there away to Create jsut 1 trigger that will work
> > for
> >> > all
> >> >> > the tables?
> >> >> > This for:
> >> >> > - don't having to make 100 times the same trigger
> >> >> > - just having to do maintenance on only 1 trigger and not 100
> >> >> >
> >> >> > Any help would be really aprpeciated,
> >> >> >
> >> >> > Thansk a lot,
> >> >> >
> >> >> > Pieter
> >> >> >
> >> >> >
> >> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>|||Open source tools are not free. Like any other software (including
anything you develop) they have a cost of implementation and ownership.
Sorry to state the obvious but the fallacy that you implied is too
commonly repeated. The cost of acquiring software is typically only a
small fraction of the cost of owning and running it.
If you want to log SQL statements without any capital outlay then you
could use SQL Profiler. You pay for this in performance on your server
though, so you may well find the third-party tools are cheaper :-).
--
David Portas
SQL Server MVP
--

how to lock a row when perform SELECT statement

Hi,
i would like to know how to lock a table from other to view when select statement..can anyone help me with that?
i tried

SELECT * FROM IMIS_FRAMEWORK_RUNNING_NUMBER with (HOLDLOCK,ROWLOCK)
WHERE running_type=@.type;

but i m still able to see my row when i perform my SELECT STATEMENT from other query analyser..

Please help me with this..thank youCheck out this page, it shows you how to do what you're asking:

http://www.sql-server-performance.com/at_sql_locking.asp|||What you are asking to do is a sort of pessimistic lock, and this can be very dangerous in a web application. What happens e.g. when a user SELECTs the record and then their browser crashes?

You can read about concurrency control here:ASP.NET Optimistic Concurrency Control

Maybe if you explain more about what you are trying to do we can offer some suggestions.

Terri|||Thanks for helping ,

First of all i have a table to stores running number

tblRunningNumber
ID Desc LatestNumber
1 ModuleA 01
2 ModuleB 12

Then i wanna gat the latest runningnumber from tblRunningNumber n stores it in to my other table. For example i wanan get the latestNumber for ModuleA and stores it into ModuleA table.

To i need to have a stored procedure to lock the ModuleA rows n then update the latestNumber from 01 to 02 and at the same time update the 01 in my ModuleA database Table.

That y i need to lock the row..my issues is that when i do the select * statement it just hang there...until i close the connection or transactin is commited...

that the whole senarios that i m doing..can u give me any good suggestion on that coz i cannot use asp.net to control the running number so i decided to use storedprocedure .

Thanks alot.|||"can be very dangerous in a web application"

That's an understatement! Especially with a relatively unguarded select statement!|||But to ensure consistency, 'sometimes' you will have to enforce locking for the duration of a transcation. Can you tell us what would happen when a user SELECTs the record and then their browser crashes? The SELECT statement uses a HOLDLOCK. Isn't there a way to handle this crash scenario?|||

Holdlocks works on tables, pages, or rows it won't lock an entire database. If the commit and/or rollback is in the stored procedure, or contained in the batch sent to the SQL Server, then no. The batch and/or stored procedure will run to completion.

If you do something like issue this to the SQL Server: BEGIN TRANSACTION (Or start a transaction using the transaction object), then issue a SELECT ... (WITH HOLDLOCK) then crash before your ASP.NET application rollsback or commits the transaction, and the database server is remote, then yes, it'll be locked until the SQL Server realizes the connection is dead, and I'm not sure how long that would take. If it's a local SQL Server, then it realizes it immediate, and rollsback the transaction.

Also, the client crashing, disconnecting, going off-line, getting hit by lightning, etc means nothing. The server will continue executing it's page to completion no matter if the client is there or not, unless of course the page crashes itself, in which case the connection will be closed, and SQL Server will rollback it's transactions for those connections. Of course if the web server machine takes a complete dump (power loss, blue screen of death, etc), then SQL Server (if remote from the web server), will hold the lock until it realizes the connection is dead, and I'm not sure how quickly SQL Server detects a dead connection, but I'm guessing it'll be longer than you'd want in a web farm type environment.