SQLCMD and using file instances_list.txt as target intances for applicate ACL.sq

Question

Post reply

SQLCMD and using file instances_list.txt as target intances for applicate ACL.sq

Mark2Fox

SSC Enthusiast

Points: 128
More actions
September 24, 2019 at 2:16 pm

#3682952

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?
Thank You!
Mark2F

Viewing 11 posts - 1 through 10 (of 10 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

with powershell, here is how i would do it.

Assuming instances_list.txt is a single column list with no header.

also assuming the script has USE [somedb] or fully qualified names so it can execute form the master database.

$AllServersToScan = Get-Content -Path 'C:\Data\instances_list.txt'
$sqlfile = 'C:\Data\ACL.sql'
$LogPath = 'C:\Data\ACL_Log.txt'

foreach($SQLServer in $AllServersToScan)
{
  try
      {
          $StagingFileName = "--Begin " + [System.IO.Path]::GetFileName($sqlfile) + ' on ' + $SQLServer
          Write-Host $StagingFileName -ForegroundColor Green
          Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999
          Invoke-SqlCmd -InputFile $sqlfile  -ServerInstance $SQLServer -Database master -Querytimeout 0 | Out-File -LiteralPath $LogPath -Append -Encoding ASCII -Width 99999
          $StagingFileName = "--End   " + [System.IO.Path]::GetFileName($sqlfile)
          Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999

      }
      catch
      {
          $ErrorMessage = $_.Exception.Message
          $FailedItem = $_.Exception.ItemName
          $StagingFileName = "--Error " + [System.IO.Path]::GetFileName($sqlfile)  + ' on ' + $SQLServer
          Out-File -LiteralPath $LogPath -InputObject $FailedItem -Append -Encoding ASCII -Width 99999
          Out-File -LiteralPath $LogPath -InputObject $ErrorMessage -Append -Encoding ASCII -Width 99999
          Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999
      }
}

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mark2Fox SSC Enthusiast Points: 128 More actions · Answer 2

Unfortunately the policy of the organization banned Powershell. That's what I suggested first.

Your solution looks effective.

Thank you.

Lowell SSC Guru Points: 323518 More actions · Answer 3

then in that case, why don't you simply put all of the servers in Central Management Servers, and execute then via SSMS all together?

stick with the tools you know, isntead of sqlcmd , a programming language or powershell.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mark2Fox SSC Enthusiast Points: 128 More actions · Answer 4

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)?

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

Your last sentence doesn't quite make sense. You can just let the output come back and use redirects >>, to put the data into a file.

Mark2Fox SSC Enthusiast Points: 128 More actions · Answer 6

Hi,

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 5 database instances, I have 5 log entries for each, so a total of 25 log entries.

How to make each instance in the instances list be processed only once?

Best Regards,

M2F

This reply was modified 5 years, 1 month ago by Mark2Fox.

Mark2Fox SSC Enthusiast Points: 128 More actions · Answer 7

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 8

Try this in a file, edit the SQLCMD to match what you need.

@@echo off
setlocal EnableDelayedExpansion
for %%x in (*.sql) do (
  set source=%%x
  echo sqlcmd -E -i !source!
)

FWIW, banning PoSh is a little crazy, since MS has invested heavily in this and provides lots of interfaces for their products. This is really the future, and I might ask someone to reconsider.

This reply was modified 5 years, 1 month ago by Steve Jones - SSC Editor.

Mark2Fox SSC Enthusiast Points: 128 More actions · Answer 9

Mark2Fox

SSC Enthusiast

Points: 128

October 16, 2019 at 12:42 pm

#3689236

It works, thank you very much Steve!

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 10

Glad it works. You are welcome and hope this solves your issue.