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.
I have double checked these conditions to make sure that it is not true:
a. Allow Inprocess is not checked for the provider
b. SQL Server service is started as a Local Service
c. The user is a regular user that is part of the Administrator’s group. (either local user or domain user)
d. User Account Control (UAC) is turned off
e. SQL Server Management Studio is opened as the regular user using Windows Authentication
I am suspecting that there may be syntax issues or the query is returning too many rows. But not 100% since I cant run those queries myself. Any other way I can proceed to troubleshoot?
Any recommendations will be helpful.