VS 2015 EF 6.0
I am trying to automate a horrendous job: create a multi-sheet excel workbook where each sheet is the result of a different query. First, if there is an existing, automatic way of doing this without rolling my own solution please let me know. Otherwise:
I build up the collection of per-sheet queries then for each query string I do the following:
...
"query" passed in as string above
...
DataTable dt;
using (var db = new MyDbEntities()) {
db.Database.CommandTimeout = 180;
try {
dt = db.DataTable(query);
} catch (Exception e) {
throw new Exception("Datatable creation failed: " + e.Message);
}
}
...
create spreadsheet-sheet
...
I have one query that causes the exception: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding
I am pretty sure that I am getting the timeout way before the 180 second timeout expires. Timing it with my watch suggests the DataTables call is ignoring the CommandTimeout. It seems to be timing out after about 30 seconds.
EDIT
The query in question was requested. It follows. It was inherited. It is ugly. It is a slow pig. It is querying from a remote, linked Oracle DB and a couple of "local" DBs. When run from SSMS it takes a few minutes to complete, but it does complete.
SELECT
UPPER(LOCAL_CONSUMER_TABLE.machine) AS machine,
UPPER(LOCAL_CONSUMER_TABLE.personId) AS personId,
Persons.LastName AS lastName,
Persons.FirstName AS firstName
FROM
(
SELECT
GROUP_USER_ACCOUNT.UAC_ITU__ID
FROM
ORACLE_LINKED_SERVER..CONSUMER_TABLE.GROUP_USER_ACCOUNT AS GROUP_USER_ACCOUNT
WHERE GROUP_USER_ACCOUNT.UAC_ITU__ID IN
(
SELECT
GROUP_USERVIEW.ITU_ITU__ID
FROM
LOCAL_CONSUMER_TABLE.dbo.LoginInfoFinal AS LOCAL_CONSUMER_TABLE
INNER JOIN ORACLE_LINKED_SERVER..CONSUMER_TABLE.ITG_USER AS GROUP_USERVIEW
ON UPPER(LOCAL_CONSUMER_TABLE.personId) = UPPER(GROUP_USERVIEW.ITU_personId)
)
AND GROUP_USER_ACCOUNT.UAC_DATE_REVOKED IS NULL
GROUP BY
GROUP_USER_ACCOUNT.UAC_ITU__ID
HAVING COUNT(GROUP_USER_ACCOUNT.UAC_ITU__ID) > 1
) AS USER_ACCOUNT
INNER JOIN ORACLE_LINKED_SERVER..CONSUMER_TABLE.ITG_USER AS GROUP_USERVIEW
ON USER_ACCOUNT.UAC_ITU__ID = GROUP_USERVIEW.ITU_ITU__ID
INNER JOIN LOCAL_CONSUMER_TABLE.dbo.LoginInfoFinal AS LOCAL_CONSUMER_TABLE
ON UPPER(LOCAL_CONSUMER_TABLE.personId) = UPPER(GROUP_USERVIEW.ITU_personId)
INNER JOIN FACILITY.Personnel.Persons AS Persons
ON UPPER(LOCAL_CONSUMER_TABLE.personId) = UPPER(Persons.personId);
