Change the properties of a SQL Server login account

Enabling a disabled login
The following example enables the login Emma.

ALTER LOGIN Emma ENABLE

Changing the password of a login
The following example changes the password of login Emma to 123EEDWQxz829.

ALTER LOGIN Emma WITH PASSWORD = '123EEDWQxz829'


Changing the name of a login
The following example changes the name of login Emma to DavidB.

ALTER LOGIN Emma WITH NAME = DavidB

Changing the Name of a Database User

The following piece of T-SQL changes the name of a database user named james to jesse.


Use AdventureWorks;
Alter USER Lara WITH NAME = jesse
GO

Display Identity Columns in all Tables

This piece of T-SQL displays all the identity columns in all the tables in a Database.

Use Adventureworks
Go
Select o.name, i.name As 'ColumnName'
From sys.identity_columns i, sys.objects o
Where i.object_id = o.object_id
Go

Dynamically Passing Parameters at run time to a Package

Imagine developing an SSIS package and using variables to dynamically pass parameters to the package at runtime. Your package works perfectly on SSIS development environment.
Now you want to schedule this package as a job and wondering the hassles of opening and modifying the package on SSIS development environment each time you want the package to run with different parameters.

Well with SSIS, you can dynamically pass a parameter to a package at run time. The example below will demonstrate how to schedule a package to run as a job by dynamically assigning a parameter at run time.

Connect to your SQL Server.
Click SQL Server Agent to expand it.
Right click jobs, Select New job.
On the General Page, Enter job name.
Click Step, on the Steps page, Click New button.
On the General page of select a step page window, Enter the step name.
On the Type heading, Select SQL Server Integration Services Package from the drop down box.
ON Package source, select where the package is stored. For this demonstration I will select SQL Server.
ON Serve heading: select the server.
Choose your authentication mode.
On Package heading, select the package.



Now to dynamically pass parameters at run time to this package,
Click the Set values tab.
On the Property Path column, type this: \Package.Variables[Users::myvariable].value
On the Value column, type the value of the parameter you want to pass. In this demonstration, I typed "November" for the value column.



Click Ok and Ok again.

Note:
myvariable is the name of the varibale that i declared during the development of the package on BIDS(Business Intelligence Development Studio).
Also remember that variables within SSIS packages are case sensitive.

Lock Monitor

Lock Monitor thread detects deadlock. It does that by inspecting processes ecery 5 seconds to determine if there are any deadlock cycles. When it finds one, it automatically chooses one thread as the deadlock victim. It then rolls back the
victims transaction, cancel its query and returns error 1205.

The Lock Monitor chooses the deadlock victim by choosing the least expensive transaction to roll back.
You can overide this by using SET DEADLOCK_PRIORITY to low

Error Number 18456: Login Failed For User

When an attempt to connect is rejected because of an authentication failure that involves a bad password or user name, a message similar to the following is returned to the client: "Login failed for user ''. (Microsoft SQL Server, Error: 18456)".
The following message might also be returned:

Msg 18456, Level 14, State 1, Server , Line 1"
"Login failed for user ''."

To troubleshoot this failure, look at the error state that maps to an authentication failure condition. Compare the error state to the table list below to determine the reason for the login failure.

Copying Tables

To create a copy of a table, you can use SELECT INTO to extract all the rows from an existing table into a new table. The new table must not exist already.

The following example will copy all the rows from Address table to a new table called TempAddress

Use AdventureWorks
Go
Select * into TempAddress from Person.Address

This statement does not copy the indexes, constraints,triggers, Keys etc. to the new table

Determining System and Server Information

The system and server information is available on the General page of the server properties dialog box. You can also use the extended stored procedure xp_msver.

exec xp_msver

Command-Line Utilities

Command Utilities provides database administrators different way to access the databse engine. SQL Server 2005 provides new command line utilities such as:
DTA
TableDiff
sac: which can be used to import or export settings that relates to surface area configuration.
sqlcmd: Lets you execute T-SQL statements and scripts from the command prompt or from query editor window is SSMS. sqlcmd provides a mechanism for utilizing variables within sqlcmd input files or scripts.

To demostrate the use of scripting variables, lets use this example.

A new input file named backupAdventureWorks.sql is created with the following sql statements inside it.

BACKUP DATABASE $(DatabasetoBackup)
TO DISK ='c:\$(DatabasetoBackup).bak'

Scripting varaibles are refrenced using the $() designators. The -v option is used to assign a value to a variable at the command prompt as shown in the following example, which backups Adventureworks database.

Now execute the following in a command prompt.

sqlcmd -S pavilion -E -i"c:\backupAdventureWorks.sql" -v DatabasetoBackup = AdventureWorks

Scripting variables can also be assigned within the script using :SETVAR command. The input file from the previous backup would be modified as follows to assign the DatabasetoBackup variable within the script:


:SETVAR DatabasetoBackup AdventureWorks
BACKUP DATABASE $(DatabasetoBackup)
TO DISK ='c:\$(DatabasetoBackup).bak'

Then execute the following in a command prompt.

sqlcmd -S pavilion -E -i"c:\backupAdventureWorks.sql"

There are also other command-line utilities that are also new to SQL Server 2005 that are used to lauch the graphical tools that come with SQL Server.

• profiler90: Lauches the SQL Server Profiler application.
• sqlwb: This utility launches the SQL Server Management Studion(SSMS).
• dtswizard: This util luanches import/export wizard that allows you to import and export data between SQL Server data sources and other data sources.

DTA Command-Line Utility

dta is the command line version of the graphical Database Engine TunningAdvisory. They both provide performance recommendations based on the workload provided to them.
The workload to tune would either be a workload file or a workload table. The workload filemust be either a profiler trace file or an SQL script or SQL Server trace file(.log).
We will use the T-SQL below which is stored in a workload file named c:\dtascript.sql.


USE AdventureWorks
Go
Select * from HumanResources.Employee
where gender = 'M'

To demonstrate the use of dta, lets look at this example.

C:\>dta -S pavilion -E -D AdventurewORKS -if c:\dtascript.sql -s Mysession -of c:\dtaOutputscript.sql -F

Microsoft (R) SQL Server Microsoft SQL Server Database Engine Tuning Advisor command line utilityVersion 9.00.3042.00Copyright (c)
Microsoft Corporation. All rights reserved.

Tuning session successfully created. Session ID is 6.
Total time used: 00:00:01Workload consumed: 100%, Estimated improvement: 47%

Tuning process finished.Successfully generated recommendations script: c:\dtaOutputscript.sql.

The output of the dtaoutputscript.sql will contain the following performance recommendation:

use [AdventureWorks]
go
CREATE NONCLUSTERED INDEX [_dta_index_Employee_14_869578136__K9_1_2_3_4_5_6_7_8_10_11_12_13_14_15_16] ON [HumanResources].[Employee]
( [Gender] ASC)
INCLUDE ( [EmployeeID],
[NationalIDNumber],
[ContactID],[LoginID],
[ManagerID],[Title],[BirthDate],
[MaritalStatus],
[HireDate],
[SalariedFlag],
[VacationHours],
[SickLeaveHours],
[CurrentFlag],
[rowguid],
[ModifiedDate]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

The dta ouput recommends that a new non-clustered index be created on the Gender column in the Employee table.

TableDiff Command-Line Utility

Tablediff is an SQLCMD command utility that allows you to compare the data contained within two different tables. This was developed for replication when data in two tables should be the same.This tool can determine if the content of tables involve in replication are the same, and if they are different, it can identify what data in the tables are different.

Let me demostrate this with this simple example:

USE AdventureWorks
GO
Create table table1( id int identity(1,1) NOT NULL, name varchar(50) )GO
Create table table2( id int identity(1,1) NOT NULL, name varchar(50) )GO
insert into table1 values('Jesse')
insert into table1 values('Emmanuela')
GO
insert into table2 values('Jesse')
insert into table2 values('Emma')
GO
--Now run the tablediff util on the command line from this folder:C:\program files\microsoft sql server\90\com\tablediff.exe

C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "pavilion" -sourcedatabase "AdventureWorks" -sourceschema "dbo" -sourcetable "table1" -destinationserver "pavilion\sql2005" -destinationdatabase "AdventureWorks" -destinationschema "dbo" -destinationtable "table2" -f c:\Tablediff_output.txt

-- The execution of the utility with the above parameters results in the following output to the command prompt window:

User-specified agent parameter values:
-sourceserver pavilion\sql2005
-sourcedatabase AdventureWorks
-sourceschema dbo-sourcetable table1
-destinationserver pavilion\sql2005
-destinationdatabase AdventureWorks
-destinationschema dbo
-destinationtable table2
-f c:\Tablediff_output.txt

Table [AdventureWorks].[dbo].[table1] on pavilion\sql2005 and Table [AdventureWorks].[dbo].[table2] on pavilion\sql2005 have 1 differences.Fix SQL written to c:\Tablediff_output.txt.sql.

Err id Col
Mismatch 2 name
The requested operation took 1.46875 seconds.

The output displays the summary and shows the comparison results. It found one difference between table1 and table2. The -f parameter caused tablediff utility to output a SQL file that can be used to fix the difference in the destination file.

The output of the SQL file is:

-- Host: pavilion\sql2005
-- Database: [AdventureWorks]
-- Table: [dbo].[table2]
SET IDENTITY_INSERT [dbo].[table2] ON
UPDATE [dbo].[table2]
SET [name]='Emmanuela' WHERE [id] = 2
SET IDENTITY_INSERT [dbo].[table2] OFF

Encrypting Data with a PassPhrase

The simplest form of encryption is encrypting data by a PassPhrase using the EncryptByPhrase function.

EncryptByPhrase function lets you encrypt your data using a passphrase. In order to decrypt the same data, you use the DecryptByPassPhrase function. This lets you decrypt the data using the same passphrase you used in encrypting the data. THe example below demonstrate how to use a Passphrase in encrypting and decrypting data.


Declare @text varchar(100)
Declare @key varchar(100)
Declare @decrypted varchar(100)
Declare @encrypted varbinary(102) --requires two additional bytes

Set @text ='Hello World'
Set @key = 'Pa55word'

-- Encrypt the text using the @key Pasphrase(Pa55word)
Set @encrypted = EncryptByPassPhrase(@key, @text)
Select @text as 'Text to Encrypt', @encrypted as 'Ecnrypted text'

-- Deccrypt the text using the @key Pasphrase(Pa55word)
Set @decrypted = DecryptByPassPhrase(@key,@encrypted)
Select @encrypted as 'Encrypted text', @decrypted as 'Decrypted text'

Output:

-- The text has been encrypted using the PassPhrase set at @key variable
Text to Encrypt Encrypted Text
----------------------------------------------------------------------------------------
Hello World 0x010000007E380B8599411C0F7636F3D03145C84A34D260F0796182D9A9813959B6CF3BDF

-- The text has been decrypted using the PassPhrase set at @key variableEncrypted
Encrypted text Decrypted Text
0x010000007E380B8599411C0F7636F3D03145C84A34D260F0796182D9A9813959B6CF3BDF Hello World

Note that the PassPhrase are case sensitive. Secondly, you can only decrypt the text using the same passphrasethat were used in ecrypting the data.

Percentage of Logspace Used

You can use this SQL below to check the percentage of logspace currently used by your database.



dbcc sqlperf(logspace)

Apart from setting up alert to tell you when your database is running out of logspace, the above query does the same as well. However, you actually have to manually run the query to view the database logspace usage.

Current Active Transactions

To find how many active transactions are currently running, use the SQL below.



select * from sys.dm_tran_session_transactions

Concatenate Single Column into Row

This query concatenates a single column into one row

CREATE TABLE Numbers
(
number VARCHAR(20)
)
GO
INSERT Numbers SELECT 'one'
INSERT Numbers SELECT 'two'
INSERT Numbers SELECT 'three'
INSERT Numbers SELECT 'four'
GO

declare @list varchar(4000)
set @list = ''
select @list = @list + ',' + number from numbers
select @list = substring( @list, 2, len( @list ) -1) -- To Remove first comma
select @list

OUTPUT
-----------------------------------
one,two,three,four

Authentication Mode

To find out if you are connected to SQL Server using NTLM authentication or Kerberos.

select * from sys.dm_exec_connections where session_id = @@spid

select net_transport, auth_scheme from sys.dm_exec_connections where session_id = @@spid

Database Collation

The following code returns the collation settings of AdventureWorks database.

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation

To display your own database collation, substitute the AdventureWorks Database with your own database.

DBCC Shrinkfile

The code below shrinks the transaction log file to target size specified.

Use AdventureWorks
GO
DBCC Shrinkfile(filename, target_size)

filename is the logical name of the file to shrink
traget_size is the size of the transaction log file in megabytes to be shrunk. For example

Use AdventureWorks
GO
DBCC Shrinkfile(AdventureWorks_Log, 10)

The above code shrinks down the transaction log file named AdventureWorks_Log to 10MB

Network Load Balancing

Network Load Balancing(NLB) distributes incoming I.P(Network) traffic across cluster of servers. NLB provides high availability by automatically detecting the failure on a server and repartioning incoming traffic among the remaining servers.

NLB is mostly used with web services. You cannot run both NLB and Microsoft Clustering Services(MSCS) on the server, however you can run them on the same solution.

You might use both on a database deriven web servers.

Saving SSIS Package to MSDB Database

SSIS does not support multi-instances. It does not allow saving Packages to MSDB database if there are no default SQL Server 2005 installation.

To enable saving packages on the MSDB subfolder in SSIS, You will have to change the MsDtsSrvr.ini.xml file located in C:\Program Files\Microsoft SQL Server\90\DTS\Binn directory. Sample file:



change the line where the red arrow points to the dot(.)to:



Do this ONLY if there are no default instance of SQL SERVER 2005 installed on your Server.

SERVERPROPERTY Function

SERVERPROPERTY Function returns property information about the server instance.

Level of the version of the instance of SQL Server.
SELECT SERVERPROPERTY('ProductLevel')

Returns one of the following:
'RTM' = Original release version
'SPn' = Service pack version
'Bn', = Beta version


Installed product edition of the instance of SQL Server
SELECT SERVERPROPERTY('Edition')

Name of the instance the user is connected
SELECT SERVERPROPERTY('InstanceName')

Server instance is configured in a failover cluster
SELECT SERVERPROPERTY('IsClustered')

Consult BOL for other SERVERPROPERTY propertyname

Single Quotes

When specifying a value with a single quote, you need to double it.

SELECT 'Hello''s'

Returns: Hello's

Enabling Service Broker

To configure database mail, you need to enable service broker on MSDB databse. Make sure that Sql Server Agent is stoped before running the following query below.

ALTER DATABASE MSDB
SET ENABLE_BROKER

To check that service broker has been enabled, run this piece of sql below.

SELECT IS_broker_Enabled
FROM sys.databases
WHERE name = 'MSDB'

If 1= Broker enabled.
If 0 = Broker Not enabled.

ERROR 18452: The User is not associated with a Trusted SQL Server Connection

This can occur due to one of the following reasons:

1: If SQL Server has been configured to operate in “Windows Authentication mode” and doesn’t allow the use of SQL Logins.
Resolution: Change the Authentication mode from “Windows Authentication mode” to “Mixed Mode”(SQL SERVER and “Windows Authentication mode”)

2: You are trying to connect using SQL Server Authentication but the login used does no t exist on the SQL Server.

3: The login could have been using windows Authentication but the login is an unrecognized windows principal. An unrecognized windows principal means that the login cannot be verified by windows. This could be because the windows login is from a domain not trusted by the server. In this case do the following to determine if there is a trust between the client and the server.
PING: The Server on both IP and Name from the Client.
PING: The Client on both IP and Name from the Server.

Find all SQL Servers on the Network

To find all the available SQL Servers on the network that are broadcasting their existence.

Exec Master..XP_CMDSHELL 'SQLCMD -L'

First you have to enable XP_CMDSHELL from the Surface Area configuration before running this command.

Error when configuring database mirroring

This error occurs when configuring database mirroring on a stand alone PC. One or more of the server network addresses lacks a fully qualified domain name (FQDN).Specify the FQDN for each server, and click Start Mirroring again.The syntax for a fully-qualified TCP address is:TCP://.[.]:
--------------------------------------------------------------------------------------
To trouble shoot this error, do the following below.
Right click on the "My Computer" then properties.
Click on the "computer name" tab, then click on the change button next to the "To Rename this computer or join a domain.
click change" then you see the "Computer name changes" window, click the "More" button on it. You will see the "DNS suffix and NetBIOS ComputerName" window. Here, type "local" to the "Primary DNS Suffix of this Computer" textbox. then press OK 3 times and restart computer.

After put this "computername.local" to your computer name for the mirroring computer server network address.

If you are in domain environment, just use really the FQDN.This is applicable just in case of stand alone machine.

Page Life Expectancy

Page Life Expectancy value is the number of seconds a page will stay in the buffer pool without references. So a buffer that has 300 seconds page life expectancy will keep any given page in the buffer pool for 5 minutes before the buffer pool flushes the page to disk.
To determine page life expectsncy value using query.

SELECT [cntr_value]
FROM master..sysperfinfo
WHERE object_name = 'SQLSERVER:BUFFER Manager'
AND counter_name ='Page Life Expectancy'

Copy-Only Backup

Copy-only backup are new feature in SQL 2005. It allows a backup of any type to be taken without affecting any other backups. Copy-only backup doesn’t affect the restore chain. It cannot be used as a restored base in the event of failure. They are useful in a situation where you want to get a copy of the database for testing purposes or when you want to refresh the staging, development and testing databases.

Copy-only backups are not supported via SQL Server Management Studio(SSMS). It must be performed using T-SQL Backup command.

Backup Database MYDATABASE
TO DISK = 'Drive\Folder\filename.bak'
WITH COPY-ONLY

Error: Cannot resolve collation conflict for equal to operator

If two tables coluns has different collation, when you try to do a join on both querries, it will generate an error "Cannot resolve collation conflict for equal to operator".To resolve the collation conflict add following keywords around the equal to operator.

SELECT Column01, Column02
FROM Table01
INNER JOIN Table02
ON Table01.Colum01 COLLATE DATABASE DEFAULT = Table02.Column01 COLLATE DATABASE DEFAULT

Oldest Active Transaction

The following code displays the oldest active transaction on a Database.

USE Mydb
Go
DBCC OPENTRAN
Go