/*
--启用CMDshell
;EXEC sp_configure 'show advanced options', 1
;RECONFIGURE WITH OVERRIDE --
;EXEC sp_configure 'xp_cmdshell', 1
;RECONFIGURE WITH OVERRIDE
;EXEC sp_configure 'show advanced options', 0
*/
SET NOCOUNT ON
DECLARE @difftablesql VARCHAR(6000)
DECLARE @outputfile VARCHAR(400)
DECLARE @deloldoutputfile VARCHAR(400)
DECLARE @sourceserver VARCHAR(400)
DECLARE @sourceuser VARCHAR(400)
DECLARE @sourcepassword VARCHAR(400)
DECLARE @sourcedatabase VARCHAR(400)
DECLARE @sourcetable VARCHAR(400)
DECLARE @destinationserver VARCHAR(400)
DECLARE @destinationuser VARCHAR(400)
DECLARE @destinationpassword VARCHAR(400)
DECLARE @destinationdatabase VARCHAR(400)
DECLARE @destinationtable VARCHAR(400)
SET @sourceserver = '.\sql2005'
SET @sourceuser = 'sa'
SET @sourcepassword = 'abc.123'
SET @sourcedatabase = 'DatabaseA'
SET @sourcetable = 'SourceTable'
SET @destinationserver = '.\sql2005A'
SET @destinationuser = 'sa'
SET @destinationpassword = 'abc.123'
SET @destinationdatabase = 'DatabaseB'
SET @destinationtable = 'SourceTable'
SET @outputfile='C:\diffs.sql'
SET @deloldoutputfile='del '+@outputfile
--C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe --添加到环境
SET @difftablesql ='tablediff.exe '
SET @difftablesql =@difftablesql+' -sourceserver "'+@sourceserver+'"'
SET @difftablesql =@difftablesql+' -sourceuser "'+@sourceuser+'"'
SET @difftablesql =@difftablesql+' -sourcepassword "'+@sourcepassword+'"'
SET @difftablesql =@difftablesql+' -sourcedatabase "'+@sourcedatabase+'"'
SET @difftablesql =@difftablesql+' -sourcetable "'+@sourcetable+'"'
SET @difftablesql =@difftablesql+' -destinationserver "'+@destinationserver+'"'
SET @difftablesql =@difftablesql+' -destinationuser "'+@destinationuser+'"'
SET @difftablesql =@difftablesql+' -destinationpassword "'+@destinationpassword+'"'
SET @difftablesql =@difftablesql+' -destinationdatabase "'+@destinationdatabase+'"'
SET @difftablesql =@difftablesql+' -destinationtable "'+@destinationtable+'"'
SET @difftablesql =@difftablesql+' -sourcelocked -destinationlocked -f "' +@outputfile+'"'
PRINT @difftablesql
EXEC master..xp_cmdshell @deloldoutputfile
EXEC master..xp_cmdshell @difftablesql
DECLARE @sqlcmd VARCHAR(6000)
set @sqlcmd='sqlcmd '
set @sqlcmd=@sqlcmd+' -S "'+ @destinationserver+'"'
set @sqlcmd=@sqlcmd+' -U "'+ @destinationuser+'"'
set @sqlcmd=@sqlcmd+' -P "'+ @destinationpassword+'"'
set @sqlcmd=@sqlcmd+' -d "'+ @destinationdatabase+'"'
set @sqlcmd=@sqlcmd+' -i "'+ @outputfile+'"'
PRINT @sqlcmd
EXEC master..xp_cmdshell @sqlcmd
--EXEC master..xp_cmdshell @deloldoutputfile
SELECT * FROM DatabaseA.dbo.SourceTable
SELECT * FROM [2005a].DatabaseB.dbo.SourceTable