Tuesday, April 28, 2009

How to upload/Deploy Sql server 2000 database on a remote web server using Helm control panel and Enterprise Manager

I have an Asp.net 2.0 web application using sql server 2000 as back end. I want to deploy the web application on Web hosting company’s rented web server.

The deployment of the asp.net 2.0 is done easily using the FTP (example filezilla). But to deploy the Database is quite tricky.

(Steps using HELM control panel)

Step 1: we need to create a database on the server, by logging in to the server using the HELM control panel, (details of accessing the Helm control panel will be pro

vided by the Service provider). Login –> Domains –> select ur domain –> Click Database manager –> select Sql server 2000 icon –> click New database and give the desired database name (E.g. MyDB). It will create an empty database on the server.(Also, note down the IP address, Port Number used. if you cant see the ip address and port number(xxx.xxx.xxx.xxx,&&&&), ask your service provider to get you the detail of port number)

Step 2: Create the Database user, give the username n password that you will use to access the database from your web application / Enterprise manager (E.g. MyDBUser).

(Steps using your local development/testing machine to upload the sql server 2000 database.)

Step 3:

Open Enterprise Manager –> Select Register New Sql Server from left pane –> Registered Sql Sever Properties will popup as shown in the figure below. Key in the IP Address, Port No. (xxx.xxx.xxx.xxx,&&&&), without parenthesis. Give the username n pwd we just created in step 2

sql1

Step 4:

Once the Sql server is registered, you can use it like a local db and use DDL, DML statements very easily.

I hope the post helps people to understand deployment better.

If you like the post, please bookmark it and use friend connect to be my friend on this blog.

Happy Programming.

Read more!

Thursday, April 9, 2009

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

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!