This discussion is archived
1 Reply Latest reply: May 14, 2013 7:33 AM by BrianWebb RSS

Help! Changing attachment path in SQL database

726207 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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/%'

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points