Umanga


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




Re: Connect SQL server 2005 with ODBC drive ※SQL Native Client"

John Gordon - MSFT


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







Re: Connect SQL server 2005 with ODBC drive ※SQL Native Client"

Umanga

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.






Re: Connect SQL server 2005 with ODBC drive ※SQL Native Client"

Umanga

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





Re: Connect SQL server 2005 with ODBC drive ※SQL Native Client"

wendelf

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





Re: Connect SQL server 2005 with ODBC drive ※SQL Native Client"

Waseem Basheer - MSFT

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

Thanks

Waseem