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

[Bill Beckner <Bbeckner@ICC.CC.IL.US>]

Try the -DB..etc without the single quote marks....so that it looks
something like this:

Begin-SQL -DBDSN=SQLSrvr;UID=myname;PWD=mypassword
End-SQL

Also, I usually set up the connect string into a variable
(move 'DSN=SQLSrvr;UID=myname;PWD=mypassword' to $Connect)

And then use the following:

Begin-SQL -DB$Connect
End-SQL

Hope this helps!
--
William Beckner -- Stunt Programmer (Just call me "Bill")
Illinois Central College, Rm L141 -- East Peoria, IL  61635-0001
PH: (309) 694-8419 -- FAX: (309) 694-8995
"Take my advice - Don't listen to me!"

                -----Original Message-----
                From:   Joe Johnson [mailto:jej1216@HOTMAIL.COM]
                Sent:   Wednesday, September 27, 2000 9: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