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).
Often I have to search for stored procedures in SQL Server to find pieces of text. The motivation for this is to find procedures which use a table or to look for code which the developer has done silly things like hardcoded directories (Boris….). Luckly you can query sys.procedures to generally find the information that you need. Below is an example of query that searches for stored procedures for the word ‘c:\*’.
|
1 2 3 |
SELECT Name FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%c:\*%' |
REFERENCES:
To create a method that can take unlimited parameters, utilize the params keyword in the method declaration. This allows you to specify a method parameter that take an argument where the number of arguments is variable. You are not allowed to have any other method parameters declared after a params method parameter.
Example:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
static void Main(string[] args) { AddNumbers(12, 14, 15); } public static void AddNumbers(params int[] numbers) { int result = 0; for (int i = 0; i < numbers.Length; i++) result+= numbers[i]; Console.WriteLine(result); } |
PSGMUMGGBJAR
Connect to the instance of SQL Server, and then run the following query (this will work for all versions of SQL Server):
|
1 |
Select @@version |
The result form the query will look like the following.
Microsoft SQL Server 2008 (SP1) – 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
For version of SQL Server 2005 and newer you also can use the SERVERPROPERTY function to retrieve additional information. Executing the following will provide you with the SQL Server product version (i.e. 10.0.2531.0, product level (RTM) and edition information (i.e. Standard, Enterprise):
|
1 |
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') |
You can lookup the product version using the following table:
| Release | Product Version |
| SQL Server 2012 Service Pack 1 | 11.00.3000.00 |
| SQL Server 2012 RTM | 11.00.2100.60 |
| SQL Server 2008 R2 Service Pack 1 | 10.50.2500.0 |
| SQL Server 2008 R2 RTM | 10.50.1600.1 |
| SQL Server 2008 Service Pack 3 | 10.00.5500.00 |
| SQL Server 2008 Service Pack 2 | 10.00.4000.00 |
| SQL Server 2008 Service Pack 1 | 10.00.2531.00 |
| SQL Server 2008 RTM | 10.00.1600.22 |
| SQL Server 2005 Service Pack 4 | 9.00.5000.00 |
| SQL Server 2005 Service Pack 3 | 9.00.4035 |
| SQL Server 2005 Service Pack 2 | 9.00.3042 |
| SQL Server 2005 Service Pack 1 | 9.00.2047 |
| SQL Server 2005 RTM | 9.00.1399 |
| SQL Server 2000 Service Pack 4 | 8.00.2039 |
| SQL Server 2000 Service Pack 3 | 8.00.760 |
| SQL Server 2000 Service Pack 2 | 8.00.534 |
| SQL Server 2000 Service Pack 1 | 8.00.384 |
| SQL Server 2000 RTM | 8.00.194 |
Where to find information about the latest SQL Server builds use the following link: http://support.microsoft.com/default.aspx?scid=kb;EN-US;957826
The following query can be run (substitute the ‘TableName’ in the query with the table name you want column information on) to get the column names for any table in T-SQL.
|
1 2 3 |
select column_name from information_schema.columns where table_name = 'TableName' order by ordinal_position |
|
1 |
To get information on all the columns for all tables in a database use the following query:
|
1 2 |
SELECT Table_Schema, Table_Name, Column_Name, Data_Type FROM information_schema.columns |
SQL Server also provides a stored procedure (‘sp_help’) which is able to display information on any object listed in sysobjects. To use this to get information on a table execute the following:
|
1 |
EXEC sp_help 'tablename' |
The iPad is a very easy to use device. What is surprising is that sometimes things that you wouldn’t normally do everyday are possible but are hidden away.
Thankfully taking a screenshot on the iPad is very easy to do. All that is required is to press the Home button and Power button as the same time.
When done correctly you will hear a camera click sound and the screen will blank out for a second.
The screenshot is stored in the Photo’s app.
In the Photo app you can email one or more screen shots, by clicking the menu icon. Clicking the menu icon from a single picture will email the one image.
If you click the menu button from the main page, you can select multiple pictures and send select the menu icon to email them.
When you install a SQL Server instance, any feature that is not necessary for the core engine to run has been disabled by default. xp_cmdshell is a significant security risk because it allows a compromised SQL Server to elevate the attack to the operating system itself, and from there to the entire network.
You can enable or disable features within your instance by executing the system stored procedure sp_configure with the xp_cmdshell option.
When the xp_cmdshell feature is disabled you will see the following message when it is executed:
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.
You can verify that the xp_cmdshell feature featured is disabled by executing the following query:
|
1 2 3 |
SELECT value_in_use FROM sys.configurations WHERE name = 'xp_cmdshell' |
If the results of the query is 0 then the feature is disabled; if 1 then it is enabled.
In order to enable xp_cmdshell execute the following:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE GO |
