/*

--启用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

 --C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe --添加到环境

SET @difftablesql ='tablediff.exe '

SET @difftablesql -sourceserver "

SET @difftablesql -sourceuser "

SET @difftablesql -sourcepassword "

SET @difftablesql -sourcedatabase "

SET @difftablesql -sourcetable "

SET @difftablesql -destinationserver "

SET @difftablesql -destinationuser "

SET @difftablesql -destinationpassword "

SET @difftablesql -destinationdatabase "

SET @difftablesql -destinationtable "

SET @difftablesql -sourcelocked -destinationlocked  -f "'

 

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