Loosing records on left outer join in T-SQL Microsoft SQL Server 2008

If you do a left outer join and you loose records of the “left” table, this might be caused by the where clause:

This query will result in all records of table Production.HourGroup, only Production.HourGroup rows that have a correspoding Production.SortPerHour and on Production.SortPerHour.Sortday ‘2009-04-21’ are counted.

select
u.HourGroup   ,
sum(isnull(s.Amount,0))
from
Production.HourGroup u
left outer join
Production.SortPerHour s on u.HourGroup = s.HourGroup and s.Sortday =  '2009-04-21'
group by u.HourGroup
order by u.HourGroup


But the query below will not result in all records of table Production.HourGroup, because of the where clause.

The where clause is run on the left outer join result. So only the records in table Production.HourGroup with a corresponding SortPerHour on Sortday ‘2009-01-21’ will be listed and counted.

select
u.HourGroup   ,
sum(isnull(s.Amount,0))
from
Production.HourGroup u
left outer join
Production.SortPerHour s on u.HourGroup = s.HourGroup
where
s.Sortday = '2009-04-21'
group by u.HourGroup
order by u.HourGroup

How to resolve collation conflicts in Microsoft SQL Server 2008 stored procedure

To resolve collation conflicts like:

Msg 468, Level 16, State 9, Line 135
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

You should surround the ‘=’ with collate database_default

select * from Product p
inner join Sales s
where p.Name collate database_default = s.ProductName collate database_default

Solution found at: http://blog.sqlauthority.com/2007/06/11/sql-server-cannot-resolve-collation-conflict-for-equal-to-operation/

Add a WebService reference to a Microsoft Office Excel sheet

> Start you’re webservice
> Start Microsoft Office Excel
> Show the Control Toolbox toolbar: > View > Toolbars > Control Toolbox
> Enter Design mode by clicking the “Design Mode” button on the Control Toolbox
> Add a button to you’re Excel sheet
> Double click the button, this will op en de Microsoft Visual Basic Editor
> In the Microsoft Visual Basic Editor click: Tools > Web Service Reference… >
> On the Microsoft Office 2003 Web Services Toolkit dialog click: “Web Service URL” and enter you’re Web Service URL, click Search
> In the Search Results area, check you’re Web Service and click Add

image

This will generate a class “clsws_Service” with methods foreach WebMethod of the WebService, but the constant “Private c_WSDL_URL As String” should be changed to “Public URL As String”, so you can set the URL dynamic like:

    Dim service As New clsws_Service
    Dim data As String
    service.URL = ActiveSheet.Cells(1, "A").Value
    data = service.wsm_GetOpsMail(ActiveSheet.Cells(2, "A").Value, ActiveSheet.Cells(3, "A").Value)

More information can be found on: http://www.brainbell.com/tutorials/ms-office/excel/Access_SOAP_Web_Services_From_Excel.htm

SketchPath XPath query tool

If you want to know the XPath query to an element of a XML file, but you’re new to XPath, you can use the Freeware tool: SketchPath to get show the XPath query. In mine case I wanted to update the “ConnectionString” property of a SSIS pacakge.

XPath query to use in NANT Task: /DTS:Executable/DTS:Configuration[1]/DTS:Property[2]

 image

You can download SketchPath at: http://pgfearo.googlepages.com/downloads

How to use the ReportViewer.Drillthrough event in ASP .NET 2.0

If you have created a Microsoft Reporting Server report with a drillthrough. The user can Navigate to another report by clicking a link. This will fire the ReportViewer.Drilltrhough event on a ReportViewer control in ASP .NET 2.0. The DrillthroughEventArgs contains the new ReportPath. With this information you can create navigate to the correct URL on you’re ASP .NET 2.0 website.

        protected void ReportViewer1_Drillthrough(object sender, Microsoft.Reporting.WebForms.DrillthroughEventArgs e)
{
Response.Redirect(string.Format("Test.aspx?reportPath={0}", e.ReportPath));
}

Find largest files and folders with freeware tools like JDiskReport and TreeSize

Find largest files and folders with freeware tools like JDiskReport and TreeSize

Download TreeSize Free at:
http://www.jam-software.com/freeware/TreeSizeFree.zip

image

Download JDiskReport at:
http://www.jgoodies.com/downloads/index.html

image

Found at: http://www.raymond.cc/blog/archives/2007/04/30/find-out-what-files-and-folders-are-taking-up-hard-drive-spaces/

Simplest way to detect session timeout in an asp .net 2.0 web application in C#

The simplest way to detect if a session timeout occurred in an asp .net 2.0 web application use Global.asax Session_Start and Page_Load event.
see: http://blogs.msdn.com/nikhiln/archive/2007/06/21/detecting-session-timeout-in-asp-net-2-0-web-applications.aspx

 

Using Session["xxx"] value to determine the session timeout: This is a "quick and dirty" hack that can be introduced into an application to figure out whether a timeout has occured. We need to do two things here.
First, in Global.asax, create your own GUID and put it in the session object,

    void

Session_Start(object sender, EventArgs e)
    {
       // Code that runs when a new session is started
       Session["CustomSessionId"] = Guid.NewGuid();
    }

Second
, BasePage.cs which would have inherited Page, in PageLoad() event, check whether the Session["CustomSessionId"] == null, if it IS null, it means that the session was timed-out and AspNet runtime cleared it out.

    if( Session["CustomSessionId"] == null)
    {
        Response.Redirect(
"TimeoutPage.htm");
    }

Convert dutch string to DateTime and DateTime to dutch String with TryParseExact in C#

To convert a dutch string to DateTime and a DateTime to a dutch string in C# use the class:

    public class DateTimeHelper
{
/// <summary>
/// Returns a DateTime object if conversion from dutch string succeeded.
/// Return DateTime.MinValue if conversion from dutch string failed.
/// </summary>
/// <param name="dateTime">The string to convert</param>
/// <returns>See description</returns>
public DateTime ConvertDutchStringToDateTime(string dateTime)
{
DateTime result = DateTime.MinValue;
bool conversionResult = DateTime.TryParseExact(dateTime, "dd-MM-yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out  result);
return result;
}
/// <summary>
/// Return a dutch datetime formatted string.
/// </summary>
/// <param name="dateTime">DateTime object to convert</param>
/// <returns>See description</returns>
public string ConvertDateTimeToDutchString(DateTime dateTime)
{
return dateTime.ToString("dd-MM-yyyy");
}
}


UnitTest class

    [TestFixture]
public class DateTimeHelperTest
{
[Test]
public void ConvertDutchStringToDateTime_Basic()
{
DateTimeHelper helper = new DateTimeHelper();
DateTime dateTime = helper.ConvertDutchStringToDateTime("12-01-2009");
Assert.That(dateTime, Is.EqualTo(new DateTime(2009,1,12)));
}
[Test]
public void ConvertDateTimeToDutchString_Basic()
{
DateTimeHelper helper = new DateTimeHelper();
string result = helper.ConvertDateTimeToDutchString(new DateTime(2009, 1, 12));
Assert.That(result, Is.EqualTo("12-01-2009"));
}
}