Solved

VBScript Running A Stored Procedure - Cannot Return Output as Int

Posted on 2012-03-19
3
499 Views
Last Modified: 2014-07-26
Hello;

I've created a stored procedure that is called upon by a VBScript.  The VBScript passes two varchar parameters to the sp - User Name and Computer Name.  My intent of the SP is to return an integer to be used later in the script.  When the script runs, it errors out with the following error:

 Microsoft VBScript runtime error: Invalid or unqualified reference

I'm specifying the return value in the script as an integer, so I'm not sure where this is going wrong. I've attached the SP in a text file and the script used to run the SP, so if anyone out there can take a look and provide me some assistance I would much appreciate it.  Thank you!

stpMostUsedPCByUserID.txt

GetPCInfo.vbs
0
Comment
Question by:danner29
3 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 265 total points
Comment Utility
You are combining OUTPUT and RETURN parameters.  Choose one or the other.

This is how you do it with a RETURN parameter:
Your Stored Procedure:
CREATE PROCEDURE dbo.stpMostUsedPCByUserID  
	@userid VARCHAR(50),
	@compName VARCHAR(50)

AS

DECLARE @rtnValue integer

SET NOCOUNT ON;

SELECT	@rtnValue = COUNT(*) 
FROM	LOGON_SESSIONS  
WHERE	userid = @UserID
	AND Computer = @CompName

RETURN @rtnValue

Open in new window



And here is your VBScript:
...
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
			
				
sSql = "stpMostUsedPCByUserID"
cmd.CommandText = sSql
						 
With cmd
	.Parameters.Append .CreateParameter("@RETURN", adInteger, adParamReturnValue)
	.Parameters.Append .CreateParameter("@UserID", adVarChar, adParamInput, 50, Left(Right(oSysInfo.UserName,Len(oSysInfo.UserName) -3 ),InStr(Right(oSysInfo.UserName,Len(oSysInfo.UserName) -3),",")- 1))
	.Parameters.Append .CreateParameter("@compName", adVarChar, adParamInput, 50, Left(Right(oSysInfo.ComputerName,Len(oSysInfo.ComputerName) -3 ), InStr(Right(oSysInfo.ComputerName,Len(oSysInfo.ComputerName) -3 ),",")- 1))
End With	

'On Error Resume Next
cmd.Execute
rtnVal = .Parameters.Value("@RETURN")
WScript.Echo rtnVal
'on Error GoTo 0
...

Open in new window

0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now