Search code examples
sqlsql-serveropenquery

Openquery Hung at Same Row Number

I'm attempting to do run an openquery in SQL Server 17 via a linked server to an Oracle connection feed. When I run the query posted below it gets stuck at row 7833 every single time.

Query:

SELECT 
    sys_ohi, 
    "Region", 
    sub_acct_no_ohi, 
    serv_cde_ohi, 
    connect_dte_ohi, 
    charge_amt_ohi 

FROM openquery (MyServer, ' 
(SELECT DISTINCT 
    sys_ohi, 
CASE 
    WHEN prin_ohi = ''1000'' THEN ''Seattle East'' 
    WHEN prin_ohi = ''1500'' THEN ''Seattle West'' 
    WHEN prin_ohi = ''2000'' THEN ''Oregon'' 
    WHEN prin_ohi = ''3000'' THEN ''Sacramento''
    WHEN prin_ohi = ''3500'' THEN ''San Francisco'' 
        END AS "Region", 
    sub_acct_no_ohi, 
    serv_cde_ohi, 
    connect_dte_ohi, 
    charge_amt_ohi 

FROM mytable_ohi

WHERE serv_cde_ohi IN (''INSTALL'')
AND connect_dte_ohi > trunc(to_date(''06-01-2017'',''MM-DD-YYYY'')))')

I have 36 different serv_cde_ohi IN (''INSTALL'') but I have created 36 UNION ALL queries for the different scenarios since attempting to put them within the 'IN' statement was terrible for performance.

I need to know why this query gets stuck at the same spot.

Solution

  • It is possible your UNION ALL declaration is dependent upon implicit data conversions to begin executing, then a data condition occurs during the cursor fetch where the implicit conversion fails in this manner you are observing.

    Eliminate all implicit dependencies. For each item in each SELECT, explicitly establish the data types (the same type for each item, ordinally of course).