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