MS SQL Server - Exception vs RaiseError

Below is just an observation that needs further investigation.

It appears that while using ODBC to connect to MS SQL Server there's a difference between RAISERROR and an occurence of an exception.

Performing a SELECT 1/0 within a stored procedure called as Post-SQL query results in an error like:

Database driver error...
...
FnName: Execute Direct -- [Informatica][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Divide by zero error encountered.]

Now, this is perfectly fine. An error occured, the session has failed. But what if we need to store some audit information in a log table in case of error in stored procedure? It's possible with TRY...CATCH. Whenever an error occurs, the CATCH block is executed. We can log the information and do a RAISERROR afterwards to fail the session.

But the session never fails. There's no track of RAISERROR in session log, the session completes successfully.

UPDATE:

The issue is caused with returning any dataset. Any print statement or result set seems to cover the error. Even the information about number of affected rows. Hence, the solution is to:

• remove any SELECT / PRINT statements from within the Stored Procedure
• suppressing the information about affected rows by using the SET NOCOUNT ON

The mapping is potentially unsafe and cannot be imported.

So, you got this error while trying to import your mapping to Powercenter:

The mapping m_your_mapping is potentially unsafe and cannot be imported.

What is wrong and how can it be fixed? What's the root cause? Hard to tell - the error message does not explain a lot.

The Cause
Most likely the XML file has been altered outside Powercenter. It's quite common to export and to some edits in XML before importing. Some renaming, replacing some paths or parameters. However some transformations or mappings have this

CRCVALUE="123123123"

property. If the XML file has been altered, it's very much possible the CRC is no longer correct.

The Solution
While importing the XML try to use the "-s" option. It can't be found in the Informatica Command Reference (at least in all versions I've tried) but it seems to work. Instead of the usual command:

pmrep objectimport -i /pathtoyourxmlfile/m_your_mapping.xml -c controlFile.txt - l output.log

try using

pmrep objectimport -s -i /pathtoyourxmlfile/m_your_mapping.xml -c controlFile.txt - l output.log

Remarks
This worked in my case. And I have no idea what the "-s" stands for and what actually happens here...