SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
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  

jan.adolfsson
Starting Member

Sweden
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?

SP:
USE [ProjectBudget]
GO

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

GO



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.Refresh

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

objCommandSec.execute

nigelrivett
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
http://www.nigelrivett.net/aSP/DBAccess.inc.html
http://www.nigelrivett.net/VB/VBExecSPSimple.html


==========================================
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.06 seconds. Powered By: Snitz Forums 2000