application process don't continue after run trigger

Hi,
There is an application about cashier application process. I'd
wrote a trigger for send information messages to users. Send an e-mail
message when TransactType equal to db. But there is a problem, e-mail
message is sending but other application process is not continue and
record is not write to MSSQL database when TransactType equal to db.
Have any suggestion? Why is not application process continue after run
the Trigger proccess.
MSSQL version 7.0
Thanks.
CREATE TRIGGER sendmail_trigger ON [Transact]
FOR INSERT
AS
DECLARE
@.ptype varchar (100),
@.psubject varchar (100),
@.pacctid varchar (100),
@.ptransactamount varchar (100),
@.premail varchar (100),
@.pmessage varchar (900),
@.pname varchar (100)
set @.ptype = (select TransactType from Inserted)
if (@.ptype = 'db')
begin
set @.pacctid = (select AcctID from Inserted)
set @.ptransactamount = (select TransactAmount/-50 from Inserted)
set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
set @.psubject = 'Information Message'
set @.pmessage =
'Dear '+ @.pname+',
Debit '+ @.ptransactamount + ' your account.
For your information.'
EXEC master..xp_sendmail @.recipients = @.premail,
@.blind_copy_recipients = 'xxx@.yyy.zzz',
@.subject = @.psubject, @.message = @.pmessage
end
It is possible that you are getting an error, which may cause a scope
abort... Scope aborts do NOT return control back to the calling Sp etc, they
merely exit...
Comment out lines of code one at a time and see if you can find the line
which is hurting you... I suspect the email send may be causing the
problem...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"U A" <umuta@.sabanciuniv.edu> wrote in message
news:u1YkANWSEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> There is an application about cashier application process. I'd
> wrote a trigger for send information messages to users. Send an e-mail
> message when TransactType equal to db. But there is a problem, e-mail
> message is sending but other application process is not continue and
> record is not write to MSSQL database when TransactType equal to db.
> Have any suggestion? Why is not application process continue after run
> the Trigger proccess.
> MSSQL version 7.0
> Thanks.
> --
> CREATE TRIGGER sendmail_trigger ON [Transact]
> FOR INSERT
> AS
> DECLARE
> @.ptype varchar (100),
> @.psubject varchar (100),
> @.pacctid varchar (100),
> @.ptransactamount varchar (100),
> @.premail varchar (100),
> @.pmessage varchar (900),
> @.pname varchar (100)
> set @.ptype = (select TransactType from Inserted)
> if (@.ptype = 'db')
> begin
> set @.pacctid = (select AcctID from Inserted)
> set @.ptransactamount = (select TransactAmount/-50 from Inserted)
> set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
> set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
> set @.psubject = 'Information Message'
> set @.pmessage =
> 'Dear '+ @.pname+',
> Debit '+ @.ptransactamount + ' your account.
> For your information.'
> EXEC master..xp_sendmail @.recipients = @.premail,
> @.blind_copy_recipients = 'xxx@.yyy.zzz',
> @.subject = @.psubject, @.message = @.pmessage
> end
> --
>
|||Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
successfuly finished. But e-mail didn't send. What can I do for send
e-mail automatically this trigger method?
thanks.
On 03-06-2004 15:30, Wayne Snyder wrote:
> It is possible that you are getting an error, which may cause a scope
> abort... Scope aborts do NOT return control back to the calling Sp etc, they
> merely exit...
> Comment out lines of code one at a time and see if you can find the line
> which is hurting you... I suspect the email send may be causing the
> problem...
>
|||I suggest you don't send email from the trigger. Have the trigger to insert necessary information into a table
and create a job which runs every x minutes that reads off of this table and does the email sending.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"U A" <umuta@.sabanciuniv.edu> wrote in message news:e144dYXSEHA.3872@.TK2MSFTNGP10.phx.gbl...
> Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
> successfuly finished. But e-mail didn't send. What can I do for send
> e-mail automatically this trigger method?
> thanks.
>
> On 03-06-2004 15:30, Wayne Snyder wrote:
>
|||Thanks your suggestion but it gave same error. I had insert the records
to a new table and send e-mail with a remote perl script. I think,
trigger occasion exits from application.
thanks.
On 03-06-2004 18:55, Tibor Karaszi wrote:
> I suggest you don't send email from the trigger. Have the trigger to insert necessary
information into a table and create a job which runs every x minutes
that reads off of
this table and does the email sending.

application process don't continue after run trigger

Hi,
There is an application about cashier application process. I'd
wrote a trigger for send information messages to users. Send an e-mail
message when TransactType equal to db. But there is a problem, e-mail
message is sending but other application process is not continue and
record is not write to MSSQL database when TransactType equal to db.
Have any suggestion? Why is not application process continue after run
the Trigger proccess.
MSSQL version 7.0
Thanks.
--
CREATE TRIGGER sendmail_trigger ON [Transact]
FOR INSERT
AS
DECLARE
@.ptype varchar (100),
@.psubject varchar (100),
@.pacctid varchar (100),
@.ptransactamount varchar (100),
@.premail varchar (100),
@.pmessage varchar (900),
@.pname varchar (100)
set @.ptype = (select TransactType from Inserted)
if (@.ptype = 'db')
begin
set @.pacctid = (select AcctID from Inserted)
set @.ptransactamount = (select TransactAmount/-50 from Inserted)
set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
set @.psubject = 'Information Message'
set @.pmessage =
'Dear '+ @.pname+',
Debit '+ @.ptransactamount + ' your account.
For your information.'
EXEC master..xp_sendmail @.recipients = @.premail,
@.blind_copy_recipients = 'xxx@.yyy.zzz',
@.subject = @.psubject, @.message = @.pmessage
end
--It is possible that you are getting an error, which may cause a scope
abort... Scope aborts do NOT return control back to the calling Sp etc, they
merely exit...
Comment out lines of code one at a time and see if you can find the line
which is hurting you... I suspect the email send may be causing the
problem...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"U A" <umuta@.sabanciuniv.edu> wrote in message
news:u1YkANWSEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> There is an application about cashier application process. I'd
> wrote a trigger for send information messages to users. Send an e-mail
> message when TransactType equal to db. But there is a problem, e-mail
> message is sending but other application process is not continue and
> record is not write to MSSQL database when TransactType equal to db.
> Have any suggestion? Why is not application process continue after run
> the Trigger proccess.
> MSSQL version 7.0
> Thanks.
> --
> CREATE TRIGGER sendmail_trigger ON [Transact]
> FOR INSERT
> AS
> DECLARE
> @.ptype varchar (100),
> @.psubject varchar (100),
> @.pacctid varchar (100),
> @.ptransactamount varchar (100),
> @.premail varchar (100),
> @.pmessage varchar (900),
> @.pname varchar (100)
> set @.ptype = (select TransactType from Inserted)
> if (@.ptype = 'db')
> begin
> set @.pacctid = (select AcctID from Inserted)
> set @.ptransactamount = (select TransactAmount/-50 from Inserted)
> set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
> set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
> set @.psubject = 'Information Message'
> set @.pmessage =
> 'Dear '+ @.pname+',
> Debit '+ @.ptransactamount + ' your account.
> For your information.'
> EXEC master..xp_sendmail @.recipients = @.premail,
> @.blind_copy_recipients = 'xxx@.yyy.zzz',
> @.subject = @.psubject, @.message = @.pmessage
> end
> --
>|||Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
successfuly finished. But e-mail didn't send. What can I do for send
e-mail automatically this trigger method?
thanks.
On 03-06-2004 15:30, Wayne Snyder wrote:
> It is possible that you are getting an error, which may cause a scope
> abort... Scope aborts do NOT return control back to the calling Sp etc, th
ey
> merely exit...
> Comment out lines of code one at a time and see if you can find the line
> which is hurting you... I suspect the email send may be causing the
> problem...
>|||I suggest you don't send email from the trigger. Have the trigger to insert
necessary information into a table
and create a job which runs every x minutes that reads off of this table and
does the email sending.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"U A" <umuta@.sabanciuniv.edu> wrote in message news:e144dYXSEHA.3872@.TK2MSFTNGP10.phx.gbl...

> Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
> successfuly finished. But e-mail didn't send. What can I do for send
> e-mail automatically this trigger method?
> thanks.
>
> On 03-06-2004 15:30, Wayne Snyder wrote:
>|||Thanks your suggestion but it gave same error. I had insert the records
to a new table and send e-mail with a remote PERL script. I think,
trigger occasion exits from application.
thanks.
On 03-06-2004 18:55, Tibor Karaszi wrote:
> I suggest you don't send email from the trigger. Have the trigger to insert necess
ary
information into a table and create a job which runs every x minutes
that reads off of
this table and does the email sending.

application process don't continue after run trigger

Hi,
There is an application about cashier application process. I'd
wrote a trigger for send information messages to users. Send an e-mail
message when TransactType equal to db. But there is a problem, e-mail
message is sending but other application process is not continue and
record is not write to MSSQL database when TransactType equal to db.
Have any suggestion? Why is not application process continue after run
the Trigger proccess.
MSSQL version 7.0
Thanks.
--
CREATE TRIGGER sendmail_trigger ON [Transact]
FOR INSERT
AS
DECLARE
@.ptype varchar (100),
@.psubject varchar (100),
@.pacctid varchar (100),
@.ptransactamount varchar (100),
@.premail varchar (100),
@.pmessage varchar (900),
@.pname varchar (100)
set @.ptype = (select TransactType from Inserted)
if (@.ptype = 'db')
begin
set @.pacctid = (select AcctID from Inserted)
set @.ptransactamount = (select TransactAmount/-50 from Inserted)
set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
set @.psubject = 'Information Message'
set @.pmessage = 'Dear '+ @.pname+',
Debit '+ @.ptransactamount + ' your account.
For your information.'
EXEC master..xp_sendmail @.recipients = @.premail,
@.blind_copy_recipients = 'xxx@.yyy.zzz',
@.subject = @.psubject, @.message = @.pmessage
end
--It is possible that you are getting an error, which may cause a scope
abort... Scope aborts do NOT return control back to the calling Sp etc, they
merely exit...
Comment out lines of code one at a time and see if you can find the line
which is hurting you... I suspect the email send may be causing the
problem...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"U A" <umuta@.sabanciuniv.edu> wrote in message
news:u1YkANWSEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> There is an application about cashier application process. I'd
> wrote a trigger for send information messages to users. Send an e-mail
> message when TransactType equal to db. But there is a problem, e-mail
> message is sending but other application process is not continue and
> record is not write to MSSQL database when TransactType equal to db.
> Have any suggestion? Why is not application process continue after run
> the Trigger proccess.
> MSSQL version 7.0
> Thanks.
> --
> CREATE TRIGGER sendmail_trigger ON [Transact]
> FOR INSERT
> AS
> DECLARE
> @.ptype varchar (100),
> @.psubject varchar (100),
> @.pacctid varchar (100),
> @.ptransactamount varchar (100),
> @.premail varchar (100),
> @.pmessage varchar (900),
> @.pname varchar (100)
> set @.ptype = (select TransactType from Inserted)
> if (@.ptype = 'db')
> begin
> set @.pacctid = (select AcctID from Inserted)
> set @.ptransactamount = (select TransactAmount/-50 from Inserted)
> set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
> set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
> set @.psubject = 'Information Message'
> set @.pmessage => 'Dear '+ @.pname+',
> Debit '+ @.ptransactamount + ' your account.
> For your information.'
> EXEC master..xp_sendmail @.recipients = @.premail,
> @.blind_copy_recipients = 'xxx@.yyy.zzz',
> @.subject = @.psubject, @.message = @.pmessage
> end
> --
>|||Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
successfuly finished. But e-mail didn't send. What can I do for send
e-mail automatically this trigger method?
thanks.
On 03-06-2004 15:30, Wayne Snyder wrote:
> It is possible that you are getting an error, which may cause a scope
> abort... Scope aborts do NOT return control back to the calling Sp etc, they
> merely exit...
> Comment out lines of code one at a time and see if you can find the line
> which is hurting you... I suspect the email send may be causing the
> problem...
>|||I suggest you don't send email from the trigger. Have the trigger to insert necessary information into a table
and create a job which runs every x minutes that reads off of this table and does the email sending.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"U A" <umuta@.sabanciuniv.edu> wrote in message news:e144dYXSEHA.3872@.TK2MSFTNGP10.phx.gbl...
> Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
> successfuly finished. But e-mail didn't send. What can I do for send
> e-mail automatically this trigger method?
> thanks.
>
> On 03-06-2004 15:30, Wayne Snyder wrote:
> > It is possible that you are getting an error, which may cause a scope
> > abort... Scope aborts do NOT return control back to the calling Sp etc, they
> > merely exit...
> >
> > Comment out lines of code one at a time and see if you can find the line
> > which is hurting you... I suspect the email send may be causing the
> > problem...
> >
>|||Thanks your suggestion but it gave same error. I had insert the records
to a new table and send e-mail with a remote perl script. I think,
trigger occasion exits from application.
thanks.
On 03-06-2004 18:55, Tibor Karaszi wrote:
> I suggest you don't send email from the trigger. Have the trigger to insert necessary
information into a table and create a job which runs every x minutes
that reads off of
this table and does the email sending.

App receiving "Options" message from Service Broker

I have an app receiving messages from SQL Service Broker when data is updated. (Messages are located at http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationinfo.aspx )

When I run this app against a remote SQL Server, I receive the message "Updated" which I expect.

But when I run the same app against the local machine SQL Server, I receive the message "Options".

Does anyone know if there are SQL Server options that must be set to certain values?
I can't seem to find anything that troubleshoots this message... either from a SQLServer- or a .NET standpoint.

JFoushee wrote:

Does anyone know if there are SQL Server options that must be set to certain values?
I can't seem to find anything that troubleshoots this message... either from a SQLServer- or a .NET standpoint.

From http://msdn2.microsoft.com/en-us/library/ms181122.aspx:

SET Option Settings

When a SELECT statement is executed under a notification request, the connection that submits the request must have the options for the connection set as follows:

ANSI_NULLS ON

ANSI_PADDING ON

ANSI_WARNINGS ON

CONCAT_NULL_YIELDS_NULL ON

QUOTED_IDENTIFIER ON

NUMERIC_ROUNDABORT OFF

ARITHABORT ON|||

On my local machine,
sp_configure 'user options' returns 0 for config_value .

So I ran the enumeration for the options above...

ANSI_NULLS ON 32
ANSI_PADDING ON 16
ANSI_WARNINGS ON 8
CONCAT_NULL_YIELDS_NULL ON 4096
QUOTED_IDENTIFIER ON 256
NUMERIC_ROUNDABORT OFF
ARITHABORT ON 64

4474

And then I ran this against my local machine...

sp_configure 'user options', 4474

and the program suddenly works!

|||

The recommended way of fixing this is to actualy run the SET options from the user connection. This way the app sets it's needed settings overwritting whatever defaults are in the database/instance.

HTH,
~ Remus

|||

Agreed, I was using the example to explain this wasn't the app's fault.
It still manages to work against the remote server with no intervention. (Why?)

Thanks for your assistance.

|||

JFoushee wrote:

It still manages to work against the remote server with no intervention. (Why?)

sp_configure changes the global instance level settings. I'd guess that the remote server and local server are different at the database level settings (ALTER DATABASE ... SET ...)

HTH,
~ Remus

|||

I ran a compare between the two databases.

The local one, in 80-compatibility, needed the various SET options.

The remote one, in 90-compatibility, needed nothing extra to work.

When I changed the local one to 90-compatibility, the program magically worked, with or without the SET options.

(I downloaded the pubs database from MS for the example and attached to both local and remote.)

Anyway to track SQL Server Agent error messages

We are in on SQL2000 and our SQL Server Agent started to receive errors for
Mail Profile not available. It wasn't until we looked at the agent error log
we saw the problem.
Is there any way to report the SQL Server Agent Warnings/Errors via email or
alert as a pro-active stance?
JTS
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1There is product called ProIT 4 (I work for the company and wrote the SQL
monitoring function in it) that will do it. ProIT is a server & network
monitoring software package. In additionally to writting it, we use it
internally and have customers using it to monitor both the sql error log and
the system eventlogs for SQL Agent job failures and SQL errors.
"jsheldon via SQLMonster.com" wrote:
> We are in on SQL2000 and our SQL Server Agent started to receive errors for
> Mail Profile not available. It wasn't until we looked at the agent error log
> we saw the problem.
> Is there any way to report the SQL Server Agent Warnings/Errors via email or
> alert as a pro-active stance?
> JTS
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||Netmon what is the web address?
Thanks
Netmon wrote:
>There is product called ProIT 4 (I work for the company and wrote the SQL
>monitoring function in it) that will do it. ProIT is a server & network
>monitoring software package. In additionally to writting it, we use it
>internally and have customers using it to monitor both the sql error log and
>the system eventlogs for SQL Agent job failures and SQL errors.
>> We are in on SQL2000 and our SQL Server Agent started to receive errors for
>> Mail Profile not available. It wasn't until we looked at the agent error log
>[quoted text clipped - 4 lines]
>> JTS
--
Message posted via http://www.sqlmonster.com

Anyway to track SQL Server Agent error messages

We are in on SQL2000 and our SQL Server Agent started to receive errors for
Mail Profile not available. It wasn't until we looked at the agent error log
we saw the problem.
Is there any way to report the SQL Server Agent Warnings/Errors via email or
alert as a pro-active stance?
JTS
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
There is product called ProIT 4 (I work for the company and wrote the SQL
monitoring function in it) that will do it. ProIT is a server & network
monitoring software package. In additionally to writting it, we use it
internally and have customers using it to monitor both the sql error log and
the system eventlogs for SQL Agent job failures and SQL errors.
"jsheldon via droptable.com" wrote:

> We are in on SQL2000 and our SQL Server Agent started to receive errors for
> Mail Profile not available. It wasn't until we looked at the agent error log
> we saw the problem.
> Is there any way to report the SQL Server Agent Warnings/Errors via email or
> alert as a pro-active stance?
> JTS
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
|||Netmon what is the web address?
Thanks
Netmon wrote:[vbcol=seagreen]
>There is product called ProIT 4 (I work for the company and wrote the SQL
>monitoring function in it) that will do it. ProIT is a server & network
>monitoring software package. In additionally to writting it, we use it
>internally and have customers using it to monitor both the sql error log and
>the system eventlogs for SQL Agent job failures and SQL errors.
>[quoted text clipped - 4 lines]
Message posted via http://www.droptable.com

Anyway to track SQL Server Agent error messages

We are in on SQL2000 and our SQL Server Agent started to receive errors for
Mail Profile not available. It wasn't until we looked at the agent error lo
g
we saw the problem.
Is there any way to report the SQL Server Agent Warnings/Errors via email or
alert as a pro-active stance?
JTS
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1There is product called ProIT 4 (I work for the company and wrote the SQL
monitoring function in it) that will do it. ProIT is a server & network
monitoring software package. In additionally to writting it, we use it
internally and have customers using it to monitor both the sql error log and
the system eventlogs for SQL Agent job failures and SQL errors.
"jsheldon via droptable.com" wrote:

> We are in on SQL2000 and our SQL Server Agent started to receive errors fo
r
> Mail Profile not available. It wasn't until we looked at the agent error
log
> we saw the problem.
> Is there any way to report the SQL Server Agent Warnings/Errors via email
or
> alert as a pro-active stance?
> JTS
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>|||Netmon what is the web address?
Thanks
Netmon wrote:[vbcol=seagreen]
>There is product called ProIT 4 (I work for the company and wrote the SQL
>monitoring function in it) that will do it. ProIT is a server & network
>monitoring software package. In additionally to writting it, we use it
>internally and have customers using it to monitor both the sql error log an
d
>the system eventlogs for SQL Agent job failures and SQL errors.
>
>[quoted text clipped - 4 lines]
Message posted via http://www.droptable.com