We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL 2k ...8000 length limit to varchar...now what?

Medium Priority
590 Views
Last Modified: 2012-08-13
The below sp builds a SELECT + UNION ALL from a table of "incoming feeds" which represent actual tables in my DB,.. then returns the results.

I have too many entries now because the select statement is grown over 8000 characters in length and it won't run now.

So....

Now what do I do?
I have to make it work somehow, and i'm not sure how I can split this up.
 
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER  PROCEDURE dbo.SearchInFeedUrlsForKeyWord(@Keyword varchar(50))
AS
SET NOCOUNT ON

DECLARE @FeedId int
DECLARE @FeedName varchar(100)
DECLARE @FeedTableName varchar(200)
DECLARE @MainSQL varchar(8000)
DECLARE @PreSQL varchar(8000)
DECLARE @PostSQL varchar(8000)

--get cursor for all in feeds
DECLARE InFeedsCursor CURSOR LOCAL FOR
SELECT	[Id], FeedName
FROM	tbl_Feeds
WHERE IsNull(IsOutFeed,0) = 0

--loop through cursor and populate temptable with data
OPEN InFeedsCursor
FETCH NEXT FROM InFeedsCursor INTO @FeedId,@FeedName
SET @MainSql = ''
WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT @FeedTableName = 'lbf_' + CONVERT(varchar(10),@FeedId) + '_' + REPLACE(@FeedName,' ', '_')
	SET @MainSQL = @MainSQL + 'SELECT ''' + @FeedName + ''' As FeedName,URL, [TimeStamp] FROM [dbo].' + @FeedTableName + ' WITH (READPAST) WHERE URL Like ''%' + @Keyword + '%'' UNION ALL '
	FETCH NEXT FROM InFeedsCursor INTO @FeedId,@FeedName
END

--remove last union all
SET @MainSQL = LTRIM(SUBSTRING(@MainSQL,0,LEN(@MainSQL) - 9))
SET @PreSQL = 'SELECT SourceURL=ISNULL(URL,''NO URL''),URLCount=Count(ISNULL(URL,''NO URL'')),LessThan7DaysOld=Sum(CASE WHEN DATEDIFF(dd,[TimeStamp],GetDate()) <7 THEN 1 ELSE 0 END),[30to60daysOld]=Sum(CASE WHEN DATEDIFF(dd,[TimeStamp],GetDate()) >=30 AND DATEDIFF(dd,[TimeStamp],GetDate()) <=60 THEN 1 ELSE 0 END) FROM ('
SET @PostSQL = ') results GROUP BY ISNULL(URL,''NO URL'') ORDER BY COUNT(ISNULL(URL, ''NO URL'')) DESC'

EXEC(@PreSQL + @MainSQL + @PostSQL)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Have a look at this article from a Microsoft SQL Server MVP

http://www.sommarskog.se/dynamic_sql.html#sp_executesqlong
CERTIFIED EXPERT
Top Expert 2012

Commented:
You have have to resort to using Dynamic SQL then you will have to create additional variables as in:
DECLARE @SQL1 varchar(8000), @SQL2 varchar(8000), @SQL3 varchar(8000), ...
EXEC (@SQL1 + @SQL2 + @SQL3 + ...)

There is no other way.
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Aaron TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT

Commented:
Or you could pull some of this logic back into whatever programming language you are using... This is a lot of heavy lifting for tsql
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
aarontomosky,

I hope you're joking, because RDBMS are designed specifically for heavy lifting. I also would not want to drag millions of records into the front end across the wire just to crunch it using a custom aggregation function for 0.1% the number of rows in GROUP BY results.
Just put a counter in the loop, when you reach 8000 chars do an else and put remaining string concatenation in a second 8000 variable. Then just concatenate them when you issue your execute.
 
DECLARE   @SQL1 VARCHAR(8000),
                   @SQL2 VARCHAR(8000),
SET @SQl1 = '8000 characters here'
SET @SQl2 = '8000 characters here'
EXEC (@SQL1 + @SQL2 )

If you need it explained further let me know. Or just check out this article, it has several options you could implement in your sp:
http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/sql-2000-when-8000-characters-is-not-eno
Aaron TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT

Commented:
Cyberkiwi,
What I was referring to by heavy lifting was the use of curser and the flow of the whole thing. Heavy lifting was not the best phrase. This whole proc looks like someone who is fluent in another language is bending SQL to their will. It's well written and creative and shows skill in SQL but I think if we were given the desired output and schema a much cleaner solution could be had.

For a quick mod I think cyberkiwi's solution of the temptable is exactly the direction I would go.
Ron MalmsteadInformation Services Manager
CERTIFIED EXPERT

Author

Commented:
Cyberwiki, your solution worked right out of the gate and was actually what I had in mind to begin with, I just didn't know how to make it work.

While creative and skilled...my old sql guy had a habit of building sql strings like this.
...now i'm going to apply this idea to other 15 sp's that are broken for the same reason...lol  ;)


Thanks to everyone.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.