May 18, 2011 at 8:16 am
Hi
I need help how to change the attatchment for drve to anther drive
UPDATE dbo.dlnk
SET attachment = ('[new attachment path]' + right (attachment, len(attachment) - [old path length]))
WHERE left(attachment, [old path length]) = '[old attachment path]';
GO
Note the following values within the script template.
[new attachment path] - The new path to the physical attachment files. (i know)
[old attachment path] - The old path to the physical attachment files.( i dont know)
[old attachment path length] - The number of characters in the old attachment path, not including the attachment file name.
but i need to know
how can i get the old attachment path?
how can i get the old attachment path length?
my database is Sql 2005 express SP2
Thank you in advance
May 18, 2011 at 8:27 am
I amde the assumption that all your attachments have the full path in the name. Also, this type of update will assume that all the files will be in the same directory going forward.
declare @t table (id int identity(1,1), attachment varchar(500))
declare @NewPath varchar(20)
declare @OldPath varchar(20)
set @NewPath = 'D:\Work\'
set @OldPath = 'C:\Test\'
insert into @t
select 'C:\test\file1.htm'
union all
select 'c:\test\directory1\LongFileName.xml'
-- Modifies the string to use new Path
select @NewPath + RIGHT(Attachment, charindex('\',REVERSE(attachment)) - 1) NewPath, * from @t
-- Modifies path to move from C:\Test to D:\Work
select REPLACE(Attachment, @OldPath, @NewPath) NewPath, attachment from @t
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 18, 2011 at 8:34 am
Thank you in advance.
is there script to know the old path for attatchment in database
May 18, 2011 at 8:45 am
declare @t table (id int identity(1,1), attachment varchar(500))
declare @NewPath varchar(20)
declare @OldPath varchar(20)
insert into @t
select 'C:\test\file1.htm'
union all
select 'c:\test\directory1\LongFileName.xml'
-- To Find the Old String
select
left(Attachment, Len(Attachment) - charindex('\',REVERSE(attachment)) + 1) OldPath, * from @t
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply