Visio for Enterprise Architects (2005) install with VS 2008

Posted: November 26, 2009 | Author: | Filed under: Visual Studio | Leave a comment

Here is a link to a solution to solve the problem with installation issue for Visio EA 2005 without installation of VS 2005.

http://www.gravitycube.net/blog/post/Visual-Studio-2008-and-Visio-for-Enterprise-Architects.aspx

How to install ISO file without burning CD/DVD first?

Posted: November 26, 2009 | Author: | Filed under: My IT tools | Leave a comment
It is holiday break at home and I have time to install some software I am interested to play with. I don’t want to burn all my MSDN software to discs so I search for a solution that is free and easy to use. I came to this product: Virtual Clone Drive, a free program from Slysoft.
http://www.slysoft.com/en/virtual-clonedrive.html
 I am waiting for the ISO download now and I’ll post back to report how I feel about it.
 
Edit:
I have installed VS 2008 in iso format with this Virtual Clone Drive by simply clicking on the iso file and it worked pretty well.

Avoid Aggregate Distinct in T-SQl (Sql Server 2008)

Posted: November 25, 2009 | Author: | Filed under: SQL Server 2008 | Leave a comment
Here is a discussion about the hamful effect of Aggregate Distinct syntax in SQL query (SQL Server 2008).
http://blogs.msdn.com/sqlqueryprocessing/archive/2008/09/22/distinct-aggregation-considered-harmful.aspx
 
The proposed solution is to use two steps to get the aggregates.
1. Get a partial aggregation derived table; 2. calculate the final aggrgation from the derived table.
 

;with PartialSums as (

SELECT

COUNT(*) AS countstarpartialcount FROM myTest  GROUP BY pvalue

)

SELECT

SUM(countstarpartialcount) AS countstar,

SUM(1) AS countproductkeys FROM partialsums

 

–" … Note that in sum(1), the 1 is actually a constant number value, not a column reference."

Tips,Web link: Tricks, and Advice from the SQL Server Query Processing Team

Posted: November 25, 2009 | Author: | Filed under: SQL Server | Leave a comment

http://blogs.msdn.com/sqlqueryprocessing/archive/2006/09/26/771411.aspx

Also I need to bookmark the SQLCAT site for some Top 10 lists for review issues I am working on recently:

http://sqlcat.com/

 

 

 

Sort a column by ignoring the leading English articles

Posted: November 18, 2009 | Author: | Filed under: SQL Server | Leave a comment
Here is an example to sort a column by ignoring the leading articles:

declare @t table (id int, fieldname varchar(2000))

insert

into @t values (1, ‘A headache In Iraq’)

insert

into @t values (2, ‘A Fighting Man’)

insert

into @t values (3, ‘The Region Map’)

insert

into @t values (13, ‘the Joyful UN’)

insert

into @t values (14, ‘an Apple Tree’)

insert

into @t values (15, ‘The butiful land’)

SELECT

*

–, SUBSTRING(fieldname, charindex(‘ ‘,fieldname), len(fieldname))

FROM

@t

ORDER

BY CASE WHEN substring(fieldname, 0, charindex(‘ ‘, fieldname))=‘A’

OR

substring(fieldname, 0, charindex(‘ ‘, fieldname))=‘An’

OR

substring(fieldname, 0, charindex(‘ ‘, fieldname))=‘The’

THEN

substring(fieldname, charindex(‘ ‘, fieldname), len(fieldname))

ELSE

fieldname END

UNPIVOT and PIVOT –An Example

Posted: November 9, 2009 | Author: | Filed under: SQL Server 2005 | Leave a comment

declare @t table (id int, pulse1 int, pulse2 int, pulse3 int, pulse4 int, pulse5 int)

insert

into @t values (1 , 76, 89 , null , null , null)

insert into @t values (2 , 89 , 0, 99, 100, null)

insert

into @t values (3 , 0 , 0 , 0 , 110, null)

insert

into @t values (4 , 0 , 0 , 0, 0 , 130)

insert

into @t values (5 , 0 , 0 , 0 , 110, 0)

insert

into @t values (6 , 78, 0 , 0 , 109 , 0)

;

with mycte

AS

(select id, myValue, myPulse,

ROW_NUMBER

() OVER (PARTITION BY id ORDER BY myPulse DESC) rn FROM (select id,

[pulse1]

,[pulse2],[pulse3],[pulse4],[pulse5] from @t)

src

UNPIVOT

(

myValue For myPulse IN ([pulse1],[pulse2],[pulse3],[pulse4],[pulse5])) unpvt

)

SELECT

id, [pulse1],[pulse2],[pulse3],[pulse4],[pulse5]

FROm

(select id, myValue, myPulse FROM mycte

WHERE

ID IN (select id FROM mycte

WHERE

rn=1 AND myValue<>0)) src

PIVOT

(MAX(myValue) FOR

myPulse

IN ([pulse1],[pulse2],[pulse3],[pulse4],[pulse5])) pvt

Show nested Repeater inside a Gridview on Selection

Posted: November 4, 2009 | Author: | Filed under: ASP.NET 2 | Leave a comment
Here is a way to show a nested repeater inside a Gridview.

Protected Sub GridView1_OnSelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.SelectedIndexChanged

For Each row As GridViewRow In GridView1.Rows

row.FindControl(

"DefinitionRepeater").Visible = False

Next

Dim myRepeater As Repeater = DirectCast(GridView1.SelectedRow.FindControl("DefinitionRepeater"), Repeater)

myRepeater.Visible =

True

End Sub

The markup part (nest repeater inside a Gridview)

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

DataSourceID="SqlDataSource1" OnSelectedIndexChanged="GridView1_OnSelectedIndexChanged">

<Columns>

<asp:CommandField ShowSelectButton="True" />

<asp:BoundField DataField="countryid" HeaderText="countryid"

SortExpression="countryid" />

<asp:TemplateField HeaderText="CountryName" SortExpression="CountryName">

<ItemTemplate>

<asp:Label ID="Label1" runat="server" Text=’<%# Eval("CountryName") %>‘></asp:Label>

<asp:Label ID="lblcountryid" runat="server" Text=’<%# Eval("countryid") %>‘ Visible="false"></asp:Label></div>

<asp:Repeater ID="DefinitionRepeater" runat="server" DataSourceID="SqlDataSource2" Visible="false">

<HeaderTemplate>

</HeaderTemplate>

<ItemTemplate>

<b><%# "&nbsp;&nbsp;&nbsp;&nbsp;" + Eval("cityname")%></b>

<br /><br />

</ItemTemplate>

<FooterTemplate>

</FooterTemplate>

</asp:Repeater>

<asp:SqlDataSource ID="SqlDataSource2" runat="server"

ConnectionString="<%$ ConnectionStrings:test_for_forumConnectionString %>"

SelectCommand="SELECT cityid, cityname, description FROM [Table2] WHERE [countryid]=@countryid">

<SelectParameters>

<asp:ControlParameter ControlID="lblcountryid" Name="countryid" /></SelectParameters>

</asp:SqlDataSource>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:test_for_forumConnectionString %>"

SelectCommand="SELECT [countryid], [CountryName] FROM [Table1]">

</asp:SqlDataSource>