ODBC Bulk Update runs from command line but not from Task Scheduler

We have a batch routine that runs an ODBC bulk update to populate a SQL DB from another DB source.  It works fine from the command line but when run from the Task Scheduler, we get System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode).  Sometimes this can be addressed by creating an Administrator user with full rights to run the task but that user has to stay logged in.  it used to work perfectly with SYSTEM as the login but has stopped.  Lastly, this only happens when the ODBC database is on a different server than the SQL db to which the bulk update is being applied.
branudaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
SYSTEM account only has privileges in the local server and that's why it fails in remote servers.
I'm wondering why you have it in a task scheduler instead of a SQL Server job.
0
 
ste5anSenior DeveloperCommented:
What errors do you get? Why don't you use SSIS?
0
 
Hypercat (Deb)Commented:
What login credentials are you using for the ODBC connection? Does that login have sysadmin rights to the database? Also are you running this on a 32 or 64 bit machine?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
NVITCommented:
It used to work as SYSTEM because the database was local. Now that its on a different station, it must authenticate.

One possible fix is to add this to your batch file:
psexec \\computername -u domain\user -p password "command.exe"

Open in new window

0
 
branudaAuthor Commented:
We deploy this on client sites most of which have SQL Express - so no jobs.
0
 
branudaAuthor Commented:
The ODBC is 32-bit regardess of the O/S
0
 
Hypercat (Deb)Commented:
Yes, but on a 64-bit machine you need to use odbcad32.exe from the SYSWOW64 folder instead of the system32 one. I was just wondering if you had inadvertently used the wrong ODBC setup .exe which can cause login issues.
0
 
branudaAuthor Commented:
As this runs perfectly and makes the correct connection from the batch file, we know that odbcad32.exe is correctly being used.
0
 
branudaAuthor Commented:
The batch file is contained within an executable so we cannot use any switches.  This used to work with the data on two different servers using the SYSTEM login from the first.
0
 
branudaAuthor Commented:
What about making the user a domain admin?
0
 
NVITCommented:
Is it possible to run the main command via psexec? example:
psexec \\HostNameToUpdateDB -u DomainName\AdminName -p password "maincommand.exe"

Open in new window

0
 
NVITCommented:
Does cached credentials provide a clue?
cmdkey /list

Open in new window

0
 
branudaAuthor Commented:
Thanks for that tip.  We will see if that provides any insight.
0
 
NVITCommented:
Hi branuda... Just wondering if you still need help with this or not. Either way, if you would close your question appropriately we'd really appreciate it
0
All Courses

From novice to tech pro — start learning today.