How to set Concurrent execution of 2 insert statements

Hi,
I ahve the followign trigger. In this I want to set the 2 Insert statements
at the bottom, to execute concurrently. How can I do that?
CREATE TRIGGER [Identity_Trigger] ON dbo.WyethDataCard
FOR INSERT
AS
DECLARE @.CompOrderNo VarChar(30)
DECLARE @.Sno int
DECLARE @.EndUser varchar(25)
DECLARE @.ESN varchar(20)
DECLARE @.MinNo varchar(12)
SELECT @.Sno=SNo, @.EndUser=[End User], @.ESN=ESN , @.MinNo=MobileNo from
inserted --where CompanyORderNo is null
UPDATE WyethDataCard
SET CompanyORderNo= CONVERT(VARCHAR(15), 'TK-WT-VZ-'+RTRIM(SNo) )
SELECT @.CompOrderNo= CompanyORderNo from WyethDataCard
INSERT INTO OrderStatus ( Company_OrderNo,Status_ID,Created_Date,C
ompanySno
)
VALUES( @.CompOrderNo ,'25', GetDate(),@.Sno )
INSERT INTO EndUserChangeHistory
(MasterOrderNo,ESN,MinNo,EndUser,Modifie
dBy,ModifiedOn)
VALUES ( @.CompOrderNo,
@.ESN,
@.MinNo,
@.EndUser,
'Database Import / Manual Insert',
GetDate()
)
Is there some statement like CONCURRENT EXEC to achieve this?
Thanks
pmudWhy do you want to make them concurrent? Even if the machine has several pro
cessors, there's no
guarantee that this will happen at the same time. If you want both or nothin
g, you should have some
error handling in the trigger (see the articles on error handling at www.sommarsko
g.se).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:8338600F-74B5-4742-85E6-62789BD55AFD@.microsoft.com...
> Hi,
> I ahve the followign trigger. In this I want to set the 2 Insert statement
s
> at the bottom, to execute concurrently. How can I do that?
> CREATE TRIGGER [Identity_Trigger] ON dbo.WyethDataCard
> FOR INSERT
> AS
> DECLARE @.CompOrderNo VarChar(30)
> DECLARE @.Sno int
> DECLARE @.EndUser varchar(25)
> DECLARE @.ESN varchar(20)
> DECLARE @.MinNo varchar(12)
> SELECT @.Sno=SNo, @.EndUser=[End User], @.ESN=ESN , @.MinNo=MobileNo from
> inserted --where CompanyORderNo is null
> UPDATE WyethDataCard
> SET CompanyORderNo= CONVERT(VARCHAR(15), 'TK-WT-VZ-'+RTRIM(SNo) )
> SELECT @.CompOrderNo= CompanyORderNo from WyethDataCard
> INSERT INTO OrderStatus ( Company_OrderNo,Status_ID,Created_Date,C
ompanyS
no)
> VALUES( @.CompOrderNo ,'25', GetDate(),@.Sno )
> INSERT INTO EndUserChangeHistory
> (MasterOrderNo,ESN,MinNo,EndUser,Modifie
dBy,ModifiedOn)
> VALUES ( @.CompOrderNo,
> @.ESN,
> @.MinNo,
> @.EndUser,
> 'Database Import / Manual Insert',
> GetDate()
> )
> Is there some statement like CONCURRENT EXEC to achieve this?
> Thanks
> --
> pmud|||Hi Tibor,
Actually, by concurrent execution, I meant parallel execution of the 2
statements. Is tehre any way I can set parallel execution fo teh 2 statement
s
rather than having one after the other?
Thanks
--
pmud
"Tibor Karaszi" wrote:

> Why do you want to make them concurrent? Even if the machine has several p
rocessors, there's no
> guarantee that this will happen at the same time. If you want both or noth
ing, you should have some
> error handling in the trigger (see the articles on error handling at www.sommars
kog.se).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:8338600F-74B5-4742-85E6-62789BD55AFD@.microsoft.com...
>|||Do you mean that the inserts should be atomic or within a single
transaction?
If so, then refer to this document, specifically the section titled
"Database Transactions".
http://msdn.microsoft.com/library/d...ent.
asp
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:8338600F-74B5-4742-85E6-62789BD55AFD@.microsoft.com...
> Hi,
> I ahve the followign trigger. In this I want to set the 2 Insert
> statements
> at the bottom, to execute concurrently. How can I do that?
> CREATE TRIGGER [Identity_Trigger] ON dbo.WyethDataCard
> FOR INSERT
> AS
> DECLARE @.CompOrderNo VarChar(30)
> DECLARE @.Sno int
> DECLARE @.EndUser varchar(25)
> DECLARE @.ESN varchar(20)
> DECLARE @.MinNo varchar(12)
> SELECT @.Sno=SNo, @.EndUser=[End User], @.ESN=ESN , @.MinNo=MobileNo from
> inserted --where CompanyORderNo is null
> UPDATE WyethDataCard
> SET CompanyORderNo= CONVERT(VARCHAR(15), 'TK-WT-VZ-'+RTRIM(SNo) )
> SELECT @.CompOrderNo= CompanyORderNo from WyethDataCard
> INSERT INTO OrderStatus (
> Company_OrderNo,Status_ID,Created_Date,C
ompanySno)
> VALUES( @.CompOrderNo ,'25', GetDate(),@.Sno )
> INSERT INTO EndUserChangeHistory
> (MasterOrderNo,ESN,MinNo,EndUser,Modifie
dBy,ModifiedOn)
> VALUES ( @.CompOrderNo,
> @.ESN,
> @.MinNo,
> @.EndUser,
> 'Database Import / Manual Insert',
> GetDate()
> )
> Is there some statement like CONCURRENT EXEC to achieve this?
> Thanks
> --
> pmud|||no
why?
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:B0D56D29-D70C-4684-9F7F-0957CBFF6443@.microsoft.com...
> Hi Tibor,
> Actually, by concurrent execution, I meant parallel execution of the 2
> statements. Is tehre any way I can set parallel execution fo teh 2
> statements
> rather than having one after the other?
> Thanks
> --
> pmud
>
> "Tibor Karaszi" wrote:
>|||Is there any tutorial or book you rae aware which I can read to quickly come
up to speed with transctions and how they are implemeted?
Actually, I am aware of the basic concept of trnsctions but have never
implemeted them.
Do you have any suggestions for me to get up to speed Quickly?
Thanks
--
pmud
"JT" wrote:

> Do you mean that the inserts should be atomic or within a single
> transaction?
> If so, then refer to this document, specifically the section titled
> "Database Transactions".
> http://msdn.microsoft.com/library/d...en
t.asp
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:8338600F-74B5-4742-85E6-62789BD55AFD@.microsoft.com...
>
>|||You will not need a book to understand the concept of basic transactions.
Database transaction:
http://en.wikipedia.org/wiki/Database_transaction
BEGIN TRANSACTION
http://msdn2.microsoft.com/en-us/library/ms188929(SQL.90).aspx
COMMIT TRANSACTION
http://msdn2.microsoft.com/en-us/library/ms190295(SQL.90).aspx
ROLLBACK TRANSACTION
http://msdn2.microsoft.com/en-us/library/ms181299(SQL.90).aspx
The simplified sample below attempts to transfer a balance of $100 between 2
accounts. If all goes well, the transaction is committed (saved) and the
value of 1 is returned. If there is an error, then the transaction is rolled
back (cancelled), and a value of -1 or -2 is returned. By executing both
updates within a transactions, we insure that the work is either all saved
or all cancelled and the accounts are balanced.
begin transaction
update Accounts set balance = balance - 100 where acct_number = 4625
if @.@.error <> 0
begin
rollback transaction
return -1
end
update Accounts set balance = balance + 100 where acct_number = 4650
if @.@.error <> 0
begin
rollback transaction
return -2
end
commit transaction
return 1
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:33501F2A-06A5-4CA4-A15F-3D8987DE9C80@.microsoft.com...
> Is there any tutorial or book you rae aware which I can read to quickly
> come
> up to speed with transctions and how they are implemeted?
> Actually, I am aware of the basic concept of trnsctions but have never
> implemeted them.
> Do you have any suggestions for me to get up to speed Quickly?
> Thanks
> --
> pmud
>
> "JT" wrote:
>|||Thanks JT. That is definitely helpful. :)
--
pmud
"JT" wrote:

> You will not need a book to understand the concept of basic transactions.
> Database transaction:
> http://en.wikipedia.org/wiki/Database_transaction
> BEGIN TRANSACTION
> http://msdn2.microsoft.com/en-us/library/ms188929(SQL.90).aspx
> COMMIT TRANSACTION
> http://msdn2.microsoft.com/en-us/library/ms190295(SQL.90).aspx
> ROLLBACK TRANSACTION
> http://msdn2.microsoft.com/en-us/library/ms181299(SQL.90).aspx
> The simplified sample below attempts to transfer a balance of $100 between
2
> accounts. If all goes well, the transaction is committed (saved) and the
> value of 1 is returned. If there is an error, then the transaction is roll
ed
> back (cancelled), and a value of -1 or -2 is returned. By executing both
> updates within a transactions, we insure that the work is either all saved
> or all cancelled and the accounts are balanced.
> begin transaction
> update Accounts set balance = balance - 100 where acct_number = 4625
> if @.@.error <> 0
> begin
> rollback transaction
> return -1
> end
> update Accounts set balance = balance + 100 where acct_number = 4650
> if @.@.error <> 0
> begin
> rollback transaction
> return -2
> end
> commit transaction
> return 1
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:33501F2A-06A5-4CA4-A15F-3D8987DE9C80@.microsoft.com...
>
>|||pmud (pmud@.discussions.microsoft.com) writes:
> I ahve the followign trigger. In this I want to set the 2 Insert
> statements at the bottom, to execute concurrently. How can I do that?
You can't, and there would be no point with it.
On the other hand:

> SELECT @.Sno=SNo, @.EndUser=[End User], @.ESN=ESN , @.MinNo=MobileNo from
> inserted --where CompanyORderNo is null
This is not going to work out well. Triggers in SQL Server fires
once *per statement*, so the inserted table can hold many rows, which
your trigger fails to handle.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

how to send mail using trigger

Hello
I have created a update grigger. I want to send a mail whenever is trigger
is fired. I tried using xp_sendmail, but it required mapi account to be
configured in server. I want to avoid this situation. Can I use cdo code in
trigger?
regards
Ashish
"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
I believe so. But I wouldn't recommend it.
It'll really mess up performance.
You're better off having the trigger enter a row into a table and then
having a scheduled task go through once every X minutes sending emails.

> regards
> Ashish
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Well for SQL Server 2000 I agree, but as the Mail Service is now
enabled with SQL Server Service Broker in SQL Server 2005 you *could*
consider doing this within a trigger.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Try xp_smtp_sendmail.
TheSQLGuru
President
Indicium Resources, Inc.
"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
> regards
> Ashish
>
|||DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Hello All
I tried following solution, it is working for me. I like to know pros/cons
on this if any.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20649
Ashish
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegr oups.com...
DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||I already pointed out the cons, the same applies for this solution as
it might stale your application and the transaction with it. I
addition, the process might cancel the transaction if you execute it
within a trigger, depending on the error level coming back, The con
against a solution with a mail table and a scheduled delivery is that
it would not be send immediately, but you could implement something
like a retry whereas you cannot do this in the above procedure used
within a trigger. The best thing is to use the Service broker enabled
mail service if you are using SQL Server 2005. If you are using SQL
Server 2000 you should use a mail table which queues up mails and
sends them periodically.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Sorry, I didn't see the user state he was using SQL Server 2005. Oh, wait.
.. . he didn't! :-)
TheSQLGuru
President
Indicium Resources, Inc.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegr oups.com...
DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de

how to send mail using trigger

Hello
I have created a update grigger. I want to send a mail whenever is trigger
is fired. I tried using xp_sendmail, but it required mapi account to be
configured in server. I want to avoid this situation. Can I use cdo code in
trigger?
regards
Ashish"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
I believe so. But I wouldn't recommend it.
It'll really mess up performance.
You're better off having the trigger enter a row into a table and then
having a scheduled task go through once every X minutes sending emails.

> regards
> Ashish
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Well for SQL Server 2000 I agree, but as the Mail Service is now
enabled with SQL Server Service Broker in SQL Server 2005 you *could*
consider doing this within a trigger.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Try xp_smtp_sendmail.
TheSQLGuru
President
Indicium Resources, Inc.
"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
> regards
> Ashish
>|||DOn=B4t use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Hello All
I tried following solution, it is working for me. I like to know pros/cons
on this if any.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20649
Ashish
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegroups.com...
DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||I already pointed out the cons, the same applies for this solution as
it might stale your application and the transaction with it. I
addition, the process might cancel the transaction if you execute it
within a trigger, depending on the error level coming back, The con
against a solution with a mail table and a scheduled delivery is that
it would not be send immediately, but you could implement something
like a retry whereas you cannot do this in the above procedure used
within a trigger. The best thing is to use the Service broker enabled
mail service if you are using SQL Server 2005. If you are using SQL
Server 2000 you should use a mail table which queues up mails and
sends them periodically.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Sorry, I didn't see the user state he was using SQL Server 2005. Oh, wait.
. . he didn't! :-)
TheSQLGuru
President
Indicium Resources, Inc.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegroups.com...
DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--

how to send mail using trigger

Hello
I have created a update grigger. I want to send a mail whenever is trigger
is fired. I tried using xp_sendmail, but it required mapi account to be
configured in server. I want to avoid this situation. Can I use cdo code in
trigger?
regards
Ashish"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
I believe so. But I wouldn't recommend it.
It'll really mess up performance.
You're better off having the trigger enter a row into a table and then
having a scheduled task go through once every X minutes sending emails.
> regards
> Ashish
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Well for SQL Server 2000 I agree, but as the Mail Service is now
enabled with SQL Server Service Broker in SQL Server 2005 you *could*
consider doing this within a trigger.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Try xp_smtp_sendmail.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
> regards
> Ashish
>|||DOn=B4t use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Hello All
I tried following solution, it is working for me. I like to know pros/cons
on this if any.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20649
Ashish
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegroups.com...
DOn´t use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||I already pointed out the cons, the same applies for this solution as
it might stale your application and the transaction with it. I
addition, the process might cancel the transaction if you execute it
within a trigger, depending on the error level coming back, The con
against a solution with a mail table and a scheduled delivery is that
it would not be send immediately, but you could implement something
like a retry whereas you cannot do this in the above procedure used
within a trigger. The best thing is to use the Service broker enabled
mail service if you are using SQL Server 2005. If you are using SQL
Server 2000 you should use a mail table which queues up mails and
sends them periodically.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Sorry, I didn't see the user state he was using SQL Server 2005. Oh, wait.
. . he didn't! :-)
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegroups.com...
DOn´t use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--

How to send mail throuth Trigger ?...

Hi,

I am using Trigger to send me a mail, when ever any DML operation occur on my DepartmentMaster table. my trigger is as follow.

CREATE TRIGGER reminderON DepartmentMasterFOR INSERT,UPDATE,DELETE AS EXEC master..xp_sendmail'MaryM','Don''t forget to print a report for the distributors.'

delete from DepartmentMaster where deptcode = '02'

my out express is configured with default account. but when ever i delete any row from DepartmentMaster table mail is not sent and i get following error..

Server: Msg 18030, Level 16, State 1, Line 0
xp_sendmail: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.

Which version of SQL Server are you using?

|||

Sql Server 2000

|||

You need to configure the SQL Mail component. Have a look at:

http://www.evogenic.com/downloads/evo_sql_mail1.pdf SQL Mail Tutorial