Using sqlcmd (:R) inside migration script

EricLamontagne

Hi,

I'm trying to use a new stored procedure inside a migration script.
From what I understand, the sp is only created after all the migration scripts have been processed, thus it's not available in the migration script.

I tried to use SQLCMD :r command to import and run the script directly inside the migration script.
I have tried different path to import the script (full path, relative to the .sqlproj location) without success.

Here's the command I'm using:

:R "D:\I4\Analytics\Datawarehouse\ProligentDW\Programmable Objects\maintenance\Functions\fct_GetIndexVersion.sql"

or

:R ".\Programmable Objects\maintenance\Functions\fct_GetIndexVersion.sql"

Should it be possible to do? Is there another way of doing it?

regards,

Eric

EricLamontagne

If I just do a deploy in Visual Studio 2022, it actually works 

It's when I build the project that it fails.
The error is:

C:\I4\Analytics\Datawarehouse\ProligentDW\ProligentDW.sqlproj: Error:  : Error parsing line 2, column 1: Incorrect syntax near GO.

Error parsing line 5, column 1: Incorrect syntax near :.: Error parsing line 2, column 1: Incorrect syntax near GO.

Error parsing line 5, column 1: Incorrect syntax near :.

Done building project "ProligentDW.sqlproj" -- FAILED.

EricLamontagne

I found this: Using include scripts in Visual Studio - SQL Change Automation 4 - Product Documentation (red-gate.com)

Only pre and post migration support :r including scripts.
I tried to do it that way, but again, not much success, the programmable object scripts are not found

DanC

Hi @EricLamontagne

In this case, you can script the creation of that SPROC beforehand in the migrations script (recommend using CREATE OR ALTER, if using a compatible version of SQL Server or some sort of conditional code)

If you change the code of the Programmable Object, then a more scalable solution would be to switch to using ScriptInMigrations: https://documentation.red-gate.com/sca/developing-databases/concepts/migrations/programmable-objects

Process would then be:
 

• Delete the existing programmable objects folder
• Delete the migration you're trying to use
• Recreate the migration and this will then include the stored proc in the script