Simple Task Problem 2

[yumbelie]

Hi, I'm pretty new to actually doing something useful in SQL, so can someone shed some light on my problem. I need to change from my current database to the master database, execute some sp_addextendedproc statements, then return to the previous database. Thats it. I don't know the name of the current database, so a simple:

USE master
sp_addextendedproc #1
sp_addextendedproc #n
USE currentdatabase

wouldn't work. I've tried declaring a varchar and stuffing the name of the current database in there prior to exectuing the statement, but the USE statement refuses to accept the (seemingly valid) database name contained within my variable when I try to return:

DECLARE @blah varchar(100)
SET @blah = DBNAME(DB_ID())
USE master
SELECT @blah AS "the current dbs name"
USE @blah

Any help greatly appericated.

Many thanks

Yum.

 

[dbomrrsm]

this will do it - bit long winded but works:

DECLARE @blah varchar(100),@SQL varchar (50)
SET @blah = (select distinct(TABLE_CATALOG) from information_schema.tables)
SET @SQL = 'use '+@blah
print @blah
USE master
SELECT * from sysobjects
EXEC (@SQL)
select distinct(TABLE_CATALOG) from information_schema.tables

[blue]DBomrrsm[/blue]

 

[jdgonzalez]

Can you provide some additional detail? What error message did you get?

 

[ProdAir]

just do
exec master..sp_addextendedproc #1

this statement won't change the current database

 

[yumbelie]

Hi, many thanks for your replies - I tried the simplest solution first (ProdAir) - I entered this as the syntax:

exec master.dbo.sp_addextendedproc 'xp_mystoredproc', 'mysqlxp.dll'

and it all worked perfectly. Many thanks to both of you for your solutions.

Yum.