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,
\\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 appreciatedNot 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\')|||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 :)|||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.|||update EmployeeDocuments set EmployeeDocs = replace(\\gcfs01\database\Backgrounds\SCANNED ACROBAT DOCUMENTS (BACKGROUNDS)\SCANNED ACTIVE FILES,'\\gcfs01\','\\gcsql\')|||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\')|||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.|||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|||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.

Change UNC of DB Backup?

What is the easiest way to change the location where I backup my databases?
Can I just modify the command field of the sysjobsteps table or is there a
stored proc I can use instead? They're setup using the DB Maint plan at the
moment and I have about 20 to change on 3 servers, so I'd prefer to not do
it manually. This will be on 2 2k servers and one sql7 server.
TIA
Matt
>
> Take a look into the system stored procedure sp_update_jobstep in MSDB
> database.
>
Hari-
I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
how can I take what is already there and change the command field to reflect
the new UNC. The command line for that is huge and there are 2 places that
the UNC needs to be changed in it. Can I run a select statement and exec
sp_update_jobstep on each row returned from the select using the Replace()
function?
|||One thing to note is that backing up to a UNC share has permission
implications - you may need to change the credentials of the SQL Server
and Agent services to a domain one.
Regards
Stuart
Matt Williamson wrote:
> Hari-
> I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
> how can I take what is already there and change the command field to reflect
> the new UNC. The command line for that is huge and there are 2 places that
> the UNC needs to be changed in it. Can I run a select statement and exec
> sp_update_jobstep on each row returned from the select using the Replace()
> function?
|||Hello,
Execute the sp_update_jobstep using the parameters @.Jobname,@.JobStep_id and
@.command. If you give an UNC path ensure that the SQL Server Startup account
have
rights to read and write that partcular share and directory.
Thanks
Hari
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:OCXSGq2LHHA.2028@.TK2MSFTNGP03.phx.gbl...
> Hari-
> I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
> how can I take what is already there and change the command field to
> reflect the new UNC. The command line for that is huge and there are 2
> places that the UNC needs to be changed in it. Can I run a select
> statement and exec sp_update_jobstep on each row returned from the select
> using the Replace() function?
>
>
|||Thanks for the input. That's a non-issue though. It's already a UNC path.
I'm just changing it to a different one. The service that runs the job is
already a domain level account with appropriate permissions.
"NonNB" <nonnb@.webmail.co.za> wrote in message
news:1167905284.272432.183690@.i80g2000cwc.googlegr oups.com...
> One thing to note is that backing up to a UNC share has permission
> implications - you may need to change the credentials of the SQL Server
> and Agent services to a domain one.
> Regards
> Stuart
> Matt Williamson wrote:
>
|||> Execute the sp_update_jobstep using the parameters @.Jobname,@.JobStep_id
> and @.command. If you give an UNC path ensure that the SQL Server Startup
> account have
> rights to read and write that partcular share and directory.
Would there be any disadvantage to running something like this?
Update sysjobsteps
set command = replace(command, '\\OLDSERVER', '\\NEWSERVER')
where command like 'EXECUTE%'
In a test table that I created using a select from sysjobsteps, it worked
exactly how I wanted. I haven't tried it on the actual sysjobsteps table in
the msdb database yet though.

Change UNC of DB Backup?

What is the easiest way to change the location where I backup my databases?
Can I just modify the command field of the sysjobsteps table or is there a
stored proc I can use instead? They're setup using the DB Maint plan at the
moment and I have about 20 to change on 3 servers, so I'd prefer to not do
it manually. This will be on 2 2k servers and one sql7 server.
TIA
Matt>
> Take a look into the system stored procedure sp_update_jobstep in MSDB
> database.
>
Hari-
I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
how can I take what is already there and change the command field to reflect
the new UNC. The command line for that is huge and there are 2 places that
the UNC needs to be changed in it. Can I run a select statement and exec
sp_update_jobstep on each row returned from the select using the Replace()
function?|||One thing to note is that backing up to a UNC share has permission
implications - you may need to change the credentials of the SQL Server
and Agent services to a domain one.
Regards
Stuart
Matt Williamson wrote:
> >
> > Take a look into the system stored procedure sp_update_jobstep in MSDB
> > database.
> >
> Hari-
> I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
> how can I take what is already there and change the command field to reflect
> the new UNC. The command line for that is huge and there are 2 places that
> the UNC needs to be changed in it. Can I run a select statement and exec
> sp_update_jobstep on each row returned from the select using the Replace()
> function?|||Hello,
Execute the sp_update_jobstep using the parameters @.Jobname,@.JobStep_id and
@.command. If you give an UNC path ensure that the SQL Server Startup account
have
rights to read and write that partcular share and directory.
Thanks
Hari
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:OCXSGq2LHHA.2028@.TK2MSFTNGP03.phx.gbl...
> >
>> Take a look into the system stored procedure sp_update_jobstep in MSDB
>> database.
> Hari-
> I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
> how can I take what is already there and change the command field to
> reflect the new UNC. The command line for that is huge and there are 2
> places that the UNC needs to be changed in it. Can I run a select
> statement and exec sp_update_jobstep on each row returned from the select
> using the Replace() function?
>
>|||Thanks for the input. That's a non-issue though. It's already a UNC path.
I'm just changing it to a different one. The service that runs the job is
already a domain level account with appropriate permissions.
"NonNB" <nonnb@.webmail.co.za> wrote in message
news:1167905284.272432.183690@.i80g2000cwc.googlegroups.com...
> One thing to note is that backing up to a UNC share has permission
> implications - you may need to change the credentials of the SQL Server
> and Agent services to a domain one.
> Regards
> Stuart
> Matt Williamson wrote:
>> >
>> > Take a look into the system stored procedure sp_update_jobstep in MSDB
>> > database.
>> >
>> Hari-
>> I've been reading about the sp_update_jobstep proc in BOL but I'm not
>> sure
>> how can I take what is already there and change the command field to
>> reflect
>> the new UNC. The command line for that is huge and there are 2 places
>> that
>> the UNC needs to be changed in it. Can I run a select statement and exec
>> sp_update_jobstep on each row returned from the select using the
>> Replace()
>> function?
>|||> Execute the sp_update_jobstep using the parameters @.Jobname,@.JobStep_id
> and @.command. If you give an UNC path ensure that the SQL Server Startup
> account have
> rights to read and write that partcular share and directory.
Would there be any disadvantage to running something like this?
Update sysjobsteps
set command = replace(command, '\\OLDSERVER', '\\NEWSERVER')
where command like 'EXECUTE%'
In a test table that I created using a select from sysjobsteps, it worked
exactly how I wanted. I haven't tried it on the actual sysjobsteps table in
the msdb database yet though.

Change UNC of DB Backup?

What is the easiest way to change the location where I backup my databases?
Can I just modify the command field of the sysjobsteps table or is there a
stored proc I can use instead? They're setup using the DB Maint plan at the
moment and I have about 20 to change on 3 servers, so I'd prefer to not do
it manually. This will be on 2 2k servers and one sql7 server.
TIA
MattHello,
Take a look into the system stored procedure sp_update_jobstep in MSDB
database.
Thanks
Hari
"Matt Williamson" wrote:

> What is the easiest way to change the location where I backup my databases
?
> Can I just modify the command field of the sysjobsteps table or is there a
> stored proc I can use instead? They're setup using the DB Maint plan at th
e
> moment and I have about 20 to change on 3 servers, so I'd prefer to not do
> it manually. This will be on 2 2k servers and one sql7 server.
> TIA
> Matt
>
>|||>
> Take a look into the system stored procedure sp_update_jobstep in MSDB
> database.
>
Hari-
I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
how can I take what is already there and change the command field to reflect
the new UNC. The command line for that is huge and there are 2 places that
the UNC needs to be changed in it. Can I run a select statement and exec
sp_update_jobstep on each row returned from the select using the Replace()
function?|||One thing to note is that backing up to a UNC share has permission
implications - you may need to change the credentials of the SQL Server
and Agent services to a domain one.
Regards
Stuart
Matt Williamson wrote:
> Hari-
> I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
> how can I take what is already there and change the command field to refle
ct
> the new UNC. The command line for that is huge and there are 2 places that
> the UNC needs to be changed in it. Can I run a select statement and exec
> sp_update_jobstep on each row returned from the select using the Replace()
> function?|||Hello,
Execute the sp_update_jobstep using the parameters @.Jobname,@.JobStep_id and
@.command. If you give an UNC path ensure that the SQL Server Startup account
have
rights to read and write that partcular share and directory.
Thanks
Hari
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:OCXSGq2LHHA.2028@.TK2MSFTNGP03.phx.gbl...
> Hari-
> I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
> how can I take what is already there and change the command field to
> reflect the new UNC. The command line for that is huge and there are 2
> places that the UNC needs to be changed in it. Can I run a select
> statement and exec sp_update_jobstep on each row returned from the select
> using the Replace() function?
>
>|||Thanks for the input. That's a non-issue though. It's already a UNC path.
I'm just changing it to a different one. The service that runs the job is
already a domain level account with appropriate permissions.
"NonNB" <nonnb@.webmail.co.za> wrote in message
news:1167905284.272432.183690@.i80g2000cwc.googlegroups.com...
> One thing to note is that backing up to a UNC share has permission
> implications - you may need to change the credentials of the SQL Server
> and Agent services to a domain one.
> Regards
> Stuart
> Matt Williamson wrote:
>|||> Execute the sp_update_jobstep using the parameters @.Jobname,@.JobStep_id
> and @.command. If you give an UNC path ensure that the SQL Server Startup
> account have
> rights to read and write that partcular share and directory.
Would there be any disadvantage to running something like this?
Update sysjobsteps
set command = replace(command, '\\OLDSERVER', '\\NEWSERVER')
where command like 'EXECUTE%'
In a test table that I created using a select from sysjobsteps, it worked
exactly how I wanted. I haven't tried it on the actual sysjobsteps table in
the msdb database yet though.