Overlapped I/O operation is in progress, fix Windows Installer error

Hi all, here how you can try to fix Windows Installer error:
Overlapped I/O operation is in progress
On Microsoft SQL Server Setup Installing have Overlapped error

Try to follow these steps:

1    Click Start, click Run, type regedit in the Open box, and then click OK.
2    Locate and then click the following subkey in the registry:
     HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Installer
3    On the Edit menu, point to New, and then click DWORD Value.
     Type SecureRepairPolicy for the name of the DWORD, and then press Enter.
4    Right-click SecureRepairPolicy, and then click Modify.
5    In the Value data box, type 2, and then click OK.
6    Locate and then click the following subkey in the registry:
     HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Installer
7    On the Edit menu, point to New, and then click Key.
8    Type SecureRepairWhitelist for the name of the key, and then press Enter.
9    Double-click the SecureRepairWhitelist key to open it.
10   On the Edit menu, point to New, and then click String Value. Create String   Values that contain the product codes (including braces {})
     of the products that have to be added to the Safe Recipients list.

     The NAME of the String Value is the "product code" and the VALUE can be left blank. To obtain the product code for other MSIs, open the MSI by using the ORCA tool that is available in Windows SDK.
   
     LWEBCODE NOTES:
     If you can't access to "product code" with ORCA, you can try this on another machine wich have already the program installed
     in this example we have trouble installing MSSQL Server, so find in another machine
     HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup
     key:Product Code => you can copy the value
   
11     Retry to install
   
     If install doesn't work in step N° 5 try to set 1 for SecureRepairPolicy value
   
Reference:
https://support.microsoft.com/en-us/help/2918614/ms14-049-description-of-the-security-update-for-windows-installer-service-august-12,-2014

https://blogs.msdn.microsoft.com/vsnetsetup/2014/10/27/error-997-overlapped-io-operation-is-in-progress-kb2918614-breaks-windows-installer-service/

http://www.sqlservergeeks.com/sql-server-finding-out-sql-server-product-code-from-windows-registry/

C# Export Dataset To XML, ISNULL Exporting fix

Hi All, Now see what happen, when export Datate filled with a standard SQL query/stetement:
With this SQL Table:


CREATE TABLE [dbo].[TabExample](
    [FieldNvar] [nvarchar](10) NULL,
    [FieldInt] [int] NULL,
    [FieldDate] [datetime] NULL
   
) ON [PRIMARY]

GO

With this record #1:

 FieldNvar | FieldInt | FieldDate
 lwebcode  |   NULL   | 01/01/2017


with standard SQL to fill Dataset we can have some problem:

Dataset ds = ... "SELECT * FROM TabExample"

ds.WriteXml(sTargetXML);

//will return this XML:

<?xml version="1.0" standalone="true"?>
<NewDataSet>
    <Table>
        <FieldNvar>lwebcode</FieldNvar>
        <FieldDate>2017-01-01T00:00:00+01:00</FieldDate>
    </Table>
</NewDataSet>

FieldInt Was Not Write!

With this record #2:

 FieldNvar | FieldInt | FieldDate
    NULL   |    2     | 01/01/2017

with standard SQL to fill Dataset we can have some problem:

Dataset ds = ... "SELECT * FROM TabExample"

ds.WriteXml(sTargetXML);

//will return this XML:

<?xml version="1.0" standalone="true"?>
<NewDataSet>
    <Table>
        <FieldInt>2</FieldInt>
        <FieldDate>2017-01-01T00:00:00+01:00</FieldDate>
    </Table>
</NewDataSet>

FieldNvar Was Not Write!

With this record #3:

 FieldNvar | FieldInt | FieldDate
    NULL   |    2     | NULL


with standard SQL to fill Dataset we can have some problem:

Dataset ds = ... "SELECT * FROM TabExample"

ds.WriteXml(sTargetXML);

//will return this XML

<?xml version="1.0" standalone="true"?>
<NewDataSet>
    <Table>
        <FieldInt>2</FieldInt>
        <FieldDate>1900-01-01T00:00:00+01:00</FieldDate>
    </Table>
</NewDataSet>

If datetime has NULL value it will put firs day of datetime in xml
As in #2 FieldNvar Was Not Write!

To fix this issue, you have to edit SQL Statement using ISNULL Function:

Dataset ds = ... "SELECT ISNULL(FieldNvar, '') FieldNvar, ISNULL(FieldInt, '') FieldInt, ISNULL(FieldDate , '') FieldDate FROM TabExample"

with record #1, we have a error on converting Numeric Value to char, see later what to do.

with record #2
XML file will like:

<?xml version="1.0" standalone="true"?>
<NewDataSet>
    <Table>
        <FieldNvar/>
        <FieldInt>2</FieldInt>
        <FieldDate>2017-01-01T00:00:00+01:00</FieldDate>
    </Table>
</NewDataSet>

with record #3
XML file will like:

<?xml version="1.0" standalone="true"?>
<NewDataSet>
    <Table>
        <FieldNvar/>
        <FieldInt>2</FieldInt>
        <FieldDate/>
    </Table>
</NewDataSet>

with record #1 there is a last thing to do on all numeric value (int, decimal etc...), in SQL you neet to put ISNULL function with CAST our SQL query will Like:

Dataset ds = ... "SELECT ISNULL(FieldNvar, '') FieldNvar, CASE WHEN FieldInt IS NULL THEN '' ELSE CAST(FieldInt AS NVARCHAR(50)) END FieldInt, ISNULL(FieldDate , '') FieldDate FROM TabExample"

Now XML file will like:

<?xml version="1.0" standalone="true"?>
<NewDataSet>
    <Table>
        <FieldNvar>lwebcode</FieldNvar>
        <FieldInt/>
        <FieldDate>2017-01-01T00:00:00+01:00</FieldDate>
    </Table>
</NewDataSet>

With this fix in sql statement, we can export every kind of fields and be sure that each field will be write on xml file.

c# fast traslation of websites, Extract *.resx To *.xls

Hi All, Here a very quick Class which get all Entries to translate from resource *.resx file and make a Excel *.xls file with a list of all Entries.
Program writes entries in “A” column, 
In “B” (“C”,”D”…if you have more languages) Final user have to puts his language translation,
when done, after xls is saved and came back:
make a copy of *.resx file
With advanced text editor such as notepad++ or ultraedit after have make a copy of *.resx file, with “Find and Replace” tool I can easly traslate all the entries.
here the code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using Excel; // Add refer to Excel (in COM object Microsoft Excel Library )
using System.IO;

//#LWEBCODE
//Website Building & free script at http://lwebcode.blogspot.com/

namespace LWEBCODE
{
    public partial class ResxToXls
    {
        public static void MyStart()
        {
        string sPath =@"\\server\folder\" // where resx files are
        string DestFile = "", Title = "", ResxFile = "";
        string[] SourceFiles = Directory.GetFiles(sPath);
        if (SourceFiles.Length > 0)
        {
            for (int i = 0; i < SourceFiles.Length; i++)
            {
                if (SourceFiles[i].EndsWith(".resx"))
                {
                ResxFile = SourceFiles[i];
                Title = PageNameASPX(ResxFile);
                DestFile = ResxFile.Substring(0, ResxFile.LastIndexOf(@"\") + 1) + Title + ".xls";

                ReadResx(ResxFile, DestFile);
                }
            }
        }
        }

        public static void ReadResx(string ResxPath, string XlsPath)
        {
        Excel.ApplicationClass excel = new ApplicationClass();
        XmlTextReader xTr = new XmlTextReader(ResxPath);
        string ris = "";
        bool bwrite = true;
            while (xTr.Read())
            {
                // Do some work here on the data.
                //Console.WriteLine(xTr.Name);
                switch (xTr.NodeType)
                {
                case XmlNodeType.Element: // The node is an element.
                Console.Write("");

                break;
                case XmlNodeType.Text: //Display the text in each element.
                Console.WriteLine(xTr.Value);

                if (bwrite)
                ris += xTr.Value + ";";
                break;
                case XmlNodeType.EndElement: //Display the end of the element.
                Console.Write("");

                if ((xTr.Name.ToLower() == "comment") || (xTr.Name.ToLower() == "resheader"))
                bwrite = true;
                break;
                }
            }
        Console.Write(ris);
        Excel.Workbook workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
        Excel.Sheets sheets = workbook.Worksheets;

        excel.Visible = true;
        Excel.Worksheet mySheet = (Excel.Worksheet)sheets.get_Item(1);

        string[] splRis = ris.Split(Convert.ToChar(";"));
        int ExRow = 0;
        for (int j = 1; j < splRis.Length; j++)
        {
            ExRow = j + 4;
            Excel.Range myCell = (Excel.Range)mySheet.get_Range("A" + ExRow.ToString(), "A" + ExRow.ToString());
            myCell.Value = splRis[j - 1];
        }
        workbook.SaveAs(XlsPath, Excel.XlFileFormat.xlExcel9795, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null);
        excel.Quit();
        }

        public static string PageNameASPX(string ResxPath)
        {
        return ResxPath.Substring(ResxPath.LastIndexOf(@"\") + 1).Replace(".resx","");
        }
    }
}

C# GridView Runtime Change Header Text, AutoGenerateColumns=”true” AND AllowSorting =”True”

Hi All, here’s function to runtime Change Header Text with AutoGenerateColumns=”true” AND AllowSorting =”True” in ASPX GridView:

//#LWEBCODE
//Website Building & free script at http://lwebcode.blogspot.com/
 
protected void GVTestate_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.Header)
    {

        LinkButton lnk = new LinkButton();
        // I've hide My first column in my GridView
        // You should have to Start from index 0
        // lnk = (LinkButton)e.Row.Cells[0].Controls[0];

        lnk = (LinkButton)e.Row.Cells[1].Controls[0];
        lnk.Text = "HeaderText1"
        e.Row.Controls[1].Controls.Clear();
        e.Row.Controls[1].Controls.Add(lnk);

        lnk = (LinkButton)e.Row.Cells[2].Controls[0];
        lnk.Text = "HeaderText2"
        e.Row.Controls[2].Controls.Clear();
        e.Row.Controls[2].Controls.Add(lnk);

        lnk = (LinkButton)e.Row.Cells[3].Controls[0];
        lnk.Text = "HeaderText3"
        e.Row.Controls[3].Controls.Clear();
        e.Row.Controls[3].Controls.Add(lnk);

        lnk = (LinkButton)e.Row.Cells[4].Controls[0];
        lnk.Text = "HeaderText4"
        e.Row.Controls[4].Controls.Clear();
        e.Row.Controls[4].Controls.Add(lnk);

        lnk = (LinkButton)e.Row.Cells[5].Controls[0];
        lnk.Text = "HeaderText5"
        e.Row.Controls[5].Controls.Clear();
        e.Row.Controls[5].Controls.Add(lnk);

        lnk = (LinkButton)e.Row.Cells[6].Controls[0];
        lnk.Text = "HeaderText6"
        e.Row.Controls[6].Controls.Clear();
        e.Row.Controls[6].Controls.Add(lnk);
    }
}

SQL – Servers, Databases, Tables & Fields List

Hi All, here a very easy SQL script to get  Servers, Databases, Tables & Fields of  Table
 
To see other property of fields put ” ,*”  after COLUMN_NAME

1) Servers List:

SELECT * FROM master.dbo.sysservers

2)Databases List:

SELECT * FROM master.dbo.sysdatabases

3)Tables/Views List:

SELECT     TABLE_NAME
FROM         INFORMATION_SCHEMA.TABLES
WHERE     (TABLE_TYPE = ‘BASE TABLE’)

Change ‘BASE TABLE’ to ‘VIEWS’ to get Views list

4)Table's Field/Columns List:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =’MyTalbe_Name’