Solved

DTS Schedule Fails - ODBC Connection to Progress Database

Posted on 2004-03-29
10
1,153 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

839 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