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.

VBA (Excel) – Add Single Quote, Excel text cells, Zeros 0s Fix

Hill All, happens to copy and paste from SQL tables or web Page to Excel files, if some fields begin with Zero 
example:“0000545LWEBCODE” , 
when paste to Excel Zeros 0s before other numbers were removed 
example:“545LWEBCODE”
To fix Zeros 0 before number and other things , Here is VBA funcion/Script which add a single quote ‘ before each cells: you can copy from any external platform like Internet, SQL Tables, SQL Views etc.., and paste it in your Microsoft Excel sheet,
Just Copy and Paste this code in a Module a run it.


‘** VBA Script Excel Zeros 0s Fix Script by lwebcode.blogspot.com
Public Sub AddQuote()
Dim Start_, End_, Col, i As Integer
Dim SName As String

Start_ = InputBox(“Row Start:”, “Start”, “1”)
End_ = InputBox(“Row End:”, “End”, “10000”)

Col = CInt(InputBox(“Column (in Number) to Add Quote”, “Column”, “1”))

SName = InputBox(“Sheets’s Name:”, “Sheets”, Sheets(1).Name)

For i = Start_ To End_
    If Sheets(SName).Cells(i, Col).Value “” Then
        Sheets(SName).Cells(i, Col).Value = “‘” & Sheets(SName).Cells(i, Col).Value
    End If
Next

MsgBox “Executed”, vbInformation, “Executed”

End Sub