Calling BATCH & SQL Experts

[SIMMS7400]

HI Folks -

I just want to start out by saying if this is the wrong forum, then please feel free to remove it.

With that said, I know some of you have MSSQL Server experience based off other threads. Currently, I use an 'out of hte box' SQL utility called tablediff.exe.

This utility only updates one table at a time. We are looking to establish a more formal table synchronization process, so I was wondering if anyone has built a way that updates ALL tables between source and destination.

My code to synchronize just 1 table between source and destination is as such:

Code: Select all

::
::-- Option [3],[4] --::
::-- Execute tablediff.exe --::
::
SET SQLS=EXEC_SQL_SYNC.sql
IF EXIST %CD%\EXEC_SQL_SYNC.sql DEL %CD%\EXEC_SQL_SYNC.sql
CALL "%TD_CMD_PATH%" -sourceserver [%SSQLS%]  -sourcedatabase [%SSQLDB%] -sourcetable [%SSQLT%] -sourceuser [%SSQLU%] -sourcepassword [%SSQLP%] -destinationserver [%DSQLS%] -destinationdatabase [%DSQLDB%] -destinationtable [%DSQLT%] -destinationuser [%DSQLU%] -destinationpassword [%DSQLP%] -dt Difference -f %CD%\%SQLS%

And works as expected. Looking for a more dynamic approach to synchronize all tables in an automated fashion. Now, I have other processes built that restore entire DB's which I could look to use if it came down to it. But I rather update existing tables than restore an entire DB.

Thank you, all.

[SIMMS7400]

I got it folks.

I run the following SQL command:

Code: Select all

"%SQL_CMD_PATH%" -S EPM11124 -U EPMAdmin -P Password -d master -h-1 -i EXEC_SQL_SYNC_ALL.sql -o C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\EXEC_SYNC_ALLZ.bat

The SQL -i file is the following:

Code: Select all

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 @sourceuser NVARCHAR(100), @destinationuser NVARCHAR(100)
DECLARE @sourcepassword NVARCHAR(100), @destinationpassword NVARCHAR(100)

DECLARE @tablediffpath NVARCHAR(512)
DECLARE @command NVARCHAR(4000)

SET @sourceserver = 'EPM11124'
SET @sourcedatabase = 'hypdrm'
SET @sourceuser = 'sa'
SET @sourcepassword = 'Password'

SET @destinationserver = 'EPM11123TRAIN'
SET @destinationdatabase = 'HYP_DRM_TRAIN'
SET @destinationuser = 'sa'
SET @destinationpassword = 'Password'

-- 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 + ' -sourceuser ' + @sourceuser + ' -sourcepassword ' + @sourcepassword +' -sourceschema ' + @compareschema + ' -sourcetable ' +
@comparetable + ' -destinationserver ' + @destinationserver + ' -destinationdatabase ' + @destinationdatabase + ' -destinationuser ' + @destinationuser + ' -destinationpassword ' + @destinationpassword + ' -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

Which outputs the tablediff.exe syntax is the correct format based on all of my tables.

The tablediff.exe output looks like this:

Code: Select all

"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver EPM11124 -sourcedatabase hypdrm -sourceuser sa -sourcepassword Password -sourceschema dbo -sourcetable DAL_Sequence -destinationserver EPM11123TRAIN -destinationdatabase HYP_DRM_TRAIN -destinationuser sa -destinationpassword Password -destinationschema dbo -destinationtable DAL_Sequence -f C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-DAL_Sequence.sql -o C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-DAL_Sequence.txt
"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver EPM11124 -sourcedatabase hypdrm -sourceuser sa -sourcepassword Password -sourceschema dbo -sourcetable DAL_Updates -destinationserver EPM11123TRAIN -destinationdatabase HYP_DRM_TRAIN -destinationuser sa -destinationpassword Password -destinationschema dbo -destinationtable DAL_Updates -f C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-DAL_Updates.sql -o C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-DAL_Updates.txt
"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver EPM11124 -sourcedatabase hypdrm -sourceuser sa -sourcepassword Password -sourceschema dbo -sourcetable RM_Access_Group_Ctrl_Hier -destinationserver EPM11123TRAIN -destinationdatabase HYP_DRM_TRAIN -destinationuser sa -destinationpassword Password -destinationschema dbo -destinationtable RM_Access_Group_Ctrl_Hier -f C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Access_Group_Ctrl_Hier.sql -o C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Access_Group_Ctrl_Hier.txt
"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver EPM11124 -sourcedatabase hypdrm -sourceuser sa -sourcepassword Password -sourceschema dbo -sourcetable RM_Access_Group_Definition -destinationserver EPM11123TRAIN -destinationdatabase HYP_DRM_TRAIN -destinationuser sa -destinationpassword Password -destinationschema dbo -destinationtable RM_Access_Group_Definition -f C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Access_Group_Definition.sql -o C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Access_Group_Definition.txt
"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver EPM11124 -sourcedatabase hypdrm -sourceuser sa -sourcepassword Password -sourceschema dbo -sourcetable RM_Access_Group_Prop_Global -destinationserver EPM11123TRAIN -destinationdatabase HYP_DRM_TRAIN -destinationuser sa -destinationpassword Password -destinationschema dbo -destinationtable RM_Access_Group_Prop_Global -f C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Access_Group_Prop_Global.sql -o C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Access_Group_Prop_Global.txt
"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver EPM11124 -sourcedatabase hypdrm -sourceuser sa -sourcepassword Password -sourceschema dbo -sourcetable RM_Access_Group_Prop_Local -destinationserver EPM11123TRAIN -destinationdatabase HYP_DRM_TRAIN -destinationuser sa -destinationpassword Password -destinationschema dbo -destinationtable RM_Access_Group_Prop_Local -f C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Access_Group_Prop_Local.sql -o C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Access_Group_Prop_Local.txt
"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver EPM11124 -sourcedatabase hypdrm -sourceuser sa -sourcepassword Password -sourceschema dbo -sourcetable RM_Access_Group_User -destinationserver EPM11123TRAIN -destinationdatabase HYP_DRM_TRAIN -destinationuser sa -destinationpassword Password -destinationschema dbo -destinationtable RM_Access_Group_User -f C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Access_Group_User.sql -o C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Access_Group_User.txt
"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver EPM11124 -sourcedatabase hypdrm -sourceuser sa -sourcepassword Password -sourceschema dbo -sourcetable RM_Blender -destinationserver EPM11123TRAIN -destinationdatabase HYP_DRM_TRAIN -destinationuser sa -destinationpassword Password -destinationschema dbo -destinationtable RM_Blender -f C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Blender.sql -o C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Blender.txt
"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver EPM11124 -sourcedatabase hypdrm -sourceuser sa -sourcepassword Password -sourceschema dbo -sourcetable RM_Blender_Default_Param -destinationserver EPM11123TRAIN -destinationdatabase HYP_DRM_TRAIN -destinationuser sa -destinationpassword Password -destinationschema dbo -destinationtable RM_Blender_Default_Param -f C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Blender_Default_Param.sql -o C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Blender_Default_Param.txt
"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver EPM11124 -sourcedatabase hypdrm -sourceuser sa -sourcepassword Password -sourceschema dbo -sourcetable RM_Blender_Hierarchy_Set -destinationserver EPM11123TRAIN -destinationdatabase HYP_DRM_TRAIN -destinationuser sa -destinationpassword Password -destinationschema dbo -destinationtable RM_Blender_Hierarchy_Set -f C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Blender_Hierarchy_Set.sql -o C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Blender_Hierarchy_Set.txt
"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver EPM11124 -sourcedatabase hypdrm -sourceuser sa -sourcepassword Password -sourceschema dbo -sourcetable RM_Blender_Property_List -destinationserver EPM11123TRAIN -destinationdatabase HYP_DRM_TRAIN -destinationuser sa -destinationpassword Password -destinationschema dbo -destinationtable RM_Blender_Property_List -f C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Blender_Property_List.sql -o C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Blender_Property_List.txt
"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver EPM11124 -sourcedatabase hypdrm -sourceuser sa -sourcepassword Password -sourceschema dbo -sourcetable RM_Blender_Property_List_Item -destinationserver EPM11123TRAIN -destinationdatabase HYP_DRM_TRAIN -destinationuser sa -destinationpassword Password -destinationschema dbo -destinationtable RM_Blender_Property_List_Item -f C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Blender_Property_List_Item.sql -o C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Blender_Property_List_Item.txt
"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver EPM11124 -sourcedatabase hypdrm -sourceuser sa -sourcepassword Password -sourceschema dbo -sourcetable RM_Book -destinationserver EPM11123TRAIN -destinationdatabase HYP_DRM_TRAIN -destinationuser sa -destinationpassword Password -destinationschema dbo -destinationtable RM_Book -f C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Book.sql -o C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Book.txt
"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver EPM11124 -sourcedatabase hypdrm -sourceuser sa -sourcepassword Password -sourceschema dbo -sourcetable RM_Book_Default_Param -destinationserver EPM11123TRAIN -destinationdatabase HYP_DRM_TRAIN -destinationuser sa -destinationpassword Password -destinationschema dbo -destinationtable RM_Book_Default_Param -f C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Book_Default_Param.sql -o C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Book_Default_Param.txt
"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver EPM11124 -sourcedatabase hypdrm -sourceuser sa -sourcepassword Password -sourceschema dbo -sourcetable RM_Book_Item -destinationserver EPM11123TRAIN -destinationdatabase HYP_DRM_TRAIN -destinationuser sa -destinationpassword Password -destinationschema dbo -destinationtable RM_Book_Item -f C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Book_Item.sql -o C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Book_Item.txt
"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver EPM11124 -sourcedatabase hypdrm -sourceuser sa -sourcepassword Password -sourceschema dbo -sourcetable RM_Category -destinationserver EPM11123TRAIN -destinationdatabase HYP_DRM_TRAIN -destinationuser sa -destinationpassword Password -destinationschema dbo -destinationtable RM_Category -f C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Category.sql -o C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Category.txt
"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver EPM11124 -sourcedatabase hypdrm -sourceuser sa -sourcepassword Password -sourceschema dbo -sourcetable RM_Category_User -destinationserver EPM11123TRAIN -destinationdatabase HYP_DRM_TRAIN -destinationuser sa -destinationpassword Password -destinationschema dbo -destinationtable RM_Category_User -f C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Category_User.sql -o C:\Hyperion_Batch\Scripts\batch\CT_POC\SQL_MGMT\DIFFTEST-RM_Category_User.txt

Then, the output of the tablediff.exe executions spit out *.sql files (if there are differences between source and target) in which ill invoke SQLCMD to execute them.

Sorry for the bother! As always, comments/critiques welcomed!