Text Pointers
-
I have a table containing a text field. The text field normally contains well in excess of 8000 bytes so a varchar cannot be used.
Consider the following query...
SELECT CT.CONSUMER, TEXTPTR(CT.CONS_TEXT)
FROM RECOMMEND..CONS_TEXT CT
If we wish to print out the text pointer as a VARCHAR we might try...
SQL 1 = PRINT 'Text Pointer: ' + @CUST_TXTPTR
(yields a data type conversion error)
SQL 2 = PRINT 'Text Pointer: ' + CAST(@CUST_TXTPTR AS VARCHAR(1000))
(yields "Text Pointer: ÿÿãC€" which is incorrect format)
SQL 3 = PRINT @CUST_TXTPTR
(yields "0xFFFFE343800100006C58080001000400" - correct format but not converted to varchar variable for generating dynamic SQL).
Question: How can the text pointer be cast for insertion into a varchar variable that can be used for generating dynamic SQL?
-
G'day,
The following is taken from books on line. I *think* you could construct the readtext statement dynamically. In other words, I think you need to use READTEXT instead of SELECT to retrieve the actual text.
CREATE TABLE t1 (c1 int, c2 text)
EXEC sp_tableoption 't1', 'text in row', 'on'
INSERT t1 VALUES ('1', 'This is text.')
GO
BEGIN TRAN
DECLARE @ptrval VARBINARY(16)
SELECT @ptrval = TEXTPTR(c2)
FROM t1
WHERE c1 = 1
READTEXT t1.c2 @ptrval 0 1
COMMIT
Hope this helps
Wayne
-
OK...Here's the big picture...I need to loop through over 230,000 consumers and their custom text sitting on the database. I need to generate a separate text file for each customer on a network server.
To facilitate this, I am creating a dynamic READTEXT statement for each customer.
The problem is converting the value of the TEXT POINTER from a varbinary to a char or varchar in order to construct the READTEXT SQL code.
/****************************************************************************************/
/*** EXPORT TEXT DATE TO FLAT FILES ***/
/****************************************************************************************/
/****************************************************************************************/
SET NOCOUNT ON
DECLARE @CONSUMER VARCHAR(11), @CUST_TXTPTR VARBINARY(16), @SQLTEXT VARCHAR(1000),
@COUNTER INTEGER, @FILENAME VARCHAR(8000), @DATALENGTH INTEGER
SELECT @COUNTER = 0
--1. GET CUSTOMERS - THIS CAN BE A FORWARD-ONLY CURSOR IN PRODUCTION
PRINT '1. Get Customers'
DECLARE customers_cursor CURSOR FAST_FORWARD FOR
SELECT CT.CONSUMER, TEXTPTR(CT.CONS_TEXT)
FROM DBO.CONS_TEXT CT
ORDER BY CT.CONSUMER
OPEN customers_cursor
FETCH NEXT FROM customers_cursor INTO @CONSUMER, @CUST_TXTPTR
PRINT '2. Loop through customers and write text to spool directory.'
WHILE @@FETCH_STATUS = 0 AND @COUNTER <= 10 --Comment out the counter filter after debugging
BEGIN
--Set the counter value and the filename value
SET @COUNTER = @COUNTER + 1
SET @FILENAME = '\\servername\sharename\CUSTOM_' + LEFT(@CONSUMER,10) + '.TXT'
SELECT @DATALENGTH = (SELECT DATALENGTH(CT.CONS_TEXT) FROM DBO.CONS_TEXT CT WHERE CT.CONSUMER = @CONSUMER)
SET @SQLTEXT = 'READTEXT CONS_TEXT.CONS_TEXT CUST_TXTPTR 0 DATALENGTH '
SET @SQLTEXT = REPLACE(@SQLTEXT,'CUST_TXTPTR',@CUST_TXTPTR) --THIS IS THE PIECE THAT DOES NOT WORK – CANNOT CONVERT TO TEXT
SET @SQLTEXT = REPLACE (@SQLTEXT,'DATALENGTH',CAST(@DATALENGTH AS VARCHAR(6)))
--For diagnostics - print variable values
PRINT 'Record Number: ' + CONVERT(VARCHAR(7),@COUNTER)
PRINT 'Consumer: ' + CONVERT(VARCHAR(11),@CONSUMER)
--THIS ONE BELOW DOES NOT WORK, CANNOT CONVERT/CAST TEXT POINTER TO CHAR/VARCHAR DATA TYPE
PRINT 'Text Pointer: ' + CAST(CAST(@CUST_TXTPTR AS VARBINARY) AS VARCHAR(1000))
--THIS ONE BELOW WORKS FINE
PRINT @CUST_TXTPTR
PRINT @DATALENGTH
PRINT @FILENAME
PRINT @SQLTEXT
--Dump EMail message to file – from “2 guys from rolla”
EXEC USP_OutputToFile 'servername’, 'database', 'uid’, 'pwd', @FILENAME, @SQLTEXT
-- fetch the next customer from the cursor and increment the counter
FETCH NEXT FROM customers_cursor
INTO @CONSUMER, @CUST_TXTPTR
IF (SELECT @counter % 10) = 0
BEGIN
PRINT Convert(varchar(7),@COUNTER) + ' EMail messages written to queue.'
END
END
CLOSE customers_cursor
DEALLOCATE customers_cursor
SET NOCOUNT OFF
--END OF STORED PROCEDURE
--Check your work
exec master..xp_cmdshell 'type \\servername\sharename\CUSTOM_consumeridnumber.TXT'
/****************************************************************************************/
--From 4GuysFrom Rolla
--User Defined Stored Procedure to export a file from SQL Server
/****************************************************************************************/
drop procedure USP_OutputToFile
go
create procedure USP_OutputToFile
@sServer varchar(30),
@sDB varchar(30),
@sUser varchar(30),
@sPWD varchar(30),
@sOutFile varchar(255),
@sSQL varchar(1000)
as
set nocount on
create table #trash (out varchar(1000) null )
declare @sXP varchar(1000),
@nMaxWidth int
select @nMaxWidth = 8000
select @sXP = 'osql' +
' -d' + @sDB +
' -U' + @sUser +
' -P' + @sPWD +
' -S' + @sServer +
' -h-1 ' +
' -w' + convert(varchar,@nMaxWidth) +
' -n' +
' -Q"' + @sSQL + '"' +
' -o' + @sOutFile
insert into #trash(out)
exec master..xp_cmdshell @sXP
go
---------END OF STORED PROCEDURE ---------
--Send results of a query to a text file (replace servername, username, password and this will query your PUBS.Authors table)
EXEC USP_OutputToFile 'servername', 'databasename', 'uid', 'pwd', 'C:\trythis.txt', 'select au_fname, au_lname from authors'
--See the results
exec master..xp_cmdshell 'type c:\trythis.txt'
--- END OF PASTE ------------------
-
I figured out how to do it...
This project is worthy a Question of the Day plus a How-to article sometime soon.
Marty
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply