Solved

VBScript Running A Stored Procedure - Cannot Return Output as Int

Posted on 2012-03-19
3
525 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…

751 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