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

["Schelske, Steve K." <SKSchelske@AAAMICHIGAN.COM>]

I agree with the message that said to take the extra parameters off of the
BEGIN-SQL/BEGIN-SELECT and simply put the name of your ODBC data source as
the database name in the SQRW logon dialog.

I've been running SQRW against an Access databases and Excel spreadsheets
using ODBC definitions by doing what is described above without any
problems.

Is there a reason you need to code the connection information in your SQR
program?

-----Original Message-----
From: Discussion of SQR, Brio Technology's database reporting language
[mailto:SQR-USERS@list.iex.net]On Behalf Of Joe Johnson
Sent: Wednesday, September 27, 2000 10:40 AM
To: SQR-USERS@list.iex.net
Subject: Re: How to use SQR to extract data from SQL Server and put into
PS in Oracle


Thanks everyone, but I still can't get it to work.

I evened commented out everything but:

Begin-SQL -DB'DSN=SQLSrvr;UID=myname;PWD=mypassword'
End-SQL

To see if I could just connect to the database.

I still get the error message:

Error on line 31:
   (SQR 3730) Incorrect arguments for BEGIN-SQL:
 [-Cnn] [ON-ERROR=procedure[(arg1[,argi]...)]]
Begin-SQL   -DB'DSN=SQLSrvr;UID=myname;PWD=mypassword'

I have tried
Begin-Select -DB'DSN=SQLSrvr;UID=myname;PWD=mypassword'
and get the same basic error message.

I have double (triple)-checked my ODBC DSN.UID.PWD and they are correct.
Plus, I can use Access to link to this SQLServer database with no problems.

I know the culprit must be in my
-DB'DSN=SQLSrvr;UID=myname;PWD=mypassword'
but I can't for the life of me see any syntax error here.

What else can I try?

TIA!

Joe Johnson
PeopleSoft Systems Analyst
joejo@snellingcorp.com
972-776-1332
----- Original Message -----
Franck Masson wrote:
> 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