Solved

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

Posted on 2014-12-10
14
102 Views
Last Modified: 2015-02-02
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.
0
Comment
Question by:branuda
  • 6
  • 4
  • 2
  • +2
14 Comments
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40491813
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
 
LVL 32

Expert Comment

by:ste5an
ID: 40491816
What errors do you get? Why don't you use SSIS?
0
 
LVL 38

Expert Comment

by:Hypercat (Deb)
ID: 40491827
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
 
LVL 23

Expert Comment

by:NVIT
ID: 40491835
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
 

Author Comment

by:branuda
ID: 40491989
We deploy this on client sites most of which have SQL Express - so no jobs.
0
 

Author Comment

by:branuda
ID: 40491991
The ODBC is 32-bit regardess of the O/S
0
 
LVL 38

Expert Comment

by:Hypercat (Deb)
ID: 40492025
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:branuda
ID: 40492051
As this runs perfectly and makes the correct connection from the batch file, we know that odbcad32.exe is correctly being used.
0
 

Author Comment

by:branuda
ID: 40492123
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
 

Author Comment

by:branuda
ID: 40493987
What about making the user a domain admin?
0
 
LVL 23

Expert Comment

by:NVIT
ID: 40494030
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
 
LVL 23

Expert Comment

by:NVIT
ID: 40494072
Does cached credentials provide a clue?
cmdkey /list

Open in new window

0
 

Author Comment

by:branuda
ID: 40504756
Thanks for that tip.  We will see if that provides any insight.
0
 
LVL 23

Expert Comment

by:NVIT
ID: 40569601
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now