(Fresh Scripter)

2006-03-01 11:39 PM

SQL Connection

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

Allen

(KiX Supporter)

2006-03-01 11:58 PM

Re: SQL Connection

I don't think it has anything to do with the error, but you got TRUE spelled TURE... still looking...

Allen

(KiX Supporter)

2006-03-02 12:10 AM

Re: SQL Connection

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?

Shaun

(Fresh Scripter)

2006-03-02 04:22 AM

Re: SQL Connection

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

Allen

(KiX Supporter)

2006-03-02 04:46 AM

Re: SQL Connection

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.

Shaun

(Fresh Scripter)

2006-03-02 04:54 AM

Re: SQL Connection

K ... thanks for the input, I was doing that, just getting a bit lazy

Been a long day.

Shaun

(Fresh Scripter)

2006-03-03 12:19 AM

Re: SQL Connection

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:

Shawn

(KiX Supporter)

2006-03-03 12:39 AM

Re: SQL Connection

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")

Shaun

(Fresh Scripter)

2006-03-03 02:13 AM

Re: SQL Connection

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]

Shawn

(KiX Supporter)

2006-03-03 02:58 AM

Re: SQL Connection

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

Shaun

(Fresh Scripter)

2006-03-03 03:37 AM

Re: SQL Connection

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

Shawn

(KiX Supporter)

2006-03-03 03:50 AM

Re: SQL Connection

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)

NTDOC

(KiX Master)

2006-03-03 06:14 AM

Re: SQL Connection

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))

Shaun

(Fresh Scripter)

2006-03-03 07:15 AM

Re: SQL Connection

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)

Sealeopard

(KiX Master)

2006-03-05 01:57 PM

Re: SQL Connection

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.

Shaun

(Fresh Scripter)

2006-03-06 01:52 AM

Re: SQL Connection

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

Shaun

(Fresh Scripter)

2006-03-06 02:17 AM

Re: SQL Connection

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

Sealeopard

(KiX Master)

2006-03-06 02:47 AM

Re: SQL Connection

So it was a permissions problem and not a problem with the UDF itself?

NTDOC

(KiX Master)

2006-03-06 05:45 AM

Re: SQL Connection

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.