Sign in to follow this  
Followers 0
sublimnl

Executing an MS SQL stored procedure...

3 posts in this topic

#1 ·  Posted (edited)

Hi, I am using the UDF found here to connect to an SQL server and execute a stored procedure. The problem is that I am not getting any return from the stored procedure at all when executing this in AutoIt. Below is the code I am using.

#include <_sql.au3>

_SQLStartup()
_SQLConnect(-1,$server,$dbname,$user,$pass)
$query = "sp_timesheet_check 'test.user'"
$result = _SQLExecute(-1,$query)
_SQLClose()
MsgBox(4096,"",$result)

$result always comes back blank when running the code above, but if I run the same stored procedure from osql.exe I get back the following

per end date = Feb 29 2008 12:00AM
usr_group_code = ACCTADMIN
 alert
 -----
     0

(1 row affected)

How can I get this information from the stored procedure via AutoIt? Really all I am after is the value for "alert" which will be a 1 or a 0 so that I can take action based on that.

Thanks for your help.

Edited by sublimnl

Share this post


Link to post
Share on other sites



Maybe this help you ......

CODE

AutoItSetOption("MustDeclareVars",1)

Dim $oConn1

Dim $oRS1

Dim $server = "."; local server

Dim $user = "sa";The user

Dim $pass = "password";The ......... password !!!

Dim $dbname = "NorthWind";the database

Dim $adCmdUnspecified = -1

Dim $adCmdText = 1

Dim $adCmdTable = 2

Dim $adCmdStoredProc = 4

Dim $adCmdUnknown = 8

Dim $adCmdFile = 256

Dim $adCmdTableDirect = 512

Dim $adOpenForwardOnly = 0

Dim $adOpenKeyset = 1

Dim $adOpenDynamic = 2

Dim $adOpenStatic = 3

Dim $adOpenUnspecified = -1

Dim $adLockBatchOptimistic =4

Dim $adLockOptimistic =3

Dim $adLockPessimistic =2

Dim $adLockReadOnly =1

Dim $adLockUnspecified =-1

Dim $strConnexion = "DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $dbname & ";uid=" & $user & ";pwd=" & $pass & ";"

Dim $strSqlQuery = "sp_oledb_database" ; a MsSql2000 stored proc of master database !

Dim $IntRetour = 0

Dim $intNbLignes = 0

$oConn1 = ObjCreate("ADODB.Connection")

$oRS1 = ObjCreate("ADODB.Recordset")

$oConn1.open($strConnexion)

$oRS1.open($strSqlQuery,$oConn1,$adOpenKeyset,$adLockOptimistic,$adCmdStoredProc)

$intNbLignes = $oRS1.recordCount

$oRS1.MoveFirst

For $i = 0 To $intNbLignes -1

ConsoleWrite($oRS1.Fields.Item(0).value & @CRLF)

$oRS1.MoveNext

Next

$oRS1.close

$oConn1.close

AdoAdox2MSSql2000.au3

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0