Red Gate forums :: View topic - compare sql serve db against SSMS generated script file sql
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare Previous Versions
SQL Compare Previous Versions forum

compare sql serve db against SSMS generated script file sql

Search in SQL Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
robin banks



Joined: 24 Nov 2010
Posts: 4

PostPosted: Wed Nov 24, 2010 4:43 pm    Post subject: compare sql serve db against SSMS generated script file sql Reply with quote

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.
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1120
Location: Twitter: @dtabase

PostPosted: Wed Nov 24, 2010 5:08 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
robin banks



Joined: 24 Nov 2010
Posts: 4

PostPosted: Wed Nov 24, 2010 5:37 pm    Post subject: Reply with quote

Worked very nice. Thank you.
Back to top
View user's profile Send private message
robin banks



Joined: 24 Nov 2010
Posts: 4

PostPosted: Wed Nov 24, 2010 5:43 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
robin banks



Joined: 24 Nov 2010
Posts: 4

PostPosted: Wed Nov 24, 2010 5:46 pm    Post subject: Reply with quote

BTW: it did import date and time as [sys].[date], [sys].[time]
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1120
Location: Twitter: @dtabase

PostPosted: Wed Nov 24, 2010 5:48 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
smiller



Joined: 22 Jan 2009
Posts: 2

PostPosted: Wed Jan 12, 2011 10:39 pm    Post subject: date shows as [sys].[date] Reply with quote

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.
Back to top
View user's profile Send private message
ccurcillo



Joined: 07 Sep 2011
Posts: 2

PostPosted: Wed Sep 07, 2011 11:47 pm    Post subject: Re: date shows as [sys].[date] Reply with quote

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?
Back to top
View user's profile Send private message
ccurcillo



Joined: 07 Sep 2011
Posts: 2

PostPosted: Thu Sep 08, 2011 3:37 pm    Post subject: Re: date shows as [sys].[date] Reply with quote

[quote="ccurcillo"]
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.
Back to top
View user's profile Send private message
aspnerd



Joined: 04 May 2011
Posts: 8
Location: Jacksonville, FL

PostPosted: Tue Mar 27, 2012 7:41 pm    Post subject: sys.date Reply with quote

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?
Back to top
View user's profile Send private message AIM Address Yahoo Messenger MSN Messenger
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group