Page 1 of 2 12 LastLast
Results 1 to 10 of 11
  1. #1
    Join Date
    May 2005
    Location
    Germany
    Posts
    43

    Default Compare MS SQL Server data bases

    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?
    Stummfilm - ich bin dabei!

  2. #2
    Join Date
    Oct 2007
    Location
    Pennsylvania
    Posts
    1,772

    Default

    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.
    BC v4.0.7 build 19761
    ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

  3. #3
    Join Date
    Oct 2007
    Location
    Madison, WI
    Posts
    11,384

    Default

    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.
    Aaron P Scooter Software

  4. #4
    Join Date
    May 2005
    Location
    Germany
    Posts
    43

    Default

    Hello,
    Quote Originally Posted by Michael Bulgrien View Post
    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:
    Code:
      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)
    Stummfilm - ich bin dabei!

  5. #5
    Join Date
    May 2005
    Location
    Germany
    Posts
    43

    Default

    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
    Stummfilm - ich bin dabei!

  6. #6
    Join Date
    Jan 2012
    Posts
    22

    Default 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

  7. #7
    Join Date
    Jan 2012
    Posts
    22

    Default 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:

    Code:
    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:
    Code:
    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"
    Code:
    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"
    Code:
    @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
    Last edited by Gurce; 27-Jan-2012 at 06:21 PM.

  8. #8
    Join Date
    Jan 2012
    Posts
    1

    Default

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

  9. #9
    Join Date
    Oct 2007
    Location
    Madison, WI
    Posts
    4,584

    Default

    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).
    Chris K Scooter Software

  10. #10
    Join Date
    Oct 2011
    Posts
    31

    Default

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •