Solved

VBScript Running A Stored Procedure - Cannot Return Output as Int

Posted on 2012-03-19
3
517 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
ID: 37742272
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

830 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