I got error like this IErrorInfo.GetDescription failed with E_FAIL(0x80004005) while converting msaccess db to sql

Question

1.00/5 (1 vote)

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 29-Sep-15 23:19pm

TarunKumarSusarapu

Updated 29-Sep-15 23:20pm

Add a Solution

Comments

CHill60 30-Sep-15 8:23am

That query looks weird - a combination of a query and a connection string. Why aren't you using the openrowset that is commented out?

Maciej Los 30-Sep-15 16:23pm

My virtual 5!

CHill60 30-Sep-15 16:29pm

Thank you!

Maciej Los 30-Sep-15 16:33pm

You're welcome, Caroline. You inspired me to post my answer. Please, see it.

TarunKumarSusarapu 30-Sep-15 8:27am

I also used openrowset but it is also getting error.Any help to my problem would be much appreciated

CHill60 30-Sep-15 16:28pm

I would go back to using OpenRowSet and deal with the error produced from that - you can use the Improve question link to change your code and share the error

CHill60 30-Sep-15 16:31pm

Just spotted your earlier post which has also been answered IErrorInfo.GetDescription failed with E_FAIL(0x80004005).[^]
Please don't repost questions

1 solution

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)

Maciej Los · Answer 1 · 2015-09-30T10:30:00

Solution 1

Sorry... Wrong approach!

Use safe class: SqlBulkCopy[^].

How to use it (step by step)?
1. Create connection [^]to MS Access database
2. Load data from MS Access database into DataTable[^]
3. Disconnect MS Access database (do NOT remove datatable object!)
4. Create connection[^] to MS SQL Server database
5. Use datatable object within SqlBulkCopy.WriteToServer[^] method to copy data between databases
6. Disconnect MS SQL Server

That's all!

Posted 30-Sep-15 10:30am

Maciej Los

Updated 30-Sep-15 20:17pm

v3

Comments

TarunKumarSusarapu 1-Oct-15 1:43am

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. I will try this one also

Maciej Los 1-Oct-15 2:12am

I'm not talking about linked server. Please, read my answer carefully. I'm recommending you to connect to MS Access database and load data into DataTable object, then connect to MS SQL Server and use SqlBulkCopy.WriteToServer method to copy data between databases. Got it? See updated answer.

TarunKumarSusarapu 1-Oct-15 2:44am

K Thanks for ur info I will try in your way and tell u soon

Maciej Los 1-Oct-15 4:41am

Please, notify me about your results.

TarunKumarSusarapu 5-Oct-15 2:32am

Boss I want to copy total database from ms access to sql in which it has to create its own table and copy that data into sql like insert * into query. Upto 2nd step it is ok we can retrieve the data from access table and load that data into datatable but to copy that data into sql we need to create that same table with all columns in sql then use write to server method.

I have more than 1000 tables in my access mdb it takes a more time to create every table in sql and copy that database.

Maciej Los 5-Oct-15 2:56am

I'm not your Boss ;)
Use connection.GetSchema method to get the list of MS Access database tables. Use the same method to get details about each table to be able to re-create them on sql server side.

Over 1000 tables in MS Access database sounds dangerous...

TarunKumarSusarapu 5-Oct-15 3:05am

Would u please elaborate it for one table here is the sample query
select * into [ACTYPE] IN ODBC; Driver={SQL Server};Server='PC01';Initial Catalog=TARR;Persist Security Info=True;User ID= 'sa';Password='123' FROM [ACTYPE]

TarunKumarSusarapu 6-Oct-15 5:16am

I got this error when while executing the code
//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 [" + con + "].[" + tableName + "] FROM [" + tableName + "]", connection);
//OleDbCommand cmd = new OleDbCommand("select [[" + tableName + "].*] into [" + con + "].[" + tableName + "] From [" + tableName + "]");
cmd.Connection = connection;
cmd.ExecuteNonQuery();
connection.Close();

select * into [ODBC;Driver=SQL Server;Data Source=serverpc\sqlexpress,52120;Initial Catalog=TARUN_DB;Persist Security Info=True;User ID=vipl-user;Password=vipl-user!@#$%;MultipleActiveResultSets=True].[ACTYPE] FROM [ACTYPE]

Maciej Los 6-Oct-15 10:51am

Please, read my answer carefully! You're doing it wrong. Do not focus on your solution...