SQL Question

Question 1:

create table trans

(

[TID] INT NOT NULL IDENTITY(1,1),

[NO] varchar(10),

[Time] DATETIME DEFAULT('1900/01/01'),

[TAmt] MONEY

)

 

INSERT INTO trans([TID],[NO],[Time],[TAmt]) VALUES('0124-98',DEFAULT,1000)

INSERT INTO trans([TID],[NO],[Time],[TAmt]) VALUES('0124-98',GETDATE(),1000)

INSERT INTO trans([TID],[NO],[Time],[TAmt]) VALUES(SCOPE_IDENTITY(),'0124-98',DEFAULT,1000)

INSERT INTO trans([TID],[NO],[Time],[TAmt]) VALUES(SCOPE_IDENTITY(),'0124-98',GETDATE(),1000)

 

Question 2:

select round(689.89,-1,1)

 

Question 3:

create function tomoro

returns datetime

as

begin

        declare @td datetime

        set @td = getdate()

        set @td = dateadd(DD, datediff(DD, 0, @td), 0)

        return dateadd(DD, 1, @td)

end

 

 

Question 4:

SET ARITHABORT OFF

SET ANSI_WARNINGS OFF

select 200/0

 

 

Question 5:

create table champs

(

[team] nvarchar(10),

[title] int

)

 

insert into champs values('a',27)

insert into champs values('b',7)

insert into champs values('c',10)

insert into champs values('aa',5)

insert into champs values('aa5',5)

insert into champs values('aa4',5)

insert into champs values('aa3',5)

insert into champs values('aa2',5)

insert into champs values('bb',3)

insert into champs values('bb4',4)

insert into champs values('cc',3)

 

select [team], [title], rank() over(order by [title] desc) from champs

select [team], [title], dense_rank() over(order by [title] desc) from champs

 

Question 6:

select round(123.89,0)

 

Question 7:

SELECT * FROM sys.dm_db_missing_index_details

select object_id, name, index_id, type_desc from sys.indexes where type_desc = 'SPATIAL'

 

Calender Extender [AjaxToolkit] enable month selection and apply rules b/w tow cal extenders

 

 

<asp:Content ID="Content3" ContentPlaceHolderID="ContentContent" runat="server">

 

        <script type="text/javascript">

 

 

            var cal1;

            var cal2;

 

 

            function pageLoad() {

                cal1 = $find("calendar1");

                cal2 = $find("calendar2");

 

                    //modify cal ext delegate to support month selection and stop further date selection

                modifyCalDelegates(cal1);

                //modifyCalDelegates(cal2);

            }

 

 

            function modifyCalDelegates(cal) {

                //we need to modify the original delegate of the month cell.

                cal._cell$delegates = {

                    mouseover: Function.createDelegate(cal, cal._cell_onmouseover),

                    mouseout: Function.createDelegate(cal, cal._cell_onmouseout),

 

 

                    click: Function.createDelegate(cal, function(e) {

                        /// <summary>

                        /// Handles the click event of a cell

                        /// </summary>

                        /// <param name="e" type="Sys.UI.DomEvent">The arguments for the event</param>

 

 

                        e.stopPropagation();

                        e.preventDefault();

 

 

                        if (!cal._enabled) return;

 

 

                        var target = e.target;

                        var visibleDate = cal._getEffectiveVisibleDate();

                        Sys.UI.DomElement.removeCssClass(target.parentNode, "ajax__calendar_hover");

                        switch (target.mode) {

                            case "prev":

                            case "next":

                                cal._switchMonth(target.date);

                                break;

                            case "title":

                                switch (cal._mode) {

                                    case "days": cal._switchMode("months"); break;

                                    case "months": cal._switchMode("years"); break;

                                }

                                break;

                            case "month":

                                //if the mode is month, then stop switching to day mode.

                                if (target.month == visibleDate.getMonth()) {

                                    //this._switchMode("days");

                                } else {

                                    cal._visibleDate = target.date;

                                    //this._switchMode("days");

                                }

                                cal.set_selectedDate(target.date);

                                cal._switchMonth(target.date);

                                cal._blur.post(true);

                                cal.raiseDateSelectionChanged();

                                break;

                            case "year":

                                if (target.date.getFullYear() == visibleDate.getFullYear()) {

                                    cal._switchMode("months");

                                } else {

                                    cal._visibleDate = target.date;

                                    cal._switchMode("months");

                                }

                                break;

 

 

                            //                case "day":

                            //                    this.set_selectedDate(target.date);

                            //                    this._switchMonth(target.date);

                            //                    this._blur.post(true);

                            //                    this.raiseDateSelectionChanged();

                            //                    break;

                            case "today":

                                cal.set_selectedDate(target.date);

                                cal._switchMonth(target.date);

                                cal._blur.post(true);

                                cal.raiseDateSelectionChanged();

                                break;

                        }

 

 

                    })

                }

 

 

            }

 

 

            function onCalendarShown(sender, args) {

                //set the default mode to month

                sender._switchMode("months", true);

                changeCellHandlers(cal1);

            }

 

 

 

 

            function changeCellHandlers(cal) {

 

 

                if (cal._monthsBody) {

 

 

                    //remove the old handler of each month body.

                    for (var i = 0; i < cal._monthsBody.rows.length; i++) {

                        var row = cal._monthsBody.rows[i];

                        for (var j = 0; j < row.cells.length; j++) {

                            $common.removeHandlers(row.cells[j].firstChild, cal._cell$delegates);

                        }

                    }

                    //add the new handler of each month body.

                    for (var i = 0; i < cal._monthsBody.rows.length; i++) {

                        var row = cal._monthsBody.rows[i];

                        for (var j = 0; j < row.cells.length; j++) {

                            $addHandlers(row.cells[j].firstChild, cal._cell$delegates);

                        }

                    }

 

 

                }

            }

 

 

            function onCalendarHidden(sender, args) {

 

 

                if (sender.get_selectedDate()) {

                    if (cal1.get_selectedDate() && cal2.get_selectedDate() && cal1.get_selectedDate() > cal2.get_selectedDate()) {

                        alert('The "From" Date should smaller than the "To" Date, please reselect!');

                        sender.show();

                        return;

                    }

                    //get the final date

                    var finalDate = new Date(sender.get_selectedDate());

                    var selectedMonth = finalDate.getMonth();

                    finalDate.setDate(1);

                    if (sender == cal1) {

                        // set the calender2's default date 10^th day of the next month as per calender1

                        finalDate.setMonth(selectedMonth + 1);

                        finalDate.setDate(finalDate.getDate() + 9);

                        finalDate = new Date(finalDate);

                    }

                    //set the date to the TextBox

                    cal2.get_element().value = finalDate.format(cal2._format);

                }

            }

 

 

        </script>

 

 

        <div>

            TP :

            <asp:TextBox ID="TextBox1" runat="server" AutoCompleteType="Disabled"></asp:TextBox>

            <ajaxToolkit:CalendarExtender ID="CalendarExtender1" BehaviorID="calendar1" runat="server"

                Enabled="True" Format="yyyyMM" TargetControlID="TextBox1" OnClientShown="onCalendarShown"

                OnClientHidden="onCalendarHidden">

            </ajaxToolkit:CalendarExtender>

            SD :

            <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>

            <ajaxToolkit:CalendarExtender ID="CalendarExtender2" BehaviorID="calendar2" runat="server"

                Enabled="True" Format="yyyy/MM/dd" TargetControlID="TextBox2" >

            </ajaxToolkit:CalendarExtender>

        </div>

</asp:Content>

 

WCF Service dead end exception

{System.Collections.EmptyReadOnlyDictionaryInternal}

 

System.ExecutionEngineException was unhandled

  Message="Exception of type 'System.ExecutionEngineException' was thrown."

  InnerException:

 

 

Methods to execute/run SSIS packages

Running SSIS on remote server is supposed to be a trick one for which option are given below with details

 

http://blogs.msdn.com/b/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

 

Running SSIS package on a remote machine à http://msdn.microsoft.com/en-us/library/ms403355.aspx

 

Javascript validate date time [safest way]

<script type="text/javascript" language="javascript">

 

        function isLeapYear() {

            return (year % 4 != 0 ? false :

                ( year % 100 != 0? true:

                ( year % 1000 != 0? false : true)));

        }

 

        function isValidDate(date_string) {

            var days = [0,31,28,31,30,31,30,31,31,30,31,30,31];

            var year, month, day, date_parts = null;

            var rtrn = false;

            var format = 'mm/dd/yyyy';

            var decisionTree = {

                'm/d/y':{

                    're':/^(\d{1,2})[./-](\d{1,2})[./-](\d{2}|\d{4})$/,

                    'month': 1,'day': 2, year: 3

                },

                'mm/dd/yy':{

                    're':/^(\d{1,2})[./-](\d{1,2})[./-](\d{2})$/,

                    'month': 1,'day': 2, year: 3

                },

                'mm/dd/yyyy':{

                    're':/^(\d{1,2})[./-](\d{1,2})[./-](\d{4})$/,

                    'month': 1,'day': 2, year: 3

                },

                'y/m/d':{

                    're':/^(\d{2}|\d{4})[./-](\d{1,2})[./-](\d{1,2})$/,

                    'month': 2,'day': 3, year: 1

                },

                'yy/mm/dd':{

                    're':/^(\d{1,2})[./-](\d{1,2})[./-](\d{1,2})$/,

                    'month': 2,'day': 3, year: 1

                },

                'yyyy/mm/dd':{

                    're':/^(\d{4})[./-](\d{1,2})[./-](\d{1,2})$/,

                    'month': 2,'day': 3, year: 1

                }

            };

            var test = decisionTree[format];

            if (test) {

                date_parts = date_string.match(test.re);

                if (date_parts) {

                    year = date_parts[test.year] * 1;

                    month = date_parts[test.month] * 1;

                    day = date_parts[test.day] * 1;

 

                    test = (month == 2 &&

                            isLeapYear() &&

                            29 ||

                            days[month] || 0);

 

                    rtrn = 1 <= day && day <= test;

                }

            }

            return rtrn;

        }

    </script>

 

 

Javascript Popup with dataentry

HTML Markup :

 

<asp:Button ID="btnCOBDC" runat="server" Text="COBD Close" OnClick="COBD_Click" OnClientClick="return show_COBDprompt();" />

<asp:HiddenField ID="hdnCOBDValue" runat="server" />

<asp:HiddenField ID="hdnExpectedCOBD" runat="server" />

 

 

Javascript to Handle prompt and input value [limitation : only one input value] [for multiple value input use div]

 

<script type="text/javascript" language="javascript">

 

        function isLeapYear() {

            return (year % 4 != 0 ? false :

                ( year % 100 != 0? true:

                ( year % 1000 != 0? false : true)));

        }

 

        function isValidDate(date_string) {

            var days = [0,31,28,31,30,31,30,31,31,30,31,30,31];

            var year, month, day, date_parts = null;

            var rtrn = false;

            var format = 'mm/dd/yyyy';

            var decisionTree = {

                'm/d/y':{

                    're':/^(\d{1,2})[./-](\d{1,2})[./-](\d{2}|\d{4})$/,

                    'month': 1,'day': 2, year: 3

                },

                'mm/dd/yy':{

                    're':/^(\d{1,2})[./-](\d{1,2})[./-](\d{2})$/,

                    'month': 1,'day': 2, year: 3

                },

                'mm/dd/yyyy':{

                    're':/^(\d{1,2})[./-](\d{1,2})[./-](\d{4})$/,

                    'month': 1,'day': 2, year: 3

                },

                'y/m/d':{

                    're':/^(\d{2}|\d{4})[./-](\d{1,2})[./-](\d{1,2})$/,

                    'month': 2,'day': 3, year: 1

                },

                'yy/mm/dd':{

                    're':/^(\d{1,2})[./-](\d{1,2})[./-](\d{1,2})$/,

                    'month': 2,'day': 3, year: 1

                },

                'yyyy/mm/dd':{

                    're':/^(\d{4})[./-](\d{1,2})[./-](\d{1,2})$/,

                    'month': 2,'day': 3, year: 1

                }

            };

            var test = decisionTree[format];

            if (test) {

                date_parts = date_string.match(test.re);

                if (date_parts) {

                    year = date_parts[test.year] * 1;

                    month = date_parts[test.month] * 1;

                    day = date_parts[test.day] * 1;

 

                    test = (month == 2 &&

                            isLeapYear() &&

                            29 ||

                            days[month] || 0);

 

                    rtrn = 1 <= day && day <= test;

                }

            }

            return rtrn;

        }

 

        function show_COBDprompt() {

            var expectedCOBD = document.getElementById('hdnExpectedCOBD').value;

            var cobd = prompt("Please enter COB Date [mm/dd/yyyy] :", expectedCOBD);

            if (cobd != null && cobd != "") {

                if(!isValidDate(cobd)){ alert('Please enter the date in correct format i.e. mm/dd/yyyy'); return false; }

                document.getElementById('hdnCOBDValue').value = cobd;

                return true;

            }

            return false;

        }

    </script>

 

Microsoft Chart Tool 3.5 guide

To display a chart similar to the one below

 

 

Three easy steps to do the chart:

1. Create a DataTable with columns equal to number of bars needed for each week and a column for week itself. Populate table data.
2. Create a chart with series based on the datatable columns as below

for (int i = 1; i < chartData.Columns.Count; i++)

        {

            if (isFirstColumn)

            {

                Chart1.Series[0].XValueMember = chartData.Columns[0].ToString();

                Chart1.Series[0].YValueMembers = chartData.Columns[i].ToString();

                isFirstColumn = false;

            }

            else

            {

                Chart1.Series.Add(Convert.ToString(i));

                Chart1.Series[Convert.ToString(i)].XValueMember = chartData.Columns[0].ToString();

                Chart1.Series[Convert.ToString(i)].YValueMembers = chartData.Columns[i].ToString();

            }

        }

3. Your are done, just bind the chart with the datatable

 

 

Reference : http://www.skill-guru.com/blog/2009/11/29/advanced-features-of-net-chart-control/