Search code examples
sql-serverfile-iocmdsqlcmd

r: "$(fileName)" in a script called from sqlcmd

I'm trying to create a script just to run other scripts and do some extra stuff in case of successful or failure.

You have the full code on this link just to try to be clear what I'm trying to achieve.

Basically I want to:

-- DO SOME STUFF HERE

r: "$(fileName)"

-- MORE STUFF HERE

and call it from sqlcmd this way:

sqlcmd -i "RunScript.sql" -v fileName="someFileName.sql" -s server -d database

But I can't, I'm getting the following error:

Msg 102, Level 15, State 1, Server SERVER, Line 19 Incorrect syntax near 'someFileName.sql'.

So, it seems that the little r: couldn't be used with a parameter on his side.

Just to clarify, someFileName.sql isn't in the SQL Server, but in my machine, so I couldn't use this way to read the file. In fact, I just tried it later.

Is there a workaround to archive this? Any ideas to solve it?

Solution

  • You could break your current RunScript.sql script into separate header and footer SQL scripts and concatenate them together with a SQL script in the middle that is denoted by an input parameter to a CMD script. For example:

    RunSQL.CMD consists of:

    @ECHO OFF

COPY /V /Y RunScriptHeader.sql + %1 + RunScriptFooter.sql RunScriptTemp.sql

SQLCMD -i "RunScriptTemp.sql" -s server -d database
    • The /V does a verify on the copy
    • The /Y suppresses prompting to confirm overwriting an existing file

    You would run it as follows:

    RunSQL.CMD someFileName.sql