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

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

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

connec to sql express

at work, I'm trying to use Native client under ODBC to connect to another pc have the SQl Expess server, but never get connect. the message said the sql express may not allow remote connect.

I run a test, install sql express at home and install native client on the second pc and get connect easily.

So what do I miss here. Please help. thanks

You need to explicitly enable remote connections for SQL Express since they are off by default. You can enable them during install using the DISABLENETWORKPROTOCOLS flag:

(From template.ini)

;--
; The DISABLENETWORKPROTOCOLS switch is used to disable network protocol for SQL Server instance.
; Set DISABLENETWORKPROTOCOLS = 0; for Shared Memory= On, Named Pipe= On, TCP= On
; Set DISABLENETWORKPROTOCOLS = 1; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= Off
; Set DISABLENETWORKPROTOCOLS = 2; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= On

; Note: DISABLENETWORKPROTOCOLS if not specified has the following defaults.
; Default value for SQL Server Express/Evaluation/Developer: DISABLENETWORKPROTOCOLS =1
; Default value for Enterprise/Standard /Workgroup: DISABLENETWORKPROTOCOLS =2

or after install using TSQL or following this blog:

http://support.microsoft.com/kb/914277

Thanks,
Sam Lester (MSFT)

|||

Tried it, still not connect. Any other way? Thanks