帮助tablediff.exe高级解决方案 [英] Help with tablediff.exe advanced solution
问题描述
Hello Folks -
Hello Folks -
我需要在客户端使用tablediff.exe，但是我需要遍历所有表然后执行同步脚本而不是执行EACH表的命令。
I have a need to use tablediff.exe at a client, however I need to cycle through ALL tables and then execute the synchronization script rather than execute a command for EACH table.
据说，我已经能够找到解决方案来做到这一点。 它正确打印到屏幕，但是，它不会将生成的.sql脚本导出到指定的目录。可以帮我解决这个问题吗？
With that said, I've been able to track down a solution to do that. It prints to the screen correctly, however, it's not exporting the generated .sql script to the directory specified. Can any help me fix this?
代码如下：
USE hypdrm
go
DECLARE @compareschema NVARCHAR（100），@ comparetable NVARCHAR（400）
DECLARE @sourceserver NVARCHAR（100），@ destinationserver NVARCHAR（100）
DECLARE @sourcedatabase NVARCHAR（100），@ destinationdatabase NVARCHAR（100）
DECLARE @ tablediffpath NVARCHAR（512）
DECLARE @command NVARCHAR（4000）
SET @sourceserver ='EPM11124'
SET @sourcedatabase ='hypdrm'
SET @destinationserver ='EPM11123TRAIN'
SET @destinationdatabase ='HYP_DRM_TRAIN'
- 在这里设置正确的路径....
SET @ tablediffpath ='" C：\Program Files \ Microsoft SQL Server \\\\\\\\\\\\\ .exe"'
DECLARE tablenames_cursor CURSOR FAST_FORWARD LOCAL FOR $ $ $ $ SELECT TABLE_SCHEMA，TABLE_NAME
FROM INFORMATION _SCHEMA.TABLES
WHERE TABLE_TYPE ='BASE TABLE'
ORDER BY TABLE_NAME
OPEN tablenames_cursor
FETCH NEXT FROM tablenames_cursor INTO @compareschema，@ comparetable
WHILE（@@ FETCH_STATUS<> -1）
BEGIN
SET @command = @tablediffpath +' - sourceserver'+ @sourceserver +' - sourcedatabase'+ @sourcedatabase +' - sourceschema'+ @compareschema +' - sourcetable'+
@comparetable +'-destinationserver'+ @destinationserver +'-destinationdatabase'+ @destinationdatabase +'-destinationschema'+ @compareschema +
'-destinationtable'+ @comparetable +' - f C：\Hyperion_Batch \Scripts\batch\CT_POC\SQL_MGMT \ DIFFTEST-'+ @comparetable +'。sql'+' -o C：\Hyperion_Batch \Scripts\batch\CT_POC\SQL_MGMT \ DIFFTEST-'+ @comparetable +'。txt'
$
PRINT @命令
FETCH NEXT FROM tablenames_cursor INTO @compareschema，@ comparetable
END
CLOSE tablenames_cursor
DEALLOCATE tablenames_cursor
USE hypdrm
go
DECLARE @compareschema NVARCHAR(100), @comparetable NVARCHAR(400)
DECLARE @sourceserver NVARCHAR(100), @destinationserver NVARCHAR(100)
DECLARE @sourcedatabase NVARCHAR(100), @destinationdatabase NVARCHAR(100)
DECLARE @tablediffpath NVARCHAR(512)
DECLARE @command NVARCHAR(4000)
SET @sourceserver = 'EPM11124'
SET @sourcedatabase = 'hypdrm'
SET @destinationserver = 'EPM11123TRAIN'
SET @destinationdatabase = 'HYP_DRM_TRAIN'
-- set the right path here....
SET @tablediffpath= '"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe"'
DECLARE tablenames_cursor CURSOR FAST_FORWARD LOCAL FOR
SELECT TABLE_SCHEMA,TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
OPEN tablenames_cursor
FETCH NEXT FROM tablenames_cursor INTO @compareschema, @comparetable
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @command = @tablediffpath + ' -sourceserver ' + @sourceserver + ' -sourcedatabase ' + @sourcedatabase + ' -sourceschema ' + @compareschema + ' -sourcetable ' +
@comparetable + ' -destinationserver ' + @destinationserver + ' -destinationdatabase ' + @destinationdatabase + ' -destinationschema ' + @compareschema +
' -destinationtable ' + @comparetable + ' -f C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-' + @comparetable + '.sql' + ' -o C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-' + @comparetable + '.txt'
PRINT @command
FETCH NEXT FROM tablenames_cursor INTO @compareschema, @comparetable
END
CLOSE tablenames_cursor
DEALLOCATE tablenames_cursor
也可以使用任何其他自动化解决方案来完成我的目标。
Also open to any other automated solutions to accomplish my goal.
谢谢大家！
推荐答案
你的避风港没有明确说明，但我假设您正在从"PRINT @ command"获取输出。步骤，然后从服务器上的cmd提示符执行它？
You haven't stated it explicitly, but I assume that you are taking the output from the "PRINT @command" step and then executing it from a cmd prompt on your server?
假设是这种情况，您是否也确定它为生成脚本存在一些差异？
Assuming that is the case, are you also sure that there are some differences for it to generate scripts for?
您可以使用的一些替代工具包括Redgate的SQL数据比较和SQL比较工具 - 这些工具可以帮助生成一个脚本来对齐这两个实例。
Some alternative tools you could use include Redgate's SQL Data Compare and SQL Compare tools - these can help produce a single script to align the two instances.
这篇关于帮助tablediff.exe高级解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
