Manipulating Text,nText data types filed in tsql

I have to run a dynamic sql that i save in the database as a TEXT data type(due to a large size of the sql.) from a .NET app. Now i have to run this sql from the stored proc that returns the results back to .net app. I am running this dynamic sql with sp_executesql like this..

EXEC sp_executesql@.Statement,N'@.param1 varchar(3),@.param2 varchar(1)',@.param1,@.param2,
GO

As i can't declare text,ntext etc variables in T-Sql(stored proc), so i am using this method in pulling the text type field "Statement".

DECLARE@.Statement varbinary(16)
SELECT@.Statement = TEXTPTR(Statement)
FROM table1
READTEXT table1.statement@.Statement 0 16566

So far so good, the issue is how to convert @.Statment varbinary to nText to get it passed in sp_executesql.

Note:- i can't use Exec to run the dynamic sql becuase i need to pass the params from the .net app and Exec proc doesn't take param from the stored proc from where it is called.

I would appreciate if any body respond to this.

Yes, the limitation of using NTEXT as variable causes the problem. I know a workaround, but... you still need EXEC to warp the execution of sp_executesql. Read this:

sp_executesql and Long SQL Strings in SQL 2000
There is a limitation with sp_executesql on SQL 2000 and SQL 7, since you cannot use longer SQL strings than 4000 characters. (On SQL 2005, use nvarchar(MAX) to avoid this problem.) If you want to use sp_executesql despite you query string is longer, because you want to make use of parameterised query plans, there is actually a workaround. To wit, you can wrap sp_executesql in EXEC():

DECLARE @.sql1 nvarchar(4000),
@.sql2 nvarchar(4000),
@.state char(2)
SELECT @.state = 'CA'
SELECT @.sql1 = N'SELECT COUNT(*)'
SELECT @.sql2 = N'FROM dbo.authors WHERE state = @.state'
EXEC('EXEC sp_executesql N''' + @.sql1 + @.sql2 + ''',
N''@.state char(2)'',
@.state = ''' + @.state + '''')
This works, because the @.stmt parameter to sp_executesql is ntext, so by itself, it does not have any limitation in size.

You can even use output parameters by using INSERT-EXEC, as in this example:

CREATE TABLE #result (cnt int NOT NULL)
DECLARE @.sql1 nvarchar(4000),
@.sql2 nvarchar(4000),
@.state char(2),
@.mycnt int
SELECT @.state = 'CA'
SELECT @.sql1 = N'SELECT @.cnt = COUNT(*)'
SELECT @.sql2 = N'FROM dbo.authors WHERE state = @.state'
INSERT #result (cnt)
EXEC('DECLARE @.cnt int
EXEC sp_executesql N''' + @.sql1 + @.sql2 + ''',
N''@.state char(2),
@.cnt int OUTPUT'',
@.state = ''' + @.state + ''',
@.cnt = @.cnt OUTPUT
SELECT @.cnt')
SELECT @.mycnt = cnt FROM #result
You have my understanding if you think this is too messy to be worth it.

So you can break the NTEXT statement from your table into several NVARCHAR(4000) strings, then pass the strings as parameter for sp_executesql. The original wonderful artilc can be found here:

http://www.sommarskog.se/dynamic_sql.html

|||Thanks, I had to break the query into pieces to get this working.

manipulating ntext in stored procedures

Hi,

I have been trying to write a SP where, in the result set is brought back, two ntext columns are combined with a text string, However I get an error when I click on apply;

"Error 403: Invalid operator for data type. Operator equals add, type equals ntext"

I can only assume that this means you can't add (or even manipulate) ntext columns in a SP.

Short of changing the sp to bring back 3 columns and to do the manipulation in my c# code I can't think of a way round this.

Anyone with any ideas?

thanks in advance

rich

SP code ( I have highlighted the problem area):

CREATE PROCEDURE [dbo].[usp_RCTReportQuery]

@.PersonType varchar(255),
@.Status varchar(255),
@.Oustanding varchar(255)

AS

DECLARE @.PersonTypeID int,
@.StatusID int,
@.OustandingID int

SELECT @.PersonTypeID = PersonTypeID FROM TBL_LU_People_Type WHERE PersonType = @.PersonType
SELECT @.StatusID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Status
SELECT @.OustandingID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Oustanding

SELECT surname as [Potential Claimant], s.thevalue + '<BR /><BR />' + o.thevalue as [Status]
FROM tbl_people p
INNER JOIN tbl_Lu_people lup on lup.personid = p.personid and persontypeid = @.PersonTypeID
LEFT JOIN tbl_memo s on s.caseID = p.caseID and s.memotypeid = @.StatusID
LEFT JOIN tbl_memo o on o.caseID = p.caseID and o.memotypeid = @.OustandingID
GO

Richie:

If you are running SQL Server 2005 you might be able to leverage a function to work around this issue. Perhaps a function like this:

alter function dbo.appendText
( @.prm_textIn nvarchar(max),
@.prm_appendString varchar (8000)
)
returns nvarchar (max)
as

begin

return ( @.prm_textIn + @.prm_appendString )

end

This is coded as a scalar function; you might be better off setting it up as an inline function so that you can potentially leverage it in the future with the CROSS JOIN operator for other purposes. Below is an example:

declare @.what nvarchar (max) set @.what = ''
declare @.firstPart varchar (40) set @.firstPart = ' ** This is'
declare @.secondPart varchar (40) set @.secondPart = ' a test. **'

select @.what = @.what + ' #' + convert (nvarchar(5), iter)
from small_iterator (nolock)

select len (@.what) as [len],
substring (dbo.appendText (@.what, @.firstPart + @.secondPart), len(@.what) - 6, 30)
as [righthand piece]

-- Sample Output: --

-- len righthand piece
-- -- --
-- 218263 #32767 ** This is a test.

Dave

|||if you are on 2005, you could just use a nvarchar(MAX) column instead of ntext.

www.elsasoft.org|||

Richie:

You might be able to get your query to work in SQL 2005 doing something like this:

SELECT surname as [Potential Claimant], cast (s.thevalue as nvarchar (max)) + '<BR /><BR />' + o.thevalue as [Status]

|||

Please specify the version of SQL Server in your posts so it is easy to suggest the correct solution.

1. If you are using SQL Server 2005 then use varchar(max), nvarchar(max), and varbinary(max). The text, ntext, and image data types have been deprecated. The newer max data types will allow you to manipulate the values on the server-side just like regular varchar, nvarchar or varbinary values. Also, for backward compatibility reasons any expression that involves string concatenation will return only a string of maximum length 8000 bytes. In order to concatenate larger length strings you have to cast at least one of the values to varchar(max) or nvarchar(max). So in your SELECT list, you could do something like CAST(s.thevalue as varchar(max)) + '<BR/><BR>' + o.thevalue.

2. If you are not using SQL Server 2005 then there is no way to concatenate strings larger than 8000 bytes. You have to either dump the rows into a temporary table and use UPDATETEXT to manipulate each row individually or simply return the values as multiple columns and concatenate on the client-side

Lastly, you should actually leave the presentation to the client side and do these type of operations on the server. What if you want to later introduce a paragraph break or ident the line? What if the formatting is more complex? You should just return the strings as is to the client and then format it there.

|||

Cheers for the tip. Unfortunately I am still using Sql Server 2000 :(

I tried creating a function but you can't have variables of type ntext in functions.

Is there anyway I can do this in SQL Server 2000?

|||I am using sql server 2000 and try using table temp.

Whats wrong?

ROBUST PLAN. Somebody help?

---

No

se puede crear una fila de tabla de trabajo más larga que el máximo

admitido. Vuelva a enviar la consulta con la sugerencia ROBUST PLAN.

TRANSLATION

Quote:

Originally Posted by

A

row of table of work cannot be created longer than the admitted

maximum. Return to send the consultation with suggestion ROBUST PLAN.

Cant create line in table work longer,

CREATE TABLE temp
(
Proc_id INT,
Proc_Name SYSNAME,
Definition NTEXT
)

-- get the names of the procedures that meet our criteria
INSERT temp(Proc_id, Proc_Name)
SELECT id, OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY id, OBJECT_NAME(id)
HAVING COUNT(*) > 1

--Inicializar the NTEXT column
UPDATE temp SET Definition=''

CODE --

DECLARE

@.txtPval binary(16),

@.txtPidx INT,

@.curName SYSNAME,

@.curtext NVARCHAR(4000)
--

--
DECLARE C CURSOR

LOCAL FORWARD_ONLY STATIC READ_ONLY FOR

SELECT OBJECT_NAME(id), text

FROM syscomments s

INNER JOIN #MYTEMP t

ON s.id = t.Proc_id

ORDER BY id, colid

OPEN c -> HERE TELL ME ERROR about robust plan.

FETCH NEXT FROM c INTO @.curname, @.curtext

--Start Loop

WHILE (@.@.FETCH_STATUS = 0 )
BEGIN
--get pointer for the current procedure name colid
SELECT @.txtPval = TEXTPTR(Definition)
FROM temp
WHERE Proc_Name = @.curname

--find out where to append the @.temp table′s value

SELECT @.txtPidx = DATALENGTH(Definition)/2
FROM temp
WHERE Proc_Name = @.curName

--Apply the append of the current 8kb chunk
UPDATETEXT temp.definition @.txtPval @.txtPidx 0 @.curtext
FETCH NEXT FROM c INTO @.curName, @.curtext
END

-- check what was produced
SELECT Proc_name, Definition, DATALENGTH(Definition)/2
FROM temp

-- check our filter
SELECT Proc_Name, Definition
FROM temp
WHERE definition LIKE '%foobar%'

--Clean up

DROP TABLE temp
CLOSE c
DEALLOCATE c
Thanks for try help.

manipulating ntext in stored procedures

Hi,

I have been trying to write a SP where, in the result set is brought back, two ntext columns are combined with a text string, However I get an error when I click on apply;

"Error 403: Invalid operator for data type. Operator equals add, type equals ntext"

I can only assume that this means you can't add (or even manipulate) ntext columns in a SP.

Short of changing the sp to bring back 3 columns and to do the manipulation in my c# code I can't think of a way round this.

Anyone with any ideas?

thanks in advance

rich

SP code ( I have highlighted the problem area):

CREATE PROCEDURE [dbo].[usp_RCTReportQuery]

@.PersonType varchar(255),
@.Status varchar(255),
@.Oustanding varchar(255)

AS

DECLARE @.PersonTypeID int,
@.StatusID int,
@.OustandingID int

SELECT @.PersonTypeID = PersonTypeID FROM TBL_LU_People_Type WHERE PersonType = @.PersonType
SELECT @.StatusID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Status
SELECT @.OustandingID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Oustanding

SELECT surname as [Potential Claimant], s.thevalue + '<BR /><BR />' + o.thevalue as [Status]
FROM tbl_people p
INNER JOIN tbl_Lu_people lup on lup.personid = p.personid and persontypeid = @.PersonTypeID
LEFT JOIN tbl_memo s on s.caseID = p.caseID and s.memotypeid = @.StatusID
LEFT JOIN tbl_memo o on o.caseID = p.caseID and o.memotypeid = @.OustandingID
GO

Richie:

If you are running SQL Server 2005 you might be able to leverage a function to work around this issue. Perhaps a function like this:

alter function dbo.appendText
( @.prm_textIn nvarchar(max),
@.prm_appendString varchar (8000)
)
returns nvarchar (max)
as

begin

return ( @.prm_textIn + @.prm_appendString )

end

This is coded as a scalar function; you might be better off setting it up as an inline function so that you can potentially leverage it in the future with the CROSS JOIN operator for other purposes. Below is an example:

declare @.what nvarchar (max) set @.what = ''
declare @.firstPart varchar (40) set @.firstPart = ' ** This is'
declare @.secondPart varchar (40) set @.secondPart = ' a test. **'

select @.what = @.what + ' #' + convert (nvarchar(5), iter)
from small_iterator (nolock)

select len (@.what) as [len],
substring (dbo.appendText (@.what, @.firstPart + @.secondPart), len(@.what) - 6, 30)
as [righthand piece]

-- Sample Output: --

-- len righthand piece
-- -- --
-- 218263 #32767 ** This is a test.

Dave

|||if you are on 2005, you could just use a nvarchar(MAX) column instead of ntext.

www.elsasoft.org|||

Richie:

You might be able to get your query to work in SQL 2005 doing something like this:

SELECT surname as [Potential Claimant], cast (s.thevalue as nvarchar (max)) + '<BR /><BR />' + o.thevalue as [Status]

|||

Please specify the version of SQL Server in your posts so it is easy to suggest the correct solution.

1. If you are using SQL Server 2005 then use varchar(max), nvarchar(max), and varbinary(max). The text, ntext, and image data types have been deprecated. The newer max data types will allow you to manipulate the values on the server-side just like regular varchar, nvarchar or varbinary values. Also, for backward compatibility reasons any expression that involves string concatenation will return only a string of maximum length 8000 bytes. In order to concatenate larger length strings you have to cast at least one of the values to varchar(max) or nvarchar(max). So in your SELECT list, you could do something like CAST(s.thevalue as varchar(max)) + '<BR/><BR>' + o.thevalue.

2. If you are not using SQL Server 2005 then there is no way to concatenate strings larger than 8000 bytes. You have to either dump the rows into a temporary table and use UPDATETEXT to manipulate each row individually or simply return the values as multiple columns and concatenate on the client-side

Lastly, you should actually leave the presentation to the client side and do these type of operations on the server. What if you want to later introduce a paragraph break or ident the line? What if the formatting is more complex? You should just return the strings as is to the client and then format it there.

|||

Cheers for the tip. Unfortunately I am still using Sql Server 2000 :(

I tried creating a function but you can't have variables of type ntext in functions.

Is there anyway I can do this in SQL Server 2000?

|||I am using sql server 2000 and try using table temp.

Whats wrong?

ROBUST PLAN. Somebody help?

---

No

se puede crear una fila de tabla de trabajo más larga que el máximo

admitido. Vuelva a enviar la consulta con la sugerencia ROBUST PLAN.

TRANSLATION

Quote:

Originally Posted by

A

row of table of work cannot be created longer than the admitted

maximum. Return to send the consultation with suggestion ROBUST PLAN.

Cant create line in table work longer,

CREATE TABLE temp
(
Proc_id INT,
Proc_Name SYSNAME,
Definition NTEXT
)

-- get the names of the procedures that meet our criteria
INSERT temp(Proc_id, Proc_Name)
SELECT id, OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY id, OBJECT_NAME(id)
HAVING COUNT(*) > 1

--Inicializar the NTEXT column
UPDATE temp SET Definition=''

CODE --

DECLARE

@.txtPval binary(16),

@.txtPidx INT,

@.curName SYSNAME,

@.curtext NVARCHAR(4000)
--

--
DECLARE C CURSOR

LOCAL FORWARD_ONLY STATIC READ_ONLY FOR

SELECT OBJECT_NAME(id), text

FROM syscomments s

INNER JOIN #MYTEMP t

ON s.id = t.Proc_id

ORDER BY id, colid

OPEN c -> HERE TELL ME ERROR about robust plan.

FETCH NEXT FROM c INTO @.curname, @.curtext

--Start Loop

WHILE (@.@.FETCH_STATUS = 0 )
BEGIN
--get pointer for the current procedure name colid
SELECT @.txtPval = TEXTPTR(Definition)
FROM temp
WHERE Proc_Name = @.curname

--find out where to append the @.temp table′s value

SELECT @.txtPidx = DATALENGTH(Definition)/2
FROM temp
WHERE Proc_Name = @.curName

--Apply the append of the current 8kb chunk
UPDATETEXT temp.definition @.txtPval @.txtPidx 0 @.curtext
FETCH NEXT FROM c INTO @.curName, @.curtext
END

-- check what was produced
SELECT Proc_name, Definition, DATALENGTH(Definition)/2
FROM temp

-- check our filter
SELECT Proc_Name, Definition
FROM temp
WHERE definition LIKE '%foobar%'

--Clean up

DROP TABLE temp
CLOSE c
DEALLOCATE c
Thanks for try help.

Managing XML field with Enterprise Manager

With SQL Server 2000, I have a table with a field called fldhistory, defined
as a ntext [16] field.
This column is intended for storing some archived history data in XML format.
I have an example XML which is valid and:
1) has <1300 characters, including spaces
2) Wel-formed, readable by IE
3) <10 lines
However, I can't put anything more than say a few hundred charaters in this
column under Enterprise Manager (for testing purposes), the past option is
simply disabled and if I try ctrl-V, I get a Windows warning tone.
why is this and how can I fix this? the ntext column should be capable of
handling >1300 characters!
Hello,
Please refer to the following information in SQL server Books Online(BOL):
Topic: Adding ntext, text, or image Data to Inserted Rows
These are ways to add ntext, text, or image values to a row:
"Specify relatively short amounts of data in an INSERT statement in the
same way char, nchar, or binary data is.
"Use the WRITETEXT statement. For more information, see WRITETEXT.
"ADO applications can use the AppendChunk method to specify long amounts
of ntext, text, or image data. For more information, see Managing Long Data
Types.
"OLE DB applications can use the ISequentialStream interface to write new
ntext, text, or image values. For more information, see BLOBs and OLE
Objects.
"ODBC applications can use the data-at-execution form of SQLPutData to
write new ntext, text, or image values. For more information, see Managing
text and image Columns.
"DB-Library applications can use the dbwritetext function. For more
information, see Text and Image Functions.
You can use above ways to insert ntext data. Please also refer to the
following topics in BOL:
"Using text and image Data"
"Managing ntext, text, and image Data"
"text, ntext, and image Data When text in row Is Set to ON"
You can also refer to the following articles which provide good information:
194975 How To Read and Write BLOBs Using GetChunk and AppendChunk
http://support.microsoft.com/?id=194975
258038 How To Access and Modify SQL Server BLOB Data by Using the ADO Stream
http://support.microsoft.com/?id=258038
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Managing XML field with Enterprise Manager

With SQL Server 2000, I have a table with a field called fldhistory, defined
as a ntext [16] field.
This column is intended for storing some archived history data in XML format.
I have an example XML which is valid and:
1) has <1300 characters, including spaces
2) Wel-formed, readable by IE
3) <10 lines
However, I can't put anything more than say a few hundred charaters in this
column under Enterprise Manager (for testing purposes), the past option is
simply disabled and if I try ctrl-V, I get a Windows warning tone.
why is this and how can I fix this? the ntext column should be capable of
handling >1300 characters!Hello,
Please refer to the following information in SQL server Books Online(BOL):
Topic: Adding ntext, text, or image Data to Inserted Rows
---
These are ways to add ntext, text, or image values to a row:
" Specify relatively short amounts of data in an INSERT statement in the
same way char, nchar, or binary data is.
" Use the WRITETEXT statement. For more information, see WRITETEXT.
" ADO applications can use the AppendChunk method to specify long amounts
of ntext, text, or image data. For more information, see Managing Long Data
Types.
" OLE DB applications can use the ISequentialStream interface to write new
ntext, text, or image values. For more information, see BLOBs and OLE
Objects.
" ODBC applications can use the data-at-execution form of SQLPutData to
write new ntext, text, or image values. For more information, see Managing
text and image Columns.
" DB-Library applications can use the dbwritetext function. For more
information, see Text and Image Functions.
---
You can use above ways to insert ntext data. Please also refer to the
following topics in BOL:
"Using text and image Data"
"Managing ntext, text, and image Data"
"text, ntext, and image Data When text in row Is Set to ON"
You can also refer to the following articles which provide good information:
194975 How To Read and Write BLOBs Using GetChunk and AppendChunk
http://support.microsoft.com/?id=194975
258038 How To Access and Modify SQL Server BLOB Data by Using the ADO Stream
http://support.microsoft.com/?id=258038
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Managing XML field with Enterprise Manager

With SQL Server 2000, I have a table with a field called fldhistory, defined
as a ntext [16] field.
This column is intended for storing some archived history data in XML format
.
I have an example XML which is valid and:
1) has <1300 characters, including spaces
2) Wel-formed, readable by IE
3) <10 lines
However, I can't put anything more than say a few hundred charaters in this
column under Enterprise Manager (for testing purposes), the past option is
simply disabled and if I try ctrl-V, I get a Windows warning tone.
why is this and how can I fix this? the ntext column should be capable of
handling >1300 characters!Hello,
Please refer to the following information in SQL server Books Online(BOL):
Topic: Adding ntext, text, or image Data to Inserted Rows
---
These are ways to add ntext, text, or image values to a row:
" Specify relatively short amounts of data in an INSERT statement in the
same way char, nchar, or binary data is.
" Use the WRITETEXT statement. For more information, see WRITETEXT.
" ADO applications can use the AppendChunk method to specify long amounts
of ntext, text, or image data. For more information, see Managing Long Data
Types.
" OLE DB applications can use the ISequentialStream interface to write new
ntext, text, or image values. For more information, see BLOBs and OLE
Objects.
" ODBC applications can use the data-at-execution form of SQLPutData to
write new ntext, text, or image values. For more information, see Managing
text and image Columns.
" DB-Library applications can use the dbwritetext function. For more
information, see Text and Image Functions.
---
You can use above ways to insert ntext data. Please also refer to the
following topics in BOL:
"Using text and image Data"
"Managing ntext, text, and image Data"
"text, ntext, and image Data When text in row Is Set to ON"
You can also refer to the following articles which provide good information:
194975 How To Read and Write BLOBs Using GetChunk and AppendChunk
http://support.microsoft.com/?id=194975
258038 How To Access and Modify SQL Server BLOB Data by Using the ADO Stream
http://support.microsoft.com/?id=258038
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Managing ntext, text with a long text data

Hi,
I have a problem to insert(update) a long text (more than 64K) into
SQL 2000 (datatype - 'text'). It cuts the data and insert only 64K.
MSDN says: "When the ntext, text, and image data values get larger,
however, they must be handled on a block-by-block basis. Both
Transact-
SQL and the database APIs contain functions that allow applications to

work with ntext, text, and image data block by block." Could somebody

give me an example how to do this, please.
Thank youThere are examples under UPDATETEXT and WRITETEXT in Books Online - do
these cover what you're trying to do? The MSSQL Resource Kit also has a
whole chapter on working with BLOBs, including a number of examples
using TSQL and ADO:

http://www.microsoft.com/technet/pr...art3/c1161.mspx

Simon|||igorsl (igorsl@.yahoo-dot-com.no-spam.invalid) writes:
> I have a problem to insert(update) a long text (more than 64K) into
> SQL 2000 (datatype - 'text'). It cuts the data and insert only 64K.
> MSDN says: "When the ntext, text, and image data values get larger,
> however, they must be handled on a block-by-block basis. Both
> Transact-
> SQL and the database APIs contain functions that allow applications to
> work with ntext, text, and image data block by block." Could somebody
> give me an example how to do this, please.

I believe this limitation is in the client API rather than in T-SQL
itself. (Altough inserting a 1MB value through a plain INSERT is not
that performant.) Which API are you using?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp