Running a stored procedure from VBScript

Googled around and found this for VBScript code to run a SP:
dim conn
set conn=createobject("adodb.connection")
conn.connectionstring="provider=sqloledb.1;persist security info=false;user
id=xx;password=xx;initial catalog=xx;data source=xx"
conn.open
conn.execute "test_sp"
conn.close
set conn=nothing
test_sp:
RAISERROR ('Test message',20,1) WITH LOG
When I run the VBScript, it basically outputs 'test message' as an error
from what I can tell.
I want the VBScript to run quietly, and not output anything.
How can this be done or is there a better VBScript script that I can use?
I'm hoping I can also catch errors with the same script if the SP fails...
Thanks,
MarcoObviously, your SP raises an error and pass it to the calling process (your
VBScript's ADODB.Connection.Execute() call). You simply need to handle the
error in your VBScript code (unfortunately, VBScript has poor exception
handling method):
Public Sub DoSQLStuff()
...
On Error Resume Next
cn.Open
If Err.Number<>0 Then
'Do something on error, if you want to
Exit Sub
End If
cn.Execute "test_sp"
If Err.Number<>0 Then
'Do something of your choice
Exit Sub
End If
...
End Sub
The point is, after each line of code, if the code could cause runtime
error, you need to check Err object to see if there is error or not.
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message
news:OM2G86oqGHA.4760@.TK2MSFTNGP05.phx.gbl...
> Googled around and found this for VBScript code to run a SP:
> dim conn
> set conn=createobject("adodb.connection")
> conn.connectionstring="provider=sqloledb.1;persist security
> info=false;user
> id=xx;password=xx;initial catalog=xx;data source=xx"
> conn.open
> conn.execute "test_sp"
> conn.close
> set conn=nothing
> test_sp:
> RAISERROR ('Test message',20,1) WITH LOG
> When I run the VBScript, it basically outputs 'test message' as an error
> from what I can tell.
> I want the VBScript to run quietly, and not output anything.
> How can this be done or is there a better VBScript script that I can use?
> I'm hoping I can also catch errors with the same script if the SP fails...
> Thanks,
> Marco
>sql

Running a stored procedure from VBScript

Googled around and found this for VBScript code to run a SP:
dim conn
set conn=createobject("adodb.connection")
conn.connectionstring="provider=sqloledb.1;persist security info=false;user
id=xx;password=xx;initial catalog=xx;data source=xx"
conn.open
conn.execute "test_sp"
conn.close
set conn=nothing
test_sp:
RAISERROR ('Test message',20,1) WITH LOG
When I run the VBScript, it basically outputs 'test message' as an error
from what I can tell.
I want the VBScript to run quietly, and not output anything.
How can this be done or is there a better VBScript script that I can use?
I'm hoping I can also catch errors with the same script if the SP fails...
Thanks,
MarcoObviously, your SP raises an error and pass it to the calling process (your
VBScript's ADODB.Connection.Execute() call). You simply need to handle the
error in your VBScript code (unfortunately, VBScript has poor exception
handling method):
Public Sub DoSQLStuff()
...
On Error Resume Next
cn.Open
If Err.Number<>0 Then
'Do something on error, if you want to
Exit Sub
End If
cn.Execute "test_sp"
If Err.Number<>0 Then
'Do something of your choice
Exit Sub
End If
...
End Sub
The point is, after each line of code, if the code could cause runtime
error, you need to check Err object to see if there is error or not.
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message
news:OM2G86oqGHA.4760@.TK2MSFTNGP05.phx.gbl...
> Googled around and found this for VBScript code to run a SP:
> dim conn
> set conn=createobject("adodb.connection")
> conn.connectionstring="provider=sqloledb.1;persist security
> info=false;user
> id=xx;password=xx;initial catalog=xx;data source=xx"
> conn.open
> conn.execute "test_sp"
> conn.close
> set conn=nothing
> test_sp:
> RAISERROR ('Test message',20,1) WITH LOG
> When I run the VBScript, it basically outputs 'test message' as an error
> from what I can tell.
> I want the VBScript to run quietly, and not output anything.
> How can this be done or is there a better VBScript script that I can use?
> I'm hoping I can also catch errors with the same script if the SP fails...
> Thanks,
> Marco
>

running a script which accepts a parameter against SQL2000 database

Hi there,
I'm fairly new to this stuff - I want to write a batch file or small
vbscript app that will aceept a parameter and then insert this into a
simple update command and execute agaisnt a SQL2000 database. andy help
appreciated!!
Cheers,
Paulat this link you can find about connecting to SQL server and adding new
records to a table using ADO
http://www.microsoft.com/technet/scriptcenter/scripts/misc/database/default.mspx?mfr=true
for using command line arguments, this link:
http://www.microsoft.com/technet/scriptcenter/resources/tales/sg0704.mspx
--
urkec
"pauls1888" wrote:
> Hi there,
> I'm fairly new to this stuff - I want to write a batch file or small
> vbscript app that will aceept a parameter and then insert this into a
> simple update command and execute agaisnt a SQL2000 database. andy help
> appreciated!!
> Cheers,
> Paul
>|||Look up batch files, using replaceable parameters, and then using SQL Server
Books Online, check out osql.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"pauls1888" <pauls1888@.gmail.com> wrote in message
news:1164726964.399837.44300@.45g2000cws.googlegroups.com...
> Hi there,
> I'm fairly new to this stuff - I want to write a batch file or small
> vbscript app that will aceept a parameter and then insert this into a
> simple update command and execute agaisnt a SQL2000 database. andy help
> appreciated!!
> Cheers,
> Paul
>|||Thanks for the help guys

Run a stored proc using vbscript

I understand that it may not be a good thing to use an ADO execute statement
using VBscript to update a SQL database. Rather the preferred method is to
execute a stored procedure. How does one do this using VBscript, and how
would you confirm that the update took place properly ?
Thanks !Hi,
You will receive an error by Errors collection on connection object.
Tomasz B.
"Rob C" wrote:

> I understand that it may not be a good thing to use an ADO execute stateme
nt
> using VBscript to update a SQL database. Rather the preferred method is
to
> execute a stored procedure. How does one do this using VBscript, and how
> would you confirm that the update took place properly ?
> Thanks !
>
>|||How To Invoke a Stored Procedure with ADO Query Using VBA/C++/Java
http://support.microsoft.com/?kbid=185125
AMB
"Rob C" wrote:

> I understand that it may not be a good thing to use an ADO execute stateme
nt
> using VBscript to update a SQL database. Rather the preferred method is
to
> execute a stored procedure. How does one do this using VBscript, and how
> would you confirm that the update took place properly ?
> Thanks !
>
>|||Rob C wrote:
> I understand that it may not be a good thing to use an ADO execute
> statement using VBscript to update a SQL database. Rather the
> preferred method is to execute a stored procedure. How does one do
> this using VBscript, and how would you confirm that the update took
> place properly ?
> Thanks !
This was written with ASP in mind. Remove "Server." from the createobject
statements to make it relevant to straight vbscript :
http://groups.google.com/groups?hl=...FTNGP12.phx.gbl
As somebody else stated, errors will be returned to the connection's errors
collection (the first error in the collection will usually bubble up to the
vbscript error-handler as well)
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Run a MS Access Sub from DTS pkg??

Does anyone have any experience with running an Access subroutine from a DTS package (vbscript)? I've tried using the Call function & get the following error. (also tried changing the sub to a function, but still get the error)

Error Source: DAO Properties

Error Description: Error Code 0
Error Source = Microsoft VBScript runtime error
Error Description: Type Mismatch: 'Att4Sum2'

Error on Line 8

Property not found.

Here's my vbscript code in the DTS package:

Function ExportAttachmentData()
Dim objDB
Set objDB = CreateObject("Access.Application")
objDB.OpenCurrentDatabase("C:\DBReports.mdb")
Call Att4Sum2
objDB.CloseCurrentDatabase
objDB.Quit
ExportAttachmentData = DTSTaskExecResult_Success
End Function

Thanks!
Brianbump bump bump