Solved

DTS Schedule Fails - ODBC Connection to Progress Database

Posted on 2004-03-29
10
1,154 Views
Last Modified: 2012-08-13
using a Merant 3.70 32-bit Progress ODBC driver, i can append data to an existing table structure manually through a DTS package.  if i try to schedule it as a job within the SQL Server Agent, it fails.  Suggestions? ---ssam

following is the actual error message:

DTSRun:  Loading...   DTSRun:  Executing...   DTSRun OnStart:  Copy Data from Results to [SytelineImport].[dbo].[custaddr] Step   DTSRun OnError:  Copy Data from Results to [SytelineImport].[dbo].[custaddr] Step, Error = -2147467259 (80004005)      Error string:  [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS      Error source:  Microsoft OLE DB Provider for ODBC Drivers      Help file:        Help context:  0      Error Detail Records:      Error:  -2147467259 (80004005); Provider Error:  0 (0)      Error string:  [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS      Error source:  Microsoft OLE DB Provider for ODBC Drivers      Help file:        Help context:  0      DTSRun OnFinish:  Copy Data from Results to [SytelineImport].[dbo].[custaddr] Step   DTSRun:  Package execution complete.  Process Exit Code 1.  The step failed.
0
Comment
Question by:ssam
  • 4
  • 4
  • 2
10 Comments
 
LVL 3

Accepted Solution

by:
debi_mela earned 500 total points
ID: 10705919
When you schedule the package as job, it will use 'sqlservice' account and will run under that user. Please make sure this account has all rights to the folder in which you are reading / importing the file from/to.

In general when you run the dts package manually it will use your login.

0
 
LVL 34

Expert Comment

by:arbert
ID: 10706087
Actually, it will use the SQLAgent service account--not the SQLService account....
0
 
LVL 1

Author Comment

by:ssam
ID: 10706877
we've tried a number of different setups to get at this and they all fail.  we have tried both named users and system admins and they all give the same error.  is there a specific name to the default SQLAgent user that we could set up as a network admin? more suggestions?  thanks in advance.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:debi_mela
ID: 10706924
Right click on SQL ServerAgent, go to properties..

find out what is checked under,  the startup service account..

It should be either system account or user account.. (user account could be sqlservice).. Please verify.

0
 
LVL 1

Author Comment

by:ssam
ID: 10707071
done this many times - gone back and forth using either system or named account and stopping/starting service each time.  entered a variety of accounts that have domain admin priviledges.  no luck.  also no account called "sqlservice" anywhere.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10708714
Does the progress driver use the userid/password of the user that's logged in?

Idealy, you SQL Server is setup to have the SQLAgent service and MSSQLServer service run using a domain user account.  You make sure that this domain user has rights to all files, databases, and resources that are use by your DTS package...
0
 
LVL 34

Expert Comment

by:arbert
ID: 10708720
Like I said above, debi_mela was incorrect when the "sqlservice" statement was made--it's SQLAgent
0
 
LVL 1

Author Comment

by:ssam
ID: 10709436
understood about the disctinction between 'SQLAgent' vs. 'sqlservice'.

no, the progress driver is supposed to use a generic password via the odbc.  we have tried a variety of combinations ranging from no permisisons to all permissions both athte MSSQLServer service and SQL Agent levels.  
0
 
LVL 34

Expert Comment

by:arbert
ID: 10709508
My suggest, to get a better message, logon with the Account you have SQLAgent starting up with and run your dts package from designer....
0
 
LVL 1

Author Comment

by:ssam
ID: 10807937
persmissions was the correct direction, but the solution was actually an update/upgrade to the Progress client installed on the box where the SQL server resided.  Progress persmissions, not Windows permissions were at play here.  In addition, manual configuration of the Progress database wouldn't work without the udpate.
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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

749 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