SQL with Manoj

 
Today while running an ETL I encountered an error I’d never seen before. The Informatica ETL triggers a Stored Procedure with 2 parameters which takes table names and creates a dynamic MERGE statement. The SP was failing and the Informatica Monitor was showing following error:

Severity: ERROR
Thread: MAPPING
Message Code: CMN_1022
Message: Database driver error…
CMN_1022 [EXEC spMergeTables ‘STG.ABCtblXYZ’,’ABC.tblXYZ’
Microsoft SQL Server Native Client 11.0: Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.
SQL State: 42000 Native Error: 2754
State: 1 Severity: 16
SQL Server Message: Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.

Database driver error…
Function Name : ExecuteDirect
]

Thus, somewhere within this SP the error is being generated with severity greater than 18.
 

So, to identify the cause I tried to execute the above MERGE statement directly and I got the same error:

EXEC spMergeTables 'STG.ABCtblXYZ','ABC.tblXYZ'

(0 row(s) affected)
Msg 2754, Level 16, State 1, Procedure spMergeTables, Line 107
Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.

In the SP after creating the dynamic string for the MERGE statement the SP executes the string variable, so I replaced the EXEC(@str), with PRINT(@str), to PRINT the MERGE statement rather than executing it, which came as follows:

MERGE ABC.tblXYZ AS tgt
USING STG.ABCtblXYZ AS src
ON tgt.[PK_ID] = src.[PK_ID] 
WHEN MATCHED THEN  
	UPDATE SET <tgt.column = src.column>
WHEN NOT MATCHED BY TARGET THEN 
	INSERT (<Columns list>) 
	VALUES (<column values>);

On executing the above MERGE statement, I got the actual error which is as follows:

Msg 823, Level 24, State 12, Line 1
The operating system returned error 21(The device is not ready.) to SQL Server during a write at offset 0x000000270e0000 in file ‘F:\TempDB\tempdb2.ndf’.
Additional messages in the SQL Server error log and system event log may provide more detail.
This is a severe system-level error condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB).
This error can be caused by many factors; for more information, see SQL Server Books Online.

So, its clear now that there was something wrong with the tempdb file and the OS is returning error with severity as 21, which is greater than 18.
 

As this was a tempdb file issue, thus I restarted the SQL Server services and reconnected the SSMS client, and re-ran the Job, and this time the ETL got executed successfully 🙂