调度程序帮助

扫码查看
本文介绍了调度程序帮助的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

嗨专家,

我有脚本这个脚本每天都会手动运行。

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

请帮助

推荐答案

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


这篇关于调度程序帮助的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-23 00:03
查看更多