September 5, 2002 at 10:09 am
Hi,
I seem to be completely lost trying to do something that should be fairly simple.
What I am trying to do is this:
In a table called: "Contsupp"
there are two fields: "Address1" and "Address2" that when combined contain a path and filename.
Address1: "\\bssql1\bssql1data\goldmine\MailBox\Att"
Address2: "ach\so2 report for 405 354-2444.doc"
Address1+Address2: "\\bssql1\bssql1data\goldmine\MailBox\Attach\so2 report for 405 354-2444.doc"
In the same "Contsupp" table there is a "text" type field called: "Linkeddoc" that contains this:
Linkeddoc: "~~SYNC=1
~~CREATETIME=200104301256
~~FILENAME=\\bssql1\bssql1data\goldmine\MailBox\Attach\so2 report"
As you can see, the part of the "linkeddoc" field after ~~FILENAME= should contain Address1+Address2 fields, but it is cut off (due to a previous mistake of mine).
**So what I am trying to do is replace everything after "~~FILENAME=" with the contents of Address1 + Address2 in all the fields that need it.
(Fields in the table that need it are where address1 like "\\bssql1\bssql1data\goldmine\MailBox\Att%")
Here is the non-working mess I have created so far:
update GOLDMINE_TESTRETENTION.dbo.CONTSUPP
declare @filename varchar(200)
set @filename = contsupp.address1 + contsupp.address1
declare @filename varchar(100)
set @filename = contsupp.address1 + contsupp.address1
from contsupp
where address1 like "\\bssql1\bssql1data\goldmine\MailBox\Att%"
and linkeddoc like "%FILENAME=\\bssql1\bssql1data\goldmine\MailBox\Attach\%"
set LINKEDDOC = replace(CAST(LINKEDDOC AS varchar(200)), '~~FILENAME=%', ''~~FILENAME='+@filename')
I would appreciate any help at all.
Thanks,
Larry
September 5, 2002 at 11:33 pm
Hi gunnykiln,
Should you change "set @filename = contsupp.address1 + contsupp.address1" to "set @filename = contsupp.address1 + contsupp.address2"?
Mike
September 6, 2002 at 2:18 am
Provided that the 'Replace' stuff works correctly, you can write this as
UPDATE contsupp
SET LINKEDDOC = replace(CAST(LINKEDDOC AS varchar(200)), '~~FILENAME=%', ''~~FILENAME='+address1 + address2')
WHERE address1 like "\\bssql1\bssql1data\goldmine\MailBox\Att%"
and linkeddoc like "%FILENAME=\\bssql1\bssql1data\goldmine\MailBox\Attach\%"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply