SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Another "Overcome the 8000 varchar limit" question
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 01/06/2010 :  01:50:32  Show Profile  Visit russell's Homepage  Reply with Quote
Nothing like reviving 4 year old topics with irrelavent gibberish. Hmmm, maybe just to post a link?

Free spam, click here
Go to Top of Page

shell_l_d
Starting Member

Australia
17 Posts

Posted - 06/02/2010 :  20:59:57  Show Profile  Reply with Quote
I'm interested in the solution to this for SQL 2000 (SQL Query Analyser / Stored Procedure)...
eg: here's what I have & cant seem to beat the 8000 limit by adding 3 together:


-- declare variables for the cursor
DECLARE @tLine integer
DECLARE @tCount integer
DECLARE @tWorkFlow varchar(300)
DECLARE @strWorkFlow1 varchar(8000)
DECLARE @strWorkFlow2 varchar(8000)
DECLARE @strWorkFlow3 varchar(8000)

SET @tCount = 0
SET @tLine = 1
SET @strWorkFlow1 = (SELECT '')
SET @strWorkFlow2 = (SELECT '')
SET @strWorkFlow3 = (SELECT '')

-- prepare the string using a cursor
DECLARE tblCursor SCROLL CURSOR FOR 
	SELECT 
		RTRIM(chngStatus) + ' by ' + RTRIM(employee) + ' on ' + 
			CAST( DATEPART(dd,   chngDate) as varchar(2) ) + 
			CAST( DATENAME(mm,   chngDate) as varchar(3) ) + 
			CAST( DATEPART(yyyy, chngDate) as varchar(4) ) + ' ' +
			CONVERT(varchar, chngDate, 108 ) as [workFlow]
	FROM @tblTicketHistory

OPEN tblCursor
FETCH NEXT FROM tblCursor INTO @tWorkFlow

WHILE @@fetch_status = 0 
BEGIN

	SET @tCount = @tCount + 1
	PRINT '--------------------COUNT---------------------------'
	PRINT @tCount
	PRINT @tWorkFlow
	PRINT LEN( @tWorkFlow )

	IF (@tCount = 1)
		SET @strWorkFlow1 = RTRIM(@tWorkFlow) 
	ELSE
	BEGIN
		IF (@tLine = 1)
		BEGIN
			PRINT 'line 1 code'
			IF (LEN(@tWorkFlow) + LEN(@strWorkFlow1)) <= 8000
				SET @strWorkFlow1 = @strWorkFlow1 + ' > ' + RTRIM(@tWorkFlow) 
			ELSE
				SET @tLine = @tLine + 1
		END
		IF (@tLine = 2)
		BEGIN
			PRINT 'line 2 code'
			IF (LEN(@tWorkFlow) + LEN(@strWorkFlow2)) <= 8000
				SET @strWorkFlow2 = @strWorkFlow2 + ' > ' + RTRIM(@tWorkFlow)
			ELSE
				SET @tLine = @tLine + 1
		END
		IF (@tLine = 3)
		BEGIN
			PRINT 'line 3 code'
			IF (LEN(@tWorkFlow) + LEN(@strWorkFlow3)) <= 8000
				SET @strWorkFlow3 = @strWorkFlow3 + ' > ' + RTRIM(@tWorkFlow)
			ELSE
				SET @tLine = @tLine + 1
		END
	END
	PRINT 'LINE'
	PRINT @tLine
	
	FETCH NEXT FROM tblCursor INTO @tWorkFlow

END

PRINT '--------------------EXEC---------------------------'
-- ERROR: Must declare the variable '@strWorkFlow1'
Exec( 'SELECT @strWorkFlow1 + @strWorkFlow2 + @strWorkFlow3' )		-- doesnt actually return/display anything at all

PRINT '--------------------SELECT---------------------------'
SELECT @strWorkFlow1 as [str1]
SELECT @strWorkFlow2 as [str2]
SELECT @strWorkFlow3 as [str3]
SELECT @strWorkFlow1 + @strWorkFlow2 + @strWorkFlow3 as [all3]		-- truncates to 8000
--SELECT CONVERT(text, @strWorkFlow1 + @strWorkFlow2 + @strWorkFlow3)	-- truncates to 8000
--SELECT CAST(@strWorkFlow1 + @strWorkFlow2 + @strWorkFlow3 AS TEXT)	-- truncates to 8000

PRINT '--------------------PRINT---------------------------'
PRINT LEN( @strWorkFlow1 )
PRINT @strWorkFlow1
PRINT LEN( @strWorkFlow2 )
PRINT @strWorkFlow2
PRINT LEN( @strWorkFlow3 )
PRINT @strWorkFlow3
PRINT ( LEN( @strWorkFlow1 ) + LEN( @strWorkFlow2 ) + LEN( @strWorkFlow3 ) )
PRINT LEN( @strWorkFlow1 + @strWorkFlow2 + @strWorkFlow3 )  	-- max 8000
PRINT @strWorkFlow1 + @strWorkFlow2 + @strWorkFlow3  		-- truncates to 8000


CLOSE tblCursor
DEALLOCATE tblCursor

Edited by - shell_l_d on 06/02/2010 21:27:37
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000