#158045 - 2006-03-01 11:39 PM
SQL Connection
|
Shaun
Fresh Scripter
Registered: 2006-03-01
Posts: 9
|
I'm trying to execute a stored proceedure, using the UDF's DBConnOpen() DBExecuteSQL() and DBConnClose()
However I the Stored proceedure returns an error number, I know the SP runs when executed on the SQL server, can anyone see anything wrong with my function?
Any help appreicated.
Code:
function GetDetails() DIM $isTM,$SQLSvr,$DumpType,$awDB,$DSN,$SQLCon,$SQLCode,$EXResult,$CloseCon $isTM=iif(ingroup("Traffic Managers"),"Ture","False") $SQLSvr="TRNDataRep" $DumpType="End Of Month" $awDB="TRNHAM"
$DSN="DRIVER={SQL Server};SERVER=$SQLSvr;UID=;PWD=;DATABASE=$awDB"
;Open Connection $SQLCon=DBConnOpen($DSN)
;Execute Stored Proceedure $SQLCode="EXEC $awDB.dbo.awDBDump"
$EXResult=DBExecuteSQL($SQLCon, $SQLCode,4)
;Close Connection $CloseCon=DBConnClose($SQLCon)
endfunction
Edited by Shaun (2006-03-02 04:23 AM)
|
Top
|
|
|
|
#158048 - 2006-03-02 04:22 AM
Re: SQL Connection
|
Shaun
Fresh Scripter
Registered: 2006-03-01
Posts: 9
|
It's a function because it is triggered by a KixForm button, also will be used in many locations with differeng servers and datases, hence the reason for the var strings etc, I have now also spotted the code tag
I will gather the returned values from the DB functions and post tomorrow.
Thanks
|
Top
|
|
|
|
#158049 - 2006-03-02 04:46 AM
Re: SQL Connection
|
Allen
KiX Supporter
Registered: 2003-04-19
Posts: 4545
Loc: USA
|
This... Code:
$DSN="DRIVER={SQL Server};SERVER=$SQLSvr;UID=;PWD=;DATABASE=$awDB"
...To this... Code:
$DSN="DRIVER={SQL Server};SERVER=" + $SQLSvr + ";UID=;PWD=;DATABASE=" + $awDB
...is what I was meaning about the vars in strings... for me its alot easier to read, and there are other around here who claim it is a much better coding style.
|
Top
|
|
|
|
#158050 - 2006-03-02 04:54 AM
Re: SQL Connection
|
Shaun
Fresh Scripter
Registered: 2006-03-01
Posts: 9
|
K ... thanks for the input, I was doing that, just getting a bit lazy
Been a long day.
|
Top
|
|
|
|
#158052 - 2006-03-03 12:39 AM
Re: SQL Connection
|
Shawn
Administrator
Registered: 1999-08-13
Posts: 8611
|
Can you show us what this returns:
Code:
$EXResult=DBExecuteSQL($SQLCon, $SQLCode,4)
?"Error=" + @SERROR
You'll probably have to put this at the top of your script, so you will be able to see the entire error message:
$= SetOption("WrapAtEol", "On")
|
Top
|
|
|
|
#158058 - 2006-03-03 07:15 AM
Re: SQL Connection
|
Shaun
Fresh Scripter
Registered: 2006-03-01
Posts: 9
|
Thanks guys for the input.
Further testing by adjusting the script to insert to a database specificly made on the server, confirms that the DBConn Open and Close are fine. Also confirms that the DBExecuteSQL is working when doing an INSERT.
Looking more like an issue with getting the Stored Proceedure in MSSQL to work properly, or an issue of corectly calling it.
If I use the 'SQL Query Analyser' and run the stored proceedure from the same PC with the same credintials it works, one would assume (never a good thing) that it then should work with the kix script.
Either that or my SP is screwed and i need to look at that side (most likely since its my first attempt at this)
|
Top
|
|
|
|
#158060 - 2006-03-06 01:52 AM
Re: SQL Connection
|
Shaun
Fresh Scripter
Registered: 2006-03-01
Posts: 9
|
Thanks again.
And yes, it is an issue with the SP and rights to do Backup's. Tried it with 'sa' details and it worked fine. Will have to play about with setting on the AD\SQL and see what I can get working.
Thanks for the great UDF's
Cheers Shaun
|
Top
|
|
|
|
#158061 - 2006-03-06 02:17 AM
Re: SQL Connection
|
Shaun
Fresh Scripter
Registered: 2006-03-01
Posts: 9
|
And to close .... With a 'sql' account configured on MSSQL as a 'db_backupoperator' will run a stored proceedure dumping the database to disk without an issue.
However once you are connecting via the UDF's to excecute the code, it appears that you also irequire to be a 'db_datareader'.
Teach me for thinking for myself
Cheers for the help guys ....
Shaun
|
Top
|
|
|
|
Moderator: Glenn Barnas, NTDOC, Arend_, Jochen, Radimus, Allen, ShaneEP, Ruud van Velsen, Mart
|
1 registered
(Allen)
and 466 anonymous users online.
|
|
|