exec xp_sendmail error on SQL Server 2000

I have try to send a mail via xp_sendmail in Query Analyzer and it succeeded.
So I try to have it executed in a trigger but it failed.

Here is the trigger creation script and error message

use mlcb
go
if exists (select name
from sysobjects
where name = 'test' and
type = 'TR')
DROP TRIGGER TEST
GO

CREATE TRIGGER test on mlcb.dbo.trans_errlog
for insert
as
declare @.email_subject varchar(100),
@.email_content varchar(4000),
@.email_recipients varchar(50)

set @.email_subject='SQL Mail test mail'
set @.email_recipients='some@.world.com.tw'
set @.email_content='this is a test mail, don't reply this mail'

exec master.dbo.xp_sendmail @.recipients=@.email_recipients,@.subject=@.email_subj ect,@.message=@.email_content
GO

Error Message:
Server: Msg 2812, Level 16, State 62, Line 6
Could not find stored procedure 'master.xp_startmail'.
The statement has been terminated.

Appreciate any prompt reply.

JDbefore we get to your problem, let's talk about this for a second.

is your logic valid if more than one record is inserted at a time?

have you thought about the associated overhead for each transaction here?

have you thought about your sql server getting hung up try to connect to exchange if the mail server is suddenly unavailable?

I am nearly certain your problem is permissions related. which is another can of worms.|||before we get to your problem, let's talk about this for a second.

is your logic valid if more than one record is inserted at a time?

have you thought about the associated overhead for each transaction here?

have you thought about your sql server getting hung up try to connect to exchange if the mail server is suddenly unavailable?

I am nearly certain your problem is permissions related. which is another can of worms.

Thanks very much for your reminders
I really didn't think about these.

Only one record is inserted at a time.
There is no much transaction, just something like error notification.

mmmmm, I didn't know unavailable exchange server will cause such issue.
So how can I avoid it?

How to fix the permission issue?

Thanks for your help!!

JD|||Let me echo the warning that was already posted. When you use SQL Mail in SQL 2000, you are opening a potential can of worms. Outlook is a single threaded application. If it hangs for any reason (say the Exchange server takes a vacation), you can end up with a heap of trouble. I tried running a subscription based service off of SQL 7.0/2000 back in '00/'01. We had to abandon that effort because SQL kept hanging whenever the mail server went off line (or network connectivity prevented a connection).

Go with something that is lightweight (ie, SMTP). Consider some other method for sending notifications; insert a record into a table, create an external app that runs on a schedule to watch that table, etc. Anything but this.

That said,

Error Message:
Server: Msg 2812, Level 16, State 62, Line 6
Could not find stored procedure 'master.xp_startmail'.
The statement has been terminated.

Have you checked for the existence of this sp in master? the name looks wrong to me. It should by rights be 'master.dbo.xp_startmail'.

Have you configured a SQL Mail profile? Is the profile correct? Can you send mail outside of the trigger?

Regards,

hmscott|||hi hmscott,

Thanks for helping me away such dangerous condition.

It is really bad hear that. I thought I am almost there.
I will take your advice not using SQL Mail. There is no such warning heard before when searching around the web. Now I have to go from the beginning.

Can you provide any reference for SMTP usage?

But I am still want to know how to solve the issue I have right now.
I can run the script out of trigger.

I did exec master.dbo.xp_startmail.

master.xp_startmail was in the error message when firing the trigger.

Best Regards,
JD|||My first suggestion for looking into SMTP would be to investigate SQL 2005. SQL 2005 introduces Database Mail which is an SMTP based solution and works very well (you can even define multiple SMTP servers in case the primary is out to lunch somewhere). Besides, anything new you are designing now should be done in SQL 2005 since mainstream support for SQL 2000 won't be around too much longer...

As far as your error message...are you certain that you have configured SQL Mail correctly? Be sure you differentiate between SQL Mail and SQL Agent Mail. They work the same way (using Outlook and an Outlook Profile), but they must be configured separately.

Also, did you check for the existence of the master.dbo.sp_startmail proc?

Regards,

hmscott

EXEC Query Performance

I am running into a situation where a program runs a stored procedure, withi
n that stored procedure a SQL statement is built and then executed using the
EXEC command. It looks like that when the generated statement exceeds a ce
rtain time threshold, the s
tored procedure exits without any warning.
I know there is no warning because I have run profiler and I get a statement
start time but no end time. I have also logged every step in the stored pr
ocedure and it just bails on me. Any ideas?Funny you mention that Peter, I run the stored procedure in QA and it runs f
ine. The funny thing is that when the stored proc fails, the rest of the pro
cess completes. I have not been able to get access to the .Net code to view
how it is executed yet.
"Dan" wrote:

> I am running into a situation where a program runs a stored procedure, within that
stored procedure a SQL statement is built and then executed using the EXEC command.
It looks like that when the generated statement exceeds a certain time threshold,
the
stored procedure exits without any warning.
> I know there is no warning because I have run profiler and I get a statement start
time but no end time. I have also logged every step in the stored procedure and it
just bails on me. Any ideas?|||Your connection timeout setting is probably too low. Try adjusting it.
Andrew J. Kelly SQL MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> I am running into a situation where a program runs a stored procedure,
within that stored procedure a SQL statement is built and then executed
using the EXEC command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure exits without any
warning.
> I know there is no warning because I have run profiler and I get a
statement start time but no end time. I have also logged every step in the
stored procedure and it just bails on me. Any ideas?|||Andrew, that is what I am thinking too. I have finally gotten a hold of the
code the developer uses to call this. Can any of you see anything wrong wi
th this code?
Dim cnTemp As Connection
Dim rsTemp As Recordset '--ADODB.Recordset
Dim strSQL As String
Set cnTemp = New ADODB.Connection
Set rsTemp = New ADODB.Recordset
cnTemp.ConnectionString = "Provider=SQLOLEDB;Data Source=PowerWare2000;Initi
al Catalog=PWProd; User ID=*******;Password=********;"
cnTemp.Open
strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
rsTemp.CursorLocation = adUseClient
rsTemp.CursorType = adOpenStatic
rsTemp.LockType = adLockOptimistic
rsTemp.Open strSQL, cnTemp, , , adCmdText
"Andrew J. Kelly" wrote:

> Your connection timeout setting is probably too low. Try adjusting it.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> within that stored procedure a SQL statement is built and then executed
> using the EXEC command. It looks like that when the generated statement
> exceeds a certain time threshold, the stored procedure exits without any
> warning.
> statement start time but no end time. I have also logged every step in th
e
> stored procedure and it just bails on me. Any ideas?
>
>|||You don't actually set the timeout and by default I believe it is set to 30
seconds. This should help with that:
http://www.aspfaq.com/show.asp?id=2066
But you also want to optimize these queries so they don't take so long to
begin with. Using stored procedures with parameters instead of adhoc sql is
a good start.
Andrew J. Kelly SQL MVP
"Daniel Avsec" <DanielAvsec@.discussions.microsoft.com> wrote in message
news:9F7A3F1B-3868-4B19-9A5F-63462C3D48A4@.microsoft.com...
> Andrew, that is what I am thinking too. I have finally gotten a hold of
the code the developer uses to call this. Can any of you see anything wrong
with this code?
> Dim cnTemp As Connection
> Dim rsTemp As Recordset '--ADODB.Recordset
> Dim strSQL As String
> Set cnTemp = New ADODB.Connection
> Set rsTemp = New ADODB.Recordset
> cnTemp.ConnectionString = "Provider=SQLOLEDB;Data
Source=PowerWare2000;Initial Catalog=PWProd; User
ID=*******;Password=********;"[vbcol=seagreen]
> cnTemp.Open
> strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
> rsTemp.CursorLocation = adUseClient
> rsTemp.CursorType = adOpenStatic
> rsTemp.LockType = adLockOptimistic
> rsTemp.Open strSQL, cnTemp, , , adCmdText
> "Andrew J. Kelly" wrote:
>
the[vbcol=seagreen]sql

EXEC Query Performance

I am running into a situation where a program runs a stored procedure, within that stored procedure a SQL statement is built and then executed using the EXEC command. It looks like that when the generated statement exceeds a certain time threshold, the stored procedure exits without any warning.
I know there is no warning because I have run profiler and I get a statement start time but no end time. I have also logged every step in the stored procedure and it just bails on me. Any ideas?How are you executing the original SP, and have you tried
it in QA ?
>--Original Message--
>I am running into a situation where a program runs a
stored procedure, within that stored procedure a SQL
statement is built and then executed using the EXEC
command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure
exits without any warning.
>I know there is no warning because I have run profiler
and I get a statement start time but no end time. I have
also logged every step in the stored procedure and it just
bails on me. Any ideas?
>.
>|||Your connection timeout setting is probably too low. Try adjusting it.
--
Andrew J. Kelly SQL MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> I am running into a situation where a program runs a stored procedure,
within that stored procedure a SQL statement is built and then executed
using the EXEC command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure exits without any
warning.
> I know there is no warning because I have run profiler and I get a
statement start time but no end time. I have also logged every step in the
stored procedure and it just bails on me. Any ideas?|||You don't actually set the timeout and by default I believe it is set to 30
seconds. This should help with that:
http://www.aspfaq.com/show.asp?id=2066
But you also want to optimize these queries so they don't take so long to
begin with. Using stored procedures with parameters instead of adhoc sql is
a good start.
--
Andrew J. Kelly SQL MVP
"Daniel Avsec" <DanielAvsec@.discussions.microsoft.com> wrote in message
news:9F7A3F1B-3868-4B19-9A5F-63462C3D48A4@.microsoft.com...
> Andrew, that is what I am thinking too. I have finally gotten a hold of
the code the developer uses to call this. Can any of you see anything wrong
with this code?
> Dim cnTemp As Connection
> Dim rsTemp As Recordset '--ADODB.Recordset
> Dim strSQL As String
> Set cnTemp = New ADODB.Connection
> Set rsTemp = New ADODB.Recordset
> cnTemp.ConnectionString = "Provider=SQLOLEDB;Data
Source=PowerWare2000;Initial Catalog=PWProd; User
ID=*******;Password=********;"
> cnTemp.Open
> strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
> rsTemp.CursorLocation = adUseClient
> rsTemp.CursorType = adOpenStatic
> rsTemp.LockType = adLockOptimistic
> rsTemp.Open strSQL, cnTemp, , , adCmdText
> "Andrew J. Kelly" wrote:
> > Your connection timeout setting is probably too low. Try adjusting it.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Dan" <Dan@.discussions.microsoft.com> wrote in message
> > news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> > > I am running into a situation where a program runs a stored procedure,
> > within that stored procedure a SQL statement is built and then executed
> > using the EXEC command. It looks like that when the generated statement
> > exceeds a certain time threshold, the stored procedure exits without any
> > warning.
> > >
> > > I know there is no warning because I have run profiler and I get a
> > statement start time but no end time. I have also logged every step in
the
> > stored procedure and it just bails on me. Any ideas?
> >
> >
> >

EXEC Query Performance

I am running into a situation where a program runs a stored procedure, within that stored procedure a SQL statement is built and then executed using the EXEC command. It looks like that when the generated statement exceeds a certain time threshold, the s
tored procedure exits without any warning.
I know there is no warning because I have run profiler and I get a statement start time but no end time. I have also logged every step in the stored procedure and it just bails on me. Any ideas?
Funny you mention that Peter, I run the stored procedure in QA and it runs fine. The funny thing is that when the stored proc fails, the rest of the process completes. I have not been able to get access to the .Net code to view how it is executed yet.
"Dan" wrote:

> I am running into a situation where a program runs a stored procedure, within that stored procedure a SQL statement is built and then executed using the EXEC command. It looks like that when the generated statement exceeds a certain time threshold, the
stored procedure exits without any warning.
> I know there is no warning because I have run profiler and I get a statement start time but no end time. I have also logged every step in the stored procedure and it just bails on me. Any ideas?
|||Your connection timeout setting is probably too low. Try adjusting it.
Andrew J. Kelly SQL MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> I am running into a situation where a program runs a stored procedure,
within that stored procedure a SQL statement is built and then executed
using the EXEC command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure exits without any
warning.
> I know there is no warning because I have run profiler and I get a
statement start time but no end time. I have also logged every step in the
stored procedure and it just bails on me. Any ideas?
|||Andrew, that is what I am thinking too. I have finally gotten a hold of the code the developer uses to call this. Can any of you see anything wrong with this code?
Dim cnTemp As Connection
Dim rsTemp As Recordset '--ADODB.Recordset
Dim strSQL As String
Set cnTemp = New ADODB.Connection
Set rsTemp = New ADODB.Recordset
cnTemp.ConnectionString = "Provider=SQLOLEDB;Data Source=PowerWare2000;Initial Catalog=PWProd; User ID=*******;Password=********;"
cnTemp.Open
strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
rsTemp.CursorLocation = adUseClient
rsTemp.CursorType = adOpenStatic
rsTemp.LockType = adLockOptimistic
rsTemp.Open strSQL, cnTemp, , , adCmdText
"Andrew J. Kelly" wrote:

> Your connection timeout setting is probably too low. Try adjusting it.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> within that stored procedure a SQL statement is built and then executed
> using the EXEC command. It looks like that when the generated statement
> exceeds a certain time threshold, the stored procedure exits without any
> warning.
> statement start time but no end time. I have also logged every step in the
> stored procedure and it just bails on me. Any ideas?
>
>
|||You don't actually set the timeout and by default I believe it is set to 30
seconds. This should help with that:
http://www.aspfaq.com/show.asp?id=2066
But you also want to optimize these queries so they don't take so long to
begin with. Using stored procedures with parameters instead of adhoc sql is
a good start.
Andrew J. Kelly SQL MVP
"Daniel Avsec" <DanielAvsec@.discussions.microsoft.com> wrote in message
news:9F7A3F1B-3868-4B19-9A5F-63462C3D48A4@.microsoft.com...
> Andrew, that is what I am thinking too. I have finally gotten a hold of
the code the developer uses to call this. Can any of you see anything wrong
with this code?
> Dim cnTemp As Connection
> Dim rsTemp As Recordset '--ADODB.Recordset
> Dim strSQL As String
> Set cnTemp = New ADODB.Connection
> Set rsTemp = New ADODB.Recordset
> cnTemp.ConnectionString = "Provider=SQLOLEDB;Data
Source=PowerWare2000;Initial Catalog=PWProd; User
ID=*******;Password=********;"[vbcol=seagreen]
> cnTemp.Open
> strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
> rsTemp.CursorLocation = adUseClient
> rsTemp.CursorType = adOpenStatic
> rsTemp.LockType = adLockOptimistic
> rsTemp.Open strSQL, cnTemp, , , adCmdText
> "Andrew J. Kelly" wrote:
the[vbcol=seagreen]

Exec not returning a result

Hi all,
How can I tell if a executed dynamically created sql is returning no rows.
As in:
select @.sql= 'insert into PLImport2 select * from
OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel
8.0;Database=C:\work\coke\Detailed P&L June RE.xls'', ''SELECT top 5000 *
FROM [Import1] where F1 > '+ltrim(str(@.Rowid))+''')'
exec (@.sql)
Which whould normally retun and insert 5000 rows. I want to this in a loop,
but am afraid to because as soon as it does not find any rows, I want it to
stop. At the momen it can carry on for ever
Thanks
RobertDoes it make a difference that you are INSERTing into [PLImport2] and you
are SELECTing from [Import1]?
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Robert Bravery" <me@.u.com> wrote in message
news:%23TrjLTunGHA.4952@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> How can I tell if a executed dynamically created sql is returning no rows.
> As in:
> select @.sql= 'insert into PLImport2 select * from
> OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel
> 8.0;Database=C:\work\coke\Detailed P&L June RE.xls'', ''SELECT top 5000 *
> FROM [Import1] where F1 > '+ltrim(str(@.Rowid))+''')'
> exec (@.sql)
> Which whould normally retun and insert 5000 rows. I want to this in a
> loop,
> but am afraid to because as soon as it does not find any rows, I want it
> to
> stop. At the momen it can carry on for ever
> Thanks
> Robert
>|||HI,
Yes, PLImport2 is a SQL Table that I am inserting into, and Import1 is
range in an excel File. I am selecting data from the Excel file into the SQL
table
Thanks
Robert
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eWIQ2VunGHA.4104@.TK2MSFTNGP04.phx.gbl...
> Does it make a difference that you are INSERTing into [PLImport2] and you
> are SELECTing from [Import1]?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:%23TrjLTunGHA.4952@.TK2MSFTNGP02.phx.gbl...
rows.
*
>

exec a ssis pkg from java or .net application

hi, does anyone know if an ssis pkg can be executed from a java or .net application?

You can execute packages using the DTSExec program and passing it command line arguments. And this could be done in any windows application where you can invoke the DTSExec program, including .NET and Java applications.

|||I think you meant DTExec, not DTSexec.|||

If running a .net app from a citrix server, would any componenets need to be installed like they had to be for DTS in SQL 2000? I keep trying to find some info on this and can't find anything.

Thanx,

Mike

Exception when ExecuteNonQuery is executed

Hi All,
SqlComand.ExecuteNonQuery()
gives the following exception:
Incorrect syntax near '1.', .Net SqlClient Data Provider, at
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream)
Would anybody be able to tell me the cause of this exception?
Thanks.This error means that the SQL statement you are trying to execute is
syntactically invalid. Try running the same SQL statement via Query
Analyzer for debugging. Post the CommandText if you need more help.
Hope this helps.
Dan Guzman
SQL Server MVP
"kd" <kd@.discussions.microsoft.com> wrote in message
news:D54DBDB4-2CBA-4AA9-A658-D154F47F69B1@.microsoft.com...
> Hi All,
> SqlComand.ExecuteNonQuery()
> gives the following exception:
> Incorrect syntax near '1.', .Net SqlClient Data Provider, at
> System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior,
> RunBehavior runBehavior, Boolean returnStream)
> Would anybody be able to tell me the cause of this exception?
> Thanks.|||Hi Dan,
The SQL command is being executed in the Query analyser and returns 0 rows.
Here is the SQL statement
select count(*) from testtab where UpdateDt = getdate()
Could there be any other reason for the exception?
Regards,
kd
"Dan Guzman" wrote:

> This error means that the SQL statement you are trying to execute is
> syntactically invalid. Try running the same SQL statement via Query
> Analyzer for debugging. Post the CommandText if you need more help.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kd" <kd@.discussions.microsoft.com> wrote in message
> news:D54DBDB4-2CBA-4AA9-A658-D154F47F69B1@.microsoft.com...
>
>|||kd wrote:
> Hi Dan,
> The SQL command is being executed in the Query analyser and returns 0
> rows. Here is the SQL statement
> select count(*) from testtab where UpdateDt = getdate()
You're not making any sense. This is a record-returning select statement. It
should always return a single record. In this case, the record will contain
a single field containing the value "0" if no rows in testtab meet the
criteria in the WHERE clause.

> Could there be any other reason for the exception?
Yes, you are using ExecuteNonQuery to execute a select statement that
returns records. Although I am curious: I do not see the character "1"
(mentioned in the error message) in this sql statement.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Hi Bob,
I am sorry about that...its not the ExecuteNonQuery that is throwing an
exception, but, it is the ExecuteScalar!
kd
"Bob Barrows [MVP]" wrote:

> kd wrote:
> You're not making any sense. This is a record-returning select statement.
It
> should always return a single record. In this case, the record will contai
n
> a single field containing the value "0" if no rows in testtab meet the
> criteria in the WHERE clause.
>
> Yes, you are using ExecuteNonQuery to execute a select statement that
> returns records. Although I am curious: I do not see the character "1"
> (mentioned in the error message) in this sql statement.
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||So is your problem solved? If not, we're going to need to see some code
(although this would be better handled in one of the dotnet newsgroups)
Bob Barrows
kd wrote:
> Hi Bob,
> I am sorry about that...its not the ExecuteNonQuery that is throwing
> an exception, but, it is the ExecuteScalar!
> kd
> "Bob Barrows [MVP]" wrote:
>
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Hi Bob,
The problem is solved. The following statement was the reason for the
exception to occur; though I can't understand why this statement cannot be
given.
SqlCommandObj.CommandText = CommandType.Text
When I commented the line, the exception did not occur when
SqlCommandObj.ExecuteScalar was executed.
Thanks for the assistance.
Regards,
kd.
"Bob Barrows [MVP]" wrote:

> So is your problem solved? If not, we're going to need to see some code
> (although this would be better handled in one of the dotnet newsgroups)
> Bob Barrows
> kd wrote:
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||kd wrote:
> Hi Bob,
> The problem is solved. The following statement was the reason for the
> exception to occur; though I can't understand why this statement
> cannot be given.
> SqlCommandObj.CommandText = CommandType.Text
What are you trying to do with that statement? CommandText is the text
for the command and CommandType is the type of command: SP, TableDirect,
or Text.
David Gugick
Imceda Software
www.imceda.com