Solved

DTS Schedule Fails - ODBC Connection to Progress Database

Posted on 2004-03-29
10
1,150 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 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

12 Experts available now in Live!

Get 1:1 Help Now