December 30, 2009 at 1:12 pm
I'm trying to do a simple update across multiple database updating a value that has an internal question mark '?'.
No matter what I try, the question mark is converted to the database name.
ie.
EXECUTE sp_msforeachdb
update table set column = "<<^KqdP/?;SP<_]LA_?"
or
DECLARE @command varchar(1000)
SELECT @command =
BEGIN USE [?]
update table set column = "<<^KqdP/?;SP<_]LA_?"
END'
EXEC sp_MSforeachdb @command
What can I do to tell sp_msforeachdb that the '?' in not a DB place holder?
thanks very
Tim White
December 31, 2009 at 4:11 am
Hello,
Try to put Square Bracket around Question Mark.
like [?]
this might give you a break.....
Rgds,
Pankaj
December 31, 2009 at 6:51 am
Pankaj, thanks for the suggestion, but I already tried it. Didn't work.
fyi..starting tomorrow (1-1-10), I'll be out of the office for two weeks and will not be able to respond to this post. Please go ahead and leave your suggestions so I can see them when I get back.
Tim White
December 31, 2009 at 11:06 am
See if this will work for you.
DECLARE @command varchar(1000)
SELECT @command = '
BEGIN
declare @colVal varchar(19)
Set @colVal = "<<^KqdP/" + char(63)+ ";SP<_]LA_" + char(63) + ""
USE [?]
update table set column = @colVal
END'
EXEC sp_MSforeachdb @command
December 31, 2009 at 11:20 am
Yes, that worked perfectly, just what I was looking for.
Brilliant strategy going for the char(63) string.
Well done!
Tim White
December 31, 2009 at 11:21 am
Ohh, forgot to say thanks much !
Tim White
February 3, 2010 at 2:47 pm
The sp_MSforeachdb can have the "replace character" changed to a users preference. By default it is a '?'. It is the second parameter in the call. By changing this to something other than the ? you may also be able to resolve your issue.
sp_MSforeachdb @command1, @replacechar
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply