Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1328
  • Last Modified:

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

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
bluegrasscell
Asked:
bluegrasscell
  • 5
  • 4
1 Solution
 
Meir RivkinFull stack Software EngineerCommented:
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
 
Meir RivkinFull stack Software EngineerCommented:
is this line not working for you?

>> for /f "tokens=*" %%A in ('type %TmpFile%') do set counter=%%A
0
 
bluegrasscellAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
bluegrasscellAuthor Commented:
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
 
Meir RivkinFull stack Software EngineerCommented:
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
 
bluegrasscellAuthor Commented:
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
 
bluegrasscellAuthor Commented:
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
 
Meir RivkinFull stack Software EngineerCommented:
i missed that one totally, good for you
0
 
bluegrasscellAuthor Commented:
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now