RedGate Migrations V2 Beta keep getting Invalid Object Name [TableName]

Question

I am currently evaluating the use of RedGate SQL Source Control and SQLCompare for our Continuous Integration initiative. Starting out everything went very smoothly, it was easy to Add/Drop tables, Add nullable columns, drop columns, and so on without any problem. That is until I started looking at migration scripts. The simple example I was trying out was making a nullable column non nullable.

Our process: Change is made in shared development SQL Server -> Change is checked into source control -> Deploy to test environment by comparing source control with test database.

Setup:

• Source Control: GIT
• Model: Shared - All Developers work on the same database
• Temporary database: LocalDB - Developers do not have ability to create databases on servers
• Use the source controlled as the "truth center" because someone might be in the middle of a change on the Development Server. Unintended/unfinished changes might be pushed up.

Sample migration script:

DECLARE @ShouldRunMigrationScript BIT
SET @ShouldRunMigrationScript = 1
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.Columns WHERE TABLE_SCHEMA = 'dbo' AND table_name='Test' AND COLUMN_NAME = 'testColumn' AND IS_NULLABLE = 'No')
BEGIN
    SET @ShouldRunMigrationScript = 0;
    PRINT 'Column [testColumn] in [dbo].[Test] is already not nullable - skipping migration';   
END
IF @ShouldRunMigrationScript = 1
BEGIN        
     UPDATE [dbo].[Test] SET testColumn = anotherTestColumn WHERE TestColumn IS NULL;
     ALTER TABLE [dbo].[Test] ALTER COLUMN [testColumn] VARCHAR(500) NOT NULL;    
END

I run SQL Compare from the command prompt:

"C:\Program Files (x86)\Red Gate\SQL Compare 10\sqlcompare" /scripts1:"[Folder where SQL Source Control Saves]"  /Server2:[Test SQL Server] /Database2:[Test Database] /scriptfile:"c:\Migrations.txt" /f /Options:Defaults,UseMigrationsV2

It gives me this error "Error while running migration script: Invalid object name 'dbo.Test.'

Both development and test SQL servers have dbo.Test. I'm not dropping the column or the table in any of my scripts. Does anybody have any idea why that would happen?

Answer 1

I read through the documentation about how V2 Migrations Work provided by RedGate (found here), but it didn't help as much as I'd hope. I also discovered the Google Group forum post where a person was running into a similar problem. In that post RedGate said when using LocalDB as the temporary server, databases are created in the instance (LocalDB)\RedGateTemp. It is important to note Migrations V2 is still in beta so this might change.

I know localDB is installed because I ran this the sqlLocalDb command from the command prompt and no error was thrown. I was also able to connect to (localDb)\RedGateTemp via Sql Server Management Studio, so that was running fine. After a little trial and error I was able to figure out what is happening with V2 Migrations.

1. SQLCompare sees you are comparing a script folder with a database (in this case test) and there is a new migration script
2. SQLCompare pulls the schema information from the deployment database and creates a copy of that database on (LocalDB)\RedGateTemp
3. SQLCompare then runs the new migration script on the temp database (LocalDB)\RedGateTemp
4. SQLCompare figures out what changes and generates a migration script, or if you use the /sync switch, deploys the changes
5. If during the temp database creation on (LocalDB)\RedGateTemp an exception occurs then the entire temp database is dropped, but the migration script is still ran.

What I did to find my error was run SQL Profiler on (LocalDB)\RedGateTemp with it monitoring the events ErrorLog and EventLog from Errors and Warnings section and SQL:BatchStarting from TSQL section. By doing that I saw LocalDB was throwing an error when a table-valued function with the Contains command on a full text field was being created from the test database. LocalDB doesn't support that functionality. Luckily for me, that function was not needed so I deleted it. After that everything started working again.

I wanted to share how I solved this problem to help point people in the right direction.