using cmd file to execute sqlcmd, oSQL

  • 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

  • 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]

  • 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.

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

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

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