Solved

VBScript Running A Stored Procedure - Cannot Return Output as Int

Posted on 2012-03-19
3
505 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

867 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

12 Experts available now in Live!

Get 1:1 Help Now