SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Anybody know about logins?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Charles Egan
Starting Member

21 Posts

Posted - 06/20/2013 :  11:50:24  Show Profile  Reply with Quote
I know I can create a login as follows:

CREATE LOGIN test_login
WITH PASSWORD = 'some_password',
DEFAULT_DATABASE = AdventureWorksDW, -- or whatever database
DEFAULT_LANGUAGE = us_english,
CHECK_POLICY = OFF;

I also know that, if I'm in SQL Server Management Studio and I enable Query | SQLCMD Mode, instead of

CREATE LOGIN test_login

I can use

:setvar LoginName "test_login"
CREATE LOGIN $(LoginName)

in the above SQL code.

• What is the advantage of using the latter method?

• Is there a way I can use the latter method from outside of SQL Server Management Studio (like when I'm using SQL commands in some app)?

• In SQL Server Management Studio, I don't see any indication when SQLCMD Mode is enabled or it's not, so if I forget whether I’ve enabled it, I just have to play with it to find out for myself. Am I missing something? How can I tell?

• I know that in the above example, AdventureWorksDW can be enclosed in square brackets as follows:

DEFAULT_DATABASE = [AdventureWorksDW],

and the command will still work. But what effect, if any, does this have on the CREATE LOGIN command?

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 06/20/2013 :  14:53:30  Show Profile  Reply with Quote
1. SQLCMD is usually used for running queries from outside of SSMS. The SQLCMD mode in SSMS allows you to test such queries from within the IDE. I don't know of any other advantage to using SQLCMD mode.

2. You can run SQLCMD from a command window. There are a few examples here: http://msdn.microsoft.com/en-us/library/ms180944.aspx

3. You are right, I haven't seen any indicator or alert that tells you which mode you are in.

4. The square brackets are escape characters. So if you had some funny database name, for example something that started with a number, or something with a special character in it etc. use of escape characters is mandatory. Otherwise it is optional. But, it does not have any effect on the create login command.
Go to Top of Page

Charles Egan
Starting Member

21 Posts

Posted - 06/20/2013 :  17:45:49  Show Profile  Reply with Quote
Hi, James K -

Thanks for the informative answer.

However, from the link you provided it appears that there is perhaps no way to use SQLCMD Mode in a call to SQL made from within an app that I might write myself (should I ever wish to do so).

Is that your read on the situation as well?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 06/20/2013 :  17:51:40  Show Profile  Reply with Quote
The only way to call SQLCMD from your application (such as a C#/.Net app) is to invoke system command - i.e., you will be forking a process. However, if controlling/querying SQL from your application is your objective, then there are better ways - everything from SQL SMO to plain ADO.Net or Entity Framework. In that sense, SQLCMD is designed more as a commandline tool.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000