SQL CI drops all constraint while deploying a build
Andrei Sharafullin
Posts: 2
Dear Sirs,
I try to migrate out Visual Studio Database project to SQL Source control and Use SQL CI to deploy that. I did find the only option to do that is to deploy a database to SQL server and link it to SQL Source Control from SSMS.
Next step was deploying the project to database using SQL Compare. And it was successful.
Then I run SQL CI from command line to deploy that with MSBuild. First it failed on several function and as I found out it was because of missing AS key word:
CREATE FUNCTION [dbo].[fnSampleFunction] ( @ID AS INT )
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
...
It was not very clear from exception, but what I do not realize is why that happened in SQL CI after successful deployment from SQL Compare (as it seems that SQL CI uses SQL Compare)?
After fixing those issues I got another one. I have a table which uses FILESTREAM and it has a ID column with type "NOT NULL ROWGUIDCOL" and a primary key on that. SQL CI tries to remove that Primary Key and get error from SQL Server that:
A table that has FILESTREAM columns must have a nonnull unique column with the ROWGUIDCOL property. Error executing the following SQL: ALTER TABLE [dbo].[SomeTable] DROP CONSTRAINT [PK_SomeTable].
Could you help with those issues?
Regards,
Andrei Sharafullin.
I try to migrate out Visual Studio Database project to SQL Source control and Use SQL CI to deploy that. I did find the only option to do that is to deploy a database to SQL server and link it to SQL Source Control from SSMS.
Next step was deploying the project to database using SQL Compare. And it was successful.
Then I run SQL CI from command line to deploy that with MSBuild. First it failed on several function and as I found out it was because of missing AS key word:
CREATE FUNCTION [dbo].[fnSampleFunction] ( @ID AS INT )
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
...
It was not very clear from exception, but what I do not realize is why that happened in SQL CI after successful deployment from SQL Compare (as it seems that SQL CI uses SQL Compare)?
After fixing those issues I got another one. I have a table which uses FILESTREAM and it has a ID column with type "NOT NULL ROWGUIDCOL" and a primary key on that. SQL CI tries to remove that Primary Key and get error from SQL Server that:
A table that has FILESTREAM columns must have a nonnull unique column with the ROWGUIDCOL property. Error executing the following SQL: ALTER TABLE [dbo].[SomeTable] DROP CONSTRAINT [PK_SomeTable].
Could you help with those issues?
Regards,
Andrei Sharafullin.
Comments
Can you help me reproduce the issue? If you can post the database files as a zip file?