Solved

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

Posted on 2014-12-10
14
114 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +2
14 Comments
 
LVL 51

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 34

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 25

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
 

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 25

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 25

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 25

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

636 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