1

I Connected to Oracle as Linked Server from Sql Server 2008 R2 after Executing 20-30 Min i getting following error:

    OLE DB provider "OraOLEDB.Oracle" for linked server "CBSLINKED"
 returned message "ORA-12170: TNS:Connect timeout occurred".
    Cannot initialize the data source object of OLE DB provider
 "OraOLEDB.Oracle" for linked server "CBSLINKED".

Script:

set @query = '   
      INSERT INTO dbo.tbl_MyTable  
 (  
  AccNo,  
  BranchCode,  
  BranchName,  
  CustomerName,  
  ScanDate,  
  TranCount,  
  TranAmount,  
  AccType  
 )    
   select AccNo,BranchCode,BranchName,LongName,  
  '''+CONVERT(CHAR(10),@RunningDate,111 )+'''   
   ,TranCount,TranAmount,AccType  

  from OPENQUERY(CBSLINKED,''select TBM.AC as AccNo,  
      TBM.BC as BranchCode ,CM.CNM, COUNT(*) TranCount,    
  SUM(TBM.AMT) TranAmount, TBM.AH  as AccType,CM.CNM as LongName  
     ,BM.BR_NM as BranchName  
     from BKP.T_2014 TBM  
     LEFT OUTER JOIN BKP.ACMST AM  
     ON TBM.AC=AM.AC   and TBM.BC=AM.BC  
     and TBM.AH=AM.AH  
     LEFT OUTER JOIN BKP.BCMST  BM  
     ON TBM.BC=BM.BC  
     LEFT OUTER JOIN BKP.CLMST CM  
     ON TBM.CN=CM.CNO  
   where    TBM.AMT>=''''' +CONVERT(CHAR(50),@MinAmt,111)+ ''''' and
   CM.CNM NOT LIKE ''''CENTRALISE%'''' and  
   TBM.NR NOT LIKE ''''%Del%'''' and  
      CAST(TBM.DT as DATE) = '''''+CONVERT(CHAR(10),@RunningDate,111 )+'''''   
   AND TBM.DC= ''''D''''   
    AND (TC=''''CP'''' OR TC=''''CR'''')  
    GROUP BY  
  TBM.BC,TBM.AH,  
  TBM.AC,CM.CNM,BM.BR_NM  
   '')where AccType IN (select AccTypeCode from  tbl_ACCMAST where AccType IN (  
  select distinct AccType from RuleMaster where RuleID=6))  
     '  
   print @query  
  execute sp_executesql @query

and

My <code>sp_configure</code> values Use Control+Mouse Scroll to enlarge image

Stephan
  • 41,764
  • 65
  • 238
  • 329
Dgan
  • 10,077
  • 1
  • 29
  • 51
  • http://technet.microsoft.com/en-us/library/ms186839(v=sql.105).aspx there's an entry for timeout for linked servers. Additonally, if it's running for 20-30 mins then timing out, I'd re-evaluate your approach and maybe execute the query in batches using rownum values. – Dave Brown Sep 09 '14 at 08:14
  • @DaveBrown you can see in image i Already set remote query timeout to infinite using `sp_configure 'remote query timeout', 0 go reconfigure with override go ` – Dgan Sep 09 '14 at 08:19
  • Yes, but have you also considered that it may be timing out on execution in the Oracle engine and returning that back to SQL Server? Just because the linked server is configured to allow infinite timeouts doesn't mean Oracle is suddenly going to over-ride settings (not saying this IS the case but you have to consider the Oracle DB in this). – Dave Brown Sep 09 '14 at 08:24
  • so you mean to say i have to set connection/query timeout in Oracle 10g too where i connect as linked server – Dgan Sep 09 '14 at 08:30
  • 1
    I can't know that for sure, but since you've got the Linked Server set up correctly, it's all I can suggest. Many years ago we had a similar situation on a development Oracle box - once the DBA's lifted the query restrictions the problem went away. It's, at best, a guess but it fits with the message. I still strongly urge anyone that has queries that take that long to execute to do it in a different way - return smaller "chunks" back with RowNum (there's a lot of examples on stackoverflow specifically for Oracle on this) and insert them in smaller amounts. – Dave Brown Sep 09 '14 at 12:00

1 Answers1

1

I configured two things and its works for me

1. Set Remote Query Timeout to infinite

sp_configure 'remote query timeout', 0  
go  
reconfigure with override  
go

2. Set Remote Login Timeout to 2147483647

sp_configure 'remote login timeout (s)', 2147483647
go
reconfigure with override
go

Dgan
  • 10,077
  • 1
  • 29
  • 51