[SOLVED] Run sql script from another sql script

Run sql script from another sql script

I want to run all scripts from specified folder. I created sql-cursor which contains script's paths.

I want to execute script and log usage of them, but how can I run sql script inside other sql script. There is ':r' statement used in sqlproj in Visual Studio, but it doesn't work in my case, so how can I resolve it? I use SQL Server 2016.

    FETCH NEXT FROM ScriptsFromFolderCursor INTO @scriptName
    WHILE @@FETCH_STATUS = 0
    BEGIN 
        DECLARE @fullScriptPath nvarchar(1024) = @MyPath + @scriptName

        -- WHAT SHOULD BE THERE ??
        :r @fullScriptPath

        -- log usage of script
        INSERT INTO  DeployedSqlScripts([ScriptName], [Version],[DateUtc])
        VALUES (@fullScriptPath, @DbVersion, GetUtcDate())

        FETCH NEXT FROM ScriptsFromFolderCursor INTO @scriptName
    END

I got error:

Error SQL72001: The included file @fullScriptPath (C:\GIT\myPath\@FULLSCRIPTPATH) does not exist. (54, 8)

Solution

Not sure what error you get, but by the looks of it you are trying to use a SQLCMD statement (:r)

Have you executed this script in SQLCMD Mode? Documentation - https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility