2 Replies Latest reply: Sep 5, 2013 9:17 AM by JeffJon RSS

    Object Open Error while trying to export the vDataFact table

    user8714121

      I am getting an error when trying to export the vDataFact table to a flat file.  I have been able to do this several time before with the segment tables but would like to run this against vDataFact

       

      The error in the error log is:

         ERROR:

             Code............ -2147217915

             Description..... Object was open.

       

      The screen error is:

            The connection cannot be used to perform this operation.  It is either closed or invalid in this context.

       

      The line that appears to cause the issue is: Set rssql = API.DataWindow.DataAccess.farsKeySet(CStr(strSQL))

       

      Here is my complete script:

      Sub webdatafacts()

       

      'Declare Variables

         Dim objFileSystem 'File System

         Dim strFileName 'File name

         Dim ObjTextFile

              

      'SQL variables

        Dim strSQL ' Placehold for SQL Query

        Dim rssql 'Placeholder for result set for sql query execution

       

      'SQL Statement

                  strSQL = "SELECT PartitionKey, Account, AccountX, Entity, EntityX, ICP, ICPX, UD1, UD1X, UD2, UD2X, UD3, UD3X, UD4, UD4X, Amount  FROM vDataFact" 

                  set rsSQL = DW.DataAccess.farsFireHose(strSQL,False)

      'Create the filesystem object

                  Set objFileSystem = CreateObject("Scripting.FileSystemObject")

                  Set ObjTextFile = objFileSystem.OpenTextFile(DW.Connection.PstrDirInbox & "\VDataFactExport.txt", 2, TRUE, 0)  

                  ObjTextFile.Close

       

                  If Not rssql.EOF and Not rssql.BOF Then

                  Set objTextFile = objFileSystem.OpenTextFile(DW.Connection.PstrDirInbox & "\VDataFactExport.txt", 2, FALSE, 0)

                  Do Until rssql.EOF

       

      'Loop across all fields in recordset delimiting them with a tab key

                  For i = 0 To rssql.Fields.Count -1

                 strString = StrString & rssql(i) & vbTab

                 Next

       

      'Output the resulting string to the text file

                ObjTextFile.WriteLine (strString)

       

      'Reset the string to empty for the next record

               strString = ""

       

      'Move to the next record in the recordset

                    rssql.MoveNext

                    Loop

       

      'Close the recordset and the text file

                   rssql.Close

                   ObjTextFile.Close

          End If

       

      'Destroy the instance of the recordset from memory

            Set rssql = Nothing

                 

      End Sub