How to Convert rows to CSV in T-SQL

To convert several rows to unique row in CSV using T-SQL, use the following code:

DECLARE @myList varchar(MAX)
SELECT @myList = coalesce(@myList + ',', '') + UserName 
FROM [User]

SELECT @myList

WCF: The maximum string content length quota (8192) has been exceeded

A very annoying error I received when passing a very large string to a WCF Service was:

The formatter threw an exception while trying to deserialize the message: There was an error while trying to deserialize parameter . The InnerException message was 'There was an error deserializing the object of type System.String. The maximum string content length quota (8192) has been exceeded while reading XML data. This quota may be increased by changing the MaxStringContentLength property on the XmlDictionaryReaderQuotas object used when creating the XML reader. Line 8, position 9944.'. Please see InnerException for more details.

This is a cryptic error and only after some investigation I found the solution:

The readerQuotas element must be configured on the binding configuration:

<binding name="basicHttpBinding_IMyService" maxReceivedMessageSize="63400320">
      <readerQuotas maxDepth="2147483647" maxStringContentLength="2147483647" maxArrayLength="2147483647"
       maxBytesPerRead="2147483647"   maxNameTableCharCount="2147483647"/>
</binding><br />

This is a simple example, you must configure it for your own needs.
I have added the additional configuration elements to show the other options.

Bulk insert efficiently

Using an ORM like Entity Framework or performing single inserts is not efficient to perform bulk operations.
To  bulk insert efficiently, the SqlBulkCopy class ADO.Net must be used.

The SqlBulkCopy class is used to efficiently bulk load a Sql Server table with data from another sources.
SQL Server has a command-prompt utility named bcp to also perform a bulk insert. The SqlBulkCopy is the equivalent for writing managed code.

The data source, like for example a flat file, can be converted to a generic list, that is the source of the SqlBulkCopy or a DataTable can be used directly.

To bulk insert a generic list, it must be converted to a DataTable so that it can be used by the SqlBulkCopy:

public static DataTable ConvertToDataTable<T>(IList<T> list)
{
    PropertyDescriptorCollection propertyDescriptorCollection = TypeDescriptor.GetProperties(typeof(T));
    DataTable table = new DataTable();
    for (int i = 0; i < propertyDescriptorCollection.Count; i++)
    {
        PropertyDescriptor propertyDescriptor = propertyDescriptorCollection[i];
        Type propType = propertyDescriptor.PropertyType;
        if (propType.IsGenericType && propType.GetGenericTypeDefinition() == typeof(Nullable<>))
        {
            table.Columns.Add(propertyDescriptor.Name, Nullable.GetUnderlyingType(propType));
        }
        else
        {
            table.Columns.Add(propertyDescriptor.Name, propType);
        }
    }
    object[] values = new object[propertyDescriptorCollection.Count];
    foreach (T listItem in list)
    {
        for (int i = 0; i < values.Length; i++)
        {
            values[i] = propertyDescriptorCollection[i].GetValue(listItem);
        }
        table.Rows.Add(values);
    }
    return table;
}

Then the SqlBulkCopy can be used.
The steps to use the SqlBulkCopy are simple:
1) A SqlConnection open must be passed to it's constructor.
2) A DataTable must have the data to bulk insert
3) The mappings between the database columns and the DataTable must be defined, using the ColumnMappings propery.  The Column mappings define the relationships between columns in the data source and columns in the destination.
4) The DestinationTableName property must be set with the name of the destination table on the server.
5) The WriteToServer method copies all the rows of the DataTable to the specified destination table.

In the following example the user table is bulk inserted, where dt is the DataTable with the data to bulk insert.

DataTable dt = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection))
    {
        sqlBulkCopy.ColumnMappings.Add("UserID", "UserID");
        sqlBulkCopy.ColumnMappings.Add("UserName", "UserName");
        sqlBulkCopy.ColumnMappings.Add("Password", "Password");
        sqlBulkCopy.DestinationTableName = "User";
        sqlBulkCopy.WriteToServer(dt);
    }
}

The WriteToServer method also supports a DataRow array or a IDataReader.
CodeProject

Recover the mirror database

Database mirroring is a software solution for increasing database availability.
Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.
When the mirror is removed from the Primary database the mirrored one sometimes doesn't recover and it says "Recovering".
If the database doesn't leave this state, the following TSQL command can help:

RESTORE DATABASE myDatabase WITH RECOVERY

This will allow to recover the former mirror database.