MS SQL | Backup remote database on local machine

Hi guys

At times we have to have a recent backup of the SQL database on a development machine to insure that our testing is false-proof prior the same is pushed to a production instance. As SSMS (SQL Server Management Studio) doesn’t allow remote backup functionality out of the box, we can try the following (applies to both home networks and windows domain networks)

From your local machine, setup a shared folder, giving full read/write access to the entity “Everyone”

Now, prior attempting below instructions, be sure of your privileges against the target database. Are you a sys admin? Does your database account have the rights to make a backup? If yes, go ahead

Now, start SSMS & proceed with the backup task

Depending upon the security constraints, you may disable the sharing of local folder immediately after the backup completion.

regards,

rajesh

MS SQL 2019 | Upgrade/Fresh Installation

Hi guys

My laptop has many software development components installed, say few of them are there from last many years (regardless whether I still need them) & such get me in to complicated situations (most of the times)

I’ve had Visual Studio 2013 Professional edition installed for last many years & recently I have switched to the community edition (which is as good as pro edition & costs nothing)

After upgrading my SQL Server 2017 (Developer Edition) on my home computer using 2019 ISO mounted media, I decided to upgrade the SQL Server 2017 on my laptop and it failed, miserably!

So I decided to do a fresh installation, that also failed. Luckily I came across the following thread:

https://techcommunity.microsoft.com/t5/sql-server-support/sql-server-2019-installation-error-an-error-occurred-for-a/ba-p/998033#

While, there could be more reasons for the failure to install/upgrade your existing SQL server, make sure whether you have “Microsoft SQL Server 2012 Native Client” already installed, if yes, remove it (Which might popup a warning stating the dependency of Local DB 2016 on the Native client, hence make sure that you know what you are doing)

Once the 2012 Native client uninstalled, try again to upgrade/fresh installation of SQL Server 2019.

You should be through!

regards,

rajesh

ASP.Net | Freeze GridView header row using java script & CSS

Hi guys

As I started developing a web application for our business, One of the toughest requirements from end users were to “freeze” the header row of data grid views. I left .Net development almost 11 years back and was struggling to catch up with the whole set of changes .Net has came up with.

I’ve scavenged through dozen’s of articles explaining different hacks to freeze/lock the header row of grid views & was surprised to see that Microsoft didn’t address this requirement throughout their .Net iterations. Few of the workarounds those I tried out were ONLY applicable to static set of data, not at all applicable to data grids those have more than three or four columns and failed while column contents exceeded certain number of characters.

Finally, after a number of days’ search & trying out various solutions I came across a post at https://www.aspsnippets.com/Articles/Dynamically-freeze-ASP.Net-Gridview-header-using-JavaScript.aspx

The post has a VS solution download (Dated 21st February 2011) which was giving me multiple reference errors while opened with VS 2019 Community edition. So I decided to open the .aspx and code behind pages manually and create a new solution using VS 2019

Although the java script does freeze the header rows (while no themes applied to the grid & has limited columns), the moment a theme applied to the grid, the header row alignment with the data columns go for a toast. You will able to see how it works with attached example that you can download from the provided link by the bottom of the post.

I’ve created a huge employee list with 20 columns, where few of the columns have varying data lengths (like employee name, department, section name & job positions) as an XML source and throughout the examples I will be using the same data source to demonstrate how each page adjusts the GridView with minor hacks using both java script and CSS styling.

Example one, no java script, no themes gridview

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="FreezeGridViewHeader.Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
                <Columns>
<asp:BoundField DataField="EmployeeNumber" HeaderText="Code" />
<asp:BoundField DataField="fullname" HeaderText="Name"  />
<asp:BoundField DataField="department" HeaderText="Department"  />
<asp:BoundField DataField="section" HeaderText="Section"  />
<asp:BoundField DataField="position" HeaderText="Position"  />
<asp:BoundField DataField="basicsalary" HeaderText="Salary"  />
<asp:BoundField DataField="other" HeaderText="other"  />
<asp:BoundField DataField="petrol" HeaderText="petrol"  />
<asp:BoundField DataField="mobile" HeaderText="mobile"  />
<asp:BoundField DataField="car" HeaderText="car"  />
<asp:BoundField DataField="transport" HeaderText="transport"  />
<asp:BoundField DataField="sign" HeaderText="sign"  />
<asp:BoundField DataField="house" HeaderText="house"  />
<asp:BoundField DataField="acco" HeaderText="acco"  />
<asp:BoundField DataField="driving" HeaderText="driving"  />
<asp:BoundField DataField="monthly" HeaderText="monthly"  />
<asp:BoundField DataField="oncall" HeaderText="oncall"  />
<asp:BoundField DataField="engineer" HeaderText="engineer"  />
<asp:BoundField DataField="special" HeaderText="special"  />
<asp:BoundField DataField="total" HeaderText="total"  />
</Columns>
            </asp:GridView>

        </div>
    </form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace FreezeGridViewHeader
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            if (!IsPostBack)
            {
                DataSet ds = new DataSet();
                ds.ReadXml(Server.MapPath("~/App_Data/employees.xml"));
                GridView1.DataSource = ds;
                GridView1.DataBind();
            }

          
        }
    }
}

Executing Default.aspx brings up a page like below

Now, we will use the java script to see how far the intended results are achieved.

Example Two, Calling java script function to freeze the header row

Gridjs.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Gridjs.aspx.cs" Inherits="FreezeGridViewHeader.Gridjs" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="FreeGrid.js" type="text/javascript"></script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical">
                <AlternatingRowStyle BackColor="#DCDCDC" />
                <Columns>
                    <asp:BoundField DataField="EmployeeNumber" HeaderText="Code" />
                    <asp:BoundField DataField="fullname" HeaderText="Name" />
                    <asp:BoundField DataField="department" HeaderText="Department" />
                    <asp:BoundField DataField="section" HeaderText="Section" />
                    <asp:BoundField DataField="position" HeaderText="Position" />
                    <asp:BoundField DataField="basicsalary" HeaderText="Salary" />
                    <asp:BoundField DataField="other" HeaderText="other" />
                    <asp:BoundField DataField="petrol" HeaderText="petrol" />
                    <asp:BoundField DataField="mobile" HeaderText="mobile" />
                    <asp:BoundField DataField="car" HeaderText="car" />
                    <asp:BoundField DataField="transport" HeaderText="transport" />
                    <asp:BoundField DataField="sign" HeaderText="sign" />
                    <asp:BoundField DataField="house" HeaderText="house" />
                    <asp:BoundField DataField="acco" HeaderText="acco" />
                    <asp:BoundField DataField="driving" HeaderText="driving" />
                    <asp:BoundField DataField="monthly" HeaderText="monthly" />
                    <asp:BoundField DataField="oncall" HeaderText="oncall" />
                    <asp:BoundField DataField="engineer" HeaderText="engineer" />
                    <asp:BoundField DataField="special" HeaderText="special" />
                    <asp:BoundField DataField="total" HeaderText="total" />
                </Columns>
                <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
                <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
                <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
                <SortedAscendingCellStyle BackColor="#F1F1F1" />
                <SortedAscendingHeaderStyle BackColor="#0000A9" />
                <SortedDescendingCellStyle BackColor="#CAC9C9" />
                <SortedDescendingHeaderStyle BackColor="#000065" />
            </asp:GridView>
            <script type="text/JavaScript">
                window.onload = function () {
                    this.FreezeGrid("<%=GridView1.ClientID %>",500);

                }
            </script>
        </div>
    </form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace FreezeGridViewHeader
{
    public partial class Gridjs : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataSet ds = new DataSet();
                ds.ReadXml(Server.MapPath("~/App_Data/employees.xml"));
                GridView1.DataSource = ds;
                GridView1.DataBind();
            }
        }
    }
}

I’ve applied one of the built-in themes available for GridView control with the above example, which causes the header and data rows misalignment.

However, the java script has done what it was meant to do! Freezing the header row and providing a scrolling function without breaking much sweat. I have noticed one thing, if the theme is removed from the GridView control, everything goes back to as expected, header row is perfectly aligned with the data row & the scrolling works as expected. Please note, if your grid is populated with huge volume of data, then it will take few moments before the java script manages to bring up the effects in place. I’ve tested this solution using Microsoft Edge Chromium, Mozilla Firefox & Internet Explorer. Both the modern browsers deal with the java script much efficiently while Internet Explorer experiences were NOT that good.

Example Three, fixing the misalignment issues by using some simple CSS styling.

Gridjscss.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Gridjscss.aspx.cs" Inherits="FreezeGridViewHeader.Gridjscss" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="FreeGrid.js" type="text/javascript"></script>
    <style>
        .Colsmall {
            width: 60px;
            max-width: 60px;
            min-width: 60px;
        }

        .Colmedium {
            width: 80px;
            max-width: 80px;
            min-width: 80px;
        }

        .Colbig {
            width: 150px;
            max-width: 150px;
            min-width: 150px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical">
                <AlternatingRowStyle BackColor="#DCDCDC" />
                <Columns>
                    <asp:BoundField DataField="EmployeeNumber" HeaderText="Code" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" />
                    <asp:BoundField DataField="fullname" HeaderText="Name" ItemStyle-CssClass="Colbig" HeaderStyle-CssClass="Colbig" />
                    <asp:BoundField DataField="department" HeaderText="Department" ItemStyle-CssClass="Colbig" HeaderStyle-CssClass="Colbig" />
                    <asp:BoundField DataField="section" HeaderText="Section" ItemStyle-CssClass="Colbig" HeaderStyle-CssClass="Colbig" />
                    <asp:BoundField DataField="position" HeaderText="Position" ItemStyle-CssClass="Colbig" HeaderStyle-CssClass="Colbig" />
                    <asp:BoundField DataField="basicsalary" HeaderText="Salary" ItemStyle-CssClass="Colmedium" HeaderStyle-CssClass="Colmedium" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="other" HeaderText="other" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="petrol" HeaderText="petrol" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="mobile" HeaderText="mobile" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="car" HeaderText="car" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="transport" HeaderText="transport" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="sign" HeaderText="sign" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="house" HeaderText="house" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="acco" HeaderText="acco" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="driving" HeaderText="driving" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="monthly" HeaderText="monthly" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="oncall" HeaderText="oncall" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="engineer" HeaderText="engineer" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="special" HeaderText="special" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="total" HeaderText="total" ItemStyle-CssClass="Colmedium" HeaderStyle-CssClass="Colmedium" ItemStyle-HorizontalAlign="Right" />
                </Columns>
                <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
                <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
                <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
                <SortedAscendingCellStyle BackColor="#F1F1F1" />
                <SortedAscendingHeaderStyle BackColor="#0000A9" />
                <SortedDescendingCellStyle BackColor="#CAC9C9" />
                <SortedDescendingHeaderStyle BackColor="#000065" />
            </asp:GridView>
            <script type="text/JavaScript">
                window.onload = function () {
                    this.FreezeGrid("<%=GridView1.ClientID %>", 500);

                }
            </script>
        </div>
    </form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace FreezeGridViewHeader
{
    public partial class Gridjscss : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataSet ds = new DataSet();
                ds.ReadXml(Server.MapPath("~/App_Data/employees.xml"));
                GridView1.DataSource = ds;
                GridView1.DataBind();
            }
        }
    }
}

Now, everything looks fine! A theme is applied, header and data rows are in perfect sync & the header row is locked when the data rows are scrolled up and down.

There are many alternatives, modern approaches using jQuery and other, which I do not understand at this point of learning. Once I am confident enough to, will sure try them and get back to you guys. Happy coding!

Download VS Solution

Please note, this solution has been developed using .Net 4.7.2 & I have modified the java script in a way that I can call the script from any page by passing couple of parameters to it. The whole credit for the java script goes to original coder.

regards,

rajesh

Transact SQL | Virtual Calendar

Hello guys

We’re integrating MS SQL servers everywhere for our business along with long trusted technology partner Oracle & will try to include what we have “learned” in our labs within this blogosphere.

Our bio metric attendance system reports are designed with virtual dates (weekends when the employees are not punching, yet the reporting requires the whole month dates appearing in the final output, marking the date and day name for the date) from a virtual calendar created using connect by level pseudocolumn

CREATE OR REPLACE FORCE VIEW XXFPAUTOCALNDR_V
(
   EMPLOYEE_NUMBER,
   DAY_DATE,
   PUNCH_PERIOD
)
AS
       SELECT NULL EMPLOYEE_NUMBER,
              TRUNC (TO_DATE ('01-JAN-2009', 'DD-MON-YYYY') + LEVEL) day_date,
              TO_CHAR (TRUNC (TO_DATE ('01-JAN-2009', 'DD-MON-YYYY') + LEVEL),
                       'MMYYYY')
                 punch_period
         FROM DUAL
   CONNECT BY TO_DATE ('01-JAN-2009', 'DD-MON-YYYY') + LEVEL <=
                 LAST_DAY (TRUNC (SYSDATE));

Well, MS SQL doesn’t provide connect by level approach, instead recursive CTE (common table expression) & let us see how we can create a virtual calendar using CTE on MS SQL

CREATE VIEW UnisVCalendar
as
WITH CTE(virtual_date) AS (
  SELECT cast('1/1/2016' as datetime) as virtual_date
  UNION ALL
  SELECT virtual_date+1
  FROM CTE 
  WHERE virtual_date <= GETDATE() 
)
SELECT * FROM CTE
GO

CTE allows referring the table itself, thus indirectly providing a level sort of reference.

Well, the downside of such a virtual calendar is, one should make sure that to use option(maxrecursion 0) to avoid “The maximum recursion 100 has been exhausted before statement completion” error

Select CONVERT(date,virtual_date,105) from UnisVCalendar
WHERE CONVERT(date,virtual_date,105) BETWEEN '01-JAN-2020' AND '31-JAN-2020'
option (maxrecursion 0)
GO

Now you can safely refer this virtual calendar at applicable situations.