Re: Query analyzer and changing database on the fly?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi

Your exec works fine, but its scope is only the duration of the exec, so
when the exec is finished, you're back to the db you started in. You can
include more commands in the exec
('use ' + @db_name + ' <do some work in other db>')

You can find out your current database with db_name:

SELECT db_name()


--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com


"Major" <lievonen@xxxxxxxxxxxxxxxxxx> wrote in message
news:%23Fskf0PdGHA.5048@xxxxxxxxxxxxxxxxxxxxxxx
How I can change database in the Query Analyzer on the fly if I have
database name in the @db_namd -variable?

I tried:
use master
exec ('use ' + @db_name) and it didn't cause any error messages, but it
still use master...

And how I can query "current database" in the QA?





.

Relevant Pages

  • Re: Cant get global config data and KB 900499 doesnt help
    ... The account you use to create the database MUST have the following rights on ... DECLARE @AdminVSAccount nvarchar ... DECLARE @ContentVSAccount nvarchar ... EXEC sp_grantlogin @ContentVSAccount; ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: Permission in database
    ... You can create database roles and assign object permissions to these ... EXEC sp_addrole 'Administrators' ... GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO Administrators ...
    (microsoft.public.sqlserver.security)
  • Re: stored procedure
    ... Seems you can't have a variable for the database name in CREATE DATABASE. ... the whole CRE¤ATE DATABASE command in a variable and use EXECto execute the statement. ... But why do you want to create a database from inside a stored procedure? ... >> is done inside EXEC so it is in its own scope. ...
    (microsoft.public.sqlserver.server)
  • Re: Database Ownership
    ... can I declare database ownership on more than one ... believe the performance difference is noticeable for most applications. ... then grant select permissions on the views. ... EXEC sp_changedbowner 'MyLogin' ...
    (microsoft.public.sqlserver.security)
  • Re: Permissions!
    ... be aware that 'dbo' is a special user that has full permissions. ... Database permissions are not checked for the 'dbo' user so it serves no ... You will need individual logins/users so that SQL Server can identify users ... EXEC sp_addrole 'Role1' ...
    (microsoft.public.sqlserver.security)