OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”

1. On 64-bit servers and boxes, you need to first UNINSTALL all 32-bit Microsoft Office applications and instances (Access 2007 install, Office 10 32-bit, etc.). If you dont, you cannot install the new 64-bit Microsoft Access Database Engine 2010 Redistributable components. Yes, its a headache but the only way I found to install the new replacements for the JET engine components that need to run on 64-bit machines.
2. Download and install the new component from Microsoft:http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=en
   • This will install the access and other engines you need to set up linked servers, OPENROWSET excel files, etc.
3. Open up SQL Server and run the following:

   sp_configure 'show advanced options', 1;
   GO
   RECONFIGURE;
   GO
   sp_configure 'Ad Hoc Distributed Queries', 1;
   GO
   RECONFIGURE;
   GO
   EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
   GO
   EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
   GO

   • This sets the parameters needed to access and run queries related to the components. Address ‘null
4. Now, if you are running OPENROWSET calls you need to abandon calls ,made using the old JET parameters and use the new calls as follows:

   (*Example, importing an EXCEL file directly into SQL):
   DONT DO THIS….
   SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;Database=c:\PATH_TO_YOUR_EXCEL_FILE.xls','select * from [sheet1$]')
   
   USE THIS INSTEAD…
   SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\PATH_TO_YOUR_EXCEL_FILE.xls','select * from [sheet1$]')
   
   *At this point resolved two SQL issues and ran perfectly

5. Now for the fun part…..find all your Office Disks and reinstall Office and/or applications needed back onto the machine. You can install the 64- bit version of Office 10 by going onto the disk and going into the 64-bit folder and running it but beware as in some cases some third party apps dont interface yet with that version of Office.

How to detect if a string contains special characters or number

DECLARE @MyString VARCHAR(100)
SET @MyString = 'adgkjb$'

IF (@MyString LIKE '%[^a-zA-Z0-9]%')
    PRINT 'Contains "special" characters'
ELSE
    PRINT 'Does not contain "special" characters'

for number

DECLARE @MyString VARCHAR(100)
SET @MyString = 'adgkjb$'

IF (@MyString LIKE '%[^0-9]%')
    PRINT 'not Contains "number" characters'
ELSE
    PRINT 'contain "number" characters'

SQL SERVER – Get Numeric Value From Alpha Numeric String – UDF for Get Numeric Numbers Only

CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO

/* Run the UDF with different test values */
SELECT dbo.udf_GetNumeric('') AS 'EmptyString';
SELECT dbo.udf_GetNumeric('asdf1234a1s2d3f4@@@') AS 'asdf1234a1s2d3f4@@@';
SELECT dbo.udf_GetNumeric('123456') AS '123456';
SELECT dbo.udf_GetNumeric('asdf') AS 'asdf';
SELECT dbo.udf_GetNumeric(NULL) AS 'NULL';
GO

JAVA : Read Recursive XML Using Dom

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;


public class DomFunction {
   
   
    public static void main(String args[])
    {
        try
        {
            DocumentBuilderFactory dbl=DocumentBuilderFactory.newInstance();
            DocumentBuilder db=dbl.newDocumentBuilder();
            Document doc=db.parse("http://www.freakycoders.com/android/t5_data/phonebook.xml");
           
           
           
            printAttributes(doc);
            printElement(doc);
           
           
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    private static void printAttributes(Document doc)
    {
        NodeList nl=doc.getElementsByTagName("*");
        for(int i=0; i < nl.getLength(); i++)
        {
            Element e=(Element)nl.item(i);
            NamedNodeMap nnm=e.getAttributes();
           
            if(nnm != null)
            {
                for(int k=0; k < nnm.getLength(); k++)
                {
                    Node n=nnm.item(k);
                    System.out.println(e.getNodeName() + " : " + n.getNodeName() +" : " + n.getNodeValue());
                }
            }
        }
    }

    private static void printElement(Document doc) {
        NodeList nl=doc.getElementsByTagName("*");
        for(int i=0; i < nl.getLength(); i++)
        {
            Node n=nl.item(i);
            System.out.println(n.getNodeName() + " : " + n.getFirstChild().getTextContent());
           
        }
       
    }


}

Select distinct values from a large DataTable column

Method 1:

DataView view = new DataView(table);
   DataTable distinctValues = view.ToTable(true, "id");

Method 2: You will have to create a class matching your datatable column names and then you can use the following extension method to convert Datatable to List

 public static List<T> ToList<T>(this DataTable table) where T : new()
    {
        List<PropertyInfo> properties = typeof(T).GetProperties().ToList();
        List<T> result = new List<T>();

        foreach (var row in table.Rows)
        {
            var item = CreateItemFromRow<T>((DataRow)row, properties);
            result.Add(item);
        }

        return result;
    }

    private static T CreateItemFromRow<T>(DataRow row, List<PropertyInfo> properties) where T : new()
    {
        T item = new T();
        foreach (var property in properties)
        {
            if (row.Table.Columns.Contains(property.Name))
            {
                if (row[property.Name] != DBNull.Value)
                    property.SetValue(item, row[property.Name], null);
            }
        }
        return item;
    }

and then you can get distinct from list using

YourList.Select(x => x.Id).Distinct();

Getting the source of a specific image element with jQuery

<div>
    <img alt="example" src="\images\show.jpg" />
    <img  alt="exampleAll" src="\images\showAll.jpg" />  

</div>

var src = $('img[alt="example"]').attr('src');
alert("source of image with alternate text = example - " + src);


var srcAll = $('img[alt="exampleAll"]').attr('src');
alert("source of image with alternate text = exampleAll - " + srcAll );

JAVA : Read XML Using DOM Parsing.

.xml

<?xml version="1.0" encoding="UTF-8"?>
<book>

    <person>

        <first>bhavesh</first>
        <last>Rangani</last>
        <age>22</age>

    </person>
    <person>
        <first>Vivek</first>
        <last>Patoliya</last>
        <age>21</age>
    </person>
    <person>
        <first>Vishal</first>
        <last>Mangroliya</last>
        <age>21</age>
    </person>
</book>

.JAVA

import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.DocumentBuilder;
import org.w3c.dom.Document;
import org.w3c.dom.NodeList;
import org.w3c.dom.Node;
import org.w3c.dom.Element;


public class DomParser {
   
    public static void main(String args[])
    {
        try
        {
           
           
            DocumentBuilderFactory f = DocumentBuilderFactory.newInstance();
            DocumentBuilder b = f.newDocumentBuilder();
            Document doc = b.parse("/Volumes/Project/Bhavesh/workspace/DomParser/src/person.xml");

            doc.getDocumentElement().normalize();
            System.out.println ("Root element: " +
                        doc.getDocumentElement().getNodeName());
      
           
            NodeList items = doc.getElementsByTagName("person");
            for (int i = 0; i < items.getLength(); i++)
            {
                Node n = items.item(i);
                if (n.getNodeType() != Node.ELEMENT_NODE)
                    continue;
                Element e = (Element) n;
              
                System.out.println("\n");
                System.out.println("Person:");
                NodeList first =e.getElementsByTagName("first");
                Element titleElem = (Element) first.item(0);               
                Node titleNode = titleElem.getChildNodes().item(0);
                System.out.println("First Name :"+titleNode.getNodeValue());
               
                NodeList last =e.getElementsByTagName("last");
                Element titleElem1 = (Element) last.item(0);               
                Node titleNode1 = titleElem1.getChildNodes().item(0);
                System.out.println("Last name :"+titleNode1.getNodeValue()); 
               
                NodeList age =e.getElementsByTagName("age");
                Element titleElem11 = (Element) age.item(0);               
                Node titleNode11 = titleElem11.getChildNodes().item(0);
                System.out.println("Age :"+titleNode11.getNodeValue());
               
               
               
            }
       
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
       
    }
   

}