using cmd file to execute sqlcmd, oSQL

Question

using cmd file to execute sqlcmd, oSQL

D-SQL

SSCrazy

Points: 2265
More actions
April 13, 2016 at 5:34 pm

#148873

Currently the company I work they have a process to use cmd file to exucute a cmd file to push all the sql to the server and they use sql login.
I was tasked to make it windows authentication. But they want it if SQL login fail use windows authentication. Then log the output with the error or success naming it by the servername. I dont know sqlcmd or o-sql. Here is what I put together. I have a cmd file that already outputs correctly so I tried to add that in.
any help is appreciated
test.cmd is the file name
@echo off
REM: Command File Created by Database Tools
REM: Date Generated:2015-09-24
REM: Authentication type: SQL Server
echo Begining Run the command file
SET DatabaseServer="SERVERNAME"
SET DatabaseName="DBA"
SET DbLoginID="sa"
SET DbPassword="@2008sql"
call TestOutput.cmd %DatabaseServer% %DatabaseName% %DbLoginID% %DbPassword%
@if errorlevel 1 goto :windows auth
:windows auth
call TestOutput.cmd %DatabaseServer% %DatabaseName% -E
@if errorlevel 1 goto :errors
ECHO OFF
rem mode con codepage select=1252
chcp 1252
@echo off
Setlocal enabledelayedexpansion
PUSHD %INPUT%
REM change backlash character \ to underscore _
SET SERVER2=!DatabaseServer:\=_!
echo %SERVER2%
ECHO %DATE%>"OUTPUT_%DatabaseServer%.TXT"
ECHO %TIME%>>"OUTPUT_%DatabaseServer%.TXT"
ECHO %INPUT%>>"OUTPUT_%DatabaseServer%.TXT"
ECHO ************>>"OUTPUT_%DatabaseServer%.TXT
FOR /F "delims=|" %%a IN ('DIR /B /O:N *.sql') DO ECHO %%a>>"OUTPUT_%SERVER2%.TXT" & SQLCMD -i "%%a">>"OUTPUT_%SERVER2%.TXT" & ECHO !TIME!>>"OUTPUT_%SERVER2%.TXT" & ECHO ************>>"OUTPUT_%SERVER2%.TXT"
START OUTPUT_%SERVER2%.TXT
TIMEOUT /t 10
POPD
echo End
goto finish
REM: error handler
:errors
echo.
echo WARNING! Error(s) were detected!
echo --------------------------------
echo Please evaluate the situation and, if needed,
echo restart this command file. You may need to,
echo supply command parameters when executing
echo this command file.
echo.
pause
goto done
REM: finished execution
:finish
echo.
echo test.cmd is complete!
pause
:done
@echo on

Viewing 4 posts - 1 through 3 (of 3 total)

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

tedmyron Newbie Points: 0 More actions · Answer 1

This is such a good post for the readers and I hope this will be a good post a s a reference material to know more about it. Structured Query Language is a DB language it has much importance in S/W field.

write my paper for cheap[/url]

terccogsresloui1975 Newbie Points: 0 More actions · Answer 2

Yeah really very informative post. I'd like to use it in the reference in my academic paper, if you don't mind of course. I'm writing that paper by myself and i think it would be great to have this article in it. Tried to order from http://ratedbystudents.com/services/freshessays but their quality and speed are so low.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

This is "Death by SQL" in two ways...

SET DatabaseServer="SERVERNAME"

SET DatabaseName="DBA"

SET DbLoginID="sa"

SET DbPassword="@2008sql"

First, it indicates that the server does NOT have the "SA" login disabled... and it must. NO ONE should ever use the "SA" login.

Second, it's insane to store any password in clear text.

You need to convince everyone that ONLY Windows Authentication should be used for such a thing and that you should NEVER store passwords in the clear.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)