August 15, 2009 at 4:55 pm
Hello all,
I am trying to analyze the extent to which two replicating databases on separate servers are out of sync. I am using TableDiff to do this. Because TableDiff can take a while to run (especially if the connection between the two servers isn't too fast), I want to create a batch file to run TableDiff for all the different tables in the database.
I am using a simple select statement to generate the commands to be issued to TableDiff.
select 'tablediff -sourceserver SERVER1 -sourcedatabase Database1 -sourcetable '
+ TABLE_NAME
+ ' -destinationserver SERVER2 -destinationdatabase Database2 -destinationtable '
+ TABLE_NAME
+ ' -o c:\resync\tdOutputTbl1.txt -f c:\resync\tdUpdateTbl1.sql'
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
order by TABLE_NAME
This query generates commands for all the tables in the database. However, there is a problem. If I run all the commands in a batch file, all the results will be lumped together.
Ideally, I'd like to put in a variable into the query, so that the outputted file from TableDiff will be named corresponding to the tables they are for. For example, if the tables I'm running them for are TableA and TableB, I'd like the output files to be "tdOutputTableA.txt", "tdOutputTableA.sql", "tdOutputTableB.txt", and "tdOutputTableB.sql". And so forth.
I'm not that strong with SQL, so I'm not sure how to write a query that will append truncated versions of the tables' names to the queries, so that I can put it in a batch file. Even an incrementing variable that would keep the output separate for separate tables (but would not be descriptive) would be ok.
Does anyone know how I might go about doing this? I would appreciate any help or advice that anyone can give. Thanks.
August 16, 2009 at 6:48 am
This query will help you.
select 'tablediff -sourceserver .\sql2k5 -sourcedatabase test12king -sourcetable '
+ TABLE_NAME
+ ' -destinationserver . -destinationdatabase test12king -destinationtable '
+ TABLE_NAME
+' -et diff_'+table_name
+ ' -o c:\resync\tdOutputTbl1.txt -f c:\resync\tdUpdateTbl1.sql'
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
order by TABLE_NAME
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 16, 2009 at 7:19 am
It will create Different-tables for all your tables at your destination server with table names prefixed with 'Diff_'
and these tables will have all the adjustment statements.
eNJOY:-)
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 17, 2009 at 5:36 pm
Thanks very much for your reply. I actually don't want to create any tables in the databases based on the TableDiff results. I'm mainly looking for a good way to automate the naming of the output files of the TableDiff command.
From your post, I gained a better understanding of using the Table_Name field as a placeholder. Thus, here is what I have now:
select 'tablediff -sourceserver SERVER1 -sourcedatabase Database1 -sourcetable '
+ TABLE_NAME
+ ' -destinationserver SERVER2 -destinationdatabase Database2 -destinationtable '
+ TABLE_NAME
+ ' -o c:\resync\tdOutput' + Table_Name + '.txt -f c:\resync\tdUpdate' + Table_Name + '.sql'
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
order by TABLE_NAME
This works great, except for one problem. I am using TableDiff on SQL 2000 databases. This may happen regardless of the database version, but the names of the files get truncated (including the extension).
So, for example, for Table1, the output files should be "tdOutputTable1.txt" and "tdUpdateTable1.sql". However, I'm getting "tdOutputTa" and "tdUpdateTa" (for example).
Thus, what I was wondering is if there is some way I can apply a mid function in the query, something like:
+ ' -o c:\resync\tdOutput' + Mid$(Table_Name, 1, 4) + '.txt -f c:\resync\tdUpdate' + Mid$(Table_Name, 1, 4) + '.sql'
That is, is there a statement for this query that will take the first 4 characters of a table name, and use it in generating the select statements? I would greatly appreciate any help anyone can give.
Thanks.
August 17, 2009 at 5:45 pm
Ah! With a little bit of Googling, I discovered the SQL equivalent of the Mid function is Substring. Thus, the following code gave me what I wanted:
+ ' -o c:\resync\tdOutput' + Substring(Table_Name, 1, 4) + '.txt -f c:\resync\tdUpdate' + Substring(Table_Name, 1, 4) + '.sql'
Thus, my understanding of Substring is that it takes arguments of the form:
Substring(field_name, start_position, substring_length)
Hopefully, this finding will be helpful to someone. 🙂
August 17, 2009 at 7:40 pm
cjohn5552 (8/17/2009)
Ah! With a little bit of Googling, I discovered the SQL equivalent of the Mid function is Substring. Thus, the following code gave me what I wanted:+ ' -o c:\resync\tdOutput' + Substring(Table_Name, 1, 4) + '.txt -f c:\resync\tdUpdate' + Substring(Table_Name, 1, 4) + '.sql'
Thus, my understanding of Substring is that it takes arguments of the form:
Substring(field_name, start_position, substring_length)
Hopefully, this finding will be helpful to someone. 🙂
Just curious... what will happen if you have 3 tables named as follows (or something similar as frequently happens)?
ShipmentHeader
ShipmentDetail
ShipmentLog
--Jeff Moden
August 18, 2009 at 11:50 am
Hehe--excellent question, Jeff. That leads me to my next question.
When table names have similar beginnings, the query generates duplicate file names. If the table names were long enough, I suppose you could try combining it with a character further in the table name, in hopes of distinguishing them (e.g. take the first 4 characters of a table name and the 7th or 8th character).
What I was hoping to do is have some sort of incrementing variable that distinguishes the table names. Is there some way of referencing the row number of a query result (even if it's not actually an identity row)? In any case, I would like something that appends 1, 2, 3, etc., to the outputted files.
I would appreciate any advice that anyone can provide. Thanks.
August 23, 2009 at 6:09 am
I'll recommend to use the below script
[Code]select 'tablediff -sourceserver .\sql2k5 -sourcedatabase test12king -sourcetable '
+ TABLE_NAME
+ ' -destinationserver . -destinationdatabase test12king -destinationtable '
+ TABLE_NAME
+' -et diff_'+table_name
+ ' -o c:\resync\tdOutputTbl1.txt -f c:\resync\tdUpdateTbl1.sql'
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
order by TABLE_NAME[/code]
It'll create a Different table with all the required scripts for each table with a unique name i.e.
If table name is Update_items the utility will create a table at the destination DB with name Diff_Update_items.
& it'll sove your purpose 1. you'll get all the scripts needed to fix the diff. & 2. Full table names will be there.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
July 5, 2010 at 1:43 pm
Hi
I am executing the above query but tables are not being creeated and the script is not giving any error also
Please suggest what needs to be checked.
Thanks
Rajendra:-)
July 6, 2010 at 6:06 am
rajmaiet (7/5/2010)
HiI am executing the above query but tables are not being creeated and the script is not giving any error also
Please suggest what needs to be checked.
Thanks
Rajendra:-)
If you ran the code exactly as it was given, that would be a problem because the server instances are probably not named correctly. You do have to mod the code with correct names to get it to work correctly.
--Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply