How to send email from Sql server 2000, Sql Server Agent, SqlMail

Technorati Tags: How to send email from Sql server 2000 using sql mail,email from sql server agent,error 22022

Some times we need to schedule a Sql Server Job for automatic execution on a certain date n time in Sql server 2000. This  Sql Server Agent Job could be execution of Stored procedure, system command, DTS, etc. Now, on the execution, we need sql server to notify by email to the concerned person in the company of the success or failure of the scheduled job.

Now, Sending an email from sql server 2000 is a bit tricky. Lets start doing it step by step. We will consider Sql Server machine is using Microsoft Exchange Server / Outlook. (There are other ways to do it. But I am considering it only thru MS exchange server.)

Step 1:

Go to Controlpanel --> administrative tools --> Service --> select the MSSQLSERVER –> right click go to properties –> log on tab –>
log on as this account –> key in  domain\administrator user and give password. Apply, Restart the MSSQLSERVER SErvice

Step 2 :

Open the Enterprise Manager –> expand the database server tree on the left pane –> navigate to the Support Service folder --> SQL mail --> right click and select properties –> PRofile name : you can see OUTLOOK by default and all the profiles created on the machine using Microsoft Exchange Server/ used in outlook. Select the OUTLOOK or the profile you want to use to send email from sql server, in the Profile name. Click Test to check the email is sent to the selected profile email id. Close the dialog box.

(Note : if you are not able to see any profiles in the Proflie Name section, it means your MSSQLSERVER is not running under the same windows NT account as the email profile you want to use for sql server mail)

Step 3 :

Open the Enterprise Manager –> expand the database server tree on the left pane –> navigate to the Management Folder –> Check whether Sql server Agent is running or not, if not right click and select Start. –> expand Sql Server Agent and select Operators –> Right click Operators –> select New operator –> Give Name of the operator in the Name field –> select Email to be notified on the action like success or failure of a job.  (click … button to open the Outlook AD address book ) –> click Test button to send the test email to the selected email id. Click OK to close the dialog box.

Step 4:

While still in Management folder tree in Enterprise Manager select Jobs under the Sql Server Agent –> Right click jobs, select New Job , on General Tab give Job Name, let the category and owner be default, if you r scheduling Stored procedure. –>

Go to Steps Tab, click on New button to create a new Step, in Name textbox, give the name of Step viz. Step1. In Type Dropdown, select Transact Sql Script - T-Sql, in Database Dropdown select the appropriate Database where you have saved the SP. In the Command section give the Stored procedure to execute. (e.g. Exec SpMyFirstSchedule (with parameters if any)) or give the system command to perform any system task. Click on the Advanced Tab, give path to Output file, the job will save the resultset of the SP at the given path, if any. Click OK to close the Step Dialog box. Now, you are back to Jobs dialog box. 

--> select the third tab, called schedule tab –> click New Schedule button, give Schedule name in the dailog box that opens, and select the schedule when you wnat the job to automatically execute. you have option of executing it once, or every week or when the system is idle or everytime when sql server agent starts. Click OK to close the dialog box. now you are back to Jobs dialog box.

--> select the forth tab, Notifications. Select the Email Operator Check box and select the operator we created just now (refer Step 3). You can Choose to send the notification when the Job completes, either when the job is successful or when job fails.  If it is a one time job, you can select the checkbox to delete the job as soon as it is successful. End of step 4.

Alternatively You can also send the detailed email to the stake holders from the stored procedure itself, using master.dbo.xp_sendmail.

if you get the errors described below, the solution can be achived if you carefully follow the steps given.

Related Errors:

Solution : Error 22022: SQL Server Agent Error: The SQL Server Agent Mail Session is not Running;Check the mail profile and/or
the SQL Server Agent Service Startup account in the SQL
Server Agent Properties Dialog.

Refer following links for other similar articles.

http://www.mssqlserver.com/articles/sqlmail_p1.asp

http://support.microsoft.com/?id=263556

Happy programming.

Read more!

How to Create Charts in Asp.Net 2.0 using Microsoft Chart .Net 3.5

Hi,

Today, I would like to share my view on how to create chart in Asp.net 2.0. As we all know traditionally microsoft asp.net didnt had support for Charts from its own factory. But, after Microsoft Took over Dundas chart, Microsoft is providing the Drag and Drop Chart functionality for visual studio 2008 or .Net framework 3.5.

To start with, You will need to download the Following  :

Microsoft .Net Framework 3.5

Microsoft .Net Framework 3.5 SP1

Microsoft Chart for .Net 3.5

You will need to install them in the order mentioned. Because the Chart control is a part of .Net Framework 3.5 SP1.

Once the installation is done, you are ready to create stunning Charts in Asp.net without using any third party tool.

Step 1: Create a New Website in Visual Studio 2005.

Step 2: Look for a Chart Control in the Toolbox. Drag and drop it to proceed. (if you are not able to find chart control, right click toolbox Add tab, give it a name Chart. Right click Chart Tab –> Add Items, Browse to Program Files –> Microsoft Chart controls –> Assemblies –> System.Web.DataVisualization.dll. Once you add this dll, you will have chart control in your toolbox)

Step 3: Add SqlDataSource and configure it to Northwind database and the simple query as shown in the image below.

Step 4: Click on the Smart tag of Chart control and select SqlDataSource1 for the Datasource.

Step 5: Now lets do some basic configuration. there are basically 2 important properties you need to know to start creating Charts in Asp.net 2.0

1) ChartAreas: Click the (…) button next to ChartAreas in property window of Chart1. Change the Property values related to Appearance of the chart like its background color, to suit your website color themes.

->  Locate the Axis Property in the ChartArea Collection Editor, click on the (…) button to open the Axis collection editor, Select X Axis from the left pane, and select Title property from the right. Type in ‘Countries’ in Title property

-> Select Y (value) Axis from left pane and select Title from right pane, enter ‘Count’ in Title Property. Click OK to clost the window.

-> Select Axis Property from left pane, find X Axis, LabelStyle Section, Modify the Angle Property to –90 and Interval to 1, This is required to display all the values in X axis.

2) Series: Select the Chart control, from properties, select Series property and click on (…) button to open up the Series Collection Editor.

-> In the left pane Series1 will be selected by default, now select X Value Member property from right pane, and select the appropriate column name coming from database. In our case select ‘country’.

-> Again, Select Y Value Member property in right pane and select ‘Count’ column.

That is it, We are ready to display the chart, before we execute it, let me tell you about the 3D view of the Chart. Select the ChartAreas Property collection, Look for Area3DStyle in the right pane, expand it by clicking on the + sign and set (Enable3D) to True. This will help us to display the chart in 3D angle.

This is how it look likes when you run it.

 

And ya, to change the chart to any other format you like just follow simple step,

-> Select Series collection from property window, Select different chart type from ChartType property, see the image below to get better idea.

 

Below are the few types of chart we can create using Microsoft Chart control.

Bubble

 

Doughnut

Pyramid

I hope i have done justice to this article, my next article will discuss about some common errors you may face as a beginner to Chart control.

Do comment if you have any doubts.

Technorati Tags: Microsoft Chart Control,Chart for Asp.net 3.5,chart for Asp.net 2.0

Happy programming

Source : Here

Read more!

Solution to The conversion of a char data type to a datetime data type resulted in an out-of-range datetime

Hi,

I would like to share my opinion on one of the problems i faced while working with datetime format in Asp.net 2.0 and sql server 2000 on windows server 2003.

I encountered an error 'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime'. while trying to save a date to the datetime column in database.

The Reason : Sql server supports the datetime format depending upon the regional datetime setting of the database server. so if your development server supposet MM/dd/yyyy , not necessarily your production or testing database server will support the same format. As a result you might have done coding to save it in a particular format & the same code may not work on another server.

Solution : After trying various things i came to a conclusion the best way to handle this error is to have precautionary steps while dealing with them.

Consider a case, where you just want to store the date.. without timestamp. as sql server doesnt have any specific date format you need to save the date in datetime dtatype column in database.
You can first create a stored procedure with the insert statement

CREATE PROCEDURE [dbo].[spInsert_]
@dt datetime
AS
BEGIN
Insert into IDtable (dt) values (@dt);
GO
END

Now call the above stored procedure from front end asp.net 2.0 code.

Dim dt As DateTime = DateTime.Now.ToString("yyyy-MM-dd")
MyCmd.Connection = MyCon
MyCmd.CommandType = Data.CommandType.StoredProcedure
MyCmd.CommandText = "spInsert_"
MyCmd.Parameters.Add("@dt", SqlDbType.DateTime)
MyCmd.Parameters("@dt").Value = dt
MyCmd.Transaction = trans
Dim res As Integer = MyCmd.ExecuteNonQuery()

Here, I would like to point out, the universal format supported by sql server is yyyy-MM-dd hh:nn:ss where nn is minutes...

so if you make a practice of storing your data in the said format, you will never encounter this error...

second important thing is to use Store Procedure with parameters, you can just pass the date value as parameter to store procedure, it will do the necessary casting.

Other solution :
SET DATEFORMAT dmy
before executing any sql statement. (i havent tried this one)

Source : http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/095eb7a7-222a-4a15-8301-2a2acca2ca3f/

if you find my article useful, do leave a comment.

Cheers,
happy programming
Idu

Read more!