Return output from OSQL call to variable
-
In DOS, is it possible to return the result of an OSQL call to a variable which would then be used in another call?
eg osql /q "Select process_num from processess"
Within Query Analyser this would return a value of say 200.
I know you can pipe the result to an output file but I would much rather trap the output here and then use it in my next query, which may be
osql /q "Select process_info from process_table where process_num = variable"
thanks in advance
-
I know a way. It ain't pretty. ("Don't look, Ethel!!!!")
- Create a temp table with one long VARCHAR column.
- Create a @sql VARCHAR variable and set that string equal to the query string. (I.e., SELECT process_info...")
- Create a @cmd VARCHAR variable and set up your isql/osql call. (I.e., osql /q). I almost always use 'isql'. Be sure to specify which server you're running against (-S), the relevant security info (-E or -U{login} -P{pwd}), and it wouldn't hurt to include -h-1, which turns off headers. You'll need to include the @sql string, prefaced with -Q and surrounded by double-quotes. E.g.
SELECT @sql = 'SET NOCOUNT ON SELECT COUNT (1) FROM foo'
SELECT @cmd = 'osql -E -Sfoo_server -dfoo_db -h-1 -Q" + @sql + '"'
- The SET NOCOUNT ON removes some of the detritus from the output that you don't really need.
- Run the osql command using 'xp_cmdshell' and capture the results in your temp table, i.e.
INSERT INTO #temp1 EXEC master..xp_cmdshell @cmd
- Delete any blank or NULL rows from the table.
- All data is returned in characters, so you may need to trim and convert the returned values.
-
Ugh
-
I forgot to mention I am making the OSQL call from a .bat or .cmd file. How do I capture the output and feed it in to another OSQL call as a variable?
-
Yer right, Steve, she ain't much to look at.
Right now, though, I'm concerned that I just misread the whole requirement. I assumed he wanted to know how to do that from T-SQL, and right there in big bold letter, he said, "In DOS".
I'll wait for Hammer to clarify before I suggest anything even uglier.
If it's not too late, I could also suggest that maybe a linked server might save the need for an osql call.
-
Sorry, Hammer, my fault. I saw "Query Analyzer" in your first post, and that's all I saw.
Maybe you can try using the NT-shell "FOR" command with the "text parser" option. I think the syntax would look something like:
FOR /F %i IN ('isql -h-1 -E -d{dbname} -S{servername} -Q"SET
NOCOUNT ON SELECT {etc.}"') DO @echo %i
I include the SET NOCOUNT to remove the feedback about the number of rows affected.
Instead of @echo, substitute the command of your choice. The result from the first isql call is trapped in the @i variable.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply