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

Configuring Database Mail in SQL Server 2005

Hi

I have created profiles in my database as mentioned below. now i want to delete the profile created in database.

can some one tell me how to proceed furthur.

i need to delete them permenantly.

EXECUTE msdb.dbo.sysmail_add_account_sp @.account_name = 'MyMailAccount', @.description = 'Mail account for Database Mail', @.email_address = 'makclaire@.optonline.net', @.display_name = 'MyAccount', @.username='makclaire@.optonline.net', @.password='abc123', @.mailserver_name = 'mail.optonline.net' --

EXECUTE msdb.dbo.sysmail_add_profile_sp @.profile_name = 'MyMailProfile', @.description = 'Profile used for database mail' --EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @.profile_name = 'MyMailProfile', @.account_name = 'MyMailAccount', @.sequence_number = 1

-

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @.profile_name = 'MyMailProfile', @.principal_name = 'public', @.is_default = 1 ; Regardskrishna

refer the below link which contains the list of stored procedures in database mail,

http://msdn2.microsoft.com/en-us/library/ms177580.aspx

i think you can make use of sysmail_delete_profile_sp stored procedure........

sysmail_delete_profile_sp { [ @.profile_id = ] profile_id | [
@.profile_name = ] 'profile_name' }
[, [ @.force_delete = ] force_delete ]

|||

Hi Deepak,

Thanks for the reply,

how do i configure email notification in SQL 2005 server, so that i get notification on two different mail id,s

ex:

krishna@.abc.com and krishna@.abchelp.com

regards

krishna

|||

you can make use of,

EXEC msdb.dbo.sp_send_dbmail
@.profile_name = 'AdventureWorks Administrator',
@.recipients = 'danw@.Adventure-Works.com',
@.body = 'The stored procedure finished successfully.',
@.subject = 'Automated Success Message' ;

if you want to mail multiple receipients just use semicolon in the

@.receipients='krishna@.abc.com' ;'krishna@.abchelp.com'

refer the below link for further assistance,

http://msdn2.microsoft.com/en-us/library/ms190307.aspx