Re: How to handle stored procedure in VBScript

mifisailka_at_hotmail.com
Date: 11/19/04 

Date: 19 Nov 2004 05:46:54 -0800

Hi Max, Stephen,

you both pointed me to the same thing.

I thought the same way, though...
Here's the prerequisite: this stored procedure was not created by
myself. It came to me as part of Microsoft Operations Manager 2005
database (I am MOM 2005 admin). Hence, it's not that wise for me to
modify MS proprietary stored procedure...:o)

I know for sure it returns txt string. I could get it thru Query
Analyzer.
I believe there has to be some way to catch it in the script...

Thanks,
kosta

vb_mvp@hotmail.com (Max Kudrenko) wrote in message news:<6cde9dab.0411180245.31abaa1c@posting.google.com>...
> Kosta,
>
> Your SP looks suspicious.
> First, if you want to return a parameter, you need to declare it as
> OUTPUT. Second, it's not declared as having any parameters at all (it
> does have local variables though). And last, why you call RAISERROR?

It just creates Application Log event with the same string in the
body, that's it.

> It will return an error, not a normal result. So if you want to return
> your parameter @Results to the caller, drop that RAISERROR and declare
> the SP as folllows:
>
> CREATE PROCEDURE sp_OnePointFreeSpace
>
> @Results nvarchar(255) OUTPUT
>
> AS
> ...
>
> Hope this helps,
>
> Max Kudrenko
> Brainbench MVP Program for Visual Basic
> www.brainbench.com
>
>
> kosta.iaralov@gmail.com (kosta) wrote in message news:<ab266c6b.0411171514.49b8035@posting.google.com>...
> > Hi Stephen,
> > thanks a lot! I understood the way how to do that.
> > But now I am getting NULL string as return..:o(
> > If you would be so kind and have a quick look...
> > I believe I am missing something simple.
> > Here's my store procedure (only parameters that could be important):
> > ----------
> > CREATE PROCEDURE sp_OnePointFreeSpace
> > AS
> > BEGIN
> > DECLARE @ErrMsg nvarchar(512)
> > DECLARE @Results nvarchar(255)
> > [skip]
> > INSERT INTO #spaceusedinfo
> > EXEC dbo.MOMXspaceused
> > SELECT
> > @sp_dbspace = database_size,
> > @sp_logspace = log_size,
> > @sp_unallocated = [unallocated space]
> > FROM #spaceusedinfo
> > SET @Results = 'OnePoint Free Data Space Percentage: ' +
> > convert(char(20), (@sp_unallocated / (@sp_dbspace -
> > @sp_logspace) ) * 100)
> > RAISERROR(@Results, 0, 1) WITH LOG
> > RETURN 0
> > Error_Exit:
> > RETURN 1
> > END
> > GO
> > --------------
> > It supplies just a text string in Query Analyzer:
> > "OnePoint Free Data Space Percentage: 64.6299"
> > Here's my vbscript to catch that string:
> > -----------
> > [ADODB connection established]
> > objCommand.CommandType = adCmdStoredProc
> > objCommand.Parameters.Append objCommand.CreateParameter("Results",
> > adVarChar, adParamReturnValue, 255)
> > objCommand.commandtext = "sp_OnePointFreeSpace"
> > Set oSQLQueryResultSet = objCommand.Execute
> > sTEST = objCommand.Parameters("Results").Value
> > WScript.Echo sTEST
> > ---------------
> > sTEST is always NULL.
> > What did I do wrong?