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.
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,
-- '''' 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
--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.
First, please run the query on the linked server directly and check whether we can get the result. If the codes can run properly, please run them via linked server again, check the detail error message in SQL Server error log and Windows application log, and post the error message here for analysis.
View the SQL Server Error Log (SQL Server Management Studio)
View the Windows Application Log (Windows)
TechNet Community Support
- Proposed as answer by Allen Li - MSFTMicrosoft contingent staff, Moderator Thursday, July 18, 2013 3:06 PM
- Marked as answer by Allen Li - MSFTMicrosoft contingent staff, Moderator Wednesday, July 24, 2013 3:14 AM