READTEXT into variable
-
How to you assign the output from READTEXT into a variable?
I've tried SET and SELECT @var=READTEXT
and it throws an error
-
It throws an error because you cannot do this.
Text values greater than 8k cannot be stored in a page of memory therefor you cannot assin the value to a variable, because a variable cannot exceed the size of a page.
when managing text you have to place the text in memory and use a textpointer to manage the location of the data.
But in any case you cannot manage/manipulate text data in memory with t-sql.
Using Text columns requires alot of extra work. I have been luckily able to avoid using them. You have to evaluate if managing text/image data in a database is worth it.
Books Online:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_13_8orl.asp
-
Thanks Ray for replying
I already know BOL, and text pointers.
I used text because there was no alternative, and I need to manage very large code strings for later EXECute it.
I expect READTEXT to return short strings that could be placed in VARCHARS, and since I can see what's returned in the Query Analyzer, there should be some way to store it in a var.
Is this IMPOSSIBLE?
If so..... what's the REAL utility of readtext?!
-
I don't know, I was reading on MSDN when I looked up Readtext it notes that the readtext function is going away.
and the value you see in query analyzer is only the first 8000 characters.
Even microsoft doesn't use text when storing text for stored procedures. If you look at syscomments, this table is what holds the stored procedure definitions and such, and if you write a proc that is > 8000 characters, it actually saves 2 or more rows to hold all the characters.
So you may want to find an alternate way of storing these large strings.
Even then executing these strings using t-sql will be impossible. You will probably have to write an external api to read the text and execute it outside of sql server.
-
arrrg! I only need to catch a little piece from a text file!!!!
M$ really impresses me xDD ... they are stupid or what?
Why did they call a function READTEXT, if it doesn't read???
I swear I'll find a solution!
MS bastards... your are losing my precious time :>
-
I finally got it!
My solution is a bit bizarre, but sure works.
Tomorrow I'll post it.
C U!!!
-
Why not to use simple SUBSTRING ?
You can use start > 8000 only length must be within 8000 (4000 for "N" types).
_____________
Code for TallyGenerator -
thanks sergiy
I tried it, but can't get it working because I cannot declare any text variable inside a procedure
SET @v-2 = SUBSTRING( (SELECT Code FROM tmp_AuditTriggerCustomized)
, @StartPos
, @Lenght )
The text, ntext, and image data types are invalid in this subquery or aggregate expression.
-
Use CONVERT(varchar(256),text_field) AS First256Text, you can adjust the varchar size for the desired length.
Andy
-
Declare text vriable as a parameter of your SP.
_____________
Code for TallyGenerator -
Declare the variable as varchar.
DECLARE @v-2 VARCHAR(8000)
SELECT @v-2 = SUBSTRING(Code,@StartPos, @Length) FROM tmp_AuditTriggerCustomised
You'll get an error if you give a length over 8000 (since the variable can't hold more than 8000 characters)
The restriction of varchars to 8000 has been removed in SQL 2005 and you can declare VARCHAR(max) which can hold up to 2^31-1 bytes
Note that READTEXT is deprecated and should not be used.
Is you've noticed, text fields are quite a pain to work with. Best avoided unless absolutely necessary
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Author posted:
arrrg! I only need to catch a little piece from a text file!!!!
My solution absolutely fits this task.
_____________
Code for TallyGenerator -
<sarcasm>Well excuse me for trying to help</sarcasm>
Just offerng an additional possibility that doesn't require adding an additional parameter to the procedure to deal with the output of substring.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hi!
It's been some days since this thread.
I just liked to thank everybody who helped me upon a solution.
Sergiy, thanks for the SUBSTRING clue. As I said some days before I found a paralel solution, but finally ended using SUBSTRING function.
TKXXX!!!!
Well... as allways, I'll document the thread in case someone does a search over the same problem.
SET @STRING1 = (SELECT cast(SUBSTRING(TextFIELD1, 1, 8000) as varchar(8000)) FROM TABLE1)
SET @STRING2 = (SELECT cast(SUBSTRING(TextFIELD1, 8001, 8000) as varchar(8000)) FROM TABLE1)
PRINT @STRING1
PRINT @STRING2
EXECUTE (@STRING1 + @STRING2)
This was my bizarre solution
The problem is about to replace some code in brackets (<% %>
from a full trigger stored in a text field.This macro code in brackets will explode in a bigger common SQL code.
CREATE PROCEDURE sp_FindFirstBlock_Main @StartTag varchar(10) = '<%'
, @EndTag varchar(10) = '%>'
AS
DECLARE
@StartPos int
, @EndPos int
, @PtrText varbinary(16)
, @Lenght int
SET @StartPos = ( SELECT CHARINDEX(@StartTag, Code)-1 FROM tmp_AuditTriggerCustomized )
SET @EndPos = ( SELECT CHARINDEX(@EndTag, Code)-1 FROM tmp_AuditTriggerCustomized ) + LEN(@EndTag)
SET @PtrText = ( SELECT TEXTPTR(Code) FROM tmp_AuditTriggerCustomized )
SET @Lenght = @EndPos - @StartPos
READTEXT tmp_AuditTriggerCustomized.Code @PtrText
@StartPos
@Lenght
GO
CREATE PROCEDURE sp_FindFirstBlock @StartTag varchar(10) = '<%'
, @EndTag varchar(10) = '%>'
, @Block varchar(4000) OUTPUT
AS
CREATE TABLE #T1( MacroCode varchar(4000) )
INSERT INTO #T1
EXEC sp_FindFirstBlock_Main @StartTag, @EndTag
SET @Block = (SELECT TOP 1 MacroCode FROM #T1)
GO
So the call is ....
----REPLACE MACROS
SET @StartTag = '<%'
SET @EndTag = '%>'
--Seeks all the macro code blocks
EXEC sp_FindFirstBlock @StartTag, @EndTag, @Block = @CurrBlock OUTPUT
WHILE @CurrBlock <> ''
BEGIN
--Explode Macro code block
SET @SqlString = REPLACE( REPLACE(@CurrBlock, @StartTag, ''), @EndTag, '')--trim tags <% %>
SET @SqlString = N'Select @Out = ' + @SqlString
PRINT ' - [sp_CreateAuditTrigger] Macro Block: '
PRINT @SqlString
EXEC sp_executesql @SqlString
, N'@Out nvarchar(4000) OUTPUT'
, @Out = @NewText OUTPUT
SET @OldText = @CurrBlock
---- !! REPLACE !!
SET @PtrText = ( SELECT TEXTPTR(Code) FROM tmp_AuditTriggerCustomized )
SET @Len = LEN( @OldText )
SET @Pos = ( SELECT CHARINDEX(@OldText, Code)-1 FROM tmp_AuditTriggerCustomized )
WHILE @Pos <> -1
BEGIN
UPDATETEXT tmp_AuditTriggerCustomized.Code @PtrText
@Pos
@Len
@NewText
SET @Pos = ( SELECT CHARINDEX(@OldText, Code)-1 FROM tmp_AuditTriggerCustomized )
END
---- ¡¡ REPLACE ¡¡
EXEC sp_FindFirstBlock @StartTag, @EndTag, @Block = @CurrBlock OUTPUT
END
-
I know this thread is going on 2 years old, however using it and other sources I have managed to cobble together a pretty decent solution for SQL 2000.
My problem was that I needed to send a bunch of T-SQL code from C#.NET to SQL Server 2000. Much as I wish we were on SQL 2005 and had varchar(Max) it wasn't an option. Since the amount of code frequently exceeded 8000 characters I needed to use an NText variable type. However, EXEC doesn't take an NText parameter. After trying many different ideas I finally decided to buckle and parse the NText parameter into a bunch of nvarchar parameters, string them together, and exec the whole thing.
Unfortunately you can't do much with an NText parameter so I created a temporary table and stuck it in there to play with it.
In my case the destination table was a global temporary table. Please don't flame me, I didn't write the original code and can't get permission to rewrite that part. However if you want to flame me for pointlessly using the Where clause when there is only one entry in the table, go for it 🙂
-- @sqlFromWhere is the NText input parameter
-- @sqlSelect is an nvarchar input parameter
-- @verbose is a bit input parameter
-- after creating a table and dumping the text into it I need to append a bit of SQL onto the front of it using UpdateText.
-- Create and populate the temporary table in one step
SELECT @sql = @sqlSelect + ' INTO ##input '
-- Manipulate, parse, and execute the text variable
Declare @ptrval binary(16), @iLength decimal, @iNum int, @iCount int, @iPlace int, @tmpSql nvarchar(4000), @iTmpLength int
Create Table #tblFromWhere(id int, statement ntext)
Insert Into [#tblFromWhere] ([id],[statement]) VALUES (1,@sqlFromWhere)
Select @ptrval = TEXTPTR([statement]) From [#tblFromWhere] Where [id] = 1
UpdateText #tblFromWhere.[statement] @ptrval 0 0 @sql
Select @iLength = DataLength([statement]) From [#tblFromWhere] Where [id] = 1
Set @iNum = Ceiling(@iLength/4000)
Set @iCount = 0
Set @tmpSql = ''
Set @sql = 'Exec ('
IF @verbose = 1 Begin
print 'Length of NText param: ' + Cast(@iLength as varchar(10))
print 'Num of loops required: ' + Cast(@iNum as varchar(10))
End
While @iCount < @iNum Begin
Set @iCount = @iCount + 1
Set @iPlace = (@iCount * 4000) - 4000 --+ 1
Set @iTmpLength = 4000
If @iCount = @iNum Begin Set @iTmpLength = @iLength-@iPlace End
Set @tmpSql = @tmpSql + 'Declare @StmntSeg' + Cast(@iCount as varchar(10)) + ' nvarchar(4000) '
Set @tmpSql = @tmpSql + 'Select @StmntSeg' + Cast(@iCount as varchar(10)) + ' = SubString([statement],'+Cast(@iPlace as varchar(20))+','+Cast(@iTmpLength as varchar(20))+') From #tblFromWhere Where [id] = 1 '
Set @sql = @sql + '@StmntSeg' + Cast(@iCount as varchar(10))
If @iCount < @iNum Begin Set @sql = @sql + '+' End
End
IF @verbose = 1 Begin
print '[Create and populate the temporary table in one step]'
SELECT @tmpSql + @sql + ')' AS [Create and populate the temporary table in one step]
print @tmpSql + @sql + ')'
End
EXEC (@tmpSql + @sql + ')')
-----------
I know the code is a pain to follow and I really don't expect anyone to look at it unless they are as desperate as I was. It should pretty much work out of the box for its intended purpose. Here is a look at what the output looks like...
Declare @StmntSeg1 nvarchar(4000)
Select @StmntSeg1 = SubString([statement],0,4000) From #tblFromWhere Where [id] = 1
Declare @StmntSeg2 nvarchar(4000)
Select @StmntSeg2 = SubString([statement],4000,4000) From #tblFromWhere Where [id] = 1
Declare @StmntSeg3 nvarchar(4000)
Select @StmntSeg3 = SubString([statement],8000,4000) From #tblFromWhere Where [id] = 1
Declare @StmntSeg4 nvarchar(4000)
Select @StmntSeg4 = SubString([statement],12000,3872) From #tblFromWhere Where [id] = 1
Exec (@StmntSeg1+@StmntSeg2+@StmntSeg3+@StmntSeg4)
On this run the NText parameter got broken into four separate pieces, then strung together and executed.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply