1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Linked Server from SQL to Progress

Discussion in 'DataServers and ODBC' started by Ross Culver, Apr 17, 2001.

  1. Ross Culver

    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
  2. Unregistered

    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
  3. Ross Culver

    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
  4. Unregistered

    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
  5. amirameen

    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

Share This Page