How can I pass data between servers using t-sql and/or sqlcmd?

I'm trying to write an automated sqlcmd script that creates two identical logins on two different servers. So the relevant steps go:

:connect primaryDb 

create login myuser with password='mypassword'
Go

:setvar SID sid from syslogins where name = 'myuser'

:connect secondaryDb

 create login myuser with password='mypassword', sid=$(SID)
 Go

Unfortunately the script fails at the step :setvar SID sid from syslogins where name = 'myuser'. I can't seem t开发者_StackOverflow社区o find a valid syntax to allow setvar to accept the result of a SQL statement.

Is there a syntax for this? Or a workaround that doesn't involve using linked servers?

Cheers, Matt

MSDN does not give any example of how to do such a thing, and from what I read there I'm assuming there is no standard way to initialise a scripting variable with some data you get in the query.

You might try to work around that, using output to a text file and possibly some batch scripting along the way.

The idea is basically like this. You split your query into two portions. The first portion gets the SID and writes it to a text file (using the :OUT scripting command). Then you initialise the scripting var outside the second portion (and prior to executing it, of course), run the second portion, and there you hopefully are.