I got a requirement on the batch file script but I am new to write the Batch file script.
Here is the requirement:
- I have Application and Database servers.
- Batch file run on Application server.
- connect to Database server.
- run the query present in the batch file in the table of the particular database in DB server.
- output should be exported into a .csv(excel) file
- .csv file should be transferred to App server folder
- if possible maintain the log in app server on each action.
I have tried much to write the script from so many days but I am not able to do it even I have some script found through google.
@ECHO OFF <br/>
SQLCMD /? > nul 2> nul <br/>
IF ERRORLEVEL 1 ECHO.SQLCMD not found on this machine & PAUSE & GOTO :eof <br/><br/>
SET SQLCmdLine=SQLCMD -b -w400<br/>
REM use something like the next line for NT authentication; second line for SQL authentication<br/>
REM for NT SET SQLCmdLine=%SQLCmdLine% -E -S "YourServerName"<br/>
REM for SA SET SQLCmdLine=%SQLCmdLine% -U "YourSQLUserName" -P "YourSQLPassword" -S "YourServerName" <br/>
REM SET Database=YourDatabaseName <br/>
REM Matrix is my database name <br/>
SET Database=Matrix<br/><br/>
REM I will use NT Authentication for this example<br/>
SET SQLCmdLine=%SQLCmdLine% -E -S "(local)"<br/>
SET TestSQLCmdLine=%SQLCmdLine% -h-1 -d "%Database%"<br/><br/>
REM this will get the SQL version
%TestSQLCmdLine% -Q "SELECT @@VERSION" | FINDSTR /v "rows affected"
REM this will run a SQL script <br/>
%TestSQLCmdLine% -i "YourSQLScript.SQL" <br/>
PAUSE <br/>
REM -------------------------------------------------------------------------------<br/>
REM Generic script for exporting data to file from SQL Server using a SQL query.<br/>
REM The resulting file will be tab separated with newline as the row delimiter.<br/>
REM A log file is generated and kept in case of an error or when in debug mode.<br/>
REM See command syntax for details.<br/>
REM<br/>
REM History:<br/>
REM 20120327 Lars Rönnbäck CREATED<br/>
REM -------------------------------------------------------------------------------<br/>
:constants<br/>
SET myCodePage=ACP<br/>
:variables<br/>
SET theQuery=SELECT [D-U-N-S Number], [Company Name], [Street Address Line 1], [Street Address Line 2], [Street Address Line 3], [Street Address Line 4], Town, [County (Actual)], [Full Postcode], [Telephone Number] FROM B2B_Jan13.dbo.DATA_HQ_1_Site_Level <br/>
SET theFile=C:\B2B_Matching_Automation\Temp_Files\Sites.csv<br/>
SET theServer=localhost<br/>
SET theDebug=%~4<br/>
SET /a aRandomNumber=%random%%%1000<br/>
FOR /F "usebackq tokens=1-7* delims=.:/,- " %%a IN (`ECHO %DATE%_%TIME%`) DO (SET myStartTime=%%a%%b%%c%%d%%e%%f%%g)<br/>
SET myColumnQuery="select top 0 * into [#columns_%myStartTime%_%aRandomNumber%] from (%theQuery%) q; <br/>
select stuff((select char(9) + c.name from tempdb.sys.columns c where c.object_id = t.object_id order by c.column_id for XML path(''), type).value('.', 'varchar(max)'), 1,1,'') AS Header from tempdb.sys.tables t where t.name like '#columns_%myStartTime%_%aRandomNumber%%%'" <br/>
SET myHeaderFile=%theFile%.%aRandomNumber%.header<br/>
SET myDataFile=%theFile%.%aRandomNumber%.data<br/>
SET myLogFile=%theFile%.%myStartTime%_%aRandomNumber%.log<br/>
:checks<br/>
IF "%theQuery%"=="" (<br/>
GOTO syntax<br/>
) <br/><br/>
----------<br/>
IF "%theFile%"=="" (<br/>
GOTO syntax<br/>
) <br/>
IF "%theServer%"=="" ( <br/>
SET theServer=%COMPUTERNAME%<br/>
)<br/>
:information<br/>
ECHO Start Time: %myStartTime% >> "%myLogFile%" 2>&1<br/>
ECHO Random Number: %aRandomNumber% >> "%myLogFile%" 2>&1<br/>
ECHO File: %theFile% >> "%myLogFile%" 2>&1<br/>
ECHO Server Name: %theServer% >> "%myLogFile%" 2>&1<br/>
ECHO Query: >> "%myLogFile%" 2>&1<br/>
ECHO. >> "%myLogFile%" 2>&1<br/>
ECHO %theQuery% >> "%myLogFile%" 2>&1<br/>
:export<br/>
BCP %myColumnQuery% queryout "%myHeaderFile%" -T -S "%theServer%" -a 65535 -c -C %myCodePage% -q >> "%myLogFile%" 2>&1<br/>
IF ERRORLEVEL 1 GOTO error<br/>
BCP "%theQuery%" queryout "%myDataFile%" -T -S "%theServer%" -a 65535 -c -C %myCodePage% -q >> "%myLogFile%" 2>&1<br/>
IF ERRORLEVEL 1 GOTO error<br/>
ECHO. >> "%myLogFile%" 2>&1<br/>
ECHO Merging files... >> "%myLogFile%" 2>&1<br/>
ECHO. >> "%myLogFile%" 2>&1<br/>
COPY /A "%myHeaderFile%" + "%myDataFile%" "%theFile%" /B /Y >> "%myLogFile%" 2>&1<br/>
IF ERRORLEVEL 1 GOTO error<br/>
:cleanup<br/>
DEL "%myHeaderFile%" >NUL 2>&1<br/>
IF ERRORLEVEL 1 GOTO error<br/>
DEL "%myDataFile%" >NUL 2>&1<br/>
IF ERRORLEVEL 1 GOTO error<br/>
IF /I NOT [%theDebug%]==[Y] (<br/>
DEL "%myLogFile%"<br/>
)<br/>
IF ERRORLEVEL 1 GOTO error<br/>
GOTO end<br/>
:error<br/>
ECHO <br/>
ECHO ERROR: An export error has occured!<br/>
IF NOT [%myLogFile: =%]==[] (<br/>
ECHO Details can be found in:<br/>
ECHO %myLogFile%<br/>
)<br/>
ECHO <br/>
EXIT /B 1<br/>
:syntax<br/>
ECHO.<br/>
ECHO SYNTAX: %0 "sql query" "output file" [server] [Y]<br/>
ECHO -------------------------------------------------------------------------------<br/>
ECHO You must specify an SQL query and an output file name in which the results of <br/>
ECHO the query will be stored. Specifying a server is optional and defaults to the <br/>
ECHO server you are executing on. If a fourth argument is given as Y a log file of<br/>
ECHO the command outputs will be saved in the same folder as the output file.<br/>
ECHO -------------------------------------------------------------------------------<br/>
