Red Gate forums: SQL Compare 10

RE: Fulltext Indext Stoplist Options

howarthcd — Wed, 22 May 2013 20:03:57 GMT

Hi Steve

Thanks for your reply, however this isn't a feature request - I believe that it is a bug.

The point is that although the fulltext index definition differs there is no relevant sync statement included in the synchronisation script.

Chris

RE: Fulltext Indext Stoplist Options

stanori — Wed, 22 May 2013 19:18:17 GMT

Hi howarthcd,

Thanks for your post regarding SQL Compare. Unfortunately, there is not a way to present this difference at the same object level as a table. SQL Compare treats this difference at the table level, and will generate the appropriate change in the resulting deployment script.

I would suggest posting your feature request in the following forum so that others can vote as well:
http://redgate.uservoice.com/forums/141379-sql-compare-feature-suggestions

Best Regards,
Steve

Use migration script with Snapshots

rickjr82 — Wed, 22 May 2013 14:05:32 GMT

We have a process where we are creating snapshots based on check-ins to SQL source control and then picking a version to deploy and using sql compare to deploy that snapshot to the target database.

It appears that deploying from a snapshot doesn't pick up any migration scripts- am I missing anything?

RE: Found new bug when using "Add object existence check" option

JohnnyT — Tue, 21 May 2013 20:48:58 GMT

Here is a simple scenario for you to reproduce this error. Hope this helps.

Code:

-- Create identical TestA and TestB databases
create database TestA ;
go
use TestA ;
go
create default [dbo].[empty_number] AS 0 ;
go
create table dbo.Table1
(
column1 char(10) not null,
column2 int not null
) ;

execute sp_bindefault N'dbo.empty_number', N'dbo.Table1.column2' ;
go

create database TestB ;
go
use TestB ;
go
create default [dbo].[empty_number] AS 0 ;
go
create table dbo.Table1
(
column1 char(10) not null,
column2 int not null
) ;

execute sp_bindefault N'dbo.empty_number', N'dbo.Table1.column2' ;
go

-- Now drop the "column2" in Table1 in the TestA database.
use TestA ;
go
execute sp_unbindefault N'dbo.Table1.column2' ;
go
alter table dbo.Table1 drop column column2 ;
go

-- Now use SQL Compare 10 to generate a script to make TestB match TestA. Be sure to
-- check the "Add object existence checks" option before generating the script.
-- Run the generated script on the TestB database and see the error.

RE: Command line automation does not sync extended properties

james.billings — Tue, 21 May 2013 11:38:06 GMT

DB level extended properties require DB Owner permission, so I'd check that first.

There's a Compare option to ignore the Source Control properties, but this is not set by default and you don't seem to be specifying it as far as I can tell.

RE: Command line automation does not sync extended properties

ZHCS — Tue, 21 May 2013 11:35:39 GMT

Hi,

Many thanks for your response.

- these are the extended properties which SQL Source Control adds
- No, i am running the command line with different credentials. What permissions would i need to be able to synchronize the extended properties?
- we have SQL Compare 10
- /scr1:"%system.teamcity.build.checkoutDir%" /revision1:HEAD /server2:XXXXX /db2:XXXXX /Include:identical /o:Default /Report:XXXXX /ReportType:Simple /ScriptFile:XXXXX /Force /logLevel:Verbose /sync

RE: Found new bug when using "Add object existence check" option

JohnnyT — Mon, 20 May 2013 20:33:55 GMT

Brian Donahue wrote:

Hello,

Thanks for supplying all of the output from SQL Compare... would it be possible to script a scenario that caused this problem to happen? I can see where the problem occurs in your deployment but I can't figure out how to reproduce it so I can submit a proper bug report.

I'll see what I can do. It may take some time for me to set this up.

RE: Command line automation does not sync extended properties

james.billings — Mon, 20 May 2013 17:18:49 GMT

Thanks for your post.

Based on an initial test here, DB level extended properties should get scripted OK, so I need to check a few things:

- are these extended properties the ones that SQL Source Control adds, or your own ones?
- are you running the command line with the same credentials as when you tried it through the GUI?
- what version of SQL Compare are you on?
- what *exact* command line are you running? Can you post an example? For instance I was able to script the differences in extended properties with pretty much no options:

Code:

sqlcompare.exe /db1:database1 /db2:database2 /sf:d:\sync.sql

RE: can't exclude identical objects?!

howarthcd — Mon, 20 May 2013 14:22:16 GMT

Hi Red-Gate

It's been four months since we initially reported the filter bug (for which you created SC-6165).

Are you able to confirm when this issue will be resolved?

Thanks
Chris

Fulltext Indext Stoplist Options

howarthcd — Mon, 20 May 2013 14:05:24 GMT

When comparing a table script file containing a full text index definition with another script file with an equivalent fulltext definition but where the STOPLIST option is included, then SQL Compare treats the scripts as being the same even though the differencing pane highlights the differences between the scripts.

e.g.

1st script contains this:

CREATE FULLTEXT INDEX ON [dbo].[MyTable] KEY INDEX [PK_MyTable] ON [ftCatalog] WITH STOPLIST OFF
GO

2nd script contains this:

CREATE FULLTEXT INDEX ON [dbo].[MyTable] KEY INDEX [PK_MyTable] ON [ftCatalog]
GO

Is there a project option that can be specified so that this difference is raised at the table level? Full-text indexing is not selected under the 'Ignore' project option.

SQL Compare 10.3.8.406 Professional Edition running on Windows 7 64-bit.

Thanks
Chris

RE: Incorrect syntax near ')'

Anu Deshpande — Mon, 20 May 2013 08:51:53 GMT

We are glad that it is working now!

RE: Rollback scripts

John Palmer — Sat, 18 May 2013 17:15:32 GMT

EDIT: I'm sorry, after rereading your question, I now realize you wanted to be able to reverse a migration script. Sorry I missed that detail initially. I'll leave my somewhat out of context answer in the hopes that it may prove useful to someone!
--------------------------------------

As you noted, this isn't possible for all types of changes, but I've taken to using this technique with good success:

your_deploy_name.date.deploy.sql

Do not change any selections!

your_deploy_name.date.rollback.sql

You now have two script files, one to deploy and one to backout.

As an extra verification step, I have TeamCity running locally and use it and various RedGate tools to:

RE: Incorrect syntax near ')'

kevinw — Fri, 17 May 2013 13:00:09 GMT

Sorry. Ignore me. It was caused by a bad merge that removed the TransactionWeek column.

Incorrect syntax near ')'

kevinw — Fri, 17 May 2013 12:18:42 GMT

Hi,

I'm using SQL Compare 10.3.8.406. I'm comparing a TFS source controlled script folder to a database.

From the command line or via the UI it appears that SQL compare is not reading the content of a script file correctly resulting in a badly formed migration script.

The script file contains this :

CREATE TABLE [dbo].[CategorySales]
(
[CategorySalesId] [int] NOT NULL IDENTITY(1, 1),
[TransactionDate] [datetime] NOT NULL,
[LocationId] [int] NOT NULL,
[CategoryId] [int] NOT NULL,
[TransactionYear] [int] NOT NULL,
[TransactionMonth] [int] NOT NULL,
[TotalValue] [money] NOT NULL,
[TotalCost] [money] NOT NULL CONSTRAINT [DF_CategorySales_TotalCost] DEFAULT ((0)),
[TotalMargin] [money] NOT NULL CONSTRAINT [DF_CategorySales_TotalMargin] DEFAULT ((0)),
[SalesTax] [money] NOT NULL CONSTRAINT [DF_CategorySales_SalesTax] DEFAULT ((0))
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [CU_CategorySales_TransDateLocationIdCategoryId] ON [dbo].[CategorySales] ([TransactionDate], [LocationId], [CategoryId]) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_CategorySales_TransactionMonth] ON [dbo].[CategorySales] ([TransactionMonth]) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_CategorySales_TransactionWeek] ON [dbo].[CategorySales] ([TransactionWeek]) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_CategorySales_TransactionYear] ON [dbo].[CategorySales] ([TransactionYear]) ON [PRIMARY]

ALTER TABLE [dbo].[CategorySales] ADD CONSTRAINT [PK_CategorySales] PRIMARY KEY NONCLUSTERED ([CategorySalesId]) ON [PRIMARY]

GO

But when I point SQL compare to the script folder as the source and view the differences it appears like this :

-- Columns

CREATE TABLE [dbo].[CategorySales]
(
[CategorySalesId] [int] NOT NULL IDENTITY(1, 1),
[TransactionDate] [datetime] NOT NULL,
[LocationId] [int] NOT NULL,
[CategoryId] [int] NOT NULL,
[TransactionYear] [int] NOT NULL,
[TransactionMonth] [int] NOT NULL,
[TotalValue] [money] NOT NULL,
[TotalCost] [money] NOT NULL CONSTRAINT [DF_CategorySales_TotalCost] DEFAULT ((0)),
[TotalMargin] [money] NOT NULL CONSTRAINT [DF_CategorySales_TotalMargin] DEFAULT ((0)),
[SalesTax] [money] NOT NULL CONSTRAINT [DF_CategorySales_SalesTax] DEFAULT ((0))
)
GO
-- Constraints and Indexes

ALTER TABLE [dbo].[CategorySales] ADD CONSTRAINT [PK_CategorySales] PRIMARY KEY NONCLUSTERED ([CategorySalesId])
GO
CREATE NONCLUSTERED INDEX [IX_CategorySales_TransactionWeek] ON [dbo].[CategorySales])
GO
CREATE UNIQUE CLUSTERED INDEX [CU_CategorySales_TransDateLocationIdCategoryId] ON [dbo].[CategorySales] ([TransactionDate], [LocationId], [CategoryId])
GO
CREATE NONCLUSTERED INDEX [IX_CategorySales_TransactionMonth] ON [dbo].[CategorySales] ([TransactionMonth])
GO
CREATE NONCLUSTERED INDEX [IX_CategorySales_TransactionYear] ON [dbo].[CategorySales] ([TransactionYear])
GO

It's missed off some of the sql where it's creating the indexes. This results in the migration script having incorrect syntax.

It looks like a bug in SQL compare to me. How can I resolve this?

Thanks
Kevin

Command line automation does not sync extended properties

ZHCS — Fri, 17 May 2013 11:36:03 GMT

We are using SQLCompare.exe for automated deployment of our databases. We realized that the deployment script created by sqlcompare.exe doesnt not synchronize extended properties of the databases. When i try to create the deployment script from SQL Compare GUI, the resulting script does include part synchronizing the extended properties. The options in GUI and for command line set up in identical way. We are not using Ignore extended properties option. Any ideas?