Solved

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

Posted on 2012-04-09
9
1,232 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
 

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Many admins will agree: WSUS is is a nice invention but using it on the client side when updating a newly installed computer is still time consuming as you have to do several reboots and furthermore, the procedure of installing updates, rebooting an…
This is a little timesaver I have been using for setting up Microsoft Small Business Server (SBS) in the simplest possible way. It may not be appropriate for every customer. However, when you get a situation where the person who owns the server is i…
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…
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now