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.

How to bulk insert efficiently

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

To insert a generic list it must be converted to a DataTable:

        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. In the example the user table is bulk inserted:

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);
    }
}

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.

ValidateRequest=“false” doesn't work in Asp.Net 4

Before Asp.Net 4.0 is some scenarios it was useful to disable the automatic request validation performed by the .Net Framework to prevent Script Attacks.

This could be done by going to web.config or the page and setting

 ValidateRequest="false"

In Asp.Net 4.0 this feature only works if we set requestValidationMode="2.0" :

Transaction management

In the context of a transaction each work-unit performed in a database must either complete in its entirety or rollback entirely.
In SQL Server an "normal" exception does not rollback a transaction by default.

In the old days the T-SQL  to manage a transaction was tedious.
An error variable must be checked to rollback the transaction if needed.
 Example:

BEGIN TRANSACTION 

...
IF (@@ERROR <> 0)
BEGIN
   ROLLBACK TRANSACTION 
    RAISERROR ('Error Description.', 16, 1)
END

COMMIT TRANSACTION

But the newer versions of SQL Server support TRY CATCH block, that simplifies the process:

BEGIN TRY
BEGIN TRANSACTION

...

IF @@TRANCOUNT > 0
BEGIN
    COMMIT TRANSACTION
END

END TRY
BEGIN CATCH

 DECLARE @ErrorMessage VARCHAR(4000)
  
 SET @ErrorMessage = 'Procedure: ' + ISNULL(ERROR_PROCEDURE(), '') + ' Line: ' + 
CAST(ERROR_LINE() AS VARCHAR(10)) + ' Message: ' + ERROR_MESSAGE()
  
 IF @@TRANCOUNT > 0
 BEGIN
  ROLLBACK TRANSACTION;
 END
  
 RAISERROR (@ErrorMessage, 16, 1)  

END CATCH;

Another way of doing the same is using SET XACT_ABORT. I don't recommend using it, but in some temporary scripts it can be useful.

The SET XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a statement raises a run-time error.

SET XACT_ABORT ON
BEGIN TRANSACTION

....

COMMIT TRANSACTION

Note also that the RAISERROR stament does not honor the XACT_ABORT setting, so if an error occurs there is no automatic rollback even if XACT_ABORT is ON.

How to stop windows time synchronization

In a testing scenario where there are calculations depending on the current time, it may be useful to set the windows time to a fixed date.

The problem is that the NTP time source in Windows Server 2008 will sync the time with the internet or the active directory. To stop this synchronization, the service windows time can be stopped or even un-installed.

This is where de command w32tm comes in:

w32tm /unregister

Also to stop the windows time service:

net stop "windows time"

Now the date can be fixed.

The problem is that the windows authentication is going to fail when trying to connect those servers. The problem isn’t a issue if the testing scenario is to set the sql server database server date and you can use sql server authentication.

Good testing.

WCF And Debugging Exceptions

A common problem in WCF is to receive the exceptions on client when debugging.
The WCF behaviors configuration allows this using the includeExceptionDetailInFaults, but to receive the inner expection the returnUnknownExceptionsAsFaults must also be set to true.
The maxItemsInObjectGraph is only to avoid the error of exceeding the maximum number of items that can be serialized or deserialized.

<behaviors>
    <serviceBehaviors>
        <behavior name="MyServiceBehavior" returnUnknownExceptionsAsFaults="True">          
            <serviceDebug includeExceptionDetailInFaults="true" />
            <dataContractSerializer maxItemsInObjectGraph="2147483647" />
        </behavior>
    </serviceBehaviors>
</behaviors>            

Run Visual Studio in the context of an administrator account

If visual studio displays the error "... run Visual Studio in the context of an administrator account" a simple fix can be made:
1) Goto C:\Program Files (x86)\Common Files\Microsoft shared\MSEnv
2) Find the file VSLauncher.exe
3) Right click on it, select Properties, and then the Compatibility tab
4) Check the box for Run this program as an administrator

From now on when a solution file is opened the visual studio runs in the context of an administrator.

The same procedure must also be made for any shortcut to visual studio that you may have.