From sqr-users-bounces@sqrug.org Sat Apr 01 00:02:09 2006 Date: Sat, 1 Apr 2006 00:00:02 -0500 From: Nathan Stratton Treadway Subject: [sqr-users] Monthly sqr-users Hints Posting sqr-users Mailing List Hints and Guidelines Nathan Stratton Treadway, Ray Ontko & Co. (nathanst@ontko.com) $Revision: 1.22 $ $Date: 2005/08/19 05:12:40 $ This document gathers in one location information about the sqr-users mail list. It includes instructions for changing your subscription settings (including unsubscribing), as well are guidelines for using the list. ______________________________________________________________________ Table of Contents 1. Changes Since Previous Version 2. Introduction 2.1 sqr-users Mailing List 2.2 sqr-users Hints and Guidelines Document 2.3 Related Mailing Lists 3. Interacting with the list server 3.1 Subscribing to the list 3.2 Unsubscribing from the list 3.3 When your email address changes 3.4 Digests 3.5 Obtaining your password 3.6 Problems 4. Posting to the sqr-users List 4.1 General Guidelines 4.2 Posting a Message 4.3 Replying to a Message 5. Job Announcements ______________________________________________________________________ 1. Changes Since Previous Version Changes since the version dated 2004/03/04: o New name and URL for "HyperionSQR" mailing list. 2. Introduction 2.1. sqr-users Mailing List The sqr-users mailing list was created to enable discussion related to the SQR database language from Hyperion (and to other products in the SQR product suite). Note: SQR used to be published by SQRIBE Technologies, which was merged into Brio Technology on August 3, 1999. Brio Technology changed its name to Brio Software in October 2001. On October 16, 2003, Hyperion acquired Brio Software. SQR and related applications have also been called "Brio.Report" and "Brio Reports". The mailing list is provided as a service of Ray Ontko & Co. A searchable archive of past postings to the mailing list is available from the SQR Users Group web site . 2.2. sqr-users Hints and Guidelines Document This document gives various hints and guidelines about the sqr-users mailing list. It will be posted approximately monthly to the mailing list so that new users can see it (and existing users can find it easily). An HTML version of the document is always available at http://www.sqrug.org/sqr-users/sqr-users-hints.html. If you have comments or suggestions about this document, please contact me directly at the address listed in the title section. This document is Copyright (C) 1999-2004 by Ray Ontko & Co. It may be freely copied and distributed provided it is not modified in any way and it retains the original copyright notice. 2.3. Related Mailing Lists Here are some mailing lists that cover topics relating to SQR in some way. sqr-jobs : SQR Jobs This list may be used for posting SQR-related resumes, recruitments, job announcements, project announcments, help- wanted, work-wanted ads, etc. To join, follow the link above, or send any message to "sqr-jobs-subscribe@yahoogroups.com". peoplesoft-fans : PeopleSoft Fans Moderated list covering technical and functional aspects of PeopleSoft. To join, follow the link above, or send any message to "peoplesoft-fans-subscribe@yahoogroups.com". psusers : PeopleSoft Users For questions related to any aspect of PeopleSoft (including PeopleSoft-specific SQR issues). This list has merged with the Peoplesoft Fans mailing list and is no longer active, but the message achive can still be found at the link above. HyperionSQR : Hyperion- SQR List for discussion of SQR, SQR Developer (formerly called Brio.Report and Report Builder), and SQR iServer. To join, follow the link above, or send any message to "HyperionSQR- subscribe@yahoogroups.com". rm-users : Report- Mart Users List for discussion of Brio Portal (previously called ReportMart). To join, follow the link above, or send a message containing the word "subscribe" in the body to "rm-users- request@sqrug.org". brioportalusersgroup : Brio Portal Users Group list List for discussion of Brio Portal. To join, follow the link above, or send any message to "brioportalusersgroup- subscribe@yahoogroups.com". brio-l : OpenITx Brio List List for technical and functional discussion of all Brio products (but most messages are currently about Brio Intelligence). To join, follow the link above. briousers : Brio.Enter- prise Users List for discussion of the Brio.Enterprise (now called Intelligence) product. To join, follow the link above, or send any message to "briousers-subscribe@yahoogroups.com". briolist : Brio List List for discussion of Brio Intelligence. To join, follow the link above, or send any message to "briolist- subscribe@yahoogroups.com". 3. Interacting with the list server The sqr-users mailing list is hosted using the Mailman list server package. All of the Mailman functions described below are accessed from the sqr-users List Info page, "http://www.sqrug.org/mailman/listinfo/sqr-users". 3.1. Subscribing to the list To subscribe to the mailing list, enter your e-mail address in the box at the top of the "Subscribing to sqr-users" section on the sqr-users List Info page, then choose a password to assign to your subscription and enter it in both "Password" boxes. This password is used to allow you to securely make changes to your subscription over the web. This password will be e-mailed back to you at varous times, so you should not use the same password as you are using elsewhere. Once you have filled in the password, click the "Subscribe" button. You should soon receive an e-mail message from the list server requesting confirmation of your subscription request. Once you have replied to this message and your subscription has been processed, you will receive a "welcome" message giving you more information on the list server, and as well as a reminder of the subscription password you chose. 3.2. Unsubscribing from the list To unsubscribe from the list, go to the sqr-users List Info page, enter your e-mail address in the "sqr-users Subscribers" section near the bottom of the page, and click the "Unsubscribe or edit options" button. You will then see the "member options" page; fill in your password at the top of the page and click "Log in". (If you have forgotton your password, see the ``Obtaining your Password'' section, below). This will take you to the "membership configuration" page. In the middle of that page, there will be an "Unsubscribing from sqr- users" section; simply turn on the "Yes, I really want to unsubscribe" checkbox and click the "Unsubscribe" button. You should get a page back saying that the removal was successful (as well as a confirmation email message). The removal will take effect immediately; there is no need to send in a confirmation e-mail message. If you don't know your password, you can choose to unsubscribe using e-mail confirmation. Instead of entering the password on the "member options" page, just click the "Unsubscribe" button found just below the "Log In" button. You will then see a page noting that a confirmation e-mail has been sent; once you follow the confirmation instructions in that e-mail your removal will be complete. 3.3. When your email address changes If your e-mail address changes -- for example, if your company changes names, or if your mail administrator changes the way your name is spelled out or abbreviated -- you will need to update your subscription to match your new address before you can post to the list (see the ``Posting'' section, below). To do this, simply log in to your "membership configuration" page (see the directions for logging in to this page in the ``Unsubscribing'' section, above). At the top of that page you'll see a "Changing your membership information" section. Fill out the text boxes and click the "Change My Address and Name" button. You will be sent a confirmation e-mail, and once you have responded to that e-mail your subscription will be updated and you can begin posting from your new address. 3.4. Digests You can request to get your messages in digest form (one large message containing all of the posts made each day). To do this, log in to your "membership configuration" page (see the directions on doing this in the ``Unsubscribing'' section, above). At the bottom of the page, you will see a section titled "Your sqr-users Subscription Options". Change the "Set Digest Mode" option (the second option in the list) to "On" and click the "Submit My Changes" button at the bottom of the page. You actually have a choice of two different digest formats, "MIME" or "Plain Text". The MIME format sends each individual list message as a separate MIME attachment to the digest; the plain text format consists of one large text message, with all of the individual messages run together. Which format you will want to use depends on your mail- reading software (and personal preference). If you are not sure which format will work best, you can just leave the default setting of "MIME" and see if the next morning's digest is easy to read in your mail reader -- you can change to the other format at any time using that same web page. If you use digests, you should not just "reply" to the digest mailing. Instead, be sure your message's subject header matches the subject in the original posting to which you are replying, and that you don't include a quoted copy of the entire digest in your post. To restore normal distribution, simply follow the above instructions choosing the "Off" setting for the Digest option. 3.5. Obtaining your password You will not be able to change your subscription options without your password. If you have forgotten your password, simply click the "Email My Password to Me" button on the "member options" page and your password will be emailed to you. You will also be e-mailed a copy of your password at the beginning of each month, as part of a "mailing list memberships reminder" message from the Mailman software. 3.6. Problems If the above instructions do not work, please contact the list manager at "sqr-users-manager@sqrug.org". (Do not send a message to the list itself -- no one there can help you with your problem!) Please include a description of what you were trying to do and what exactly happened when you made the attempt. 4. Posting to the sqr-users List In order to post to the mailing list, you must be subscribed to it (see directions above). More specifically, in order for Mailman to recognize that this message was sent by a subscriber, the return address on your message much exactly match the addresss you used to subscribe -- if you have more than one address you will need to be sure that you are sending from the correct one. (See the ``When you email address changes'' section, above, if your e-mail address has changed since you subscribed.) 4.1. General Guidelines For any message you send to the list, please: o make sure your mail software is not requesting a "Return Receipt". (If a "Return Receipt Requested" message is sent to the list, several dozen receipt messages will be sent out to the mailing list as other subscribers' mail systems repond....) o do not send messages in HTML or other special formats. (These are not usable by many subscribers, especially those who receive their messages in digest format.) The list server will delete these special attachments from your message and send you a warning notice. Also, if you are trying to send (for example) SQR source code files, make sure your mail-reader software is assigning proper MIME types to your attachments or they may not be distributed. o keep in mind that over 500 people will receive your message, and that it will appear in the list archives. Try to keep your messages on-topic and relatively short. Consider sending your message directly to particular people if it won't interest many of the list's subscribers. o ignore abusive and inappropriate messages that get sent to the list, rather than getting drawn into a debate. The very nature of these messages will cause most subscribers to automatically discredit them -- no "response" is necessary. To the contrary, any response will only "add fuel to the fire," prolonging the controversy and further interupting the normal conversation on the list. 4.2. Posting a Message Once you are subscribed, you can post to the list by sending a message to "sqr-users@sqrug.org". Be sure your subject heading describes your specific problem or question (i.e. don't just use "SQR Question"). In the body of your message, please include the following information: o SQR product name and version number (see notes below) o Operating system name and version number o Database name and version number o Application name (i.e. PeopleSoft) and a version number, if any Also, include a short code sample whenever possible. It's much easier for other people to understand what you are trying to do when they can see the actual code. (At the same time, be careful not to post confidential or proprietary code, since the posting will be placed in a public web archive.) Note that your question should relate to SQR or other Brio Report product in some way. Don't ask questions about PeopleSoft, for example, unless you are having SQR-related issues. (For PeopleSoft- specific questions, see other lists mentioned in the ``Related Mailing Lists'' section.) In 2001, PeopleSoft purchased from Brio the rights to the source code for SQR and began releasing its own line of SQR. PeopleSoft's SQR version numbering is different from Brio/Hyperion's, so it's very important to mention whether you are using Hyperion's "SQR" or "SQR for PeopleSoft". (This info is included automatically if you include the full SQR version-id string as described below.) To get the version-id string for your copy of SQR, you can run the following program: begin-program show $sqr-ver end-program This will produce a line that looks something like this: SQR/3.0.15/Intel/SCO Unix R3.2 V5.0/Oracle 7.2.2.3/Sep 25 1996 Include the full line of output in your message. Note that the oper- ating system and database version numbers listed here are the ones under which this copy of SQR was compiled, so you'll still need to include this information for your own environment. In a command-line environment (Unix, etc.), you can also get this version-id string by saying sqr -id 4.3. Replying to a Message Messages sent out on the list have a Reply-To header pointing to the list. This means that you should be able to simply use your mail reader's "reply" function to send a reply to the list. Generally, all answers to questions sent out on the list should be public replies, so that other subscribers can benefit. In those cases where a private reply is appropriate, be sure to over-ride the default Reply-To address. If your message is not actually a reply to the earlier message, please do not use the "reply" function. Instead, address a new e-mail message to the list as described in the previous section. (This is because the list archives will incorrectly show the new message in the original thread if you use "reply".) You should quote enough lines from the original message that later readers can understand your answer even if they missed the original question. At the same time, you should remove any unnecessary lines from the quote in order to make your message shorter and easier to understand. (Remember that some people read the mailing list in digest form, and the digests become much larger if unnecessary lines are quoted.) 5. Job Announcements Please do not post job announcements on the sqr-users list, but use the sqr-jobs list instead. (See ``Related Mailing Lists'' for more information on this list.) A large number of sqr-users subscribers do not want to receive job announcements (for various reasons) and such posts tend to cause a lot of disruption of the discussion on the list. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Mon Apr 03 10:23:18 2006 Date: Mon, 3 Apr 2006 10:20:37 -0400 From: "Violet Martin" Subject: [sqr-users] Dynamic Query Variable Error I am receiving the following error message when I use a Dynamic Query Variable in my sqr. The sql runs fine when it is coded C.INSTITUTION =3D $INSTUTITION, without the Dynamic Query Variable. It looks like the dynamic SQL was built correctly, but I cannot determine what ORA-00911: invalid character is not being built correctly. =20 =20 $start_dt 01-JUL-2005 $end_dt 30-JUN-2006 $institution RC278 $ALL_or_ONE $institution Report Date 04/03/2006 (SQR 5528) ORACLE OCIStmtExecute error 911 in cursor 5: ORA-00911: invalid character SQL: SELECT A.EMPLID, A.ACAD_CAREER, A.INSTITUTION, A.STRM, A.CRSE_GRADE_OFF, A.CLASS_NBR, A.SESSION_CODE, A.STDNT_ENRL_STATUS, B.NAME, C.ACAD_CAREER=20 FROM PS_STDNT_ENRL A, PS_NAMES B, PS_CLASS_TBL C, PS_CLASS_ATTRIBUTE D WHERE A.EMPLID =3D B.EMPLID AND B.NAME_TYPE =3D 'PRI' AND B.EFFDT =3D (SELECT MAX(B_ED.EFFDT) FROM PS_NAMES B_ED WHERE B.EMPLID =3D B_ED.EMPLID AND B.NAME_TYPE =3D B_ED.NAME_TYPE AND B_ED.EFFDT <=3D :1) AND A.ACAD_CAREER =3D C.ACAD_CAREER AND A.INSTITUTION =3D C.INSTITUTION AND A.STRM =3D C.STRM AND A.CLASS_NBR =3D C.CLASS_NBR AND C.SESSION_CODE =3D A.SESSION_CODE AND C.END_DT BETWEEN :2 and :3 AND C.ENRL_TOT > 0 AND C.CRSE_ID =3D D.CRSE_ID AND C.CRSE_OFFER_NBR =3D D.CRSE_OFFER_NBR AND C.STRM =3D D.STRM AND C.SESSION_CODE =3D D.SESSION_CODE AND C.CLASS_SECTION =3D D.CLASS_SECTION AND D.CRSE_ATTR =3D '*WDS' AND D.CRSE_ATTR_VALUE =3D '*OPEN' AND C.INSTITUTION =3D $institution order by b.name, a.emplid=20 =20 Error on line 203: (SQR 3722) Could not set up cursor. =20 =20 This is how I am defining the variable: begin-procedure Get-Values let $institution =3D UPPER(&RUNCNTL.INSTITUTION) let $start_dt =3D &RUNCNTL.START_DATE let $end_dt =3D &RUNCNTL.END_DATE let $ALL_or_ONE =3D $institution =20 This is the where clause in the sqr: =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20 FROM PS_STDNT_ENRL A, PS_NAMES B, PS_CLASS_TBL C, PS_CLASS_ATTRIBUTE D WHERE A.EMPLID =3D B.EMPLID AND B.NAME_TYPE =3D 'PRI' AND B.EFFDT =3D (SELECT MAX(B_ED.EFFDT) FROM PS_NAMES B_ED WHERE B.EMPLID =3D B_ED.EMPLID AND B.NAME_TYPE =3D B_ED.NAME_TYPE AND B_ED.EFFDT <=3D $asoftoday) AND A.ACAD_CAREER =3D C.ACAD_CAREER AND A.INSTITUTION =3D C.INSTITUTION AND A.STRM =3D C.STRM AND A.CLASS_NBR =3D C.CLASS_NBR AND C.SESSION_CODE =3D A.SESSION_CODE AND C.END_DT BETWEEN $START_DT and $END_DT AND C.ENRL_TOT > 0 AND C.CRSE_ID =3D D.CRSE_ID AND C.CRSE_OFFER_NBR =3D D.CRSE_OFFER_NBR AND C.STRM =3D D.STRM AND C.SESSION_CODE =3D D.SESSION_CODE AND C.CLASS_SECTION =3D D.CLASS_SECTION AND D.CRSE_ATTR =3D '*WDS' AND D.CRSE_ATTR_VALUE =3D '*OPEN' AND C.INSTITUTION =3D [$ALL_or_ONE] order by b.name, a.emplid =20 Any help would be greatly appreciated. =20 Violet A. Martin Systems Analyst Application Support Center Virginia Community College System (804) 423-6746 =20 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Mon Apr 03 10:33:56 2006 Subject: RE: [sqr-users] Dynamic Query Variable Error Date: Mon, 3 Apr 2006 09:31:30 -0500 From: "Jim Womeldorf" Hi Violet, I believe you are getting your error at compile time. SQR has a bit of trouble getting things correct when you have subqueries within a dynamic SQL query. Try the following: let $blank =3D '' FROM PS_STDNT_ENRL A, PS_NAMES B, PS_CLASS_TBL C, PS_CLASS_ATTRIBUTE D WHERE A.EMPLID =3D B.EMPLID AND B.NAME_TYPE =3D 'PRI' AND B.EFFDT =3D (SELECT MAX(B_ED.EFFDT) FROM [$blank] PS_NAMES B_ED WHERE B.EMPLID =3D B_ED.EMPLID AND B.NAME_TYPE =3D B_ED.NAME_TYPE AND B_ED.EFFDT <=3D $asoftoday) AND A.ACAD_CAREER =3D C.ACAD_CAREER AND A.INSTITUTION =3D C.INSTITUTION AND A.STRM =3D C.STRM AND A.CLASS_NBR =3D C.CLASS_NBR AND C.SESSION_CODE =3D A.SESSION_CODE AND C.END_DT BETWEEN $START_DT and $END_DT AND C.ENRL_TOT > 0 AND C.CRSE_ID =3D D.CRSE_ID AND C.CRSE_OFFER_NBR =3D D.CRSE_OFFER_NBR AND C.STRM =3D D.STRM AND C.SESSION_CODE =3D D.SESSION_CODE AND C.CLASS_SECTION =3D D.CLASS_SECTION AND D.CRSE_ATTR =3D '*WDS' AND D.CRSE_ATTR_VALUE =3D '*OPEN' AND C.INSTITUTION =3D [$ALL_or_ONE] order by b.name, a.emplid =20 I believe this will move the statement into the run time arena. Jim -----Original Message----- From: sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org [mailto:sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org] On Behalf Of Violet Martin Sent: Monday, April 03, 2006 9:21 AM To: sqr-users@sqrug.org Subject: [sqr-users] Dynamic Query Variable Error I am receiving the following error message when I use a Dynamic Query Variable in my sqr. The sql runs fine when it is coded C.INSTITUTION =3D $INSTUTITION, without the Dynamic Query Variable. It looks like the dynamic SQL was built correctly, but I cannot determine what ORA-00911: invalid character is not being built correctly. =20 =20 $start_dt 01-JUL-2005 $end_dt 30-JUN-2006 $institution RC278 $ALL_or_ONE $institution Report Date 04/03/2006 (SQR 5528) ORACLE OCIStmtExecute error 911 in cursor 5: ORA-00911: invalid character SQL: SELECT A.EMPLID, A.ACAD_CAREER, A.INSTITUTION, A.STRM, A.CRSE_GRADE_OFF, A.CLASS_NBR, A.SESSION_CODE, A.STDNT_ENRL_STATUS, B.NAME, C.ACAD_CAREER=20 FROM PS_STDNT_ENRL A, PS_NAMES B, PS_CLASS_TBL C, PS_CLASS_ATTRIBUTE D WHERE A.EMPLID =3D B.EMPLID AND B.NAME_TYPE =3D 'PRI' AND B.EFFDT =3D (SELECT MAX(B_ED.EFFDT) FROM PS_NAMES B_ED WHERE B.EMPLID =3D B_ED.EMPLID AND B.NAME_TYPE =3D B_ED.NAME_TYPE AND B_ED.EFFDT <=3D :1) AND A.ACAD_CAREER =3D C.ACAD_CAREER AND A.INSTITUTION =3D C.INSTITUTION AND A.STRM =3D C.STRM AND A.CLASS_NBR =3D C.CLASS_NBR AND C.SESSION_CODE =3D A.SESSION_CODE AND C.END_DT BETWEEN :2 and :3 AND C.ENRL_TOT > 0 AND C.CRSE_ID =3D D.CRSE_ID AND C.CRSE_OFFER_NBR =3D D.CRSE_OFFER_NBR AND C.STRM =3D D.STRM AND C.SESSION_CODE =3D D.SESSION_CODE AND C.CLASS_SECTION =3D D.CLASS_SECTION AND D.CRSE_ATTR =3D '*WDS' AND D.CRSE_ATTR_VALUE =3D '*OPEN' AND C.INSTITUTION =3D $institution order by b.name, a.emplid=20 =20 Error on line 203: (SQR 3722) Could not set up cursor. =20 =20 This is how I am defining the variable: begin-procedure Get-Values let $institution =3D UPPER(&RUNCNTL.INSTITUTION) let $start_dt =3D &RUNCNTL.START_DATE let $end_dt =3D &RUNCNTL.END_DATE let $ALL_or_ONE =3D $institution =20 This is the where clause in the sqr: =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20 FROM PS_STDNT_ENRL A, PS_NAMES B, PS_CLASS_TBL C, PS_CLASS_ATTRIBUTE D WHERE A.EMPLID =3D B.EMPLID AND B.NAME_TYPE =3D 'PRI' AND B.EFFDT =3D (SELECT MAX(B_ED.EFFDT) FROM PS_NAMES B_ED WHERE B.EMPLID =3D B_ED.EMPLID AND B.NAME_TYPE =3D B_ED.NAME_TYPE AND B_ED.EFFDT <=3D $asoftoday) AND A.ACAD_CAREER =3D C.ACAD_CAREER AND A.INSTITUTION =3D C.INSTITUTION AND A.STRM =3D C.STRM AND A.CLASS_NBR =3D C.CLASS_NBR AND C.SESSION_CODE =3D A.SESSION_CODE AND C.END_DT BETWEEN $START_DT and $END_DT AND C.ENRL_TOT > 0 AND C.CRSE_ID =3D D.CRSE_ID AND C.CRSE_OFFER_NBR =3D D.CRSE_OFFER_NBR AND C.STRM =3D D.STRM AND C.SESSION_CODE =3D D.SESSION_CODE AND C.CLASS_SECTION =3D D.CLASS_SECTION AND D.CRSE_ATTR =3D '*WDS' AND D.CRSE_ATTR_VALUE =3D '*OPEN' AND C.INSTITUTION =3D [$ALL_or_ONE] order by b.name, a.emplid =20 Any help would be greatly appreciated. =20 Violet A. Martin Systems Analyst Application Support Center Virginia Community College System (804) 423-6746 =20 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Mon Apr 03 10:37:15 2006 From: "the dragon" Subject: RE: [sqr-users] Dynamic Query Variable Error Date: Mon, 03 Apr 2006 09:34:10 -0500 Violet, I think the problem is the splats (*) in the where clause D.CRSE_ATTR = '*WDS' AND D.CRSE_ATTR_VALUE = '*OPEN' . I believe Oracle views the splats as wild cards, in which case you wouldn't be able to compare with an equal sign. hth. let me know. peace, clark PSA: Salary <> Slavery. If you earn a salary, your employer is renting your services for 40 hours a week, not purchasing your soul. Your time is the only real finite asset that you have, and once used it can never be recovered, so don't waste it by giving it away. I work to live; I don't live to work. "Time is the coin of your life. It is the only coin you have, and only you can determine how it will be spent. Be careful lest you let other people spend it for you." Carl Sandburg (1878 - 1967) ----Original Message Follows---- I am receiving the following error message when I use a Dynamic Query Variable in my sqr. The sql runs fine when it is coded C.INSTITUTION = $INSTUTITION, without the Dynamic Query Variable. It looks like the dynamic SQL was built correctly, but I cannot determine what ORA-00911: invalid character is not being built correctly. $start_dt 01-JUL-2005 $end_dt 30-JUN-2006 $institution RC278 $ALL_or_ONE $institution Report Date 04/03/2006 (SQR 5528) ORACLE OCIStmtExecute error 911 in cursor 5: ORA-00911: invalid character SQL: SELECT A.EMPLID, A.ACAD_CAREER, A.INSTITUTION, A.STRM, A.CRSE_GRADE_OFF, A.CLASS_NBR, A.SESSION_CODE, A.STDNT_ENRL_STATUS, B.NAME, C.ACAD_CAREER FROM PS_STDNT_ENRL A, PS_NAMES B, PS_CLASS_TBL C, PS_CLASS_ATTRIBUTE D WHERE A.EMPLID = B.EMPLID AND B.NAME_TYPE = 'PRI' AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_NAMES B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.NAME_TYPE = B_ED.NAME_TYPE AND B_ED.EFFDT <= :1) AND A.ACAD_CAREER = C.ACAD_CAREER AND A.INSTITUTION = C.INSTITUTION AND A.STRM = C.STRM AND A.CLASS_NBR = C.CLASS_NBR AND C.SESSION_CODE = A.SESSION_CODE AND C.END_DT BETWEEN :2 and :3 AND C.ENRL_TOT > 0 AND C.CRSE_ID = D.CRSE_ID AND C.CRSE_OFFER_NBR = D.CRSE_OFFER_NBR AND C.STRM = D.STRM AND C.SESSION_CODE = D.SESSION_CODE AND C.CLASS_SECTION = D.CLASS_SECTION AND D.CRSE_ATTR = '*WDS' AND D.CRSE_ATTR_VALUE = '*OPEN' AND C.INSTITUTION = $institution order by b.name, a.emplid Error on line 203: (SQR 3722) Could not set up cursor. This is how I am defining the variable: begin-procedure Get-Values let $institution = UPPER(&RUNCNTL.INSTITUTION) let $start_dt = &RUNCNTL.START_DATE let $end_dt = &RUNCNTL.END_DATE let $ALL_or_ONE = $institution This is the where clause in the sqr: FROM PS_STDNT_ENRL A, PS_NAMES B, PS_CLASS_TBL C, PS_CLASS_ATTRIBUTE D WHERE A.EMPLID = B.EMPLID AND B.NAME_TYPE = 'PRI' AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_NAMES B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.NAME_TYPE = B_ED.NAME_TYPE AND B_ED.EFFDT <= $asoftoday) AND A.ACAD_CAREER = C.ACAD_CAREER AND A.INSTITUTION = C.INSTITUTION AND A.STRM = C.STRM AND A.CLASS_NBR = C.CLASS_NBR AND C.SESSION_CODE = A.SESSION_CODE AND C.END_DT BETWEEN $START_DT and $END_DT AND C.ENRL_TOT > 0 AND C.CRSE_ID = D.CRSE_ID AND C.CRSE_OFFER_NBR = D.CRSE_OFFER_NBR AND C.STRM = D.STRM AND C.SESSION_CODE = D.SESSION_CODE AND C.CLASS_SECTION = D.CLASS_SECTION AND D.CRSE_ATTR = '*WDS' AND D.CRSE_ATTR_VALUE = '*OPEN' AND C.INSTITUTION = [$ALL_or_ONE] order by b.name, a.emplid Any help would be greatly appreciated. Violet A. Martin Systems Analyst Application Support Center Virginia Community College System (804) 423-6746 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _________________________________________________________________ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Mon Apr 03 14:08:53 2006 From: "Alexander, Steven" Subject: RE: [sqr-users] Dynamic Query Variable Error Date: Mon, 3 Apr 2006 11:05:42 -0700 Look at the SQL statement that was sent to Oracle. It says "AND C.INSTITUTION = $institution". Oracle doesn't know what $institution is. It doesn't even know that $institution is a variable within your SQR program. You cannot have an SQR variable within the text of a dynamic variable. You wrote: AND C.INSTITUTION = [$institution] That was an unnecessary use of a dynamic variable; after all, it worked when you wrote: AND C.INSTITUTION = $institution Or, you could write: Let $institution = 'AND C.INSTITUTION = ''' || UPPER(&RUNCNTL.INSTITUTION) || '''' And replace the whole line with [$institution] -----Original Message----- From: Violet Martin [mailto:vmartin@vccs.edu] Sent: Monday, April 03, 2006 7:21 AM To: sqr-users@sqrug.org Subject: [sqr-users] Dynamic Query Variable Error I am receiving the following error message when I use a Dynamic Query Variable in my sqr. The sql runs fine when it is coded C.INSTITUTION = $INSTUTITION, without the Dynamic Query Variable. It looks like the dynamic SQL was built correctly, but I cannot determine what ORA-00911: invalid character is not being built correctly. $start_dt 01-JUL-2005 $end_dt 30-JUN-2006 $institution RC278 $ALL_or_ONE $institution Report Date 04/03/2006 (SQR 5528) ORACLE OCIStmtExecute error 911 in cursor 5: ORA-00911: invalid character SQL: SELECT A.EMPLID, A.ACAD_CAREER, A.INSTITUTION, A.STRM, A.CRSE_GRADE_OFF, A.CLASS_NBR, A.SESSION_CODE, A.STDNT_ENRL_STATUS, B.NAME, C.ACAD_CAREER FROM PS_STDNT_ENRL A, PS_NAMES B, PS_CLASS_TBL C, PS_CLASS_ATTRIBUTE D WHERE A.EMPLID = B.EMPLID AND B.NAME_TYPE = 'PRI' AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_NAMES B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.NAME_TYPE = B_ED.NAME_TYPE AND B_ED.EFFDT <= :1) AND A.ACAD_CAREER = C.ACAD_CAREER AND A.INSTITUTION = C.INSTITUTION AND A.STRM = C.STRM AND A.CLASS_NBR = C.CLASS_NBR AND C.SESSION_CODE = A.SESSION_CODE AND C.END_DT BETWEEN :2 and :3 AND C.ENRL_TOT > 0 AND C.CRSE_ID = D.CRSE_ID AND C.CRSE_OFFER_NBR = D.CRSE_OFFER_NBR AND C.STRM = D.STRM AND C.SESSION_CODE = D.SESSION_CODE AND C.CLASS_SECTION = D.CLASS_SECTION AND D.CRSE_ATTR = '*WDS' AND D.CRSE_ATTR_VALUE = '*OPEN' AND C.INSTITUTION = $institution order by b.name, a.emplid Error on line 203: (SQR 3722) Could not set up cursor. This is how I am defining the variable: begin-procedure Get-Values let $institution = UPPER(&RUNCNTL.INSTITUTION) let $start_dt = &RUNCNTL.START_DATE let $end_dt = &RUNCNTL.END_DATE let $ALL_or_ONE = $institution This is the where clause in the sqr: FROM PS_STDNT_ENRL A, PS_NAMES B, PS_CLASS_TBL C, PS_CLASS_ATTRIBUTE D WHERE A.EMPLID = B.EMPLID AND B.NAME_TYPE = 'PRI' AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_NAMES B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.NAME_TYPE = B_ED.NAME_TYPE AND B_ED.EFFDT <= $asoftoday) AND A.ACAD_CAREER = C.ACAD_CAREER AND A.INSTITUTION = C.INSTITUTION AND A.STRM = C.STRM AND A.CLASS_NBR = C.CLASS_NBR AND C.SESSION_CODE = A.SESSION_CODE AND C.END_DT BETWEEN $START_DT and $END_DT AND C.ENRL_TOT > 0 AND C.CRSE_ID = D.CRSE_ID AND C.CRSE_OFFER_NBR = D.CRSE_OFFER_NBR AND C.STRM = D.STRM AND C.SESSION_CODE = D.SESSION_CODE AND C.CLASS_SECTION = D.CLASS_SECTION AND D.CRSE_ATTR = '*WDS' AND D.CRSE_ATTR_VALUE = '*OPEN' AND C.INSTITUTION = [$ALL_or_ONE] order by b.name, a.emplid Any help would be greatly appreciated. Violet A. Martin Systems Analyst Application Support Center Virginia Community College System (804) 423-6746 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Mon Apr 03 14:30:56 2006 Subject: RE: [sqr-users] Dynamic Query Variable Error Date: Mon, 3 Apr 2006 14:28:31 -0400 From: "Violet Martin" Thanks to everyone who gave their input. This is the solution that worked: Let $institution =3D 'AND C.INSTITUTION =3D ''' || UPPER(&RUNCNTL.INSTITUTION)|| '''' And replace the whole line with [$institution] Violet A. Martin Systems Analyst Application Support Center Virginia Community College System (804) 423-6746 -----Original Message----- From: sqr-users-bounces+vmartin=3Dvccs.edu@sqrug.org [mailto:sqr-users-bounces+vmartin=3Dvccs.edu@sqrug.org] On Behalf Of Alexander, Steven Sent: Monday, April 03, 2006 2:06 PM To: 'This list is for discussion about the SQR database reportinglanguagefrom Hyperion Solutions.' Subject: RE: [sqr-users] Dynamic Query Variable Error Look at the SQL statement that was sent to Oracle. It says "AND C.INSTITUTION =3D $institution". Oracle doesn't know what $institution is. It doesn't even know that $institution is a variable within your SQR program. You cannot have an SQR variable within the text of a dynamic variable. You wrote: AND C.INSTITUTION =3D [$institution] That was an unnecessary use of a dynamic variable; after all, it worked when you wrote: AND C.INSTITUTION =3D $institution Or, you could write: Let $institution =3D 'AND C.INSTITUTION =3D ''' || UPPER(&RUNCNTL.INSTITUTION) || '''' And replace the whole line with [$institution] -----Original Message----- From: Violet Martin [mailto:vmartin@vccs.edu]=20 Sent: Monday, April 03, 2006 7:21 AM To: sqr-users@sqrug.org Subject: [sqr-users] Dynamic Query Variable Error I am receiving the following error message when I use a Dynamic Query Variable in my sqr. The sql runs fine when it is coded C.INSTITUTION =3D $INSTUTITION, without the Dynamic Query Variable. It looks like the dynamic SQL was built correctly, but I cannot determine what ORA-00911: invalid character is not being built correctly. =20 =20 $start_dt 01-JUL-2005 $end_dt 30-JUN-2006 $institution RC278 $ALL_or_ONE $institution Report Date 04/03/2006 (SQR 5528) ORACLE OCIStmtExecute error 911 in cursor 5: ORA-00911: invalid character SQL: SELECT A.EMPLID, A.ACAD_CAREER, A.INSTITUTION, A.STRM, A.CRSE_GRADE_OFF, A.CLASS_NBR, A.SESSION_CODE, A.STDNT_ENRL_STATUS, B.NAME, C.ACAD_CAREER=20 FROM PS_STDNT_ENRL A, PS_NAMES B, PS_CLASS_TBL C, PS_CLASS_ATTRIBUTE D WHERE A.EMPLID =3D B.EMPLID AND B.NAME_TYPE =3D 'PRI' AND B.EFFDT =3D (SELECT MAX(B_ED.EFFDT) FROM PS_NAMES B_ED WHERE B.EMPLID =3D B_ED.EMPLID AND B.NAME_TYPE =3D B_ED.NAME_TYPE AND B_ED.EFFDT <=3D :1) AND A.ACAD_CAREER =3D C.ACAD_CAREER AND A.INSTITUTION =3D C.INSTITUTION AND A.STRM =3D C.STRM AND A.CLASS_NBR =3D C.CLASS_NBR AND C.SESSION_CODE =3D A.SESSION_CODE AND C.END_DT BETWEEN :2 and :3 AND C.ENRL_TOT > 0 AND C.CRSE_ID =3D D.CRSE_ID AND C.CRSE_OFFER_NBR =3D D.CRSE_OFFER_NBR AND C.STRM =3D D.STRM AND C.SESSION_CODE =3D D.SESSION_CODE AND C.CLASS_SECTION =3D D.CLASS_SECTION AND D.CRSE_ATTR =3D '*WDS' AND D.CRSE_ATTR_VALUE =3D '*OPEN' AND C.INSTITUTION =3D $institution order by b.name, a.emplid=20 =20 Error on line 203: (SQR 3722) Could not set up cursor. =20 =20 This is how I am defining the variable: begin-procedure Get-Values let $institution =3D UPPER(&RUNCNTL.INSTITUTION) let $start_dt =3D &RUNCNTL.START_DATE let $end_dt =3D &RUNCNTL.END_DATE let $ALL_or_ONE =3D $institution =20 This is the where clause in the sqr: =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20 FROM PS_STDNT_ENRL A, PS_NAMES B, PS_CLASS_TBL C, PS_CLASS_ATTRIBUTE D WHERE A.EMPLID =3D B.EMPLID AND B.NAME_TYPE =3D 'PRI' AND B.EFFDT =3D (SELECT MAX(B_ED.EFFDT) FROM PS_NAMES B_ED WHERE B.EMPLID =3D B_ED.EMPLID AND B.NAME_TYPE =3D B_ED.NAME_TYPE AND B_ED.EFFDT <=3D $asoftoday) AND A.ACAD_CAREER =3D C.ACAD_CAREER AND A.INSTITUTION =3D C.INSTITUTION AND A.STRM =3D C.STRM AND A.CLASS_NBR =3D C.CLASS_NBR AND C.SESSION_CODE =3D A.SESSION_CODE AND C.END_DT BETWEEN $START_DT and $END_DT AND C.ENRL_TOT > 0 AND C.CRSE_ID =3D D.CRSE_ID AND C.CRSE_OFFER_NBR =3D D.CRSE_OFFER_NBR AND C.STRM =3D D.STRM AND C.SESSION_CODE =3D D.SESSION_CODE AND C.CLASS_SECTION =3D D.CLASS_SECTION AND D.CRSE_ATTR =3D '*WDS' AND D.CRSE_ATTR_VALUE =3D '*OPEN' AND C.INSTITUTION =3D [$ALL_or_ONE] order by b.name, a.emplid =20 Any help would be greatly appreciated. =20 Violet A. Martin Systems Analyst Application Support Center Virginia Community College System (804) 423-6746 =20 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Wed Apr 05 11:15:11 2006 Date: Wed, 5 Apr 2006 20:43:11 +0530 From: "jiju vengal" Subject: Re: [sqr-users] Dynamic Query Variable Error Hi Violet.... You dont need to use the 'square brackets' at all....this statement would just be fine.... *AND C.INSTITUTION =3D $ALL_or_ONE* ** Dynamic SQL statements are not required when you are simply assigning the column value to a variable.... That should work! Regards, Jiju Vengal. Peoplesoft Practice Group Cognizant Technology Solutions. On 4/3/06, Violet Martin wrote: > > I am receiving the following error message when I use a Dynamic Query > Variable in my sqr. The sql runs fine when it is coded C.INSTITUTION =3D > $INSTUTITION, without the Dynamic Query Variable. > > It looks like the dynamic SQL was built correctly, but I cannot > determine what ORA-00911: invalid character is not being built > correctly. > > > > > > $start_dt 01-JUL-2005 $end_dt 30-JUN-2006 $institution RC278 > $ALL_or_ONE $institution > Report Date 04/03/2006 > (SQR 5528) ORACLE OCIStmtExecute error 911 in cursor 5: > ORA-00911: invalid character > SQL: SELECT A.EMPLID, A.ACAD_CAREER, A.INSTITUTION, A.STRM, > A.CRSE_GRADE_OFF, > A.CLASS_NBR, A.SESSION_CODE, A.STDNT_ENRL_STATUS, B.NAME, > C.ACAD_CAREER > FROM PS_STDNT_ENRL A, PS_NAMES B, PS_CLASS_TBL C, > PS_CLASS_ATTRIBUTE D > WHERE A.EMPLID =3D B.EMPLID AND B.NAME_TYPE =3D 'PRI' AND B.EFFDT = =3D > (SELECT > MAX(B_ED.EFFDT) FROM PS_NAMES B_ED WHERE B.EMPLID =3D B_ED.EMPLID > AND > B.NAME_TYPE =3D B_ED.NAME_TYPE AND B_ED.EFFDT <=3D :1) AND > A.ACAD_CAREER =3D > C.ACAD_CAREER AND A.INSTITUTION =3D C.INSTITUTION AND A.STRM =3D > C.STRM AND > A.CLASS_NBR =3D C.CLASS_NBR AND C.SESSION_CODE =3D A.SESSION_CODE AND > C.END_DT BETWEEN :2 and :3 AND C.ENRL_TOT > 0 AND C.CRSE_ID =3D > D.CRSE_ID > AND C.CRSE_OFFER_NBR =3D D.CRSE_OFFER_NBR AND C.STRM =3D D.STRM AND > C.SESSION_CODE =3D D.SESSION_CODE AND C.CLASS_SECTION =3D > D.CLASS_SECTION > AND D.CRSE_ATTR =3D '*WDS' AND D.CRSE_ATTR_VALUE =3D '*OPEN' AND > C.INSTITUTION =3D $institution order by b.name, a.emplid > > Error on line 203: > (SQR 3722) Could not set up cursor. > > > > > > This is how I am defining the variable: > > begin-procedure Get-Values > > let $institution =3D UPPER(&RUNCNTL.INSTITUTION) > > let $start_dt =3D > &RUNCNTL.START_DATE > > let $end_dt =3D > &RUNCNTL.END_DATE > > let $ALL_or_ONE =3D $institution > > > > This is the where clause in the sqr: > > > > FROM PS_STDNT_ENRL A, PS_NAMES B, PS_CLASS_TBL C, PS_CLASS_ATTRIBUTE D > > WHERE A.EMPLID =3D B.EMPLID > > AND B.NAME_TYPE =3D 'PRI' > > AND B.EFFDT =3D > > (SELECT MAX(B_ED.EFFDT) FROM PS_NAMES B_ED > > WHERE B.EMPLID =3D B_ED.EMPLID > > AND B.NAME_TYPE =3D B_ED.NAME_TYPE > > AND B_ED.EFFDT <=3D $asoftoday) > > AND A.ACAD_CAREER =3D C.ACAD_CAREER > > AND A.INSTITUTION =3D C.INSTITUTION > > AND A.STRM =3D C.STRM > > AND A.CLASS_NBR =3D C.CLASS_NBR > > AND C.SESSION_CODE =3D A.SESSION_CODE > > AND C.END_DT BETWEEN $START_DT and $END_DT > > AND C.ENRL_TOT > 0 > > AND C.CRSE_ID =3D D.CRSE_ID > > AND C.CRSE_OFFER_NBR =3D D.CRSE_OFFER_NBR > > AND C.STRM =3D D.STRM > > AND C.SESSION_CODE =3D D.SESSION_CODE > > AND C.CLASS_SECTION =3D D.CLASS_SECTION > > AND D.CRSE_ATTR =3D '*WDS' > > AND D.CRSE_ATTR_VALUE =3D '*OPEN' > > AND C.INSTITUTION =3D [$ALL_or_ONE] > > order by b.name, a.emplid > > > > Any help would be greatly appreciated. > > > > Violet A. Martin > > Systems Analyst > > Application Support Center > > Virginia Community College System > > (804) 423-6746 > > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Wed Apr 05 15:08:39 2006 From: "Joe Johnson" Date: Wed, 5 Apr 2006 13:06:01 -0600 Subject: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Greetings Group, I have seen this error posted in the history for SQRUG, but none of the posts seem to fit my error here. We are using SQR for Peoplesoft 8.47 and MS SQL Server 2000. I have a straightforward program that inserts Merit and Accomplishment data from a csv file into 4 tables. The majority of the fields that are inserted for each table are obtained by doing a select on that table. What I have run into is this: If the CSV file has more than 50 rows in it, it bombs with the following error: Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. The insert for '@P83' is this: Begin-sql insert into PS_JOB_JR (Columns listed .....) Values ...... ...... ,&JOBJR.FP_PT_END_DT ...... Etc. The value &JOBJR.FP_PT_END_DT is selected from PS_JOB_JR in a previous step. Here's the part that gets me -- if I cut the input csv file to 40 rows, there is no error. If I run the rest of the file, as long as it is less than 50 rows, there is no error. My file parameters being set are: #define RecLength 1500 #define RecType Vary What gives? There is no temp table involved, nor is there a stored procedure. Any help will be appreciated. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Wed Apr 05 16:11:18 2006 Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Date: Wed, 5 Apr 2006 15:08:41 -0500 From: "Jim Womeldorf" I assume you have examined the csv file closely to be certain it does not contain any strange characters like form feeds? Jim=20 -----Original Message----- From: sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org [mailto:sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 2:06 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Greetings Group, I have seen this error posted in the history for SQRUG, but none of the posts seem to fit my error here. We are using SQR for Peoplesoft 8.47 and MS SQL Server 2000. I have a straightforward program that inserts Merit and Accomplishment data from a csv file into 4 tables. The majority of the fields that are inserted for each table are obtained by doing a select on that table. What I have run into is this: If the CSV file has more than 50 rows in it, it bombs with the following error: Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. The insert for '@P83' is this: Begin-sql insert into PS_JOB_JR (Columns listed .....) Values ...... ...... ,&JOBJR.FP_PT_END_DT ...... Etc. The value &JOBJR.FP_PT_END_DT is selected from PS_JOB_JR in a previous step. Here's the part that gets me -- if I cut the input csv file to 40 rows, there is no error. If I run the rest of the file, as long as it is less than 50 rows, there is no error. My file parameters being set are: #define RecLength 1500=20=20=20=20=20=20=20=20 #define RecType Vary What gives? There is no temp table involved, nor is there a stored procedure.=20=20 Any help will be appreciated. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Wed Apr 05 16:18:44 2006 From: "Joe Johnson" Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Date: Wed, 5 Apr 2006 14:16:15 -0600 Yes - the file looks just as it should. Plus, I just successfully loaded the data, but not before I split the input file into 4 files and did each one separately. Equally strange - I would cut down each file until it ran successfully, saving off what I cut into an 'error' file. Then, without making any changes to the data, I ran the 'error' file, and it loaded successfully as well! That tells me the data is fine, but there must be something squirrelly in SQL Server, PeopleSoft ODBC, SQR. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org] On Behalf Of Jim Womeldorf Sent: Wednesday, April 05, 2006 2:09 PM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT buttheactual parameter not declared OUTPUT. I assume you have examined the csv file closely to be certain it does not contain any strange characters like form feeds? Jim -----Original Message----- From: sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org [mailto:sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 2:06 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Greetings Group, I have seen this error posted in the history for SQRUG, but none of the posts seem to fit my error here. We are using SQR for Peoplesoft 8.47 and MS SQL Server 2000. I have a straightforward program that inserts Merit and Accomplishment data from a csv file into 4 tables. The majority of the fields that are inserted for each table are obtained by doing a select on that table. What I have run into is this: If the CSV file has more than 50 rows in it, it bombs with the following error: Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. The insert for '@P83' is this: Begin-sql insert into PS_JOB_JR (Columns listed .....) Values ...... ...... ,&JOBJR.FP_PT_END_DT ...... Etc. The value &JOBJR.FP_PT_END_DT is selected from PS_JOB_JR in a previous step. Here's the part that gets me -- if I cut the input csv file to 40 rows, there is no error. If I run the rest of the file, as long as it is less than 50 rows, there is no error. My file parameters being set are: #define RecLength 1500 #define RecType Vary What gives? There is no temp table involved, nor is there a stored procedure. Any help will be appreciated. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Wed Apr 05 17:49:01 2006 Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Date: Wed, 5 Apr 2006 16:46:31 -0500 From: "Jim Womeldorf" This is a real stab in the dark, but I'd try 'show'ing the values before the insert to see if anything jumps out at you. Perhaps a COMMIT every so often would help? Like I said, stabbing in the dark :)=20 I'm totally unfamiliar with the 'Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT.' error. Jim -----Original Message----- From: sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org [mailto:sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 3:16 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Yes - the file looks just as it should. Plus, I just successfully loaded the data, but not before I split the input file into 4 files and did each one separately. Equally strange - I would cut down each file until it ran successfully, saving off what I cut into an 'error' file. Then, without making any changes to the data, I ran the 'error' file, and it loaded successfully as well! That tells me the data is fine, but there must be something squirrelly in SQL Server, PeopleSoft ODBC, SQR. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org] On Behalf Of Jim Womeldorf Sent: Wednesday, April 05, 2006 2:09 PM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT buttheactual parameter not declared OUTPUT. I assume you have examined the csv file closely to be certain it does not contain any strange characters like form feeds? Jim=20 -----Original Message----- From: sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org [mailto:sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 2:06 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Greetings Group, I have seen this error posted in the history for SQRUG, but none of the posts seem to fit my error here. We are using SQR for Peoplesoft 8.47 and MS SQL Server 2000. I have a straightforward program that inserts Merit and Accomplishment data from a csv file into 4 tables. The majority of the fields that are inserted for each table are obtained by doing a select on that table. What I have run into is this: If the CSV file has more than 50 rows in it, it bombs with the following error: Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. The insert for '@P83' is this: Begin-sql insert into PS_JOB_JR (Columns listed .....) Values ...... ...... ,&JOBJR.FP_PT_END_DT ...... Etc. The value &JOBJR.FP_PT_END_DT is selected from PS_JOB_JR in a previous step. Here's the part that gets me -- if I cut the input csv file to 40 rows, there is no error. If I run the rest of the file, as long as it is less than 50 rows, there is no error. My file parameters being set are: #define RecLength 1500=20=20=20=20=20=20=20=20 #define RecType Vary What gives? There is no temp table involved, nor is there a stored procedure.=20=20 Any help will be appreciated. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Wed Apr 05 17:55:34 2006 From: "Joe Johnson" Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Date: Wed, 5 Apr 2006 15:53:05 -0600 Thanks - I tried the show thing, all of the data (successful and bombed) looks exactly the same. I'll try the Commit approach. What's weird is that the data being inserted is very lite - it should be able to insert 90 rows of data without errors. I've also posted this with Microsoft - a Google search shows similar errors with other apps accessing SQL Server. I'll post my final findings to this group. Joe email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org] On Behalf Of Jim Womeldorf Sent: Wednesday, April 05, 2006 3:47 PM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT buttheactual parameter not declared OUTPUT. This is a real stab in the dark, but I'd try 'show'ing the values before the insert to see if anything jumps out at you. Perhaps a COMMIT every so often would help? Like I said, stabbing in the dark :) I'm totally unfamiliar with the 'Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT.' error. Jim -----Original Message----- From: sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org [mailto:sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 3:16 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Yes - the file looks just as it should. Plus, I just successfully loaded the data, but not before I split the input file into 4 files and did each one separately. Equally strange - I would cut down each file until it ran successfully, saving off what I cut into an 'error' file. Then, without making any changes to the data, I ran the 'error' file, and it loaded successfully as well! That tells me the data is fine, but there must be something squirrelly in SQL Server, PeopleSoft ODBC, SQR. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org] On Behalf Of Jim Womeldorf Sent: Wednesday, April 05, 2006 2:09 PM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT buttheactual parameter not declared OUTPUT. I assume you have examined the csv file closely to be certain it does not contain any strange characters like form feeds? Jim -----Original Message----- From: sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org [mailto:sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 2:06 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Greetings Group, I have seen this error posted in the history for SQRUG, but none of the posts seem to fit my error here. We are using SQR for Peoplesoft 8.47 and MS SQL Server 2000. I have a straightforward program that inserts Merit and Accomplishment data from a csv file into 4 tables. The majority of the fields that are inserted for each table are obtained by doing a select on that table. What I have run into is this: If the CSV file has more than 50 rows in it, it bombs with the following error: Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. The insert for '@P83' is this: Begin-sql insert into PS_JOB_JR (Columns listed .....) Values ...... ...... ,&JOBJR.FP_PT_END_DT ...... Etc. The value &JOBJR.FP_PT_END_DT is selected from PS_JOB_JR in a previous step. Here's the part that gets me -- if I cut the input csv file to 40 rows, there is no error. If I run the rest of the file, as long as it is less than 50 rows, there is no error. My file parameters being set are: #define RecLength 1500 #define RecType Vary What gives? There is no temp table involved, nor is there a stored procedure. Any help will be appreciated. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Wed Apr 05 18:04:11 2006 Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Date: Wed, 5 Apr 2006 17:02:19 -0500 From: "Engelkes, Kristy" I have seen a similar error. By saving the & references into actual $ and # variables before the insert have helped me in the past.=20=20 -----Original Message----- From: sqr-users-bounces+kristy.engelkes=3Drsmi.com@sqrug.org [mailto:sqr-users-bounces+kristy.engelkes=3Drsmi.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 4:53 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Thanks - I tried the show thing, all of the data (successful and bombed) looks exactly the same. I'll try the Commit approach. What's weird is that the data being inserted is very lite - it should be able to insert 90 rows of data without errors. I've also posted this with Microsoft - a Google search shows similar errors with other apps accessing SQL Server. I'll post my final findings to this group. Joe=20 email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org] On Behalf Of Jim Womeldorf Sent: Wednesday, April 05, 2006 3:47 PM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT buttheactual parameter not declared OUTPUT. This is a real stab in the dark, but I'd try 'show'ing the values before the insert to see if anything jumps out at you. Perhaps a COMMIT every so often would help? Like I said, stabbing in the dark :)=20 I'm totally unfamiliar with the 'Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT.' error. Jim -----Original Message----- From: sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org [mailto:sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 3:16 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Yes - the file looks just as it should. Plus, I just successfully loaded the data, but not before I split the input file into 4 files and did each one separately. Equally strange - I would cut down each file until it ran successfully, saving off what I cut into an 'error' file. Then, without making any changes to the data, I ran the 'error' file, and it loaded successfully as well! That tells me the data is fine, but there must be something squirrelly in SQL Server, PeopleSoft ODBC, SQR. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org] On Behalf Of Jim Womeldorf Sent: Wednesday, April 05, 2006 2:09 PM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT buttheactual parameter not declared OUTPUT. I assume you have examined the csv file closely to be certain it does not contain any strange characters like form feeds? Jim=20 -----Original Message----- From: sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org [mailto:sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 2:06 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Greetings Group, I have seen this error posted in the history for SQRUG, but none of the posts seem to fit my error here. We are using SQR for Peoplesoft 8.47 and MS SQL Server 2000. I have a straightforward program that inserts Merit and Accomplishment data from a csv file into 4 tables. The majority of the fields that are inserted for each table are obtained by doing a select on that table. What I have run into is this: If the CSV file has more than 50 rows in it, it bombs with the following error: Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. The insert for '@P83' is this: Begin-sql insert into PS_JOB_JR (Columns listed .....) Values ...... ...... ,&JOBJR.FP_PT_END_DT ...... Etc. The value &JOBJR.FP_PT_END_DT is selected from PS_JOB_JR in a previous step. Here's the part that gets me -- if I cut the input csv file to 40 rows, there is no error. If I run the rest of the file, as long as it is less than 50 rows, there is no error. My file parameters being set are: #define RecLength 1500=20=20=20=20=20=20=20=20 #define RecType Vary What gives? There is no temp table involved, nor is there a stored procedure.=20=20 Any help will be appreciated. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users DISCLAIMER: This e-mail is only intended for the person(s) to whom it is addressed and = may contain confidential information. Unless stated to the contrary, any op= inions or comments are personal to the writer and do not represent the offi= cial view of the company. If you have received this e-mail in error, please= notify us immediately by reply e-mail and then delete this message from yo= ur system. Please do not copy it or use it for any purposes, or disclose it= s contents to any other person. Thank you for your cooperation. Any advice contained in this email (including any attachments unless expres= sly stated otherwise) is not intended or written to be used, and cannot be = used, for purposes of avoiding tax penalties that may be imposed on any tax= payer. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Wed Apr 05 18:13:55 2006 From: "Joe Johnson" Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Date: Wed, 5 Apr 2006 16:11:26 -0600 When you had the error, did it bomb only on select fields? I'm inserting into PS_JOB, PS_JOB_JR, PS_COMPENSATION, and PS_ACCOMPLISHMENTS. The error occurs on the 83rd field for PS_JOB_JR. Thanks, Joe email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org] On Behalf Of Engelkes, Kristy Sent: Wednesday, April 05, 2006 4:02 PM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. I have seen a similar error. By saving the & references into actual $ and # variables before the insert have helped me in the past. -----Original Message----- From: sqr-users-bounces+kristy.engelkes=rsmi.com@sqrug.org [mailto:sqr-users-bounces+kristy.engelkes=rsmi.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 4:53 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Thanks - I tried the show thing, all of the data (successful and bombed) looks exactly the same. I'll try the Commit approach. What's weird is that the data being inserted is very lite - it should be able to insert 90 rows of data without errors. I've also posted this with Microsoft - a Google search shows similar errors with other apps accessing SQL Server. I'll post my final findings to this group. Joe email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org] On Behalf Of Jim Womeldorf Sent: Wednesday, April 05, 2006 3:47 PM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT buttheactual parameter not declared OUTPUT. This is a real stab in the dark, but I'd try 'show'ing the values before the insert to see if anything jumps out at you. Perhaps a COMMIT every so often would help? Like I said, stabbing in the dark :) I'm totally unfamiliar with the 'Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT.' error. Jim -----Original Message----- From: sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org [mailto:sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 3:16 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Yes - the file looks just as it should. Plus, I just successfully loaded the data, but not before I split the input file into 4 files and did each one separately. Equally strange - I would cut down each file until it ran successfully, saving off what I cut into an 'error' file. Then, without making any changes to the data, I ran the 'error' file, and it loaded successfully as well! That tells me the data is fine, but there must be something squirrelly in SQL Server, PeopleSoft ODBC, SQR. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org] On Behalf Of Jim Womeldorf Sent: Wednesday, April 05, 2006 2:09 PM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT buttheactual parameter not declared OUTPUT. I assume you have examined the csv file closely to be certain it does not contain any strange characters like form feeds? Jim -----Original Message----- From: sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org [mailto:sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 2:06 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Greetings Group, I have seen this error posted in the history for SQRUG, but none of the posts seem to fit my error here. We are using SQR for Peoplesoft 8.47 and MS SQL Server 2000. I have a straightforward program that inserts Merit and Accomplishment data from a csv file into 4 tables. The majority of the fields that are inserted for each table are obtained by doing a select on that table. What I have run into is this: If the CSV file has more than 50 rows in it, it bombs with the following error: Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. The insert for '@P83' is this: Begin-sql insert into PS_JOB_JR (Columns listed .....) Values ...... ...... ,&JOBJR.FP_PT_END_DT ...... Etc. The value &JOBJR.FP_PT_END_DT is selected from PS_JOB_JR in a previous step. Here's the part that gets me -- if I cut the input csv file to 40 rows, there is no error. If I run the rest of the file, as long as it is less than 50 rows, there is no error. My file parameters being set are: #define RecLength 1500 #define RecType Vary What gives? There is no temp table involved, nor is there a stored procedure. Any help will be appreciated. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users DISCLAIMER: This e-mail is only intended for the person(s) to whom it is addressed and may contain confidential information. Unless stated to the contrary, any opinions or comments are personal to the writer and do not represent the official view of the company. If you have received this e-mail in error, please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any other person. Thank you for your cooperation. Any advice contained in this email (including any attachments unless expressly stated otherwise) is not intended or written to be used, and cannot be used, for purposes of avoiding tax penalties that may be imposed on any taxpayer. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Apr 06 10:14:11 2006 Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Date: Thu, 6 Apr 2006 09:12:00 -0500 From: "Engelkes, Kristy" It seems to have something to do with how big the dataset is you are working with or something like that. So when the input file is smaller, it doesn't hit whatever memory problem there is but once you hit the threshold (whatever that is), it then has the problem with keeping track of & variables from your select statement.=20=20 -----Original Message----- From: sqr-users-bounces+kristy.engelkes=3Drsmi.com@sqrug.org [mailto:sqr-users-bounces+kristy.engelkes=3Drsmi.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 5:11 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. When you had the error, did it bomb only on select fields? I'm inserting into PS_JOB, PS_JOB_JR, PS_COMPENSATION, and PS_ACCOMPLISHMENTS. The error occurs on the 83rd field for PS_JOB_JR. Thanks, Joe=20 email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org] On Behalf Of Engelkes, Kristy Sent: Wednesday, April 05, 2006 4:02 PM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. I have seen a similar error. By saving the & references into actual $ and # variables before the insert have helped me in the past.=20=20 -----Original Message----- From: sqr-users-bounces+kristy.engelkes=3Drsmi.com@sqrug.org [mailto:sqr-users-bounces+kristy.engelkes=3Drsmi.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 4:53 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Thanks - I tried the show thing, all of the data (successful and bombed) looks exactly the same. I'll try the Commit approach. What's weird is that the data being inserted is very lite - it should be able to insert 90 rows of data without errors. I've also posted this with Microsoft - a Google search shows similar errors with other apps accessing SQL Server. I'll post my final findings to this group. Joe=20 email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org] On Behalf Of Jim Womeldorf Sent: Wednesday, April 05, 2006 3:47 PM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT buttheactual parameter not declared OUTPUT. This is a real stab in the dark, but I'd try 'show'ing the values before the insert to see if anything jumps out at you. Perhaps a COMMIT every so often would help? Like I said, stabbing in the dark :)=20 I'm totally unfamiliar with the 'Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT.' error. Jim -----Original Message----- From: sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org [mailto:sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 3:16 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Yes - the file looks just as it should. Plus, I just successfully loaded the data, but not before I split the input file into 4 files and did each one separately. Equally strange - I would cut down each file until it ran successfully, saving off what I cut into an 'error' file. Then, without making any changes to the data, I ran the 'error' file, and it loaded successfully as well! That tells me the data is fine, but there must be something squirrelly in SQL Server, PeopleSoft ODBC, SQR. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org] On Behalf Of Jim Womeldorf Sent: Wednesday, April 05, 2006 2:09 PM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT buttheactual parameter not declared OUTPUT. I assume you have examined the csv file closely to be certain it does not contain any strange characters like form feeds? Jim=20 -----Original Message----- From: sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org [mailto:sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 2:06 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Greetings Group, I have seen this error posted in the history for SQRUG, but none of the posts seem to fit my error here. We are using SQR for Peoplesoft 8.47 and MS SQL Server 2000. I have a straightforward program that inserts Merit and Accomplishment data from a csv file into 4 tables. The majority of the fields that are inserted for each table are obtained by doing a select on that table. What I have run into is this: If the CSV file has more than 50 rows in it, it bombs with the following error: Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. The insert for '@P83' is this: Begin-sql insert into PS_JOB_JR (Columns listed .....) Values ...... ...... ,&JOBJR.FP_PT_END_DT ...... Etc. The value &JOBJR.FP_PT_END_DT is selected from PS_JOB_JR in a previous step. Here's the part that gets me -- if I cut the input csv file to 40 rows, there is no error. If I run the rest of the file, as long as it is less than 50 rows, there is no error. My file parameters being set are: #define RecLength 1500=20=20=20=20=20=20=20=20 #define RecType Vary What gives? There is no temp table involved, nor is there a stored procedure.=20=20 Any help will be appreciated. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users DISCLAIMER: This e-mail is only intended for the person(s) to whom it is addressed and may contain confidential information. Unless stated to the contrary, any opinions or comments are personal to the writer and do not represent the official view of the company. If you have received this e-mail in error, please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any other person. Thank you for your cooperation. Any advice contained in this email (including any attachments unless expressly stated otherwise) is not intended or written to be used, and cannot be used, for purposes of avoiding tax penalties that may be imposed on any taxpayer. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users DISCLAIMER: This e-mail is only intended for the person(s) to whom it is addressed and = may contain confidential information. Unless stated to the contrary, any op= inions or comments are personal to the writer and do not represent the offi= cial view of the company. If you have received this e-mail in error, please= notify us immediately by reply e-mail and then delete this message from yo= ur system. Please do not copy it or use it for any purposes, or disclose it= s contents to any other person. Thank you for your cooperation. Any advice contained in this email (including any attachments unless expres= sly stated otherwise) is not intended or written to be used, and cannot be = used, for purposes of avoiding tax penalties that may be imposed on any tax= payer. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Apr 06 10:37:02 2006 From: "Joe Johnson" Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Date: Thu, 6 Apr 2006 08:34:28 -0600 My select to get the bulk of the insert values goes after 3 tables that have a total column count of about 350. I do the select after I read the input file, which has 12 columns of data, 90 rows. Then I do a series of inserts. If it is an ODBC error, how do we address that? I'm not aware of a way to 'tell' ODBC to use more memory. Do I need to try a temp table to store the input data? Or would I reduce the memory load by selecting from 1 table, inserting back into that table, then selecting from the 2nd table, etc.? Thanks, Joe Direct: (505) 798-6480 email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org] On Behalf Of Engelkes, Kristy Sent: Thursday, April 06, 2006 8:12 AM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. It seems to have something to do with how big the dataset is you are working with or something like that. So when the input file is smaller, it doesn't hit whatever memory problem there is but once you hit the threshold (whatever that is), it then has the problem with keeping track of & variables from your select statement. -----Original Message----- From: sqr-users-bounces+kristy.engelkes=rsmi.com@sqrug.org [mailto:sqr-users-bounces+kristy.engelkes=rsmi.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 5:11 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. When you had the error, did it bomb only on select fields? I'm inserting into PS_JOB, PS_JOB_JR, PS_COMPENSATION, and PS_ACCOMPLISHMENTS. The error occurs on the 83rd field for PS_JOB_JR. Thanks, Joe email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org] On Behalf Of Engelkes, Kristy Sent: Wednesday, April 05, 2006 4:02 PM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. I have seen a similar error. By saving the & references into actual $ and # variables before the insert have helped me in the past. -----Original Message----- From: sqr-users-bounces+kristy.engelkes=rsmi.com@sqrug.org [mailto:sqr-users-bounces+kristy.engelkes=rsmi.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 4:53 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Thanks - I tried the show thing, all of the data (successful and bombed) looks exactly the same. I'll try the Commit approach. What's weird is that the data being inserted is very lite - it should be able to insert 90 rows of data without errors. I've also posted this with Microsoft - a Google search shows similar errors with other apps accessing SQL Server. I'll post my final findings to this group. Joe email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org] On Behalf Of Jim Womeldorf Sent: Wednesday, April 05, 2006 3:47 PM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT buttheactual parameter not declared OUTPUT. This is a real stab in the dark, but I'd try 'show'ing the values before the insert to see if anything jumps out at you. Perhaps a COMMIT every so often would help? Like I said, stabbing in the dark :) I'm totally unfamiliar with the 'Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT.' error. Jim -----Original Message----- From: sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org [mailto:sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 3:16 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Yes - the file looks just as it should. Plus, I just successfully loaded the data, but not before I split the input file into 4 files and did each one separately. Equally strange - I would cut down each file until it ran successfully, saving off what I cut into an 'error' file. Then, without making any changes to the data, I ran the 'error' file, and it loaded successfully as well! That tells me the data is fine, but there must be something squirrelly in SQL Server, PeopleSoft ODBC, SQR. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org] On Behalf Of Jim Womeldorf Sent: Wednesday, April 05, 2006 2:09 PM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT buttheactual parameter not declared OUTPUT. I assume you have examined the csv file closely to be certain it does not contain any strange characters like form feeds? Jim -----Original Message----- From: sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org [mailto:sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 2:06 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Greetings Group, I have seen this error posted in the history for SQRUG, but none of the posts seem to fit my error here. We are using SQR for Peoplesoft 8.47 and MS SQL Server 2000. I have a straightforward program that inserts Merit and Accomplishment data from a csv file into 4 tables. The majority of the fields that are inserted for each table are obtained by doing a select on that table. What I have run into is this: If the CSV file has more than 50 rows in it, it bombs with the following error: Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. The insert for '@P83' is this: Begin-sql insert into PS_JOB_JR (Columns listed .....) Values ...... ...... ,&JOBJR.FP_PT_END_DT ...... Etc. The value &JOBJR.FP_PT_END_DT is selected from PS_JOB_JR in a previous step. Here's the part that gets me -- if I cut the input csv file to 40 rows, there is no error. If I run the rest of the file, as long as it is less than 50 rows, there is no error. My file parameters being set are: #define RecLength 1500 #define RecType Vary What gives? There is no temp table involved, nor is there a stored procedure. Any help will be appreciated. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users DISCLAIMER: This e-mail is only intended for the person(s) to whom it is addressed and may contain confidential information. Unless stated to the contrary, any opinions or comments are personal to the writer and do not represent the official view of the company. If you have received this e-mail in error, please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any other person. Thank you for your cooperation. Any advice contained in this email (including any attachments unless expressly stated otherwise) is not intended or written to be used, and cannot be used, for purposes of avoiding tax penalties that may be imposed on any taxpayer. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users DISCLAIMER: This e-mail is only intended for the person(s) to whom it is addressed and may contain confidential information. Unless stated to the contrary, any opinions or comments are personal to the writer and do not represent the official view of the company. If you have received this e-mail in error, please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any other person. Thank you for your cooperation. Any advice contained in this email (including any attachments unless expressly stated otherwise) is not intended or written to be used, and cannot be used, for purposes of avoiding tax penalties that may be imposed on any taxpayer. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Apr 06 10:24:01 2006 Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Date: Thu, 6 Apr 2006 09:21:59 -0500 From: "Knapp, Richard" Where does the error message come from? Not SQR. Operating system? Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 2:06 PM To: 'This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions.' Subject: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Greetings Group, I have seen this error posted in the history for SQRUG, but none of the posts seem to fit my error here. We are using SQR for Peoplesoft 8.47 and MS SQL Server 2000. I have a straightforward program that inserts Merit and Accomplishment data from a csv file into 4 tables. The majority of the fields that are inserted for each table are obtained by doing a select on that table. What I have run into is this: If the CSV file has more than 50 rows in it, it bombs with the following error: Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. The insert for '@P83' is this: Begin-sql insert into PS_JOB_JR (Columns listed .....) Values ...... ...... ,&JOBJR.FP_PT_END_DT=20=20 ...... Etc. The value &JOBJR.FP_PT_END_DT is selected from PS_JOB_JR in a previous step. Here's the part that gets me -- if I cut the input csv file to 40 rows, there is no error. If I run the rest of the file, as long as it is less than 50 rows, there is no error. My file parameters being set are: #define RecLength 1500=20=20=20=20=20=20=20=20 #define RecType Vary What gives? There is no temp table involved, nor is there a stored procedure.=20=20 Any help will be appreciated. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Apr 06 10:33:05 2006 From: "Joe Johnson" Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Date: Thu, 6 Apr 2006 08:30:22 -0600 Operating System. The full error statement is: [Microsoft][ODBC SQL Server Driver][SQL Server]Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Thanks, Joe Direct: (505) 798-6480 email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org] On Behalf Of Knapp, Richard Sent: Thursday, April 06, 2006 8:22 AM To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT buttheactual parameter not declared OUTPUT. Where does the error message come from? Not SQR. Operating system? Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 2:06 PM To: 'This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions.' Subject: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Greetings Group, I have seen this error posted in the history for SQRUG, but none of the posts seem to fit my error here. We are using SQR for Peoplesoft 8.47 and MS SQL Server 2000. I have a straightforward program that inserts Merit and Accomplishment data from a csv file into 4 tables. The majority of the fields that are inserted for each table are obtained by doing a select on that table. What I have run into is this: If the CSV file has more than 50 rows in it, it bombs with the following error: Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. The insert for '@P83' is this: Begin-sql insert into PS_JOB_JR (Columns listed .....) Values ...... ...... ,&JOBJR.FP_PT_END_DT ...... Etc. The value &JOBJR.FP_PT_END_DT is selected from PS_JOB_JR in a previous step. Here's the part that gets me -- if I cut the input csv file to 40 rows, there is no error. If I run the rest of the file, as long as it is less than 50 rows, there is no error. My file parameters being set are: #define RecLength 1500 #define RecType Vary What gives? There is no temp table involved, nor is there a stored procedure. Any help will be appreciated. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Apr 06 15:07:50 2006 Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Date: Thu, 6 Apr 2006 14:05:43 -0500 From: "Knapp, Richard" And you do have the ODBC version of SQR?=20=20 Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org] On Behalf Of Joe Johnson Sent: Thursday, April 06, 2006 9:30 AM To: 'This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Operating System. The full error statement is: [Microsoft][ODBC SQL Server Driver][SQL Server]Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Thanks, Joe=20 Direct: (505) 798-6480=20 email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org] On Behalf Of Knapp, Richard Sent: Thursday, April 06, 2006 8:22 AM To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT buttheactual parameter not declared OUTPUT. Where does the error message come from? Not SQR. Operating system? Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 2:06 PM To: 'This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions.' Subject: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Greetings Group, I have seen this error posted in the history for SQRUG, but none of the posts seem to fit my error here. We are using SQR for Peoplesoft 8.47 and MS SQL Server 2000. I have a straightforward program that inserts Merit and Accomplishment data from a csv file into 4 tables. The majority of the fields that are inserted for each table are obtained by doing a select on that table. What I have run into is this: If the CSV file has more than 50 rows in it, it bombs with the following error: Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. The insert for '@P83' is this: Begin-sql insert into PS_JOB_JR (Columns listed .....) Values ...... ...... ,&JOBJR.FP_PT_END_DT=20=20 ...... Etc. The value &JOBJR.FP_PT_END_DT is selected from PS_JOB_JR in a previous step. Here's the part that gets me -- if I cut the input csv file to 40 rows, there is no error. If I run the rest of the file, as long as it is less than 50 rows, there is no error. My file parameters being set are: #define RecLength 1500=20=20=20=20=20=20=20=20 #define RecType Vary What gives? There is no temp table involved, nor is there a stored procedure.=20=20 Any help will be appreciated. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Apr 06 17:23:32 2006 From: "Joe Johnson" Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Date: Thu, 6 Apr 2006 15:21:00 -0600 Yes - I can run SQRW on one machine against a database on another. Joe Direct: (505) 798-6480 email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org] On Behalf Of Knapp, Richard Sent: Thursday, April 06, 2006 1:06 PM To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. And you do have the ODBC version of SQR? Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of Joe Johnson Sent: Thursday, April 06, 2006 9:30 AM To: 'This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Operating System. The full error statement is: [Microsoft][ODBC SQL Server Driver][SQL Server]Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Thanks, Joe Direct: (505) 798-6480 email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org] On Behalf Of Knapp, Richard Sent: Thursday, April 06, 2006 8:22 AM To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT But the actual parameter not declared OUTPUT. Where does the error message come from? Not SQR. Operating system? Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 2:06 PM To: 'This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions.' Subject: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but The actual parameter not declared OUTPUT. Greetings Group, I have seen this error posted in the history for SQRUG, but none of the posts seem to fit my error here. We are using SQR for Peoplesoft 8.47 and MS SQL Server 2000. I have a straightforward program that inserts Merit and Accomplishment data from a csv file into 4 tables. The majority of the fields that are inserted for each table are obtained by doing a select on that table. What I have run into is this: If the CSV file has more than 50 rows in it, it bombs with the following error: Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. The insert for '@P83' is this: Begin-sql insert into PS_JOB_JR (Columns listed .....) Values ...... ...... ,&JOBJR.FP_PT_END_DT ...... Etc. The value &JOBJR.FP_PT_END_DT is selected from PS_JOB_JR in a previous step. Here's the part that gets me -- if I cut the input csv file to 40 rows, there is no error. If I run the rest of the file, as long as it is less than 50 rows, there is no error. My file parameters being set are: #define RecLength 1500 #define RecType Vary What gives? There is no temp table involved, nor is there a stored procedure. Any help will be appreciated. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Apr 06 18:17:53 2006 Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Date: Thu, 6 Apr 2006 17:15:53 -0500 From: "Knapp, Richard" I don't think that is a useful criterion. I have the Oracle version and I can connect to a dozen different Oracle instances here but I cannot connect to a MYSQL database which supports ODBC connections. For that I would need the ODBC version of SQR. Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org] On Behalf Of Joe Johnson Sent: Thursday, April 06, 2006 4:21 PM To: 'This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Yes - I can run SQRW on one machine against a database on another. Joe=20 Direct: (505) 798-6480=20 email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org] On Behalf Of Knapp, Richard Sent: Thursday, April 06, 2006 1:06 PM To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. And you do have the ODBC version of SQR?=20=20 Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org] On Behalf Of Joe Johnson Sent: Thursday, April 06, 2006 9:30 AM To: 'This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Operating System. The full error statement is: [Microsoft][ODBC SQL Server Driver][SQL Server]Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Thanks, Joe=20 Direct: (505) 798-6480=20 email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org] On Behalf Of Knapp, Richard Sent: Thursday, April 06, 2006 8:22 AM To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT But the actual parameter not declared OUTPUT. Where does the error message come from? Not SQR. Operating system? Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 2:06 PM To: 'This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions.' Subject: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but=20 The actual parameter not declared OUTPUT. Greetings Group, I have seen this error posted in the history for SQRUG, but none of the posts seem to fit my error here. We are using SQR for Peoplesoft 8.47 and MS SQL Server 2000. I have a straightforward program that inserts Merit and Accomplishment data from a csv file into 4 tables. The majority of the fields that are inserted for each table are obtained by doing a select on that table. What I have run into is this: If the CSV file has more than 50 rows in it, it bombs with the following error: Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. The insert for '@P83' is this: Begin-sql insert into PS_JOB_JR (Columns listed .....) Values ...... ...... ,&JOBJR.FP_PT_END_DT=20=20 ...... Etc. The value &JOBJR.FP_PT_END_DT is selected from PS_JOB_JR in a previous step. Here's the part that gets me -- if I cut the input csv file to 40 rows, there is no error. If I run the rest of the file, as long as it is less than 50 rows, there is no error. My file parameters being set are: #define RecLength 1500=20=20=20=20=20=20=20=20 #define RecType Vary What gives? There is no temp table involved, nor is there a stored procedure.=20=20 Any help will be appreciated. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Fri Apr 07 10:51:57 2006 From: "Joe Johnson" Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Date: Fri, 7 Apr 2006 08:49:25 -0600 Richard, I guess I'm not sure what you mean by SQR ODBC driver. Is that the SQL Server ODBC Driver? Our PeopleSoft ODBC Driver is the version that came with our recent upgrade to PeopleTools 8.47.04. Our SQL Server ODBC Driver is 2000.86.1830.00. We run many SQR programs through PeopleSoft as well as some SQR through SQRW that function with no issues. The SQRW we are using is what the default PeopleTools installation installs. Thanks, Joe Johnson email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org] On Behalf Of Knapp, Richard Sent: Thursday, April 06, 2006 4:16 PM To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT buttheactual parameter not declared OUTPUT. I don't think that is a useful criterion. I have the Oracle version and I can connect to a dozen different Oracle instances here but I cannot connect to a MYSQL database which supports ODBC connections. For that I would need the ODBC version of SQR. Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of Joe Johnson Sent: Thursday, April 06, 2006 4:21 PM To: 'This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Yes - I can run SQRW on one machine against a database on another. Joe Direct: (505) 798-6480 email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org] On Behalf Of Knapp, Richard Sent: Thursday, April 06, 2006 1:06 PM To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. And you do have the ODBC version of SQR? Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of Joe Johnson Sent: Thursday, April 06, 2006 9:30 AM To: 'This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Operating System. The full error statement is: [Microsoft][ODBC SQL Server Driver][SQL Server]Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Thanks, Joe Direct: (505) 798-6480 email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=ernesthealth.com@sqrug.org] On Behalf Of Knapp, Richard Sent: Thursday, April 06, 2006 8:22 AM To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT But the actual parameter not declared OUTPUT. Where does the error message come from? Not SQR. Operating system? Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 2:06 PM To: 'This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions.' Subject: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but The actual parameter not declared OUTPUT. Greetings Group, I have seen this error posted in the history for SQRUG, but none of the posts seem to fit my error here. We are using SQR for Peoplesoft 8.47 and MS SQL Server 2000. I have a straightforward program that inserts Merit and Accomplishment data from a csv file into 4 tables. The majority of the fields that are inserted for each table are obtained by doing a select on that table. What I have run into is this: If the CSV file has more than 50 rows in it, it bombs with the following error: Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. The insert for '@P83' is this: Begin-sql insert into PS_JOB_JR (Columns listed .....) Values ...... ...... ,&JOBJR.FP_PT_END_DT ...... Etc. The value &JOBJR.FP_PT_END_DT is selected from PS_JOB_JR in a previous step. Here's the part that gets me -- if I cut the input csv file to 40 rows, there is no error. If I run the rest of the file, as long as it is less than 50 rows, there is no error. My file parameters being set are: #define RecLength 1500 #define RecType Vary What gives? There is no temp table involved, nor is there a stored procedure. Any help will be appreciated. Thanks, Joe Johnson Software Developer Ernest Health, Inc. joejohnson@ernesthealth.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Fri Apr 07 11:01:41 2006 Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Date: Fri, 7 Apr 2006 09:59:44 -0500 From: "Knapp, Richard" It's not just the driver. There is something about the SQR executable that limits its use to the database you specified when you bought (SQR or PeopleSoft). If you bought PS for SQL Server and all SQL Server supports is ODBC access, then you have an ODBC version of SQR. Of course, you also need the correct driver. My Oracle SQRW will not work with an ODBC database like MYSQL even if I specify an ODBC driver. Maybe Nathan or Ray could jump in here and flesh out our understanding. Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org] On Behalf Of Joe Johnson Sent: Friday, April 07, 2006 9:49 AM To: 'This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Richard, I guess I'm not sure what you mean by SQR ODBC driver. Is that the SQL Server ODBC Driver? Our PeopleSoft ODBC Driver is the version that came with our recent upgrade to PeopleTools 8.47.04. Our SQL Server ODBC Driver is 2000.86.1830.00. We run many SQR programs through PeopleSoft as well as some SQR through SQRW that function with no issues. The SQRW we are using is what the default PeopleTools installation installs. Thanks, Joe Johnson email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org] On Behalf Of Knapp, Richard Sent: Thursday, April 06, 2006 4:16 PM To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT buttheactual parameter not declared OUTPUT. I don't think that is a useful criterion. I have the Oracle version and I can connect to a dozen different Oracle instances here but I cannot connect to a MYSQL database which supports ODBC connections. For that I would need the ODBC version of SQR. Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org] On Behalf Of Joe Johnson Sent: Thursday, April 06, 2006 4:21 PM To: 'This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Yes - I can run SQRW on one machine against a database on another. Joe=20 Direct: (505) 798-6480=20 email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org] On Behalf Of Knapp, Richard Sent: Thursday, April 06, 2006 1:06 PM To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. And you do have the ODBC version of SQR?=20=20 Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org] On Behalf Of Joe Johnson Sent: Thursday, April 06, 2006 9:30 AM To: 'This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions.' Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but theactual parameter not declared OUTPUT. Operating System. The full error statement is: [Microsoft][ODBC SQL Server Driver][SQL Server]Formal parameter '@P83' was defined as OUTPUT but the actual parameter not declared OUTPUT. Thanks, Joe=20 Direct: (505) 798-6480=20 email: joejohnson@ernesthealth.com -----Original Message----- From: sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org [mailto:sqr-users-bounces+joejohnson=3Dernesthealth.com@sqrug.org] On Behalf Of Knapp, Richard Sent: Thursday, April 06, 2006 8:22 AM To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Subject: RE: [sqr-users] Formal parameter '@P83' was defined as OUTPUT But the actual parameter not declared OUTPUT. Where does the error message come from? Not SQR. Operating system? Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org] On Behalf Of Joe Johnson Sent: Wednesday, April 05, 2006 2:06 PM To: 'This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions.' Subject: [sqr-users] Formal parameter '@P83' was defined as OUTPUT but=20 The actual parameter not declared OUTPUT. Greetings Group, I have seen