easy way to change those file links

I’m sure most of you could figure this out for yourselves, but I’ve written a little proc that you can use to change file link strings … you can use it in test mode just to print out the sql that you would run if you feel more comfortable doing that way… or you can trust the values you put into the variables and set @test = 0 and just go for it.

As with all scripts I place in this blog, use at your own risk.  You are solely responsible for any effects of scripts you re-use from this blog.  The scripts provided in this blog are for use by SQL Server experts only, with a minimum of 5+ years of t-sql programming experience.

Here you go:

alter procedure z_admin_change_filelink(@test tinyint,@findstring varchar(255), @replacestring varchar(255), @additionalwheres varchar(8000)=”)
as
BEGIN

/*
Author: Loren Saunders
loren.saunders@gmail.com
*/
/* useage:

exec z_admin_change_filelink
 @test = 1
, @findstring = ‘\\oldserver\’
, @replacestring = ‘\\newserver\’
, @additionalwheres = ”
*/

declare @sqlstring varchar(8000)
declare @tablename varchar(255)
set @tablename = ”
set @sqlstring = ”

set @tablename = (select top 1 [name] from sysobjects where [name] > @tablename and [name] like ‘%filelinks%’ and xtype = ‘u’ order by [name])

while ISNULL(@tablename,”)”
begin
— concat string
set @sqlstring = @sqlstring + 
update ‘ + @tablename + ‘ set filepath = replace(filepath,”’ + @findstring + ”’,”’ + @replacestring + ”’) where filepath is not null ‘ 
+ @additionalwheres + ‘ 
— get next table
set @tablename = (select top 1 [name] from sysobjects where [name] > @tablename and [name] like ‘%filelinks%’ and xtype = ‘u’ order by [name])
end

print @sqlstring

if @test = 0
begin
exec(@sqlstring)
end

END

Comments or questions for Loren?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s