Detaching and Attaching a SQL Database

In this blog I will cover about detaching and attaching the SQL database.

When to go for detaching and attaching a Database?
If you can afford the database to be taken offline and want it to be reused then this is the right option rather than going for backup & restore.
When you want to move the database from one instance to another instance of SQL Server in the same machine this has to be used. Practically during the development time the same database data may be used for development as well as testing. The DB can be taken offline and the mdf & ldf sql files can be shared with testers so they attach the DB. This will help both developers and testers to proceed separately with same data.

Detaching the SQL Database

Takes the SQL DB offline.

SQL Query:

Syntax: EXEC sp_detach_db @DBNname=<<DB Name>>, @skipchecks = <<TRUE or FALSE for skipchecks>>, @keepfulltextindexfile = <<TRUE or FALSE for keepfulltextindexfile>>

Here @DBName is a mandatory parameter.

Usage: sp_detach_db <dbname>, [TRUE|FALSE], [TRUE|FALSE]

Example: EXEC sp_detach_db @DBName = 'DemoDB';

Practically, it can also be used like this.

db_id(<<DB Name>>) is used to check whether the DB exists or not. If DB doesn't exist and if its tried to be detached then the below error is thrown.

Database '<<DB name>>' does not exist. Make sure that the name is entered correctly.

Sometimes the DB may be used and at that time if its tried to be detached then the below error is thrown. Kill the process which is currently attached to our Database and then take the database offline.

Cannot detach the database '<<DB Name>>' because it is currently in use.



SQL


DECLARE @DatabaseName nvarchar(15) = N'DemoDB'

If db_id(@DatabaseName) is not null
    BEGIN
       
        DECLARE @SQL varchar(max)
           
         SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + 
         Convert(varchar, SPId) + ';'
         FROM MASTER..SysProcesses
         WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId   
       
         EXEC(@SQL)

         EXEC sp_detach_db @DatabaseName
       
         If db_id(@DatabaseName) is null
         BEGIN       
            PRINT @DatabaseName + ' is deattached successfully'
         END
    END
Else
    PRINT 'DB does not exist'


SQL PowerShell Script:

This uses the SQL server provider for PowerShell. If its not installed then .Net framework dlls can be consumed and then the query can be executed.

Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $ServerInstance -Database $DBName -verbose

The above code executes the sql query, most of the parameters passed to Inovke-sqlCmd here can be guessed just by their name but still let me brief about each and every parameter that are passed to Invoke-Sqlcmd command.

$SQLQuery is the sql query that has to be executed.

Invoke-Sqlcmd -InputFile "C:\SQLCmd.sql"

Here in our case the query is very small, if in case if the SQL Query is large then the Sql query can be stored as script file and it can be invoked in PowerShell.

$ServerInstance is the SQL server instance that has to be used, if its not passed then the default instance is used.

$DBName is the initial database that has to be used for executing the query, this can also be achieved by having USE <<DB Name>> as the first line in the SQL query.

-Verbose - This displays the SQL messages. In our SQL query we are having Print statement, to show the SQL messages -verbose is employed.



PowerShell


#
# Add the SQL Server Provider.
#

$ErrorActionPreference = "Stop"

#Check whether SQL PowerShell snapin is loaded or not
if ( (Get-PSSnapin -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue) -eq $null -and
     (Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue) -eq $null)
{
    #Load the SQL Powershell snapin
    write-host "Loading SQL snapin ..."
   
    $sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

    if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
    {
        throw "SQL Server Provider for Windows PowerShell is not installed."
    }
    else
    {
        $item = get-itemproperty -path $sqlpsreg
        $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
    }


    #
    # Set mandatory variables for the SQL Server provider
    #
    Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
    Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
    Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
    Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

    #
    # Load the snapins, type data, format data
    #
    Push-Location
    cd $sqlpsPath
    Add-PSSnapin SqlServerCmdletSnapin100
    Add-PSSnapin SqlServerProviderSnapin100
    Update-TypeData -PrependPath SQLProvider.Types.ps1xml
    update-FormatData -prependpath SQLProvider.Format.ps1xml
    Pop-Location
}

#SQL Query to be executed
$SQLQuery = "  DECLARE @DatabaseName nvarchar(15) = N'DEMODB'

If db_id(@DatabaseName) is not null
    BEGIN
       
        DECLARE @SQL varchar(max)
           
         SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
         FROM MASTER..SysProcesses
         WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId   
       
         EXEC(@SQL)

         EXEC sp_detach_db @DatabaseName
       
         If db_id(@DatabaseName) is null
         BEGIN       
            PRINT @DatabaseName + ' is deattached successfully'
         END
    END
Else
    Print 'DB does not exist'"

#SQL Instance
$ServerInstance = "SQLSERVER"                   

#SQL DB Name
$DBName = "Master"

#Execution
Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $ServerInstance -Database $DBName -verbose


Attaching the SQL Database

This brings the database online.

SQL Query:
sp_attach_db can be used for attaching the database but use of this stored procedure is not advised. 'Create Database' should be used for attaching the database.

If only mdf file is available, then use the below query.



SQL


  CREATE DATABASE SMS_POC
        ON
        ( NAME = SMS_POCDAT,   
            FILENAME = N'D:\Aravind\SQL\SMS_POC.mdf')
            FOR ATTACH ;


If both mdf & ldf files are available, then the below query can be used.



SQL


DECLARE @DatabaseName nvarchar(15) = N'DemoDB'

If db_id(@DatabaseName) is null
    BEGIN
        CREATE DATABASE DemoDB
        ON
        ( NAME = DEMODAT,   
            FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\DemoDB.mdf')
            LOG ON
        ( NAME = Demo_log,
            FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\DemoDBlog.ldf')
        FOR ATTACH ;
       
        If db_id(@DatabaseName) is not null
         BEGIN       
            PRINT @DatabaseName + ' is attached successfully'
         END
    END
ELSE
Print 'DB ' +@DatabaseName+ ' already exists'


PowerShell Script

The below script executes the SQL query to attach the database from PowerShell.



PowerShell


#
# Add the SQL Server Provider.
#

$ErrorActionPreference = "Stop"

#Check whether SQL PowerShell snapin is loaded or not
if ( (Get-PSSnapin -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue) -eq $null -and
     (Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue) -eq $null)
{
    #Load the SQL Powershell snapin
    write-host "Loading SQL snapin ..."
   
    $sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

    if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
    {
        throw "SQL Server Provider for Windows PowerShell is not installed."
    }
    else
    {
        $item = get-itemproperty -path $sqlpsreg
        $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
    }


    #
    # Set mandatory variables for the SQL Server provider
    #
    Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
    Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
    Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
    Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

    #
    # Load the snapins, type data, format data
    #
    Push-Location
    cd $sqlpsPath
    Add-PSSnapin SqlServerCmdletSnapin100
    Add-PSSnapin SqlServerProviderSnapin100
    Update-TypeData -PrependPath SQLProvider.Types.ps1xml
    update-FormatData -prependpath SQLProvider.Format.ps1xml
    Pop-Location
}

#SQL Query to be executed
$SQLQuery = "  DECLARE @DatabaseName nvarchar(15) = N'DemoDB'

If db_id(@DatabaseName) is null
    BEGIN
        CREATE DATABASE DemoDB
        ON
        ( NAME = DEMODAT,   
            FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\DemoDB.mdf')
            LOG ON
        ( NAME = Demo_log,
            FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\DemoDBlog.ldf')
        FOR ATTACH ;
       
        If db_id(@DatabaseName) is not null
         BEGIN       
            PRINT @DatabaseName + ' is attached successfully'
         END
    END
ELSE
Print 'DB ' +@DatabaseName+ ' already exists'"

#SQL Instance
$ServerInstance = "SQLSERVER"                   

#SQL DB Name
$DBName = "Master"

#Execution
Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $ServerInstance -Database $DBName -verbose


Aravind: Run time error - Office 2010 Add-Ins

Aravind: Run time error - Office 2010 Add-Ins: "One among the common problem faced by office 2010 Add-Ins developer is 'Not loaded. A runtime error occurred during loading of COM Add-in'. ..."

Run time error - Office 2010 Add-Ins

One among the common problem faced by office 2010 Add-Ins developer is "Not loaded. A runtime error occurred during loading of COM Add-in". Here I'm providing some steps for troubleshooting it.

First I will walk you through the steps if the Add-Ins is not visible/enabled  by default. At the end I will cover the trouble shooting.

Go to File --> Options --> Add-Ins. Check whether the Add-In is listed under Disabled Application Add-ins or Inactive Application Add-ins.

Excel Add-Ins

Disabled Add-Ins

We can enable the disabled Add-Ins. Under the Manage dropdown select Disabled Items. By default COM Add-Ins is selected, select Disabled Items from it and then click on Go button next to it. Select the disabled Add-In and click Enable button. Close the Office application and re-open it. Most probably this issue should have been fixed by now. You can verify it by going to File --> Options --> Add-Ins and now the Add-In should be listed under Active Application Add-Ins.

Enabling the Disabled Add-Ins

Inactive Application Add-ins

From Office 2010 UI

Go to File --> Options --> Add-Ins. Under the Manage dropdown select COM Add-Ins and this is only selected by default. Click on manage button next to it. It will open a dialog with the list of Add-ins. If the Add-In is not checked, check it and click on Ok.

Enabling the inactive Add-ins

Try closing and opening the application. This should fix the problem.
There are many ways to get it working. Lets go step by step

Registry key

Here I have taken office Excel as example. It applies to all Microsoft Office application.

Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\Excel\Addins\<<Add-in name>>
 Under this registry you can find two string values - Description and FriendlyName. These explain the name and the description of the Add-in. There is not of  something which is more important of our discussion. There is a DWORD registry value - LoadBehaviour. Check the value of it. It infers the status of our Add-In.

This link explains the value, their Add-In status, Add-in behaviour along with their description.

Value (in decimal)	Add-in status	Add-in load behavior	Description
0	Unloaded	Do not load automatically	The application never tries to load the add-in automatically. The user can try to manually load the add-in, or the add-in can be loaded programmatically. If the add-in is successfully loaded, the LoadBehavior value remains 0, but the status of the add-in in the COM Add-ins dialog box is updated to indicate that the add-in is loaded.
1	Loaded	Do not load automatically	The application never tries to load the add-in automatically. The user can try to manually load the add-in, or the add-in can be loaded programmatically. Although the COM Add-ins dialog box indicates that the add-in is loaded after the application starts, the add-in isn't actually loaded until it is loaded manually or programmatically. If the application successfully loads the add-in, the LoadBehavior value changes to 0, and remains at 0 after the application closes.
2	Unloaded	Load at startup	The application does not try to load the add-in automatically. The user can try to manually load the add-in, or the add-in can be loaded programmatically. If the application successfully loads the add-in, the LoadBehavior value changes to 3, and remains at 3 after the application closes.
3	Loaded	Load at startup	The application tries to load the add-in when the application starts. This is the default value when you build or publish an add-in in Visual Studio. If the application successfully loads the add-in, the LoadBehavior value remains 3. If an error occurs when loading the add-in, the LoadBehavior value changes to 2, and remains at 2 after the application closes.
8	Unloaded	Load on demand	The application does not try to load the add-in automatically. The user can try to manually load the add-in, or the add-in can be loaded programmatically. If the application successfully loads the add-in, the LoadBehavior value changes to 9.
9	Loaded	Load on demand	The add-in will be loaded only when the application requires it, such as when a user clicks a UI element that uses functionality in the add-in (for example, a custom button in the Ribbon). If the application successfully loads the add-in, the LoadBehavior value remains 9, but the status of the add-in in the COM Add-ins dialog box is updated to indicate that the add-in is currently loaded. If an error occurs when loading the add-in, the LoadBehavior value changes to 8.
16	Loaded	Load first time, then load on demand	Set this value if you want your add-in to be loaded on demand. The application loads the add-in when the user runs the application for the first time. The next time the user runs the application, the application loads any UI elements that are defined by the add-in, but the add-in is not loaded until the user clicks a UI element that is associated with the add-in. When the application successfully loads the add-in for the first time, the LoadBehavior value remains 16 while the add-in is loaded. After the application closes, the LoadBehavior value changes to 9.

Most probably the Add-in will be designed to load at the startup and its value will be 3. If any error occurs the value changes to 2. Try changing this value to 3 and reopen the office application. This can solve the problem. Once again if it is changing to 2 then go for the next step.

Trouble shooting

Set the environment variable VSTO_SUPPRESSDISPLAYALERTS to 0.

steps to create/modify environment variables.
Go to Control Panel\System and Security\System [This is in Windows 7]
Click on advanced system settings.
Click on Environment variables button.
Check VSTO_SUPPRESSDISPLAYALERTS environment variable is present. It can be user specific environment variable or at system level. If its present edit it and set the value to 0. Sometimes it will be 1. Most probably it will not be present. To create it click on New button and then create it with the value as 0.

Now Open the application it will throw the exception dialog box. This can help the developer to fix the bug.

To write the errors to a log file, set the VSTO_LOGALERTS environment variable to 1 (one). This will create a log file where the manifest file for the Add-in is present.
This link explains it in more.

.Net framework version error
If you are getting any .net framework error check that this KB article is published or not -  http://support.microsoft.com/kb/982305