Automate Row Update and send email?

I'd like to update rows matching a certain criteria each night automatically and then send an email.

I have a table with a Status column and an Expire Date column. I want to update rows where the Status = Open and the Expire Date < The current Date by changing the Status to Closed then send an HTML email. Do I have to write a program and schedule it to run or is there a way to do this in MS SQL? I looked at using a stored procedure but I don't see how to schedule one to run (other than at startup) and I'm not sure if the GETDATE function can be called.

Thanks for any help.

Joewindows service or a scheduled task.

sql -> a scheduled job.

It's not some procedure you can create or a function. You have to set it up on the main service to execute.|||create a SQL Server Agent Job to run the stored proc on a schedule...|||I have the same situation.
I am using Access, how can I do this?
Create a .vbs and schedule it with scheduled tasks?
I tried but i get an error in the first Dim statement.|||Thanks for the help. I ended up just creating another asp.net page to take care of it. Then I just run it as a scheduled task and kill it. I haven't learned enough about stored procedure to do it the otherway yet.

Joe

Auto Process Messages in Target Queue

I am little confused with this statement, can please some one clarify!

Create QUEUE ReceiverQueue WITH STATUS = ON,

ACTIVATION ( PROCEDURE_NAME = dbo.process, MAX_QUEUE_READERS = 1, EXECUTE AS 'dbo' )

Does this mean when a message reaches to ReceiverQueue, it will automatically processed by dbo.Process procedure

Create PROC dbo.process As

Begin

DECLARE @.conversationHandle UNIQUEIDENTIFIER

DECLARE @.message_body varchar(100)

DECLARE @.rid int

Begin Transaction;

While (1=1)

Begin

RECEIVE TOP(1)

@.rid = Cast(Convert(Nvarchar(max),message_body) as int)

From ReceiverQueue

If (@.@.RowCount = 0 Or @.@.ERROR <> 0 ) Break

Exec PriceChange.Extract_AS400Data @.rid

End

Commit Transaction;

End

Or do I have to put WAITFOR statement in my stored proc (dbo.Process) to run it continously.

Please guide

Thanks

By WAITFOR I assume you mean WAITFOR (RECEIVE...), not the WAITFOR DELAY.

Both ways are correct, but a small WAITFOR timeout added to the RECEIVE in the loop is more efficient. If you have a small timeout (e.g. 10 seconds) the procedure wil linger around for up to 10 seconds, so new incoming messages within 10 seconds will find it already activated.

You need to receive also the message_type_name columns and check what message type you RECEIVED. There are two built-in message types you procedure should be ready to handle: EndDialog and Error. As it is now, when will receive an EndDialog or Error it will actually call the PriceChange.Extract_AS400Data procedure with the wrong data. The usual way to reac to these two message types is to issue and END CONVERSATION. For this you need to RECEIVE also the conversation_handle column.

Also, you need to think who will END the conversations you use first and when. How are those pricechange mesages sent? Each change is sent as BEGIN DIALOG/SEND, or there is one long lived dialog that sends all changes?

HTH,
~ Remus

|||

Thanks for the detailed reply. My whole intent is to extract AS400 data using SQL server asynchronously.

Users from front end application (ASP.NET 2) sends a request to download certain batches of data. When users submits a request, request is saved into a database table and a requestid is generated. Generated id is sent as message to sender queue which in truns go to receiver queue. The stored proc associated with the receiver queue will execute an try to download data from as400 by reading the request table based on sent id.

Mostly it will be one message and conversation is closed. Then later time another message and conversation is closed.

Please Guide me for the above scanario.

Thanks

|||

1> How will the stored proc download data from AS400? Do you plan to use a CLR assembly with external access permission? If so, note that your outbound connections will be from the sqlservr.exe process which is running as the SQL Server User Account (eg> LOCALSYSTEM, NETWORKSERVICE, etc). Also, you will be using up a thread in the database engine waiting for the data to be fetched from AS400. And you also need to be careful with handling error semantics (what if AS400 is unavailable or there is a network failure while downloading the data).

One solution to the problem of executing in the context of SQL Server database engine is not to use internal activation. You can use external activation as described here (http://rushi.desai.name/Blog/tabid/54/EntryID/7/Default.aspx) so that your services can be written as standalone EXEs that could run as whatever principal you wish and also on a separate machine if desired.

The other solution, is to use a different SQL Server instance for your outbound access. This instance could even be a copy of the free SQL Server Express.

2> "Mostly it will be one message and conversation is closed". If the initiator service sends the message and closes the conversation in the same transaction (i.e. fire-and-forget) you cannot achieve application-level reliability. In order to get reliability, the app must get back some acknowledgment from the target that the message was received and processed. This response message could be as simple as EndDialog. So it is always better for the target to close the conversation first, after it has successfully received the message.

|||

Thanks for the informatin. Data from AS400 is extracted using the LinkedServer feature of SQL2005.

Is it possible to give some example or url reference to achive what's mentioned in the point 2.

Thnaks

Shafiq