Retrieving SQL Server SPs Return Recordset Column Names

Retrieving SQL Server SPs Return Recordset Column Names

Post by Maher K. Al-Jendas » Wed, 23 Jul 2003 09:20:25



Hello All,

Is there anyway to retrieve column names that return from
SQL Server stored procedure via ADO, or ADOX? For example,
if I have the following procedure:

CREATE PROC GetEmployees

AS

   SELECT emp_id, emp_name
   FROM Employees

GO

Is it possible to do something in ADO, or ADOX that
returns names of columns (i.e emp_id, and emp_name)??

Thanks in advance

Maher K. Al-Jendasi

 
 
 

Retrieving SQL Server SPs Return Recordset Column Names

Post by Maher K. Al-Jendas » Wed, 23 Jul 2003 11:02:03


Hello Dean,

This mean that I have to execute the procedure and know the names of
returned fields through Fields collection. This method have some performance
issues, such as it must be execute first, and what if this procedure return
say 100000 records???

What I need is knowing the names of returned columns directly through ADO,
or ADOX, or if there a SQL Server procedure that can do this for me.

Regards,

Maher K. Al-Jendasi


Quote:> Dim RS as ADODB.Recordset
> ...
> ...
> ...
> RS(0).name should give you "emp_id"
> and RS(1).name will give you "emp_name".

> Default collection of ADODB.Recordset is Fields from which you can get the

number of fields in the recordset:
> RS.fields.count

> --
> Dean Savovic
> www.teched.hr




> > Hello All,

> > Is there anyway to retrieve column names that return from
> > SQL Server stored procedure via ADO, or ADOX? For example,
> > if I have the following procedure:

> > CREATE PROC GetEmployees

> > AS

> >    SELECT emp_id, emp_name
> >    FROM Employees

> > GO

> > Is it possible to do something in ADO, or ADOX that
> > returns names of columns (i.e emp_id, and emp_name)??

> > Thanks in advance

> > Maher K. Al-Jendasi



 
 
 

Retrieving SQL Server SPs Return Recordset Column Names

Post by Dean Savovi » Wed, 23 Jul 2003 09:56:20


Dim RS as ADODB.Recordset
...
...
...
RS(0).name should give you "emp_id"
and RS(1).name will give you "emp_name".

Default collection of ADODB.Recordset is Fields from which you can get the number of fields in the recordset:
RS.fields.count

--
Dean Savovic
www.teched.hr


> Hello All,

> Is there anyway to retrieve column names that return from
> SQL Server stored procedure via ADO, or ADOX? For example,
> if I have the following procedure:

> CREATE PROC GetEmployees

> AS

>    SELECT emp_id, emp_name
>    FROM Employees

> GO

> Is it possible to do something in ADO, or ADOX that
> returns names of columns (i.e emp_id, and emp_name)??

> Thanks in advance

> Maher K. Al-Jendasi


 
 
 

Retrieving SQL Server SPs Return Recordset Column Names

Post by Dean Savovi » Wed, 23 Jul 2003 10:38:53


If your procedure is as simple as this one the you can use:

create table Employees
(emp_id int identity(1,1)
, emp_name char(30))

go

CREATE PROC GetEmployees

AS

   SELECT emp_id, emp_name
   FROM Employees

GO

insert into Employees (emp_name)
select 'John'
union
select 'Henry'

select
ltrim(rtrim(substring(text, charindex(' ', text, charindex('select' , text)), charindex('from', text) - charindex(' ', text,
charindex('select' , text)))))
from syscomments
where object_name(id) = 'GetEmployees'

drop proc GetEmployees

drop table Employees

Otherwise you should keep in mind that system table sysomments for stored procedures with more than 4000 characters has more than
one row (one row
for each 4000 chars).

You could try the firs method but if you don't want to get all of the records first execute SQL statement "set rowcount 1"
with your command object (ADODB.Command) and then execute the stored procedure which shoul return only one record.

Then use
RS(0).name and RS(1).name

--
Dean Savovic
www.teched.hr


> Hello Dean,

> This mean that I have to execute the procedure and know the names of
> returned fields through Fields collection. This method have some performance
> issues, such as it must be execute first, and what if this procedure return
> say 100000 records???

> What I need is knowing the names of returned columns directly through ADO,
> or ADOX, or if there a SQL Server procedure that can do this for me.

> Regards,

> Maher K. Al-Jendasi



> > Dim RS as ADODB.Recordset
> > ...
> > ...
> > ...
> > RS(0).name should give you "emp_id"
> > and RS(1).name will give you "emp_name".

> > Default collection of ADODB.Recordset is Fields from which you can get the
> number of fields in the recordset:
> > RS.fields.count

> > --
> > Dean Savovic
> > www.teched.hr



> > > Hello All,

> > > Is there anyway to retrieve column names that return from
> > > SQL Server stored procedure via ADO, or ADOX? For example,
> > > if I have the following procedure:

> > > CREATE PROC GetEmployees

> > > AS

> > >    SELECT emp_id, emp_name
> > >    FROM Employees

> > > GO

> > > Is it possible to do something in ADO, or ADOX that
> > > returns names of columns (i.e emp_id, and emp_name)??

> > > Thanks in advance

> > > Maher K. Al-Jendasi


 
 
 

Retrieving SQL Server SPs Return Recordset Column Names

Post by SriSam » Wed, 23 Jul 2003 11:21:12


Check out the SET FMTONLY ON option in SQL Server. This lets you get only
the metadata and not any rows. Remember to make it OFF immediately,
otherwise none of your queries will return any data :-)
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp



> Hello Dean,

> This mean that I have to execute the procedure and know the names of
> returned fields through Fields collection. This method have some
performance
> issues, such as it must be execute first, and what if this procedure
return
> say 100000 records???

> What I need is knowing the names of returned columns directly through ADO,
> or ADOX, or if there a SQL Server procedure that can do this for me.

> Regards,

> Maher K. Al-Jendasi



> > Dim RS as ADODB.Recordset
> > ...
> > ...
> > ...
> > RS(0).name should give you "emp_id"
> > and RS(1).name will give you "emp_name".

> > Default collection of ADODB.Recordset is Fields from which you can get
the
> number of fields in the recordset:
> > RS.fields.count

> > --
> > Dean Savovic
> > www.teched.hr



> > > Hello All,

> > > Is there anyway to retrieve column names that return from
> > > SQL Server stored procedure via ADO, or ADOX? For example,
> > > if I have the following procedure:

> > > CREATE PROC GetEmployees

> > > AS

> > >    SELECT emp_id, emp_name
> > >    FROM Employees

> > > GO

> > > Is it possible to do something in ADO, or ADOX that
> > > returns names of columns (i.e emp_id, and emp_name)??

> > > Thanks in advance

> > > Maher K. Al-Jendasi


 
 
 

Retrieving SQL Server SPs Return Recordset Column Names

Post by Maher K. Al-Jendas » Wed, 23 Jul 2003 12:18:36


Hello Dean,

Thanks alot for this great solution, unfortunatly it can't be upplied to
more complex SPs like the one that have join (unfortuantly also, most of my
stored procedures are complex)

Regards,

Maher K. Al-Jendasi


Quote:> If your procedure is as simple as this one the you can use:

> create table Employees
> (emp_id int identity(1,1)
> , emp_name char(30))

> go

> CREATE PROC GetEmployees

> AS

>    SELECT emp_id, emp_name
>    FROM Employees

> GO

> insert into Employees (emp_name)
> select 'John'
> union
> select 'Henry'

> select
> ltrim(rtrim(substring(text, charindex(' ', text, charindex('select' ,

text)), charindex('from', text) - charindex(' ', text,
Quote:> charindex('select' , text)))))
> from syscomments
> where object_name(id) = 'GetEmployees'

> drop proc GetEmployees

> drop table Employees

> Otherwise you should keep in mind that system table sysomments for stored

procedures with more than 4000 characters has more than
Quote:> one row (one row
> for each 4000 chars).

> You could try the firs method but if you don't want to get all of the

records first execute SQL statement "set rowcount 1"
Quote:> with your command object (ADODB.Command) and then execute the stored

procedure which shoul return only one record.

> Then use
> RS(0).name and RS(1).name

> --
> Dean Savovic
> www.teched.hr




- Show quoted text -

> > Hello Dean,

> > This mean that I have to execute the procedure and know the names of
> > returned fields through Fields collection. This method have some
performance
> > issues, such as it must be execute first, and what if this procedure
return
> > say 100000 records???

> > What I need is knowing the names of returned columns directly through
ADO,
> > or ADOX, or if there a SQL Server procedure that can do this for me.

> > Regards,

> > Maher K. Al-Jendasi



> > > Dim RS as ADODB.Recordset
> > > ...
> > > ...
> > > ...
> > > RS(0).name should give you "emp_id"
> > > and RS(1).name will give you "emp_name".

> > > Default collection of ADODB.Recordset is Fields from which you can get
the
> > number of fields in the recordset:
> > > RS.fields.count

> > > --
> > > Dean Savovic
> > > www.teched.hr



> > > > Hello All,

> > > > Is there anyway to retrieve column names that return from
> > > > SQL Server stored procedure via ADO, or ADOX? For example,
> > > > if I have the following procedure:

> > > > CREATE PROC GetEmployees

> > > > AS

> > > >    SELECT emp_id, emp_name
> > > >    FROM Employees

> > > > GO

> > > > Is it possible to do something in ADO, or ADOX that
> > > > returns names of columns (i.e emp_id, and emp_name)??

> > > > Thanks in advance

> > > > Maher K. Al-Jendasi


 
 
 

Retrieving SQL Server SPs Return Recordset Column Names

Post by Maher K. Al-Jendas » Wed, 23 Jul 2003 14:09:21


Hello SriSamp,

It's a perfect solution for simple procedures, but it did not work with
procedures that build and execute dynamoc SQL

Regards,

Maher K. Al-Jendasi


> Check out the SET FMTONLY ON option in SQL Server. This lets you get only
> the metadata and not any rows. Remember to make it OFF immediately,
> otherwise none of your queries will return any data :-)
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp



> > Hello Dean,

> > This mean that I have to execute the procedure and know the names of
> > returned fields through Fields collection. This method have some
> performance
> > issues, such as it must be execute first, and what if this procedure
> return
> > say 100000 records???

> > What I need is knowing the names of returned columns directly through
ADO,
> > or ADOX, or if there a SQL Server procedure that can do this for me.

> > Regards,

> > Maher K. Al-Jendasi



> > > Dim RS as ADODB.Recordset
> > > ...
> > > ...
> > > ...
> > > RS(0).name should give you "emp_id"
> > > and RS(1).name will give you "emp_name".

> > > Default collection of ADODB.Recordset is Fields from which you can get
> the
> > number of fields in the recordset:
> > > RS.fields.count

> > > --
> > > Dean Savovic
> > > www.teched.hr



> > > > Hello All,

> > > > Is there anyway to retrieve column names that return from
> > > > SQL Server stored procedure via ADO, or ADOX? For example,
> > > > if I have the following procedure:

> > > > CREATE PROC GetEmployees

> > > > AS

> > > >    SELECT emp_id, emp_name
> > > >    FROM Employees

> > > > GO

> > > > Is it possible to do something in ADO, or ADOX that
> > > > returns names of columns (i.e emp_id, and emp_name)??

> > > > Thanks in advance

> > > > Maher K. Al-Jendasi


 
 
 

Retrieving SQL Server SPs Return Recordset Column Names

Post by Dean Savovi » Wed, 23 Jul 2003 14:11:11


If setting

SET FMTONLY ON

before executing sp from ADO or ADOX doesn't work for you

then use:

SET ROWCOUNT 1
and then execute SP.

--
Dean Savovic
www.teched.hr


> Hello SriSamp,

> It's a perfect solution for simple procedures, but it did not work with
> procedures that build and execute dynamoc SQL

> Regards,

> Maher K. Al-Jendasi



> > Check out the SET FMTONLY ON option in SQL Server. This lets you get only
> > the metadata and not any rows. Remember to make it OFF immediately,
> > otherwise none of your queries will return any data :-)
> > --
> > HTH,
> > SriSamp
> > Please reply to the whole group only!
> > http://www32.brinkster.com/srisamp



> > > Hello Dean,

> > > This mean that I have to execute the procedure and know the names of
> > > returned fields through Fields collection. This method have some
> > performance
> > > issues, such as it must be execute first, and what if this procedure
> > return
> > > say 100000 records???

> > > What I need is knowing the names of returned columns directly through
> ADO,
> > > or ADOX, or if there a SQL Server procedure that can do this for me.

> > > Regards,

> > > Maher K. Al-Jendasi



> > > > Dim RS as ADODB.Recordset
> > > > ...
> > > > ...
> > > > ...
> > > > RS(0).name should give you "emp_id"
> > > > and RS(1).name will give you "emp_name".

> > > > Default collection of ADODB.Recordset is Fields from which you can get
> > the
> > > number of fields in the recordset:
> > > > RS.fields.count

> > > > --
> > > > Dean Savovic
> > > > www.teched.hr



> > > > > Hello All,

> > > > > Is there anyway to retrieve column names that return from
> > > > > SQL Server stored procedure via ADO, or ADOX? For example,
> > > > > if I have the following procedure:

> > > > > CREATE PROC GetEmployees

> > > > > AS

> > > > >    SELECT emp_id, emp_name
> > > > >    FROM Employees

> > > > > GO

> > > > > Is it possible to do something in ADO, or ADOX that
> > > > > returns names of columns (i.e emp_id, and emp_name)??

> > > > > Thanks in advance

> > > > > Maher K. Al-Jendasi


 
 
 

Retrieving SQL Server SPs Return Recordset Column Names

Post by Maher K. Al-Jendas » Wed, 23 Jul 2003 17:50:28


Hello Dean,

Yes I think it is the best solution.
Thanks for your time.

Regards,

Maher K. Al-Jendasi


> If setting

> SET FMTONLY ON

> before executing sp from ADO or ADOX doesn't work for you

> then use:

> SET ROWCOUNT 1
> and then execute SP.

> --
> Dean Savovic
> www.teched.hr




- Show quoted text -

> > Hello SriSamp,

> > It's a perfect solution for simple procedures, but it did not work with
> > procedures that build and execute dynamoc SQL

> > Regards,

> > Maher K. Al-Jendasi



> > > Check out the SET FMTONLY ON option in SQL Server. This lets you get
only
> > > the metadata and not any rows. Remember to make it OFF immediately,
> > > otherwise none of your queries will return any data :-)
> > > --
> > > HTH,
> > > SriSamp
> > > Please reply to the whole group only!
> > > http://www32.brinkster.com/srisamp



> > > > Hello Dean,

> > > > This mean that I have to execute the procedure and know the names of
> > > > returned fields through Fields collection. This method have some
> > > performance
> > > > issues, such as it must be execute first, and what if this procedure
> > > return
> > > > say 100000 records???

> > > > What I need is knowing the names of returned columns directly
through
> > ADO,
> > > > or ADOX, or if there a SQL Server procedure that can do this for me.

> > > > Regards,

> > > > Maher K. Al-Jendasi



> > > > > Dim RS as ADODB.Recordset
> > > > > ...
> > > > > ...
> > > > > ...
> > > > > RS(0).name should give you "emp_id"
> > > > > and RS(1).name will give you "emp_name".

> > > > > Default collection of ADODB.Recordset is Fields from which you can
get
> > > the
> > > > number of fields in the recordset:
> > > > > RS.fields.count

> > > > > --
> > > > > Dean Savovic
> > > > > www.teched.hr



> > > > > > Hello All,

> > > > > > Is there anyway to retrieve column names that return from
> > > > > > SQL Server stored procedure via ADO, or ADOX? For example,
> > > > > > if I have the following procedure:

> > > > > > CREATE PROC GetEmployees

> > > > > > AS

> > > > > >    SELECT emp_id, emp_name
> > > > > >    FROM Employees

> > > > > > GO

> > > > > > Is it possible to do something in ADO, or ADOX that
> > > > > > returns names of columns (i.e emp_id, and emp_name)??

> > > > > > Thanks in advance

> > > > > > Maher K. Al-Jendasi


 
 
 

Retrieving SQL Server SPs Return Recordset Column Names

Post by Maher K. Al-Jendas » Thu, 24 Jul 2003 16:59:11


Hello Dean,

What if my SP is an action query (for example DELETE ), if I execute it then
it will perform the action; causing data loss. In this case how I could know
whether it a action query or not???

Regards,

Maher K. Al-Jendasi


> If setting

> SET FMTONLY ON

> before executing sp from ADO or ADOX doesn't work for you

> then use:

> SET ROWCOUNT 1
> and then execute SP.

> --
> Dean Savovic
> www.teched.hr




- Show quoted text -

> > Hello SriSamp,

> > It's a perfect solution for simple procedures, but it did not work with
> > procedures that build and execute dynamoc SQL

> > Regards,

> > Maher K. Al-Jendasi



> > > Check out the SET FMTONLY ON option in SQL Server. This lets you get
only
> > > the metadata and not any rows. Remember to make it OFF immediately,
> > > otherwise none of your queries will return any data :-)
> > > --
> > > HTH,
> > > SriSamp
> > > Please reply to the whole group only!
> > > http://www32.brinkster.com/srisamp



> > > > Hello Dean,

> > > > This mean that I have to execute the procedure and know the names of
> > > > returned fields through Fields collection. This method have some
> > > performance
> > > > issues, such as it must be execute first, and what if this procedure
> > > return
> > > > say 100000 records???

> > > > What I need is knowing the names of returned columns directly
through
> > ADO,
> > > > or ADOX, or if there a SQL Server procedure that can do this for me.

> > > > Regards,

> > > > Maher K. Al-Jendasi



> > > > > Dim RS as ADODB.Recordset
> > > > > ...
> > > > > ...
> > > > > ...
> > > > > RS(0).name should give you "emp_id"
> > > > > and RS(1).name will give you "emp_name".

> > > > > Default collection of ADODB.Recordset is Fields from which you can
get
> > > the
> > > > number of fields in the recordset:
> > > > > RS.fields.count

> > > > > --
> > > > > Dean Savovic
> > > > > www.teched.hr



> > > > > > Hello All,

> > > > > > Is there anyway to retrieve column names that return from
> > > > > > SQL Server stored procedure via ADO, or ADOX? For example,
> > > > > > if I have the following procedure:

> > > > > > CREATE PROC GetEmployees

> > > > > > AS

> > > > > >    SELECT emp_id, emp_name
> > > > > >    FROM Employees

> > > > > > GO

> > > > > > Is it possible to do something in ADO, or ADOX that
> > > > > > returns names of columns (i.e emp_id, and emp_name)??

> > > > > > Thanks in advance

> > > > > > Maher K. Al-Jendasi


 
 
 

Retrieving SQL Server SPs Return Recordset Column Names

Post by Dean Savovi » Thu, 24 Jul 2003 20:38:14


Why would you want to execute DELETE store procedure in this manner. DELETE statement will not return a recordset, and
if I remember correctly, you wan't to find out recordset field names.

If you have DELETE and SELECT (that returns recodrset) statements in your stored procedures then you might consider changing
your sp implementation.

--
Dean Savovic
www.teched.hr


> Hello Dean,

> What if my SP is an action query (for example DELETE ), if I execute it then
> it will perform the action; causing data loss. In this case how I could know
> whether it a action query or not???

> Regards,

> Maher K. Al-Jendasi



> > If setting

> > SET FMTONLY ON

> > before executing sp from ADO or ADOX doesn't work for you

> > then use:

> > SET ROWCOUNT 1
> > and then execute SP.

> > --
> > Dean Savovic
> > www.teched.hr



> > > Hello SriSamp,

> > > It's a perfect solution for simple procedures, but it did not work with
> > > procedures that build and execute dynamoc SQL

> > > Regards,

> > > Maher K. Al-Jendasi



> > > > Check out the SET FMTONLY ON option in SQL Server. This lets you get
> only
> > > > the metadata and not any rows. Remember to make it OFF immediately,
> > > > otherwise none of your queries will return any data :-)
> > > > --
> > > > HTH,
> > > > SriSamp
> > > > Please reply to the whole group only!
> > > > http://www32.brinkster.com/srisamp



> > > > > Hello Dean,

> > > > > This mean that I have to execute the procedure and know the names of
> > > > > returned fields through Fields collection. This method have some
> > > > performance
> > > > > issues, such as it must be execute first, and what if this procedure
> > > > return
> > > > > say 100000 records???

> > > > > What I need is knowing the names of returned columns directly
> through
> > > ADO,
> > > > > or ADOX, or if there a SQL Server procedure that can do this for me.

> > > > > Regards,

> > > > > Maher K. Al-Jendasi



> > > > > > Dim RS as ADODB.Recordset
> > > > > > ...
> > > > > > ...
> > > > > > ...
> > > > > > RS(0).name should give you "emp_id"
> > > > > > and RS(1).name will give you "emp_name".

> > > > > > Default collection of ADODB.Recordset is Fields from which you can
> get
> > > > the
> > > > > number of fields in the recordset:
> > > > > > RS.fields.count

> > > > > > --
> > > > > > Dean Savovic
> > > > > > www.teched.hr



> > > > > > > Hello All,

> > > > > > > Is there anyway to retrieve column names that return from
> > > > > > > SQL Server stored procedure via ADO, or ADOX? For example,
> > > > > > > if I have the following procedure:

> > > > > > > CREATE PROC GetEmployees

> > > > > > > AS

> > > > > > >    SELECT emp_id, emp_name
> > > > > > >    FROM Employees

> > > > > > > GO

> > > > > > > Is it possible to do something in ADO, or ADOX that
> > > > > > > returns names of columns (i.e emp_id, and emp_name)??

> > > > > > > Thanks in advance

> > > > > > > Maher K. Al-Jendasi


 
 
 

Retrieving SQL Server SPs Return Recordset Column Names

Post by Maher K. Al-Jendas » Thu, 24 Jul 2003 22:45:49


Hello Dean,

I want to write some user control that is similar to ADO Data control, to
enable my team to quickly write code to execute SPs, I tried to use ADO Data
control, but what I notice that it will execute even DELETE queries at
design-time; causing lose of data (try it), so I need some thing that maybe
open the connection in read-only mode, or other solutions that will not
delete my data??

Any idea.????

Thanks again

Regards,

Maher K. Al-Jendasi


Quote:> Why would you want to execute DELETE store procedure in this manner.

DELETE statement will not return a recordset, and
Quote:> if I remember correctly, you wan't to find out recordset field names.

> If you have DELETE and SELECT (that returns recodrset) statements in your

stored procedures then you might consider changing
> your sp implementation.

> --
> Dean Savovic
> www.teched.hr




> > Hello Dean,

> > What if my SP is an action query (for example DELETE ), if I execute it
then
> > it will perform the action; causing data loss. In this case how I could
know
> > whether it a action query or not???

> > Regards,

> > Maher K. Al-Jendasi



> > > If setting

> > > SET FMTONLY ON

> > > before executing sp from ADO or ADOX doesn't work for you

> > > then use:

> > > SET ROWCOUNT 1
> > > and then execute SP.

> > > --
> > > Dean Savovic
> > > www.teched.hr



> > > > Hello SriSamp,

> > > > It's a perfect solution for simple procedures, but it did not work
with
> > > > procedures that build and execute dynamoc SQL

> > > > Regards,

> > > > Maher K. Al-Jendasi



> > > > > Check out the SET FMTONLY ON option in SQL Server. This lets you
get
> > only
> > > > > the metadata and not any rows. Remember to make it OFF
immediately,
> > > > > otherwise none of your queries will return any data :-)
> > > > > --
> > > > > HTH,
> > > > > SriSamp
> > > > > Please reply to the whole group only!
> > > > > http://www32.brinkster.com/srisamp



> > > > > > Hello Dean,

> > > > > > This mean that I have to execute the procedure and know the
names of
> > > > > > returned fields through Fields collection. This method have some
> > > > > performance
> > > > > > issues, such as it must be execute first, and what if this
procedure
> > > > > return
> > > > > > say 100000 records???

> > > > > > What I need is knowing the names of returned columns directly
> > through
> > > > ADO,
> > > > > > or ADOX, or if there a SQL Server procedure that can do this for
me.

> > > > > > Regards,

> > > > > > Maher K. Al-Jendasi



> > > > > > > Dim RS as ADODB.Recordset
> > > > > > > ...
> > > > > > > ...
> > > > > > > ...
> > > > > > > RS(0).name should give you "emp_id"
> > > > > > > and RS(1).name will give you "emp_name".

> > > > > > > Default collection of ADODB.Recordset is Fields from which you
can
> > get
> > > > > the
> > > > > > number of fields in the recordset:
> > > > > > > RS.fields.count

> > > > > > > --
> > > > > > > Dean Savovic
> > > > > > > www.teched.hr



> > > > > > > > Hello All,

> > > > > > > > Is there anyway to retrieve column names that return from
> > > > > > > > SQL Server stored procedure via ADO, or ADOX? For example,
> > > > > > > > if I have the following procedure:

> > > > > > > > CREATE PROC GetEmployees

> > > > > > > > AS

> > > > > > > >    SELECT emp_id, emp_name
> > > > > > > >    FROM Employees

> > > > > > > > GO

> > > > > > > > Is it possible to do something in ADO, or ADOX that
> > > > > > > > returns names of columns (i.e emp_id, and emp_name)??

> > > > > > > > Thanks in advance

> > > > > > > > Maher K. Al-Jendasi


 
 
 

Retrieving SQL Server SPs Return Recordset Column Names

Post by Dean Savovi » Fri, 25 Jul 2003 09:21:55


If you wan't to deifferentiate desing-time from run-time then use this function. In run-time function returns True
and in design-time False. HTH

Public Function IsRuntimeMode() As Boolean
On Error Resume Next
    Debug.Print 2 / 0
    If Err.Number = 0 Then
        IsRuntimeMode = True
    Else
        'DesignTime
        IsRuntimeMode = False
    End If
On Error GoTo 0
End Function

--
Dean Savovic
www.teched.hr


> Hello Dean,

> I want to write some user control that is similar to ADO Data control, to
> enable my team to quickly write code to execute SPs, I tried to use ADO Data
> control, but what I notice that it will execute even DELETE queries at
> design-time; causing lose of data (try it), so I need some thing that maybe
> open the connection in read-only mode, or other solutions that will not
> delete my data??

> Any idea.????

> Thanks again

> Regards,

> Maher K. Al-Jendasi



> > Why would you want to execute DELETE store procedure in this manner.
> DELETE statement will not return a recordset, and
> > if I remember correctly, you wan't to find out recordset field names.

> > If you have DELETE and SELECT (that returns recodrset) statements in your
> stored procedures then you might consider changing
> > your sp implementation.

> > --
> > Dean Savovic
> > www.teched.hr



> > > Hello Dean,

> > > What if my SP is an action query (for example DELETE ), if I execute it
> then
> > > it will perform the action; causing data loss. In this case how I could
> know
> > > whether it a action query or not???

> > > Regards,

> > > Maher K. Al-Jendasi



> > > > If setting

> > > > SET FMTONLY ON

> > > > before executing sp from ADO or ADOX doesn't work for you

> > > > then use:

> > > > SET ROWCOUNT 1
> > > > and then execute SP.

> > > > --
> > > > Dean Savovic
> > > > www.teched.hr



> > > > > Hello SriSamp,

> > > > > It's a perfect solution for simple procedures, but it did not work
> with
> > > > > procedures that build and execute dynamoc SQL

> > > > > Regards,

> > > > > Maher K. Al-Jendasi



> > > > > > Check out the SET FMTONLY ON option in SQL Server. This lets you
> get
> > > only
> > > > > > the metadata and not any rows. Remember to make it OFF
> immediately,
> > > > > > otherwise none of your queries will return any data :-)
> > > > > > --
> > > > > > HTH,
> > > > > > SriSamp
> > > > > > Please reply to the whole group only!
> > > > > > http://www32.brinkster.com/srisamp



> > > > > > > Hello Dean,

> > > > > > > This mean that I have to execute the procedure and know the
> names of
> > > > > > > returned fields through Fields collection. This method have some
> > > > > > performance
> > > > > > > issues, such as it must be execute first, and what if this
> procedure
> > > > > > return
> > > > > > > say 100000 records???

> > > > > > > What I need is knowing the names of returned columns directly
> > > through
> > > > > ADO,
> > > > > > > or ADOX, or if there a SQL Server procedure that can do this for
> me.

> > > > > > > Regards,

> > > > > > > Maher K. Al-Jendasi



> > > > > > > > Dim RS as ADODB.Recordset
> > > > > > > > ...
> > > > > > > > ...
> > > > > > > > ...
> > > > > > > > RS(0).name should give you "emp_id"
> > > > > > > > and RS(1).name will give you "emp_name".

> > > > > > > > Default collection of ADODB.Recordset is Fields from which you
> can
> > > get
> > > > > > the
> > > > > > > number of fields in the recordset:
> > > > > > > > RS.fields.count

> > > > > > > > --
> > > > > > > > Dean Savovic
> > > > > > > > www.teched.hr



> > > > > > > > > Hello All,

> > > > > > > > > Is there anyway to retrieve column names that return from
> > > > > > > > > SQL Server stored procedure via ADO, or ADOX? For example,
> > > > > > > > > if I have the following procedure:

> > > > > > > > > CREATE PROC GetEmployees

> > > > > > > > > AS

> > > > > > > > >    SELECT emp_id, emp_name
> > > > > > > > >    FROM Employees

> > > > > > > > > GO

> > > > > > > > > Is it possible to do something in ADO, or ADOX that
> > > > > > > > > returns names of columns (i.e emp_id, and emp_name)??

> > > > > > > > > Thanks in advance

> > > > > > > > > Maher K. Al-Jendasi


 
 
 

1. returning column names from a informix stored procedure returning recordset

I am having problems while using an informix stored procedure which is
returning multiple record recordset. when I populate the TrueDBGrid
6.0 OLE in my Visual Basic App with this record set returned from the
SP it is getting populated with all  the columns in the grid having
first columns data. This is because the SP is returning the columns
with no names.

Like I have CREATE PROCEDURE GETDATA (parameter datatype) RETURNING
VARCHAR(255), VARCHAR(255).....

How can I make an informix stored procedure return column names or
aliases for the data returned instead of just the datatypes.

--
Posted via http://dbforums.com

2. Tutorial: on table-corruption, and prevention strategies (esp. for Delphi)

3. Column names returned but no recordset

4. Chicago Sybase developer wanted

5. Retrieving SQL Server Names and their Database Names

6. Similar Rows...

7. Host won't allow Full-Text Catalog ... now what?

8. Issue with SPs returning name-value pairs

9. Please help - SQL Stored Procedure - Retrieving Column Name

10. T-SQL retrieve data using variable containing column name

11. Returning SQL Server Name in my Company and Database name