Run sql script from another sql script

Question

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)

I've found a few descriptions of doing this in a batch file - is this OK? gallery.technet.microsoft.com/scriptcenter/… only thing is it won't do it in any particular order — Nick.Mc
– Nick.Mc, Commented Apr 19, 2017 at 10:32
@Nick.McDermaid :r should run sql scritps. Post which you suggest as duplicated need to mention all sql scripts from folder. I would like to automate it — Jacek
– Jacek, Commented Apr 19, 2017 at 10:34
Sorry - I though I retracted the duplicate after I realised you were asking for something different. Note that my suggestion :r @fullScriptPath is different to what you have which is :r (SELECT @fullScriptPath) — Nick.Mc
– Nick.Mc, Commented Apr 19, 2017 at 10:35
@Nick.McDermaid: Your link to ScriptCenter show how to run all scripts from folder by CMD, but I want to do it by sql script. — Jacek
– Jacek, Commented Apr 19, 2017 at 10:36

Mark Kremers · Accepted Answer · 2017-04-19 10:19:20Z

1

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

answered Apr 19, 2017 at 10:19

Mark Kremers

1,79712 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rd_nielsen · Accepted Answer · 2017-05-06 21:52:49Z

1

If you're willing to use a scripting environment other than T-SQL, you could do this with execsql.py (http://pypi.python.org/pypi/execsql). The INCLUDE metacommand (http://pythonhosted.org/execsql/#include) will allow you to insert another SQL script within the running script, and the example of importing all CSV files in a directory (http://pythonhosted.org/execsql/#example13) could be easily adapted to use to include all SQL scripts in a directory.

Disclaimer: I wrote execsql.py.

answered May 6, 2017 at 21:52

rd_nielsen

2,4592 gold badges14 silver badges19 bronze badges

Collectives™ on Stack Overflow

Run sql script from another sql script

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related