Solved

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

Posted on 2014-12-10
14
103 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 46

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 33

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

863 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

27 Experts available now in Live!

Get 1:1 Help Now