Timed Event

Question

Post reply

Timed Event

Peter Simard

SSC Rookie

Points: 27
More actions
June 6, 2006 at 2:35 pm

#94256

Hi;
Please excuse me for my lack of knowledge of SQL Server, it has been over 10 years since I've had to do any [major] DB development. I need to find out if it is possible to have a stored procedure called on a timed event with SQL Server. I have found documentation on Notification Services and Event Notifications, but they do not seem to be what I need exactly. I need to perform an operation (with DB data of course) on a daily basis. I was looking at SQL Server 2005 (and 2000 with Extended stored procedures) which allow the ability to call functionality outside the the "db system". I need to be able to do this, but would like some sort of trigger which would fire the event. Is this possible?
Note: I'm not looking for something that would run on another computer that would call the function using a timer, but something that works with the functionality provided by SQL Server.
thx
Peter

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Pam Brisjar SSChampion Points: 12094 More actions · Answer 1

Sounds like you're looking for SQL Server Agent.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_automate_01et.asp

Peter Simard SSC Rookie Points: 27 More actions · Answer 2

Hi;

No SQLServer Agent is an adminstrative tool. I am looking for soemthing that can be setup on a particular DB. Something similar to a trigger.

Peter

Jason- SSCrazy Points: 2795 More actions · Answer 3

this may be what you are looking for:

Copied from BOL:

ø†úÎC Ôr4rªqŒ~kà2Ÿh/$¤É#–c’IêI¯74¬¹U&¼Í0±wr64Ak²?°I*£î`oç^·àÏ]jwvò¶ž¶VÙæ¸“smÏ8P:ãÔ×”xoKµRä©sƒ¸Œ‘^³§[ì(ãÓükàñ‘z#é¨sµ«>”ÒÛS¶G²•nahhÎzV—ö\¿óÉ«Ê~'>

Data Transformation Services

Using String Messages to Trigger Tasks

You can use string messages to trigger a task on another computer or to wait for a signal from that computer before proceeding.

For example, you have an online transaction processing system running on one computer (OLTP). Your data warehouse resides on another computer (Warehouse). Every night, shipment facts are summarized on OLTP and transferred to Warehouse. Before the transfer can take place though, Warehouse dimension tables must be updated.

Two packages are used to manage this process: Update Warehouse and Prepare Facts.

Update Warehouse runs on the Warehouse computer. When Update Warehouse starts, the Ask for Facts task sends a string message telling the OLTP computer to start summarizing shipment data. While shipment data is being summarized on the OLTP computer, the Update Dimensions task updates Warehouse dimension data. When this task is complete, the Wait for Facts task waits for a string message from the OLTP computer that says shipment data is ready. Only after this message is received does a Transform Data task move the shipment data to the Warehouse computer.

Prepare Facts runs on the OLTP computer. The Wait For Trigger task initially waits for the string message from the Ask for Facts task. On receipt, the Shipment Summary task prepares the data for transfer. When the data is ready, the Alert Warehouse task sends the message which, when received by the Wait For Facts task, tells Update Warehouse to start the transfer.

Ask For Facts and Wait For Trigger form a matched pair of Message Queue tasks. Ask For Facts sends the message and Wait For Trigger receives it. Alert Warehouse and Wait For Facts form another such matched pair. Their configuration differs from the first only in the text of the message.

Configuring the Ask For Facts Task

Ask For Facts sends the message "Summarize shipments" to a queue, where it can later be read by the Wait For Trigger task.

To configure the Ask For Facts task

Enterprise Manager

How to configure the Ask For Facts task (Enterprise Manager)

To configure the Ask For Facts task

From the Task toolbar, drag a Message Queue task onto the Data Transformation Services (DTS) design sheet.
In the Message Queue Task Properties dialog box, in the Description box, type Ask For Facts.
In the Message box, click Send Message.
In the Queue box, enter the name of the computer_name\queue_type$\queue_name combination that identifies the location to which these messages will be sent.
Under Messages to be sent, click New, and then in the Message type box, click String Message.
In the String Message box, type Summarize shipments.
On the design sheet, right-click Ask For Facts, point to Workflow, and then click Workflow Properties.
Click the Options tab, and then clear the following check boxes:
- Join transaction if present
- Commit transaction on successful completion of this step
- Rollback transaction on failure

Configuring the Wait For Trigger Task

When run, Wait For Trigger checks a queue for a message that reads, "Summarize shipments". If such a message exists, Wait For Trigger deletes the message and terminates successfully. If not, the task waits until such a message arrives or the package is canceled, whichever comes first.

To configure the Wait For Trigger task

Enterprise Manager

How to configure the Wait For Trigger task (Enterprise Manager)

To configure the Wait For Trigger task

From the Task toolbar, drag a Message Queue task onto the Data Transformation Services (DTS) design sheet.
In the Message Queue Task Properties dialog box, in the Description box, type Wait for Trigger.
In the Message list, click Receive Message. In the Queue box, enter the same computer_name\queue_type$\queue_name combination as that entered for the Ask for Facts task.
In the Message type list, click String Message.
Under Compare, click Exact Match, and then in the Compare String box, type Summarize shipments.
Select the Remove from message queue check box.
On the design sheet, right-click Wait for Trigger, point to Workflow, and then click Workflow Properties.
In the Workflow Properties dialog box, click the Options tab, and then clear the following check boxes:
- Join transaction if present
- Commit transaction on successful completion of this step
- Rollback transaction on failure

-

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

>>I need to perform an operation (with DB data of course) on a daily basis.

This is exactly what SQL Agent Jobs are for. You create a job. The job has a schedule, which you will set as daily. The job has 1 of more tasks, which can be just about anything, not just DB related. The job can kick off a batch file via xp_cmdshell. Or launch a DTS package, whcih contains 1 or more execute process tasks.

Peter Simard SSC Rookie Points: 27 More actions · Answer 5

Hi;

Ok. Yes I read a bit more on Server Agent and it does look like it would be suitable. Thanks for the info.

Peter