Solved

DTS Schedule Fails - ODBC Connection to Progress Database

Posted on 2004-03-29
10
1,151 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
 
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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

867 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

17 Experts available now in Live!

Get 1:1 Help Now