Solved

USE Statement with variable

Posted on 2007-11-22
2
729 Views
Last Modified: 2008-02-01
Hi,

i am trying to script the creation of a user and add it to a database like this:

declare @DbAttachName varchar(50);
SET @DbAttachName = 'test_script';
##USE @DbAttachName;##
CREATE USER [nvt] FOR LOGIN [nvt] WITH DEFAULT_SCHEMA=[dbo];
EXEC sp_addrolemember @rolename='db_owner',@membername='nvt';

the login was created succesfully, no problem there.
on the line with the ## there is an error. apparently the USE statement does not support variables.
when i try like this:
SET @SQL='USE ' + @DbAttachName;
EXEC (@SQL);
I get no error, but the database does not change...
i suppose it only changes the database in the "EXEC" statement, and not in the script itself.

How can i solve this?
The problem is, that the database is also attached in the same script (not when testing this)
so i can not fix the USE name.
0
Comment
Question by:joachimcarrein
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20333501
> apparently the USE statement does not support variables.
correct.

>when i try like this:
>SET @SQL='USE ' + @DbAttachName;
>EXEC (@SQL);
>I get no error, but the database does not change...
 actually, it is changed, but only in the scope of that dynamic sql.
so:
>i suppose it only changes the database in the "EXEC" statement, and not in the script itself.
correct



only way:

declare @DbAttachName varchar(50);
SET @DbAttachName = 'test_script'; 
declare @sql varchar(4000)
set @sql ='USE ' + @DbAttachName + '
CREATE USER [nvt] FOR LOGIN [nvt] WITH DEFAULT_SCHEMA=[dbo]
EXEC sp_addrolemember @rolename='db_owner',@membername='nvt'
'
EXEC(@sql)

Open in new window

0
 
LVL 4

Author Comment

by:joachimcarrein
ID: 20333604
that did the trick indeed. as i suspected the use is only in the scope. but i didn't think of executing those statements in the same scope :)
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now