9 Replies Latest reply on Sep 19, 2017 2:26 AM by Shaul.Bel

    Does Goldmine store the link for file attachments on outgoing email

    rfoerst Rookie

      Is there a  table in Goldmine that stores the origination path of a file attached on an outgoing email?

      I was looking for a way to determine where they are located?

      I came across a couple of references to the CONTSUPP table, but those look like INCOMING only ?

       

      Thanks

      Rick

        • 1. Re: Does Goldmine store the link for file attachments on outgoing email
          John Neighbors SSMMVPGroup

          ALL email attachment references are literally stored in the RFC822 field of the MAILBOX table.  That is, they are stored with EACH specific email that they are applicable to.

           

          The only reason CONSTUPP would make reference to email attachments might be if the "Save attachments as linked documents" option is checked (RECTYPE = 'L' in CONTSUPP).  As noted here:

           

           

          However, CONTSUPP links can contain far more than email attachments AND we generally encourage clients to UNCHECK this option noted above, otherwise their Links tab ends up with tons of essentially "garbage" entries that are of no value.

           

          Hope this feedback helps.

          • 2. Re: Does Goldmine store the link for file attachments on outgoing email
            Shaul.Bel Apprentice

            If I am not mistaking it is stored also in the mailbox table in the RFC822 field

            You will have to extract it with a query like

            SELECT

            CASE when charindex('Transfer-Encoding: base64',cast(cast(rfc822 as varbinary(max))  as varchar(max)),1)>0

            then substring(cast(cast(rfc822 as varbinary(max))  as varchar(max)) ,

            charindex('Transfer-Encoding: base64',cast(cast(rfc822 as varbinary(max))  as varchar(max)),1) +28 ,

            LEN(cast(cast(rfc822 as varbinary(max))  as varchar(max)))) end as Attachment, recid,FLAGS

            from Mailbox

            where cast(cast(rfc822 as varbinary(max)) as varchar(max)) like '%Transfer-Encoding: base64' +char(13) +char(10) + char(13) +char(10)

            + '%'

            and folder = 'Sent'

             

            This query needs more improvements but gives the idea how GM stores the path of email attachments.

            • 3. Re: Does Goldmine store the link for file attachments on outgoing email
              PJohns Apprentice

              You could create a table and show the location of these. Run this in SQL. It will show how many and where.

               

              CREATE TABLE [Email_Attachments] (
              [RECID] [varchar] (15) NOT NULL ,
              [USERID] [varchar] (8) NULL,
              [Attachment] [varchar] (max) NULL,
              [FileExists] [int]
              ) ON [PRIMARY]
              GO

              SELECT
              RECID,
              USERID,
              SUBSTRING(cast(cast(rfc822 as varbinary(max))as varchar(max)), PATINDEX('%Content-Disposition: attachment; filename=%', cast(cast(rfc822 as varbinary(max))as varchar(max))), CAST(MAILSIZE as int)-PATINDEX('%Content-Disposition: attachment; filename=%', cast(cast(rfc822 as varbinary(max))as varchar(max)))) ATTACHMENTS
              INTO Email_Attachments_Extract
              From Mailbox

                Where
                PATINDEX('%Content-Disposition: attachment; filename=%', cast(cast(rfc822 as varbinary(max))as varchar(max))) > 1
                and CAST(MAILSIZE as int)-PATINDEX('%Content-Disposition: attachment; filename=%', cast(cast(rfc822 as varbinary(max))as varchar(max))) > 0
              GO

              DECLARE cur_Attachments CURSOR
              KEYSET
              FOR

              Select Recid, USERID, REPLACE(REPLACE(Attachments,char(10),''),Char(13)+Char(13),Char(13)) From Email_attachments_Extract

              DECLARE @Recid varchar(15)
              DECLARE @UserID varchar(8)
              DECLARE @Input varchar(8000)
              DECLARE @Output varchar(8000)
              DECLARE @EOL int
              DECLARE @FileExists int

              OPEN cur_Attachments

              FETCH NEXT FROM cur_Attachments INTO @Recid, @UserID, @Input
              WHILE (@@fetch_status <> -1)
              BEGIN
              IF (@@fetch_status <> -2)
              BEGIN

              SET @EOL = PATINDEX('%' + char(13) + '%', @Input)

              WHILE @EOL > 0
                BEGIN

                 SET @Output = LEFT(@Input, @EOL)

                 SET @Input = REPLACE(@Input, @Output, '')
                
                 IF PATINDEX('%[A-Z]:\%', @Output) = 1 OR PATINDEX('%\\%.%', @Output) = 1
                  BEGIN
                   SET @Output = REPLACE(@Output,char(13),'')

                   -- Comment out if you would like the code to test if the file exists

                   -- SET @FileExists = dbo.fnFileExists(@Output)
                  
                   INSERT Email_Attachments Values(@Recid, @UserID, @Output, @FileExists)
                  END

                 SET @EOL = PATINDEX('%' + char(13) + '%', @Input)
                END

              END
              FETCH NEXT FROM cur_Attachments INTO @Recid, @UserID, @Input
              END

              CLOSE cur_Attachments
              DEALLOCATE cur_Attachments
              GO

              if exists (select * from dbo.sysobjects where id = object_id(N'[Email_Attachments_Extract]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
              drop table [Email_Attachments_Extract]
              GO

              select
              count(*),
              left(Attachment,len(Attachment)-charindex('\',reverse(Attachment)))
              from Email_Attachments

              group by
              left(Attachment,len(Attachment)-charindex('\',reverse(Attachment)))
              order by 2

              2 of 2 people found this helpful
              • 4. Re: Does Goldmine store the link for file attachments on outgoing email
                Shaul.Bel Apprentice

                The script is missing the part of the function that checks if the file exist in the path.

                 

                dbo.fnFileExists(@Output)

                • 5. Re: Does Goldmine store the link for file attachments on outgoing email
                  paullaufer Apprentice

                  I'd love to see a fnFileExists() function if anyone has one. dBase had a nice internal function called file() that was very useful.

                  • 6. Re: Does Goldmine store the link for file attachments on outgoing email
                    PJohns Apprentice

                    This should do it.

                     

                     

                    USE [ YourGoldMineDatabase]
                    GO

                    /****** Object:  UserDefinedFunction [dbo].[fnFileExists]  /
                    SET ANSI_NULLS ON
                    GO

                    SET QUOTED_IDENTIFIER ON
                    GO


                    CREATE FUNCTION [dbo].[fnFileExists] (@File varchar(255))
                    RETURNS varchar(15)
                    AS
                    BEGIN
                        Declare @result int
                        Exec master.dbo.xp_fileexist @File, @result output
                        RETURN @result
                    END

                     

                    GO





                    1 of 1 people found this helpful
                    • 7. Re: Does Goldmine store the link for file attachments on outgoing email
                      Shaul.Bel Apprentice

                      I have changed the function a little bit to

                       

                      CREATE FUNCTION  dbo.fnFileExists(@file_path nvarchar(500))

                      RETURNS nvarchar(20)

                      AS

                      BEGIN

                      declare @file_exists    int

                      declare @Exists nvarchar(20)

                      exec master.dbo.xp_fileexist

                      @file_path,

                      @file_exists output

                       

                      select  @Exists =case @file_exists

                      when 1

                      then   'File Exists'

                      else  'File Does Not Exist'

                      end

                      RETURN @Exists

                      END

                      GO

                       

                      From my experience sometimes you have to check if the folder itself exists at all and not just if the file exists.