link server error


  • Hi,


    I have one situation here, when ever this particular query is executed through linked server, the server hangs.The services get restarted.


    In the error log, I got a stack dump error.


    Query :

    select *


    from openquery(BRTOOMNI,'Select a.CUST_ID,c.LASTEXIT_DEDUPE,c.apprefno as APPREF, a.firstname AS FIRST_NAME,a.midname AS MIDDLE_NAME,a.lastname  as LAST_NAME,

    --a.ShortName Short_Name,

     a.DATEOF_BIRTH Contact_DOB,

     -- '''' as Contact_Status,

     a.ENTITY_TYPE As Contact_Type,

    a.SEX Gender, a.MARITAL Marital_Status, a.bank_name1 Bank_Account_1, a.bank_name2 Bank_Account_2,

    a.PANno PAN,b.ADDR_MOBILENO MOBILE,b.ADDR_PH1_NO As Res_Phone,b.ADDR_PH2_NO As Off_Phone,b.addr_ph1_no As Phone_1,

    b.addr_ph2_no As Phone_2,'''' As Phone_3,'''' As Phone_4, '''' As Phone_5,b.ADDR_EMAILID Email,

    b.ADDR_FIRSTLINE Address_Line_1,b.ADDR_SECONDLINE Address_Line_2,

    b.addr_thirdline As Locality,  b.ADDR_CITY CITY,'''' District,b.ADDR_STATE STATE,b.ADDR_ZIPCODE ZIP_CODE,

    b.ADDR_COUNTRY_DESC Country,'''' As Alt_Address_Line_1, '''' As Alt_Address_Line_2,   

    '''' as Alt_Mobile, '''' As Alt_Off_Phone, '''' as Alt_Res_Phone,'''' As Alt_Email, b.ADDR_LANDMARK LANDMARK,     

    '''' As Contact_Owner

    --'''' As ContactOwnerID,'''' As Status_Code,'''' As Layout

    From hlbp_exttable c, SLBP_ENTITY a, (select * from slbp_addr@uat2rpt where addr_type = ''CURRENT RESIDENCE'') b --,punit_appref e        

    Where c.customer = a.record_id and a.record_id = b.addr_parentid

    AND trunc(c.LASTEXIT_DEDUPE)=trunc(sysdate-1)

    --nd c.apprefno = e.appref

    --and c.apprefno = ''13297840''

    and a.CUST_ID is not null')


    The 3 tables given in the query has data on around 22 lakhs to 1 crore.


    Thursday, July 11, 2013 9:22 AM