Linked Server from SQL to Progress

Ross Culver

New Member
I am supplementing Syteline/Progress using MS SQL Server 7.0. I have no problems using Merant's 3.60 32-bit Progress ODBC drivers to link most MS products and Progress. I have no problem using a DSN connection using the same ODBC drivers to pull data from Progress using SQL's DTS wizards. However, I've had limited and sporatic success setting up a Linked Server in SQL to Progress using the exact same DSN and/or connection string.

I'm using MSDASQL 2.1.

I had a Linked Server setup and working, ran several stored procedures which properly retrieved data from Progress, and then, with nothing noticebly changed, it no longer worked. The error is the typical, non-descript message of "RC=-1; SQLCODE = -1; SQLSTATE="" Optional feature not implemented"

I've tried the SQL Server newsgroups, but no one there seems to know anything about Progress.

Help!

Ross
 
U

Unregistered

Guest
Linked Servers

Ross,

Can you help? You've gotten farther than we did. We are trying to create a linked server in SQL 7.0 to Progress 9.1 using the Merant Driver 3.60 for Progress 9.1b. Can you help us with the parameters . . . Product Name, Provider String. Any help would be greatly appreciated.

Jeff Becker
jbecker@omegacab.com
 

Ross Culver

New Member
Jeff:
Here's what we have so far.
Facts: Syteline 6.0, Progress 9.1, Merant ODBC 3.60 32-Bit Drivers, SQL Server 7.0 SP2 (don't load SP3!), MDAC 2.1 SP2 (2.1.2.4202.3) GA, NT 4.0 SP4. That covers what we're running.

Important! We are not currently utilizing Progress security, only Syteline's security (this will be important later).

First create a DSN using your ODBC Administrator. Leave the userID and passwords blank. Deselect the security required on the Advanced tab. Test it.

Using the following script in your SQL Query Analyzer, create a linked server where "tmwdb" = your progress database name and "TMW" = your DSN name. The path to your db may not be the same as below "G:\syteline.db"- change to what's appropriate for your network.

exec sp_addlinkedserver
@server='SYTELINE',
@SRVPRODUCT='MERANT 3.60 32-BIT PROGRESS',
@provider='MSDASQL',
@datasrc='TMW',
@provstr='DRIVER={MERANT 3.60 32-BIT PROGRESS};UID=;GST=0;SR=1;ASC=0;DBOS=Windows;DBPA=e:\syteline.db;DBAM=Direct;OIDH=tmwdb;OIDS=symixoib;OIDP=TCP;DB=tmw;DBPR=TCP;PWD=',
@location='G:\SYTELINE.DB',
@catalog='tmw'

Go

Go to the linked server from within SQL's Enterprise Manager. Test this linked server by clicking on the tables subdirectory. If your Progress tables are shown to the right, you're connected.

Now you can run scripts in the Query Analyzer against the Progress database. It's important to use the following OpenQuery syntax to get the results you want.: Where "Syteline" = your Linked Server name. You can, if desired, run update, delete and append statements using the same format.

select *
from OpenQuery('Syteline', 'select * from co where co.order-date = today')


But you can accomplish better results simply by building DTS packages using the DSN only (not the Linked Server). Either by using the DTS Wizard or better yet, by building the DTS package from scratch by right clicking on Local Packages under the Data Transformation Services directory. I say building from scratch is better because you can build a multitude of independent data retrieval scenarios with the same DTS Package, and then only have to manual run a couple of DTS packages.

This is the good news; the bad news comes when you want to take it to the next step, automation. We've found that we cannot run DTS packages which pull through the DSN from Progress using the Scheduler. Same thing when we try to run a SQL Agent Job on one of the saved scripts running against the Linked Server. The problem seems to be with the unacceptability of a null userid and password.

Now we are discussing the pros and cons of implementing Progress security (in addition to Syteline security) in hopes that a named UserID and password will allow us to schedule DTS's instead of having to run them manually.

What securities are you using?

Ross
 
U

Unregistered

Guest
Re: linked server to Progress from SQL

Ross,

thank you for the input, we have gotten the linked server working. We are using Progress security on our end.

We're taking your advice and exploring DTS but the OLE DB provider for Progress is returning the notorious syntax error (#7357) while doing a simple insert statement via DTS. Any experience with this on your end?


Feel free to call:
800-645-9442 ext 5889
or jbecker@omegacab.com

Thanks.
Jeff
 

amirameen

New Member
Hi Ross,
I am new to this group but using progress for a long time. Trying to established linked server connection with PROGRESS but recieving error OLE DB Error trace [IDBInitalize::Initialize returned 0x80004005]
My odbc configuration is as follow

System DSN: eb2live
Driver: DATADIRECT 4.10 32-BIT Progress SQL92 v9.1E
Host Name=aref15
Port Number=5035
Database Name=test
User ID=test
Password=test

Please tell me where I am doing mistake or any suggestion

Regards
 
Top