[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 946
  • Last Modified:

How to run multiple drop proc XXXX create proc XXXX in one script

Hi,

I have made some changes to a set of stored procedures and want to drop the old versions and create the new versions all in one go.

Currently I have to run each statement independently which means I could make a mistake or miss a statement.

What can I do to automate this?

I have tried putting semi-colons on the end of each drop and each create however this is complained at when I attempt to execute it.

I have also tried putting GO after each statement however this gets a similar error as the semi-colon.
drop proc getDocuments;
create proc getDocuments(parameters etc)
begin
**code in here
end;
 
drop proc getDocuments
GO
create proc getDocuments(parameters etc)
begin
**code in here
end
GO

Open in new window

0
Simonss
Asked:
Simonss
  • 4
  • 3
1 Solution
 
grant300Commented:
Which Sybase product and version and what tool are you using to execute the SQL?

Regards,
Bill
0
 
SimonssAuthor Commented:
I'm using sybase 15 and im using two tools to execute, AnySQL which uses the ASE15 ODBC Driver and Interactive SQL, which is a Sybase app.
0
 
SimonssAuthor Commented:
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
grant300Commented:
Several things.

First, forget the semi colons.  That is a Sybase ASA syntax and is not supported by Sybase ASE which is what you are using.

Second, using ISQL, your "go" statements should be on there own line, in the first column, and be in LOWER case.  Sybase is, by default case sensitive and ISQL is always so.

Third, it is best practice to test for the existence of an object before dropping it so that you can run the script under any conditions and not get errors.  Something along the lines of...

IF OBJECT_ID('dbo.get_documents') IS NOT NULL
    DROP PROCEDURE dbo.get_documents
go
CREATE PROCEDURE get_documents.....

Finally, your syntax for the CREATE PROCEDURE statements looks more like ASA than ASE and should probably be cleaned up.  Remove the parenthesis from the argument list and add and "AS" before the begin...

CREATE PROCEDURE get_documents @doc_name varchar(64), @author varchar(64)
AS
BEGIN
 ......
END
go

Regards,
Bill
0
 
SimonssAuthor Commented:
Hi Bill,

Thanks for your comments.  I didn't realise there was syntax differences between ASA and ASE.  As you can probably guess I'm pretty new to sybase.

So i dropped the semicolons and put the go statements after each of my stored procedures and also added the code to check if they exist before dropping them.  Thanks for that, very useful.

I actually have three clients that I can use to execute against the sybase database.

Microsoft iSQL/w which appears to be version 'SQL6.5'
Interactive SQL which is part of SQL Anywhere 11
AnySQL Maestro

Using Microsoft iSQL/w (which is an old, and it my eyes pretty poor client) the code worked perfectly.

// Using SQL Anywhere Interactive SQL I get the error
Could not execute statement.
The 'DROP PROCEDURE' command is not allowed within a multi-statement transaction in the 'DATABASE_NAME' database
SQLCODE=7718, ODBC 3 State="ZZZZZ"

//Using AnySQL Maestro
[Sybase][ODBC Driver][Adaptive Server Enterprise]Incorrect syntax near 'go'

I'm guessing this has something to do with the way each of these applications uses the ODBC Driver.  Can you shed any light on why this only works in the old archaic application rather than the two newer applications?

Regards,
Simon
0
 
grant300Commented:
I would toss Microsoft iSQL/w.  It is not a Sybase product and does not support all the features and functionality of ASE 15.  It is somewhat amazing that ASE actually recognized the old dbLib version of TDS and worked at all.

Toss Interactive SQL / SQL Anywhere 11 as that is a different product with different feature sets.  Again, you might as well try and use ODBC drivers for DB2 to connect to Oracle (O.K. that is an exageration but you get my point).  Bottom line, you have to use the ASE 15 ODBC drivers are you will have problems.

Install the full ASE 15 client for Windows.  You will get Sybase Central, JISQL (a Java Windowing ISQL tool), and command line ISQL.  These all use JDBC / ctlib to connect so you don't have to fool with trying to make the wrong ODBC drivers work.

After that, you can find a whole host of free-ware and/or low cost tools for doing database development.  My "best value" product is Aquafold Aqua Data Studio.  It is $395 but is worth every penny as you get an interactive debugger with it AND it will work just as well with SQL Anywhere and SQL Server as well as a whole list of other databases.

Another alternative is Sybase Workspace.  It is an Eclipse-based tool that has a debugger as well.  Right now, it is free so you might want to jump on that bandwagon.

In any event, you are going to want a full blown database IDE.  They do things like generate the DROP PROCEDURE statements automatically for you and have lots of other really great time-saving features.

Regards,
Bill
0
 
SimonssAuthor Commented:
Bill,

Thanks for your comments.  In the end my problem lay mainly with the tools I was using to connect to the DB.

It turns out we have site wide licenses for DBArtisan and Rapid SQL.  These appear to be pretty good and using 'go' I have been able to delete/update my stored procs using one execution.

Thanks for all your input, you pointed me in the right direction and clarified a few points for me that have helped me out.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now