Connect SQL server 2005 with ODBC drive “SQL Native Client"

Hi,

When I'm using SQL server 2005, and ODBC drive “SQL Native Client” I get an error when executing the below given code. Error Message: “Transaction cannot start because more than one ODBC connection is in use.”

But this works well with SQL Server 2000 with ODBC drive "SQL Native Client" and in SQL server 2005 also when I used ODBC drive “SQL Server”.

I used aTrgConn.Execute(“Begin Transaction”) and it's working properly. But unfortunately I cannot use this method, because I want to run the same code with Oracle ODBC as well.

Is this a known issue in SQL Native client drive with SQL server 2005? Do you know a solution for this?

Please Help..............

Thanks in advance.

Code:

Dim aTrgConn As New ADODB.connection
Dim aCmdIns As New ADODB.Command
Dim aCmdUpd As New ADODB.Command

aTrgConn.Open sConnStr

Set aCmdIns.ActiveConnection = aTrgConn
Set aCmdUpd.ActiveConnection = aTrgConn

aTrgConn.BeginTrans

Why are you using two separate commands here? Couldn't you just use one command and reset the values to execute?|||

Hi John,

You are correct. but I'm afraid, it's very difficult to change the design at this stage. and it worked perfectly with SQL Server 2000.

Thank you very much for your response.

|||

Hi Again,

I think the problem comes when using the provider as MSDASQL.1 , with SQL server 2005 Native Client.

When I add "Provider=SQLNCLI;Integrated Security=SSPI;DataTypeCompatibility=80;MARS Connection=True;" to my connection string, it works correctly.

But again, I need the same coding to access oracle too. so SQLNCLI won't work with that.

It's really appriciated, If anyone could find a solution to this.

Thanks

|||

Hi,

I have the same problem with my application.

I began to use SQL Express 2005 with Native Client ODBC rather than MSDE and found the same error.

My application must works with several databases.

If anyone find a solution for this problem tell me too.

Thanks advanced,

Wendel

|||

Do you see the same problem with SQLNCLI OLEDB provider and not using MARS?

Thanks

Waseem