Another "Overcome the 8000 varchar limit" question

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

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Another "Overcome the 8000 varchar limit" question

Author

Topic

mparter
Yak Posting Veteran

86 Posts

Posted - 2005-07-12 : 08:38:48

I have the following Stored Procedure;

CREATE PROC usp_ARS_GetRegisterClassAttendance
(
	@RegisterID int
)
AS
SET NOCOUNT ON
SET DATEFORMAT dmy
DECLARE @SQLStart varchar (100)
DECLARE @SQLDynamic text --(7600)
DECLARE @SQLEnd varchar (250)
DECLARE @SQLFull varchar (8000)

--create a temporary table to hold the class dates for the register
CREATE TABLE #temp (Pivot smalldatetime)
--insert the class dates into the temp table
INSERT INTO #temp SELECT DISTINCT CONVERT(smalldatetime, AttendanceDate, 103) AS Pivot FROM dbo.vw_ARS_StudentClassAttendance WHERE RegisterID = @RegisterID ORDER BY CONVERT(smalldatetime, AttendanceDate, 103) ASC

SET @SQLStart = 'SELECT RegisterID, StudentID, SeqNo, EnrolmentID, StudentName, Status, Programme'
SET @SQLEnd = ' FROM dbo.vw_ARS_StudentClassAttendance WHERE RegisterID = ' + CONVERT(varchar(6), @RegisterID) + ' GROUP BY RegisterID, StudentID, SeqNo, EnrolmentID, StudentName, Status, Programme, stu_surn, stu_fnm1 ORDER BY stu_surn, stu_fnm1'
SET @SQLDynamic = ''
SELECT @SQLDynamic = @SQLDynamic + ', MAX(CASE AttendanceDate WHEN ''' + CAST(DATEPART(day, Pivot) AS varchar) + '/' + CAST(DATEPART(month, Pivot) AS varchar) + '/' + CAST(DATEPART(year, Pivot) AS varchar) + ''' THEN (CASE AttendCode WHEN ''L'' THEN ''L ('' + CAST(MinsLate AS varchar(5)) + '')'' WHEN ''E'' THEN ''E ('' + CAST(MinsEarly AS varchar(5)) + '')'' WHEN ''B'' THEN ''B ('' + CAST(MinsLate AS varchar(5)) + '','' + CAST(MinsEarly AS varchar(5)) + '')'' ELSE AttendCode END) ELSE ''-'' END) AS ''' + CAST(DATEPART(day, Pivot) AS varchar) + '/' + CAST(DATEPART(month, Pivot) AS varchar) + '/' + CAST(DATEPART(year, Pivot) AS varchar) + ''''
FROM #temp
DROP TABLE #temp

SET @SQLFull = @SQLStart + ISNULL(@SQLDynamic, '') + @SQLEnd

--PRINT @SQLFull

EXEC (@SQLFull)

When ran, this genenrates the following sample SQL statement;

SELECT RegisterID, StudentID, SeqNo, EnrolmentID, StudentName, Status, Programme, 
MAX(CASE AttendanceDate WHEN '11/7/2005' THEN (CASE AttendCode WHEN 'L' THEN 'L (' + CAST(MinsLate AS varchar(5)) + ')' WHEN 'E' THEN 'E (' + CAST(MinsEarly AS varchar(5)) + ')' WHEN 'B' THEN 'B (' + CAST(MinsLate AS varchar(5)) + ',' + CAST(MinsEarly AS varchar(5)) + ')' ELSE AttendCode END) ELSE '-' END) AS '11/7/2005', 
MAX(CASE AttendanceDate WHEN '10/8/2005' THEN (CASE AttendCode WHEN 'L' THEN 'L (' + CAST(MinsLate AS varchar(5)) + ')' WHEN 'E' THEN 'E (' + CAST(MinsEarly AS varchar(5)) + ')' WHEN 'B' THEN 'B (' + CAST(MinsLate AS varchar(5)) + ',' + CAST(MinsEarly AS varchar(5)) + ')' ELSE AttendCode END) ELSE '-' END) AS '10/8/2005' 
FROM dbo.vw_ARS_StudentClassAttendance WHERE RegisterID = 21 GROUP BY RegisterID, StudentID, SeqNo, EnrolmentID, StudentName, Status, Programme, stu_surn, stu_fnm1 ORDER BY stu_surn, stu_fnm1

As you can see, I've put the start of the SQL statement in one variable, SQLStart, the end of the SQL statement in another variable, SQLEnd and the dynamically generated SQL in the SQLDynamic variable.

Each iteration of the SELECT statement populating the SQLDynamic variable generated a string 324 chars long. The maximum number of iterations expected was initially 18, 18*324 = 5832, well below the 8000 limit.

I've now been informed that the maximum is infact 52!! 52*324 = 16848, just over double the limit. Obviously I now need to use 3 "dynamic SQL variables" for execution (and do away with the SQLFull variable) but I'm not sure how to implement this in the code above. How can I tell my

SELECT @SQLDynamic = @SQLDynamic

statement to use variable1 until it's full and then move onto variable2?

Kristen
Test

22859 Posts

Posted - 2005-07-12 : 09:07:20

Well, if you know what the maximum length of each iteration of #TEMP might be you could probably do something like:


SELECT @SQLDynamic3 = CASE WHEN LEN(@SQLDynamic1) > 7000 THEN @SQLDynamic2 ELSE @SQLDynamic3 END,
       @SQLDynamic2 = CASE WHEN LEN(@SQLDynamic1) > 7000 THEN @SQLDynamic1 ELSE @SQLDynamic2 END,
       @SQLDynamic1 = CASE WHEN LEN(@SQLDynamic1) > 7000 THEN '' ELSE @SQLDynamic1 END,
       @SQLDynamic1 = @SQLDynamic1 + ...
...
EXEC (@SQLDynamic3 + ' ' + @SQLDynamic2 + ' ' + @SQLDynamic1)

You would need to pre-set them to an empty string

Kristen

mparter
Yak Posting Veteran

86 Posts

Posted - 2005-07-12 : 11:31:59

Thanks, got that working a treat. So far!

Amarbank
Starting Member

8 Posts

Posted - 2005-10-04 : 20:46:33

I am having the same issue where I need to breakdown the large query into small part (exceeding 8000 limit).

Can you email me the solution that you implemented. I would really appreciate it. (amarbank@hotmail.com).

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-05 : 01:23:16

Declare @s1 varchar(8000)
Declare @s2 varchar(8000)

Assign first 8000 characters to @s1 and rest to @s2
Then Exec(@s1+@s2)

Madhivanan

Failing to plan is Planning to fail

Amarbank
Starting Member

8 Posts

Posted - 2005-10-05 : 01:56:44

what I have is a stored proc and using cursor to get the query build to execute. The code is below. The problem I am having is that @FinalSQL is getting bigger than 8000. How can I break that up into 2 or 3 parts?

Decalre @FinalSQL as varchar(8000)
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
FETCH FROM Columns_cursor
INTO @CustomTableColumnID, @ColumnName

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

SET @FinalSQL = @FinalSQL + ',' + @SubQueryText + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']'

FETCH NEXT FROM Columns_cursor
INTO @CustomTableColumnID, @ColumnName
END
exec(@FinalSQL)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-05 : 02:03:32

Declare @FinalSQL1 as varchar(8000)
Declare @FinalSQL2 as varchar(8000)
set FinalSQL1=''
set FinalSQL2=''

FETCH FROM Columns_cursor
INTO @CustomTableColumnID, @ColumnName

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

If len(@FinalSQL1 + ',' + @SubQueryText + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']')>8000

SET @FinalSQL2 = @FinalSQL2 + ',' + @SubQueryText + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']'

else

SET @FinalSQL1 = @FinalSQL1 + ',' + @SubQueryText + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']'

FETCH NEXT FROM Columns_cursor
INTO @CustomTableColumnID, @ColumnName
END
exec(@FinalSQL1+' '+FinalSQL2)

Why do you use cursor?
what is your exact requirement?

Madhivanan

Failing to plan is Planning to fail

Amarbank
Starting Member

8 Posts

Posted - 2005-10-05 : 02:26:02

It was written by someone else and I am having to fix this mess; though I am not a DBA.
Basically we are dynamically generating table/fields on a web page and the ColumnName and ColumnValues are stored in 2 different tables. So, when we want to bind it to a Grid for a .Net web page, we have to get all the rows & columns from these tables and build the query to get the data.

I added your code but it is still exceeding the limit. I am pasting the entire Stored proc; I would really apprepiate if you could help me solve this headeche:

--All required variables are going to be allocated here.
DECLARE @CustomTableColumnID AS UNIQUEIDENTIFIER
DECLARE @ColumnName AS VARCHAR(255)
DECLARE @FinalSQL AS VARCHAR(8000)
DECLARE @SubQueryText AS VARCHAR(150)
Declare @FinalSQL1 as varchar(8000)
Declare @FinalSQL2 as varchar(8000)
Declare @FinalSQL3 as varchar(8000)
Declare @FinalSQL4 as varchar(8000)
Declare @FinalSQL5 as varchar(8000)
declare @strLength as int

declare @CustomerID AS UNIQUEIDENTIFIER
declare @CustomTableID AS UNIQUEIDENTIFIER

set @CustomerID = '4b13ae9e-7eab-45b0-a110-2052e39fc763'
set @CustomTableID = '9ca2a21f-d047-4247-b1cc-e089e2cc5f39'

--These two variables are set with initial values to concatinate together to build a TSQL command to execute.
SET @SubQueryText='(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='''
SET @FinalSQL='SELECT a.CustomTableRowID AS RowID '

set @FinalSQL1 = ''
set @FinalSQL2 = ''
set @FinalSQL3 = ''

--Initiaize a cursor to hold the records needed to build our dynamic TSQL statement.
DECLARE Columns_cursor CURSOR FAST_FORWARD FOR
SELECT CustomTableColumnID, ColumnName FROM tblCustomTableColumns WHERE CustomerID=@CustomerID AND CustomTableID=@CustomTableID ORDER BY OrderNum
OPEN Columns_cursor

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
FETCH FROM Columns_cursor
INTO @CustomTableColumnID, @ColumnName

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

If len(@FinalSQL1 + ',' + @SubQueryText + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']')>8000

SET @FinalSQL2 = @FinalSQL2 + ',' + @SubQueryText + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']'

else

SET @FinalSQL1 = @FinalSQL1 + ',' + @SubQueryText + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']'

FETCH NEXT FROM Columns_cursor
INTO @CustomTableColumnID, @ColumnName

END
print @finalsql+@finalsql1+@finalsql2

SET @FinalSQL5 = ' FROM tblCustomTableRows a WHERE a.CustomerID=''' + CONVERT(VARCHAR(40), @CustomerID) + ''' AND a.CustomTableID=''' + CONVERT(VARCHAR(40), @CustomTableID) + ''''

CLOSE Columns_cursor
DEALLOCATE Columns_cursor

EXEC(@FinalSQL + @FinalSQL1 + @finalsql2 + @finalsql3 + @finalsql4 + @finalsql5)
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-05 : 02:35:08

>>Basically we are dynamically generating table/fields on a web page and the ColumnName and ColumnValues are stored in 2 different tables.

Not recommended
Can you post some sample data and the result you want after running that query?

Madhivanan

Failing to plan is Planning to fail

Amarbank
Starting Member

8 Posts

Posted - 2005-10-05 : 02:39:28

SELECT a.CustomTableRowID AS RowID ,(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='1FDB0737-08AE-4B4C-B5C4-87CFF42705DF') AS [InternalClientID],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='C118DFA7-FB88-4FE4-96AD-AA42C3A1D5FC') AS [Rule50],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='EF931947-E49E-40C4-AC61-600B0EAEB1A2') AS [Rule105],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='2FFA47DF-3568-4A13-8197-5D0BD1CCA212') AS [Rule106],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='A58F6003-A47B-4D6B-93F1-F3E61482CB08') AS [Rule107],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='6FF799E8-1C1F-4630-AA44-0472D0A5796B') AS [Rule108],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='529558F3-8E54-4DF3-839F-4A1FA2D0FB8A') AS [Rule109],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='927A2710-815A-4E67-A599-9AC83077A4D8') AS [Rule110],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='D9195687-1877-463C-9C3D-26AD3198A858') AS [Rule111],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='8F6EB3F9-F114-429C-9E8C-1C1FECD3F410') AS [Rule112],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='12BD8002-8D7B-47EC-B286-8F637C4ADF17') AS [Rule113],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='ECAD9248-524C-4F8D-9C69-49CA224C7E58') AS [Rule114],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='40A393AF-7058-4449-A2E8-6D5350FC3F93') AS [Rule115],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='949D8F98-6922-4B6F-AA7F-0569D57E5264') AS [Rule116],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='33C86163-5BEB-44EE-9F03-9A794B6E2D05') AS [Rule117],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='F06F4318-1872-41AA-9A4B-A05D9E3FB652') AS [Rule121],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='2AAC8147-9D6F-4134-BF09-9690CBDB5C60') AS [Rule122],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='3D2019E7-A631-4AFA-8026-CB94174BB1CD') AS [Rule124],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='6B4D3067-FC16-41FB-B27B-823A11ACB671') AS [Rule125],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='161D8CC3-1896-4910-82AD-5ABC978600FE') AS [Rule126],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='A27187FD-1F11-4EA0-80ED-BF617EC44FC5') AS [Rule127],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='47C6AFC6-4C19-4804-928A-684D32E6DC54') AS [Rule129],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='80946DAC-A30C-4691-90F4-2C96CC452E9C') AS [Rule130],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='20C68C9F-E944-4F54-A21B-BEF11E3FA95C') AS [Rule131],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='66A9A28C-3E97-4D53-A26B-FB3978220636') AS [Rule133],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='7523E6B7-B9DF-4133-B4AA-7BB21C341285') AS [Rule134],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='D2DC9C3B-0653-4BCE-B3D1-A6B07521B859') AS [Rule135],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='D8B531B6-B9BB-4534-A7ED-A004EBA97DE6') AS [Rule136],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='2E508E62-61DC-45BE-87FD-CD6DB725F878') AS [Rule137],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='0DB3459D-D4A2-4187-96D6-EE31688CAEA7') AS [Rule142],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='7904077E-3DAF-4C55-A686-4968D57008C6') AS [Rule143],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='FF004C2E-2105-4C21-A0B9-A4E097841EDC') AS [Rule160],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='8A80151A-C809-4EEE-BED1-23F7D6D1CBB1') AS [Rule161],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='7BA29C0C-6C4D-430C-B9EF-8734DE5CEE86') AS [Rule162],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='227E5BA8-39F1-4ED4-B523-32BF6B135290') AS [Rule165],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='1376A366-2010-4857-9914-D002DB7158BE') AS [Rule171],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='5413F835-13AD-4B0C-9E07-486EAE140F2D') AS [Rule172],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='10F7280C-4F07-456A-8E23-5854A0B53190') AS [Rule173],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='8528A7C2-88A7-462B-A8FB-6FC9C9CF6FF6') AS [Rule175],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='B8929567-9ED5-46FC-9E66-7A8826792D65') AS [Rule180],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='69EB93D4-C638-4849-8E4B-995D53015977') AS [Rule181],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='1B273B71-3F45-4205-924C-417F7F977EC7') AS [Rule182],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='2E5045AC-18CF-4A96-A3E1-DC996620C968') AS [Rule185],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='0D932181-1273-47B2-835B-7635B52DDC8E') AS [Rule186],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='8AE222FA-73FE-4B6C-8C85-2AC59AEB0905') AS [Rule187],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='0903186D-D985-4B86-B9BA-A3DE79EC7BC5') AS [Rule188],(SELECT ColumnValue FROM tblCustomTableRowColumns WHERE CustomTableRowID=a.CustomTableRowID AND CustomTableColumnID='76618882-7F02-48B0-B794-06AB144B427A') AS [Rule190],(SELECT Colum
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '9CA2A21F-D047-4247-B1CC-E089E2CC5F39'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-05 : 02:43:30

It is strange requirement

It has many select statements
Do you want to run them one by one after you build strings successfully?

Madhivanan

Failing to plan is Planning to fail

Amarbank
Starting Member

8 Posts

Posted - 2005-10-05 : 02:50:54

No, basically want to run it as a single query and the result that I am getting is is a row/column (I guess I cannot attach any image to this) but since my query builder is incomplete (@FinalSQL1+@FinalSQL2..... is over ) and so some of the fields are missing like column Rule182 through Rule205

Amarbank
Starting Member

8 Posts

Posted - 2005-10-05 : 02:53:13

Basically I added few more lines to yours and then I get some data {no errors} but still somehow I am not correctly building the statement up and as such some fields (Selects) seems to be missing and hence the columns from my results

If len(@FinalSQL + ',' + @SubQueryText + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']')>8000
SET @FinalSQL1 = @FinalSQL1 + ',' + @SubQueryText + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']'
else
If len(@FinalSQL1 + ',' + @SubQueryText + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']')>8000
SET @FinalSQL2 = @FinalSQL2 + ',' + @SubQueryText + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']'
else
If len(@FinalSQL + ',' + @SubQueryText + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']')<7000
SET @FinalSQL = @FinalSQL + ',' + @SubQueryText + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']'
else
If len(@FinalSQL2 + ',' + @SubQueryText + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']')>8000
SET @FinalSQL3 = @FinalSQL3 + ',' + @SubQueryText + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-05 : 03:02:18

Post the table structure of tblCustomTableRowColumns with some sample data and expected result as described here
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Madhivanan

Failing to plan is Planning to fail

Amarbank
Starting Member

8 Posts

Posted - 2005-10-05 : 03:10:59

CREATE TABLE [dbo].[tblCustomTableRowColumns] (
[CustomTableRowColumnID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CustomerID] [uniqueidentifier] NOT NULL ,
[CustomTableID] [uniqueidentifier] NOT NULL ,
[CustomTableRowID] [uniqueidentifier] NOT NULL ,
[CustomTableColumnID] [uniqueidentifier] NOT NULL ,
[ColumnValue] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Kristen
Test

22859 Posts

Posted - 2005-10-05 : 04:09:26

Does the solution I posted to the original poster not work for you?
basically it will shuffle round a number of @variables to prevent any of them running over 8000 characters,
whereas I think this:


WHILE @@FETCH_STATUS = 0
BEGIN

If len(@FinalSQL1 + ',' + @SubQueryText 
       + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']')>8000

SET @FinalSQL2 = @FinalSQL2 + ',' + @SubQueryText 
    + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']'

else

SET @FinalSQL1 = @FinalSQL1 + ',' + @SubQueryText 
    + CONVERT(VARCHAR(40),  @CustomTableColumnID) + ''') AS [' + @ColumnName + ']'

FETCH NEXT FROM Columns_cursor
INTO @CustomTableColumnID, @ColumnName

could easily get to the point where a value takes it over the 8000 limit, and cause it to use @FinalSQL2
- and then the next loop could have a shorter value which will still fit in @FinalSQL1

Kristen

Amarbank
Starting Member

8 Posts

Posted - 2005-10-05 : 12:07:32

I am not sure how you mean? How can I add your suggested lines into my code?

Would it be something like
SELECT @FinalSQL3 = CASE WHEN LEN(@FinalSQL1) > 7000 THEN @FinalSQL2 ELSE @FinalSQL3 END,
@FinalSQL2 = CASE WHEN LEN(@FinalSQL1) > 7000 THEN @FinalSQL1 ELSE @FinalSQL2 END,
@FinalSQL1 = CASE WHEN LEN(@FinalSQL1) > 7000 THEN '' ELSE @FinalSQL1 END,
@FinalSQL1 = @FinalSQL1 + ...

Kristen
Test

22859 Posts

Posted - 2005-10-05 : 13:33:54

Yup, the last line would be something like this I think :

@FinalSQL1 = @FinalSQL1 + ',' + @SubQueryText + CONVERT(VARCHAR(40), @CustomTableColumnID) + ''') AS [' + @ColumnName + ']'

Perhaps stick a PRINT or a SELECT in their, instead of the EXEC, to start with so you can see what you are getting.

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-05 : 14:50:52

Holy crap, all this work for a simple cross-tab. Ridiculous. Use the correct tools for the job. Return normalized, summarized data in the standard row/column format from sql server and format your results at the client. Excel, Access, ASP, Crystal, VB, etc -- all of them can easily pivot the results into as many columns as needed and it is much easier, shorter, and more efficient.

Here's a whole bunch of examples: http://weblogs.sqlteam.com/jeffs/archive/2005/05/15/5175.aspx

(including a shorter, easier way to pivot using T-SQL if you really want to take that approach, though I do not recommend it).

gchipunov
Starting Member

1 Post

Posted - 2010-01-05 : 17:17:01

I use temp tables to overcome the varchar(MAX) limit, I use SQL 2005, 2008, so not sure about 2000, if it will work, maybe not xml datatype

Code:

declare @returnXML xml;

declare @tab2 table (i int IDENTITY(1,1) NOT NULL ,ItemXML xml NULL);

set @returnXML = (select UG.*,VT.AssassinA,VT.AssassinD,VT.Attack,VT.Defense,VT.DriveByA,VT.DriveByD,VT.GTAA,VT.GTAD,VT.BuyItemsID,VT.Icon,VT.RobA,VT.RobD,VT.WeaponName,VT.Image from UserGuns UG inner join WeaponsTypes VT on UG.GunID = VT.WeaponID where UG.UserID = 1 FOR XML PATH ('weapon'), ELEMENTS);

insert into @tab2 (ItemXML) VALUES(@returnXML);

set @returnXML = (select UV.*,VT.AssassinA,VT.AssassinD,VT.Attack,VT.Defense,VT.DriveByA,VT.DriveByD,VT.GTAA,VT.GTAD,VT.BuyItemsID,VT.Icon,VT.RobA,VT.RobD,VT.CarName,VT.Image from UserVehicles UV inner join VehicleTypes VT on UV.CarID = VT.CarID where UV.UserID = 1 FOR XML PATH ('vehicle'), ELEMENTS);

insert into @tab2 (ItemXML) VALUES(@returnXML);

select ItemXML from @tab2 FOR XML PATH (''), ELEMENTS;

[url]http://www.bigideastech.com[/url]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-05 : 17:29:24

Huh?

No, you're never too old to Yak'n'Roll if you're too young to die.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources