SQLCMD and load instace from instance_list.txt

SQL Server Administration
1

Hello,

I have 2 files:

  1. file: ACL.sql script in SQL
  2. file .: instances_list.txt, where I have a listing of SQL instances where I want to apply ACL.sql

I need to use SQLCMD to load instances from instancelist.txt and apply ACL.sql

Will you help me, please?

2

powershell might be your best option. how often will you do this stuff? one time or often? how many instances?

3

Powershell is forbidden.
Application via SQLCMD twice a week.

Thx!

4

Forbidden?!? What kind of place forbids you to use such a tool. Please make us a 5 course meal with lobster and steak, but use of the kitchen is forbidden!. use wooden utensils.

How about a .bat file can you use that ?

5

Unfortunately, this is the policy of organization, that i will not influence.

Only SQLCMD :confused:

6

bummer. how about .bat files. can you use that

7

bat/ cmd is OK.

8

try this. For now just use a simple test SQLTEST.SQL file

The following is broken into multi files in order to fit this forum's textarea box.
You will have to make it into 1 long line.

FOR /F "tokens=1 delims=," %%G IN (instances_list.txt) 
DO FOR /F "tokens=1 delims=," %%G IN (instances_list.txt) 
DO SQLCMD -S %%G -i "SQLTEST.sql" -m 12 -E

your test sql file should include the following. Make sure you use a database where this command will happen. unless this is also forbidden :frowning:

if exists(select 1 from sys.tables where name = 'SQLCMD')
	begin
		drop table  dbo.SQLCMD
	end

if not exists(select 1 from sys.tables where name = 'SQLCMD')
	begin
		create table  dbo.SQLCMD(areyouthere varchar(50))
	end
1 Like
9

I think that's it. Thank you very much, yosiasz!

10

Hi,

FOR /F "tokens=1 delims=," %%G IN (instances.csv) DO FOR /F "tokens=1 delims=," %%G IN (instances.csv) DO SQLCMD -S %%G -i "ACL.sql" -m 12 -E -o "output_protocol.txt"

How do I change output_protocol.txt so that it is not overwritten but is written as an append?

Best Regards,

M2F

11

FOR /F "tokens=1 delims=," %%G IN (dblist.csv) DO FOR /F "tokens=1 delims=," %%G IN (dblist.csv) DO SQLCMD -S %%G -i "acl.sql" -m 12 -E -o "protocol_output.txt"

How do I make a change, but the protocol (-o "protocol_output.txt") has grown (=> appending)?

13

Hi all,

FOR /F "tokens=1 delims=," %%G IN (instanceslist.csv) DO FOR /F "tokens=1 delims=," %%G IN (instanceslist.csv) DO SQLCMD -S %%G -i "acl.sql" -m 12 -E -o "protocol_output.txt"

If I have 10 different SQL instances in INSTANCES_LIST.csv, then I have 10x10 records in PROTOCOL_OUTPUT.txt, for each SQL instance from INSTANCES_LIST.csv the code is executed 10 times.

How do I make the SQL code from ACL.sql run just once and get a 10x1 record in PROTOCOL_OUTPUT.txt?

Besr Redargs,

M2F

15

Finish, only this small change:

FOR /F "tokens=1 delims=0," %%G IN (INSTANCES_LIST.csv) DO SQLCMD -S %%G -i "ACL.sql" -m 12 -E >> PROTOCOL_OUTPUT.txt 2>&1

16

Hi All,

please,
How do I make SQLCMD retrieve any SQL file name using –i? (* .sql) If there will always be only 1 file *.sql in the directory?

Thx!
M2F