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

[Richard Wagner <richard.j.wagner@AC.COM>]

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


(Embedded image moved to file: pic08572.pcx)
Vishal Nayak <vishme@NETSCAPE.NET>
12/09/98 06:22 PM GMT
Please respond to SQR-USERS@USA.NET

To:   Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
cc:    (bcc: Richard J. Wagner)
Subject:  Re: [SQR Errors Calling an MSSQL Stored Procedure from within a
      SQL]




hi Rich,

First of all, a request to all the SQR users in this list.......
When we make a posting, let's keep these two steps into consideration so
that
the problem makes better sense to people :
1. When you post, make sure you atleast add the small snippet of code when
applicable, so that the problem can be well understood.
2. After the problem is solved, WHOEVER had the problem or query should
reply
back, stating how the problem was solved. In this case, when we have a lot
of
solutions suggested, we know what ultimately did the trick. This will help
us
when we later read the lists via the web.

i think this will improve our discussions a lot !! :)

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.

owner-sqr-users@list.iex.net wrote:

I'm having problems with SQR on a Microsoft SQL Server 6.5 platform.  I
have an SQR that calls a text file which contains SQL statements altering
an existing table.  The script is generated by PeopleSoft's Alter Table
process.   The last action the text file performs is an MSSQL stored
procedure, "sp_rename", which renames the table.

However, the SQR fails because it does not recognize the call to an MSSQL
stored procedure.  The message I receive is "Cannot Execute SQL."

The same SQR calling a similar script generated by PeopleSoft on an Oracle
platform, works correctly.

Does anyone know how to alter the SQR so it recognizes the stored procedure
as an MSSQL procedure?

Thanks in advance for any assistance.

-Rich.


____________________________________________________________________
More than just email--Get your FREE Netscape WebMail account today at
http://home.netscape.com/netcenter/mail

pic08572.pcx