December 22, 2010 at 7:43 am
I have been updating this field every time we change a directory path, for years. Never had a problem until today.
All of a sudden I am getting unexpected results.
this is the query
use staff
declare @currLoc varchar(20)
declare @newLoc varchar(20)
select @currLoc = '\\cbh-srv-dr1\Projects\18765'
select @newLoc = '\\cbh-srv-dr1\ReadOnly\18765'
update a
set a.physical_location
= cast(Replace(cast(b.physical_location as varchar(MAX)),''+@currLoc+'', ''+@newLoc+'') as ntext)
from tbl_out_messages b
join tbl_out_messages a
on a.email_id = b.email_id
where b.physical_location like ''+@currLoc+'%'
I would expect to see the directory path as follows
\\cbh-srv-dr1\ReadOnly\18765
but I am getting
\\cbh-srv-dr1\ReadOnts\18765
can anyone shed any light on this please?
Cheers...Steve
December 22, 2010 at 9:13 am
smadden-534358 (12/22/2010)
I have been updating this field every time we change a directory path, for years. Never had a problem until today.All of a sudden I am getting unexpected results.
this is the query
use staff
declare @currLoc varchar(20)
declare @newLoc varchar(20)
select @currLoc = '\\cbh-srv-dr1\Projects\18765'
select @newLoc = '\\cbh-srv-dr1\ReadOnly\18765'
update a
set a.physical_location
= cast(Replace(cast(b.physical_location as varchar(MAX)),''+@currLoc+'', ''+@newLoc+'') as ntext)
from tbl_out_messages b
join tbl_out_messages a
on a.email_id = b.email_id
where b.physical_location like ''+@currLoc+'%'
I would expect to see the directory path as follows
\\cbh-srv-dr1\ReadOnly\18765
but I am getting
\\cbh-srv-dr1\ReadOnts\18765
can anyone shed any light on this please?
Cheers...Steve
Not sure where the problem is at. Do the updated fields have the 'ly' changed to 'ts' after the update? Also, just wondering why the update statement is joining to itself.
This update should do the same thing unless there are details removed for the sake of posting that would be used in the string concatenation and joins.
update tbl_out_messages
set physical_location = cast(Replace(cast(b.physical_location as varchar(MAX)), @currLoc, @newLoc) as ntext)
where physical_location like @currLoc + '%'
Also you really should be using nvarchar(max) instead of ntext. The ntext datatype is deprecated and will be removed from future versions of sql. I also noticed you cast the physical location as varchar and given that the data looks like network locations there is really no need to support unicode. I would suggest you can change your datatype from ntext to varchar(max). Just my 2¢.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 22, 2010 at 9:14 am
Not sure why, but changed varchar(max) to nvarchar(max) and it all worked fine...will check it out in BOL
December 22, 2010 at 9:19 am
Hi Sean,
just seen reply as I posted thanks, we have come to the same answer and it worked.
I join the table to itself to limit the amount of rows it tries to update.
If I am not mistaken will your statement check every record in the table?
Cheers...Steve
December 22, 2010 at 9:23 am
Just re read reply :doze:
The column is ntext purely because we do not know how the application will react if we changed it.
Which is a real pain for me.
There is a move to replace the existing app and so the problem goes away and my life becomes a lot more happier 🙂
Cheers...Steve
December 22, 2010 at 9:29 am
If I am not mistaken will your statement check every record in the table?
update a
set a.physical_location
= cast(Replace(cast(b.physical_location as varchar(MAX)),''+@currLoc+'', ''+@newLoc+'') as ntext)
from tbl_out_messages b
join tbl_out_messages a
on a.email_id = b.email_id
where b.physical_location like ''+@currLoc+'%'
They both only update records where physical_location like ''+@currLoc+'%'. the difference is I changed it up a little to just be a straight update without using any joins (which in this case did not actually do anything).
Good luck on the upgrade. It is always a slow process to get things converted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply