Avatar of branuda
branuda asked on

Task Runs from Command Prompt but not Task Scheduler

We have a simple task that reads records from a Pervasive database using ODBC and writes them to a SQL database.  The task can be run from a command line but NOT from the task scheduler which throws all kinds of  ODBC connection errors (HandleError for every table).  Security, logins, etc. are all identical for the logged in user and the task.
Windows OSDatabases

Avatar of undefined
Last Comment

8/22/2022 - Mon
Patrick Bogers

What kind of job is it? batch? cmd or powershell?

Does the task use ODBC DSNs? If so, they might be defined for the user instead of the system.

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

There was a user DSN.  Now there is a System one but same results either way.

Make sure you don't mess up with 32bit and 64bit ODBC drivers. Task Scheduler might want to use 64bit executables.

we must use the 32 bit version to connect to pervasive.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

Than make sure you call 32bit tools inside of the cmd file, or call cmd.exe from the SysWoW64 folder (instead of system32) in your task, e.g. with
  %WinDir%\SysWoW64\cmd.exe /c c:\Scripts\KillTheDb.cmd

What are the exact ODBC errors being thrown?

For EVERY table (so I have just written [tablename] in this example):

Fri 12/20/2013 10:00:24 PM  (Error)  -
                                   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
   at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
   at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.Odbc.OdbcConnection.Open()
   at [tablename].CreateSchema(String tablename, Table table, Int32 connectionID)
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

Is there any exception listed in the error?  
Are you using ODBC to both read and write the data?  
Is the error occurring on the read or write portion?  
Did this work at one point and stop or has it never worked?
Does the error happen every time you run the scheduled task?

No exceptions.
Using ODBC to both read and write.
It looks like the error is on the read.
This did stop working but the customer claims that "nothing has changed".
This happens every time the task is run and NEVER when run from a command line.

At this point, I would suggest adding some debugging code to it.  Something is causing the HandleError and you need to figure out what it is.  Ideally, the debugging code would write to a log or event log and show the line(s) of code that cause or at least precede the error. It would also, hopefully, give you a better error with exception or error message that's being returned.
I've written apps (.NET and standard Win32) using the Pervasive ODBC driver (and Pervasive Managed Provider) that run as scheduled tasks without any problems so I know it's possible.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

If its server 2008 or later make sure you click the box which says "run with highest privileges"

Do you run the task under the default account or under this user account?

You can also try whether the ODBC Trace shows something (useful).
Call %WinDir%\system32\odbcad32 and %WinDir%\SysWoW64\odbcad32 (ODBC Administrator)d 32 bit), open tab Tracing, set a different log file for both, and start logging. After executing the task, stop tracing.
This will show
a) which driver is used
b) if there is some exchange at all
c) details about the failing call
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Changing servers that ran the task fixed the issue.