SQL Server – Take a database offline and detach it, then re-attach it and bring it online

The following two snippets will allow you to take a database offline then detach it and attach a database and bring it back online.  It should be noted that while this would work Ok in a single user or controlled instance it could be problematic in production environments where multiple connections maybe active to the database.  In this example I will use a database called “test_database”.

Take offline and detach:

-- Take the database offline, then detach it
ALTER DATABASE test_database SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
sp_detach_db test_database, 'true', 'false'
GO

Attach and bring online:

-- Attach the database then bring it back online
sp_attach_db N'test_database', N'C:\Data\test_database.mdf', N'C:\Data\test_database_log.ldf'
GO
ALTER DATABASE test_database SET ONLINE
GO

The syntax above may slightly differ between the mechanism you use to execute it.  The above works for me in SQL Server Management Studio (SSMS).  However, if I wanted to execute it from a .Net program using a SqlConnection/SqlCommand I would probably need to be semi-colons after each individual statement and then remove the “GO” lines.

Also, you may run into an instance where one or both of these commands fail with an error like “Exclusive access could not be obtained because the database is in use".  To remedy this you can put the database in single user mode before the restore and then change it after.  The following scripts should help with that.

Set the database to single user mode:

ALTER DATABASE test_database SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Set the database back to multi user mode:

ALTER DATABASE test_database SET MULTI_USER

Parameterized Queries and varbinary(max) problems (and fixes)

These are the exceptions that you may see occur:

“Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query."

“The parameterized query expects the parameter ‘@file_data’, which was not supplied.”

The above exceptions may occur when you try to insert a null value into a varbinary field.  There are a few things to keep in mind here.  First, use DbNull.Value as the null value and not “null” or “Nothing”.  Second, if you are using the AddWithValue method to add the parameter in with the value know that it has issues with varbinary fields.  For whatever reason, you have to specifically setup the varbinary parameter with a length of -1 and then add it in so it sends to the server.  Here is an example:

                cmd.Parameters.Add("@file_data", SqlDbType.VarBinary, -1)
                cmd.Parameters("@file_data").Value = DBNull.Value

 

VB.Net parameterized query example and why you should care

I’m going to provide a simple example and explain the benefits (and need).  Let’s assume that you have table called “user_table” with one field that is “username”.  This table resides in a database called “WebServices”.  The below would be a way to execute a parameterized query that searches the table for a specific user, returns a data reader and then iterates over it message boxing the username (in a WinForms app).

Reasons why this is important to you:

  1. Using a parameterized query helps protect you from SQL injection exploits.  If you allow a user to enter a parameter you string concat onto the query, they will be able to enter single quotes which at best will cause your query to fail and at worse will allow a sophisticated user to then append additional SQL onto your query and execute it.  If the user you’re executing the query with has read/write privileges (or worse, dbo) then that user will be able to delete data, truncate tables, drop tables, change permissions, etc.etc..  The parameter will escape the data provided by the user to protect against this.  This is the single most important reason to always use parameterized queries in my opinion.
  2. Using parameters can allow you to specify the exact field type.  Example, you can tell the parameter that it is a varchar with a length of 32.  That way, if you try to put more than 32 characters into the field, the parameter knows it cannot be that long.  You should probably be doing checks on this anyway this will stop an exception from occurring (I believe the default behavior will truncate the data, which may or may not be desirable for you).
  3. Using a parameterized query will allow you to call “Prepare” on the command if you specifically set field lengths on your parameters.  On a query that runs 1 time, this may not have a huge benefit.  However, on a query that is run over and over, in a loop or throughout the program it can have a huge impact.  Using “Prepare” will allow the SQL Server to cache the execution plan/compile it and reuse it which will give you a performance benefit.
        ' 1.) Create and open the connection, the using will handle the "Dispose" call.
        Using conn As New System.Data.SqlClient.SqlConnection("server=localhost\SQLExpress;database=WebServices;trusted_connection=yes;")
            conn.Open()

            ' 2.)  Create a SQL command that's assigned to the connection, again, the using will handle the "Dispose" call when your done.
            Using cmd As System.Data.SqlClient.SqlCommand = conn.CreateCommand

                ' The SQL statement we want to execute with the parameter.  @ is the parameter prefix for SQL Server/Express/Compact
                cmd.CommandText = "select * from user_table where username=@username"

                ' We can add this two ways, one where we give the command as much detail about @username as possible 
                ' and one where we just provide a value and the command interprets what it should be


                cmd.Parameters.AddWithValue("@username", "blake")

                ' Method two, specifically create a parameter, doing this is more code but will give you more flexibility especially
                ' when defining parameters that require precision on numeric values, etc.
                '    Dim param As New System.Data.SqlClient.SqlParameter("@username", SqlDbType.NVarChar, 100)
                '    param.Value = "blake"
                '    cmd.Parameters.Add(param)

                Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader

                While dr.Read
                    MessageBox.Show(dr("username").ToString)
                End While

                ' Close the data reader, we always want to do this when we're done.
                dr.Close()

            End Using

            conn.Close()
        End Using

 

The prefix of a parameter with SQL Server is @.  This may change depending on what provider you use (for instance, MySql uses a ?).  Here is another blog post I made which includes the various prefix’s that some ADO.NET providers use:  http://www.blakepell.com/Blog/?p=261

SQL Server: How to perform and Update Statement with an Inner Join

The scenario is, you need to perform an update statement but you need to also inner join on to be able to add where criteria in to get the database records that you want.  The command would look something like this:

UPDATE t1 SET UPDATED_FIELD = 0
FROM YOUR_FIRST_TABLE t1
    INNER JOIN YOUR_SECOND_TABLE t2 On t1.ID = t2.ID
WHERE t1.FIELD1 = 'VALUE' AND t2.DATE = '2/27/2012'

The above joins two tables and then provides an additional filter in the where clause to get down to the records that you’re wanting to update.  I’ve really only used something like this on SQL Server so I’m not sure how easily or if it ports to other flavors of SQL well. 

SQL Server – Table variables and temp tables.

I’ve heard many times that table variables are in memory on the SQL Server and thus very fast.  However, I’ve also read on a few blogs (one of which I read frequently and has a lot of good content: http://blog.sqlauthority.com/) that both of these options are stored in the tempdb.  I finally found some documentation on Microsoft’s site in a support article that indicates that the tempdb will be used if there is too much data to store in memory, but if memory suffices it will stay there.  Specifically, here is what was written (it’s for SQL Server 2000 but I assume not much has changed in how these operate):

Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?

A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

The key phrase here is If memory is available, both table variables and temporary tables are created and processed while in memory (data cahce)

The article lists advantages / disadvantages to each method because there are a few more.  In general, table variables are what I use in most cases but the article lists where each is advantageous:

SQL Server, Oracle, MySql, OleDb, Parameterized Queries and ADO.NET

One thing that irks me is the prefix qualifier that is used in various ADO.NET provider implementations.  I’m not sure why they all need to be different but alas, they are. :P  This means, depending on what provider you use, you could have two different drivers for the same database with two different qualifiers.  Having the framework generic sql for you will help with this, but I like writing my own.

Anyway, for reference, when using parameterized queries, here’s the qualifiers:

  • SqlServer “@” (Using SqlClient)
  • SqlSeverCompact “@”
  • MySql “?” (Using the MySql Connector)
  • Oracle “:” (Using Oracle’s ADO Client, not the deprecated version MS used to support)
  • OleDb:  “?” (I’ve read this is supposed to be supported on most OleDb providers, but I know for a fact that you can use @ with MSACCESS/OleDb, obviously you’ll need to research more obscure implementations).

Unspecified error [ 5,sqlcese40.dll ] at System.Data.SqlServerCe.SqlCeConnection.ProcessResults(Int32 hr)

Uhm.  The SQL Compact errors are pretty awful.  I assume they make them awful so you want to use the full blown version of  SQL Server.  Of course, I would if that were an option available to me, which it’s not.  Anyway…

I obtained the above error when trying to open a SqlCeConnection.  My problem was the user the web-site was running under didn’t have permissions to access the database (even though it was in the App_Data directory).  The user DID have access to read/write to Access databases in that same directory, so I can only assume that it was trying to invoke some action that needed additional permissions (even though MS swears you shouldn’t need additional permissions).

Anyway… my solution was to use Impersonation before opening the connection and then end impersonation after closing the connection.  This essentially executes that code segment with the privileges of the user your impersonating.  I prefer to use it via code so only the minimum amount of code necessary runs with the additional security (as opposed to the entire page).

I have an open source project over at CodePlex to use Impersonation in code.  It’s a VB project but as with any .Net language you can use the compiled DLL in your C#, F# or *insert language here*.  I believe the library on CodePlex targets 3.5 but the code should be able to be used in the framework versions 1.1-4.0.

http://impersonation.codeplex.com/

DBNameValueCollection Class

I wanted to share a simple class that inherits from NameValueCollection and automatically handles persisting that NameValueCollection into a database table.  You provide a connection, the name of the table and the key/value field names.  When it’s declared, all of the values from the database are read into the collection.  When you make changes, you can call the “CommittChanges” method to write them back out to the database.

The collection will only be written back out if changes have occurred.  There maybe a more efficient way to do this, but for this simple example the table data is deleted and re-loaded when changes are committed.  A database transaction is used so that all changes must be successful otherwise the database table is kept in it’s current state.

Code Snippet
  1. Namespace Database
  2.  
  3.     ''' <summary>
  4.     ''' A class to handle persisting a NameValueCollection to a database.  It should be noted that this class does not yet handle
  5.     ''' concurrent users, it was designed for easy storage of collections in a single user application.
  6.     ''' </summary>
  7.     ''' <remarks></remarks>
  8.     Public Class DbNameValueCollection
  9.         Inherits System.Collections.Specialized.NameValueCollection
  10.  
  11.         '*********************************************************************************************************************
  12.         '
  13.         '             Class:  DbNameValueCollection
  14.         '      Initial Date:  10/07/2011
  15.         '      Last Updated:  10/07/2011
  16.         '     Programmer(s):  Blake Pell
  17.         '
  18.         '*********************************************************************************************************************
  19.  
  20. #Region "Contructors"
  21.  
  22.         ''' <summary>
  23.         ''' Constructor
  24.         ''' </summary>
  25.         ''' <param name="conn">The database connection to use.  The connection must be initialized but does not need to be open yet.  This class will not cleanup or close the connection when it's done with it.</param>
  26.         ''' <param name="tableName">The name of the table the NameValueCollection should be stored in.</param>
  27.         ''' <param name="fieldKeyName">The database field name for the key field.</param>
  28.         ''' <param name="fieldValueName">The database field name for the value field.</param>
  29.         ''' <remarks></remarks>
  30.         Public Sub New(ByVal conn As IDbConnection, ByVal tableName As String, ByVal fieldKeyName As String, ByVal fieldValueName As String)
  31.             Me.TableName = tableName
  32.             Me.FieldKeyName = fieldKeyName
  33.             Me.FieldValueName = fieldValueName
  34.  
  35.             If conn Is Nothing Then
  36.                 Throw New NullReferenceException("The database connection cannot be null.")
  37.             End If
  38.  
  39.             _dbConnection = conn
  40.  
  41.             If conn.State <> ConnectionState.Open Then
  42.                 conn.Open()
  43.             End If
  44.  
  45.             Dim command As IDbCommand = conn.CreateCommand
  46.             command.CommandText = String.Format("SELECT {0}, {1} FROM {2}", fieldKeyName, fieldValueName, tableName)
  47.  
  48.             Dim dr As IDataReader = command.ExecuteReader
  49.  
  50.             While dr.Read
  51.                 Me.Add(dr(fieldKeyName), dr(fieldValueName))
  52.             End While
  53.  
  54.             dr.Close() : dr = Nothing
  55.             command.Dispose() : command = Nothing
  56.         End Sub
  57.  
  58. #End Region
  59.  
  60. #Region "Methods"
  61.  
  62.         ''' <summary>
  63.         ''' Clears and refreshes the contents of the collection from the database.
  64.         ''' </summary>
  65.         ''' <remarks></remarks>
  66.         Public Sub RefreshCollectionFromDatabase()
  67.             If _dbConnection Is Nothing Then
  68.                 Throw New NullReferenceException("The database connection cannot be null.")
  69.             End If
  70.  
  71.             If _dbConnection.State <> ConnectionState.Open Then
  72.                 _dbConnection.Open()
  73.             End If
  74.  
  75.             Dim command As IDbCommand = _dbConnection.CreateCommand
  76.             command.CommandText = String.Format("SELECT {0}, {1} FROM {2}", FieldKeyName, FieldValueName, TableName)
  77.  
  78.             Dim dr As IDataReader = command.ExecuteReader
  79.  
  80.             Me.Clear()
  81.  
  82.             While dr.Read
  83.                 Me.Add(dr(FieldKeyName), dr(FieldValueName))
  84.             End While
  85.  
  86.             dr.Close() : dr = Nothing
  87.             command.Dispose() : command = Nothing
  88.  
  89.             _changed = False
  90.         End Sub
  91.  
  92.         ''' <summary>
  93.         ''' Commits the changes to the database only if any have taken place.
  94.         ''' </summary>
  95.         ''' <remarks>
  96.         ''' This method will use a database transaction, if any records fail to be inserted then the entire transaction
  97.         ''' will be rolled back and no changes will occured on the database side.  After the rollback occurs the exception
  98.         ''' will be thrown.
  99.         ''' </remarks>
  100.         Public Sub CommitChanges()
  101.             If _changed = False Then
  102.                 Exit Sub
  103.             End If
  104.  
  105.             If _dbConnection Is Nothing Then
  106.                 Throw New NullReferenceException("The database connection cannot be null.")
  107.             End If
  108.  
  109.             If _dbConnection.State <> ConnectionState.Open Then
  110.                 _dbConnection.Open()
  111.             End If
  112.  
  113.             Dim transaction As IDbTransaction = _dbConnection.BeginTransaction
  114.             Dim command As IDbCommand = _dbConnection.CreateCommand
  115.  
  116.             Try
  117.                 command.Transaction = transaction
  118.                 command.CommandText = String.Format("DELETE from {0}", Me.TableName)
  119.                 command.ExecuteNonQuery()
  120.  
  121.                 command.CommandText = String.Format("INSERT INTO {0} ({1}, {2}) VALUES (@keyName, @valueName)", Me.TableName, Me.FieldKeyName, Me.FieldValueName)
  122.  
  123.                 Dim pKeyName As System.Data.IDbDataParameter = command.CreateParameter
  124.                 pKeyName.ParameterName = "@keyName"
  125.                 pKeyName.DbType = DbType.String
  126.                 command.Parameters.Add(pKeyName)
  127.  
  128.                 Dim pKeyValue As System.Data.IDbDataParameter = command.CreateParameter
  129.                 pKeyValue.ParameterName = "@valueName"
  130.                 pKeyValue.DbType = DbType.String
  131.                 command.Parameters.Add(pKeyValue)
  132.  
  133.                 For Each key In Me.Keys
  134.                     DirectCast(command.Parameters("@keyName"), IDbDataParameter).Value = key
  135.                     DirectCast(command.Parameters("@valueName"), IDbDataParameter).Value = Me(key)
  136.                     command.ExecuteNonQuery()
  137.                 Next
  138.  
  139.                 transaction.Commit()
  140.             Catch ex As Exception
  141.                 transaction.Rollback()
  142.                 Throw
  143.             Finally
  144.                 command.Dispose() : command = Nothing
  145.                 transaction.Dispose()
  146.             End Try
  147.  
  148.             _changed = False
  149.         End Sub
  150.  
  151.         ''' <summary>
  152.         ''' Removes an item from the NameValueCollection
  153.         ''' </summary>
  154.         ''' <param name="name"></param>
  155.         ''' <remarks></remarks>
  156.         Public Overrides Sub Remove(ByVal name As String)
  157.             _changed = True
  158.             MyBase.Remove(name)
  159.         End Sub
  160.  
  161.         ''' <summary>
  162.         ''' Adds an item to the NameValueCollection.
  163.         ''' </summary>
  164.         ''' <param name="name"></param>
  165.         ''' <param name="value"></param>
  166.         ''' <remarks></remarks>
  167.         Public Overrides Sub Add(ByVal name As String, ByVal value As String)
  168.             _changed = True
  169.             MyBase.Add(name, value)
  170.         End Sub
  171.  
  172.         ''' <summary>
  173.         ''' Clears the NameValueCollection
  174.         ''' </summary>
  175.         ''' <remarks></remarks>
  176.         Public Overrides Sub Clear()
  177.             _changed = True
  178.             MyBase.Clear()
  179.         End Sub
  180.  
  181.         ''' <summary>
  182.         ''' Sets a value based on it's provided key.
  183.         ''' </summary>
  184.         ''' <param name="name"></param>
  185.         ''' <param name="value"></param>
  186.         ''' <remarks></remarks>
  187.         Public Overrides Sub [Set](ByVal name As String, ByVal value As String)
  188.             _changed = True
  189.             MyBase.[Set](name, value)
  190.         End Sub
  191.  
  192. #End Region
  193.  
  194. #Region "Properties"
  195.  
  196.         Private _changed As Boolean = False
  197.         ''' <summary>
  198.         ''' Whether or not the NameValueCollection has changed since it was last committed to the database.
  199.         ''' </summary>
  200.         ''' <value></value>
  201.         ''' <returns></returns>
  202.         ''' <remarks></remarks>
  203.         Public ReadOnly Property Changed() As Boolean
  204.             Get
  205.                 Return _changed
  206.             End Get
  207.         End Property
  208.  
  209.         Private _tableName As String = ""
  210.         ''' <summary>
  211.         ''' The name of the table in the database that holds the NameValueCollection.
  212.         ''' </summary>
  213.         ''' <value></value>
  214.         ''' <returns></returns>
  215.         ''' <remarks>
  216.         ''' This table will need to have 2 character fields, one for the key and one for the associated value.
  217.         ''' </remarks>
  218.         Public Property TableName() As String
  219.             Get
  220.                 Return _tableName
  221.             End Get
  222.             Set(ByVal value As String)
  223.                 _tableName = value
  224.             End Set
  225.         End Property
  226.  
  227.         Private _fieldKeyName As String = ""
  228.         ''' <summary>
  229.         ''' The name of the database field which stores the key.
  230.         ''' </summary>
  231.         ''' <value></value>
  232.         ''' <returns></returns>
  233.         ''' <remarks></remarks>
  234.         Public Property FieldKeyName() As String
  235.             Get
  236.                 Return _fieldKeyName
  237.             End Get
  238.             Set(ByVal value As String)
  239.                 _fieldKeyName = value
  240.             End Set
  241.         End Property
  242.  
  243.         Private _fieldValueName As String = ""
  244.         ''' <summary>
  245.         ''' The name of the database field that stores the value.
  246.         ''' </summary>
  247.         ''' <value></value>
  248.         ''' <returns></returns>
  249.         ''' <remarks></remarks>
  250.         Public Property FieldValueName() As String
  251.             Get
  252.                 Return _fieldValueName
  253.             End Get
  254.             Set(ByVal value As String)
  255.                 _fieldValueName = value
  256.             End Set
  257.         End Property
  258.  
  259.         Private _dbConnection As IDbConnection
  260.         ''' <summary>
  261.         ''' The underlaying database connection.  
  262.         ''' </summary>
  263.         ''' <value></value>
  264.         ''' <returns></returns>
  265.         ''' <remarks>
  266.         ''' </remarks>
  267.         Public ReadOnly Property DbConnection() As IDbConnection
  268.             Get
  269.                 Return _dbConnection
  270.             End Get
  271.         End Property
  272.  
  273. #End Region
  274.  
  275.     End Class
  276.  
  277. End Namespace

Things to check when your SQL Server Compact 4.0 Database won’t work on a production server.

There are two things that you’ll want to check if you’ve developed an ASP.Net solution using SQL Server Compact 4.0 and it doesn’t work when you move it to your production environment (or any server that isn’t your workstation).  The first is that the proper assemblies have been included in your project.  My fatal mistake initially was adding a reference to the SQL Server CE libraries which worked locally but failed when porting to my production box.  How do you find all of the assemblies required and get the web.config updated without a headache?  Easy, follow these steps:

  1. Open your web site solution in Visual Studio 2010.
  2. Right click on the solution in the Solution Explorer (it’s very important that you right click on the solution and not the Bin directory to get the meny items you need).  You’ll choose “Add Deployable Dependencies…” from this menu.
  3. A dialog appear which will probably have two items (ASP.Net Web Pages with Razor Syntax and SQL Server Compact).  Choose “SQL Server Compact” and click ok.

The above will copy around 20 files into your bin directory (a combination of assemblies, XML files and a README file).  It will also update your web.config with the appropriate items for SQL Server CE.  You will want to move all of these assemblies with your site to production (and make sure the Web.config updates are reflected on your server).

Now, onto the next problem.  Even though the web page account had access to read/write to the AppData directory (it worked with Access databases for instance) it did not have some permissions needed to work with SQL Server CE 4.0.  Honestly, I never actually figured out what those permissions were.  My code would fail on the database connection open statement.  Positive that my connection string was connect I tried impersonation (here’s my VB.Net library for impersonating inside the code and not for the entire page:  http://impersonation.codeplex.com/).  Basically, I would start impersonating with my server account when the database connection was opened and end impersonating when it was closed.  This allowed me to connect to the database run queries against it.  Below is a basic example showing what I did (no error handling, but impersonates then connects to the DB).

Code Snippet
  1. Dim imp As New Iuf.Network.Authentication.Impersonation(AppSettings(“ProxyUsername”), AppSettings(“ProxyPassword”), AppSettings(“ProxyDomain”))
  2. imp.ImpersonateUser()
  3. Dim conn As New SqlCeConnection(“data source=|DataDirectory|WebServices.sdf”)
  4. conn.Open()
  5. conn.Close() : conn.Dispose() : conn = Nothing
  6. imp.UndoImpersonation()

A word of caution.  When you impersonate, your code is taking on the permissions of the user you’re impersonating with.  You can impersonate through the whole page life cycle and set it up via the web.config but I like to only impersonate for the small amounts of code that need those permissions.  The smaller amount of code that’s using heightened permissions means the less chance that you’re going to miss a bug that opens you up to security woes.  A general rule should be to use the least amount of permissions you need to get done what needs to get done.  Another general rule, always use parameterized statements to help protect against SQL injection attacks (and since I’m nagging, you should be validating all user input on top of that).  :)

SQL Server–Calculate Age with DateDiff/Rounding

One requirement for a reporting database might be to calculate the age of an individual to both ease report writers having to query birth dates frequently and/or the need to mask or remove birth date for security reasons (in the reporting environment, clearly the date would still be needed to recalculate the age on the data loads).  The DateDiff can come in handy for calculating the age.  However though, when using the DateDiff it will return a value that has been rounded up in the case of years, so individuals who are older than 32 but not yet 33 will have 33 as a value.  The easy fix?  Use the DateDiff function, have it return months instead of years, divide that by 12 and then round down (it should round down by default).  32.66 would then return 32 as the correct age.

 

Code Snippet
  1. datediff(mm,p.birth_date,getdate()) / 12  as age