same sp name in two databases on same server

Question

same sp name in two databases on same server

oldgoat

SSCrazy

Points: 2344
More actions
June 26, 2006 at 5:58 am

#91562

I'm building a new copy of an existing database, using scripts so as to automate it during development of the app that it supports, and have come across an issue of stored procedure naming.
We already host various 'live' and 'test' dbs on same servers, that use identical object names, so why when I try and 'create proc' does sql return
'There is already an object named 'xxx' in the database.'
I'm previously dropping each proc so as to keep versioning correct.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

Every object has a four-part name: Server.Database.Owner.ObjectName

SQL Server will fill in the blanks with the current context if you leave any of them off. If I'm logged in to \\MyServer, in the Northwind database as dbo, then CREATE PROC MyProc will create MyServer.Northwind.dbo.MyProc. The same procedure/table/function/index/etc. name in multiple databases is not a problem, because it actually can't happen - the database name is part of the object name.

Without going into all the fun details, you should *always* specify an owner when creating an object. If you want dbo.MyProc, then use CREATE PROC dbo.MyProc

Otherwise, if I'm logged in as Eddie, then CREATE PROC MyProc creates Eddie.MyProc. After that, if I drop dbo.MyProc, then try to create MyProc (which will be Eddie.MyProc), then I will get an 'object exists' error, because Eddie.MyProc already exists.

-Eddie

Eddie Wuerch
MCM: SQL

oldgoat SSCrazy Points: 2344 More actions · Answer 2

that's the bit that doesn't quite work for me. I always use dbo for the owner, since the user login is always a generic one (web-site front end).

Doing the create is fine, its just that doing the if exists test on sysobjects can't handle even 'db.dbo.proc'

''DROP PROCEDURE' does not allow specifying the database name as a prefix to the object name.''

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 3

Be sure to fire a USE statement first, then the database context is set:

USE

MyDB

GO

SET

ANSI_NULLS, QUOTED_IDENTIFIER ON

GO

IF EXISTS(SELECT ...

-Eddie

Eddie Wuerch
MCM: SQL

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

Or...

IF OBJECT_ID('dbname.dbo.objectname') IS NOT NULL

DROP objecttype dbname.dbo.objectname

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 5

Jeff: CREATE and DROP prohibit use of the database name. They must be executed in the context of the target database.

-Eddie

Eddie Wuerch
MCM: SQL

oldgoat SSCrazy Points: 2344 More actions · Answer 6

Yeah - I realised that USE would be the way to deal with it

thanks for the advice guys!

vadba SSChampion Points: 11133 More actions · Answer 7

EDIT: Sorry, you were talking about stored procedures. You're correct, SP's cannot be dropped fromanother database context.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Yep... not enough coffee there... you can do it with a table but not a proc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)