Help - SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help - SP

Author

Topic

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-01-18 : 20:39:08

how to send email in sp?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-18 : 22:15:57

quote:
Originally posted by khusiie

short description: When web orders are received at website they are synchronized into the back office database and placed into a batch of daily orders. I need SQL job that will run automatically each afternoon to do make sure that web orders are flowing into the back office from that web site.

Requirements:
The job should run automatically each afternoon at 4pm
An email confirmation should be sent if there are less than 10 web orders
Tables:
Sales Transactions Work table –If at 4pm on any given day, there are less than 10 orders, contact_person should be notified. This will give him time to correct most issues so that the warehouse will have a full day of web orders to fulfill and ship when they come in the next day
Docdate field is for the date
Order ID is ordernumber column. Web orders will always have the NET prefix on them. In house orders have the ORD prefix.

how to create stored procedure in sql2005?

Can anybody help me?

Thanks.

The steps for configuring sql server agent job can be found here:-

[url]http://msdn2.microsoft.com/en-us/library/ms191439.aspx[/url]

the logic for checking weborders will be something like this:-

SELECT COUNT(OrderID)
FROM Sales Transactions Work table
WHERE LEFT(OrderName,3)='NET'
AND Docdate=DATEADD(d,DATEDIFF(d,0,GETDATE()),0)

You might require database mail for sending confirmation. Refer link for configuring dbmail:-
[url]http://www.databasejournal.com/features/mssql/article.php/3626056[/url]

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-01-19 : 13:18:06

thanks for ur help ad suggetion vishakh16...it will help me.

thanks.

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-01-19 : 13:35:23

if the web orders are low. When there are less than 10 web orders by 4pm on any give day it usually indicates that something has gone wrong in the system somewhere. This also is another small step in beginning to understand how the system works. We have 2 types of orders - in house (entered by call center staff), and web orders (entered by customers in the Online Catalog). This job concerns web orders only

can u tell me the logic behind this...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-19 : 13:41:41

quote:
Originally posted by khusiie

if the web orders are low. When there are less than 10 web orders by 4pm on any give day it usually indicates that something has gone wrong in the system somewhere. This also is another small step in beginning to understand how the system works. We have 2 types of orders - in house (entered by call center staff), and web orders (entered by customers in the Online Catalog). This job concerns web orders only

can u tell me the logic behind this...

SELECT COUNT(OrderID)
FROM Sales Transactions Work table
WHERE LEFT(OrderName,3)='NET'
AND Docdate=DATEADD(d,DATEDIFF(d,0,GETDATE()),0)

This gives count of web orders for any day. use this count value to check if there's something wrong with system.

i.e IF count < 10
---code to perform actions (send mail)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-20 : 02:48:07