create trigger or stored procedure in external DB?

[raindogs]

I know you're not really supposed to do this, but I have a stored procedure that creates another database (using the tips in this thread:

http://www.thescripts.com/forum/thread81377.html).

It all works great, except that I need to also add some triggers and/or stored procedures in the new DB I'm creating. SQL Server does not seem too happy about me using a stored procedure to create these elements in an external database. Is there a way out of this or have I painted myself into a corner?

Thanks,
Alex

 

[mrdenny]

You would need to use Dynamic SQL with a USE command within the dynamic SQL.

create procedure something as
declare @cmd varchar(8000)
CREATE DATABASE somethingelse
set @cmd = 'USE somethingelse
create procedure usp_someprocedure as
select * from sysobjects
go'
exec (@cmd)
go

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com