Solved

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

Posted on 2012-04-09
9
1,251 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

by Nathan Brom/Bromy2004 Introduction There are numerous websites out there for any different type of program you can imagine.  Of those, you'll need to decide which ones are legitimate and aren't trying to steal your money or infect your comput…
Ever notice how you can't use a new drive in Windows without having Windows assigning a Disk Signature?  Ever have a signature collision problem (especially with Virtual Machines?)  This article is intended to help you understand what's going on and…
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…

803 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