Help! ISQL and DOS batch files

Hi all,

Can anyone help me?? I'm just a newb :D

Please consider the following:

I need to be able to query a db on server A, in a batch file, return the result (DateTime value) into a variable, and then use that date as a parameter in a query that I will query on server b.

I have the following code:

isql -E -d firstDB -S ServerA -Q "select max(load_date) from mydates"

How would I pipe the results of the above query into a variable?

I cannot create a linked server between the two servers. (Permissions)

Thanks in advance!My first suggestion would be to use either DTS or Perl instead of batch files. They simplify problems like this a bunch.

My next suggestion would be to use OSQL.EXE if you must use a batch file. It is better suited for many reasons than ISQL.EXE is.

You can send the OSQL.EXE output to a text file using the -o parameter. Once you get the data into a text file, you'll need to find a way to harvest it for use in the next query... This is where a scripting language like what exists in DTS or Perl really helps.

-PatP|||Thx, Pat.

I am also familiar with OSQL, but I've never used DTS. Looks like I have some reading to do this weekend :p

In the meantime, it's hokey, but I think I'm gonna BCP the data into the pubs db on the server as a temporary solution.

Thanks, I wouldn't have thought of it.|||If all you need is 1 parameter, then this would do:

osql -E -d firstDB -S ServerA -Q "select 'select * from other_server_table where load_date = ' + (select '''' + convert(char(10), max(load_date), 101) + '''' from mydates)" -o script.SQL

osql -E -d secondDB -S ServerB -i script.SQL|||Can you use OPENQUERY or is that also restricted? Can you use sp_Oacreate? See this link for an example of retrieving a single value with an ADODB connection and T-SQL.

http://www.davidpenton.com/testsite/scratch/dbo.sp_ExecuteAdodbScalar.txt|||If all you need is 1 parameter, then this would do:

osql -E -d firstDB -S ServerA -Q "select 'select * from other_server_table where load_date = ' + (select '''' + convert(char(10), max(load_date), 101) + '''' from mydates)" -o script.SQL

osql -E -d secondDB -S ServerB -i script.SQLNow that's just plain deviant ;)

I thought I was the only one that did perverse things like that, although I've been known to create whole scripts (breaking the 8K limit was my biggest challenge) that way!

It still isn't something I'd try to teach a newcomer, but it is a great thing to have lurking in one's bag of tricks.

-PatP|||rdjabarov, that was wild!! :) That is exactly the approach I am going to use! My query was a little too long, but I just seperated it into columns, and used "" as a col seperator. And voil! This way, I can also archive my query, as I try to avoid hard coding wherever possible. I'll have to comment a lot, but it's a whole lot less hokey than what I was planning to do!!!

vaxman- I cannot use sp_Oacreate - permissions. :(

Pat - Thanks for the suggestions, I'm still going to look into DTS, as it looks on first glance as a pretty powerful tool.

WooHoo! Thanks everyone!!|||Not really elegant, but running
isql -E -d firstDB -S ServerA -Q "select 'set MyVariable='+convert(varchar(15), max(load_date), 111) from mydates" -o myBat.bat

call myBat.batsql