1 Reply Latest reply on Jan 13, 2010 11:26 AM by 746143

    import script (SQL to FDM) - ConnectionRead error

    746143
      Hi,

      I have written an import script in FDM to load data from a SQL view. This script works fine when loading approx 5,000 rows, however, fails when trying to load 20k+ rows

      The error message I am receiving is:

      Detail: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()).
      At line: 72

      Line 72 = rs.movenext 'moves to the next record in the SQL view.


      I have tried ignoring a few columns in the import script (to reduce the data volume) - this works, however I need all columns.


      Below is a copy of the script:

      Function COG1_BrandPL(strLoc, lngCatKey, dblPerKey, strWorkTableName)

      Dim cnSS 'ADO connection object
      Dim strSQL 'SQL string
      Dim rs 'Source (Cognos) recordset
      Dim rsAppend 'FDM recordset

      'Initialize ADO objects
      Set cnSS = CreateObject("ADODB.Connection")
      Set rs = CreateObject("ADODB.Recordset")

      'Open FDM table recordset for appending
      Set rsAppend = DW.DataAccess.farsTableAppend(strWorkTableName)

      'Connect to SQL Server database
      cnSS.open "Driver={SQL Server};Server=XXXXXXXXX;OLE DB Services =-2;Database=XXXXXX;Uid=USER;Pwd=PASSWORD;"

      'Create SQL query string
      strSQL = "Select * FROM Corporate_PLUOP_view"

      'Open source recordset
      rs.Open strSQL, cnSS

      'Check for data in source system
      If rs.bof AND rs.eof Then
      RES.PlngActionType = 2
      RES.PstrActionValue = "No records to load!"
      COG1_BrandPL = False
      Exit Function
      End If

      'Loop through source records and append to FDM table
      If Not rs.bof And Not rs.eof Then
      Do While Not rs.eof

      rsAppend.AddNew
      rsAppend.Fields("PartitionKey") = RES.PlngLocKey
      rsAppend.Fields("CatKey") = lngCatKey
      rsAppend.Fields("PeriodKey") = dblPerKey
      rsAppend.Fields("DataView") = "YTD"

      'From Cognos
      rsAppend.Fields("UD3") = rs.fields("Cube").value
      rsAppend.Fields("Entity") = rs.fields("Entity").value
      rsAppend.Fields("UD1") = rs.fields("Custom1").value
      rsAppend.Fields("Account") = rs.fields("Account").value
      rsAppend.Fields("ICP") = rs.fields("ICP").value
      rsAppend.Fields("UD2") = rs.fields("Custom2").value
      rsAppend.Fields("Amount") = rs.fields("QPR_Value").value
      rsAppend.Update
      rs.movenext
      Loop
      End If

      rs.close
      cnSS.close

      'Assign Return value
      COG1_BrandPL = True

      End Function




      The only other thing I have tried is copying and pasting the script to Excel.... This works for ALL rows... so seems to be a communication issue between FDM and SQL?

      Any ideas?
        • 1. Re: import script (SQL to FDM) - ConnectionRead error
          746143
          Bit more info:

          I am using FDM 9.3.1
          SQL Server 2005 on Windows 2003 Enterprise x64

          Here's a copy of the FDM error log:


          ** Begin FDM Runtime Error Log Entry [2010-01-13-12:14:55] **
          -------------------------------------------------------------
          ERROR:
          Code......................................... -2147467259
          Description.................................. [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()).
          At line: 75
          Procedure.................................... clsImpProcessMgr.fLoadAndProcessFile
          Component.................................... upsWObjectsDM
          Version...................................... 931
          Thread....................................... 12432

          IDENTIFICATION:
          User......................................... HFM-Cognos1
          Computer Name................................ DEFMDHY9A01
          App Name..................................... CognosTest
          Client App................................... WebClient

          CONNECTION:
          Provider..................................... ORAOLEDB.ORACLE
          Data Server..................................
          Database Name................................ HFM9UAT
          Trusted Connect.............................. False
          Connect Status.. Connection Open

          GLOBALS:
          Location..................................... COGNOS1
          Location ID.................................. 750
          Location Seg................................. 3
          Category..................................... ACTUAL
          Category ID.................................. 13
          Period....................................... Jun - 2009
          Period ID.................................... 30/06/2009
          POV Local.................................... False
          Language..................................... 1033
          User Level................................... 1
          All Partitions............................... False
          Is Auditor................................... False

          Edited by: PaulScrivens on 13-Jan-2010 03:26