Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
 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.24 seconds. Powered By: Snitz Forums 2000