SQL CE, OleDb, nText, unsupportedconversion

for using Microsoft.ACE.OLEDB.12.0 while connecting Excel 2007 do we need Excel 2007 installed in our machine ?

Is this driver available ? or we have to install it ?

I have a web application developed using VS 2008... It creates & reads excel files (the creation part is done using the Enterprise Office object library 12, and the reading part is using the Microsoft.ACE.OLEDB.12.0) when deploying the application to a windows server 2008 R2 64 bit machine we faced "the microsoft.ace.oledb.12.0 provider is not registered" error. So I had to change the application pool in IIS7 to enabled 32 bit applications (which make the web application run in a 32 bit environment) and this solved our problem...

Now we had another requirement that needs to retrieve some data from an Oracle DB after installing the Oracle 10g client 64-bit and redeploying the modified web application to the server I am getting the following error: "Attempt to load Oracle client libraries threw BadImageFormatException.  This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.", Once seeing this error I tried to switch back the application pool to run on 64 bit and it worked!

Now I am stuck, Microsoft.ACE.OLEDB.12.0 requires 32 bit mode while the Oracle connection requires 64 bit mode. I searched in the net for a 64 bit replacement of ACE and found the Microsoft.ACE.OLEDB.14.0, but installing this requires removing Ms Office 2007 from the machine which I can't do because I am using the object library for creating excel files! So does anyone have any suggestion on how to tackle this? 

I have a web application developed using VS 2008... It creates & reads excel files (the creation part is done using the Enterprise Office object library 12, and the reading part is using the Microsoft.ACE.OLEDB.12.0) when deploying the application to a windows server 2008 R2 64 bit machine we faced "the microsoft.ace.oledb.12.0 provider is not registered" error. So I had to change the application pool in IIS7 to enabled 32 bit applications (which make the web application run in a 32 bit environment) and this solved our problem...

Now we had another requirement that needs to retrieve some data from an Oracle DB after installing the Oracle 10g client 64-bit and redeploying the modified web application to the server I am getting the following error: "Attempt to load Oracle client libraries threw BadImageFormatException.  This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.", Once seeing this error I tried to switch back the application pool to run on 64 bit and it worked!

Now I am stuck, Microsoft.ACE.OLEDB.12.0 requires 32 bit mode while the Oracle connection requires 64 bit mode. I searched in the net for a 64 bit replacement of ACE and found the Microsoft.ACE.OLEDB.14.0, but installing this requires removing Ms Office 2007 from the machine which I can't do because I am using the object library for creating excel files! So does anyone have any suggestion on how to tackle this?

I am attempting to use the 64-bit OLEDB Provider for Access found in the "Microsoft Access Database Engine 2010 Redistributable" to connect to and query an Access 2003 database that is on another server separate from my SharePoint server. I have not had any success making the connection. My SharePoint server is Windows Server 2008 SP2 and I am running MOSS 2007 (patched up to the April 2010 CU)

Here is the connection info in my Application Definition XML:

    <LobSystemInstance Name="LessonsLearnedDevOLEDB_Instance">
      <Properties>
        <Property Name="AuthenticationMode" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAuthenticationMode">RevertToSelf</Property>
        <Property Name="DatabaseAccessProvider" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAccessProvider">OleDb</Property>
        <Property Name="rdbconnectionData Source" Type="System.String">\\server1\share1\Lessons.mdb</Property>
        <Property Name="rdbconnectionProvider" Type="System.String">Microsoft.ACE.OLEDB.12.0</Property>
        <Property Name="rdbconnectionIntegrated Security" Type="System.String">SSPI</Property>
      </Properties>
    </LobSystemInstance>

The error I am getting in the SharePoint logs looks like:

Could not open connection using 'data source=\\server1\share1\Lessons.mdb;integrated security=SSPI;provider=Microsoft.ACE.OLEDB.12.0;persist security info=false' ... The full exception text is: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

SharePoint is adding the "persist security info=false;" to the connection string. I would hope that is not the problem.

SharePoint insists on having "Integrated Security". I don't know what the valid values are but I tried "SSPI", "false" and "true" with no luck.

I tried both RevertToSelf and PassThrough since both my account and the application pool identity account have access to the share where the mdb files resides but no luck there.

FYI, I started all this using the Application Definition Designer tool on my local machine. A really frustrating thing is that I  can use the "Add a LOB System" window to connect to this database specifying just the provider ("Microsoft.Jet.OLEDB.4.0") and data source, specify a table, click OK and then the Application Definition Designer crashes/shuts down. To work around this, I created a working Application Definition using the Access ODBC driver on my local machine and then changed the connection information to use the Microsoft.Jet.OLEDB.4.0. In trying one of the Entity Method Instances it says I needed "Integrated Security" in the connection string. I added that (trying various values) and it will not connect. 

Out of ideas, from there I just went on anyways (changing the Provider to Microsoft.ACE.OLEDB.12.0) to uploading my Application Definition XML to the BDC and trying it on my SharePoint system.

Anyone have any ideas on what is wrong with my connection string or what else I may need to do? Please don't suggest I use ODBC instead as I can and have done that already. I want to make it work with the OLEDB provider. Thanks!

Nick

I am trying to use the OPENROWSET function to import data into an SQL Server 2008 database from Excel 2007.

The following error appears upon execution:

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

I have Windows XP Professional 2002 SP3 (32 bit operating system)

I have 2007 Office System Driver:  Data Connectivity Components installed.

The registry shows the following drivers installed under ODBC --> ODBCINST.INI:

Microsoft Excel Driver (*.xls)

Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)

Code:

INSERT INTO dbo.mydatatable

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=MyFile.xlsx', [MySheet$]) 

Can anyone help with resolving this issue?

Thanks!

CREATE TABLE People ( 
      personID     int          not null 
    , name         nvarchar(50)  not null 
    , addrLine1    nvarchar(50) null 
    , addrLine2    nvarchar(50) null 
    , suburb       nvarchar(30) not null 
    , xmlRawInput  ntext        not null 
    , xmlRawOutput ntext        null 
)  
GO 
 
 
INSERT People ( 
      personID           
    , name               
    , addrLine1          
    , addrLine2          
    , suburb             
    , xmlRawInput        
    , xmlRawOutput 
) 
SELECT 
      101             AS personID            
    , 'george benson'   AS name 
    , '123 help st'   AS addrLine1 
    , NULL            AS addrLine2           
    , 'los angeles'   AS suburb 
    , '<example>record<example>' AS xmlRawInput      
    , 'I LOVE MICROSOFT' AS xmlRawOutput 
GO 

The above works; However when i replace 'I Love Microsoft' with NULL, get following error:

I have created an OLEDB source from a SqlCommand successfully when using

                CManagedComponentWrapper instanceSourceAgg = compSourceAgg.Instantiate();

                // Initialize the component
                instanceSourceAgg.ProvideComponentProperties();

                instanceSourceAgg.SetComponentProperty("AccessMode", 2);
                instanceSourceAgg.SetComponentProperty("SqlCommand", "Select EXTREF as AGG_EXTREF, AGG_CREATED_DATE, AGG_UPDATED_DATE from agg." + sourceSQLObject.Replace("[", "").Replace("]", "").Split('.')[1] + " Order by ExtRef");

Does anyone have an example of using SQL Command from variable?

I have created the variable:

                if (!package.Variables.Contains("ExtractSelection"))
                {
                    package.Variables.Add("ExtractSelection", false, "User", "");
                }
                package.Variables["ExtractSelection"].Expression = "Select * from agg.tvf" + sourceSQLObject.Replace("[", "").Replace("]", "").Split('.')[1] + "(@[User::BatchID]) Order by ExtRef";

I assume you need to add by using instanceSourceExtract.SetComponentProperty("SqlCommandFromVariable",

but cant seem to get the variable added

Any help would be appreciated

David

 When im browse an excel file i get an error message        ' The Microsoft Jet database engine could not find the object 'j$'.  Make sure the object exists and that you spell its name and the path name correctly.'   What does it mean?