Solved

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

Posted on 2014-12-10
14
111 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 50

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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 24

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 24

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 24

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 24

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

710 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