Following an upgrade to SQL Server 2012, our shop's Schema Compare tool (Redgate SQL Compare) is no longer supporting our environment.We are starting to evaluate various 3rd party products to find a possible replacement, and would be interested in what products are favored by other IT shops who do a lot of database work.
Our shop is split about 75% SQL Server, 20% Oracle, and 5% I'll call other. Ideally a product would support SQL Server and Oracle, but our focus is on SQL server right now. On that platform we have ~50 servers spread across DevUATProd environments.In basic terms, we need a tool that can identify schema differences between DBs and generate synchronization scripts to support deploys between environments. Real-time synchronization is not a requirement (nor desirable), as deploys are a gated DBA function in our shop.
I am looking for some SQL Scripts/tool to compare the two sql database and generate the difference results into Excel file. I am not looking for Sync/change scripts.
Example:
Result Type Desc ----------------------- Col1 Column Added Table1 Table Removed
I have a database project where objects have been pulled in from the database using schema compare.
Unfortunately CDC tables which are referenced in stored procedures on the database have not been pulled in by the schema compare & hence I cannot build the project and deploy changes back to the database.
Hey all,I am currently researching ways to compare databases via an XSD schema.I wrote a small app that creates a dataset from a database and exportsthat dataset to XSD. This gives me an XSD file with tables andrelationships representing the entire database.At this point, I am trying to find ways to compare these schemas. Doesanybody know of a way to do this easily and to record differences ifthere are any?Also, any information on comparing databases using any method would begreatly appreciated.Thanks,--Shock
What software can compare two text files? They are contains about 100k data generated from Visual Basic 6.0 program and MS SQL 2000. I have many files to compare daily.
I've searched quite a bit, and have found several leads on schema, stored procedure, and database contents comparison scripts and tools.
I'm now looking for recommendations on which ones are best, easiest:
ObjCompare.exe sb_ABCompareDb.sql sp_db_comp.sql
There's a mythical script from Andrew Z <mumble> that Mike Hotek talks about...
There's a DBCompare on the Back Office Resource Kit 2 CD, which of course is not in the umpteen MSDN CDs :-(
There's some *other* command line dbcompare, or maybe db_compare.
There's a DBA Compare.
I need to be able to compare divergent schemas from two developers to integrate their changes, so need schema and stored procedures compared only, and would also like to have something to compare staging servers and production servers.
Leads on other choices also welcome. I'd be happy to summarize and post, if warranted.
In the process of purging data to history tables, we wanted to make sure that no schema changes have been done to the main or the history table. So to ensure identical schemas, we use this function:
ALTER FUNCTION dbo.fnCompareTableSchema ( @t1Name NVARCHAR(257) ,@t2Name NVARCHAR(257) ) RETURNS BIT AS /* Compares the schema of 2 tables If the schema is different RETURNS 0 If the schema is identical between the two table, RETURNS 1 NOTE: system tables or non-existant tables that are NOT in INFORMATION_SCHEMA views will compare equal (RETURNS 1) ================================================================================================================== SAMPLE USAGE: DECLARE @schemaOK BIT SELECT @schemaOK = dbo.fnCompareTableSchema('dbo.table1','dbo.table2')
IF @schemaOK = 1 PRINT 'TABLE SCHEMA IDENTICAL' ELSE PRINT 'TABLE SCHEMA DIFFERENT' ================================================================================================================== */ BEGIN IF @t1Name = @t2Name RETURN 1
-- check if schema is different IF EXISTS ( SELECT* FROM ( SELECTCOLUMN_NAME, ORDINAL_POSITION, DATA_TYPE , COLUMN_DEFAULT, IS_NULLABLE , CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE , COLLATION_NAME FROMINFORMATION_SCHEMA.COLUMNS WHERETABLE_SCHEMA = COALESCE(PARSENAME(@t1Name,2),'dbo') AND TABLE_NAME = PARSENAME(@t1Name,1) UNION ALL SELECTCOLUMN_NAME, ORDINAL_POSITION, DATA_TYPE , COLUMN_DEFAULT, IS_NULLABLE , CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE , COLLATION_NAME FROMINFORMATION_SCHEMA.COLUMNS WHERETABLE_SCHEMA = COALESCE(PARSENAME(@t2Name,2),'dbo') AND TABLE_NAME = PARSENAME(@t2Name,1) ) U GROUP BY COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE HAVING COUNT(*) <> 2 ) RETURN 0
guys...do you have any recommendations for a tool that compares specific MS sql databases including data comparisons?
I typically develop locally, then deploy remotely.
it's getting to be quite a pain to not only remember which schema objects to update on the remote host, but the data elements that need to be keyed in as well.
are there any shareware tools out there you'd recommend? (I don't have a great budget as this is coming out of MY pocket and not my companies.)
I've been testing a variety of database schema migration tools. Ourcompany purchased Embarcadero Change Manager a while ago but we'vebeen less than satisfied with the results.We are looking for a tool that can compare a source/developer databasewith a target/client database and then make the necessary changes.The tool will need to update stored procedures, tables, indexes,constraints, etc. The tool will also need to make the changes in thecorrect order based on dependencies and relationships.Anyone have any recommendations?
I am running SQL Server 2008 Enterprise edition and I was asked for a way to export the database schema (Tables and Columns and their connections to each other) to HTML. I tried googling this, but all I found was paid tools that offer this and I was wondering if there is anything integrated in the SQL server or a free tool that provides this functionality?
Just wondering if there are any tools available for SQL Server 2005 which allow the comparison and scripting of data and schema between two databases. This is so that I can migrate between Dev, QA, and Live easily.
We are getting regular SSDT/VS crashes when doing schema compares (source: project, target: server). Sometimes the compare succeeds but around 80% of the time we get a crash.
We are using VS2013 Update 3 and the latest version of SSDT.
Application : devenv.exe Version du Framework : v4.0.30319 Description : le processus a été arrêté en raison d'une exception non gérée. Informations sur l'exception : System.Runtime.InteropServices.COMException Pile : à System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32, IntPtr)
I've downloaded and installed the latest SQL Server Data Tools for VS 2012. Is there anyway to export the results of the schema comparison into a report in CSV/Html format? I understand that it can generate the sql diff script, but I want a readable report that I can use to show to people.
I just recently updated to SSDT 12.0.50512.0 using Visual Studio 2013 Ultimate. I typically use SSDT Schema Compare to synchronize my schema across multiple databases and different environments. After updating i encountered a major bug while updating our production schema.Typically during schema compare, the compare will prompt me to drop users and user roles from the database as they are not present in the project. I will exclude these so they database users and their roles aren't affected. After the update to SSDT I noticed that schema compare was only prompting me to drop the User, but didn't show anything about the user's roles. Not thinking much of it I went through my usual task of updating all the production databases. I soon found out that this did in fact remove the user roles even though it showed NOTHING in the schema compare UI indicating it would do so.
GO PRINT N'Dropping <unnamed>...';
GO EXECUTE sp_droprolemember @rolename = N'db_datareader', @membername = N'dbuser';
GO PRINT N'Dropping <unnamed>...';
GO EXECUTE sp_droprolemember @rolename = N'db_datawriter', @membername = N'dbuser';
You could say this is partially my fault for not checking the generated script before running it, but after months of this routine task I've never had an issue until this update.i'm not seeing the changes that will happen to my user roles in the schema compare UI?
I'm trying to automate comparing the dacpacs we're generating from out builds against our production server to monitor drift.However, we use scripting variables to define cross database references. The schema compare is showing up all the objects which reference the other database via scripting variable as being different to what is on the server i.e. it reports a change between a table referenced as [$(db)].dbo.Table in the dacpac and db.dbo.Table in the target database.
When I do a comparison in Visual studio between the project and the target database the variables seem to be appropriately replaced and the differences don't show. Obviously this is using a project instead of a dacpac but I'm hoping I can get the dacpac/db compare to behave similarly to the project/db comparison.
Is there a way to define what the scripting variables should resolve to when I run the comparison via msbuild?
Edit: I would prefer not to deploy the dacpac and diff the deployed db against the target database but if that's the only way....
I am importing an existing database into a Visual Studio SQL Server Database project using Schema Compare. The Schema Compare works fine and I updated my project successfully. However, the project won't build because of the existence of 3-part names in some objects:
E.g. I import the database MyDB into a new project MyDB using Schema Compare. The database contains views with queries like this:
SELECT col1, col2. col3 FROM MyDB.dbo.MyTable
When trying to build this project I get errors like:
Error 39 SQL71561: View: [dbo].[vw_MyView] has an unresolved reference to object [MyDB].[dbo].[MyTable]. C:UsersRedirectionrittg2DocumentsVisual Studio 2012ProjectsMySolutionMyDBdboViewsvw_MyView_1.sql
but of course this is a bogus message since the view can clearly read from an object in the same database whether using 2-, 3-part naming (or 4-part naming for that matter).
How can I resolve these errors without editing the objects before running Schema Compare? (there are hundreds of them).
I have created Database projects in VS 2013 using SSDT. I have been mostly successful in creating and building the projects without any errors/warnings.
However for one of the databases in the project, when i do schema compare to apply the changes from a SQL Server Database to a Database Project in VS, code changes are not applied to the database project.
After i select the Update option in Schema compare window, I'm getting the following message
"Target update complete. Press Compere to refresh the comparison."
Even tough the message implies that target database is updated successfully, I do not see the objects i selected in schema compare being added to the target database project.
I see the following warning
"Target update: Could not update script for element 'dbo'"
I have 9 Database projects in the Solution and I'm able to apply changes to 8 of the database projects through schema compare successfully. I get the same warning after schema compare for all database projects.
I have same project level setting for all database projects in the solution. I'm using Visual Studio 2013 Premium Update 5 SQL Server Data Tools 12.0.41012.0
SQL Server 2012 Data Tools was working fine for me but something must've changed, now every time I try to create a new SSIS project I get:
The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT)).
When I try to open an existing project I get:
exception has been thrown by the target of an invocation
external component has thrown an exception (SSISUpgrade)
The issue seems to only arise with SSIS projects.I have already uninstalled SQL Server 2012 and reinstalled it and that didn't work.I tried to install Visual Studio 2012 Data Tools with BI and that also crashes when I try to create an SSIS project.Output of SQL Server SELECT @@VERSION is:
Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
SQL Data Tools page info:
Microsoft SQL Server Integration Services Designer Version 11.0.2100.60 Microsoft Visual Studio 2010 Version 10.0.40219.1 SP1Rel Microsoft .NET Framework Version 4.5.51641 SP1Rel
I have one database with several tables in it (table 1, table2, table3). In each table is two colums (colum1 = a number (201220) and colum2 = a number (0.50). Now, both tables will have rows with the same data in colum 1, but colum two will have different numbers (different prices). My goal is to run a query that will compare both colums in all three tables, take the lower of the three based on colum 2 and spit out the row. Obviously, this would output all rows (around 175k). The point is to create a least cost spreadsheet (csv) file based on evaluating all three tables.
A customer has messed up while moving their databases. After working for a week they found that data is missing in the database.I have two backups, one from the old server and one from the new server today, they have been working in the new one for a week.
I need to compare these two databases and then update the new database with all data that is in the old one but not in the new database. Join the data in the two databases so to say. Both databases are from the same application so they use the same users, schema and so on.
I am trying to reproduce a problem that is probably related to how a particular SQL server is configured. Is there a tool or best practice that is useful to compare all of the configuration settings between two SQL Servers (same version)?
I'm rewriting a huge FOR XML EXPLICIT procedure to use FOR XML PATH, and need to compare previous output to the refactored one, so i didn't mess up XML structure.
The thing is, i'm not sure that SQL Server will always generate exactly same xml **string**, so i'd rather not compare by:
WHERE CAST(@xml_old AS NVARCHAR(MAX)) = CAST(@xml_new AS NVARCHAR(MAX))
nor do i want to manually validate every node, since the generated xml-structure is quite complex.
I am looking for a tool to compare the result sets.
Is there any free tool or Microsoft built in tool that we can use to compare the result sets. Suppose if I change one thing in code and needs to test the am I getting the same result set or not? Instead of doing manually is there any way to compare both result set.
Table A IdName 101Dante 102Henry 103Harold 104Arnold
Table B NumberName 102Dante 107Gilbert 109Harold 110Arnold 106Susan 112Marian
I want the result in table 3 like below, if value exists in Table A and not exists in Table B then the record should enter in table 3 with table name in new column, and vice versa.
Table C Col1Col2 HenryTable A Gilbert Table B Susan Table B Marian Table B
using below logic to get the values from tables..
select t1.columnA , t2.* from table1 t1 join table2 t2 on t2.columnB = t1.columnA
using below script to compare two tables and get the values.
how to get the count of 'Table A' , 'Table B' , 'Table A & Table B' using below script.
Ex: 'Table A' -- 150 'Table B' -- 300 'Table A & Table B' -- 150 SELECT Col1 = ISNULL(a.name,b.name), Col2 = CASE WHEN ISNULL(a.name,'') = '' THEN 'Table B' WHEN ISNULL(b.name,'') = '' THEN 'Table A' ELSE 'Table A & Table B' END FROM #tableA a FULL JOIN #tableB b ON a.name = b.name;
SET NOCOUNT ON; DECLARE @items TABLE (ITEM_ID INT, ITEM_NAME VARCHAR(10)) INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 10,'ITEM 1' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 11,'ITEM 2' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 12,'ITEM 3' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 13,'ITEM 4' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 14,'ITEM 5' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 15,'ITEM 6' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 16,'ITEM 7' INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 17,'ITEM 8' SELECT * FROM @items
-- table with categories
SET NOCOUNT ON; DECLARE @categories TABLE (CAT_ID INT, CAT_NAME VARCHAR(10)) INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 100,'WHITE' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 101,'BLACK' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 102,'BLUE' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 103,'GREEN' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 104,'YELLOW' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 105,'CIRCLE' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 106,'SQUARE' INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 107,'TRIANGLE' SELECT * FROM @categories
--table where categories are assigned to master categories
SET NOCOUNT ON; DECLARE @master_categories TABLE (MASTERCAT_ID INT, CAT_ID INT) INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,100 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,101 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,102 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,103 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,104 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,105 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,106 INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,107 SELECT * FROM @master_categories
-- items-categories assignment table
SET NOCOUNT ON; DECLARE @item_categories TABLE (CAT_ID INT, ITEM_ID INT) INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,10 INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,10 INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,11 INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,11
[code]....
So now I need to query the table @t4 in and to determine the items that are assigned to category 'WHITE' in master category 1 and to 'CIRCLE' in master category 2.The important thing is to return items that are assigned solely to 'WHITE' in master cat 1 and solely to 'CIRCLE' in master cat 2.In the above example it would be only the ITEM 1 (id=10) that is returned:
1. ITEM 2 (id=11) is not returned because it has the assignment to category 'SQUARE' in master cat 2 additionally
2. ITEM 3 (id=12) is not returned because it has the assignment to category 'BLACK' in master cat 1 additionally
3. ITEM 4 (id=13) is not returned as it does not have assignment to category 'CIRCLE' in master cat 2 but only to 'WHITE' in master cat 1
3. ITEM 5 (id=14) is not returned as it does not have assignment to category 'WHITE' in master cat 1 but only to 'CIRCLE' in master cat 2
I would like to build a report with nice functionalities like filter, sorting, drill-down, something like a PowerPivot Table, but with some layout/design/format capabilities. I would also want to publish the report, refresh it let´s say once a week, notify users when a new version is available, etc.
If I use PowerPivot, then I am not able to customized the report or to mix data from different sources in one table.
If I convert the cells of the PowerPivot table to workbook formulas I lose the filter, sorting, etc functionalities.
I still have to try using Reporting Services, but I think that always something is missing.
