Solved

Outputting to a .txt file using sqlcmd and reading content into batch file variable

Posted on 2012-04-09
9
1,264 Views
Last Modified: 2012-04-15
Hello,

I'm currently working on a problem that requires me to  read a value from a database, output it to a .txt file and read that value from the file into a variable in a batch file. I've confirmed that I can successfully read content from a .txt file that I've manually created and store the value in a variable. However, when I generate the file using a sqlcmd statement, I'm unable to read the value from the file and store it in the variable. Here's the content of my batch file so far:

@echo off

set TmpFile=D:\data_extract\counterval.txt

sqlcmd -E -S servername\instance,port -d batch_stuff-Q"SET NOCOUNT ON; SELECT REPLACE(LTRIM(CounterValue),' ','') FROM batch_stuff..Counters WHERE CounterID = 1" -b -w 10 -h-1 -o "%TmpFile%"

set InFile1=D:\data_extract\get_data.sql
set InFile2=D:\data_extract\update_counter.sql
set OutFile=D:\data_extract\data\BGM01_
set CurrDate=%date:~-4,4%%date:~-7,2%%date:~-10,2%
set CurrTime=%time:~0,2%%time:~3,2%%time:~6,2%%time:~9,2%
set OsqlParams1=-E -S servername\instance,port -d database -h-1 -n -w30 -i 
set OsqlParams2=-E -S servername\instance,port -d batch_stuff -h-1 -n -w30 -i  

if not exist "%TmpFile%" (
    echo %TmpFile% not found.
    echo Press any key to quit ...
    pause > nul
    goto :EOF
)

if not exist "%InFile%" (
    echo %InFile% not found.
    echo Press any key to quit ...
    pause > nul
    goto :EOF
)

for /f "tokens=*" %%A in ('type %TmpFile%') do set counter=%%A

sqlcmd %OsqlParams1% "%InFile1%" -o "%OutFile%_D%CurrDate%_T%CurrTime%_0%counter%"

sqlcmd %OsqlParams2% "%InFile2%" 

Open in new window


Any help would be much appreciated.
0
Comment
Question by:bluegrasscell
  • 5
  • 4
9 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 37823490
can u make sure that after writing the var into the file, you released the file handle?
what i mean is that the file might be still open and that's why u couldn't read it.
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 37823500
is this line not working for you?

>> for /f "tokens=*" %%A in ('type %TmpFile%') do set counter=%%A
0
 

Author Comment

by:bluegrasscell
ID: 37823547
sedgwick,

The line you mentioned seems to work fine when I manually create a .txt file and try to read from it, but not when I use sqlcmd to output the file. I'm assuming there's an issue with how sqlcmd creates the .txt file that is causing that line not to work, but I can't seem to figure out what that is.
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:bluegrasscell
ID: 37823626
I believe the issue might be that the sqlcmd statement is adding several blank lines in the output file. According to a previous answer to a question I posted about reading file input into a batch variable, the particular line below will not work when there are multiple lines in the file.

for /f "tokens=*" %%A in ('type %TmpFile%') do set counter=%%A

If this is the issue, I guess my next question is how to deal with removing the extra lines created by sqlcmd or how to read only the first line--which is the only line that contains an actual value--into the batch file variable. Does anyone out there have any idea how to accomplish either of these?
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 37823667
try run the sqlcmd without the other stuff and see if the file is accessible (open it, try to modify it etc).
try access the file from your batch by simply output to console its content (type %TmpFile%)
0
 

Author Comment

by:bluegrasscell
ID: 37824329
I did what you suggested and discovered that I can open the file, but I'm not able to modify and re-save it. Also, I was able to open it from the batch file to display the content.
Does the fact that I'm not able to modify and save the file indicate what you suggested earlier--that it may still be open when I try to read the content into a variable?
0
 

Accepted Solution

by:
bluegrasscell earned 0 total points
ID: 37827659
Just an update for anyone else that might see this...

I was able to fix my problem by simply adding a .txt extension to my output file from the second sqlcmd statement. That fixed every issue I've been having with this particular batch file process. It read the data in TmpFile.txt into a variable successfully and appended the value of that variable to the output file name. It even corrected an issue I was having with the output file being larger than it should have been.

Thanks sedgwick for the help though!
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 37827803
i missed that one totally, good for you
0
 

Author Closing Comment

by:bluegrasscell
ID: 37847958
I'm not sure if adding the .txt extension is the best solution or not, but it is the one that works for me currently. I'll be able to rename the file to drop that extension--if needed--and that is much simpler than figuring out how to generate the file without the extension to begin with.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The way I use Experts Exchange to assist me in analyzing and diagnosing a problem is I first enter a Verbose Question at Experts Exchange like: Office 2007 will hang when opening and saving files I then launch WordPad (any text editor will do) an…
The password reset disk is often mentioned as the best solution to deal with the lost Windows password problem. In Windows 2008, 7, Vista and XP, a password reset disk can be easily created. But besides Windows 7/Vista/XP, Windows Server 2008 and ot…
Windows 8 comes with a dramatically different user interface known as Metro. Notably missing from the new interface is a Start button and Start Menu. Many users do not like it, much preferring the interface of earlier versions — Windows 7, Windows X…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question