Page 1 of 1 1
Topic Options
#158045 - 2006-03-01 11:39 PM SQL Connection
Shaun Offline
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
#158046 - 2006-03-01 11:58 PM Re: SQL Connection
Allen Administrator Online   shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4482
Loc: USA
I don't think it has anything to do with the error, but you got TRUE spelled TURE... still looking...
Top
#158047 - 2006-03-02 12:10 AM Re: SQL Connection
Allen Administrator Online   shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4482
Loc: USA
I don't know the origin of your error, but I must ask why you enclosed your script in a function. And you really should consider removing the vars in strings. Might you also place your code in between the code tags so it makes it a little easier to read.

BTW: What is the error?

Top
#158048 - 2006-03-02 04:22 AM Re: SQL Connection
Shaun Offline
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 Administrator Online   shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4482
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 Offline
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
#158051 - 2006-03-03 12:19 AM Re: SQL Connection
Shaun Offline
Fresh Scripter

Registered: 2006-03-01
Posts: 9
Here is the values of the vars after the DBExecuteSQL() function is called, anyone have any idea on that returned value?

DSN String: DRIVER={SQL Server};SERVER=TRNDataRep;DATABASE=TRNHAM;Trusted_Connection=yes;
SQL Conection: Provider=MSDASQL.1;Extended Properties="DRIVER=SQL Server;SERVER=TRNDataRep;UID=;APP=KiXtart 2001;WSID=AKENG3;DATABASE=TRNHAM;Network=DBMSSOCN;Trusted_Connection=Yes"
SQL Code: exec awDBDump
DBExecute Result: -2147352567:

Top
#158052 - 2006-03-03 12:39 AM Re: SQL Connection
Shawn Administrator Offline
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
#158053 - 2006-03-03 02:13 AM Re: SQL Connection
Shaun Offline
Fresh Scripter

Registered: 2006-03-01
Posts: 9
Here is what it returned the error ... somethings not right.

Error=COM exception error "Execute" (Microsoft OLE DB Provider for ODBC Drivers - [Microsoft][ODBC SQL Server Driver]Syntax error or access violation) [-2147352567/80020009]

Top
#158054 - 2006-03-03 02:58 AM Re: SQL Connection
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Well, whats your gut feeling here? Probably not dealing with a syntax error. What happens if you provide some credentials in your connection string ?

-Shawn

Top
#158055 - 2006-03-03 03:37 AM Re: SQL Connection
Shaun Offline
Fresh Scripter

Registered: 2006-03-01
Posts: 9
OK dropped the trusted connection, created a user account with rights to execute the stored proceedure and backup operator, also removed the 'EXEC' fromt he sql. This has somewhat changed the error to a 'timeout'. While watching the file structure on the SQL Server, I can see the initial file name created, then this goes once the process times out.

If I run it from the Query Analyser using the credintials I created it works fine ... any other ideas before I go back to the black board to find another way

Error=COM exception error "Execute" (Microsoft OLE DB Provider for ODBC Drivers - [Microsoft][ODBC SQL Server Driver]Timeout expired) [-2147352567/80020009]

Cheers
Shaun

Top
#158056 - 2006-03-03 03:50 AM Re: SQL Connection
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Can you verify that the string your "executing" in your script is EXACTLY the same as the one your running manually ... have you tried dumping your string out to the console to make sure all the variables are being resolved correctly, like this:

?"conn=" + $SQLCon
?"sql=" + $SQLCode

$EXResult = DBExecuteSQL($SQLCon, $SQLCode,4)

are you missing something small, like forgetting to add a required semi-colon to the end of the statement ? something your doing interactively that you missed in your script ? grasping at straws here ;0)

Top
#158057 - 2006-03-03 06:14 AM Re: SQL Connection
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11577
Loc: CA
Have you checked and confirmed you don't have a FIREWALL issue?

Can you remotely connnect with any other tools?

For error output in KiX script with COM you can use this.

Val('&' + Right(DecToHex(@ERROR), 4))

Top
#158058 - 2006-03-03 07:15 AM Re: SQL Connection
Shaun Offline
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
#158059 - 2006-03-05 01:57 PM Re: SQL Connection
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11162
Loc: Boston, MA, USA
One recommendation I have is to create a very simple stored procedurte that doesn't even access the database but that just rturns some value, e.g. SELECT 'TEST PASSED'. The test this SP, it'll show whether DBExecuteSQL() can handle the call to the SP. Then you know the most likely cause is the SP itself or SQL Server.
_________________________
There are two types of vessels, submarines and targets.

Top
#158060 - 2006-03-06 01:52 AM Re: SQL Connection
Shaun Offline
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 Offline
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
#158062 - 2006-03-06 02:47 AM Re: SQL Connection
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11162
Loc: Boston, MA, USA
So it was a permissions problem and not a problem with the UDF itself?
_________________________
There are two types of vessels, submarines and targets.

Top
#158063 - 2006-03-06 05:45 AM Re: SQL Connection
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11577
Loc: CA
Perhaps not the UDF but maybe either the rest of the code or the UDF in that if you attempted to do a read and did not have permissions then it should have given an error that could have been tested for in the script and write to a log if required.
Top
Page 1 of 1 1


Moderator:  Glenn Barnas, NTDOC, Arend_, Jochen, Radimus, Allen, ShaneEP, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 145 anonymous users online.
Newest Members
Jodey, Hiker, GTRoberts, KatieQuinn, Brennnnn
17684 Registered Users

Generated in 0.042 seconds in which 0.012 seconds were spent on a total of 12 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org