Create Stored Procedure

[Snowcrash_10]

I have written two Stored Procedures, one for Oracle and one for SQL Server,
that return NextID's from dynamic table/fields. My problem is that I have now
written CF scripts to send to clients to run on their DB to create the Stored
Procedures (sp) and neither of the scripts run correctly. I know the stord
procedures are correct and really that is not the problem. The problem is that
the script does not run correctly. For example, in Oracle, the query to
generate the sp starts :

<CFQUERY NAME="createSP" DATASOURCE="#dbDataSource#"
USERNAME="#dbUserName#" PASSWORD="#dbPassword#">
CREATE OR REPLACE PROCEDURE #storedProcedureName# (tableName IN varchar,
fieldName IN varchar, extraParam IN varchar, orderField IN varchar, orderParam
IN varchar, maxID OUT varchar, orderID OUT varchar)
IS
myTableName varchar(255);
myColumnName varchar(255);
myOrderField varchar(255);
BEGIN

maxID := 0 ;
orderID := 0 ;

....

End

and this runs without problem. I go to Oracle (9i) and open the stored
procedure and it shows that it has compiled with an error :

"Line # = 1 Column # = 181 Error Text = PLS-00103: Encountered the symbol ""
when expecting one of the following: ; is with authid as cluster order
using external deterministic parallel_enable pipelined "

Now if I copy the whole procedure within the Oracle environment and past it
straight back in and compile it again, I get no errors. I make absolutely no
change to any of the text.

Now when I try to run a similar scripts in SQL Server, I get an error before
the stored procedure is created which reads :

"[Macromedia][SQLServer JDBC Driver][SQLServer]Line 115: Incorrect syntax near
&apos;GO&apos;. Error Executing Database Query."

But then when I go into the SQL Server EM, the stored procedure has been
created and it compiles without error.

Anyone have any ideas about any of this.

Barb

[Snowcrash_10]

Woops, lied, the stored procedure in SQL Server doe snot get created at all. I
had an old version in my DB. But when the error is thrown, I get an output of
what the Create Stored Procedure statement looks like. If I copy this exactly
into SQL Analyzer and run it, the SP gets created without error.

Barb

[Snowcrash_10]

OK, I worked them both out. If you are interested, the SQL Server procedure do
not require the GO at the bottom (no really) and the Oracle Procedure worked
when I removed all the tabs and line returns (one very long single line).

Barb

[meatn2veg]

Yeah, Go is not even a T-SQL command, let alone has a JDBC equivalent; its a
command that is recognized by the SQL Server tools only e.g. Query Analyzer,
SEM etc. For example you cant do multiple inserts with GO in between them from
a cfquery.