error trying to execute stored procedure after changing it

Question

error trying to execute stored procedure after changing it

qaz-959475

Valued Member

Points: 50
More actions
December 5, 2008 at 7:41 am

#216164

I got a small stored procedure to work. I changed it and clicked the checkmark on the menu. In the results area, it said "Command(s) completed successfully." When I clicked on the "! Execute", I got an error in the results area saying "msg 2714, level 16, state 3, procedure try1.sql, line 20 there is already an object named 'try1' in the database."
When I run try1.sql from a Cold Fusion program, it looks like the first one is running without the change.
What am I doing wrong?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

Sounds like you have a syntax error within the code that allows it to compile but not execute.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

qaz-959475 Valued Member Points: 50 More actions · Answer 2

There is a blue check mark on the menu of Server Manager Express that says "parse" when the cursor is over it. I thought that when I clicked on that check mark and got the message "command completed successfully", that the syntax was ok.

How do I check for syntax?

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

SQL Server uses late binding. So if I wrote this query:

SELECT * FROM

Dinglefarb

And checked the syntax, it'll pass. But if I execute it, while resolving all the objects involved in the query, it comes across one that doesn't exist, you'll get an error.

This is done so that you can create objects, such as temporary tables, within your code and still have the code compile.

It's likely that you've just got a syntax error somewhere in your proc.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Dakotah SSC-Addicted Points: 420 More actions · Answer 4

Do you have "DROP TABLE #BASE" at the bottom of your Stored Procedure?

If so highlight the DROP TABLE #???? and Execute in SQL Server

If I am working on a Stpred Procedure I have to drop it sometimes.

SQL - Reporting Work in Process...