How can we help you today? How can we help you today?

Using sqlcmd (:R) inside migration script

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
0

Comments

3 comments

Sort by
  • 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
    0
  • 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
    EricLamontagne
    0
  • 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
    DanC
    0

Add comment

Please sign in to leave a comment.