Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Change Unc Path On Sql

    I have a question on how to change a hyperlink path. I have an ADP that has our scanned files linked to it the path is on SQL server. The problems is that we are migrating sql server and the files to their own seperate server,
    Code:
    \\gcfs01\database\Backgrounds\SCANNED ACROBAT DOCUMENTS (BACKGROUNDS)\SCANNED ACTIVE FILES\5630.pdf
    The server is called \\gcfsql\ Instead of gcfs01.
    does anyone know how we can change the unc path to gcfsql without

    \\gcfsQL\database\Backgrounds\SCANNED ACROBAT DOCUMENTS (BACKGROUNDS)\SCANNED ACTIVE FILES\5630.pdf

    Any help would be greatly appreciated

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Not sure I understand the question fully. Do you mean you have a bunch of UNC paths stored in a table and you want to update the server name in each path?

    If so, you could do something like this:

    update mytable set path = replace(path,'\\oldserver\','\\newserver\')

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Talking Thank you Jezemine

    See the thing is that we have about over 2000 employees who get an employee number and their file is saved in the old server as their number
    and the only thing we need to change is the destination server the rest is fine but I dont want to mess anything up. Know what I mean, so I'm asking the Gurus

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Run Jezemine's replace statement as a select to see what it produces. Unless any of your employees have "\\" in their filenames, it should give you what you want.

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    is this ok?/

    Code:
    update EmployeeDocuments set EmployeeDocs = replace(\\gcfs01\database\Backgrounds\SCANNED ACROBAT DOCUMENTS (BACKGROUNDS)\SCANNED ACTIVE FILES,'\\gcfs01\','\\gcsql\')

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    no, don't run that. it will update every record with the same value. certainly not what you want. you want this:

    update EmployeeDocuments set EmployeeDocs = replace(EmployeeDocs,'\\gcfs01\','\\gcsql\')
    Last edited by jezemine; 10-12-06 at 17:02.

  7. #7
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    You realize this goes against Normalization. In a disaster recovery situation, you may be saddled with this breaking again - just when you least have time for it. Or you may want to create a test environment and wind up with someone updating the production employee records.

    Things like "where we keep stuff" and "the servername we keep it on" should be in a central place and never hard coded into "each employee's record" - unless that can be different for different groups of employees.

    A little late to fix, but something to file away in the brain for the next time this sort of design decision arises.

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Talking Jezermine

    Thats exactly what I did and it worked.

    first I changed the ntext data type to nvchar(300)
    and it worked .

    Thank you guys worked like a charm

  9. #9
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Thank you Vich

    its just that we are changing servers thats all and we wanted to change the path from the old server to the new server thats all.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •