如何更新SQL Server 2000中的text或ntext字段

So I need to update a text field. Neither the UPDATE statement or the WRITETEXT statement work when used below

所以我需要更新一个文本字段。在下面使用时,UPDATE语句或WRITETEXT语句都不起作用

CREATE TABLE MyTable (IDField int, MyField text)
INSERT INTO MyTable (IDField) SELECT 1

DECLARE @Data1 varchar(8000), @Data2 varchar(8000), @ptrval binary(16)

SELECT @Data1 = REPLICATE('1',8000)
SELECT @Data2 = REPLICATE('2',8000)

-- this sets MyField to string of only 8000 characters
UPDATE MyTable SET MyField = @Data1 + @Data2 WHERE IDField = 1 


SELECT @ptrval = TEXTPTR(MyField ) 
FROM MyTable 
WHERE IDField = 1 

-- this causes an error: Incorrect syntax near '+'.
--WRITETEXT MyTable.MyField @ptrval @Data1 + @Data2

How am I supposed to do this when local variables cannot be of type TEXT? (If I had SSQL Server 2005 I would use varchar(max) - but I don't)

当局部变量不能是TEXT类型时,我该怎么做呢? (如果我有SSQL Server 2005,我会使用varchar(max) - 但我没有)

3 个解决方案

#1

Try using UPDATETEXT instead

请尝试使用UPDATETEXT

WRITETEXT MyTable.MyField @ptrval @Data1 
UPDATETEXT MyTable.MyField @ptrval 8000 NULL @Data2

The insert offset is zero based so 8000 should write into the 8001st character. The delete offset is null as a value of NULL deletes all data from the insert_offset position to the end of the existing text.

插入偏移量基于零,因此8000应写入第8001个字符。删除偏移量为null,因为NULL值会删除insert_offset位置到现有文本末尾的所有数据。

Ref: http://msdn.microsoft.com/en-us/library/ms189466.aspx

Do not forget nvarchar (which you should use with ntext field) have a maximum capacity of half the varchar fields that you are using so your block sizes need to be reduced to 4000 in that case.

不要忘记nvarchar(你应该使用ntext字段)的最大容量是你正在使用的varchar字段的一半,因此在这种情况下你的块大小需要减少到4000。

#2

the values will actually vary in length so I will try it like this tomorrow:

这些值的长度实际上会有所不同,所以明天我会这样尝试:

WRITETEXT MyTable.MyField @ptrval @Data1 
UPDATETEXT MyTable.MyField @ptrval Len(@Data1) NULL @Data2

the above worked but I had to calculate the length first:

以上工作但我必须首先计算长度:

WRITETEXT MyTable.MyField @ptrval @Data1
SET @Len = LEN(@Data1)
UPDATETEXT MyTable.MyField @ptrval @Len NULL @Data2

not sure why you can't use a function like LEN() where a parameter is expected.

不知道为什么你不能使用像LEN()这样的函数来预期参数。

#3

I had a hard time with this one.

我很难用这个。

I was trying to save long strings (actually rich text box contents) to a ntext feild.

我试图将长字符串(实际上是富文本框内容)保存到ntext字段中。

The solution turned out to be fairly simple.

解决方案结果相当简单。

        SQLst = "UPDATE  Test SET Text = cast (@value as ntext)" & _
            " WHERE  Num = 3 "

        Debug.Print(SQLst.ToString)

        Dim cnn As New SqlServerCe.SqlCeConnection(Tcon)
        Dim cmd = New SqlCeCommand(SQLst, cnn)
        cmd.Parameters.AddWithValue("@value", strQuestionQUESTION)
        cnn.Open()
        cmd.ExecuteNonQuery()
        cnn.Close()

Note: strQuestionQUESTION was about 3000 characters or formatting code and text. "Num" is just an integer field in the "Test" database which also contain the ntext field name "Text"

注意:strQuestionQUESTION大约有3000个字符或格式化代码和文本。 “Num”只是“Test”数据库中的一个整数字段,它还包含ntext字段名称“Text”

#1