Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Location
    Montreal, QC
    Posts
    3

    Cool Unanswered: Help! ISQL and DOS batch files

    Hi all,

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

    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!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Aug 2004
    Location
    Montreal, QC
    Posts
    3
    Thx, Pat.

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

    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.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jul 2004
    Posts
    52
    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/...dodbScalar.txt

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    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
    Now 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

  7. #7
    Join Date
    Aug 2004
    Location
    Montreal, QC
    Posts
    3
    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!!

  8. #8
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Not really elegant, but running
    Code:
    isql -E -d firstDB -S ServerA -Q "select 'set MyVariable='+convert(varchar(15), max(load_date), 111) from mydates" -o myBat.bat
     
    call myBat.bat
    F. Celaia
    DBA Sybase/DB2/Oracle/MS-SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •