Re: How to use SQR to extract data from SQL Server and put into PS in Oracle

[Franck Masson <franck.masson@BRIO.COM>]

You can use something like this:

The following SQR program example retrieves data from Oracle (the Sales
data source) and inserts it into Access (the Archive data source).

Begin-Program
  Do Main
End-Program

Begin-Procedure Main
Begin-Select -DB'DSN=Sales;UID=scott;PWD=tiger'
Ename, Job, Hiredate, Sal, Commission
  Do Insert
>From Emp
End-Select
End-procedure ! Main

Begin-procedure Insert
Begin-SQL -DB'DSN=Archive'
  insert into EmpArch (ename, job, hiredate, sal,
  commission) values (&Ename, &Job, &Hiredate, &Sal,
  &Commission)
End-SQL
End-Procedure ! Insert
The BEGIN-SELECT statement uses the -DB option to point to the Sales
ODBC data source. For each record that it retrieves, it calls the
procedure Insert. This procedure includes a BEGIN-SQL statement that
uses the -DB option to point to the Archive ODBC data source and inserts
the current record into the EmpArch table.


franck,

Joe Johnson wrote:
>
> Greetings, both groups!
>
> I logged this request with PS, but I thought someone in one of these
> two groups could shed some light for me.
>
> We need to extract data from an application using SQL Server. Our PS
> apps are on Oracle.
>
> I want to use an SQR to do this, but I'm not sure how to 'tell' the
> SQR that I want to go into a SQL Server database to get the data. Is
> the secret in the SQRW application? Do we need to configure the
> existing Oracle-flavored SQRW to read SQL Server? If so, how? If not,
> help!
>
> Thanks In Advance for your help.
>
> PS 7.0
> PT 7.0
> Oracle 8
> I have an ODBC connection to the SQL Server database
>
> Joe Johnson
> PeopleSoft Systems Analyst
> joejo@snellingcorp.com
> 972-776-1332
begin:          vcard
fn:             Franck Masson
n:              ;Franck Masson
org:            Brio Technology EMEA
adr;dom:        Le capitol;;55 av. des champs Pierreux;Nanterre;92000;;
email;internet: franck.masson@brio.com
title:          Sales Manager Northern Europe
tel;work:       33 1 55 69 36 00
tel;fax:        33 1 47 25 02 59
tel;home:       Mobile : 33 6 84 80 17 05
x-mozilla-cpt:  ;0
x-mozilla-html: FALSE
version:        2.1
end:            vcard