DS9208升级说明 V3

1、 在数据库QRCode中创建表t_Counter.

USE [QRcode]
GO

/****** Object:  Table [dbo].[t_Counter]    Script Date: 2017-11-18 16:09:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[t_Counter](
	[fInterId] [int] IDENTITY(1,1) NOT NULL,
	[fDate] [datetime] NULL,
	[fCounter] [int] NULL,
 CONSTRAINT [PK_t_Counter] PRIMARY KEY CLUSTERED 
(
	[fInterId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

2、在数据库QRCode中创建存储过程CreateOrUpdateQRCodeCounter

USE [QRcode]
GO
/****** Object:  StoredProcedure [dbo].[CreateOrUpdateQRCodeCounter]    Script Date: 2017-11-18 16:11:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CreateOrUpdateQRCodeCounter]
	-- Add the parameters for the stored procedure here

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE @QRCodeCounT INT

	DECLARE @CurrDate varchar(10)
	DECLARE @MaxDate varchar(10)
    -- Insert statements for procedure here
	SELECT @QRCodeCount = SUM([FActQty]) FROM [dbo].icstock
	
	SELECT @CurrDate = CONVERT(varchar(10),GETDATE() ,120)

	SELECT TOP 1 @MaxDate =  CONVERT(varchar(10),[fDate] ,120) FROM [dbo].[t_Counter] ORDER BY [fDate] DESC
	
	IF(@CurrDate = @MaxDate)
		BEGIN
			UPDATE [dbo].[t_Counter]  SET [fCounter] = @QRCodeCount WHERE  CONVERT(varchar(10),fDate ,120) = @CurrDate
		END 
	ELSE
		BEGIN
			INSERT INTO [dbo].[t_Counter]  ([fDate] ,[fCounter]) VALUES (@CurrDate ,@QRCodeCount)
		END	
END

3、为存储过程设置作业 

参考https://jingyan.baidu.com/article/e9fb46e19a0b0c7520f76679.html

SQL Server代理服务设置成自动启动！！！

打开SQL Server Management Studio,SQL Server代理--作业--点右键--新建作业

作业的名称取为:exec reateOrUpdateQRCodeCounterr并进行说明,若作业的数量太多,以方便区别;这里写上定时执行存储过程CreateOrUpdateQRCodeCounter确定保存.

点击常规下面的步骤,按箭头指示,新建步骤.

取名步骤名称:exec CreateOrUpdateQRCodeCounter注意选择存储过程所在的数据库录入需要执行的命令:exec CreateOrUpdateQRCodeCounter ,确定保存.若有其它参数命令也可以编写

接下来设置计划,自动定时执行的时间.新建计划,进放计划属性设置窗口.

依然要填写计划的名称:exec CreateOrUpdateQRCodeCounter计划类型:重复计划时间频率:每间隔30分自动执行一次持续时间:无结束日期确定保存.到此基本功能已经设置完成,额外其它功能可以根据需要进行增加.

4、更新DS9208.exe

5、完成