1 Reply Latest reply on Aug 17, 2012 2:50 PM by skyhanger

    FDM Integration Script Error

    Sarilla
      Hi Guru's

      When i want to pull the data from SQL table using below integration Script from admin guide, its showing <font color="red">-2147217865 Data access Error at line15 (Line 15:Set rsAppend = DW.DataAccess.farsTable(strWorkTableName) </font>

      I tried with both web client and workbench. i got same error message.
      (FYI: UDL test connection is succeeded)

      Please help me.

      SQL server name: DEV
      Database name: FDM
      Sql Table name: SDR

      SDR Table contains Below Data Example:

      Entity Account ICP Custom1 Custom2 Custom3 Custom4 Amount
      India,      Extsales,      [Icp None],      Nocc,      No Cust,      None,      None,      50000
      India,      rent,      [Icp None],     Nocc,      No Cust,      None,      None,      20000


      Intigration Script:

      Function SQLIntegration(strLoc, lngCatKey, dblPerKey, strWorkTableName)
      '------------------------------------------------------------------
      'Hyperion FDM Integration Import Script:
      'Created By: admin
      'Date Created: 04/19/2012 2:18:39 PM
      'Purpose: Pull data directly from SQL DB
      '------------------------------------------------------------------
      Dim objSS 'ADODB.Connection
      Dim strSQL 'SQL String
      Dim rs 'Recordset
      Dim rsAppend 'tTB table append rs Object
      'Initialize objects
      Set cnSS = CreateObject("ADODB.Connection")
      Set rs = CreateObject("ADODB.Recordset")
      Set rsAppend = DW.DataAccess.farsTable(strWorkTableName)
      'Connect To SQL Server database
      cnss.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=FDM;Data Source=DEV;"
      'Create query String
      strSQL = "Select * "
      strSQL = strSQL & "FROM SDR "
      'Get data
      rs.Open strSQL, cnSS
      'Check For data
      If rs.bof And rs.eof Then
      RES.PlngActionType = 2
      RES.PstrActionValue = "No Records To load!"
      Exit Function
      End If
      'Loop through records And append To tTB table In location’s DB
      If Not rs.bof And Not rs.eof Then
      Do While Not rs.eof
      rsAppend.AddNew
      rsAppend.Fields("PartitionKey") = RES.PlngLocKey
      rsAppend.Fields("CatKey") = RES.PlngCatKey
      rsAppend.Fields("PeriodKey") = RES.PdtePerKey
      rsAppend.Fields("DataView") = "YTD"
      rsAppend.Fields("CalcAcctType") = 9
      rsAppend.Fields("Amount") = rs.fields("dblAmt").Value
      rsAppend.Fields("Desc1") = rs.fields("txtAcctDes").Value
      rsAppend.Fields("Account") = rs.fields("txtAcct").Value
      rsAppend.Fields("Entity") = rs.fields("txtCenter").Value
      rsAppend.Update
      rs.movenext
      Loop
      End If
      'Records loaded
      RES.PlngActionType = 6
      RES.PstrActionValue = "SQL Import successful!"
      'Assign Return value
      SQLIntegration = True
      End Function


      <font color="red"> BELOW IS THE ERROR LOG </font>

      Error Log:

      ** Begin FDM Runtime Error Log Entry [2012-07-16-01:57:58] **
      -------------------------------------------------------------
      ERROR:
      Code............................................. -2147217865
      Description...................................... Table does not exist.

      Procedure........................................ clsDataAccess.farsTable
      Component........................................ upsWDataWindowDM
      Version.......................................... 1111
      Thread........................................... 8252

      IDENTIFICATION:
      User............................................. admin
      Computer Name....................................xxxx
      App Name......................................... xxxxx
      Client App....................................... WorkBench

      CONNECTION:
      Provider......................................... SQLOLEDB
      Data Server...................................... xxxxx
      Database Name.................................... xxxxx
      Trusted Connect.................................. False
      Connect Status.. Connection Open

      GLOBALS:
      Location......................................... India
      Location ID...................................... 751
      Location Seg..................................... 4
      Category......................................... actual
      Category ID...................................... 13
      Period........................................... Jan - 2012
      Period ID........................................ 1/31/2012
      POV Local........................................ False
      Language......................................... 1033
      User Level....................................... 1
      All Partitions................................... True
      Is Auditor....................................... False


      regards
      Sarilla