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 and Error Handling

I am building a Bulk Insert statement dynamically passing in filename location, etc.

I have coded error handling via the @.@.ERROR. To test the error handling, I am forcing the file that is bulk inserted to be missing.

The statement is created in a declared variable, IE @.SQL:

EXEC(@.SQL)

IF @.@.ERROR <> 0

BEGIN
GOTO ErrorHandler
END

Since the file is missing it causes an error, and the Stored Procedure gives the

Server: Msg 4860, Level 16, State 1, Line 1 Could not find the file etc.
and aborts aborts the whole stored proc at that point. In other words the error handling IF @.@.ERROR doesn't trap the error and send the process to the error handler routine.

Is there anyway to get the error back from the EXEC, or maybe something has to be set to trap the error because it is fatal? Because of the way the Bulk Insert statement is created dynamically, it appears you have to EXEC the statement once it is built (EXEC(@.SQL)).

Any help would be appreciated.

Barryyou can do something like :

begin Transaction exec1
-- your sql stmts
If @.@.ERROR > 0
 begin
 RAISERROR('Error in sp',16,1)
 ROLLBACK TRANSACTION exec1
 RETURN 99
END
 COMMIT TRANSACTION exec1

sql

Excluding filter via report parameter

Hi,

How to exclude a filter on a dataset such that I may either apply the filter or not?

I would like to control that through a boolean report parameter.

Edmund

try creating a stored procedure as your dataset. in the sp write an If statement. this lets you set up logic so thet a different query can be run e.g if "true" is selected query A is run filter applied

if "false" is selected query b can be run with no filter

Exception.Data[key]=value

Hi.

Is it possible to transmit custom data from SQL 2005 to the client via Exception.Data[key]=value?

The idea
I have a number of SPs that accept userId parameter which must be verified. I have written a simple CLR SP (check_userid_valid) which performs the verification of the userId parameter's value. If the parameter's value is considered invalid an exception is thrown. When the exception (System.Exception) is instantiated additional data is added via the Data property of the exception:

System.Exception ex = new System.Exception();
ex.Data["Source"] = "check_userid_valid"

So, any SP that calls the check_userid_valid with invalid userId is expected to "crash" and the exception with all the additional data is expected to be propagated back to the client which, in turn, could read the data.
Unfortunately, it seems that the ex.Data contains only entries put by the MS SQL 2005 server itself, eliminating the rest.

The question is: how can I supply additional exception data with the exception I do throw from my CLR code on the server-side, so that is can be consumed at the client-side.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CustomException()
{
try
{
//try code goes here
System.Exception firstEx = new System.Exception("first exception");
throw firstEx;
}
catch (System.Exception ex)
{

}
finally {
System.Exception secondEx = new System.Exception("check_userid_valid");
throw secondEx;
}
}
};

Excel to MS SQL Server via OLE - Date problem

Background:
I have Excel files that are not in a good 'data' format so I'm planning to
migrate the data out of Excel into MS SQL server. Since the data is spread
all over the place in the current Excel files, I'll need to write code to
grab the right data, build a select statement, and push the data into SQL
Server.
Current status:
I have working code that pushes the stuff I want into SQL server. Woot!
Problem:
The excel files contain date and time fields. The date cells have values of
39000, etc and the time fields are decimals from 0 to 1. All pretty standard.
When I grab a date field using the following OLE code:
TestDate = Cdat(xlSheet2.Range("D8").Value)
And then display it back, I see an actual date.
When I do the same thing with a time field, I just see the decimal number.
Problem is that when they get to SQL server,
Dates are all: 1/1/1900 12:00:00 AM
Times are: 1/1/1900 with what appears to be the right time.
The SQL server fields are Datetime.
I'm stuck - any suggestions?
TIA.
Doug
ps - I've cross posted in the Excel group
Nothing like posting a question to make the answer clear...
My OLE code buids a string that becomes the insert query on the SQL server
side.
The string looks something like:
Insert into tableX(Field1, Field2, Field3) Values ('StringValue',
numericValue)
Two things.
First: I needed to add the date value and time value from Excel into a
single number.
Second: rather than submit the value as a value, I send it as a string ala
Insert into tableX(Field1, Field2) Values ('MyString', '12/31/2008 08:00:00
AM')
poof, works great.
Hope this is of value to others.
Doug
"Doug_F" wrote:

> Background:
> I have Excel files that are not in a good 'data' format so I'm planning to
> migrate the data out of Excel into MS SQL server. Since the data is spread
> all over the place in the current Excel files, I'll need to write code to
> grab the right data, build a select statement, and push the data into SQL
> Server.
> Current status:
> I have working code that pushes the stuff I want into SQL server. Woot!
> Problem:
> The excel files contain date and time fields. The date cells have values of
> 39000, etc and the time fields are decimals from 0 to 1. All pretty standard.
> When I grab a date field using the following OLE code:
> TestDate = Cdat(xlSheet2.Range("D8").Value)
> And then display it back, I see an actual date.
> When I do the same thing with a time field, I just see the decimal number.
> Problem is that when they get to SQL server,
> Dates are all: 1/1/1900 12:00:00 AM
> Times are: 1/1/1900 with what appears to be the right time.
> The SQL server fields are Datetime.
> I'm stuck - any suggestions?
> TIA.
> Doug
> ps - I've cross posted in the Excel group

Excel to MS SQL Server via OLE - Date problem

Background:
I have Excel files that are not in a good 'data' format so I'm planning to
migrate the data out of Excel into MS SQL server. Since the data is spread
all over the place in the current Excel files, I'll need to write code to
grab the right data, build a select statement, and push the data into SQL
Server.
Current status:
I have working code that pushes the stuff I want into SQL server. Woot!
Problem:
The excel files contain date and time fields. The date cells have values of
39000, etc and the time fields are decimals from 0 to 1. All pretty standard.
When I grab a date field using the following OLE code:
TestDate = Cdat(xlSheet2.Range("D8").Value)
And then display it back, I see an actual date.
When I do the same thing with a time field, I just see the decimal number.
Problem is that when they get to SQL server,
Dates are all: 1/1/1900 12:00:00 AM
Times are: 1/1/1900 with what appears to be the right time.
The SQL server fields are Datetime.
I'm stuck - any suggestions?
TIA.
Doug
ps - I've cross posted in the Excel groupNothing like posting a question to make the answer clear...
My OLE code buids a string that becomes the insert query on the SQL server
side.
The string looks something like:
Insert into tableX(Field1, Field2, Field3) Values ('StringValue',
numericValue)
Two things.
First: I needed to add the date value and time value from Excel into a
single number.
Second: rather than submit the value as a value, I send it as a string ala
Insert into tableX(Field1, Field2) Values ('MyString', '12/31/2008 08:00:00
AM')
poof, works great.
Hope this is of value to others.
Doug
"Doug_F" wrote:
> Background:
> I have Excel files that are not in a good 'data' format so I'm planning to
> migrate the data out of Excel into MS SQL server. Since the data is spread
> all over the place in the current Excel files, I'll need to write code to
> grab the right data, build a select statement, and push the data into SQL
> Server.
> Current status:
> I have working code that pushes the stuff I want into SQL server. Woot!
> Problem:
> The excel files contain date and time fields. The date cells have values of
> 39000, etc and the time fields are decimals from 0 to 1. All pretty standard.
> When I grab a date field using the following OLE code:
> TestDate = Cdat(xlSheet2.Range("D8").Value)
> And then display it back, I see an actual date.
> When I do the same thing with a time field, I just see the decimal number.
> Problem is that when they get to SQL server,
> Dates are all: 1/1/1900 12:00:00 AM
> Times are: 1/1/1900 with what appears to be the right time.
> The SQL server fields are Datetime.
> I'm stuck - any suggestions?
> TIA.
> Doug
> ps - I've cross posted in the Excel group

Excel rendering

Hi, does anyone know if it's possible to dynamically create excel tabs/worksheets possibly via RS web service library or some other method.
Thanks in advance,
JimNo.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim" <Jim@.discussions.microsoft.com> wrote in message
news:B37D944C-B806-48EB-A991-905E9405A8D9@.microsoft.com...
> Hi, does anyone know if it's possible to dynamically create excel
tabs/worksheets possibly via RS web service library or some other method.
> Thanks in advance,
> Jim|||Hi,
when i export report in excel, sometimes document map color in excel comes
blue and sometimes it comes blue. Why is it so?. Is this a RDL issue or excel
issue? please help me out. My email id is kiran_nandedkar@.rediffmail.com
Thanks in advance