April 11, 2017 at 11:38 am
Hi all - thanks in advance for any suggestions, they are appreciated.
I have a need to :Connect to multiple sql instances using sqlcmd in the middle of tsql script that is kicked off by a windows batch file executing the sql script in sqlcmd mode. When I generate the code via SSMS (which is simply adding a database to an availability group) it works fine, because the server names are hardcoded in the script as
:Connect replicaName
However, I will not know the primary nor secondary replica names when kicking off the bat file. So I'm connecting to the primary via the listener, which is successful. Then I gather the secondary replica names and place them in a cursor to loop thru. When I attempt to connect to the secondary replica via the following code it gives me an error.
:Connect @replicaName
It seems as if because the command is executed via sqlcmd that it can't reconcile the variables? It is actually trying to connect to a server named @replicaName.
Any suggestions for a way around this?
April 11, 2017 at 11:59 am
random_name - Tuesday, April 11, 2017 11:38 AMHi all - thanks in advance for any suggestions, they are appreciated.
I have a need to :Connect to multiple sql instances using sqlcmd in the middle of tsql script that is kicked off by a windows batch file executing the sql script in sqlcmd mode. When I generate the code via SSMS (which is simply adding a database to an availability group) it works fine, because the server names are hardcoded in the script as
:Connect replicaNameHowever, I will not know the primary nor secondary replica names when kicking off the bat file. So I'm connecting to the primary via the listener, which is successful. Then I gather the secondary replica names and place them in a cursor to loop thru. When I attempt to connect to the secondary replica via the following code it gives me an error.
:Connect @replicaName
It seems as if because the command is executed via sqlcmd that it can't reconcile the variables? It is actually trying to connect to a server named @replicaName.
Any suggestions for a way around this?
Try using $variables instead. See here, for example.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 11, 2017 at 1:28 pm
Interesting theory. I attempted and the sqlcmd variables cannot be set to tsql variables. So :setvar server @replicaName does not resolve to the value of @replicaName, it just puts the text @replicaName into $(server) variable. It was worth a shot.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply