WRITETEXT vs. UPDATE SET .WRITE
-
Hi all --
I have the unenviable task of trying to write to a nvarchar(MAX) field from a text field in another database. I wouldn't use a text field if I could avoid it, but I do not control the database that I am getting data from. I can change the nvarchar(MAX) field to another datatype, but from my reading, it seems to be the best way to go.
I am running into all sorts of problems in trying to do this. My successes so far including being able to write any string to the nvarchar(MAX) field in the new database, but I can't seem to do use data in a select statement. Here's my attempt:
UPDATE nysvrweb03.TPGWeb.dbo.Project
SET Project.ProjectVisionWBS1=TPGPR.ProjectVisionWBS1,
ProjectInfo .WRITE (custProjectInfo, 0 , NULL ),
[bunch of fields cut],
Project.ProjectActive = '1'
FROM nyvissql01.TPG.dbo.TPGWebsiteProject TPGPR
RIGHT JOIN nysvrweb03.TPGWeb.dbo.Project
ON Project.ProjectVisionWBS1 = TPGPR.ProjectVisionWBS1
WHERE TPGPR.ProjectVisionWBS1 IN (SELECT ProjectVisionWBS1 FROM Project)
GO
Here is my error message:
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
I'm guessing that this means that I need to do the pointer business that I can see is involved in WRITETEXT (which is less ideal, since it is going to be phased out of code). I've tried, but I'm just not getting how to do it.
(Bear with me on this - I'm not a programmer, just a network admin who got handed all the SQL stuff and is learning it the hard way).
I tried this, though I'm really just copying from Books Online and don't really understand it:
DECLARE @ptrval binary
SELECT @ptrval = TEXTPTR(TPGPR.ProjectInfo)
FROM nyvissql01.TPG.dbo.TPGWebsiteProject TPGPR
LEFT JOIN nysvrweb03.TPGWeb.dbo.Project
ON Project.ProjectVisionWBS1 = TPGPR.ProjectVisionWBS1
UPDATE nysvrweb03.TPGWeb.dbo.Project
SET Project.ProjectVisionWBS1=TPGPR.ProjectVisionWBS1,
ProjectInfo .WRITE (@ptrval, 0 , 255 ),
[bunch of stuff cut],
Project.ProjectActive = '1'
FROM nyvissql01.TPG.dbo.TPGWebsiteProject TPGPR
RIGHT JOIN nysvrweb03.TPGWeb.dbo.Project
ON Project.ProjectVisionWBS1 = TPGPR.ProjectVisionWBS1
WHERE TPGPR.ProjectVisionWBS1 IN (SELECT ProjectVisionWBS1 FROM Project)
GO
This runs, but it just puts a blank (not NULL) value into the ProjectInfo field.
Help! Assistance much appreciated!
Charlotte
-
This was removed by the editor as SPAM
-
I am trying to update a text field with the information from an identical text field in a view in another database on an ongoing basis.
Here is the definition of the view:
SELECT dbo.CL.ClientID AS ClientVisionID, dbo.PR.WBS1 AS ProjectVisionWBS1, dbo.ProjectCustomTabFields.custMarketingName AS ProjectName, dbo.ProjectCustomTabFields.custProjectInfo AS ProjectInfo, dbo.PR.City AS ProjectCity, dbo.PR.State AS ProjectState, dbo.PR.Country AS ProjectCountry, dbo.ProjectCustomTabFields.custSizeRentable AS ProjectSize, dbo.ProjectCustomTabFields.custLEEDCertificationLevel AS ProjectGreenID, dbo.PR.ActCompletionDate AS ProjectYear
FROM dbo.PR INNER JOIN
dbo.CL ON dbo.PR.ClientID = dbo.CL.ClientID INNER JOIN
dbo.ProjectCustomTabFields ON dbo.PR.WBS1 = dbo.ProjectCustomTabFields.WBS1 AND dbo.PR.WBS2 = dbo.ProjectCustomTabFields.WBS2 AND
dbo.PR.WBS3 = dbo.ProjectCustomTabFields.WBS3 INNER JOIN
dbo.ClientCustomTabFields ON dbo.PR.ClientID = dbo.ClientCustomTabFields.ClientID
WHERE (dbo.ProjectCustomTabFields.custOnWebsite = 'Y')
Here is the table it's going into (note that the Column Names in both are identical):
CREATE
TABLE [dbo].[Project](
[ProjectID] [int]
IDENTITY(1,1) NOT NULL, [ProjectVisionWBS1] [varchar](30) NOT NULL, [ClientVisionID] [varchar](32) NOT NULL, [ProjectName] [varchar](50) NOT NULL,[ProjectInfo] [text] NULL,[ProjectCity] [varchar](30) NULL,[ProjectState] [varchar](50) NULL,[ProjectCountry] [varchar](30) NULL,[ProjectSize] [int] NOT NULL,[ProjectYear] [datetime] NULL,[ProjectGreenID] [int] NULL,[ProjectActive] [smallint] NOT NULL CONSTRAINT [DF_Project_ProjectActive] DEFAULT ((1)),CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED
The column I'm trying to update from the view to the table (ongoing) is the ProjectInfo field.
-
try this one
--on server nysvrweb03
use
TPGWeb
GO
UPDATE
dbo.Project
SET
ProjectInfo = custProjectInfo,
[bunch of stuff cut]
,
Project
.ProjectActive = '1'
FROM
nyvissql01.TPG.dbo.TPGWebsiteProject TPGPR
INNER
JOIN dbo.Project nyProj
ON
SUBSTRING (nyProj.ProjectVisionWBS1,0,20) = SUBSTRING(TPGPR.ProjectVisionWBS1,0,20)
--since you have joined the table using a right join there is no need to use the where
--clause. if you want the clause below then remove the right join or inner join
--in your stmt it also seems you want to update the field you are using in your filter clause,
--i have removed that in my sample.
--WHERE TPGPR.ProjectVisionWBS1 IN (SELECT ProjectVisionWBS1 FROM Project)
--i have put an arbitrary length of 20 for your ProjectVisionWBS1, other wise you can try
ON SUBSTRING
(nyProj.ProjectVisionWBS1,0,len(nyProj.ProjectVisionWBS1))
= SUBSTRING (TPGPR.ProjectVisionWBS1,0,datalength(TPGPR.ProjectVisionWBS1))
use datalength on the TEXT field and use len on your NVARCHAR field
give us a shout if it works
-
That did the trick!
Thank you so much - I've been banging my head on that for days.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply