Posts filed under 'kb'

SQL Server 2005 SP3 Released

Service pack 3 for SQL Server 2005 was released last week. In it, they have fixed a curious bug that I reported back in January.

The bug occurs when trying to delete rows from a table that have a NULL value for an image column. This works fine normally, but if there is a foreign key referencing the table (to any of its columns), any rows that have had their image column updated to be NULL fail to be deleted. This SQL demonstrates the problem:

-- create two linked tables
CREATE TABLE [dbo].[TableA]
(
    [Identity] [int] NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [TableB_Identity] [int] NULL
)

CREATE TABLE [dbo].[TableB]
(
    [Identity] [int] NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [DATA] image NULL
)

ALTER TABLE [TableA] ADD CONSTRAINT [FK_TableA_TableB]
    FOREIGN KEY ([TableB_Identity]) REFERENCES [TableB] ([Identity])

-- insert some data
INSERT INTO [TableB] ([DATA]) VALUES (NULL)
INSERT INTO [TableB] ([DATA]) VALUES (NULL)
INSERT INTO [TableB] ([DATA]) VALUES (NULL)

-- this delete works successfully
DELETE FROM [TableB] WHERE [DATA] IS NULL
SELECT COUNT(*) AS Remaining_Count_Should_Be_0 FROM [TableB]

-- insert some data
INSERT INTO [TableB] ([DATA]) VALUES (NULL)
INSERT INTO [TableB] ([DATA]) VALUES (NULL)
INSERT INTO [TableB] ([DATA]) VALUES (NULL)

-- update the data to be have a NULL value
UPDATE [TableB] SET [DATA] = NULL WHERE [DATA] IS NULL

-- this delete doesn't work
DELETE FROM [TableB] WHERE [DATA] IS NULL
SELECT COUNT(*) AS Remaining_Count_Should_Be_0 FROM [TableB]

-- this delete doesn't work
DELETE FROM [TableB] WHERE ISNULL([DATA], NULL) IS NULL
SELECT COUNT(*) AS Remaining_Count_Should_Be_0 FROM [TableB]

-- this delete does work successfully
DELETE FROM [TableB] WHERE EXISTS
    (
        SELECT * FROM [TableB] AS TB
        WHERE [DATA] IS NULL
        AND TB.[Identity] = [TableB].[Identity]
    )
SELECT COUNT(*) AS Remaining_Count_Should_Be_0 FROM [TableB]

Not all of the delete queries work correctly. The output of the script is four result sets with the count of how many rows are in the table at each point. All of them should be 0 (as is the case on SQL Server 2000), but in SQL SERVER 2005 without SP3 they are actually 0, 3, 3 and 0.

The simple delete query:

DELETE FROM [TableB] WHERE [DATA] IS NULL

does not delete any rows after the values for the Data column have been updated to NULL, even though a similar select query:

SELECT * FROM [TableB] WHERE [DATA] IS NULL

will return rows.

Notably, if either the foreign key is removed, or the:

UPDATE [TableB] SET [DATA] = NULL WHERE [DATA] IS NULL

query is not performed, the script behaves as expected. Additionally, using text or ntext instead of image does not work as well, but using the new varchar(max), nvarchar(max) or varbinary(max) data types does work.

Apparrently, the distinction between NULL values stored as a result of an insert or an update has precendece in the WRITETEXT command:

If the table does not have in row text, SQL Server saves space by not initializing text columns when explicit or implicit null values are added in text columns with INSERT, and no text pointer can be obtained for such nulls. To initialize text columns to NULL, use the UPDATE statement. If the table has in row text, you do not have to initialize the text column for nulls and you can always get a text pointer.

This points to the "text in row" option having a bearing on this behaviour. Indeed, altering this option after creating the tables:

sp_tableoption N'TableB', 'text in row', 'ON'

results in the script working as expected. Useful as a potential workaround.

The bug is present in all versions of SQL Server 2005, but not in SQL Server 2000 or 2008.

A full list of what's changed in SP3 can be found here, with a full list of the bugs fixed here.

Add comment Permalink December 23rd, 2008 Adrian Banks
Subscribe to this feed Bookmark this post on del.icio.us Digg this post on digg.com Share this post

The Difference Between Format And Quick Format

Having done several installations of Windows over the years and always chosen the "quick format" option over the normal format option (mainly due to the time it takes to do a full format compared to a quick format), I finally looked into what the differences between the two are. Microsoft's site has a knowledge base article about this very thing.

A [full] format will wipe the disk, format it and run a check disk to find any bad sectors. A quick format will wipe the disk and format it, but will skip the check disk stage. As it turns out, the check disk stage is the thing that causes a full format to take a lot longer than a quick format.

If formatting a new hard disk, it is probably wise therefore to do a full format to find any sectors that may have been damaged whilst the disk was in transit. If formatting an old disk, it is already in a known state and so a quick format should suffice.

1 comment Permalink March 23rd, 2007 Adrian Banks
Subscribe to this feed Bookmark this post on del.icio.us Digg this post on digg.com Share this post

SerializationException And System.Data.SqlClient.SqlError

When remoting, it's possible to get a strange exception if an 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-W2003 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 directory.

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 W2003 boxes to SP1 and all clients (W2000 and XP) to .Net 1.1 SP1 plus the hotfix.

1 comment Permalink July 19th, 2006 Adrian Banks
Subscribe to this feed Bookmark this post on del.icio.us Digg this post on digg.com Share this post

Windows Forms And The DesignMode Property

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.

Add comment Permalink June 30th, 2006 Adrian Banks
Subscribe to this feed Bookmark this post on del.icio.us Digg this post on digg.com Share this post

Visual Studio 2005 And Proxy Authentication

Using Visual Studio 2005 today, I received a "407 Proxy Authentication Required" message when trying to search the online help integrated into the IDE. KB910804 from Microsoft held the answer.

In order to allow VS2005 through an authenticating proxy, you have to edit the Visual Studio configuration file located at C:\Program Files\Common Files\Microsoft Shared\Help 8\dexplore.exe.config. To add support for the proxy, make sure the proxy element is present:

<configuration>
    ...
    <system.net>
        ...
        <defaultProxy enabled="true" useDefaultCredentials="true">
            <proxy bypassonlocal="True"
                   proxyaddress="http://yourproxy:port"/>

        </defaultProxy>
    </system.net>
</configuration>

where the correct proxy server is configured in the proxyaddress element.

If you use the integrated help viewer, you must also add this xml to the configuration file located at C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\devenv.exe.config.

Add comment Permalink June 20th, 2006 Adrian Banks
Subscribe to this feed Bookmark this post on del.icio.us Digg this post on digg.com Share this post


Calendar

March 2017
M T W T F S S
« Oct    
 12345
6789101112
13141516171819
20212223242526
2728293031  

Posts by Month

Posts by Category

RSS Feeds Feeds

Visitors

Visitor Map