• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 580
  • Last Modified:

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

0
Ron Malmstead
Asked:
Ron Malmstead
1 Solution
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Aaron TomoskyTechnology ConsultantCommented:
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 TomoskyTechnology ConsultantCommented:
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now