A blog based on Microsoft Technologies
Start a command prompt using the “Run as administrator” option. If you do not use this option you may be a message that ‘Access is denied.’
To start the default instance of SQL Server type the following in command line:
|
1 |
net start mssqlserver |
To stop the default instance of SQL Server type the following in the command line:
|
1 |
net stop mssqlserver |
I spent quite a few few years developing PL/SQL on Oracle databases and had grown accustomed to its syntax. One of the things which I didn’t enjoy was that the ‘FROM’ clause in a SELECT statement was required (it is not in TSQL). This causes a bit of a problem when all you want to do is do retrieve 1 row with a result. The workaround for this problem was to introduce a dummy table which just contains one row which you could use in your queries. This table was called DUAL and has a single VARCHAR2(1) column called DUMMY that has a value of ‘X’.
To demonstrate the use of DUAL, the following displays the use of a SELECT statment that calculates 1+1 in TSQL and PL/SQL:
TSQL:
|
1 |
SELECT 1+1 |
PL/SQL:
|
1 |
SELECT 1+1 FROM DUAL; |
The name DUAL seems a bit odd for a table which just has one record in it. The reason for this is that it originally had two records and was intended to be used to return two records when being used. There is a bit of history which explains this on wikipedia article.
What if you wanted to port existing code from PL/SQL to TSQL and your code already utilizes DUAL? I would personally rewrite it as you generally have to write rewrite code anyways but the other option is to actually recreate the DUAL table in your database. The following code will assist creating this:
|
1 2 3 4 5 6 |
CREATE TABLE DUAL (DUMMY VARCHAR(1)); GO INSERT INTO DUAL (DUMMY) VALUES ('X'); GO |
If your a TSQL programmer working on a PLSQL you will need to remember to utilize the DUAL table,
After creating user accounts and restoring an Microsoft SQL Server database onto another, it was discovered that database logins didn’t work. This problem is due to the mapping of database users to SQL Server logins to a database. There is a SID that associates the database user to the login id. The database user ids are stored in the user database that is moved, the sql server login is stored in the master database which is not migrated. Just so you understand the relationship between these two databases and tables issue the following statements (substitute <database_name> with a user database name):
|
1 2 3 |
select name, principal_id, sid from master.sys.sql_logins where type_desc = 'SQL_LOGIN' select name, principal_id, sid from <database_name>.sys.server_principals where type_desc = 'SQL_LOGIN' |
If there is a mismatch on the SID or if there is a missing sql server login, the user will not be able to utilize the database. Such database users are referred to as “orphaned users”.
SQL Server provides an stored procedure “sp_change_users_login” (http://technet.microsoft.com/en-us/library/ms174378.aspx) which will assist with problem by linking a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name.
To detect orphaned users, execute the following Transact-SQL statements:
|
1 2 3 4 |
USE <database_name>; GO EXEC sp_change_users_login @Action='Report'; GO |
To fix this problem you can do one of the following:
|
1 2 3 4 |
USE <database_name>; GO EXEC sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>'; GO |
|
1 2 3 4 |
USE <database_name>; GO EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password' GO |
Note: If a login with the same name does not exist, one will be created. You must specify user and password if the login does not already exist. If the user already exists, you must specify the user parameter (the password parameter will be ignored in this case).
