use clause + dynamcy sql - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

use clause + dynamcy sql

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-04 : 00:27:09

could you help me fix it?

declare @sql varchar(4000), @db sysname
set @db = 'master'
set @sql = 'USE ' + @db
exec (@sql)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-04 : 00:32:57

There's nothing wrong with it. It takes effect inside another session though as does all dynamic SQL. So you'll have to provide more information on what you are trying to do in order for us to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-04 : 00:38:42

i want to change the database. thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-04 : 00:39:45

It did, just in another session. There is no way to change the database context in the current session when you are using dynamic SQL. All dynamic SQL executes in another session, which is why you aren't seeing it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-04 : 00:45:58

How can i see it? thanks and how can i code it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-04 : 00:48:53

You can't see it. There is no way to code it.

If you provide additional information as to what you are trying to do, we may be able to come up with an alternative solution.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-04 : 00:52:44

Just want to be able to change the database when I input the db name.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-04 : 01:02:19

You can't. I hear an echo.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-04 : 01:06:02

no wonder i couldnt google it.