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

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

LVL 25
Ron MalmsteadInformation Services ManagerAsked:
Who is Participating?
 
cyberkiwiCommented:
Or you could progressively populate a #tempfeed table instead of doing one large UNION ALL at the end.

**Untested, but something like**

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

create table #tempfeed (feedname varchar(500), url varchar(500), timestamp datetime)

--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 = 'INSERT #tempfeed SELECT ''' + @FeedName + ''' As FeedName,URL, [TimeStamp] FROM [dbo].' + @FeedTableName + ' WITH (READPAST) WHERE URL Like ''%' + @Keyword + '%'''
	EXEC (@MainSQL)
	FETCH NEXT FROM InFeedsCursor INTO @FeedId,@FeedName
END

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 = '#tempfeed GROUP BY ISNULL(URL,''NO URL'') ORDER BY COUNT(ISNULL(URL, ''NO URL'')) DESC'

EXEC(@PreSQL + @PostSQL)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Open in new window

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

http://www.sommarskog.se/dynamic_sql.html#sp_executesqlong
0
 
Anthony PerkinsCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Aaron TomoskySD-WAN SimplifiedCommented:
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
0
 
cyberkiwiCommented:
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.
0
 
SQLSergentMikeCommented:
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
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
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.
0
 
Ron MalmsteadInformation Services ManagerAuthor 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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.