SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How do I run the stored proc from vbscript and rea
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 10/01/2013 :  16:46:19  Show Profile  Reply with Quote
I have a stored procedure that I run in Management studio just fine.
I need to provide 2 integer variables @id and @version.

How do I run the stored proc from vbscript and reading the output?

USE [ProjectBudget]

DECLARE @return_value int,
@name varchar(500),
@maconomy varchar(100),
@creator varchar(100),
@created datetime,
@active bit

EXEC @return_value = [dbo].[sp_project_get]
@id = 2,
@version = 2,
@name = @name OUTPUT,
@maconomy = @maconomy OUTPUT,
@creator = @creator OUTPUT,
@created = @created OUTPUT,
@active = @active OUTPUT

SELECT @name as N'@name',
@maconomy as N'@maconomy',
@creator as N'@creator',
@created as N'@created',
@active as N'@active'

SELECT 'Return Value' = @return_value


SP Returns:
@name @maconomy @creator @created @active
test 3 122112 HÃ¥kan Mattsson 2013-09-27 12:54:57.580 1

ASP Code:

Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB;Server=XXXX;database=XXXX;network=DBMSSOCN;uid=XXXX;pwd=XXXX"

Set objCommandSec = CreateObject("ADODB.Command")
objCommandSec.ActiveConnection = Conn
objCommandSec.CommandType = 4
objCommandSec.CommandText = "sp_project_get"


objCommandSec.parameters("@id") = 2
objCommandSec.parameters("@version") = 2


Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 10/02/2013 :  03:53:56  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Not sure what the question is - what happens when you run the code - does it fail? Can't you access the parameters?
To get the resultset you need to set a resultset object to the execute result.

Have a look at these for calling a stored proc and accessing a resultset

Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000