Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-04-09
9
Medium Priority
?
1,315 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

My purpose is to describe the basic concepts of virtual memory as implemented in a modern Windows-based operating system. I will also describe the problems inherent in older systems and how virtual memory solves them. The dark ages - before virtu…
Have you ever had a hard drive that you can't boot into, but need to change the registry? Here is the solution! This article guides you through accessing and editing a registry of a non-primary drive. To read registry information on a non-prim…
This video Micro Tutorial explains how to clone a hard drive using a commercial software product for Windows systems called Casper from Future Systems Solutions (FSS). Cloning makes an exact, complete copy of one hard disk drive (HDD) onto another d…
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

722 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