The use of SQL SERVER tasks in software development

In software development, regular tasks are often used. This time you might think of threading. But in fact, the threading method is troublesome, error prone, resource competition and other issues, designed to make you very headache.

Now give you a new idea, use the SQL SERVER Task Manager to solve the problem.

Resolve the following issues:

1. There is a time limit for the purchase of goods. such as scheduled online, offline. Automatically notifies the user of the auction end.

2, a data in the database has changed, need to execute a block of code ( This code is not stored procedure Code, meaning C#,java and other code ).

3, a certain data has changed, need to perform an action. ( not executed immediately )

In view of the above problem, we conclude that the program is based on data in the database changes, need to solve the change of related processing work.

In the past we had to do this, we had to use threads to monitor database changes, and to do the corresponding processing according to the changes. Use this method of threading. I think it's far less efficient to use SQL Server tasks. Easy.

The specific code is given below:

First, the product is scheduled to go offline.

exec @exeSql,@end_date,@end_time

Update Jp_shangpin set Zhuangtai=1 where id=1

I specify the time to execute. Specifies the code to execute. This code actually changes the state of a piece of data.

1. How do I run a function block? (for example, a user's fees are almost due, to send a text message to the user, to send a jpush to the user.) How to do it? ）

exec p_url ' http://111.111.111.111/task.aspx?ssss=222=3333 '

　

Create a task script:

go/****** Object:  StoredProcedure [dbo].[ P_task]    Script date:01/25/2016 16:23:56 ******/set ansi_nulls ongoset quoted_identifier ongoalter proc [dbo].[ P_task] @sql varchar (8000),--the command to execute @start_date int,    --Start execution date @start_time int   --Start execution time Asdeclare @dbname varchar (DECLARE) @jobname varchar (100)--Job name set @jobname =newid () set @dbname =db_name ()--Create job exec msdb. Sp_add_job @[email protected]--Create job step exec msdb: Sp_add_jobstep @[email protected], @step_name = ' data processing ', @subsystem = ' TSQL ', @[email protected], @command = @sql, @retry_ Attempts = 5,--Retry count @retry_interval = 5  --Retry Interval--Create schedule exec msdb. Sp_add_jobschedule @[email protected], @enabled =1, @name = ' schedule ', @freq_type =1, @[email protected]_date, @[email protected]_time--Add target server exec msdb.dbo.sp_add_jobserver @job_name = @jobname, @server_name = N ' (local) '--Job completion automatically deletes exec Msdb.dbo.sp_update_job @job_name = @jobname, @delete_level =1

SQL Access URL (with this feature you can call all the code.) )

/****** object:storedprocedure [dbo].    [P_url] Script date:01/25/2016 16:24:45 ******/set ansi_nulls ongoset quoted_identifier ongoalter procedure [dbo]. [P_url] @url nvarchar (+),--url address @value nvarchar (+) out--value, if specified as null, gets the application value asdeclare @err int, @src varchar (255), @desc varchar (255) Declare @obj int, @urlstr nvarchar (4000), @function varchar (4) Select @function =case when @value is null and ' GET ' Else ' POST ' end,@[email protected]+case when @value was null then ' else N ' &value= ' +RTR Im (@value) endexec @err =sp_oacreate ' MICROSOFT. XMLHTTP ', @obj outif @err <>0 goto lberrexec @err =sp_oamethod @obj, ' open ', null, @function, @urlstr, 0if @err <> 0 goto lberrexec @err =sp_oamethod @obj, ' send ', null, ' If @err <>0 goto lberrif @function = ' GET ' beginexec @err =sp_ Oagetproperty @obj, ' responsetext ', @value outputif @err <>0 goto lberrend--print @valueexec @err =sp_oadestroy @ Objreturnlberr:exec sp_OAGetErrorInfo 0, @src out, @desc outselect cast (@err as varbinary (4))As error number, @src as error source, @desc as error description 

Write some scattered, another day I will be packing. Try to be more clear about the benefits and principles.

The benefits of this, and the disadvantage of your own to realize. It's natural to know what you've done.

　　

The use of SQL SERVER tasks in software development