SSMS SQLCMD Mode with ApplicationIntent=ReadOnly

  • Marcia Q

    SSCrazy

    Points: 2645

    I'm new to SQL Server 2017 availability groups so any help would be appreciated. 
    For my scripts that use select queries, I would like to use SQLCMD mode to reach the secondary/read only database via a listener, instead of clicking my way through.

    I have unsuccessfully tried variations of  :CONNECT Listener_FQDN,Port;database=MyDatabase;ApplicationIntent=ReadOnly
    I tried using variables but couldn't get that working either. 

    Is it possible to use SQLCMD Mode :connect to get to the secondary with a listener?  I do not want to have to look first to see which server is secondary and then change my script accordingly.

    I can successfully connect as follows:
    a)  I can connect to the secondary using the  "Connect to Server" --> Options --> Additional Connections Parameter --> ApplicationIntent=Readonly   (and I specify a user database name so that it doesn't attempt to connect to master)
    b)  I can connect to the primary via SQLCMD mode using :CONNECT Listener_FQDN,Port

    Any suggestions?

  • Sue_H

    SSC Guru

    Points: 89884

    Marcia Q - Thursday, July 12, 2018 9:39 AM

    I'm new to SQL Server 2017 availability groups so any help would be appreciated. 
    For my scripts that use select queries, I would like to use SQLCMD mode to reach the secondary/read only database via a listener, instead of clicking my way through.

    I have unsuccessfully tried variations of  :CONNECT Listener_FQDN,Port;database=MyDatabase;ApplicationIntent=ReadOnly
    I tried using variables but couldn't get that working either. 

    Is it possible to use SQLCMD Mode :connect to get to the secondary with a listener?  I do not want to have to look first to see which server is secondary and then change my script accordingly.

    I can successfully connect as follows:
    a)  I can connect to the secondary using the  "Connect to Server" --> Options --> Additional Connections Parameter --> ApplicationIntent=Readonly   (and I specify a user database name so that it doesn't attempt to connect to master)
    b)  I can connect to the primary via SQLCMD mode using :CONNECT Listener_FQDN,Port

    Any suggestions?

    Make sure you are using: -K readonly 
    option with sqlcmd.
    Connecting with sqlcmd

    Sue

  • Marcia Q

    SSCrazy

    Points: 2645

    Thanks for the reply.  Yes.I use -K when I connect through a command prompt.   However, I am using Management Studio (SSMS).  -K doesn't work there.

    In Management studio SQLCMD Mode, this works to get me to the primary:  :CONNECT listenername.domain,port

    However, if I use :CONNECT listenername.domain,port -K   OR if I use :CONNECT listenername.domain,port; -K
    then I get the following error:  A fatal scripting error occurred.  Incorrect syntax was encountered while parsing :CONNECT.

  • Sue_H

    SSC Guru

    Points: 89884

    Marcia Q - Thursday, July 12, 2018 10:47 AM

    Thanks for the reply.  Yes.I use -K when I connect through a command prompt.   However, I am using Management Studio (SSMS).  -K doesn't work there.

    In Management studio SQLCMD Mode, this works to get me to the primary:  :CONNECT listenername.domain,port

    However, if I use :CONNECT listenername.domain,port -K   OR if I use :CONNECT listenername.domain,port; -K
    then I get the following error:  A fatal scripting error occurred.  Incorrect syntax was encountered while parsing :CONNECT.

    Sorry didn't understand what you were asking originally. You want to change connections using :CONNECT in SQLCMD mode and connect to read-only intent secondaries. I don't think there is a parameter for that using :CONNECT. The documentation is lacking but if it's correct, you can only specify server, user and password. When read-only intent isn't supported you need to set readable secondary to yet.

    Sue

  • Marcia Q

    SSCrazy

    Points: 2645

    OK.  Thank you for the followup.  I haven't found any documentation for it either, but I was hopeful that someone had found a way to make it happen!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply