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

Help writing a querry

I need some help with the following
I have a view as follow
date ValueIndex Value
20060131 2001 0.0455
20060130 2001 0.0455
20060129 2001 0.0454
20060128 2001 0.0453
20060127 2001 0.0453
20060126 2001 0.0452
20060125 2001 0.0452
20060124 2001 0.0451
20060123 2001 0.0451
20060122 2001 0.045
I would like to import a file with the date and the ValueIndex once I import
that file I would like to do a look up and get the value.
Is this possiable
Thanks
Chris>> I would like to import a file with the date and the ValueIndex once I
Yes, it is possible. What exactly do you find it difficult in doing it?
Importing the file to a table? Or writing the query to get the value?
Anith|||Anith,
Both -- I have no idea where to start
Please HELP!!!!!
"Anith Sen" wrote:

> Yes, it is possible. What exactly do you find it difficult in doing it?
> Importing the file to a table? Or writing the query to get the value?
> --
> Anith
>
>|||First of all, to get the data from a file to an SQL table, you have several
options.
Perhaps the easiest one is using DTS. Check out the topic Data
Transformation Services in the SQL Server Books Online. A simple interface
in SQL Enterprise Manager can get the job done.
Alternatively, you can use command line utilities like BCP or use BULK
INSERT to get the data from a file to the table. Again, you have all the
information needed in SQL Server Books Online.
To extract the desired data from an SQL table, you issue a query, generally
in the form of a SELECT statement. Given that your table has the columns
date_column, value_index & value with date_column as the primary key, you
could retrieve the value by using a SELECT statement like:
SELECT value FROM tbl WHERE date_column = @.param ;
-- where @.param is the parameter passed to the query to look up the desired
value.
All details needed for writing SQL can be found in SQL Server Books Online.
Anith|||Anith,
Thank you for your helpfull information.
After looking at the db I do not need to import the table it is already in
the db. I need to add a column to the table/view and populate that column
with the value base on the date and valueindex combination
Thank You
"Anith Sen" wrote:

> First of all, to get the data from a file to an SQL table, you have severa
l
> options.
> Perhaps the easiest one is using DTS. Check out the topic Data
> Transformation Services in the SQL Server Books Online. A simple interface
> in SQL Enterprise Manager can get the job done.
> Alternatively, you can use command line utilities like BCP or use BULK
> INSERT to get the data from a file to the table. Again, you have all the
> information needed in SQL Server Books Online.
> To extract the desired data from an SQL table, you issue a query, generall
y
> in the form of a SELECT statement. Given that your table has the columns
> date_column, value_index & value with date_column as the primary key, you
> could retrieve the value by using a SELECT statement like:
> SELECT value FROM tbl WHERE date_column = @.param ;
> -- where @.param is the parameter passed to the query to look up the desire
d
> value.
> All details needed for writing SQL can be found in SQL Server Books Online
.
> --
> Anith
>
>|||>> I need to add a column to the table/view
Look at ALTER TABLE .. ADD .. statement in SQL Server Books Online.
You can use an UPDATE statement, something along the lines of:
UPDATE tbl
SET value = ( SELECT t1.value FROM source_tbl t1
WHERE t1.date_col = tbl.date_col
AND t1.value_idx = tbl.value_idx )
WHERE EXISTS ( SELECT * FROM source_tbl t1
WHERE t1.date_col = tbl.date_col
AND t1.value_idx = tbl.value_idx );
For more details, lookup the topic UPDATE in SQL Server Books Online.
Anith|||A couple of links that may also help you understand the SQL...
http://www.w3schools.com/sql/sql_intro.asp
http://sqlzoo.net/
There are many others, but these both provide general walkthroughs of the
basics.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:esd6BOLPGHA.1216@.TK2MSFTNGP14.phx.gbl...
> Look at ALTER TABLE .. ADD .. statement in SQL Server Books Online.
>
> You can use an UPDATE statement, something along the lines of:
> UPDATE tbl
> SET value = ( SELECT t1.value FROM source_tbl t1
> WHERE t1.date_col = tbl.date_col
> AND t1.value_idx = tbl.value_idx )
> WHERE EXISTS ( SELECT * FROM source_tbl t1
> WHERE t1.date_col = tbl.date_col
> AND t1.value_idx = tbl.value_idx );
> For more details, lookup the topic UPDATE in SQL Server Books Online.
> --
> Anith
>