Solved

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

Posted on 2009-05-12
8
891 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
  • 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
 

Author Comment

by:Simonss
ID: 24367839
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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 75 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
improving database skills 2 619
group by and where clauses 3 552
SQL Query Syntax 12 86
SQL, Updating Statistics take too long for 60GB database 5 129
Read about the ways of improving workplace communication.
Employees depend heavily on their PCs, and new threats like ransomware make it even more critical to protect their important data.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

776 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