Re: [Re: [SQR Errors Calling an MSSQL Stored Procedure from within a]

[Steve Krim <smkrim@TELEWEB.NET>]

rICH,

Are you sure this is SQR???  In my eperience - this should be a SQL
statement run through ISQL...

isql -Uname -Ppswd -Sserver < psalter.sql > psqlter.out

Steve
At 02:35 PM 12/9/98 PST, you wrote:
>Hi Rich,
>
>Is the "Drop table" clause failing?!! After the SQR exits out, just check if
>the PS_DUMMY_RECORD is failing ........ then, you can't rename it if it
>already exists........ just see if this is the case.
>
>did you try going through this script by firing the SQL statements in the SQL
>prompt one by one?! Try that also, if possible.
>
>Can you see what sp_rename does? Just try executing this statement on a
>temporary table...
>
>Vishal.
>
>owner-sqr-users@list.iex.net wrote:
>I have the following procedure below, which calls an SQL script previously
>assigned to $SQL.  The SQL script is below the
>procedure code.  The SQL executes until it gets to the sp_rename stored
>procedure.  It then dies saying that the SQL cannot be executed.  The
>script
>was generated by PeopleSoft's SQL Alter process.  The same SQR code works
>fine calling an SQL script on a Oracle platform.
>
>Thanks for any help..............
>
>-Rich.
>
>     begin-procedure Perform-DML
>
>        !show 'DML:'
>        !show 'Data Manipulation Language (DML):'
>        !show $Pretty-Text
>        show $SQL
>     begin-SQL
>        [$SQL]
>     end-SQL
>
>        if instr($SQL,'COMMIT',1) = 0
>           show 'Rows affected: ' #sql-count
>        end-if
>        show ''
>     end-procedure  !Perform-DML
>
>
>
>This is the value of $SQL:
>
>--
>-- WARNING:
>--
>-- This script should not be run in Data Mover.  It may contain platform
>-- specific syntax that Data Mover is unable to comprehend.  Please use the
>
>-- SQL query tool included with your database engine to process this
>script.
>--
>USE GPHR75DV
>go
>SET IMPLICIT_TRANSACTIONS ON
>go
>CREATE TABLE PS_1 (ZIP CHAR(10) NOT NULL,
>   ZIPITYDODA CHAR(10) NOT NULL,
>   STATE CHAR(6) NOT NULL)
>go
>INSERT INTO PS_1 (
>        ZIP,
>    ZIPITYDODA,
>    STATE)
>  SELECT
>        ZIP,
>    ZIPITYDODA,
>    ' '
>  FROM PS_DUMMY_RECORD
>go
>IF EXISTS (SELECT 'X' FROM SYSOBJECTS WHERE TYPE = 'U' AND NAME =
> 'PS_DUMMY_RECORD') DROP TABLE PS_DUMMY_RECORD
>go
>go sp_rename PS_1, PS_DUMMY_RECORD    <<-------------This is where it
>dies........
>go
>COMMIT
>go
>
>
>Vishal Nayak wrote :
>
>hi Rich,
>
>Coming back to your problem Rich, there are two execute statements to
>execute
>a stored procedure....... I think.... one for Ingres, and the other for
>Sybase
>and MS SQL Server.
>Please paste your code that is executing these SQL statements. Are you
>using
>begin-sql ?!! Does the SQL run correctly when executed from some iSQL
>environment ?!!
>
>Cheers,
>Vishal.
>
>
>
>____________________________________________________________________
>More than just email--Get your FREE Netscape WebMail account today at
http://home.netscape.com/netcenter/mail
>