Attach database failed for Server. (Microsoft.SqlServer.Smo) An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) Unable to open the physical file . Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

Connect to the server using an administrator account with access to the file system for the database files and through Windows Authentication rather than using sa through SQL Server Authentication.

Taking a SQL server database offline fails or killing a database connection

In most cases, when the operation for taking a database offline; this is attributed to connections established to this database. The apparent solution is to close the locking connections, the below is how to force them closed.


Use [master]
GO

sp_WHO 
GO

spid | ecid | status   | loginame | hostname | blk      | dbname | cmd 
58    | 0     | sleeping| user1        | user1PC   | NULL| MyDB   |LOG WRITER      


KILL 58
GO

Command(s) completed successfully.





P.S. 
- sp_who2 provides even more details about the current connections.
- No need to mention that you must have the appropriate rights on the master database to perform this commands.

Understanding Microsoft SQL Server permissions

Below is a very good article about the permissions check order. Also explains chaining; when an object accesses another object.

http://technet.microsoft.com/en-us/library/dd283095(v=sql.100).aspx
Although the article was for SQL Server 2008, most concepts are still valid for 2012 version.

Custom SQL exceptions (error messages) to end users

Protected Sub FormView_Paper_ItemInserted(sender As Object, e As FormViewInsertedEventArgs) Handles FormView_Paper.ItemInserted
       
    If Not IsNothing(e.Exception) Then
            Dim DoDisplayCustomError As Boolean = False

            Dim SQLError As System.Data.SqlClient.SqlException
            If e.Exception.Source = ".Net SqlClient Data Provider" Then
                SQLError = DirectCast(e.Exception, SqlException)
                If SQLError.ErrorCode = -2146232060 Then
                    DoDisplayCustomError = True
                    ' Develop ur message display function!
                    Me.CurrentMasterPage.DisplayError("Missing Mandatory fields", "Fill in all mandatory fields.")
                End If
            End If

            If Not DoDisplayCustomError Then
                Me.CurrentMasterPage.DisplayError(e.Exception)
            End If
            e.ExceptionHandled = True
            e.KeepInInsertMode = True
        Else
            Response.Redirect("OtherPage.aspx")
        End If
    End Sub

Generating a sequence of numbers using SQL Select statement

SELECT (Tens.N + Units.N) NN
FROM
(
SELECT 0 N
UNION SELECT 1 N
UNION SELECT 2 N
UNION SELECT 3 N
UNION SELECT 4 N
UNION SELECT 5 N
UNION SELECT 6 N
UNION SELECT 7 N
UNION SELECT 8 N
UNION SELECT 9 N
)Units
CROSS JOIN
(
SELECT 0 N
UNION SELECT 10 N
--UNION SELECT 20 N
--UNION SELECT 30 N
--UNION SELECT 40 N
--UNION SELECT 50 N
--UNION SELECT 60 N
--UNION SELECT 70 N
--UNION SELECT 80 N
--UNION SELECT 90 N
)Tens

-----
NN
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

Thanks for:
http://sqlmag.com/t-sql/generating-sequences-t-sql

To maintain MS SQL column identity on delete

- Disable the identity for the specific column
- Delete
- Enable the identity for that same column

Thanks Dinos for the tip!

Print Date and Time Using TSQL

PRINT LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 121), '-','_'),':','_'),' ','_'), 16)

16 characters on the LEFT only to omit extra time details.

Look-up tables design

1- No to a single look-up table:
   a - Scalability.
   b - Data Maintainability (Error prone)

2- Integrity on deletion:
   a - Deletion stored procedure with the logic to restrict deletion for consumed records.
   (The reason that foreign keys are not enough since typically they reside within a single DB; however, references are usually consumed from other DBs.) [My recommendation]
   b- Don't delete: Just mark as deleted or add a status per record to be defined by the business.
    

Backup DB using SQL statement

-- Name of the DB to backup, and destination directory
DECLARE @DBName VARCHAR(50)           
DECLARE @Destination VARCHAR(2000)

SELECT @DBName = 'DB',
  @Destination = '\\TestMachine\'
+ REPLACE(@@SERVERNAME, '\','_')
+ '_' + REPLACE(@DBName, ' ','_')
+ '_' + LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 121), '-',''),':',''),' ','_'), 13) + '.bak'

--Sample Destination: \\TestMachine\DBServer_DB_20130211_1630.bak

PRINT 'Backup ' + QUOTENAME(@@SERVERNAME) + '.' + QUOTENAME(@DBName) + ' to file "' + ISNULL(@Destination, 'NULL') + '"'

BACKUP DATABASE @DBName TO DISK = @Destination WITH COPY_ONLY, COMPRESSION, STATS=5

N.B. Compression is not supported with standard editions.

Comprehensive TSQL options can be found at:
http://msdn.microsoft.com/en-us/library/ms186865.aspx

Remove ASP.NET Membership Provider objects from SQL Server Database

%WINDIR%\Microsoft.NET\Framework\v4.0.30319\aspnet_regsql.exe

Tip:
This also exists for .NET framework 2.0 in the equivalent directory

Upload image using ASP.NET and SQL Server

1) Create the below columns:

CREATE TABLE [dbo].[User](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FileName] [nvarchar](max) NULL,
[ContentType] [nvarchar](255) NULL,
[FileContent] [varbinary](max) NULL)

2) Add upload control to your web page:
<asp:FileUpload ID="FileUpload_Image" runat="server"  />

3) Add the following handler class to your project:

FileName: Handler_Image.ashx

<%@ WebHandler Language="VB" Class="Handler_Image" %>

Imports System
Imports System.Web
Imports System.Data

Public Class Handler_Image : Implements IHttpHandler
    
    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
       
        
        'Byte[] content = GetImageFromDB(id)
        Dim Content() As Byte
        Dim ContentType As String
        
        Dim oID As Object = context.Request.QueryString("ID")
        Dim oType As Object = context.Request.QueryString("Type")
                
        If Not IsNothing(oID) And Not IsNothing(oType) Then
            Dim ID As Integer = Convert.ToInt32(oID.ToString())
            Dim Type As Integer = Convert.ToInt32(oType.ToString())            
          
                    If Not IsNothing(ID) And IsNumeric(ID) Then
                    
                        Dim Dataset_File As DataSet = DBHelper.RunSQLQuery("SELECT FileName, ISNULL(ContentType,'') ContentType ,FileContent FROM [User] WHERE ID=" & ID)
                        
                        If Not IsDBNull(Dataset_File.Tables(0).Rows(0).Item("FileContent")) Then
                            ContentType = Dataset_File.Tables(0).Rows(0).Item("ContentType")
                            Content = Dataset_File.Tables(0).Rows(0).Item("FileContent")
                        End If
                                   
                    End If
     
        End If

        If Not IsNothing(Content) Then
            context.Response.BinaryWrite(Content)
        Else
            context.Response.ContentType = "text/plain"
            context.Response.Write("Hello World")
        End If
        
    End Sub

    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property

End Class



4) On Save Event:


If FileUpload_Image.HasFile Then
Dim contentType As String = FileUpload_Image.PostedFile.ContentType

Dim FileName As String = System.IO.Path.GetFileName(

FileUpload_Image.PostedFile.FileName)

Dim byteArray As Byte() = 

FileUpload_Image

.FileBytes

e.Command.Parameters("@FileName").Value = FileName
e.Command.Parameters("@ContentType").Value = contentType
e.Command.Parameters("@FileContent").Value = byteArray
End If


N.B. for pjpeg:
http://afsawaf.blogspot.com/2012/12/imagepjpeg-vs-imagejpg-mime-types.html


--22nd May, 2013

This is to answer the question of how to display this uploaded image:

<asp:Image ID="Image_Signature" runat="server" Height="100px" Width="200px" ImageUrl='<%# Eval("ID", "Handler_Image.ashx?ID={0}&Type=10&Unique=") + Now().ToString()%>' />

Use SQL image or varbinary(max)

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server.

http://msdn.microsoft.com/en-us/library/ms187993.aspx

TSQL Insert multiple values using a single insert statement

INSERT INTO TargetTable (Column1, Column2)
SELECT 'Value1', 'Value1'
UNION
SELECT 'Value2', 'Value2'
UNION
SELECT Column1, Column2
FROM SourceTable

Identifying Active directory group members using TSQL on MS SQL Server

exec master.dbo.xp_logininfo 'DomainName\GroupName','members'

Prerequisites: Requires membership in the sysadmin role for the database server.
Requires EXECUTE permission for this stored procedure within the master database.

P.S.
By experiments, I  noticed that I could enumerate only members for groups to which I belong.

TSQL: Create Date

CAST(
CAST(2012 AS varchar) + '-' +
CAST(9 AS varchar) + '-' +
CAST(17 AS varchar) AS DATETIME)

TSQL Not Equal

!=

TSQL Unique Constraint

Samples:

ALTER TABLE [PermissionsRequest] ADD CONSTRAINT UNIQUE_EMail UNIQUE (EMail)

ALTER TABLE [Schedule] ADD CONSTRAINT UNIQUE_Registration UNIQUE ( RoundNo, ClassNo)

Calculate Age using TSQL

This is a modified version from the original post by Michael Valentine Jones Yak DBA Kernel 


CREATE function [dbo].[fn_Age](@START_DATE datetime, @END_DATE datetime)
returns  varchar(9) 
as
-- Original version @http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729
-- Calculates age in years, months and days from @START_DATE through @END_DATE and
-- Returns the age in format YYY MM DD.

-- Years is the number of full years between @START_DATE and @END_DATE.
-- Months is the number of full months since the last full year anniversary.
-- Days is the number of days since the last full month anniversary.
BEGIN
--([dbo].[fn_Age](isnull([DOB],getdate()),getdate()))

declare @AGE varchar(9)

declare @AGE_YEARS int
declare @AGE_MONTHS int
declare @AGE_DAYS int

-- Return null if @START_DATE > @END_DATE
IF @START_DATE > @END_DATE BEGIN RETURN @AGE END

SELECT @AGE_YEARS = AGE_YEARS,
@AGE_MONTHS = AGE_MONTHS,
@AGE_DAYS = datediff(dd, dateadd(mm, AGE_MONTHS, dateadd(yy, AGE_YEARS, START_DATE)),END_DATE)
FROM (SELECT AGE_MONTHS = CASE WHEN AnniversaryThisMonth <= END_DATE
THEN datediff(mm,dateadd(yy,AGE_YEARS,START_DATE),END_DATE)
ELSE datediff(mm,dateadd(yy,AGE_YEARS,START_DATE),END_DATE)-1
END, *
FROM (SELECT AGE_YEARS = CASE WHEN AnniversaryThisYear <= END_DATE
THEN datediff(yy,START_DATE,END_DATE)
ELSE datediff(yy,START_DATE,END_DATE)-1
END, *
FROM (SELECT AnniversaryThisYear = dateadd(yy,datediff(yy,START_DATE,END_DATE),START_DATE),
AnniversaryThisMonth = dateadd(mm,datediff(mm,START_DATE,END_DATE),START_DATE), *
FROM (Select START_DATE = dateadd(dd,datediff(dd, 0, @START_DATE),0), END_DATE = dateadd(dd, datediff(dd, 0, @END_DATE),0)) Temp4
) Temp3
) Temp2
) Temp1

SELECT @AGE = right('000'+convert(varchar(4),@AGE_YEARS),3) + ' ' +
right('00'+convert(varchar(4),@AGE_MONTHS),2) + ' ' +
right('00'+convert(varchar(4),@AGE_DAYS),2)

RETURN @AGE
END

SQL Server Computed column drawback

On creating a computed column using a function, you will no longer be able to alter this function, unless you remove it from the computed column expression.

Otherwise you will get the error message:
Cannot ALTER 'dbo.fn_test' because it is being referenced by object 'Tester'