memory.Refresh()

When remoting, it’s possible to get a strange exception if a SQLException occurs on the server.

The stack trace of the error is:

Exception: System.Runtime.Serialization.SerializationException  
Message: Member name ‘System.Data.SqlClient.SqlError server’ not found.  

Stack trace:
at System.Runtime.Serialization.Formatters.Binary.ReadObjectInfo.GetMemberTypes (String[] inMemberNames)  
at System.Runtime.Serialization.Formatters.Binary.ObjectMap..ctor(String objectName, String[] memberNames, BinaryTypeEnum[] binaryTypeEnumA, Object[] typeInformationA, Int32[] memberAssemIds, ObjectReader objectReader, Int32 objectId, BinaryAssemblyInfo assemblyInfo, SizedArray assemIdToAssemblyTable)  
at System.Runtime.Serialization.Formatters.Binary.ObjectMap.Create(String name, String[] memberNames, BinaryTypeEnum[] binaryTypeEnumA, Object[] typeInformationA, Int32[] memberAssemIds, ObjectReader objectReader, Int32 objectId, BinaryAssemblyInfo assemblyInfo, SizedArray assemIdToAssemblyTable)  
at System.Runtime.Serialization.Formatters.Binary.__BinaryParser.ReadObjectWithMapTyped(BinaryObjectWithMapTyped record)  
at System.Runtime.Serialization.Formatters.Binary.__BinaryParser.ReadObjectWithMapTyped(BinaryHeaderEnum binaryHeaderEnum)  
at System.Runtime.Serialization.Formatters.Binary.__BinaryParser.Run()  
at System.Runtime.Serialization.Formatters.Binary.ObjectReader.Deserialize(HeaderHandler handler, __BinaryParser serParser, Boolean fCheck, IMethodCallMessage methodCallMessage)  
at System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Deserialize (Stream serializationStream, HeaderHandler handler, Boolean fCheck, IMethodCallMessage   methodCallMessage)
at System.Runtime.Remoting.Channels.CoreChannel.DeserializeBinaryResponseMessage(Stream inputStream, IMethodCallMessage reqMsg, Boolean bStrictBinding)  
at System.Runtime.Remoting.Channels.BinaryClientFormatterSink.SyncProcessMessage(IMessage msg)

What is actually happenning here is that the System.Data.dll on the client is a slightly different version to that on the server. When the SQLException is deserialised at the client end, there is a missing property on the client that cannot be deserialised.

This error usually occurs when remoting to a Windows 2003 Server box from a non-Windows 2003 client box. The client has a System.Data.dll of version 1.1.4322.2032. The server has a System.Data.dll of version 1.1.4322.2300. The difference is that the server property of the SqlError class is never set in v1.1.4322.2032, causing the serialisation error. More detailed info can be found on the DevNewsGroups site.

Microsoft has two knowledge base articles, KB884871 and KB887549, that pertain to this issue. The suggested solution is a .NET Framework 1.1 post-SP1 hotfix, but this is only available by contacting Microsoft directly.

The hotfix solves the problem, but then stops old SqlError types from being deserialised, resulting in the exception:

Exception: System.Runtime.Serialization.SerializationException
Message: Wrong number of Members. Object System.Data.SqlClient.SqlError has 8 members, number of members deserialized is 7.

Stack trace:
at System.Runtime.Serialization.Formatters.Soap.ReadObjectInfo.PopulateObjectMembers()
at System.Runtime.Serialization.Formatters.Soap.ObjectReader.ParseObjectEnd(ParseRecord pr)
at System.Runtime.Serialization.Formatters.Soap.ObjectReader.Parse(ParseRecord pr)
at System.Runtime.Serialization.Formatters.Soap.SoapHandler.EndElement(String prefix, String name, String urn)
at System.Runtime.Serialization.Formatters.Soap.SoapParser.ParseXml()
at System.Runtime.Serialization.Formatters.Soap.SoapParser.Run()
at System.Runtime.Serialization.Formatters.Soap.ObjectReader.Deserialize(HeaderHandler handler, ISerParser serParser)
at System.Runtime.Serialization.Formatters.Soap.SoapFormatter.Deserialize(Stream serializationStream, HeaderHandler handler)
at System.Runtime.Serialization.Formatters.Soap.SoapFormatter.Deserialize(Stream serializationStream)

This means that the hotfix is not backwards-compatible. The only way to get everything happy is to upgrade all Windows 2003 boxes to SP1 and all clients (Windows 2000 and Windows XP) to .Net 1.1 SP1 plus the hotfix.

Andras Belokosztolszki from Red Gate posted an interesting article about the sp_rename stored procedure in SQL Server and the pitfalls that can occur after its use.

When a stored procedure is created, an object is created in the sysobjects table (or the sys.objects view in SQL Server 2005), and the textual definition is stored in the syscomments table (or the sys.sql_modules view in SQL Server 2005).

When using sp_rename to rename a stored procudure, the definition is left intact and only the name is changed in the sysobjects table (or sys.objects view). This means that the definition of the stored procedure stored in database now has the wrong name.

The best way of renaming a stored procedure is to completely delete it and then recreate it with the new name.

The following SQL displays the definitions of all stored procedures in a database alongside their names:

SQL Server 2000

SQL Server 2000
SELECT V2.[name], V1.[text]
    FROM syscomments AS V1, sysobjects AS V2
    WHERE V1.[id] = V2.[id]
    AND (V2.[xtype] = 'P' OR V2.[xtype] = 'F')

SQL Server 2005

SQL Server 2005
SELECT V2.[name], V1.[definition]
    FROM sys.sql_modules AS V1, sys.objects AS V2
    WHERE V1.[object_id] = V2.[object_id]

It is worth noting that enterprise manager uses the sp_rename stored procedure when using the right-click rename option (although SQL Server 2005 management studio does try to correct this problem if you view the definition of a renamed stored procedure by replacing the original stored name with the new one).

In .Net Windows Forms, any form or control that derives from a Component has a DesignMode property that tells you whether the control is being rendered in design mode in Visual Studio. This is useful if your control has different behaviour at runtime than at design time (for example, if you show a connection dialog when a form opens but you don’t want it to happen in design mode inside Visual Studio).

The DesignMode property has its quirks though. Using Lutz Roeder’s Reflector reveals the implementation of the DesignMode property:

protected bool DesignMode
{
    get
    {
        if (this.site != null)
        {
            return this.site.DesignMode;
        }
        return false;
    }
}

This shows that design time support is not hooked up until the control is sited. Siting happens after the control is created, but before any properties are set, so if you check the DesignMode property in the constructor of a control, it will always be false.

There is also a bug with the DesignMode property whereby a custom control inside a custom control will always report its DesignMode property as false. Microsoft has more details of this in knowledge base article KB839202 and in their Visual Studio feedback website.

A workaround to both of the above problems it to use the following:

Application.ExecutablePath.ToLower().IndexOf("devenv.exe") > -1

A bit crude, but it works.

The latest trend for even the simplest of tools is to provide the executables as an MSI file, requiring an installation to run the application. Lots of applications simply do not require an installation and will work if the .exe contained in the MSI file is run.

Scott Willeke has made a nice little tool called Less MSIerables (aka lessmsi) that can extract the contents of an MSI file directly to disk with no installation. Extracting the contents and running the .exe has worked successfully on every simple application I’ve tried so far.

SQL Server Management Studio provides a simple results view to show the data contained in a table. With this view, it’s possible to edit the data in the table but there is no obvious method of setting a nullable field to null - emptying the cell simply sets the value to empty, which may or may not work depending on the type of the column.

To set a null value, you have to use the Ctrl-0 (Control + Zero) shortcut. Why is there no option in the GUI to do this?