It doesn't always needs to be a complicated solution... EWS “The request failed. The remote server returned an error: (401) Unauthorized"

After following this MSDN article about configuring impersonation for EWS. I was ready to go test my newly created user with impersonation rights. I already had an other account with impersonation rights which worked just fine with the following piece of code.

 Try  
      Dim svc As New ExchangeService()  
      svc.Url = New Uri("https://outlook.office365.com/EWS/Exchange.asmx")  
      svc.Credentials = New WebCredentials("svc_user@domain.com", "xxxx")   
      svc.ImpersonatedUserId = New ImpersonatedUserId(ConnectingIdType.SmtpAddress, "peter@domain.com")  
      Dim mail As New EmailMessage(svc)  
      mail.From = "peter@domain.com"  
      mail.Subject = "Test Peter svc user"  
      mail.Body = "Test Body"  
      mail.ToRecipients.Add("tom@domain.com")  
      mail.Save()  
      mail.SendAndSaveCopy()  
 Catch ex As Exception  
      MsgBox(ex.ToString)  
 End Try  

But with my newly created svc_user I kept getting the following error: "The request failed. The remote server returned an error: (401) Unauthorized". Enabling the traces on my service calls gave no extra info. After googling this error I found a lot of MSDN pages with a lot of different possible solutions, but all to no avail.
After a tip from a colleague I did what I should have done a lot sooner. Navigate to https://portal.office.com and tried logging in with the svc_user. I got nice clear message stating that my password had expired or that I should change my password on first usage. After doing this everything worked just fine and I felt a bit dumb for spending a couple of hours on this issue :-)

Getting started with SQL

If your looking for an interesting course to start learning SQL this might be it. Courtesy of codecademy
https://www.codecademy.com/courses/learn-sql

Edit 06/06/2016

another nice resource is

https://www.hackerrank.com/domains/sql/select

SQL Server triggers, how to only log rows where values have actually changed.

In SQL Server inside a trigger if you want to check if a row has actually changed, you would think you could use COLUMNS_UPDATED but this only returns which columns are included in an update statement. It doesn't check if the actual values changed. A nice solution I've found is to make use of the SQL EXCEPT operator and a CTE. Here's a sample that you may find useful.

 ...  
 FOR UPDATE  
 AS  
 declare @rijen int  
 ;with ChangedData as   
      (  
      select deleted.column1, deleted.column2, deleted.column3, deleted.column4 from deleted  
           except  
      select inserted.column1, inserted.column2, inserted.column3, inserted.column4 rom inserted  
      )  
 select @rijen = count(*) from ChangedData  
 if @rijen > 0  
 begin  
 ...  
 end  

Mysterious error: "System.OutOfMemoryException: Error creating window handle" in a MDI environment

After setting up log4net, which I can strongly recommend. I noticed that one, and only one of my users kept getting the following error message:

 System.OutOfMemoryException: Error creating window handle. ---> System.NullReferenceException: Object reference not set to an instance of an object.  
   at System.Windows.Forms.NativeWindow.WindowClass.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)  
   at System.Windows.Forms.UnsafeNativeMethods.IntCreateWindowEx(Int32 dwExStyle, String lpszClassName, String lpszWindowName, Int32 style, Int32 x, Int32 y, Int32 width, Int32 height, HandleRef hWndParent, HandleRef hMenu, HandleRef hInst, Object pvParam) ...  

After spending hours of investigating and trying to reproduce the error, which I didn't succeed in, I finally found a solution for the problem. After changing the following piece of code

 Dim objBeheer As New FrmMyForm  
 objBeheer.MdiParent = Me  
 objBeheer.WindowState = FormWindowState.Maximized  
 objBeheer.Show()  

to the following piece of code

 Dim objBeheer As New FrmMyForm  
 objBeheer.MdiParent = Me  
 objBeheer.Show()  
 objBeheer.WindowState = FormWindowState.Maximized  

the problem was solved. The only thing changed is where the windowstate is set. It still baffles me why only one user got this error. All other users never got this error and they're all working in the same Citrix environment. But in the end of the day the error is solved and there are less errors being logged in my log4net logs :-)

Developing a VB.Net VSIX package System.InvalidCastException: [A] cannot be cast to [B] error

Hi, the last months I've been developing some VSIX packages for VB.NET. Yesterday I started getting a weird error: 

"Type A originates from 'xxxxx, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' in the context  'LoadNeither' at location 'C:\Users\xxxxx\AppData\Local\Microsoft\VisualStudio\10.0Exp\ProjectAssemblies\-jqp5pix01\xxxxx.dll'. 

Type B originates from 'xxxxx, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' in the context 'LoadFrom' at location 'C:\Users\xxxxx\AppData\Local\Microsoft\VisualStudio\10.0Exp\Extensions\...\1.0\xxxxx.dll'."

In my case this problem occurred because both the package and the solution opened in the experimental instance use the same dll. In my package the dll reference was set to copy local. This causes the dll to be copied to: 'C:\Users\xxxxx\AppData\Local\Microsoft\VisualStudio\10.0Exp\Extensions\...\xxxxx.dll'

The solution I open in the experimental instance creates all the folders under: 'C:\Users\xxxxx\AppData\Local\Microsoft\VisualStudio\10.0Exp\ProjectAssemblies\'  and copies all the dll's needed for the solution to this location. The dll's however are created with a date created of when they were copied. This causes the debugger to think that it are two different dll versions. Setting the copy local property of the dll to false can solve your problem.

Use VB.NET to get the from part of SQL query PART 2

After posting the function to get the from part of a SQL query, the function has evolved :-)

If you for example would like to do a count(0) on the from part returned by the function, group and order by clauses should be stripped if present. I've added this functionality as an optional parameter. I've also updated the function to split the source query on union, intersect and except also taking into account that these keywords can be included in the query as an alias. Another important change is that square brackets [ ] are also taken into account as alias indicators.

Below is the new function, a bit more complicated as the first function and it probably needs some tidying up, but that's for when I've got some more time. I've also included an (absolutely ridiculous) source test query and the result the function produces

 Public Function ProfelGetFromPartOfAQuery(SourceQuery As String, Optional StripAfterWhere As Boolean = True) As List(Of String)  
       Dim nestLevel As Integer = 0  
       Dim blnQuoteOpen As Boolean = False  
       Dim blnBracketOpen As Boolean = False  
       Dim lstReturn As New List(Of String)  
       Dim iIndex As Integer = 0  
       Dim strWord As String = ""  
       Dim queries As New List(Of String)  
       Dim tmpQuery As String = SourceQuery  
       'First split the query on certain keywords, union, execpt, intersect  
       'This can't be done with a simple split because the keywords could also be used as aliases  
       'The string is split on except and intersect but the function doesn't tell which from part is  
       'from the except or intersect, this falls outside the scope of this function  
       For Each c As Char In SourceQuery  
         iIndex += 1  
         If c = "'"c And blnQuoteOpen = False Then  
           blnQuoteOpen = True  
         Else  
           If c = "'"c And blnQuoteOpen = True Then  
             blnQuoteOpen = False  
           End If  
         End If  
         If c = "["c And blnBracketOpen = False Then  
           blnBracketOpen = True  
         Else  
           If c = "]"c And blnBracketOpen = True Then  
             blnBracketOpen = False  
           End If  
         End If  
         If c = "(" AndAlso blnQuoteOpen = False AndAlso blnBracketOpen = False Then  
           nestLevel += 1  
         End If  
         If c = ")" AndAlso blnQuoteOpen = False AndAlso blnBracketOpen = False Then  
           nestLevel -= 1  
         End If  
         If c = " "c Then  
           strWord = ""  
         Else  
           strWord &= c  
         End If  
         If nestLevel = 0 Then  
           Select Case strWord  
             Case "union", "except", "intersect"  
               queries.Add(Mid(tmpQuery, 1, iIndex - strWord.Length))  
               tmpQuery = Mid(tmpQuery, iIndex + 1, tmpQuery.Length - iIndex)  
               iIndex = 0  
           End Select  
         End If  
       Next  
       If queries.Count = 0 Then  
         queries.Add(SourceQuery)  
       Else  
         queries.Add(tmpQuery)  
       End If  
       'Analyze the seperate queries  
       For Each query As String In queries  
         iIndex = 0  
         strWord = ""  
         blnQuoteOpen = False  
         blnBracketOpen = False  
         For Each c As Char In query  
           iIndex += 1  
           If c = "'"c And blnQuoteOpen = False Then  
             blnQuoteOpen = True  
           Else  
             If c = "'"c And blnQuoteOpen = True Then  
               blnQuoteOpen = False  
             End If  
           End If  
           If c = "["c And blnBracketOpen = False Then  
             blnBracketOpen = True  
           Else  
             If c = "]"c And blnBracketOpen = True Then  
               blnBracketOpen = False  
             End If  
           End If  
           If c = "(" AndAlso blnQuoteOpen = False AndAlso blnBracketOpen = False Then  
             nestLevel += 1  
           End If  
           If c = ")" AndAlso blnQuoteOpen = False AndAlso blnBracketOpen = False Then  
             nestLevel -= 1  
           End If  
           If c = " "c Then  
             strWord = ""  
           Else  
             strWord &= c  
           End If  
           If strWord.ToLower = "from" And nestLevel = 0 Then  
             Dim strtmp As String = Mid(query, iIndex - 3, query.Length - (iIndex - 4)).ToLower  
             'If you want to strip eveything after the where set StripAfterWhere = true  
             'this can be used if you want to do a count(0) on the from part returned  
             If StripAfterWhere = True Then  
               Dim iGroup As Integer = ProfelGetPartOfAQuery("group", strtmp)  
               If iGroup <> 0 Then  
                 lstReturn.Add(Mid(strtmp, 1, iGroup))  
               Else  
                 Dim iOrderBy As Integer = ProfelGetPartOfAQuery("order", strtmp)  
                 If iOrderBy <> 0 Then  
                   lstReturn.Add(Mid(strtmp, 1, iOrderBy))  
                 Else  
                   lstReturn.Add(strtmp)  
                 End If  
               End If  
             Else  
               lstReturn.Add(strtmp)  
             End If  
             Exit For  
           End If  
         Next  
       Next  
       Return lstReturn  
     End Function  
     Public Function ProfelGetPartOfAQuery(part As String, SourceQuery As String) As Integer  
       Dim nestLevel As Integer = 0  
       Dim blnQuoteOpen As Boolean = False  
       Dim blnBracketOpen As Boolean = False  
       Dim lstReturn As New Integer  
       Dim iIndex As Integer = 0  
       Dim strWord As String = ""  
       For Each c As Char In SourceQuery  
         iIndex += 1  
         If c = "'"c And blnQuoteOpen = False Then  
           blnQuoteOpen = True  
         Else  
           If c = "'"c And blnQuoteOpen = True Then  
             blnQuoteOpen = False  
           End If  
         End If  
         If c = "["c And blnBracketOpen = False Then  
           blnBracketOpen = True  
         Else  
           If c = "]"c And blnBracketOpen = True Then  
             blnBracketOpen = False  
           End If  
         End If  
         If c = "(" AndAlso blnQuoteOpen = False AndAlso blnBracketOpen = False Then  
           nestLevel += 1  
         End If  
         If c = ")" AndAlso blnQuoteOpen = False AndAlso blnBracketOpen = False Then  
           nestLevel -= 1  
         End If  
         If c = " "c Then  
           strWord = ""  
         Else  
           strWord &= c  
         End If  
         If strWord.ToLower = part And nestLevel = 0 Then  
           lstReturn = iIndex - (part.Length)  
           Exit For  
         End If  
       Next  
       Return lstReturn  
     End Function  

The (absolutely ridiculous) query

 select replace(replace(eb001.kltnr, '(',' '),']','')  
 from eb001  
     left outer join eb008 as [''''group)(]]'] on isnull(eb001.kltnr,'') = [''''group)(]]'].kltnr  
     left outer join ea007 on eb001.taalc = ea007.taalc  
     left outer join ea012 on eb001.pscdx = ea012.pscdx      
  where ([''''group)(]]'].bedrf = '03' or [''''group)(]]'].bedrf = '10' or eb001.intco = 'j'  
 or (select top 1 slbkh as 'group by' from eb001 group by slbkh) = 'BENTHIN')  
 group by replace(replace(eb001.kltnr, '(',' '),']','')  
 order by (select distinct replace(replace(eb001.kltnr, '(',' '),']','')  
 from eb001  
     left outer join eb008 as [''''group)(]]'] on isnull(eb001.kltnr,'') = [''''group)(]]'].kltnr  
     left outer join ea007 on eb001.taalc = ea007.taalc  
     left outer join ea012 on eb001.pscdx = ea012.pscdx      
  where ([''''group)(]]'].bedrf = '03' or [''''group)(]]'].bedrf = '10' or eb001.intco = 'j'or (select top 1 slbkh as 'group by' from eb001 group by slbkh) = 'BENTHIN'))  

The result

 from eb001  
      left outer join eb008 as [''''group)(]]'] on isnull(eb001.kltnr,'') = [''''group)(]]'].kltnr  
      left outer join ea007 on eb001.taalc = ea007.taalc  
      left outer join ea012 on eb001.pscdx = ea012.pscdx      
  where ([''''group)(]]'].bedrf = '03' or [''''group)(]]'].bedrf = '10' or eb001.intco = 'j'  
  or (select top 1 slbkh as 'group by' from eb001 group by slbkh) = 'benthin')  

Use VB.NET to get the from part of SQL query

Hello, long time no post...

this is a function I've written to extract the from part of a SQL query. When you first get this assignment it sounds simple. But once you start thinking about it, it becomes more and more complex. How do you handle sub-queries in the select and where clause. How do you handle unions, ...

The function I've written works by calculating the nesting level of your query. This is done by adding 1 to a counter if you encounter a ( and subtracting 1 from the counter if you find a ). If you then find the word "from" and the nesting level = 0 you've got the from part. The next problem is what if the query contains an extra ( or )
This can happen for example with the following query: select replace(field,'(',' '),* from table
So this simple query would ruin my whole function :-)

I solved this by only increasing and decreasing the nesting level if there are no quotes open.

I've included two functions, one which can handles union and returns the from part of every separate query joined by a union. The first function can't cope with a union, but you can use this if you know there won't be a union. If anyone finds other queries which these functions can't handle, please let me know.

 Public Function GetFromPartOfAQuery(SourceQuery As String) As String  
       Dim nestLevel As Integer = 0  
       Dim blnQuoteOpen As Boolean = False  
       Dim strReturn As String = ""  
       Dim iIndex As Integer = 0  
       Dim strWord As String = ""  
         For Each c As Char In query  
           iIndex += 1  
           If c = "'"c And blnQuoteOpen = False Then  
             blnQuoteOpen = True  
           Else  
             If c = "'"c And blnQuoteOpen = True Then  
               blnQuoteOpen = False  
             End If  
           End If  
           If c = "(" AndAlso blnQuoteOpen = False Then  
             nestLevel += 1  
           End If  
           If c = ")" AndAlso blnQuoteOpen = False Then  
             nestLevel -= 1  
           End If  
           If c = " "c Then  
             strWord = ""  
           Else  
             strWord &= c  
           End If  
           If strWord.ToLower = "from" And nestLevel = 0 Then  
             strReturn = Mid(query, iIndex - 3, query.Length - (iIndex - 4))  
             Exit For  
           End If  
         Next  
       Return lstReturn  
     End Function  

 Public Function GetFromPartOfAQuery(SourceQuery As String) As List(Of String)  
       Dim nestLevel As Integer = 0  
       Dim blnQuoteOpen As Boolean = False  
       Dim lstReturn As New List(Of String)  
       Dim iIndex As Integer = 0  
       Dim strWord As String = ""  
       Dim queries As String() = SourceQuery.ToLower.Split(New String() {"union"}, StringSplitOptions.None)  
       For Each query As String In queries  
         iIndex = 0  
         strWord = ""
         blnQuoteOpen = False  
         For Each c As Char In query  
           iIndex += 1  
           If c = "'"c And blnQuoteOpen = False Then  
             blnQuoteOpen = True  
           Else  
             If c = "'"c And blnQuoteOpen = True Then  
               blnQuoteOpen = False  
             End If  
           End If  
           If c = "(" AndAlso blnQuoteOpen = False Then  
             nestLevel += 1  
           End If  
           If c = ")" AndAlso blnQuoteOpen = False Then  
             nestLevel -= 1  
           End If  
           If c = " "c Then  
             strWord = ""  
           Else  
             strWord &= c  
           End If  
           If strWord.ToLower = "from" And nestLevel = 0 Then  
             lstReturn.Add(Mid(query, iIndex - 3, query.Length - (iIndex - 4)))  
             Exit For  
           End If  
         Next  
       Next  
       Return lstReturn  
     End Function