SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

mparter
Yak Posting Veteran

United Kingdom
86 Posts

Posted - 07/12/2005 :  08:38:48  Show Profile  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 07/12/2005 :  09:07:20  Show Profile  Reply with Quote
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
Go to Top of Page

mparter
Yak Posting Veteran

United Kingdom
86 Posts

Posted - 07/12/2005 :  11:31:59  Show Profile  Reply with Quote
Thanks, got that working a treat. So far!
Go to Top of Page

Amarbank
Starting Member

8 Posts

Posted - 10/04/2005 :  20:46:33  Show Profile  Reply with Quote
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


Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 10/05/2005 :  01:23:16  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Edited by - madhivanan on 10/05/2005 01:23:43
Go to Top of Page

Amarbank
Starting Member

8 Posts

Posted - 10/05/2005 :  01:56:44  Show Profile  Reply with Quote
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)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 10/05/2005 :  02:03:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Amarbank
Starting Member

8 Posts

Posted - 10/05/2005 :  02:26:02  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 10/05/2005 :  02:35:08  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>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
Go to Top of Page

Amarbank
Starting Member

8 Posts

Posted - 10/05/2005 :  02:39:28  Show Profile  Reply with Quote
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'.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 10/05/2005 :  02:43:30  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Amarbank
Starting Member

8 Posts

Posted - 10/05/2005 :  02:50:54  Show Profile  Reply with Quote
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

Go to Top of Page

Amarbank
Starting Member

8 Posts

Posted - 10/05/2005 :  02:53:13  Show Profile  Reply with Quote
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 + ']'
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 10/05/2005 :  03:02:18  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Amarbank
Starting Member

8 Posts

Posted - 10/05/2005 :  03:10:59  Show Profile  Reply with Quote

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




Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 10/05/2005 :  04:09:26  Show Profile  Reply with Quote
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
Go to Top of Page

Amarbank
Starting Member

8 Posts

Posted - 10/05/2005 :  12:07:32  Show Profile  Reply with Quote
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 + ...
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 10/05/2005 :  13:33:54  Show Profile  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 10/05/2005 :  14:50:52  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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).

Edited by - jsmith8858 on 10/05/2005 14:51:56
Go to Top of Page

gchipunov
Starting Member

1 Posts

Posted - 01/05/2010 :  17:17:01  Show Profile  Reply with Quote
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;


http://www.bigideastech.com
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 01/05/2010 :  17:29:24  Show Profile  Visit webfred's Homepage  Reply with Quote
Huh?


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  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.12 seconds. Powered By: Snitz Forums 2000