Jump to content

Executing an MS SQL stored procedure...


sublimnl
 Share

Recommended Posts

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
Link to comment
Share on other sites

  • 2 weeks later...

Maybe this help you ......

 
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

Edited by FourLC
Pb with code edit
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...