Invalid change scripts for database schemas?

jcavaliere

Hi All,

We recently started incorporating database schemas in SQL 2005 and are looking to migrate some DDL changes to the production servers. We used SQL compare with MS SQL 2000 which worked beautifully, but now with SQL 2K5 it creates invalid change scripts for any table not in the default "dbo" schema. Is there any way around this?


Thanks,

-Jason

JonathanWatts

Hi Jason,

Sorry about the problems you are encountering, would it be possible to discribe in what way are the scripts are invalid?

Thanks,

Jonathan

jcavaliere

No Problem - we just want to make it all work

Here's an example, but it happens with just about all of the foreign keys and check constraints if the schema is not dbo:

ALTER TABLE [RxOrder].[LineItem] WITH CHECK ADD CONSTRAINT RxOrder.LineItem_Eye_Chk] CHECK (([Eye]='L' OR [Eye]='R'))


The RxOrder.LineItem_Eye_Chk] throws an error when run becuase of the second set of [] brackes in the name.

Michelle T

Having done a couple of experiments with check constraints, I'd quite like to know how you're managing to get the constraint name schema-qualified in the first place- could you give us an example of what you think that ALTER TABLE ADD CONSTRAINT statement should look like / the creation script that originally made this constraint?

I've tried:

Just putting the schema name and a dot before the constraint name (SQL Server tells me I have a syntax error at the dot)

Logging on as someone with a default schema other than dbo and giving no schema information (SQL Server doesn't schema-qualify the constraint name)

Putting one set of square brackets around it (everything just treats the . as another character, including SQL Compare)

Creating the table in the same batch as the CREATE SCHEMA statement (this only schema-qualifies the table, not the constraint)

And I've tried these methods with the constraint as an inline constraint, as a table constraint, and as a constraint introduced by ALTER TABLE ADD CONSTRAINT, with no better results.

----

Michelle Taylor - michelle.taylor@red-gate.com
SQL Compare Test Team