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.

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.

Sql Server 2012 implements the THROW statement that is suggested by Microsoft to be used instead of the RAISERROR.
The main difference is that the THROW:
1) Can re-throw the original exception that is caught in the CATCH block.
2) Causes the statement batch to be ended and the next statements aren't executed.
3) There is no severity parameter.The exception severity is always set to 16.
4) Requires preceding statement to end with semicolon (;).
5) Default THROW statement will show the exact line where the exception was occurred


A simple usage example:

BEGIN TRY
BEGIN TRANSACTION

...

IF @@TRANCOUNT > 0
BEGIN
    COMMIT TRANSACTION
END

END TRY
BEGIN CATCH 
 IF @@TRANCOUNT > 0
 BEGIN
  ROLLBACK TRANSACTION;
 END;
  
  THROW  

END CATCH;

Note that Microsoft states that: "The THROW statement honors SET XACT_ABORT RAISERROR does not. New Applications should use THROW instead of RAISERROR."

The THROW syntax is:
THROW [error number] [message] [state]

The two first parameters are self explanatory.
The state parameter is a constant or variable between 0 and 255 that indicates the state to associate with the message.
CodeProject

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.

How to set a date to the first / last day in TSQL

To set a date to the first / last day in TSQL:

First day:

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)

Last Day :

SELECT DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))

How to convert a table column in comma separated values (CSV)

To convert a table column in comma separated values (CSV), I use the following snippet:

DECLARE @table TABLE
(
    Id INT
)

INSERT INTO @table
VALUES (1),(5),(88), (99)

SELECT SUBSTRING(
(
SELECT ',' + CAST(Id AS VARCHAR(200))
FROM @table 
FOR XML PATH('')
)
,2,2000) AS VAL

Web developer tools for the browser

The Web Developer extension adds various web developer tools to a browser. The extension is available for Firefox and Chrome, and will run on any platform that these browsers support including Windows, Mac OS X and Linux.

For more information press here.

IIS7 Sessions Getting Crossed / Mixed Up / Copied

I have detected a problem on a web farm:
IIS 7 Sessions of the useres were getting Crossed / Mixed Up / Copied. A user was getting the session of another!!!!

After some research I found that IIS 7 now has the ability to cache dynamic content as well.
It is a new feature in IIS7, this version of IIS introduced some new caching features.
(i) IIS7 automatically caches static content, such as HTML pages, images, and style sheets.
(ii) IIS7 now has the ability to cache dynamic content as well.

In IIS7, disable the caching for .aspx pages in any directory with an asp.net page that depends on the session state. The steps to do this are:
1. Run the Server Management console and navigate to Roles -> Web Server (IIS) -> Internet Information Services.
2. Select the site you wish to modify.
3. Select the folder that contains the .aspx pages you need to turn caching off for.
4. In the Feature View, double-click “Output Caching”.
5. If there is a rule there already for the .aspx extension double click it. Otherwise right click and select “Add…”
6. Enter .aspx for the “File name extension”
7. Check "User-mode caching", "Prevent all caching", check "Kernel-mode caching" and "Prevent all caching"

For more detailed information press here.