locked
Using the ":r" sqlcmd command RRS feed

Answers

  • 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

    Friday, September 7, 2012 6:11 AM

All replies

  • 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 8:34 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.sql
    END TRY
    BEGIN CATCH
        PRINT 'Something wrong'
    END CATCH

    I'll get:

    Incorrect syntax near 'CREATING INDEXES'.

    How about you?


    • Edited by smetah Thursday, September 6, 2012 9:13 PM
    Thursday, September 6, 2012 9:13 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 
    

    Thursday, September 6, 2012 9:16 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?



    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

    • Marked as answer by smetah Friday, September 7, 2012 1:17 PM
    • Unmarked as answer by smetah Sunday, February 24, 2013 8:43 PM
    Friday, September 7, 2012 2:15 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

    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


    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

    Friday, September 7, 2012 6:11 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

    Sunday, February 24, 2013 4:05 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. 


    • Edited by smetah Sunday, February 24, 2013 8:43 PM
    Sunday, February 24, 2013 8:42 PM
  • Thanks, appreciate your initative
    Sunday, February 24, 2013 8:49 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


    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 7, 2013 9:09 AM
  • 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!

    Thursday, March 14, 2013 3:38 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, October 29, 2014 4:05 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.

    • Proposed as answer by lknudsen Tuesday, October 27, 2015 9:28 AM
    Wednesday, December 24, 2014 6:10 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'.


    Monday, March 18, 2019 8:03 PM