SQL batch variable

Question

I need to automate a query so I can perform it on all servers on a network. The thing is more servers are added constantly, so I need to keep it dynamic. On a table I have a list of the currently active servers, but they are repeated several times as each one has different data. Also it shows only a number, and the server name has an specific format. I did this to solve it:

select distinct ('swp0'+ cast(rtl_loc_id as nvarchar(4000)) +'r01') 
from basename..inv_valid_destinations

I got this to an output file, but now I want to use it as an input to sqlcmd. Each server name (each line from previous output) should be used as an -s argument. I have tried different ways of doing this to no avail. It should be something like this:

SQLCMD -Sswp0241r01 -Uswpos -isalto_folio.sql -osalto_folio.txt

As I said, more servers appear constantly and we need to perform a query on all active servers at the time and produce an output file. Could you help me out?

pyrospade · Accepted Answer · 2013-02-18 20:16:23Z

1

If you really want to do this in batch you can use a loop, where yourfile is the file containing a list of servers.

for /F "tokens=*" %%A in (readme.txt) do (
    SQLCMD -S%%A -Uswpos -isalto_folio.sql -osalto_folio.txt
)

edited Feb 18, 2013 at 20:16

answered Feb 18, 2013 at 20:10

pyrospade

8,1004 gold badges38 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gabrielbaca Over a year ago

I only did an adjustment removing the "tokens=*" part, as it would not work with it. But it did what I needed, thank you very much.

Collectives™ on Stack Overflow

SQL batch variable

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related