Concurrent connections to SQL server or SQL express

SQL server or SQL express supports unlimited (maxint) number of concurrent connections. SQL server installation wizard asks for number of connections to limit to. If the value given is 0 then it means unlimited number of connections. User can specify any number of connections that he wants.

It is a myth that SQL server or SQL express supports only 5 concurrent connections. There is some history behind it. Microsoft came up with MSDE engine to overcome limitations of popular Jet database engine. The initial version of MSDE engine could allow maximum 5 concurrent connections as the performance detoriorates if more than 5 concurrent connections are made to the MSDE database engine. Governor component in MSDE engine used to control the maximum connections.

The latest versions of SQL server (I guess SQL 2000 onwards) and SQL express does not have governor component. So now what limits the concurrent connections is now how the connection is made, for how much time and how it is used.

CoCreateInstance returns CO_E_SERVER_EXEC_FAILURE (0x80080005) for ATL service created using Microsoft Visual Studio 2008

Create an ATL service project using Microsoft Visual Studio 2008. If you try to create instance of an object which is hosted by the service, CoCreateInstance returns error 0x80080005. The error means server execution failed.

It took lot of time to resolve this error and was quite frustrating as the error message is generic and does not give any clue about what could have gone wrong.
I tried creating a service which has one COM object and has a foo() method which does nothing and just returns. The CoCreateInstance was failing for this object as well with same error code.

While googgling for the error code, I came across an article which talks about a defect in Visual Studio 2008 wizard generated registry files. http://blogs.msdn.com/jigarme/archive/2008/05/08/cocreateinstance-returns-0x80080005-for-visual-studio-2008-based-atl-service.aspx

The APPID entry is missing from the wizard generated .rgs file. The error got fixed after adding the APPID entry in the .rgs file

ForceRemove {9ACB97C6-4492-40E8-A1BD-51DFE8962E92} = s 'CMyObject Class'
{
ProgID = s 'ATLComService.CMyObject.1'
VersionIndependentProgID = s 'ATLComService.CMyObject'
ForceRemove 'Programmable'
LocalServer32 = s '%MODULE%'
val AppID = s '%APPID%'
'TypeLib' = s '{E423A7DE-58C7-4535-A864-395DCEDE941F}'
}

GetRecordInfoFromTypeInfo and GetRecordInfoFromGuids returns E_INVALIDARG if COM structure contains a GUID member field

Recently I was working on developing a COM service which returns a SAFERRAY of structure as out parameter. This structure has member of type GUID.

To create SAFEARRAY of custom data types, you need to get the IRecordInfo pointer to the structure and pass it to SafeArrayCreateEx. You can get IRecordInfo pointer to the custom structure by calling either GetRecordInfoFromTypeInfo or GetRecordInfoFromGuids API.

Both of these API returns E_INVALIDARG if the sturcture contains member of type GUID. One way to workaround this problem is to convert GUID as BSTR.

This is a defect in Microsoft Visual Studio 2008 and hopefully will get fixed in next versions. Link to the microsoft page for same. https://connect.microsoft.com/VisualStudio/feedback/details/380266/getrecordinfofromtypeinfo-returns-e-invalidarg-if-a-com-structure-contains-a-guid-field

Running SQL service as console application

This might be a known fact to many of the SQL users. But I did not knew that SQL service can run as a console application which accepts command line parameters for various level of log tracing. Steps for starting SQL service as console application
1. Stope SQL server service.
2. Start sqlserver.exe in console mode using following command
start /realtime sqlserver.exe -c -s YourInstanceName. Omit -s if you are testing with default instance
3. Pass command line parameter to enable trace flag for e.g. to trace event 3505 pass 'T3505'.
You must switch to the appropriate BINN directory for the instance at a command prompt before starting sqlservr.exe. For example, if Instance1 were to use \mssql$Instance1 for its binaries, the user must be in the \mssql$Instance1\binn directory to start sqlservr.exe -s instance1.
SQL server error log file locationSQL 2008 and 2005-> Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files.

Easing The Pain Of WCF Debugging

Recently I came across a nice article about WCF debugging. htttp://davybrion.com/blog/2008/08/easing-the-pain-of-wcf-debugging/. It explains how to enable WCF tracing. It logs all the WCF activities and provides clues for the potential issues. Error tracing is quite detailed. It actually helped me a in resolving few issues related to unhanlded exceptions. The exception message text is quite detailed and sometimes gives the clue about the solution. Quit helpful and must know for every WCF developer.

WCF error: System.ServiceModel.CommunicationException: The socket connection was aborted

I wrote my first WCF service recently which supported tcp as a transport protocol. During testing I faced a very strange problem. The service used to work for sometime and then suddenly would start throwing Communication exception.
System.ServiceModel.CommunicationException: The socket connection was aborted. This could be caused by an error processing your message or a receive timeout being exceeded by the remote host
This error was very annoying and there was no clue what is going wrong. While googling for this, I came across some posts which talked about this issue. Every WCF channel has a receive timeout whose value is picked up from the app.config file when you add proxy for the service. If there is no activity during this timeout then WCF closes underlying socket and so further call on the channel throws Communication exception.
To resolve this issue, set the receive timeout to appropriate value at both client as well as server endpoints. You can use 'infinite' keyword to suggest WCF to use TimeSpan.MaxValue value. 'infinite' keyword is not recognized by Visual Studio editor and would show error for that which can be safely ignored. Another way to set infinite timeout is programmatically. At both endpoints (i.e. at client and server) set the endpoint's receivetimeout propery to TimeSpan.MaxValue.
Below is good posts on the same issue -
http://social.msdn.microsoft.com/forums/en-US/wcf/thread/06cb1522-31f0-4ce3-85f0-02656228a8e1/

Log truncation fails on SQL 2005 and SQL 2008 server

This problem was coming on SQL 2005 and SQL 2008 SQL server on both 32 bit as well as 64 bit platforms. After much digging into it, I found that issue comes only with fresh SQL server or any fresh database on the SQL server. The problem occures because I was trying to truncate the logs before taking FULL backup of the database which is not allowed by the Microsoft. This seems to be logical as well; logs cannot be truncated unless database is backed up atleast once. Here is the link from Microsoft web site which supports same thing.
http://msdn.microsoft.com/en-us/library/ms186865.aspx.