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

Exchange 2003 and "xp_sendmail: failed with mail error 0x80070005" error

Hello,
We just migrated a mailbox that sql server agent was using as a MAPI
profile to Exchange 2003 from Exchnage 2000.
I can successfully start sql agent with this profile. SQLmail is configured
successfully with this profile.
But I am having the above error when I try to use xp_sendmail from query
analyzer. Any ideas?
Thanks,
Biva
I had a similar problem a couple of weeks ago and did a lot of research
with no results. This was causing issues with us for at least a week.
For the heck of it I went into Query analyzer and ran xp_stopmail, then
xp_startmail. Don't know why, but this did it for us. We have been using
xp_Sendmail now for over a week without any errors.
Jim
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40fc1289$0$978$39cecf19@.news.twtelecom.net...
> Hello,
> We just migrated a mailbox that sql server agent was using as a MAPI
> profile to Exchange 2003 from Exchnage 2000.
> I can successfully start sql agent with this profile. SQLmail is
configured
> successfully with this profile.
> But I am having the above error when I try to use xp_sendmail from query
> analyzer. Any ideas?
> Thanks,
> Biva
>
|||Hi Jim,
You are awesome. That was it. I did xp_stopmail and xp_startmail and
now its working fine. Thanks a bunch for your help. Saved me a lot of
time.
biva
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Exchange 2003 and "xp_sendmail: failed with mail error 0x80070005" error

Hello,
We just migrated a mailbox that sql server agent was using as a MAPI
profile to Exchange 2003 from Exchnage 2000.
I can successfully start sql agent with this profile. SQLmail is configured
successfully with this profile.
But I am having the above error when I try to use xp_sendmail from query
analyzer. Any ideas?
Thanks,
BivaI had a similar problem a couple of weeks ago and did a lot of research
with no results. This was causing issues with us for at least a week.
For the heck of it I went into Query analyzer and ran xp_stopmail, then
xp_startmail. Don't know why, but this did it for us. We have been using
xp_Sendmail now for over a week without any errors.
Jim
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40fc1289$0$978$39cecf19@.news.twtelecom.net...
> Hello,
> We just migrated a mailbox that sql server agent was using as a MAPI
> profile to Exchange 2003 from Exchnage 2000.
> I can successfully start sql agent with this profile. SQLmail is
configured
> successfully with this profile.
> But I am having the above error when I try to use xp_sendmail from query
> analyzer. Any ideas?
> Thanks,
> Biva
>

Exchange 2003 and "xp_sendmail: failed with mail error 0x80070005" error

Hello,
We just migrated a mailbox that sql server agent was using as a MAPI
profile to Exchange 2003 from Exchnage 2000.
I can successfully start sql agent with this profile. SQLmail is configured
successfully with this profile.
But I am having the above error when I try to use xp_sendmail from query
analyzer. Any ideas?
Thanks,
BivaI had a similar problem a couple of weeks ago and did a lot of research
with no results. This was causing issues with us for at least a week.
For the heck of it I went into Query analyzer and ran xp_stopmail, then
xp_startmail. Don't know why, but this did it for us. We have been using
xp_Sendmail now for over a week without any errors.
Jim
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40fc1289$0$978$39cecf19@.news.twtelecom.net...
> Hello,
> We just migrated a mailbox that sql server agent was using as a MAPI
> profile to Exchange 2003 from Exchnage 2000.
> I can successfully start sql agent with this profile. SQLmail is
configured
> successfully with this profile.
> But I am having the above error when I try to use xp_sendmail from query
> analyzer. Any ideas?
> Thanks,
> Biva
>|||Hi Jim,
You are awesome. That was it. I did xp_stopmail and xp_startmail and
now its working fine. Thanks a bunch for your help. Saved me a lot of
time.
biva
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Excessive Logon Audit Events

I have a stand alone Windows 2000 server sp4 running SQL 2000 sp3. I need t
o be able to audit successful and failed Logon events and I have the group p
olicy set to do this. The box is in an offsite facility connected by at T1
over a VPN connection. My
SQL developers have the remote server registered in their Enterprise Manager
along with the local SQL servers. When they have Enterprise Manager open,
the following event is logged on the remote SQL server every 10 seconds. It
is filling my log and maki
ng it difficult to read. Why is this happening and can I make it stop?
Event Type: Success Audit
Event Source: Security
Event Category: Account Logon
Event ID: 680
Date: 6/28/2004
Time: 9:01:29 AM
User: NT AUTHORITY\SYSTEM
Computer: CONDOR
Description:
Account Used for Logon by: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0
Account Name:
SomeUser
Workstation:
UserWorkstationIn Enterprise Manager (the devs need to do this on their PC's) goto
Tools>Options and uncheck the Poll Server checkbox. As you will see the
default interval for this polling is 10 seconds.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Chris Wilkins" <Chris Wilkins@.discussions.microsoft.com> wrote in message
news:2D79110F-901D-43F2-9DA6-1AD0A97CC90F@.microsoft.com...
> I have a stand alone Windows 2000 server sp4 running SQL 2000 sp3. I need
to be able to audit successful and failed Logon events and I have the group
policy set to do this. The box is in an offsite facility connected by at T1
over a VPN connection. My SQL developers have the remote server registered
in their Enterprise Manager along with the local SQL servers. When they
have Enterprise Manager open, the following event is logged on the remote
SQL server every 10 seconds. It is filling my log and making it difficult
to read. Why is this happening and can I make it stop?
> Event Type: Success Audit
> Event Source: Security
> Event Category: Account Logon
> Event ID: 680
> Date: 6/28/2004
> Time: 9:01:29 AM
> User: NT AUTHORITY\SYSTEM
> Computer: CONDOR
> Description:
> Account Used for Logon by: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0
> Account Name:
> SomeUser
> Workstation:
> UserWorkstation
>

EXCEPTION_ACCESS_VIOLATION when installing June CTP

Hi - I'm trying to install June CTP on Win2k3 SP1.
Setup failed when trying to start services for configuration.
In log files I found the following
Computer type is AT/AT COMPATIBLE.
Bios Version is INSYDE - 1
Insyde Software MobilePRO BIOS Version 4.00.01
Current time is 11:26:54 08/30/05.
1 Intel x86 level 15, 3 Mhz processor (s).
Windows NT 5.2 Build 3790 CSD Service Pack 1.

Memory
MemoryLoad = 57%
Total Physical = 959 MB
Available Physical = 408 MB
Total Page File = 2329 MB
Available Page File = 1812 MB
Total Virtual = 2047 MB
Available Virtual = 1014 MB
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\log\SQLDump0001.txt
SqlDumpExceptionHandler: Process 5180 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server i
s terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 08/30/05 11:26:54 spid 0
*
*
* Exception Address = 77BD8944 (strncmp + 00000014)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00330072
*
* MODULE BASE END SIZE
* sqlservr 00400000 01DDEFFF 019df000
* Invalid Address 7C800000 7C8BFFFF 000c0000
* kernel32 77E40000 77F41FFF 00102000
* ADVAPI32 77F50000 77FEBFFF 0009c000
* RPCRT4 77C50000 77CEEFFF 0009f000
* CRYPT32 761B0000 76242FFF 00093000
* MSASN1 76190000 761A1FFF 00012000
* msvcrt 77BA0000 77BF9FFF 0005a000
* USER32 77380000 77411FFF 00092000
* GDI32 77C00000 77C47FFF 00048000
* MSVCP80 7C420000 7C4A4FFF 00085000
* MSVCR80 7C370000 7C408FFF 00099000
* MSWSOCK 71B20000 71B60FFF 00041000
* WS2_32 71C00000 71C16FFF 00017000
* WS2HELP 71BF0000 71BF7FFF 00008000
* NETAPI32 71C40000 71C97FFF 00058000
* opends60 41060000 41065FFF 00006000
* Secur32 76F50000 76F62FFF 00013000
* SHLWAPI 77DA0000 77DF1FFF 00052000
* USERENV 76920000 769E3FFF 000c4000
* psapi 76B70000 76B7AFFF 0000b000
* instapi 02310000 02318FFF 00009000
* sqlevn70 41070000 411ECFFF 0017d000
* SQLOS 02350000 02354FFF 00005000
* rsaenh 68000000 6802EFFF 0002f000
* AUTHZ 76C40000 76C53FFF 00014000
* MSCOREE 78800000 7883FFFF 00040000
* ole32 77670000 777A3FFF 00134000
* msv1_0 76C90000 76CB6FFF 00027000
* iphlpapi 76CF0000 76D09FFF 0001a000
* kerberos 71CA0000 71CF7FFF 00058000
* cryptdll 766E0000 766EBFFF 0000c000
* schannel 76750000 76776FFF 00027000
* security 71F60000 71F63FFF 00004000
* VERSION 77B90000 77B97FFF 00008000
* dssenh 68100000 68123FFF 00024000
* hnetcfg 5F270000 5F2C8FFF 00059000
* wshtcpip 71AE0000 71AE7FFF 00008000
* DNSAPI 76ED0000 76EF8FFF 00029000
* winrnr 76F70000 76F76FFF 00007000
* WLDAP32 76F10000 76F3DFFF 0002e000
* rasadhlp 76F80000 76F84FFF 00005000
* ntdsapi 766F0000 76704FFF 00015000
* dbghelp 3FA40000 3FB49FFF 0010a000
*
* Edi: 00330072:
* Esi: 00330072:
* Eax: 00000000:
* Ebx: 00000008:
* Ecx: 00000008:
* Edx: 01020001: EC83EC8B 78816608 53008002 820F5756 000000A3 8510508D
* Eip: 77BD8944: D9F7AEF2 FE8BCB03 F30C758B FF468AA6 473AC933 740577FF
* Ebp: 3F6EF3A8: 3F6EF3E4 766F4FF6 00330072 766F4F84 00000008 0194A7FC
* SegCs: 0000001B:
* EFlags: 00010246: 00730075 00650074 005C0072 006C0063 00730075 00650074
* Esp: 3F6EF39C: 3F6EF410 77BD8930 766F4F84 3F6EF3E4 766F4FF6 00330072
* SegSs: 00000023:
* *******************************************************************************
* -
* Short Stack Dump
Any help will be appreciated.The AV is happening in the SQL Server engine. I've moved your post to the SQL DB Engine forum. Maybe someone here can help you.

Dan|||Alexandr,

Are you still getting this error? Can you start the service manually?

From the fragment of the output you have provided there is no way to tell what is wrong.

We either need a full dump file (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\log\SQLDump0001.txt) or paste Short Stack Dump.

Regards,
Boris.|||Thanks for reply Boris.
I have reinstalled SQL Server on a brand new OS and it works fine now.

With the previous installation - i cannot start service manually - it gives the same error. I've tried to install SQL Express - and it gives the same error again.
I tried to start SQL Server even not as a service but as a console app - and it gives that error.
That may be happend because I already has installed SQL 2000 and MSDE on my PC.

Regards,
Alexandr.|||Alexandr,

An instance of SQL Server 2005 should coexist just fine with SQL Server 2000. I don't think that is the reason for the AV you were getting, although this is just a guess.

Since you have reinstalled the OS, I wonder if you have kept the dump file(s) that were created when the server generated the AV.

If yes, could you paste short stack dump here and we can take a further look.

Thank you,
Boris.|||Boris,

SQLServer 2005 works fine with SQL 2000 installed on the same box.
I haven't kept the stack dump or log files.

Thanks,
Alexandr.

Exception : Create Database failed 'tmpDb'

Hi

I'm getting Create Database failed 'tmpdb'

when I'm executing fallowing code

ServerConnection scon = new ServerConnection("local", "sa", "");

Server GSer=new Server(scon);

Database db2 = new Database(GSer, "xyz");

GSer.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql;

db2.Create();

Can Anybody help me in this?

Thanks in advance

hi,

there should be an inner exception stating what the actual problem is. if you can′t find any more information, try to use the capture mode instead of the execution mode and execute the script in the SSMS Query Window. You will get a mote detailed explanation in here.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks jens

can I create database object in C# code.

|||

Its not comming now with

GSer.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql;

getting "ExecuteNonQuery failed for Database 'xyz'.

error when I'm trying to execute scripts on db like

StreamReader sw=new StreamReader("C:\\script.sql");

string script = sw.ReadToEnd();

db2.ExecuteNonQuery(script);

What the error in this

|||

I am using SMO to create a database with this code

StreamReader sr = new StreamReader(@."C:\GestMed\Bases\Create_GestMed_Prod.sql");

string script = sr.ReadToEnd();

script = script.Replace("GestMed_Prod",nombase);

ComCr?erSite.Serveur.ConnectionContext.Connect();

try

{

ComCr?erSite.Serveur.Databases["master"].ExecuteNonQuery(script);

str = " Base " + nombase + " : cr?ation OK";

Ecrire_Journal(sw,str);

}

catch (Exception e)

{

str = " Erreur Cr?ate Base " + nombase;

Ecrire_Journal(sw,str);

str = " Message : " + e.Message;

sw.WriteLine(sw);

SuiviTxt.Text += str + nl;

if ( e.InnerException != null )

{

str = " InnerException : " + e.InnerException.Message;

sw.WriteLine(str);

sw.Flush();

SuiviTxt.Text += str + nl;

}

sw.Flush();

ComCr?erSite.Cr?? = false;

this.Refresh();

return false;;

}

ComCr?erSite.Serveur.Refresh();

That's working

Maybe you have forgotten to connect to the server

I am using a generic script where i'm able to choose the name of the database

GestMed_Prod ==> GestMedSite0Prod for example

I had some problems with SMO it's why i decided to build an installation textfile where i keep all messages Exception.Message and InnerException.Message

I hopthat it will be useful for you and excuse me for my poor english

Have a good day

|||Hi,

sorry for not keeping up quicker. You should use the capture mode to see the commands which are generated by the SMO framework:

SqlExecutionModes.CaptureSql

After catching the string[] of command you can see which commands are causing the errors.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

I guess you are trying to create a database on local server.

If that is the case please use (local) or . instead of local (notice paranthesis).

Ex:Microsoft.SqlServer.Management.Common.ServerConnection scon = new Microsoft.SqlServer.Management.Common.ServerConnection("(local)", "sa", "");

I executed your code with just this change and it worked fine for me. Hope this helps.

Thanks,

Sravanthi.

|||

using the above code

i write dingdong.sql which is following

create database dingdong
go

use [dingdong]
go

create table test(name varchar(20));
go

create table test1(father varchar(21));
go

database created but table did not created, what is the reason

Thanks

|||

Sorry my problem solved,by using following code

StreamReader sr = new StreamReader(@."C:\jehan.sql");

string script = sr.ReadToEnd();

textBox1.Text = script;

Microsoft.SqlServer.Management.Smo.Server srvSql;

Microsoft.SqlServer.Management.Common.ServerConnection srvConn = new Microsoft.SqlServer.Management.Common.ServerConnection

(Global.ConnectionToDatabase1());

srvSql = new Server(srvConn);

srvSql.Databases["master"].ExecuteNonQuery(script);

Global.con1.Close();