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

[Vishal Nayak <vishme@NETSCAPE.NET>]

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