job scheduler problem: routine abnormal behavior

[fyaqq]

I have a routine in which it uses sql statement to get the maximum number of
a column in a table (i.e. SQLExecDirect)
When the job was run by hand, the routine was executed correctly. The weird thing is when I use director to schedule the job, the routine didn't return the correct number.
Any input is welcome! Thanks!

[narasimha]

It sure is an abnormal behaviour.

A question for clarification are you getting "maximum number of a column in a table" or "maximum number of a rows in a table"
If it is the later, is the table getting updated regularly.
Is that the case you are getting different results?

Try to simulate in such a way that your manal run and your schedule runs at a small time difference to each other and check the results.

If this does not help, post your script.

[ray.wurlod]

Please prove your assertion that "the routine didn't return the correct number". The maximum value in a column is a dynamic kind of thing - it can change frequently. So you must be able to show that, at the time when the routine was invoked, there was no way that the value could be changed, that a separate method returns a correct value (proof?), and that the statistics for the table are up to date (if that's relevant).

[fyaqq]

Thanks for your reply.
I have narrowed down the problem. It occured on the statement

Code: Select all

status = SQLConnect(hdbc, dbName,userName,passwd)

When run by hand, the status is 0.
When run by scheduler, the status is -1.
The parameters are the same!
Any hints?

[ray.wurlod]

Proof?

SQL.BADHANDLE (-1) suggests that your first argument is invalid.

Is hdbc a valid handle; that is, did SQLAllocConnect() return SQL.SUCCESS?

Remember that variable names are case sensitive; hdbc and hDBC are two different variable names.

[fyaqq]

Proof?

SQL.BADHANDLE (-1) suggests that your first argument is invalid.

Is hdbc a valid handle; that is, did SQLAllocConnect() return SQL.SUCCESS?

Remember that variable names are case sensit ...

Code: Select all

status = SQLAllocEnv(henv)
     
status = SQLAllocConnect(henv,hdbc)

both return 0, no matter run by hand or by scheduler.
But

Code: Select all

     status = SQLConnect(hdbc, dbName,userName,passwd)

return 0 when run by hand, and -1 when run by scheduler.

That's really wierd!

[/code]

[narasimha]

Just for more clarification.
Can you explain what you mean when you say "by hand" and "by scheduler" ?
Can you give details of how you are scheduling it (frequency)?
Do you have the default parameters set properly?

[fyaqq]

Just for more clarification.
Can you explain what you mean when you say "by hand" and "by scheduler" ?
by hand means click the run button in designer,
by scheduler means use director to schedule the job.
Can you give details of how you are scheduling it (frequency)?
It is iirelavent to the problem I think.
Do you have the default parameters set properly?

Yes.

[narasimha]

Just for more clarification.
Can you explain what you mean when you say "by hand" and "by scheduler" ?
by hand means click the run button in designer,
by scheduler means use director to schedule the job.
Can you give details of how you are scheduling it (frequency)?
It is iirelavent to the problem I think.
Do you have the default parameters set properly?

Yes.

That answers all my questions !

[ray.wurlod]

You need to keep diagnosing. Check that job parameters are being passed correctly. Find out where the errors are occurring. Every time there is any return status that is not SQL.SUCCESS (including SQL.SUCCESS.WITH.INFO), use SQLError() repeatedly to remove all associated information from the handle in question. Log any and all information obtained.
For example:

Code: Select all

hEnv = SQL.NULL.HENV
hDBC = SQL.NULL.HDBC
hStmt = SQL.NULL.HSTMT

ErrStatus = SQLAllocEnv(hEnv)
If ErrStatus <> SQL.SUCCESS
Then
   Loop
      ErrStatus = SQLError(hEnv, hDBC, hStmt, SQLState, DBMSCode, ErrorText)
   While ErrStatus <> SQL.ERROR And ErrStatus <> SQL.NO.DATA.FOUND
      Call DSLogInfo(SQLState : " " : DBMSCode : " " : ErrorText, "Debugging")
   Repeat
End

By initializing the handle variables to null handles, a lot less fiddling needs to be done with SQLError(), and means that you could create a generic internal subroutine for dealing with non-zero return codes.

[fyaqq]

Is it because of the ODBC driver license issue?

[ray.wurlod]

No. The "ODBC driver licence issue" does not occur on Windows platforms, as far as I am aware (unless, of course, you choose to use the Data Direct drivers).

If it were the licensing issue, the routine would not work under either scenario.

Keep detecting!