re SQL Server Database queries 
Author Message
 re SQL Server Database queries

I am trying to access the metadata of  mainly MicroSoft SQL server 6.5 /7
databases for an analysis tool.   In DBConnection there are methods called
'procedure: owner: name:' and 'procedures', the second one is the most
obvious one that I require to start with as I require all the stored
procedures relating to the database, yet when I run it against a database
with 172 stored porcedures the recsult set is empty, is there  a bug in the
method? A similar thing happens with the first method.

    If I run the  following 'select name from sysobjects where type='P',
when attached to the database the whole of the list of procedures is
returned.

    I subsequent want to query the database to get both the tables and
fields that each stored procedure uses.

    As anyone done anything similar, preferably using the base methods in
the image, otherwise I will have to write my own.

    Ron



Wed, 14 Apr 2004 04:51:27 GMT  
 re SQL Server Database queries
Ron,

Quote:
> I am trying to access the metadata of  mainly MicroSoft SQL server 6.5 /7
> databases for an analysis tool.   In DBConnection there are methods called
> 'procedure: owner: name:' and 'procedures', the second one is the most
> obvious one that I require to start with as I require all the stored
> procedures relating to the database, yet when I run it against a database
> with 172 stored porcedures the recsult set is empty, is there  a bug in
the
> method? A similar thing happens with the first method.

This could way off base, but, what you are describing sounds similar to the
key field methods that turn out to be (IIRC) dependent on things that are
not included in Dolphin.  OA (well, probably Intuitive at that point) was
working on a RDB-OO mapping tool, and there are some dangling references to
it.

Quote:
>     If I run the  following 'select name from sysobjects where type='P',
> when attached to the database the whole of the list of procedures is
> returned.

Interesting - thanks for passing this along.

Have a good one,

Bill

--
Wilhelm K. Schwab, Ph.D.



Wed, 14 Apr 2004 05:57:11 GMT  
 re SQL Server Database queries
Bill
    thanks for the info.

    Here is a small workspace item that you can obtain the stored procedures
that have the tables dependent  on them.

    |a b s c|
a := (DBConnection new)open.
s := a tables.
1 to: s size do:[:x |
b := a query:('select o.name,
  type = substring(v.name, 1, 16)
   from sysobjects o, master.dbo.spt_values v, sysdepends d,
    sysusers s
   where o.id = d.id and o.name not like ''odbc#%''
    and o.sysstat & 0xf = v.number and v.type = ''O''
    and d.depid = (select id from sysobjects where name=''',(s at: x),''')
    and o.uid = s.uid
    and v.name not like ''trigger%''').
b first.
c := b at:1]

    I cannot get down to field level, but at least I can arrange to have the
application interrogate the various databases, to get the various
information automatically.

      I am turning it into a method for use by my application, which will
show the links betrween the various tables,stored procedures and the front
ends on the systems that we work on in the office.  They do not use
Smalltalk there but lets show them how quick you can get a decent
application out..

Ron

Quote:

>Ron,

>> I am trying to access the metadata of  mainly MicroSoft SQL server 6.5 /7
>> databases for an analysis tool.   In DBConnection there are methods
called
>> 'procedure: owner: name:' and 'procedures', the second one is the most
>> obvious one that I require to start with as I require all the stored
>> procedures relating to the database, yet when I run it against a database
>> with 172 stored porcedures the recsult set is empty, is there  a bug in
>the
>> method? A similar thing happens with the first method.

>This could way off base, but, what you are describing sounds similar to the
>key field methods that turn out to be (IIRC) dependent on things that are
>not included in Dolphin.  OA (well, probably Intuitive at that point) was
>working on a RDB-OO mapping tool, and there are some dangling references to
>it.

>>     If I run the  following 'select name from sysobjects where type='P',
>> when attached to the database the whole of the list of procedures is
>> returned.

>Interesting - thanks for passing this along.

>Have a good one,

>Bill

>--
>Wilhelm K. Schwab, Ph.D.




Sat, 17 Apr 2004 03:26:14 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. TCL, ODBC and SQL Server 7.0 - Database server crashing

2. A Query about SQL and databases

3. SQL queries on AS400 database

4. Access database query with SQL vi under LabVIEW 6.1

5. FYI Regarding Multiple MS SQL Server Databases

6. Database module - MS-SQL Server [Q]

7. Exporting SQL Server database

8. How can we read SQL Server database tables?

9. Opening a connection for a Microsoft SQL Server database

10. Windowbuilder PRO, acessing SQL Server databases

11. clipper query conver to sql query

12. SQL Database creation (SQL Anywhere 5)

 

 
Powered by phpBB® Forum Software