Greetings,

We have a business requirement to somehow extract the SP header from
syscomments where available. This is done from a COM+ component using
ADO in order to verify whether SP arguments are specified correctly
from a client application, primarily during integration testing. We
know to use sp_helptext, but there is a slight issue...

Parsing around comments is not a problem using block (/* ... */)
commenting style, however single line (-- ...) comments may pose a
problem. It appears as though syscomments spans such comment lines
across table rows. So we end up with something like this:

...
-- blah blah blah (exceeds 255 characters)
blah blah blah (remaining comment
...

This could present some challenges. Is there a safe (and sane) method
of determining when this is the case and making the appropriate
adjustments to the COM+ code?

If we can get past this "little" issue then I am fairly confident we
can parse the remainder of the SP header for the desired argument
information.

Any insight anyone can offer wouldb e much appreciated.

Thanks,
Michael Powell

Parsing syscomments text seems a bit of a kluge to me. If all you are
interested in is parameter names and data types, it would probably be better
to query INFORMATION_SCHEMA.PARAMETERS.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Mike" <mwpowellnm (AT) comcast (DOT) net> wrote

Wow, you are correct. This is about as direct a route as one can expect
querying for parameter information. One thing, however, how do we get
at information such as the default arguments? This doesn't appear to be
available from INFORMATION_SCHEMA.PARAMETERS?

Aside from parsing the proc header, you can use SMO to get stored procedure
default parameters. SMO is a .Net-based API introduced in SQL 2005 and
version 2.0 of the .Net framework so it needs to be called from managed
code. If your existing COM component is strictly unmanaged, you might find
it easier to wrap the needed SMO stuff in a separate assembly so that you
invoke it using COM interop.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Mike" <mwpowellnm (AT) comcast (DOT) net> wrote

Please forgive my ignorance, the only reference I see to SMO is in the
MS SOAP toolkit. Is there a primer section that could introduce me to
it? Something that explains what it is, how to begin using it, etc,
etc? From Visual Studio .NET (2002) I found the references. From VS
..NET 2003 I found no such references. Or maybe I'm missing it?

We may pursue the SMO option (research on the topic pending). Back to
our original query though... Are there any practical ways to address
the above scenario? Short of assuming that the code must be good
syntactically or it wouldn't have been applied to the syscomments
table. If that is true, then any tokens which fall outside the scope of
available SQL tokens are probably invalid and belong to the comment.
Then we're looking for the first token which appears with a '@' and
which is followed by a valid type token? (Thinking out loud...)

<quote>
Please forgive my ignorance, the only reference I see to SMO is in the
MS SOAP toolkit. Is there a primer section that could introduce me to
it? Something that explains what it is, how to begin using it, etc,
etc? From Visual Studio .NET (2002) I found the references. From VS
..NET 2003 I found no such references. Or maybe I'm missing it?
</quote>

For all,
http://msdn2.microsoft.com/en-us/library/ms162169.aspx

Parsing ad-hoc text is a real pain, even when limited to the header.
Personally, I wouldn't even go there, given the SMO alternative. Consider
cases like:

CREATE PROC MyProc
--@MyParm1,
@MyParm2 int = 1,
@MyParm3 varchar(10) = '@MyParm4 int',
@MyParm5 int = 1, @MyParm6 int = 1 --@MyParam7 int
AS
....

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Mike" <mwpowellnm (AT) comcast (DOT) net> wrote

As you've probably discovered by now, .Net Framework 2.0 requirement implies
that you'll need to develop using Visual Studio 2005 or at least the 2.0
framework SDK.


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Mike" <mwpowellnm (AT) comcast (DOT) net> wrote