[freetds] Another OTL question / problem

Lowden, James K LowdenJK at bernstein.com
Thu May 13 09:51:17 EDT 2004

> From: freetds-bounces at lists.ibiblio.org
> [mailto:freetds-bounces at lists.ibiblio.org]On Behalf Of 
> > On Thu, 13 May 2004, Frediano Ziglio <freddyz77 at tin.it> wrote:
> > > - getting store procedure informations (I don't know how)
> > 
> > What information, exactly?
> The first recordset types returned (and possibly store procedure
> parameters list)

Parameters are no problem:

SELECT   @proc_name as 'procedure'
	, c.name as 'parameter_name'
	, t.name as 'type'
	, c.length
	, case c.isnullable when 1 then 'Yes' else 'No' end as Nulls
FROM 	syscolumns as c
JOIN 	systypes as t
ON t.xusertype = c.xusertype
WHERE	c.id = object_id(@proc_name)

Result set metadata *is* a problem.  It can't be had without either A) running the procedure, or B) parsing its SQL.  Which ODBC function requires it?  

If you are thinking of SQLProcedureColumns(), there is this note on SQLProcedureColumns() in MDAC SDK:  

"Note SQLProcedureColumns might not return all columns used by a procedure. For example, a driver might only return information about the parameters used by a procedure and not the columns in a result set it generates."

Books Online specifically states that in Microsoft's ODBC implementation "SQLProcedureColumns uses the catalog stored procedure sp_sproc_columns to report the attributes of stored procedure columns."  

Note well the Remarks for sp_sproc_columns:

The returned columns belong to the parameters or result set of a stored procedure. If the SP_NUM_PARAMETERS and SP_NUM_RESULT_SETS columns returned by sp_stored_procedures for a particular stored procedure are -1 (indeterminate), sp_sproc_columns returns no rows for that stored procedure."

Then comes this glorious Alice-in-Wonderland observation:

"In SQL Server, only the column information about input and output parameters for the stored procedure are [sic] returned."

One wonders what besides SQL Server runs sp_sproc_columns?  

Looks like a dead end to me, amico.  


P.S.  This is what I love about Microsoft's documentation.  It's written by the Marketing department.  One never sees "X doesn't work" or "X doesn't exist".  One occasionally sees "X might not work" (what does "might" mean in a deterministic system?), but more often a missing feature is simply not mentioned anywhere, forcing the reader to spend enormous effort trying to prove a negative.  How many thousands of man hours have been repetitively wasted proving in isolation that neither of these functions does what they suggest they "might" do?  

The information contained in this transmission may contain privileged and confidential information and is intended only for the use of the person(s) named above. If you are not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender immediately by reply e-mail and destroy all copies of the original message. Please note that we do not accept account orders and/or instructions by e-mail, and therefore will not be responsible for carrying out such orders and/or instructions.
If you, as the intended recipient of this message, the purpose of which is to inform and update our clients, prospects and consultants of developments relating to our services and products, would not like to receive further e-mail correspondence from the sender, please "reply" to the sender indicating your wishes.  In the U.S.: 1345 Avenue of the Americas, New York, NY 10105.

More information about the FreeTDS mailing list