SQL Server作业的同步(四)
' @active_end_time='+RTRIM(@active_end_time)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @schedule_uid=N'''+RTRIM(NEWID())+''''
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
END
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N''(local)'''
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
SET @retrun = @retrun+CHAR(13)+CHAR(10) + 'COMMIT TRANSACTION'
SET @retrun = @retrun+CHAR(13)+CHAR(10) + 'GOTO EndSave'
SET @retrun = @retrun+CHAR(13)+CHAR(10) + 'QuitWithRollback:'
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' IF(@@TRANCOUNT>0)ROLLBACK TRANSACTION'
SET @retrun = @retrun+CHAR(13)+CHAR(10) + 'EndSave:'
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' '
www.2cto.com
select @retrun
我创建了一个存储过程，用来导出 作业，只有用powershell 脚本来实现同步，你可以powershell 脚本放入 sqlagent 中 定时运行起到同步的效果
一下是powershell 代码：
$server = "(local)"
$uid = "sa"
$db="master"
$pwd="fanzhouqi"
$mailprfname = "sina"
$recipients = "32116057@qq.com"
$subject = 'System Log'
function execproc($message)
{
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$CnnString ="Server = $server; Database = $db;User Id = $uid; Password = $pwd"
$SqlConnection.ConnectionString = $CnnString
$CC = $SqlConnection.CreateCommand();
$CC.CommandText=$message
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $CC
$dataset = New-Object System.Data.DataSet
#$SqlConnection.SelectCommand = $CC
if (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() }
$adapter.Fill($dataset) |out-null
$dataset.Tables[0].Rows[0][0]
$SqlConnection.Close();
}
function execsql($message)
{ www.2cto.com
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$CnnString ="Server = fanr-pc\sql2012; Database = $db;User Id = $uid; Password = $pwd"
$SqlConnection.ConnectionString = $CnnString
$CC = $SqlConnection.CreateCommand();
if (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() }
$cc.CommandText=$message
$cc.ExecuteNonQuery()|out-null
$SqlConnection.Close();
}
$jobscript = execproc " EXEC master..DumpJob @job = 'backup'"
#$jobscript
execsql $jobscript
作者 Amaranthus