The two collations are very similar:

Latin1_General_CI_AS :- Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive

SQL_Latin1_General_CP1_CI_AS:- Latin1-General, case-insensitive,accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode...

This script could be a starting point:

http://www.sqlservercentral.com/scripts/tempdb/72007/

I think that MSDTC is the culprit here. You could try tracing it and see what's inside the log:

http://blogs.msdn.com/b/distributedservices/archive/2009/02/07/the-hidden-tool-msdtc-transaction-tracing.aspx

Another option is setting up a profiler trace to capture OLEDB events.

INSERT INTO srvr2.db2.dbo.tbl_a (col1, col2)

VALUES (9999, 8885)

If you open a new SSMS window and run this, it fails, right?

OK, so, let's see how you set up the...

Which statement is failing?

I understand what you're after. I just want to know if the statements work when run one at a time.

If they work, we have to focus on the distributed transaction,...

Have you tried issuing the statements one at a time? Do they work that way?

Have you tried adding some PRINT commands inside the CATCH block to see if they get executed? Level 11 should be handled by CATCH blocks.

This should do:

DECLARE @result nvarchar(4000)

DECLARE @tResults TABLE (

ProductVersion nvarchar(128)

)

INSERT @tResults

EXEC('SELECT CAST(SERVERPROPERTY(''ProductVersion'') AS nvarchar(128)) AS product_version')

SELECT @result = ProductVersion

FROM @tResults

SELECT @result

Hope this helps,

Gianluca

Which error severity are you getting? If above 20, it could jump off the CATCH block.

Is there any additional error message or the "Severe error" thing is all you get back?

Which database product are you linked to? Is it SQL Server?

EDIT: Figured out, you are using SQLNCLI. Sorry, please ignore.

I would do it similar to this:

CREATE PROCEDURE [dbo].[UpdInsDeltrans]

AS

BEGIN

SET NOCOUNT ON

SET XACT_ABORT ON

BEGIN TRY

...

Can you post the code? (or at least part of it)

sql_novice_2007 (3/14/2011)

---

My question is how can I...

I'm sorry, I don't understand what error you are getting and against what code.

Can you clarify please?