调度程序帮助 [英] scheduler help
问题描述
嗨专家,
我有脚本这个脚本每天都会手动运行。
I am having script this script will be running daily manually.
我想把它放在程序中并安排工作
I want to put this in procedure and schedule the job
创建PROC SCHEDULER
exec sp_rename'INCOMRQST_LOG','INCMRQST_2202'
$
CREATE TABLE [dbo]。[INCMRQST_LOG](
[SNO_PK] [int] IDENTITY(1,1)NOT FOR RELICATION NOT NOT NULL,b
[INSTID] [int] NULL,
[MOB] [varchar](20)NULL,
[日期] [datetime2]( 7)NULL,
[RQST] [varchar](4000)NULL,
[ENC_RQST_ID] [varchar](300)NULL,
[IP_ADDR ] [varchar](20)NULL,
[HASHKEY] [varchar](100 )NULL,
PRIMARY KEY NONCLUSTERED
(
[MCI_SNO_PK ] ASC
)WITH(PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON,FILLFACTOR = 100)
)
GO
ALTER TABLE [dbo]。[INCMRQST_LOG] ADD DEFAULT(getdate())FOR [MCI_DATE]
GO
创建不支持的指数[IDX_INCMRQST_LOG_P1] ON [dbo]。[INCMRQST_LOG]
(
[MOB] ASC,
[ENC_RQST_ID] ASC ,
[INSTID] ASC
)WITH(PAD_INDEX = ON,STATISTICS_NORECOMPUTE = OFF,SORT_IN_TEMPDB = OFF,DROP_EXISTING = OFF,ONLINE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON,FILLFACTOR = 100)
GO
Create PROC SCHEDULER
exec sp_rename 'INCMRQST_LOG','INCMRQST_2202'
CREATE TABLE [dbo].[INCMRQST_LOG](
[SNO_PK] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[INSTID] [int] NULL,
[MOB] [varchar](20) NULL,
[DATE] [datetime2](7) NULL,
[RQST] [varchar](4000) NULL,
[ENC_RQST_ID] [varchar](300) NULL,
[IP_ADDR] [varchar](20) NULL,
[HASHKEY] [varchar](100) NULL,
PRIMARY KEY NONCLUSTERED
(
[MCI_SNO_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100)
)
GO
ALTER TABLE [dbo].[INCMRQST_LOG] ADD DEFAULT (getdate()) FOR [MCI_DATE]
GO
CREATE NONCLUSTERED INDEX [IDX_INCMRQST_LOG_P1] ON [dbo].[INCMRQST_LOG]
(
[MOB] ASC,
[ENC_RQST_ID] ASC,
[INSTID] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100)
GO
-----分区脚本
$
BEGIN TR ANSACTION
创建分区功能[pf_mnth_JAN19](datetime2(7))值范围左下(N'2017-01-01T00:00:00',N'2017-02-01T00:00: 00',N'2017-03-01T00:00:00',N'2017-04-01T00:00:00',N'2017-05-01T00:00:00',N'2017-06-01T00: 00:00',N'2017-07-01T00:00:00',N'2017-08-01T00:00:00',
N'2017-09-01T00:00:00',N' 2017-10-01T00:00:00',N'2017-11-01T00:00:00',N'2017-12-01T00:00:00',N'2018-01-01T00:00:00', N'2018-02-01T00:00:00',N'2018-03-01T00:00:00',N'2018-04-01T00:00:00',N'2018-05-01T00:00:00 ',N'2018-06-01T00:00:00',N'2018-07-01T00:00:00',
N'2018-08-01T00:00:00',N'2018-09 -01T00:00:00',N'2018-10-01T00:00:00',N'2018-11-01T00:00:00',N'2018-12-01T00:00:00',N'2019 -01-01T00:00:00',N'2019-02-01T00:00:00',N'2019-03-01T00:00:00',N'2019-04-01T00:00:00',N '2019-05-01T00:00:00',N'2019-06-01T00:00:00',
N'2019-07-01T00:00:00',N'2019-08- 01T00:00:00',N'2019-09-01T00:00:00',N'2019-10-01T00:00:00',N'2019-11-01T00:00:00',N'2019- 12-01T00:00:00',N'2020-01-01T00:00:00',N'2020-02-01T00:00:00',N'2020-03-01T00:00:00',N' 2020-04-01T00:00:00',N'2020-05-01T00:00:00',
N'2020-06-01T00:00:00',N'2020-07-01T00:00 :00',N'2020-08-01T00:00:00',N'2020-09-01T00:00:00',N'2020-10-01T00:00:00',N'2020-11-01T00 :00:00',N'2020-12-01T00:00:00',N'2021-01-01T00:00:00')
CREATE PARTITION SCHEME [ps_mnth_JAN19] AS PARTITION [pf_mnth_JAN19 ] TO([主要],[主要],[主要],[主要],[主要],[主要],[主要],[主要],[主要],[主要],[主要],[主要] ,[小学],[小学],[小学],[小学],[小学],
[小学],[小学],[小学],[小学],[小学],[小学],[小学],[小学],[小学],[小学],[小学],[小学],[小学],[小学],[小学],[小学],[小学],[小学],[小学] ,[主要],[PRIMA RY],[主要],[主要],[主要],
[主要],[主要],[主要],[主要],[主要],[主要],[主要],[主要] ,[PRIMARY])
创建集群索引[ClusteredIndex_on_ps_mnth_JAN19] ON [dbo]。[INCMRQST_LOG]
(
[MCI_DATE]
)WITH(SORT_IN_TEMPDB = OFF,DROP_EXISTING = OFF,ONLINE = OFF)ON [ps_mnth_JAN19]([MCI_DATE])
COMMIT TRANSACTION
-----Partition Script
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [pf_mnth_JAN19](datetime2(7)) AS RANGE LEFT FOR VALUES (N'2017-01-01T00:00:00', N'2017-02-01T00:00:00', N'2017-03-01T00:00:00', N'2017-04-01T00:00:00', N'2017-05-01T00:00:00', N'2017-06-01T00:00:00', N'2017-07-01T00:00:00', N'2017-08-01T00:00:00',
N'2017-09-01T00:00:00', N'2017-10-01T00:00:00', N'2017-11-01T00:00:00', N'2017-12-01T00:00:00', N'2018-01-01T00:00:00', N'2018-02-01T00:00:00', N'2018-03-01T00:00:00', N'2018-04-01T00:00:00', N'2018-05-01T00:00:00', N'2018-06-01T00:00:00', N'2018-07-01T00:00:00',
N'2018-08-01T00:00:00', N'2018-09-01T00:00:00', N'2018-10-01T00:00:00', N'2018-11-01T00:00:00', N'2018-12-01T00:00:00', N'2019-01-01T00:00:00', N'2019-02-01T00:00:00', N'2019-03-01T00:00:00', N'2019-04-01T00:00:00', N'2019-05-01T00:00:00', N'2019-06-01T00:00:00',
N'2019-07-01T00:00:00', N'2019-08-01T00:00:00', N'2019-09-01T00:00:00', N'2019-10-01T00:00:00', N'2019-11-01T00:00:00', N'2019-12-01T00:00:00', N'2020-01-01T00:00:00', N'2020-02-01T00:00:00', N'2020-03-01T00:00:00', N'2020-04-01T00:00:00', N'2020-05-01T00:00:00',
N'2020-06-01T00:00:00', N'2020-07-01T00:00:00', N'2020-08-01T00:00:00', N'2020-09-01T00:00:00', N'2020-10-01T00:00:00', N'2020-11-01T00:00:00', N'2020-12-01T00:00:00', N'2021-01-01T00:00:00')
CREATE PARTITION SCHEME [ps_mnth_JAN19] AS PARTITION [pf_mnth_JAN19] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
CREATE CLUSTERED INDEX [ClusteredIndex_on_ps_mnth_JAN19] ON [dbo].[INCMRQST_LOG]
(
[MCI_DATE]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [ps_mnth_JAN19]([MCI_DATE])
COMMIT TRANSACTION
请帮助
推荐答案
只需使用SQL Server代理创建一个作业即可。深入了解SQL Server代理,右键单击jobs文件夹并选择新作业。并按照提示。您需要按如下方式修改proc。我认为你不需要创建这个分区的
表,因为一切都在默认的主文件组中。
Just create a job using SQL server agent to do this. Drill down on SQL Server agent, right click on the jobs folder and select new job. And follow the prompt. You will need to modify your proc as follows. I don't think you need to create this partitioned table as everything is in the default primary file group.
create PROC SCHEDULER
as
exec ('sp_rename ''INCMRQST_LOG'',''INCMRQST_2202''')
CREATE TABLE [dbo].[INCMRQST_LOG](
[SNO_PK] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[INSTID] [int] NULL,
[MOB] [varchar](20) NULL,
[DATE] [datetime2](7) NULL,
[RQST] [varchar](4000) NULL,
[ENC_RQST_ID] [varchar](300) NULL,
[IP_ADDR] [varchar](20) NULL,
[HASHKEY] [varchar](100) NULL,
PRIMARY KEY NONCLUSTERED
(
[MCI_SNO_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100)
)
ALTER TABLE [dbo].[INCMRQST_LOG] ADD DEFAULT (getdate()) FOR [MCI_DATE]
CREATE NONCLUSTERED INDEX [IDX_INCMRQST_LOG_P1] ON [dbo].[INCMRQST_LOG]
(
[MOB] ASC,
[ENC_RQST_ID] ASC,
[INSTID] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100)
-----Partition Script
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [pf_mnth_JAN19](datetime2(7)) AS RANGE LEFT FOR VALUES (N'2017-01-01T00:00:00', N'2017-02-01T00:00:00', N'2017-03-01T00:00:00', N'2017-04-01T00:00:00', N'2017-05-01T00:00:00', N'2017-06-01T00:00:00', N'2017-07-01T00:00:00', N'2017-08-01T00:00:00', N'2017-09-01T00:00:00', N'2017-10-01T00:00:00', N'2017-11-01T00:00:00', N'2017-12-01T00:00:00', N'2018-01-01T00:00:00', N'2018-02-01T00:00:00', N'2018-03-01T00:00:00', N'2018-04-01T00:00:00', N'2018-05-01T00:00:00', N'2018-06-01T00:00:00', N'2018-07-01T00:00:00', N'2018-08-01T00:00:00', N'2018-09-01T00:00:00', N'2018-10-01T00:00:00', N'2018-11-01T00:00:00', N'2018-12-01T00:00:00', N'2019-01-01T00:00:00', N'2019-02-01T00:00:00', N'2019-03-01T00:00:00', N'2019-04-01T00:00:00', N'2019-05-01T00:00:00', N'2019-06-01T00:00:00', N'2019-07-01T00:00:00', N'2019-08-01T00:00:00', N'2019-09-01T00:00:00', N'2019-10-01T00:00:00', N'2019-11-01T00:00:00', N'2019-12-01T00:00:00', N'2020-01-01T00:00:00', N'2020-02-01T00:00:00', N'2020-03-01T00:00:00', N'2020-04-01T00:00:00', N'2020-05-01T00:00:00', N'2020-06-01T00:00:00', N'2020-07-01T00:00:00', N'2020-08-01T00:00:00', N'2020-09-01T00:00:00', N'2020-10-01T00:00:00', N'2020-11-01T00:00:00', N'2020-12-01T00:00:00', N'2021-01-01T00:00:00')
CREATE PARTITION SCHEME [ps_mnth_JAN19] AS PARTITION [pf_mnth_JAN19] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
CREATE CLUSTERED INDEX [ClusteredIndex_on_ps_mnth_JAN19] ON [dbo].[INCMRQST_LOG]
(
[MCI_DATE]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [ps_mnth_JAN19]([MCI_DATE])
COMMIT TRANSACTION
这篇关于调度程序帮助的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!