Solved

Run a SQL Stored Procedure from a .vbs File and return a parameter

Posted on 2013-12-04
5
2,057 Views
Last Modified: 2013-12-04
Hello

I need to execute a SQL Stored Procedure with a single input parameter (Integer) and return the result (Integer).
The code then needs to open a URL with the integer as a parameter ("exo://core_opportunity(x)")

I'm not sure how to connect to the database or return the parameter.
I need to run this from a .vbs file

Many Thanks
0
Comment
Question by:p-plater
  • 3
  • 2
5 Comments
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 500 total points
ID: 39697250
<%
' make sure to include your connection string to your db first
dim result,MyNumber
MyNumber=25
result=someFunction(MyNumber)

Function someFunction(MyParam)

   	
	Set cmd = Server.CreateObject("ADODB.Command")
With cmd
   
   	.ActiveConnection = MyConnection
    .CommandType = adCmdStoredProc
   	.CommandText = "name_of_stored_procedure" 'Name of your stored procedure
   
   	.Parameters.Append .CreateParameter("@variableX",adDouble, adParamInput)
   	.Parameters("@variableX") = MyParam 'set any variables for the SP
   
   set someFunction = .Execute

 
set cmd = nothing


end Function
%>

Open in new window

0
 

Author Comment

by:p-plater
ID: 39697280
Thanks Padas

I am getting the error " Arguments are of the wrong type, are out of acceptable range or are in conflict with each other"

Below is my Code
dim MyConnection, strSQL 
set MyConnection = CreateObject("ADODB.Connection") 
MyConnection.provider="sqloledb" 
MyConnection.Mode = "3" 
MyConnection.open  "dcserver", "sa", "xxxxxxxx" 
MyConnection.DefaultDatabase = "CRM_Test" 

	
	
' make sure to include your connection string to your db first
dim result,MyNumber
MyNumber=25
result=someFunction(MyNumber)

msgbox result

Function someFunction(MyParam)

   	
Set cmd = CreateObject("ADODB.Command")

With cmd
   
   	.ActiveConnection = MyConnection
    .CommandType = 4'adCmdStoredProc
   	.CommandText = "x_test" 'Name of your stored procedure
   	.Parameters.Append .CreateParameter("@act",adDouble, adParamInput)
   	.Parameters("@act") = MyParam 'set any variables for the SP
end with
   
   set someFunction = .Execute

 
set cmd = nothing


end Function

Open in new window


Also my Stored Procedure Code
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Ronald Kidd
-- Create date: 2013-12-05
-- Description:	Open
-- =============================================
ALTER PROCEDURE [dbo].[x_test] 
	-- Add the parameters for the stored procedure here
	@act int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    declare @RETURN_VALUE as int
   set @RETURN_VALUE = 25
	SELECT @RETURN_VALUE
	
	insert into X_PRG_ERRORLOG(COMPUTER,STAFF,TRIGGERED,ERROR)
		values('test','ters','test',@act)
	
	return 25
	
END

GO

Open in new window

0
 
LVL 52

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 500 total points
ID: 39697292
Oh, I always have adovbs included too http://support.web.com/vserver/ntguide/microsoft_adovbs_inc_file.htm

What line is the error happening on?
0
 

Author Comment

by:p-plater
ID: 39697301
Line 27
0
 

Author Comment

by:p-plater
ID: 39697310
OK
I've changed line 27 to"Parameters.Append .CreateParameter("@act",5, 1)"
And "cmd" to the .execute

It now runs but the message box errors - Doesn't return the parameter.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Tsql query 6 22
Query to capture 5 and 9 digit zip code? 4 21
Sql Query 6 67
Rename a column in the output 3 14
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

809 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