SQL change stored path to documents query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL change stored path to documents query

Author

Topic

nlinecomputers
Starting Member

17 Posts

Posted - 2007-06-22 : 22:51:38

I need someone that knows something about SQL queries.

I have a client that is running a Database known as ProLaw. It is in part a
document management system for Law Offices.

They have an SQL 2005 database that tracks per client all the documents they
create.

We had to replace there server with new server. The new server is running
sbs2003 and had to have a different Netbios name then the old sbs2000
server. (Small Bus. Server has some weird quirks that make simply using the
same netbios name impossible. Google search it if you don't believe me.)

The database holds in a single column the full network share path to each
document.

A document for example may have had a path of

"\\lawwillsbs2000\Prolaw\Documents\ACME wigets Inc\smith_deposition.doc"

Different documents may have different names and more subdirectories but the
root path of "\\lawwillsbs2000\Prolaw\Documents\" is shared by all.

The new server is named \\sbs2003 I need to change
the first part of almost 3000 path statements to the new server. The rest
of the path is unchanged.

I have had several people running prolaw tell me that I should run this
query:


UPDATE Events

SET DocDir=REPLACE(DocDir, '\\\\lawwillsbs2000', '\\\\sbs2003')

WHERE EventKind='O'

This doesn't work. Nothing is changed. I'm guessing it is because this
query assumes the value will be ONLY \\lawwillsbs2000 I see nothing in here that tells the query that this is only part of the string. No wild card or other marker.

I need some kind of string function here do I not? Anyone know enough to
help me craft a proper query?

Thanks

Nathan Williams

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-23 : 01:28:39

If all the strings start with \\lawwillsbs2000, you can try

Select '\\sbs2003'+substring(DocDir,17,len(DocDir)) from Events

If the above select gives what you want then you can run Update

Update Events
Set DocDir='\\sbs2003'+substring(DocDir,17,len(DocDir))

Madhivanan

Failing to plan is Planning to fail

nlinecomputers
Starting Member

17 Posts

Posted - 2007-06-25 : 18:23:55

Thanks, that did the trick! Now I just need to learn the syntax of that command so I don't have to beg for help for this kind of thing again.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-26 : 01:17:58

quote:
Originally posted by nlinecomputers

Thanks, that did the trick! Now I just need to learn the syntax of that command so I don't have to beg for help for this kind of thing again.

Thats good anyway

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources