Compare MS SQL Server data bases

[knut]

Hello,

actually I have a problem and it would help me, if I could compare the content of two databases (MS SQL Server, tables with same name and structure - I hope )

For BC2 I detetced "{url=http://www.scootersoftware.com/download.php?zz=kb_morerules]Rules That Use External Conversion[/url]" include MS SQL Server Object/MS SQL Server SELECT.

Is there something similar for BC3?

[Michael Bulgrien]

Beyond Compare has never had the ability to connect to a SQL Server database and compare live SQL Server objects. A SQL plug-in is available to enable BC2 to compare exported SQL server objects (SQL scripts exported to text files). BC3 comes with built-in file formats including the ability to compare SQL Scripts without the need for an additional file format plug-in.

[Aaron]

Thanks.

In addition to Michael's comments that SQL is a built in File Format, BC3's trial is fully featured with only the days-of-use time limit. You do not need to uninstall BC2. That way you can trial BC3 while still keeping BC2 on your system for day to day use.

http://www.scootersoftware.com/download.php

Please let us know if you have any questions.

[knut]

Hello,

Beyond Compare has never had the ability to connect to a SQL Server database and compare live SQL Server objects.

I found a way to compare live SQL Server objects/datasets.

The main idea is simple:
You can define file formats to compare files after a conversion.
You need only a 'conversion' doing a database selection and store the result in a file.

I added a ruby solution in my gem bc3 (version 0.2.0).
To use it, you need ruby with gems bc3, log4r and sequel. The example and unit test requires also sqlite.

The gem contains file format definitions to use the tool (bc3/examples_db_connect/BCSettings_db4bc3.bcpkg).

The files to compare contains a definition of the database connection and selection (and of the result you want).

See the example in the screenshot. The files to compare looks like this:

  adapter: sqlite
  db: ./test.db
  tab: languages
  show_sql: true
  data: csv_tab
  headline: true
  filter: 
    - :key: [ 1, 2, 3, 4, 5, 6, 7, 8 ]

(the 2nd configuration file does not contain the filter).

More details at http://rubypla.net/bc3/0.2.0/ ( deeplink: here and here)


One little feature request: Could you add an option in "file format->conversions" to set the execution path? (Default BC3-folder, optional another fixed path or source-file-path)

[knut]

addendum:

I created in meantime a windows exe-file. You can use this exe as a converter.
http://rubypla.net/bc3/index.html
http://rubypla.net/bc3/bc3_db_compare.exe

The exe worked for me with sqlite (ado is not tested up to now). I'm not sure if it will work without a ruby/sequel installation, but I hope it will

[Gurce]

This looks really handy

Hi Knut,

Your plug-in tool for BC3 looks really handy. I want to give it a try, but I'm unfamiliar with ruby/gem. Is it a big learning curve for me to comprehend it and get this working?

Or perhaps is the .exe file you created independant of ruby, and I just need that?

Well, I'll keep reading and tinkering, maybe I'll figure it out, but if you happen to read this and have any tips, I'd be happy to hear it.

Cheers,
Gurce

[Gurce]

Another way

Ok, I've been trying bits and pieces on the web. Freeware tools like DataComparisons and DBComparer weren't helpful to me, since they seemed to focus on the sql-schema and not the data within the tables.

But perhaps I should try and explain my situation a bit, so that it will clarify what sort of solution I've been looking for.

I have a database that is getting altered by an application that I am un-familiar with. I would like to learn how this program is altering the database by:

• taking 'BEFORE' snapshot of all table-data
• using the program which alters the db
• take an 'AFTER' snapshot of all table-data
• Use BC3 to compare the two table-data snapshots

I thought there at-least ought to be a way to dump this table-data into a text-file and let BeyondCompare compare the text files.

Yeah, this web-site had a good example of that:
http://beyondrelational.com/blogs/ma...text-file.aspx

It iterates through each table in your database. I ran into a few error messages with it initially, such as:

Msg 15281, Level 16, State 1, Server GURCESGCDEV, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshe
ll' because this component is turned off as part of the security configuration f
or this server. A system administrator can enable the use of 'xp_cmdshell' by us
ing sp_configure. For more information about enabling 'xp_cmdshell', see "Surfac
e Area Configuration" in SQL Server Books Online.

Ok, this web-site explained how to fix this:
http://myitforum.com/cs2/blogs/dhite...esolution.aspx

Basically, I ran SQLCMD from a console and typed in the suggested lines:

C:\Documents and Settings\vets>sqlcmd
1> Exec Master.dbo.Sp_Configure 'Show Advanced Options',1
2> Reconfigure
3> Exec Master.dbo.Sp_Configure 'XP_CmdShell',1
4> Reconfigure
5> GO

The script would then execute, but I didn't see any text files outputted. It turned out I needed an extra "-T" parameter in the 'bcp' call.

Then it worked, I saw lots of text files dumped into a hard-coded "C:\data" path. Yeah, that's ok, but I want to pass a subfolder-parameter into this table-dumping script, so that I can have my 'BEFORE' and 'AFTER' sub-folders to compare with BC3. So I made some modifications to the sql-script on that web-site and I've got the following:

"dump.sql"

USE SMGC    /* <------- !!!!REPLACE THIS WITH YOUR DB NAME!!!!! */

declare @tables table(table_name varchar(100))
 
insert into @tables
select name from sysobjects where xtype ='u'
 
declare @table_name varchar(1000)
 
select @table_name=min(table_name)from @tables

Declare @str varchar(1000)
set @str = 'Exec Master..xp_Cmdshell ''md $(folder)'''
SELECT @str
Exec(@str)

 while @table_name>''
 Begin
     set @str='Exec Master..xp_Cmdshell ''bcp "Select * from '+db_name()+'..'+@table_name+'" queryout "$(folder)\'+@table_name+'.txt" -T -c'''
     SELECT @str
     Exec(@str)
     select
         @table_name=min(table_name)
     from
         @tables
     where
         table_name>@table_name
 End

And now this means I can do the following:

• sqlcmd -i dump.sql -v folder="%CD%\dump_folder_1_before"
• run the program which alters the database
• sqlcmd -i dump.sql -v folder="%CD%\dump_folder_2_after"
• Compare "dump_folder_1_before" to "dump_folder_2_after" with Beyond Compare and see what has changed.

Yippee! That works, I'm content with this way But then again, if anyone knows of a better way, I'd be glad to hear it.

Addendum:
As an extra convenience, I've made a batch file that requests the dump sub-folder name from the user, and then executes the sqlcmd statement for you.

"dump.bat"

@ECHO OFF
ECHO Type in a dump-directory name:

SET FOLDER=

> usermessage.vbs ECHO WScript.Echo InputBox("Dump tables to sub-folder name?", "Dump sub-folder name", "dump1")

FOR /F "tokens=*" %%A IN ('CSCRIPT.EXE //NoLogo usermessage.vbs') DO SET FOLDER=%%A

DEL usermessage.vbs

IF "%FOLDER%"=="" GOTO END

@ECHO ON
sqlcmd -i dump.sql -v folder="%CD%\%FOLDER%"

:END

So now it's just a matter of:

• dbl-click the "dump.bat" file in explorer
• In the pop-up inputbox that appears, type the dump sub-folder name
• the batch file with then call sqlcmd to dump all the tables into your desired sub-folder

PS. My thanks to these web-pages for helping me figure out the intricacies of batch file input and 'if' statements:
http://www.robvanderwoude.com/usermessages.php
http://www.robvanderwoude.com/if.php

[jhewitt]

Do you know if there is a mySQL plugin for BC?

[Chris]

Sorry, BC doesn't have a mySQL plug-in. To compare data from mySQL, you'll have to dump it to a text file (.sql, .csv, etc), then open it in the Text Compare (.sql) or Data Compare (.csv).

[crashnburn]

Did someone make more headway in this direction for comparing DB's - Scheme or Data?

[Aaron]

Hello,

Directly connecting to DB's is still not something we support. You need to Export to a local file first, then we can compare that export. It looks like you also found this thread, which may have some tools for helping compare the local files:
http://www.scootersoftware.com/vbull...r-SQLite-files