VBScript Stored Proc Execute - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

VBScript Stored Proc Execute

Author

Topic

pr4t3ekd
Starting Member

31 Posts

Posted - 2009-11-08 : 23:09:25

Hi,

Just wondering how I could execute a stored procedure on a database which returns no result? I would like to perform this via a vbscript.
The parameters passing with the stored procudure change when run...

Thank You

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-09 : 10:55:23

[code]
Dim cmd
Dim sp

sp = "YOUR STORED PROCEDURE"

Set cmd = CreateObject("ADODB.Command")
With cmd
.ActiveConnection = "YOUR CONNECTION STRING"
.CommandType = 4
.CommandText = sp
.Execute
End With
Set cmd = Nothing[/code]

see here also: http://msdn.microsoft.com/en-us/library/ms678086(VS.85).aspx

pr4t3ekd
Starting Member

31 Posts

Posted - 2009-11-09 : 18:15:25

excellent.

cheers.

my final code:

[CODE]
Dim cmd
Dim sp

sp = "sp_test"

Set cmd = CreateObject("ADODB.Command")
With cmd
.ActiveConnection = "Provider=sqloledb;Data Source=COMPANYSERVER;Initial Catalog=TESTDATABASE;Integrated Security=SSPI"
.CommandType = 4
.CommandText = sp
.Execute
End With
Set cmd = Nothing

msgbox(sp & " has been executed successfully!")
[/CODE]

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-09 : 23:13:52

Great, glad to hear it!

pr4t3ekd
Starting Member

31 Posts

Posted - 2009-11-22 : 23:42:15

Hi,

I have the following code..
when executing i get the error:
"Error: Arguments are of the wrong type, are out of acceptable range, or are in conflict with another.
Code: 800A0BB9
Source: ADODB.Parameter"

Error at line 17, "par1.Direction=adParamInput"

any ideas....

code:


Dim cmd
Dim sp
Dim CommDate
Dim PayrunID
Dim par1
Dim par2

CommDate = Date-22
ID = Date
sp = "test_sp"

Set cmd = CreateObject("ADODB.Command")
set par1 = CreateObject("ADODB.Parameter")
set par2 = CreateObject("ADODB.Parameter")

par1.Direction=adParamInput
par1.name="comm_enddate"
par1.Size=50
par1.Type=adVarChar
par1.Value=CommDate

par2.Direction=adParamInput
par2.name="id"
par2.Size=50
par2.Type=adVarChar
par2.Value="data_" & ID

With cmd
.ActiveConnection = "Provider=sqloledb;Data Source=SERVER;Initial Catalog=DATABASE;Integrated Security=SSPI"
	.CommandType = 4
	.CommandText = sp
	.Parameters.Append.CreateParameter(par1) 
	.Parameters.Append.CreateParameter(par2)
	.Execute
End With
Set cmd = Nothing
set par1 = Nothing
set par2 = Nothing

pr4t3ekd
Starting Member

31 Posts

Posted - 2009-11-23 : 00:25:34

just found out, this doesn't work for some reason via vbscript. Just works using vb e.g. Microsoft Access.

How can I get this to work with vbscript?

final code i used is:


Dim cmd
Dim sp
Dim CommDate
Dim PayrunID
Dim par1
Dim par2

CommDate = Date-22
ID = Date
sp = "test_sp"

Set cmd = CreateObject("ADODB.Command")
set par1 = CreateObject("ADODB.Parameter")
set par2 = CreateObject("ADODB.Parameter")

par1.Direction=adParamInput
par1.name="comm_enddate"
par1.Size=50
par1.Type=adVarChar
par1.Value=CommDate

par2.Direction=adParamInput
par2.name="id"
par2.Size=50
par2.Type=adVarChar
par2.Value="data_" & ID

With cmd
.ActiveConnection = "Provider=sqloledb;Data Source=SERVER;Initial Catalog=DATABASE;Integrated Security=SSPI"
	.CommandType = 4
	.CommandText = sp
	.Parameters.Append par1 
	.Parameters.Append par2
	.Execute
End With
Set cmd = Nothing
set par1 = Nothing
set par2 = Nothing

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-23 : 00:33:27

you're using constants that VBScript doesn't recognize.

Add this to the top of your code:


Const adParamInput = 1
Const adVarChar = 200

behrman
Yak Posting Veteran

76 Posts

Posted - 2009-12-24 : 09:34:56

Hi.
Please refer to the url below:
http://msdn.microsoft.com/en-us/library/ms171921.aspx
Regards,
behrman.

RAQ Report: Web-based Excel-like Java reporting tool

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources