Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Running SQL in a vbs script

Status
Not open for further replies.

DrSeussFreak

Programmer
Joined
Feb 16, 2007
Messages
149
Location
US
I have a sql statement that I will be running, and I need to do 1 of 2 things.

1) Run this in a vb script and somehow find out the results (send an e-mail if "no rows selected" is listed after each select statement syaing all is ok) and (send an e-mail if it is not listed after each select saying all is not ok (and provide the actual output)).

2) Make a bat file that runs the sql script which spools a file, then the bat runs a vb script that reads the file and if it sees "no rows selected twice" it sends an e-mail saying all is ok, else it sends an e-mail with the rows that were pulled.

Code:
set pause off
set linesize 90

create table tspace_extents as select tablespace_name, bytes
from dba_free_space
/
create table table_next_extent
as select tablespace_name, table_name, next_extent from dba_tables
where owner not in ('SYS', 'SYSTEM')
/
create table index_next_extent
as select tablespace_name, index_name, next_extent from dba_indexes
where owner not in ('SYS', 'SYSTEM')
/

select table_name, A.tablespace_name, next_extent/1024/1024 NEXT_MEGS
,sum(trunc(bytes/next_extent)) EXTENTS
from table_next_extent A, tspace_extents B
where A.tablespace_name = B.tablespace_name
having sum(trunc(bytes/next_extent)) < 5
group by table_name, A.tablespace_name, next_extent/1024/1024 
/
select index_name, A.tablespace_name, next_extent/1024/1024 NEXT_MEGS
,sum(trunc(bytes/next_extent)) EXTENTS
from index_next_extent A, tspace_extents B
where A.tablespace_name = B.tablespace_name
having sum(trunc(bytes/next_extent)) < 5
group by index_name, A.tablespace_name, next_extent/1024/1024 
/

drop table tspace_extents 
/
drop table table_next_extent
/
drop table index_next_extent
/
set pause on
 
the good output is

Table created.


Table created.


Table created.


no rows selected


no rows selected


Table dropped.


Table dropped.


Table dropped.

FYI
 
Never mind, I just figured this one out, but thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top