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