changing database that is created in runtime..

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 2000 Forums

SQL Server Development (2000)

changing database that is created in runtime..

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-05-20 : 07:25:50

sowmya writes "sowmya writes "My Question is at runtime i am creating a database i.e., i don't know the database name. After creating the database , i want to change to this database..

I tried this query..

Declare @LogiName Varchar(50),
@Database nchar(128),
@exec1 char(200)
BEGIN
SET @Database = 'PUBS'
SET @Exec1 = 'use ' + @Database
EXECUTE (@Exec1)
END

Even the query runs perfectly but my desired output (change in the database is not happening.... )why is this so ??

and can i do the same in any other way ????? Kindly Clarify this..

With Regards,
Sowmya"

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-20 : 07:42:08

You need to execute whatever you want in the context of the database
exec ('exec ' + @Database + '.dbo.sp_executesql N'select * from sysobjects')

Or use the qualified name to access objects
exec ('select * from ' + @Database + '.dbo.sysobjects')

Or don't do this at is maybe the best answer.

==========================================
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.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-05-20 : 11:57:20

At runtime you are creating a database?!?!?!?!?! What in the world for? And you don't know what the name is? I'm SOOOO confused! Are you also later deleting it? Or are you just creating mass quantities of new databases? Are you simply trying to come up with a way to temporarily store information, in which case a Temporary Table or Table Variable would be better?

---------------------------
EmeraldCityDomains.com

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-20 : 16:29:43

Webhosting companies do this a lot, so calm down.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-05-20 : 17:04:11

OK, Derrick, I can buy that. Obviously I was coming from a very different perspective thinking of an on-going application, not a setup process. Guess I should have gone to lunch instead of reacting like that.

---------------------------
EmeraldCityDomains.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources