sql server programming:
[quoted text, click to view] > I want to write a batch procedure that looking for the amount of goods
> that under the critical stoke in a table.After find the goods I send email
> with Dbmail to email adresses that found on another table.
> 1-)How can I schedule this job to run hourly?
You can SQL Server Agent job with a T-SQL job step that executes a script or
stored procedure.
[quoted text, click to view] > 2-)Can you help me to write the sp?
Below is an example to get you started.
CREATE PROCEDURE dbo.usp_Check_Inventory
AS
DECLARE @EmailAddress varchar(128)
IF EXISTS(
SELECT *
FROM dbo.Inventory
WHERE QuantityOnHand <= ReorderQuantity
)
BEGIN
DECLARE EmailAddresses
CURSOR LOCAL FAST_FORWARD FOR
SELECT EmailAddress
FROM Products.dbo.InventoryClerks
OPEN EmailAddresses
WHILE 1 = 1
BEGIN
FETCH NEXT FROM EmailAddresses INTO @EmailAddress
IF @@FETCH_STATUS = -1 BREAK
EXEC msdb.dbo.sp_send_dbmail
@recipients = @EmailAddress,
@subject = 'Reorder Needed',
@body = 'Below is a list of products to reorder.' ,
@query = 'SELECT ProductCode, QuantityOnHand, ReorderQuantity
FROM Products.dbo.Inventory
WHERE QuantityOnHand <= ReorderQuantity'
END
CLOSE EmailAddresses
DEALLOCATE EmailAddresses
END
GO
[quoted text, click to view] > 3-)Can we use dbmail on Sqlserver 2005 Standard Edition?
Yes, Database Mail is a feature in Workgroup and above.
--
Hope this helps.
Dan Guzman
SQL Server MVP
[quoted text, click to view] "The_TOZ" <tunc@te-mob.com> wrote in message
news:374141EE-D604-4603-9E78-AEADD9705065@microsoft.com...
> Hi .
> I am new in the sql programming.I need your help..
>
> I want to write a batch procedure that looking for the amount of goods
> that under the critical stoke in a table.After find the goods I send email
> with Dbmail to email adresses that found on another table.
>
> 1-)How can I schedule this job to run hourly?
> 2-)Can you help me to write the sp?
> 3-)Can we use dbmail on Sqlserver 2005 Standard Edition?
>
> Thanks for your help..
>
