能不能创建一个触发器,让它一定时间的去执行 能不能创建一个触发器,让它一定时间的去执行,例如每天12点执行菜鸟一个,恳请指点啊~~ 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 那就写个存储过程,使用JOB每天12点运行最好. 楼主,你的要求用作业(JOB) 不是更好吗。 ..........这个我距离job都用代码写的地步还很遥远 要实在不行,那就在服务中先开去agent,然后在企业管理器中设置作业好了。 这个脚本创建的是一个只有一个步骤的,名字叫JOB_TEST,每天在12点执行的JOB改脚本是SQL2005的脚本USE [msdb]GOBEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'JOB_TEST', --JOB名称 @enabled=1, @notify_level_eventlog=2, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Test_STEP1', --只有一个步骤 @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=1, @os_run_priority=0, @subsystem=N'TSQL', @command=N'exec SP_text', --你需要执行的脚本 @database_name=N'DBNAME', --你的数据库名字 @flags=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'daily twice', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=8, @freq_subday_interval=12, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20060429, @active_end_date=99991231, @active_start_time=80000, @active_end_time=200159IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave: 这个脚本创建的是一个只有一个步骤的,名字叫JOB_TEST,每天在12点执行的JOB改脚本是SQL2005的脚本USE [msdb]GOBEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'JOB_TEST', --JOB名称 @enabled=1, @notify_level_eventlog=2, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Test_STEP1', --只有一个步骤 @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=1, @os_run_priority=0, @subsystem=N'TSQL', @command=N'exec SP_text', --你需要执行的脚本 @database_name=N'DBNAME', --你的数据库名字 @flags=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'daily twice', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=8, @freq_subday_interval=12, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20060429, @active_end_date=99991231, @active_start_time=80000, @active_end_time=200159IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave: 求一SQL语句优化``` 键列信息不足或不正确,更新影响到多行 求一SQL语句,根据日期插入数据 (!邹建请进)启动作业时找不到作业服务器 找高手探讨sqlserver事件信息采集 我写的一个工具,送给要维护又臭又长又乱的prodedure的朋友们 xp_cmdshell 执行时发生错误 国庆节快乐,这个SQL语句怎么写呀? 求教一个数据库查询问题 SQL Server2005数据库作业的使用(在线等) MSSQL2005数据库移植MSSQL2000 help !! 此sql语句如何优化。1千万条数据中查需要7,8分钟
改脚本是SQL2005的脚本USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'JOB_TEST', --JOB名称
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Test_STEP1', --只有一个步骤
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=1,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec SP_text', --你需要执行的脚本
@database_name=N'DBNAME', --你的数据库名字
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'daily twice',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=12,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20060429,
@active_end_date=99991231,
@active_start_time=80000,
@active_end_time=200159
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
改脚本是SQL2005的脚本USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'JOB_TEST', --JOB名称
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Test_STEP1', --只有一个步骤
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=1,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec SP_text', --你需要执行的脚本
@database_name=N'DBNAME', --你的数据库名字
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'daily twice',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=12,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20060429,
@active_end_date=99991231,
@active_start_time=80000,
@active_end_time=200159
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: