June 3, 2008 at 3:04 pm
I'm attempting to run the following query, but I'm getting a "timeout expired" error:
SELECT derivedtbl_1.DAY, derivedtbl_1.CHARGETYPE, derivedtbl_1.CALLTYPE, derivedtbl_1.CELLID, derivedtbl_1.PRE_POST_INDICATOR,
derivedtbl_1.NO_OF_CALLS, derivedtbl_1.TOTAL_VOLUME, derivedtbl_1.ETL_TIME, derivedtbl_1.SOURCE_SYSTEM
FROM dbo.vwMaxDay_EDW INNER JOIN
OPENQUERY(edw, 'select * from dwdata.dwh_nams_stats') AS derivedtbl_1 ON dbo.vwMaxDay_EDW.MaxDay < derivedtbl_1.DAY AND
dbo.vwMaxDay_EDW.SOURCE_SYSTEM = derivedtbl_1.SOURCE_SYSTEM
- I am running SQL Server Management Studio
- The "derivedtbl_1" is an Oracle linked server table
- The "dbo.vwMaxDay_EDW" is a SQL Server view
- If I query either the view or the oracle table separately, the query runs fine
Any help would be greatly appreciated.
Danny S.
June 3, 2008 at 8:15 pm
Try this...
SELECT A.*
FROM (
SELECT derivedtbl_1.DAY,
derivedtbl_1.CHARGETYPE,
derivedtbl_1.CALLTYPE,
derivedtbl_1.CELLID,
derivedtbl_1.PRE_POST_INDICATOR,
derivedtbl_1.NO_OF_CALLS,
derivedtbl_1.TOTAL_VOLUME,
derivedtbl_1.ETL_TIME,
derivedtbl_1.SOURCE_SYSTEM
FROM OPENQUERY(edw, 'select * from dwdata.dwh_nams_stats') AS derivedtbl_1
) A
INNER JOIN dbo.vwMaxDay_EDW
ON dbo.vwMaxDay_EDW.MaxDay < A.DAY AND
dbo.vwMaxDay_EDW.SOURCE_SYSTEM = A.SOURCE_SYSTEM
June 3, 2008 at 8:29 pm
I'll give this a shot and let you know - thank you.
June 4, 2008 at 11:31 am
I was using the above sql to create a view that could be referenced by SSIS. The proffered modified sql didn't work either - still got the timeout expired error.
So I used this sql in a query instead of a view, and it ran fine - took 11 minutes (about 6 million records being checked).
Still don't know how to resolve the timeout in the view.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply