IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

Question

0.00/5 (No votes)

See more:

I got the error like this 'IErrorInfo.GetDescription failed with E_FAIL(0x80004005)'.
while executing the query to transfer the database from msaccess to SQL.

I have written this code

C#

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
               DataTable userTables = null;
               using (connection)
               {
                   string mappath = dataGridView1.CurrentRow.Cells["Path"].Value.ToString();
                   string[] filePaths = Directory.GetFiles(@"" + mappath + "", "*.mdb", SearchOption.TopDirectoryOnly);
                   // c:\test\test.mdb
                   foreach (string tr in filePaths)
                   {
                       connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + tr + "";
                       string[] restrictions = new string[4];
                       restrictions[3] = "Table";
                       connection.Open();
                       userTables = connection.GetSchema("Tables", restrictions);
                       List<string> tableNames = new List<string>();
                       for (int i = 0; i < userTables.Rows.Count; i++)
                           tableNames.Add(userTables.Rows[i][2].ToString());
                       try
                       {
                           foreach (string tableName in tableNames)
                           {
                               //cn1 = new SqlConnection(con);
                               //if (cn1.State != ConnectionState.Open) { cn1.Open(); }
                               //SqlCommand cmd = new SqlCommand("select * into [" + tableName + "] from OPENROWSET('Microsoft.Jet.OLEDB.4.0','" + tr + "';'Admin';'',[" + tableName + "])");
                               //cmd.Connection = cn1;
                               //cmd.ExecuteNonQuery();
                               OleDbCommand cmd = new OleDbCommand("select * into ["+tableName+"] IN "+con+" FROM ["+tableName+"]",connection);
                               cmd.ExecuteNonQuery();
                               connection.Close();
                           }
                       }
                       catch (Exception Ex) { connection.Close(); }
                       connection.Close();
                   }
               }
           }
           catch (Exception Ex) { }

And the query is like this

SQL

select * into [ACTYPE] IN ODBC; Driver={SQL Server};Server='PC01';Initial Catalog=TARR;Persist Security Info=True;User ID= 'sa';Password='123' FROM [ACTYPE]

Would u pls resolve this error.

Posted 5-Aug-15 23:21pm

TarunKumarSusarapu

Updated 29-Sep-15 20:36pm

v2

Add a Solution

2 solutions

Solution 1

I believe this would be simpler if you would use OPENROWSET[^] and execute the statement in Sql Server. The query could look like

SQL

INSERT INTO SqlServerTableName (col1, col2, ...) 
SELECT col1, col2, ...
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'path to the mdb file';
                'admin';'',
                AccessTableName);

If you don't want to use OPENROWSET you can create a linked server on the Sql Server which would point to the Access database.

In order to create a linked server, have a look at sp_addlinkedserver[^].

So creating the server could be something like

SQL

EXEC sp_addlinkedserver 
        @server     = 'mydatainaccess', 
        @provider   = 'Microsoft.Jet.OLEDB.4.0', 
        @srvproduct = 'OLE DB Provider for Jet',
        @datasrc    =  'path to the mdb file'

So you'd create this only once on the SQL Server and after that you can copy the data by executing the INSERT INTO SELECT statements on the Sql Server. Something like:

SQL

INSERT INTO SqlServerTableName (col1, col2, ...) 
SELECT col1, col2, ... FROM mydatainaccess...AccessTableName

Posted 30-Sep-15 6:58am

Wendelius

Comments

TarunKumarSusarapu 1-Oct-15 0:45am

Thanks for your consideration I got error like this 'Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.' while using openrowset. I will try linked server.
This is my query
insert into [ACTYPE] select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0','c:\EZACC\DATA\AH.mdb';'Admin';'',[ACTYPE])

TarunKumarSusarapu 1-Oct-15 1:43am

Bro I tried all that i got error like this 'Access to the remote server is denied because no login-mapping exists.' while accessing the data from the linked server i created linked server login and linkedserver also but i am using a server which is connected to all the machines.
Is it necessary to take the permission from admin to do this operation? Here Mdb files are in local machine i.e my pc.

Wendelius 1-Oct-15 14:47pm

If you don't have any password protection on the Access mdb you can skip the username and password.

However, the main problem with OPENROWSET seems to be that ad hoc queries are not allowed in your system. Have a look at ad hoc distributed queries Server Configuration Option[^]

TarunKumarSusarapu 7-Oct-15 7:50am

Thank you very much for your information bro But I got error while saving table into destination table would u pls observe below solution and guide me your suggestion

Wendelius 7-Oct-15 8:38am

Instead of writing the commands into the program, try first creating the linked server in SSMS and running the query in SSMS. This way you can ensure that the commands that you build are correct.

TarunKumarSusarapu 8-Oct-15 0:41am

Actually we are calling the commands from front end we need to do import from access to sql on button click can u pls elaborate this?

Wendelius 8-Oct-15 1:36am

I understand that you're using the program to do the import but in order to see what goes wrong and how to correct it, it's more simple to use SSMS as a test bench. When you get it working in SSMS then write it in the program. This simplified the error-solving process.

TarunKumarSusarapu 8-Oct-15 1:43am

I tried same with stored procedure but got same error here. Actually we have more than 50 mdb files in a folder and each have 40 tables we need to access one by one and executing the query. would u pls write the above query and use SSMS

TarunKumarSusarapu 8-Oct-15 4:56am

is there any use with nolock using here

TarunKumarSusarapu 14-Oct-15 2:54am

I have successfully imported database from msaccess to sql server using linked servers but it doesn't set is identity prpoerty to columns in destination table.

I have written one stored procedure with setting the identity_insert on before the select * into query but it is being gotten error.

This is my procedure
create proc
SP_Create(@servername nvarchar(100),
@datasr nvarchar(MAX),@table nvarchar(100),
@table1 nvarchar(100),@db nvarchar(100))
as begin EXEC sp_addlinkedserver @server = @servername,
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = @datasr,@provstr=''
exec sp_addlinkedsrvlogin @rmtsrvname=@servername,
@useself = N'false',@locallogin = NULL,@rmtuser = N'Admin',
@rmtpassword = NULL declare @sql nvarchar(MAX)
set @sql=' SET IDENTITY_INSERT '+@table1+' ON; select * into '+@table1+' from '+@servername+'...'+@table+' with (nolock) SET IDENTITY_INSERT '+@table1+' ON '
Exec(@sql)
declare @sql1 nvarchar(MAX) set @sql1 ='EXEC master.sys.sp_dropserver '+@servername+',''droplogins''' exec(@sql1)
end

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

TarunKumarSusarapu · Accepted Answer · 2015-10-07T01:46:00

Finally I have written code by using linked server to import db from msaccess to sql

C#

foreach (string tableName in tableNames)
                          {
                              string acc = tableName + DateTime.Now.Millisecond;
                              string str = "EXEC sp_addlinkedserver "+
                              "@server     = '"+acc+"', "+
                              "@provider   = 'Microsoft.Jet.OLEDB.4.0', "+
                              "@srvproduct = 'OLE DB Provider for Jet',"+
                              "@datasrc    =  '"+tr+"',"+
                              "@provstr=''";
                              try
                              {
                                  cn1 = new SqlConnection(con);
                                  if (cn1.State != ConnectionState.Open) { cn1.Open(); }
                                  cmd = new SqlCommand(str);
                                  cmd.Connection = cn1;
                                  cmd.ExecuteNonQuery();
                              }
                              catch (Exception Ex) { }
                              str = "exec sp_addlinkedsrvlogin @rmtsrvname=N'" + acc + "', " +
                                         "@useself = N'false'," +
                                         "@locallogin = NULL," +
                                         "@rmtuser = N'Admin'," +
                                         "@rmtpassword = NULL";
                               try
                               {
                                   if (cn1.State != ConnectionState.Open) { cn1.Open(); }
                                   cmd = new SqlCommand(str);
                                   cmd.Connection = cn1;
                                   cmd.ExecuteNonQuery();
                               }
                               catch (Exception EX) { }
                               try
                               {
                                   cn1 = new SqlConnection(con);
                                   if (cn1.State != ConnectionState.Open) { cn1.Open(); }
                                   cmd = new SqlCommand("select * into " + tableName + " from " + acc + "..." + tableName + "");
                                   cmd.Connection = cn1;
                                   cmd.CommandTimeout = 180;
                                   cmd.ExecuteNonQuery();-------ERROR 
                               }
                               catch (Exception EX) { }
                               try
                               {
                                   cn1 = new SqlConnection(con);
                                   if (cn1.State != ConnectionState.Open) { cn1.Open(); }
                                   cmd = new SqlCommand("IF EXISTS(SELECT * FROM sys.servers WHERE name = N'" + acc + "') EXEC master.sys.sp_dropserver '" + acc + "','droplogins'");
                                   cmd.Connection = cn1;
                                   cmd.ExecuteNonQuery();
                               }
                               catch (Exception EX) { }
                              //OleDbCommand cmd = new OleDbCommand("select * into [" + tableName + "] IN " + con + " FROM [" + tableName + "]", connection);
                              ////OleDbCommand cmd = new OleDbCommand("select [[" + tableName + "].*] into  [" + con + "].[" + tableName + "] From [" + tableName + "]");
                              //cmd.Connection = connection;
                              //cmd.ExecuteNonQuery();
                              //connection.Close();
                               cn1.Close();
                          }
                      }
                      catch (Exception Ex) { connection.Close(); cn1.Close(); }
                      connection.Close();

But I am getting error like this 'Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ClientAc723".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ClientAc723" returned message "Could not use ''; file already in use.".' at select * into query

If any help from you would be much appreciated.