SQLCMD and Batch File magic

Question

Post reply

SQLCMD and Batch File magic

Manu Mohanan

SSC Journeyman

Points: 99
More actions
November 18, 2015 at 10:10 pm

#305166

Comments posted to this topic are about the item SQLCMD and Batch File magic

Viewing 15 posts - 1 through 15 (of 22 total)

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

anujmishra007 SSC Rookie Points: 39 More actions · Answer 1

There is one more approach I have tried

Keep all the .sql files in one .sql file with their path as given below:

ON Error EXIT /*if you want to exit when an error occurs*/

:SETVAR Path "C:\Users\Master Data Scripts\Scripts"

:r $(Path)\1.0.sql

Go

:r $(Path)\2.0.sql

Go

.........So on so far

Then run the below command in cmd

sqlcmd -S SERVERNAME -d DATABASENAME -U USERNAME -P PASSWORD -i "c:\Users" -r1 2> c:\Users\Master Data Scripts\scripts\03.Master Data Script Using Files.sql" -r1 2> c:\Users\Master Data Scripts\Scripts\Error.log 1>NUL

It will also create a folder having all the errors.

Hope this helps...

Indianrock SSC-Insane Points: 20333 More actions · Answer 2

Good work. I'm curious why you chose batch language instead of powershell.

schleep SSChampion Points: 12583 More actions · Answer 3

Try this in Powershell: this basic functionality is relatively simple to learn.

You'll never go back to .bat

artilugio SSC-Addicted Points: 450 More actions · Answer 4

Excellent:short,elegant, simple to configure

Yes, can do another version in powershell with more bells and whistles, but this is perfect . Five stars.

r5d4 SSCrazy Points: 2522 More actions · Answer 5

It's a great technique. I've been doing similar for years.

I've recently recreated it in PowerShell, but keep having to revert to batch files as clients don't have up to date systems and getting the OK to install/upgrade powershell components isn't easy on public sector servers!

darragh.hickey SSC Rookie Points: 48 More actions · Answer 6

I've been running something similar but with a run-once flag file for safety.

Darragh

ECHO OFF

IF EXIST _FLAG.TMP GOTO ALREADYRUNLABEL

GOTO OKTORUNLABEL

:ALREADYRUNLABEL

ECHO This SQL update batch file has already been run once.

ECHO Please contact R+D for support.

GOTO END

:OKTORUNLABEL

sqlcmd calls here

ECHO. |TIME > _FLAG.TMP

:END

ECHO

ECHO Execution finished.

Robert Sterbal SSChampion Points: 11049 More actions · Answer 7

It would be nice if someone posted both the Powershell and the bat script in the same article.

This .bat approach is very helpful

Thanks!

412-977-3526 call/text

vivasjimmy SSC Enthusiast Points: 110 More actions · Answer 8

Hello falk

I have a batch file running sqlcmd and use Windows schedule task to trigger it every 30 minutes.

My issue is that for an unknown reason the batch file is not running from the scheduler, it is giving me a very general error 0x1.

If I run the batch file manually works perfect but the scheduler is not triggering

I already checked path location batch name. Those basis stuff

Any ideas ?

sknox SSChampion Points: 12388 More actions · Answer 9

Great article

Powershell fans: write this same type of article using powershell instead and publish it here. Then readers will have easy reference to two great ways to accomplish this.

Robert Sterbal SSChampion Points: 11049 More actions · Answer 10

vivasjimmy (11/19/2015)
Hello falk
I have a batch file running sqlcmd and use Windows schedule task to trigger it every 30 minutes.
My issue is that for an unknown reason the batch file is not running from the scheduler, it is giving me a very general error 0x1.
If I run the batch file manually works perfect but the scheduler is not triggering
I already checked path location batch name. Those basis stuff
Any ideas ?

Check permissions.

run a command to see if the scheduler is working: echo %time% test >> c:\test-output.txt

412-977-3526 call/text

r5d4 SSCrazy Points: 2522 More actions · Answer 11

vivasjimmy - I've encountered this in the past.

Look at permissions and get your task to be owned and executed by an account with the appropriate permissions.

You may have to add an account to run tasks.

Windows Scheduler will need you to supply the account name and password, so make sure the password does not expire.

schleep SSChampion Points: 12583 More actions · Answer 12

The Powershell would look something like this. I haven't tested it (the -o part may need some work).

One problem with the *.SQL files-in-this-folder approach is that one may run into dependency issues: the files will be ordered alphabetically (or sorted on some other file property).

To get around this, I explicitly add files to an array; they are then applied in the inserted order.

#@@echo off

# Note: this does not work in the PowerGUI application. It does work when this file is run as a .ps1

$scriptPath = Split-Path -parent $myInvocation.myCommand.definition

#del errors /f /s /q

#rd Errors

$ErrorFolder = "$scriptPath\Error"

if (Test-Path -Path $ErrorFolder) {Remove-Item -Path $ErrorFolder -Force}

#md Errors

New-Item -Name "Errors" -ItemType Directory -Path $scriptPath

$SqlFiles = Get-ChildItem -Path $scriptPath -Filter "*.SQL"

$Databases = ("DATABASE1", "DATABASE2") #...."DATABASEn"

#FOR %%A IN (*.SQL) DO ( sqlcmd -S SERVERNAME -d DATABASE1 -U username -P password -i "%%A" -o "Errors\%%AError_DB1.txt" -I )

#FOR %%A IN (*.SQL) DO ( sqlcmd -S SERVERNAME -d DATABASE2 -U username -P password -i "%%A" -o "Errors\%%AError_DB2.txt" -I )

foreach ($Database in $Databases)

{

foreach ($File in $Files)

{

$OutFile = "$ErrorFolder\$($File.Name)_DB1.txt"

&sqlcmd -S ServerName -d $Database -U userName -P password -i $File -o $OutFile -I

}

jvoogt1 SSC Enthusiast Points: 110 More actions · Answer 13

Here is my solution, might need some improvements.

@echo off

cls

set /p SName=Server Name :

set /p UName=User Name :

set /p Pwd=Password :

set /p DbName=Database Name :

:begin

if exist _Deploy.txt del _Deploy.txt

IF [%SName%] == [] set SName=localhost

IF [%DbName%] == [] set DbName=master

ECHO %SName%

@echo off

dir /b *.sql > _Deploy.txt

if exist _Run.txt del _Run.txt

IF [%UName%] == [] GOTO Trusted

GOTO NonTrusted

:Trusted

for %%f in (*.sql) do sqlcmd.exe -S %SName% -E -d %DbName% -i "%%f" >>_Run.txt

GOTO Output

:NonTrusted

for %%f in (*.sql) do sqlcmd.exe -S %SName% -U %UName% -P %Pwd% -d %DbName% -i "%%f" >>_Run.txt

GOTO Output

:Output

@notepad _Run.txt

@notepad _Deploy.txt

:end

siraj.jamdar SSC Rookie Points: 26 More actions · Answer 14

I would like to suggest that instead of hard coding server names, user names etc. define them as variables.

Then call a separate configuration file where the variables are assigned the values based on the environment parameter

That way, the same set of scripts can be run in dev, test, uat, staging, production and postproduction without any change to the batch files, thus minimizing errors.

PowerShell would be nice, but it is still not ubiquitous enough as mentioned in a previous comment.