DBISQL CMD Failing via Task Scheduler

[former_SQLA_member1694959]

Preface... I'm totally new to Sybase SQL Anywhere. I've created a CMD file that does 4 data pulls from our Sybase SQL Anywhere database. The CMD file resides on the C Drive of the server that the database is installed on. When I run the CMD file manually, it connects and pulls the data successfully. When I schedule it via the Task Scheduler, it fails with a result of (0x9). Here's one of my data pull lines in the CMD file:

dbisql -nogui -C "eng=sh_asa_server;dbn=ShData;uid=dba;pwd=sql" "SELECT AlertCode, Caption, ModuleCode, IntCode, StrCode, replace(replace(AlertMessage, CHAR(13) + CHAR(10), ' '), '|', '-') AS AlertMsg, Check1, Check2, Check3, Check4 FROM SH_Alerts WHERE Check3 = 1; OUTPUT TO Alerts.txt FORMAT ASCII DELIMITED BY '|' QUOTE ''" >nul 2>nul

Any assistance would be very greatly appreciated.

[chris_keating]

My guess is that the command shell results in a GUI that can be a poblem depending on how you setup the task. If you create the scheduled task as only when the user is logged on, the GUI is shown and it completes without error.

[former_SQLA_member1694959]

It's set up to run with the highest privileges and to run whether the user is logged on or not.

[former_SQLA_member1694959]

Also, I have it running under a domain admin account. The same one that I log on to the server with and can run it manually without issue.

[Breck_Carter]

The 0x9 might be "9 EXIT_UNABLE_TO_CONNECT Unable to connect to the database server" according to the Software Component Exit Codes.

If that is the case, perhaps it needs some TCPIP host port stuff in the connection string.

Whenever I need to wave a dead chicken over a connection string problem, I turn to my favorite template...

-c "ENG=ddd; DBN=ddd; UID=dba; PWD=sql; LINKS=TCPIP(HOST=123.123.123.123:49152; DOBROAD=NONE);"

[Baron]

Have you tried to set the scheduled job to be run as Hidden?, I don't know exactly whether it is called Hidden on an English Windows, but it is the last checkbox on the bottom under General Tab.

Another thing is to try is to create the scheduled job not as domain admin account, but as the user with which you succeed to start it as CMD or as .bat (it can be that the domain admin doesn't see the service or is missing privilages).

I think here is also important to know how is the DB started (as service or standalone...).

[former_SQLA_member1694959]

I have tried hidden and it still fails with (0x9). I log in to the server under the domain admin account when I created the job and any time that I run it. I've tried it under 2 different domain admin accounts.

How do I find out how the DB starts?

[former_SQLA_member1694959]

Breck - I added the LINKS section from yours to mine and it caused the manual run of the CMD file to fail/freeze. It never pulled any data.

[former_SQLA_member1694959]

I tried adding this to my -C, which worked when I ran it manually but the Task Scheduler job still failed for the (0x9): LINKS=TCPIP(HOST=GR-ALLTRACK:2638)

I verified that our DB is mapped to port 2638 and not 49152.

[Breck_Carter]

> not 49152

Yeah, that's why it's called a "template"... your HOST isn't 123.123.123.123 either 🙂

Off Topic: 49152 is the next port assigned when you start two SQL Anywhere servers on the same host.

[Breck_Carter]

Your scheduled task may not be allowed to write a file to the current folder which AFAIK is what the OUTPUT TO Alerts.txt is trying to do... do you even know what the "current folder" is in this context? (I don't 🙂

Anyway, maybe try OUTPUT TO 'C:\\\\full\\\\path\\\\Alerts.txt' FORMAT ... with the double slashes used to "escape" the backslash in a SQL Anywhere string literal.

[former_SQLA_member1694959]

Changing the order of the connection string (UN, PW, ENG, DB, LINKS), adding the TCPIP and mapping the full path of the OUTPUT TO has worked. Thank you all!! You've very much been sanity and life saviors!!!

[Baron]

Yes, OUTPUT TO Alerts.txt causes to try writing in the path where dbisql is located.

Maybe better to try without OUTPUT TO .... to see whether dbisql succeed in connecting to DB.

@Breck Carter, do you think that in this case the domain admin (the owner of the scheduled job) needs the write privilege, or the user who started the DB needs that privilege? I think the latter.

[Breck_Carter]

> do you think

Long answer... Here's what I think: Every time I think I know how privileges work, I'm wrong.

Short answer... I'm wrong 🙂

[Breck_Carter]

> the path where dbisql is located

Are you sure? This is the Task Scheduler we're talking about, and on the Create Task - General tab there is a "Location:" field which defaults to a single backslash... whatever that means... I cannot find any documentation for "Location:"

It could mean "where the task is located", it could mean "start in", and the backslash could mean "root folder" or "current folder".

[Breck_Carter]

> sanity

You're welcome. Feel free to come back with any questions you have... even easy questions... easy questions are the best because, well, they're easy 🙂

[former_SQLA_member1694959]

Changing the order of the connection string (UN, PW, ENG, DB, LINKS), adding the TCPIP and mapping the full path of the OUTPUT TO has worked. Thank you all!! You've very much been sanity and life saviors!!!