Monday, February 25, 2013

SQL Server - FileStream content length using len and datalength

Basically there are some differences between len and datalength functions which almost all SQL developers may know. len() returns the no of characters by trimming the right side and datalength() returns the no of bytes used to store the data structure in the table.
I would like to add one more scenario to this comparison which is nothing but finding length of FileStream content. If you are new to SQL FileStream please refer this article. The scenario we were encountering was so simple. We had to list out all the files which are empty. To be more on business side, need to delete all the zero byte files in file stream storage table.
Below is the table def
CREATE TABLE [DocumentContent](
    [DocumentContentID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [FileStreamContent] [varbinary](max) FILESTREAM  NULL,
 CONSTRAINT [PK_DocumentContent] PRIMARY KEY NONCLUSTERED 
(
    [DocumentContentID] ASC
))
The initial query used to determine the list of blank files is
select DocumentContentID 
from DocumentContent 
where len(FileStreamContent) <=1
This works without errors but the time taken to execute is really huge and directly proportional to the no of documents in the table also the size of the documents. Sometimes exceeds 30 seconds and started getting timeouts and eventually deadlocks. So modified as follows.
select DocumentContentID 
from DocumentContent 
where datalength(FileStreamContent) <=1
It rocks. Now the query takes normal time even if the table has many rows.

Tuesday, February 19, 2013

Configuration error when converting from ASP.Net 3.5 to ASP.Net 4.0

There is a duplicate 'system.web.extensions/scripting/scriptResourceHandler' section defined

The above message appeared when we were trouble shooting a staging server. When we looked at our config there was no duplicate section. We really got confused about this and commented out this section and continued testing since the intention of that web.config was to host services rather than web pages.

But the question remained there till our next tea. The idea flashed when somebody said about a bug in Microsoft windows. Suddenly we googled and found the below link.

http://www.asp.net/whitepapers/aspnet4/breaking-changes#0.1__Toc256770149

According to this article, .Net 4.0 moved the some configurations section to the root level (machine.config) which were common in all the .net 3.5 ASP.Net apps. So if we just change the application pool of .Net 3.5 application to .Net 4.0, this error message will popup.So better to convert using Visual Studio 2010 or remove this configuration element manually.

The sad part was the application which we were trouble shooting is supposed to install in a .Net 3.5 app pool.By mistake only it came to ASP.Net 4.0 pool and showed this error. Wasted some time but got a future time saving point.

Monday, February 11, 2013

Maximum length of .net exe filename

Environment : Win 7
.Net version : 4.0 (File version of C:\Windows\Microsoft.NET\Framework\v4.0.30319\mscorlib.dll is 4.0.30319.17929)
Technology : WPF

We had a situation where there is a WPF exe which contains more than 47 characters in file name .When we double click on the exe it simply shows 'not working'.

eg: The below file works
ABCEDFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTU.exe
But if we add one more character to the file name it stops working
ABCEDFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUV.exe

This happens even in case we place this file in c:\ which means this is not windows file limitation.

Console application

Obviously when we hit with this issue the first will be to try out the scenario in a console application. I tried that and there is no issue. We can have big file names.

Whats the error showing

There are no exception thrown in .net code. Simple shows stopped working. When we get the "Stopped working" dialog, if we select debug with new Visual Studio instance it shows some call stack in kernel

In the event log its logged as Application Error


Faulting application name: ABCEDFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUV.exe, version: 1.0.0.0, time stamp: 0x50e559e0
Faulting module name: igdumd32.dll, version: 8.15.10.1995, time stamp: 0x4af4b4e4
Exception code: 0xc0000409
Fault offset: 0x00014fe6
Faulting process id: 0x13c8
Faulting application start time: 0x01ce08d99c9f454b
Faulting application path: E:\ABCEDFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUV.exe
Faulting module path: C:\Windows\system32\igdumd32.dll
Report Id: dbba9063-74cc-11e2-bed6-005056c00008


Also there is another information entry


Fault bucket 50, type 5
Event Name: BEX
Response: Not available
Cab Id: 0

Problem signature:
P1: ABCEDFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUV.exe
P2: 1.0.0.0
P3: 50e559e0
P4: igdumd32.dll
P5: 8.15.10.1995
P6: 4af4b4e4
P7: 00014fe6
P8: c0000409
P9: 00000000
P10:

Attached files:
C:\Users\joyg\AppData\Local\Temp\WERDD06.tmp.WERInternalMetadata.xml

These files may be available here:
C:\Users\joyg\AppData\Local\Microsoft\Windows\WER\ReportArchive\AppCrash_ABCEDFGHIJKLMNOP_934479f7c7ed9f73aa3ad0f14ce25746e867c84a_1f8bfff1

Analysis symbol:
Rechecking for solution: 0
Report Id: e98cb1b8-74cb-11e2-bed6-005056c00008
Report Status: 0


What to do

When googled we were left with no answers. Asked in Stackoverflow .For the time being we reduced the file length and continuing. Hopefully can get some answers using windbg.

Monday, February 4, 2013

MS Word interop and {"Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))"}

When you first see below exception your Word interop code execution in production environment, you think that there is something directly related with security. It normally results in spending more time on verifying the security of files and com dlls.

System.UnauthorizedAccessException {"Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))"}

But is that the actual problem?
No.When we got this exception, we spent sometime on the security aspects. But when we looked at the below code we easily found that this is thrown because a com object is not supporting TextFrame property. The code was as follows.

For Each oShape As Word.Shape In wDoc.Shapes
                    If (oShape.TextFrame IsNot NothingThen
                        If (oShape.TextFrame.HasText <> 0 AndAlso oShape.TextFrame.TextRange IsNot NothingThen
                            'Processing code
                        End If
                    End If
...

Actually when we iterate through the Shapes, there may be some shapes which may not support the TextFrame property .We need to avoid those shapes from the TextFrame processing. This confusion would have been avoided if MSFT provides a meaningful exception message.

Happy debugging...