Dictionary Not Found

Kept getting the error: "Dictionary Not Found..." once I uploaded new changes to the web server to allow spell checking. So, my first step was to open the developer tools in IE (F12) and do a quick capture of the network traffic.

Which gave:

URL: /fieldperformance/C1Spell_en-US.dct
Method: GET
Result: 404
Type: text/html
Received: 1.37 KB
Taken: 265 ms 
Initiator:
Wait‎‎: 1482
Start: 62
Request: 203
Response‎: 0
Cache: 0
read‎‎ Gap‎‎: 5067

So, it's looking in the fieldperformance folder for the dictionary file. Doing a quick check, yes the file is located there. The problem then ends up being the MIME type is missing.

Going to the IIS Manager, I added the Extension dct with a MIME type of application/octet-stream.

WCF & Silverlight max buffer size issue

Ran into these problems a few times with the WCF errors, in which the buffer size was the issue:

• “The remote server returned an error: NotFound”
• "Unable to read data from the transport connection: The connection was closed"
• "The maximum message size quota for incoming messages (65536) has been exceeded. To increase the quota, use the MaxReceivedMessageSize property on the appropriate binding element."

This would be an easy solution if only dealing with the WCF. Since, we are using the WCF layer to pass data to/from the Silverlight layer the problem arises in two locations -- not one.

Over at Mehroz's Experiments (http://smehrozalam.wordpress.com/2009/01/29/retrieving-huge-amount-of-data-from-wcf-service-in-silverlight-application/) he goes into good detail on solving this problem.

So in the Silverlight's ServiceReferences.ClientConfig we increase the buffer size.

Code Snippet

 <binding name="BasicHttpBinding_IScientificDataService"   closeTimeout="00:01:00"openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00" maxBufferSize="2147483647" maxReceivedMessageSize="2147483647" textEncoding="utf-8" transferMode="Buffered">
    <security mode="None"/>
binding>

In the WCF's Web.config, we needed to increase the maxBufferSize and maxReceivedMessageSize to a larger number Code Snippet   <system.serviceModel>     <behaviors>       <serviceBehaviors>         <behavior>           <serviceMetadata httpGetEnabled="true" />           <serviceDebug includeExceptionDetailInFaults="true" />           <dataContractSerializer maxItemsInObjectGraph="2147483647"/>         behavior>       serviceBehaviors>     behaviors>     <serviceHostingEnvironment multipleSiteBindingsEnabled="true" aspNetCompatibilityEnabled="false" />   system.serviceModel>

SQL String Manipulations

Interesting article in SQL Server Magazine about String Manipulations by Itzik Ben-Gan (http://www.sqlmag.com/article/tsql/string-manipulation-tips-techniques-part-1-136427)

So to give me a quick reference in the future, I'll summarize it here:

Counting Occurrences of a subString within a string:

DECLARE
  @str    AS VARCHAR(1000) = 'abchellodehellofhello',
  @substr AS VARCHAR(1000) = 'hello';

SELECT (LEN(@str) - LEN(REPLACE(@str, @substr, ''))) / LEN(@substr);


Exactly N Occurrences of a substring within a string:

DECLARE
  @str    AS VARCHAR(1000) = 'abchellodehellofhello',
  @substr AS VARCHAR(1000) = 'hello',
  @N      AS INT           = 3;

SELECT
  CASE
    WHEN (LEN(@str) - LEN(REPLACE(@str, @substr, ''))) / LEN(@substr) = @N
      THEN 'True'
    ELSE 'False or Unknown'
  END;
-OR-

SELECT
  CASE
    WHEN @str LIKE '%' + REPLICATE(@substr + '%', @N)
         AND @str NOT LIKE '%' + REPLICATE(@substr + '%', @N+1)
      THEN 'True'
    ELSE 'False or Unknown'
  END;

Replacing Multiple Contiguous Spaces with a single space:

DECLARE @str AS VARCHAR(1000) = 'this   is     a   string    with     lots   of   spaces';

SELECT REPLACE(REPLACE(REPLACE(@str, ' ', '~ '), ' ~', ''), '~ ', ' ');

Replacing Overlapping Occurrences:

DECLARE @str AS VARCHAR(1000) = '.x.x.x.x.';

SELECT REPLACE(REPLACE(@str, '.x.', '.y.'), '.x.', '.y.');

-OR-

SELECT REPLACE(REPLACE(REPLACE(@str, '.', '..'), '.x.', '.y.'), '..', '.');
String Formatting Numbers with Leading Zeros:

DECLARE @num AS INT = -1759;

SELECT CASE SIGN(@num) WHEN -1 THEN '-' ELSE '' END + REPLACE(STR(ABS(@num), 10), ' ', '0');

-OR-

SELECT CASE SIGN(@num) WHEN -1 THEN '-' ELSE '' END + RIGHT('000000000' + CAST(ABS(@num) AS VARCHAR(10)), 10);

-OR (In Denali)-

SELECT FORMAT(@num, '0000000000');

Left Trimming Leading Occurrences of a Character:

DECLARE @str AS VARCHAR(100) = '0000001709';

SELECT REPLACE(LTRIM(REPLACE(@str, '0', ' ')), ' ', '0');

Checking That a String Is Made of Only Digits:

DECLARE @str AS VARCHAR(1000) = '1759';
SELECT
  CASE
    WHEN @str NOT LIKE '%[^0-9]%' THEN 'True'
    ELSE 'False or Unknown'
  END;

-OR-

CHECK (col1 NOT LIKE '%[^0-9]%')

Median in SQL

Median is not a default aggregate in SQL-Server, but is sometime a perferable statistical function than Average. So here is quick tip on how I got the median:

Define: Median -

"The median of a finite list of numbers can be found by arranging all the observations from lowest value to highest value and picking the middle one. If there is an even number of observations, then there is no single middle value; the median is then usually defined to be the mean of the two middle values" - Wikipedia http://en.wikipedia.org/wiki/Median

By using ROW_NUMBER() function twice I can get my approximation of where the median is located.

For Example:
ROW_NUMBER() OVER (PARTITION BY Date, Array, Inverter ORDER BY Current ASC

-and-

ROW_NUMBER() OVER (PARTITION BY Date, Array, Inverter ORDER BY Current DESC

By using these two values I can pull the absolute value from their subtraction

ABS(ASCRow - DescRow)

To get an approximation of where it is associated with the median. Now if the values you are sorting are distinct, then you can find the median by looking for numbers that are less than or equal to 1 and then taking their average.

ABS(ASCRow - DescRow) <=1

In my case I can get more than a count of 2 numbers that are at the median, in this case I did another ROW_NUMBER function but this time order on the absolute difference and then I selected the top row in an outer query:

ROW_NUMBER() OVER (PARTITION BY Date, Array, Inverter ORDER BY MedianDistance) as ROW_NUM


This is where I stopped, even though there is still the chance of not getting the "true" Median. I would basically need to go further and pull all data points with the same value as the MedianDistance for those partitions and do an average.

One cause of Silverlight's White Screen of Death (WSOD)

We have this silverlight (4.0) website that would work for most, but a select few were only seeing a white screen. I tried running the website in different IE formats and even in firefox. The results were the same no matter what browsers were used. So we narrowed it down to something with the machine.

A white screen usually means that there is a silverlight error, which can be tough to trace/track down. In our case it came down to the issue that nested user controls cannot have static resources. Don't know how this applies to the machine level is beyond my comprehension.

So instead of this:
 <usercontrol.resources>
  <style targettype="sdk:DataGridColumnHeader" x:key="DataGridHeader">
      <setter Property="FontSize" Value="10"/>
      <setter Property="Padding" Value="3"/>
      <setter Property="Margin" Value="0"/>
 </style>
</usercontrol.resources>

We needed to put the style more inline:
<sdk:datagrid.columnheaderstyle>
   <style targettype="sdk:DataGridColumnHeader">
      <setter Property="FontSize" Value="10"/>
      <setter Property="Padding" Value="3"/>
      <setter Property="Margin" Value="0"/>
 </style>
</sdk:datagrid.columnheaderstyle>

Another SQL Trick/Tip

I had a request to change a view to display information as a result of "Zones" instead of "Observation". So, a little background...

For each processed file, after inserting, I set an observation number based on the row in the file. This is easily done with the ROW_NUMBER() function in SQL-Server:

ROW_NUMBER() OVER (ORDER BY ObservationSQN) AS ROWNUM

,and some other unique tricks I use to separate it out per file.

The request then asked, instead of displaying Observation Numbers, they wanted an average; and if the number is less than or equal to 0 then don't include that observation in the average.

Zone 1 has the average of observations 1,2,3,4,6
Zone 2 has the average of observations 5,8,11
Zone 3 has the average of observations 10,12,13,14,15

Well, thankfully SQL-Server doesn't include a value in it's average function if it's null. Just have to set the value to NULL if <= 0

So at first I did this brute force technique (which ends up being ugly and slow):
SELECT
avg(zone1.value) as zone1Avg,
avg(zone2.value) as zone2Avg,
avg(zone3.value) as zone3Avg,
zone1.FileSQN
FROM
(
SELECT
FileSQN,
CASE WHEN value <= 0 THEN NULL ELSE value END AS value
FROM Table1
WHERE Observation in (1,2,3,4,6)
AND DateDiff(day, ProcessEndDate, GETDATE()) <= 1)
)zone1
INNER JOIN
(
SELECT
FileSQN,
CASE WHEN value <= 0 THEN NULL ELSE value END AS value
FROM Table1
WHEREObservation in (5,8,11)
AND DateDiff(day, ProcessEndDate, GETDATE()) <= 1)
)zone2 on zone2.FileSQN = zone1.FileSQN
INNER JOIN
(
SELECT
FileSQN,
CASE WHEN  value <= 0 THEN NULL ELSE value END AS value
FROM Table1
WHERE Observation in (10,12,13,14,15)
AND DateDiff(day, ProcessEndDate, GETDATE()) <= 1)
)zone3 on zone3.FileSQN = zone2.FileSQN
GROUP BY FileSQN

The technique I ended up using, used the WITH common table expression:

WITH WithTable (FileSQN, value, ObservationNumber)
AS
(
SELECT
FileSQN,
CASE WHEN value <= 0 THEN NULL ELSE value END AS value,
ObservationNumber
FROMTable1
WHERE DateDiff(day, ProcessEndDate, GETDATE()) <= 1)
)

SELECT
zone1.zone1Avg,
zone2.zone2Avg,
zone3.zone3Avg,
FileSQN
FROM
(
SELECT
avg(value) as zone1Avg,
FileSQN
FROM
WithTable
WHERE ObservationNumber in (1,2,3,4,6)
GROUP BY FileSQN
) zone1
INNER JOIN
(
SELECT
avg(value) as zone2Avg,
FileSQN
FROM
WithTable
WHEREObservationNumber in (5,8,11)
GROUP BY FileSQN
)zone2 on zone2.FileSQN = zone1.FileSQN
INNER JOIN
(
SELECT
avg(value) as zone3Avg,
FileSQN
FROM
WithTable
WHEREObservationNumber in (10,12,13,14,15)
GROUP BY FileSQN
)


Summary: The first technique was slower, since I was basically pulling the same info 3 times, compared to the second technique's once.

Note: The names of the tables have been change to protect the innocent. I wrote this in line, so not everything might be syntactically correct

Reminder: How to use Window's Shell32 to do basic decompression (unzipping).

Add the shell32.dll reference found in C:\Windows\System32

Excerpt from my code:

Shell32.Shell sc = new Shell32.Shell();
Shell32.Folder SrcFlder = sc.NameSpace(file.FullName);
Shell32.Folder DestFlder = sc.NameSpace(args[2]);
Shell32.FolderItems items = SrcFlder.Items();
DestFlder.CopyHere(items, 20);

where:
file.FullName is a string representing the full path to the *.zip file. (Actually a FileInfo type)
arg[2] is another string representing the destination folder.

Debugging Interop in SSIS

Had a little trouble getting a COM (interop) dll/tbl from working in a SSIS's script task, while a similar program in a normal C# project would run sucessfully.

First thing I did was a quick registry hack to turn logging on for interop:
HKEY_LOCAL_MACHINE/SOFTWARE/MICROSOFT/Fusion

and created a new DWORD called EnableLog
and set the value to 1

This then gave me more info when I ran the package "without debugging".

In this case, it was having trouble finding my interop dll, because it was looking in the SQL-Server and .Net directories instead of the build directory.

So, as a quick solution, I copied the interop dlls from the build directory and drop them in the necessary folders:

C:\Program Files\Microsoft SQL Server\100\DTS\Binn\

-and-

C:\Windows\Microsoft.NET\Framework\v2.0.50727

Sending multivalue from SSRS to a stored procedure.

One, of the many, features that I wish they would add to SSRS; is an easier way to send in multivalue selected parameters to a stored procedure.

The best solution to this problem, is to use a function that converts a string of delimited values into a table.

 Here are the quick low down steps:

1) Go to your SSRS dataset properties -> Parameters -> (select the multivalue parameter function [fx]) and change the parameter to send in a sting of delimited values. For example,

=Join(Parameters!MeasurementID.Value,",")

2) Create the function that splits and creates a table:

CREATE FUNCTION dbo.fn_charlist_to_table
(
@list ntext,
@delimiter nchar(1) = N','
)
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,

str varchar(4000),
nstr nvarchar(2000)) AS

BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)

SET @textpos = 1
SET @leftover = ''

WHILE @textpos <= datalength(@list) / 2
  BEGIN
    SET @chunklen = 4000 - datalength(@leftover) / 2
    SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
    SET @textpos = @textpos + @chunklen
    SET @pos = charindex(@delimiter, @tmpstr)

    WHILE @pos > 0
      BEGIN
        SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
        INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
        SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
        SET @pos = charindex(@delimiter, @tmpstr)
       END
      
    SET @leftover = @tmpstr
  END

INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),
ltrim(rtrim(@leftover)))

RETURN
END
GO

3) In your stored procedure, call the function. For example:

Select * from measurment where
measurement.MeasurementID in (select [str] from fn_charlist_to_table(@MeasurementID,Default))

Mimic Domain for SQL Server or other executables.

One tiresome thing about being a consultant is the lack of ability to use Windows Authentication when on site. Your computer is on a separate domain and you run into the "I need permissions" issue.

One solution to this is to use the run as command in a shortcut's target.

First create a shortcut of the executable.

For this example, I used SQL Server.

C:\Windows\System32\runas.exe /netonly /user:consultantDomain\wandrus "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

The highlighted yellow is what was added, where I set the domain\user_name to what I would like to use. When this shortcut is then clicked, it will prompt for your password.