Share via

Using the ":r" sqlcmd command

  • Article

Question

Wednesday, September 5, 2012 5:59 PM

hi, 

I use the :r command in SQLCMD script to include another script  into the calling script. Just like this,

http://www.mssqltips.com/sqlservertip/1543/using-sqlcmd-to-execute-multiple-sql-server-scripts/

The problem is, I found that the :r command works fine until I move it into a IF or BEGIN TRY/END TRY statement. I.e., the :r command won't work if I enclose it within other statements. 

Is it a known issue, or I've neglected something? 

http://msdn.microsoft.com/en-us/library/ms162773.aspx say, 

"If the file contains Transact-SQL statements that are not followed by GO, you must enter GO on the line that follows :r."

but I've done that. 

Please help.

Thanks

All replies (14)

Friday, September 7, 2012 6:11 AM ✅Answered | 1 vote

Thanks Ray, 

About that GO, there are some commands that must followed by GO. 

Where can I find a full list of such commands? 

I am afraid that my scripts uses several of them, but I don't want to go the trial and error way.

Thanks

Hi smetah,

The documentation of :r says: it loads the file into the sqlcmd buffer and it sends it to the server at next GO. So maybe GOs are only allowed in the parent script, and the included script should not have GO or the ‘GO’ itself is sent to the SQL, which does not understand it. For more details, please refer to this similar thread.

Best Regards,
Ray Chen

Thursday, September 6, 2012 8:34 AM | 1 vote

The problem is, I found that the :r command works fine until I move it into a IF or BEGIN TRY/END TRY statement. I.e., the :r command won't work if I enclose it within other statements. 

Hi smetah,

Could you please explain your scenario much more? :r command parses additional Transact-SQL statements and sqlcmd commands from the file specified by <filename> into the statement cache.

I did a similar test on the sample which you provided. I added an IF statement before the :r command:

IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'MSSQLTIPS'):r d:\CREATE_TABLES.sql

Is this what you described? And this works fine on my machine. If I misunderstand you, please provide more information for further troubleshooting.

Best Regards,
Ray Chen

Thursday, September 6, 2012 9:13 PM

I did a similar test on the sample which you provided. I added an IF statement before the :r command:

IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'MSSQLTIPS')
:r d:\CREATE_TABLES.sql

Thanks Ray, 

I not able to duplicate the if error for the moment, but here is BEGIN TRY/END TRY statement:

BEGIN TRY
    :r CREATE_INDEXES.sql
END TRY
BEGIN CATCH
    PRINT 'Something wrong'
END CATCH

I'll get:

Incorrect syntax near 'CREATING INDEXES'.

How about you?

Thursday, September 6, 2012 9:16 PM

I not able to duplicate the if error for the moment

Here it is:

IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'MSSQLTIPS')
BEGIN
    :r CREATE_TABLES.sql
END

Friday, September 7, 2012 2:15 AM

I did a similar test on the sample which you provided. I added an IF statement before the :r command:

IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'MSSQLTIPS'):r d:\CREATE_TABLES.sql

Thanks Ray, 

I not able to duplicate the if error for the moment, but here is BEGIN TRY/END TRY statement:

BEGIN TRY    :r CREATE_INDEXES.sqlEND TRYBEGIN CATCH    PRINT 'Something wrong'END CATCH

I'll get:

Incorrect syntax near 'CREATING INDEXES'.

How about you?

Hi smetach,

Yes, I have the same error as yours when I test it. But after I remove all commands “GO” from the CREATE_TABLES.sql, it works fine. So please try to remove all commands “GO” from your CREATE_INDEXES.sql and give it a try again.

Best Regards,
Ray Chen

Friday, September 7, 2012 2:36 AM

Thanks Ray, 

About that GO, there are some commands that must followed by GO. 

Where can I find a full list of such commands? 

I am afraid that my scripts uses several of them, but I don't want to go the trial and error way.

Thanks

Sunday, February 24, 2013 4:05 PM

I have some strange behavior with :r

I have VS 2010 DB project, added the following code to "Script.PostDeployment.sql"

IF NOT EXISTS (SELECT 1 FROM states WHERE state_abv = 'WY')
Print 'States table already filled'
else
:r .\Script.PostDeployment-States.sql

Under the :r  I have a red broken line, hovering it indicates "SQL80001: Incorrect syntax near ':'

What is strange, when I did a deploy, the script was executed Script.PostDeployment-States.sql and table was populated as it was the 1st run.

New when I re-deploy, I get "statemants terminated ......." in the output window as it seems that the script was re-executed and the "IF not exists..." had no impact , then in the VS errors list window I have

Error 1 SQL80001: Incorrect syntax near ':'. E:\Salam\Dev\VS2010\Tips\DemoDB\Scripts\Post-Deployment\Script.PostDeployment.sql 25 1 DemoDB

Sunday, February 24, 2013 8:42 PM | 1 vote

Ha, another victim of the ":r" sqlcmd command.

eliassal(/Shulei Chen), I'll mark the thread unsolved for you, so that people will notice. Otherwise, it might fly under the radar. 

I know some people would recommend eliassal to open a brand new thread. But IMHO that is only good for managing the Q&A here, not good for people looking for answers, because related questions would then be scattered all around chaotically. 

Here it goes. 

Sunday, February 24, 2013 8:49 PM

Thanks, appreciate your initative

Thursday, March 7, 2013 9:09 AM

I have some strange behavior with :r

I have VS 2010 DB project, added the following code to "Script.PostDeployment.sql"

IF NOT EXISTS (SELECT 1 FROM states WHERE state_abv = 'WY')
Print 'States table already filled'
else
:r .\Script.PostDeployment-States.sql

Under the :r  I have a red broken line, hovering it indicates "SQL80001: Incorrect syntax near ':'

What is strange, when I did a deploy, the script was executed Script.PostDeployment-States.sql and table was populated as it was the 1st run.

New when I re-deploy, I get "statemants terminated ......." in the output window as it seems that the script was re-executed and the "IF not exists..." had no impact , then in the VS errors list window I have

Error 1 SQL80001: Incorrect syntax near ':'. E:\Salam\Dev\VS2010\Tips\DemoDB\Scripts\Post-Deployment\Script.PostDeployment.sql 25 1 DemoDB

Any ideas for eliassal's issue?

Ed Price (a.k.a User Ed), SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

Answer an interesting question? Create a wiki article about it!

Thursday, March 14, 2013 3:38 PM

Ha, another victim of the ":r" sqlcmd command.

eliassal(/Shulei Chen), I'll mark the thread unsolved for you, so that people will notice. Otherwise, it might fly under the radar. 

I know some people would recommend eliassal to open a brand new thread. But IMHO that is only good for managing the Q&A here, not good for people looking for answers, because related questions would then be scattered all around chaotically. 

Here it goes. 

Good for finding answers. Not good for getting answers. Also not good for motivating anyone to answer, if you unmark their answer after they answered your question.

Thanks!

Ed Price (a.k.a User Ed), SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

Answer an interesting question? Create a wiki article about it!

Wednesday, October 29, 2014 4:05 PM

I'm having the same issue in VS 2013 Database project. We just recently upgraded from VS 2010. Any resolution?

I'm building my scripts like this

SET XACT_ABORT ON
BEGIN TRAN
:r .\Next\01_INSERT_PFS_INVOICE.sql
:r .\Next\02_CREATE_ADMIN_VIEWS.sql
:r .\Next\03_ALTER_VIEW_V_ECM_FACILITY_INVENTORIES_RAW.sql
COMMIT

I'm getting the error on the first :r command. If I remove that sql, the error moves on to the second :r command

Error 19 SQL80001: Incorrect syntax near ':'. C:\Code\ETRAC\FP\Database\CMS.Database\Scripts\Post-Deployment\Script.PostDeployment.sql 14 2 CMS.Database

This is strange. No clue on how to go about resolving this issue. The DB project builds fine, but its not spitting out the output sql merging all my scripts.

Wednesday, December 24, 2014 6:10 PM

Late I know, but for the sake of recorded knowledge:

If you turn on the SQLCMD mode by hitting the said button at the top of the page you should be good.

Monday, March 18, 2019 8:03 PM

This most likely "worked" only because the condition was true when executed. If the condition is false, the :r command will STILL execute. I've verified this in SMSS 13 (SQL Server 2016) with this:

if 1=2
    !!DIR
else
    print 'nope'

The directory is gotten regardless of whether the if expression evaluates to true. "Nope" is never printed, and in fact, after the directory listing in the output pane, it gets the error:

Msg 156, Level 15, State 1, Line xx
Incorrect syntax near the keyword 'else'.