Linked Server Job Error

Question

I have a scheduled job that is running everyday. The job step is to execute a stored procedure for hourly caching logic for Case load Data.

The job has been successful until yesterday afternoon when it failed with the following errors:

• Cannot get the column information from OLE DB provider "MSDASQL" for linked server "EDV_TBI". [SQLSTATE 42000] (Error 7350)
• OLE DB provider "MSDASQL" for linked server "EDV_TBI" returned message "ERROR: TEIID30504 COHORT_PROD_ENT_NICOE: 17008 TEIID11008:TEIID11004Error executing statement(s): [Prepared Values: [] SQL: SELECT DISTINCT g_0."SITE_ID", g_0."DIVISION_DMIS_ID_CODE" FROM "ENT"."CHCS_DIVISION" g_0, (SELECT MAX(g_1."LAST_CHANGED_TIMESTAMP") AS c_0, g_1."DIVISION_DMIS_ID_CODE" AS c_1 FROM "ENT"."CHCS_DIVISION" g_1, "ENT"."CHCS_HOSPITAL_LOCATION" g_2 WHERE g_1."SITE_ID" = g_2."SITE_ID" AND g_1."DIVISION_ID" = g_2."DIVISION_ID" AND g_1."DIVISION_INACTIVE_FLAG" = 'A' AND g_1."DIVISION_NAME" <>". [SQLSTATE 01000] (Error 7412). The step failed.

I am unable to test connections or even run the select statements as above ^ since I am not a mapped login on the remote Oracle server. I am an SA on the local SQL server though.

How should I proceed?
Any recommendations will be helpful.

Answer 1

It looks like an error message coming from the Oracle side of things. Although it looks a little funny that a SELECT statement would that kind of errors. But it talks about getting column information. Maybe it is a permission issue for the Oracle account.

I think you need to work with someone on the Oracle side of things.

By the way, how come you are using MSDASQL + Oracle ODBC rather than Oracles OLE DB provider?

Answer 2

Cannot get the column information from OLE DB provider "MSDASQL" for linked server "EDV_TBI". [SQLSTATE 42000] (Error 7350)

A common error in combination with Oracle, most causes are

• Option "Allow Inprocess" for the data provider isn't set
• A "fency" Orcale specific data type is used

See Oracle Support for more details: https://support.oracle.com/knowledge/Oracle%20Database%20Products/789481_1.html