[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2009-05-12
8
Medium Priority
?
935 Views
Last Modified: 2012-05-06
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
Comment
Question by:Simonss
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 19

Expert Comment

by:grant300
ID: 24367653
Which Sybase product and version and what tool are you using to execute the SQL?

Regards,
Bill
0
 

Author Comment

by:Simonss
ID: 24367833
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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 19

Expert Comment

by:grant300
ID: 24367990
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
 

Author Comment

by:Simonss
ID: 24372639
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
 
LVL 19

Accepted Solution

by:
grant300 earned 300 total points
ID: 24376329
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
 

Author Comment

by:Simonss
ID: 24624465
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Desired Skill Set for Microsoft Dynamics CRM Technical Resources – Part III
Ransomware, the malware that locks down its victim’s files until they pay up, has always been a frustrating issue to deal with. However, a recent mobile ransomware will make the issue a little more personal… by sharing the victim’s mobile browsing h…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question