Fully qualifying names when creating procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Fully qualifying names when creating procedures

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2007-11-20 : 06:49:15

Hi

I want to be able to create a procedure whilst connected to a different database. I can do this when creating tables by fully qualifying the name, but cannot use this method for procedure creations:

CREATE DATABASE Test1
GO
CREATE DATABASE	Test2
GO
USE Test1
GO
-- Method 1 - doesn't allow you to do this as CREATE has to be first in batch
IF OBJECT_ID('Test2.dbo.Test2Proc') IS NULL
CREATE PROCEDURE Test2.dbo.Test2Proc
AS
PRINT 'Hello'
GO

-- Method 2 - works but not using server name
IF OBJECT_ID('Test2.dbo.Test2Proc') IS NOT NULL
DROP PROCEDURE Test2.dbo.Test2Proc
GO
CREATE PROCEDURE Test2.dbo.Test2Proc
AS
PRINT 'Hello'
GO

USE [Master]
DROP DATABASE Test1
GO
DROP DATABASE Test2
GO

Is what I want to do possible? Thanks

Hearty head pats

nr
SQLTeam MVY

12543 Posts

Posted - 2007-11-20 : 08:41:02

exec svr.db.dbo.sp_executesql N'CREATE PROCEDURE Test2Proc
AS
PRINT ''Hello''
'

I usually create a file and execute it in the database using osql.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Bex
Aged Yak Warrior

580 Posts

Posted - 2007-11-20 : 09:30:06

That works a treat. Thankyou

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources