Openquery Hung at Same Row Number

Question

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.

In Microsoft SQL Server the query complete stops running once it reaches row 7833; however, the status of the query is 'Executing Query'. When I check the activity monitor it is 'stuck' in RUNNING task and SELECT command. The query does not stop until I cancel it and fully reboot my server. — cbrown92, Dec 19 '17 at 19:37
If you run the query on the `MyServer`, does it complete properly? — SS_DBA, Dec 19 '17 at 19:38
Yes it does - I made sure the large UNION ALL (and of course each individual query) query worked on this server first before moving it to SQL Server. I'm trying to process this query in Microsoft SQL Server to create a table and use it in SSRS. — cbrown92, Dec 19 '17 at 19:43

score 0 · Accepted Answer · answered Dec 19 '17 at 22:03

0

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).

answered Dec 19 '17 at 22:03

Michael O'Neill

946
7
22

Thank you for your response. You're correct it has to do with data conversion specifically of the date. I'm pulling from Oracle and this database has a different date format than what I'm pulling into Microsoft SQL Server. Thank you for the answer, it got me to reevaluate the date fields and it's working now. – cbrown92 Dec 19 '17 at 23:18
@cbrown92 Glad to hear. Implicit conversions of date values bites everyone eventually. Although I would like to clarify that it isn't that Oracle and SQL Server have "different date formats". Somewhere in the chain of data custody, either the date values are just strings (and not date data type) or the date values are date data types being converted into strings (and perhaps back into data data types) with unexpected/implicit format patterns. – Michael O'Neill Dec 20 '17 at 21:25
I greatly appreciate the followup, Michael! – cbrown92 Dec 22 '17 at 17:31

Openquery Hung at Same Row Number

1 Answers1