1 Reply Latest reply: May 14, 2013 9:33 AM by BrianWebb RSS

    Help! Changing attachment path in SQL database

    726207
      Changing paths in the dbo.dlnk table isn't too difficult, but I came accross something that has me stumped.
      Rather than a straight forward path such as \\servername\documents\ there are many that have a URL in the path such as http://servername/exponline/attachmentframe.jsp?filename=83089.pdf&fullpath=%5C%5Cservername%5Cimagetool%5Carchive%5Chce%5C78%5C83089.pdf&dk=h8Tjf4FcM7Km%2BpwH637w6w%3D%3D

      Does anyone have a sql script that will filter out the url crud and leave a simple path.
      You can see the path in there, it's \\servername\imagetool\archive\hce\78\5C83089.pdf
        • 1. Re: Help! Changing attachment path in SQL database
          BrianWebb
          SELECT SUBSTRING(REPLACE(SUBSTRING(@path,
          PATINDEX('%fullpath=%', @path) + 9,
          255), '%5c', '\'), 1,
          PATINDEX('%&dk=%',
          REPLACE(SUBSTRING(@path,
          PATINDEX('%fullpath=%',
          @path) + 9, 255),
          '%5c', '\')) - 1) AS [absolute_path]
          ,[description]
          ,[master_key]
          ,[parent_key]
          ,[cr_external_key]
          INTO #temp
          FROM ATTACHMENT_PATHS


          UPDATE attachment_paths
          SET ATTACHMENT_PATHS.absolute_path = #temp.absolute_path
          FROM ATTACHMENT_PATHS
          INNER JOIN #temp ON ATTACHMENT_PATHS.master_key = #temp.master_key
          WHERE ATTACHMENT_PATHS.absolute_path LIKE '%/exponloine/%'