compare sql serve db against SSMS generated script file sql

Forum for users of SQL Compare schema synchronization utility

Moderators: JonathanWatts, Chris Auckland, David Atkinson

compare sql serve db against SSMS generated script file sql

Postby robin banks » Wed Nov 24, 2010 4:43 pm

I have generated a .sql file of all db objects using SSMS-Tasks-Generate Scripts. How can I compare that against a live SQL Server database instance? 2008 R2 Express.
robin banks
 
Posts: 4
Joined: Wed Nov 24, 2010 4:40 pm

Postby David Atkinson » Wed Nov 24, 2010 5:08 pm

This is not officially supported but should work.

Put your script (or scripts) in a separate folder. Create a new project in SQL Compare 8 Pro with your database to compare on one side and your folder of scripts on the other side (you'll have to pick Source Control and Scripts folder).

You are probably better off ignoring collations in the 'Options' for the project.

Hope this helps. Can I ask what you're trying to achieve?

David Atkinson
Red Gate Software
Last edited by David Atkinson on Wed Nov 24, 2010 5:38 pm, edited 1 time in total.
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby robin banks » Wed Nov 24, 2010 5:37 pm

Worked very nice. Thank you.
robin banks
 
Posts: 4
Joined: Wed Nov 24, 2010 4:40 pm

Postby robin banks » Wed Nov 24, 2010 5:43 pm

What am I trying to achieve?

I generate full db script from SSMS. Save the .sql in Source Safe. Make some changes to dev db. Then I want to gen and diff my dev db script with the Source Safe copy to see what changes to deploy to production. SSMS rearranges the order of the objects in its generate script so I cannot just windiff it too easily (need to edit the file to rearrange).
Compare worked great.
robin banks
 
Posts: 4
Joined: Wed Nov 24, 2010 4:40 pm

Postby robin banks » Wed Nov 24, 2010 5:46 pm

BTW: it did import date and time as [sys].[date], [sys].[time]
robin banks
 
Posts: 4
Joined: Wed Nov 24, 2010 4:40 pm

Postby David Atkinson » Wed Nov 24, 2010 5:48 pm

Glad to hear that this worked for you.

We're actually adding VSS support to SQL Source Control. This will be in beta next week. Is this something you'd like to try as an alternative to your current process. the advantage is that this would be fully integrated into SSMS and you'd just need to click on 'Commit' once you've changed an object in SSMS, rather than having to script out each time. To see the differences between your Dev DB and source control you can simply visit the Commit tab and review the differences.

If you've not seen the tool, it's described here:
http://www.red-gate.com/products/SQL_Source_Control/

Let me know if you're interested and I'll put you on the notification list.

David
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

date shows as [sys].[date]

Postby smiller » Wed Jan 12, 2011 10:39 pm

I generated a table script from SQL Server 2008 and compared to a snapshot. A field is defined as DATE in the script and DB, but shows as [sys].[date] in the script in the comparision report.

How can the conversion to [sys].[date] be prevented, or ignored when generating the comparision report?

Thanks.
smiller
 
Posts: 2
Joined: Thu Jan 22, 2009 11:32 pm

Re: date shows as [sys].[date]

Postby ccurcillo » Wed Sep 07, 2011 11:47 pm

smiller wrote:I generated a table script from SQL Server 2008 and compared to a snapshot. A field is defined as DATE in the script and DB, but shows as [sys].[date] in the script in the comparision report.

How can the conversion to [sys].[date] be prevented, or ignored when generating the comparision report?

Thanks.


...having same issue. any solution?
ccurcillo
 
Posts: 2
Joined: Wed Sep 07, 2011 11:46 pm

Re: date shows as [sys].[date]

Postby ccurcillo » Thu Sep 08, 2011 3:37 pm

ccurcillo wrote:
smiller wrote:I generated a table script from SQL Server 2008 and compared to a snapshot. A field is defined as DATE in the script and DB, but shows as [sys].[date] in the script in the comparision report.

How can the conversion to [sys].[date] be prevented, or ignored when generating the comparision report?

Thanks.


On your data source screen, select "comparison settings". make sure that the SQL versions are in sync. I am guessing that yours is still set to 2005 (default), which does not know what a "date" data type is.
ccurcillo
 
Posts: 2
Joined: Wed Sep 07, 2011 11:46 pm

sys.date

Postby aspnerd » Tue Mar 27, 2012 7:41 pm

So I've scripted out in 2008 mode in SQL compare, to two folders, but even though the file I'm comparing does not have sys.date when it compares to another folder which only has date it says they are different...

So folder1 has [date] and folder2 has [date] but when it compares it says the file matches but when I look at the file it says folder1 is [date] and folder2 is [sys].[date]...

any ideas what is going on?
aspnerd
 
Posts: 8
Joined: Wed May 04, 2011 5:03 pm
Location: Jacksonville, FL


Return to SQL Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 1 guest