OleDbConnection.Open() throws a "No error message available" error before even contacting the SQL Server

Question

Hi all

Using a connection string of

"Provider=SQLOLEDB;Data Source=localhost;User ID=foo;password=bar;Database=CodeLists;Pooling=true;Min Pool Size=20;Max Pool Size=30;"

I get the following stack trace

System.Data.OleDb.OleDbException: No error message available, result code: -2147024770(0x8007007E). at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper) at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open()

I get this error even if I change the server URL from localhost to hkfjhuidhf which is invalid, so I assume it's an issue on the server with respect to OleDb connection/setup and/or the MDAC.

The server is Windows Server 2003 running the latest service pack and the MDAC is 2.8 SP2.

The code I'm using is:

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void run_Click(object sender, EventArgs e)
    {
        output.Text = string.Empty;
        try
        {
            OleDbConnection connection;
            try
            {
                connection = new OleDbConnection(conString.Text);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error creating connection");
                put(ex.ToString());
                return;
            }

            OleDbCommand command;
            try
            {
                command = connection.CreateCommand();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error creating command");
                put(ex.ToString());
                return;
            }

            command.CommandType = CommandType.Text;
            command.CommandText = "select top 10 * from " + table.Text;

            if (connection.State != ConnectionState.Open)
                connection.Open();

            OleDbDataReader reader;

            try
            {
                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    string @out = string.Empty;
                    while (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            @out += reader[i] + ", ";
                        }
                    }
                    put(@out);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                put(ex.ToString());
            }
            finally
            {
                connection.Close();
            }
        }
        catch (Exception ex)
        {
            put(ex.ToString());
        }

    }

    private void put(string message)
    {
        output.Text += message+Environment.NewLine;
    }
}

And this falls over at the connection.Open()

Does anyone have any ideas? I've reinstalled MDAC from the inf file, however I've read some articles slating SP2 of MDAC 2.8 with regards to .Net code.

Any and all input is extremely welcome.

Answer 1

If you are connecting to SQL you should use SqlClient:

var conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = connectionString;
conn.Open();

As already suggested, try replacing your Data Source param with "MyServer\SQLExpress". The error could be occurring because it can't find the SQL instance.

Answer 2

It sounds like one or more of the OLEDB components is missing or broken.

When I had a similar problem, I found that reinstalling MDAC didn't work - something which the first install did prevented subsequent reinstalls from fixing missing references/files.

I ended up fixing it by using RegMon to find the failing registry calls, then comparing the keys on which calls failed to a working machine. This will give you a pointer to a missing DLL. I ended up manually re-registering and editing registry entries for 5 or 6 DLLs before I got it working.

It might be worth also asking on serverfault.