Re: How to handle stored procedure in VBScript
mifisailka_at_hotmail.com
Date: 11/23/04
- Next message: Cindy Winegarden: "Re: Connecting to a FoxPro DBF file"
- Previous message: Stephanie Stowe: "opinion about a view"
- In reply to: Mark J. McGinty: "Re: How to handle stored procedure in VBScript"
- Next in thread: kosta: "Re: How to handle stored procedure in VBScript"
- Messages sorted by: [ date ] [ thread ]
Date: 23 Nov 2004 06:28:16 -0800
"Mark J. McGinty" <mmcginty@spamfromyou.com> wrote in message news:<bkxod.165779$hj.105134@fed1read07>...
> <mifisailka@hotmail.com> wrote in message
> news:ad2bf7fd.0411220705.2c9f76f1@posting.google.com...
> >> >
> >> > I know for sure it returns txt string. I could get it thru Query
> >> > Analyzer.
> >>
> >> Stored procedures do not return strings, they can return int and they can
> >> generate a result set. They can output strings either by calling print,
> >> or
> >> by raising an error.
> >>
> >> You can retrieve error messages from the connection.errors collection,
> >> e.g.,
> >> if connection.errors. > 0 then errmsg =
> >> connection.errors(0).description
> >>
> >> Note that there may be more than one error in the collection.
> >>
> >> You will sometimes see print output in the errors collection as well (the
> >> number property of the error object will be 0) but I've found it to be
> >> unreliable as a way to return data from a stored procedure.
> >>
> >>
> >> -Mark
> >>
> > Hi Mark,
> > thank you very much for your reply.
> > I still have troubles to grab that message.
> > Here's my piece of code:
> > -------
> > sConnection = "Provider='SQLOLEDB'; Data Source='" & strServerName &
> > "'; " & _
> > "Initial Catalog='ONEPOINT'; Integrated Security='SSPI';"
> >
> > Set objDBconnection = createobject("adodb.connection")
> > objDBconnection.open sConnection
> > Set objCommand = CreateObject("adodb.command")
> > objCommand.ActiveConnection = objDBconnection
> > objCommand.CommandType = adCmdStoredProc
> > objCommand.Parameters.Append objCommand.CreateParameter("Results",
> > adVarChar, adParamReturnValue, 255)
> >
> > sSQLQuery = "sp_OnePointFreeSpace"
> > objCommand.commandtext=sSQLQuery
> > Set oSQLQueryResultSet = objCommand.Execute
> > sTEST=objDBconnection.errors(0).description
> > ------
> >
> > After execution I am getting this:
> >
> > ADODB.Connection: Item cannot be found in the collection corresponding
> > to the requested name or ordinal.
> >
> > What did I do wrong?
> > Thank you very much in advance!
>
> You must test the connection.errors.count property to make sure it is
> greater than zero, if not, there are no errors in the collection. Why there
> wouldn't be, after you have raised an error in your SP I'm uncertain, maybe
> it needs a non-zero severity? I'll try to exeriment with it later tonight.
>
>
Hi Mark,
unfortunately, connection.errors.count is 0.
No luck....
Then, where does that message go??? :(
I will really appreciate any idea.
Kind regrads,
kosta
>
>
> >> > 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?
- Next message: Cindy Winegarden: "Re: Connecting to a FoxPro DBF file"
- Previous message: Stephanie Stowe: "opinion about a view"
- In reply to: Mark J. McGinty: "Re: How to handle stored procedure in VBScript"
- Next in thread: kosta: "Re: How to handle stored procedure in VBScript"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
