Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172

    Unanswered: Varchar(8000) is not enough... what can I do?

    Hi all!

    I'm using this store proc to get a pivot table in SQL:

    Code:
    USE [m0851System]
    GO
    /****** Object:  StoredProcedure [dbo].[sp_CrossTabIntoTable]    Script Date: 01/25/2008 09:59:37 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[sp_CrossTabIntoTable] 
    @select varchar(8000),
    @sumfunc varchar(100), 
    @pivot varchar(100), 
    @table varchar(100)
    -- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007
    ,@tbl_result varchar(100),
    @fld_sufx varchar(10)
    -- FIN JULIEN BONNIER
    AS
    
    --DECLARE @sql varchar(8000), @delim varchar(1)
    DECLARE @sql varchar(8000), @delim varchar(1)
    SET NOCOUNT ON
    SET ANSI_WARNINGS OFF
    
    -- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007
    -- MODIFIÉ PAR JULIEN BONNIER 25 janvier 2008 (ajout de la clause case)
    IF LEFT(@tbl_result,1)='#'
    	BEGIN
    		IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type='U' AND name='' + @tbl_result + '')
    		EXEC ('DROP TABLE ' + @tbl_result + '')
    	END	
    ELSE
    	BEGIN
    		IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name='' + @tbl_result + '')
    		EXEC ('DROP TABLE ' + @tbl_result + '')
    	END
    -- FIN JULIEN BONNIER
    
    EXEC ('SELECT ' + @pivot + ' AS pvt INTO ##pivot FROM ' + @table + ' WHERE 1=2')
    EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' 
    + @pivot + ' Is Not Null')
    
    SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
    
    SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) 
    WHEN 0 THEN '' ELSE '''' END 
    FROM tempdb.information_schema.columns 
    WHERE table_name='##pivot' AND column_name='pvt'
    
    -- MODIFIÉ PAR JULIEN BONNIER 18 juillet 2007
    --SELECT @sql=@sql + '''' + convert(varchar(100), pvt) + ''' = ' + 
    SELECT @sql=@sql + '''' + convert(varchar(100), pvt) + '' + @fld_sufx + ''' = ' + 
    stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' 
    + @delim + convert(varchar(100), pvt) + @delim + ' THEN ' ) + ', ' FROM ##pivot
    -- FIN JULIEN BONNIER
    -- AJOUTÉ PAR JULIEN BONNIER 15 octobre 2007
    ORDER BY pvt
    -- FIN JULIEN BONNIER
    
    DROP TABLE ##pivot
    
    SELECT @sql=left(@sql, len(@sql)-1)
    PRINT(LEN(@sql))
    SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
    -- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007
    SELECT @select=stuff(@select, charindex(' FROM ', @select), 6, ' INTO ' + @tbl_result + ' FROM ')
    -- FIN JULIEN BONNIER
    
    --EXEC (@select)
    PRINT('YOYO'+@select)
    RETURN
    SET ANSI_WARNINGS ON
    But now my @sql and @select that are varchar(8000) get bigger than 8000 for one of my reports... So my query fails ever times.

    What can I do to fix it ?

    Thanks in advance.

    Or Tho
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  2. #2
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    BTW: I tried to replace my varchar(8000) with text but then I got this error message:

    Quote Originally Posted by MSSQL
    Msg 8116, Level 16, State 1, Procedure sp_CrossTabIntoTable, Line 56
    Argument data type text is invalid for argument 1 of stuff function.
    Msg 8116, Level 16, State 1, Procedure sp_CrossTabIntoTable, Line 58
    Argument data type text is invalid for argument 1 of stuff function.
    Msg 402, Level 16, State 1, Procedure sp_CrossTabIntoTable, Line 62
    The data types varchar and text are incompatible in the add operator.
    NB: I'm using MS SQL 2k5 SP2
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think you can exec:
    Code:
    EXEC (@select1 + @select2 + @select3)
    CAn't remember 100% sure though

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    upgrade to sql server 2k5 and use varchar(max)?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Quote Originally Posted by Thrasymachus
    upgrade to sql server 2k5 and use varchar(max)?
    I'm using SQL server 2k5 and 8000 is the limit of varchar... loll
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    no its not. look up varchar(max) silly.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasymachus
    no its not. look up varchar(max) silly.
    Can you EXEC VARCHAR(MAX)?




    Should I just look it up\ try....?

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    i dunno. It is still the year 2000 at my shop. It would be worth investigating. All of my 2K5 knowledge comes from a few books and a couple of months spent doing a migration, but it would seem to that they created this data type to do things like this.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    EXEC varchar(max)

    results:
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'max'.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    sweet son of the holy pasta monster. Is that what you tried to execute. I am sorry I do not spoon feed code to the OPs.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    try declaring a variable of that datatype, populating said variable and then executing said variable.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  12. #12
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    But if I change my store proc and try to replace varchar(8000) with varchar(8001) this what I get:

    Msg 131, Level 15, State 3, Procedure sp_CrossTabIntoTable, Line 2
    The size (8001) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  13. #13
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    what's the comaptibility level of the DB? 80 or 90?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Doesn't matter - that ain't valid in 2k or 2k5. Ortho - open up books online and just spend 2 minutes reading up on VARCHAR(MAX).

  15. #15
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Lol

    Quote Originally Posted by MSSQL BOOKS ONLINE
    # Use char when the sizes of the column data entries are consistent.
    # Use varchar when the sizes of the column data entries vary considerably.
    # Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.
    BUT even varchar(max) doen't seem to be enough...

    Here's some debug infos
    Code:
    PRINT(@sql) returns:
    'A0205000R' = SUM( CASE StyleColor WHEN 'A0205000' THEN Qty END), 'A0205015R' = SUM( CASE StyleColor WHEN 'A0205015' THEN Qty END), 'A0205200R' = SUM( CASE StyleColor WHEN 'A0205200' THEN Qty END), 'A0205625R' = SUM( CASE StyleColor WHEN 'A0205625' THEN Qty END), 'A0305000R' = SUM( CASE StyleColor WHEN 'A0305000' THEN Qty END), 'A0305200R' = SUM( CASE StyleColor WHEN 'A0305200' THEN Qty END), 'A0305216R' = SUM( CASE StyleColor WHEN 'A0305216' THEN Qty END), 'A0305500R' = SUM( CASE StyleColor WHEN 'A0305500' THEN Qty END), 'A0305627R' = SUM( CASE StyleColor WHEN 'A0305627' THEN Qty END), 'A0305700R' = SUM( CASE StyleColor WHEN 'A0305700' THEN Qty END), 'A0405015R' = SUM( CASE StyleColor WHEN 'A0405015' THEN Qty END), 'A0405200R' = SUM( CASE StyleColor WHEN 'A0405200' THEN Qty END), 'A0405202R' = SUM( CASE StyleColor WHEN 'A0405202' THEN Qty END), 'A0405250R' = SUM( CASE StyleColor WHEN 'A0405250' THEN Qty END), 'A0405500R' = SUM( CASE StyleColor WHEN 'A0405500' THEN Qty END), 'A0500000R' = SUM( CASE StyleColor WHEN 'A0500000' THEN Qty END), 'A0505000R' = SUM( CASE StyleColor WHEN 'A0505000' THEN Qty END), 'A0505216R' = SUM( CASE StyleColor WHEN 'A0505216' THEN Qty END), 'A0505721R' = SUM( CASE StyleColor WHEN 'A0505721' THEN Qty END), 'A0507012R' = SUM( CASE StyleColor WHEN 'A0507012' THEN Qty END), 'A0507700R' = SUM( CASE StyleColor WHEN 'A0507700' THEN Qty END), 'A0605000R' = SUM( CASE StyleColor WHEN 'A0605000' THEN Qty END), 'A0605216R' = SUM( CASE StyleColor WHEN 'A0605216' THEN Qty END), 'A0605250R' = SUM( CASE StyleColor WHEN 'A0605250' THEN Qty END), 'A0605605R' = SUM( CASE StyleColor WHEN 'A0605605' THEN Qty END), 'A0705000R' = SUM( CASE StyleColor WHEN 'A0705000' THEN Qty END), 'A0705200R' = SUM( CASE StyleColor WHEN 'A0705200' THEN Qty END), 'A0705250R' = SUM( CASE StyleColor WHEN 'A0705250' THEN Qty END), 'A0705325R' = SUM( CASE StyleColor WHEN 'A0705325' THEN Qty END), 'A0705500R' = SUM( CASE StyleColor WHEN 'A0705500' THEN Qty END), 'A0707012R' = SUM( CASE StyleColor WHEN 'A0707012' THEN Qty END), 'A0805000R' = SUM( CASE StyleColor WHEN 'A0805000' THEN Qty END), 'A0805200R' = SUM( CASE StyleColor WHEN 'A0805200' THEN Qty END), 'A0805325R' = SUM( CASE StyleColor WHEN 'A0805325' THEN Qty END), 'A1005000R' = SUM( CASE StyleColor WHEN 'A1005000' THEN Qty END), 'A1005401R' = SUM( CASE StyleColor WHEN 'A1005401' THEN Qty END), 'A1605700R' = SUM( CASE StyleColor WHEN 'A1605700' THEN Qty END), 'A1805000R' = SUM( CASE StyleColor WHEN 'A1805000' THEN Qty END), 'A1805017R' = SUM( CASE StyleColor WHEN 'A1805017' THEN Qty END), 'A1805200R' = SUM( CASE StyleColor WHEN 'A1805200' THEN Qty END), 'A1805211R' = SUM( CASE StyleColor WHEN 'A1805211' THEN Qty END), 'A1805216R' = SUM( CASE StyleColor WHEN 'A1805216' THEN Qty END), 'A1805250R' = SUM( CASE StyleColor WHEN 'A1805250' THEN Qty END), 'A1805604R' = SUM( CASE StyleColor WHEN 'A1805604' THEN Qty END), 'A1805700R' = SUM( CASE StyleColor WHEN 'A1805700' THEN Qty END), 'A1805710R' = SUM( CASE StyleColor WHEN 'A1805710' THEN Qty END), 'A1807012R' = SUM( CASE StyleColor WHEN 'A1807012' THEN Qty END), 'A1905722R' = SUM( CASE StyleColor WHEN 'A1905722' THEN Qty END), 'A2005000R' = SUM( CASE StyleColor WHEN 'A2005000' THEN Qty END), 'A2005700R' = SUM( CASE StyleColor WHEN 'A2005700' THEN Qty END), 'A2007012R' = SUM( CASE StyleColor WHEN 'A2007012' THEN Qty END), 'A2105200R' = SUM( CASE StyleColor WHEN 'A2105200' THEN Qty END), 'A2105206R' = SUM( CASE StyleColor WHEN 'A2105206' THEN Qty END), 'A2105550R' = SUM( CASE StyleColor WHEN 'A2105550' THEN Qty END), 'A2105732R' = SUM( CASE StyleColor WHEN 'A2105732' THEN Qty END), 'A2205000R' = SUM( CASE StyleColor WHEN 'A2205000' THEN Qty END), 'A2305000R' = SUM( CASE StyleColor WHEN 'A2305000' THEN Qty END), 'A2305200R' = SUM( CASE StyleColor WHEN 'A2305200' THEN Qty END), 'A2305216R' = SUM( CASE StyleColor WHEN 'A2305216' THEN Qty END), 'A2605000R' = SUM( CASE StyleColor WHEN 'A2605000' THEN Qty END), 'A2605015R' = SUM( CASE StyleColor WHEN 'A2605015' THEN Qty END), 'A2605200R' = SUM( CASE StyleColor WHEN 'A2605200' THEN Qty END), 'A2605206R' = SUM( CASE StyleColor WHEN 'A2605206' THEN Qty END), 'A2605211R' = SUM( CASE StyleColor WHEN 'A2605211' THEN Qty END), 'A2605216R' = SUM( CASE StyleColor WHEN 'A2605216' THEN Qty END), 'A2605250R' = SUM( CASE StyleColor WHEN 'A2605250' THEN Qty END), 'A2605604R' = SUM( CASE StyleColor WHEN 'A2605604' THEN Qty END), 'A2605605R' = SUM( CASE StyleColor WHEN 'A2605605' THEN Qty END), 'A2605700R' = SUM( CASE StyleColor WHEN 'A2605700' THEN Qty END), 'A2607012R' = SUM( CASE StyleColor WHEN 'A2607012' THEN Qty END), 'A2607250R' = SUM( CASE StyleColor WHEN 'A2607250' THEN Qty END), 'A3005015R' = SUM( CASE StyleColor WHEN 'A3005015' THEN Qty END), 'A3005200R' = SUM( CASE StyleColor WHEN 'A3005200' THEN Qty END), 'A3005202R' = SUM( CASE StyleColor WHEN 'A3005202' THEN Qty END), 'A3005216R' = SUM( CASE StyleColor WHEN 'A3005216' THEN Qty END), 'A3005250R' = SUM( CASE StyleColor WHEN 'A3005250' THEN Qty END), 'A3007012R' = SUM( CASE StyleColor WHEN 'A3007012' THEN Qty END), 'A3105000R' = SUM( CASE StyleColor WHEN 'A3105000' THEN Qty END), 'A3105200R' = SUM( CASE StyleColor WHEN 'A3105200' THEN Qty END), 'A3105402R' = SUM( CASE StyleColor WHEN 'A3105402' THEN Qty END), 'A3105627R' = SUM( CASE StyleColor WHEN 'A3105627' THEN Qty END), 'A3105700R' = SUM( CASE StyleColor WHEN 'A3105700' THEN Qty END), 'A3207000R' = SUM( CASE StyleColor WHEN 'A3207000' THEN Qty END), 'A3305000R' = SUM( CASE StyleColor WHEN 'A3305000' THEN Qty END), 'A3505000R' = SUM( CASE StyleColor WHEN 'A3505000' THEN Qty END), 'A3505200R' = SUM( CASE StyleColor WHEN 'A3505200' THEN Qty END), 'A3505206R' = SUM( CASE StyleColor WHEN 'A3505206' THEN Qty END), 'A3505211R' = SUM( CASE StyleColor WHEN 'A3505211' THEN Qty END), 'A3505216R' = SUM( CASE StyleColor WHEN 'A3505216' THEN Qty END), 'A3505250R' = SUM( CASE StyleColor WHEN 'A3505250' THEN Qty END), 'A3505325R' = SUM( CASE StyleColor WHEN 'A3505325' THEN Qty END), 'A3505550R' = SUM( CASE StyleColor WHEN 'A3505550' THEN Qty END), 'A3505626R' = SUM( CASE StyleColor WHEN 'A3505626' THEN Qty END), 'A3505627R' = SUM( CASE StyleColor WHEN 'A3505627' THEN Qty END), 'A3505700R' = SUM( CASE StyleColor WHEN 'A3505700' THEN Qty END), 'A3505710R' = SUM( CASE StyleColor WHEN 'A3505710' THEN Qty END), 'A3507012R' = SUM( CASE StyleColor WHEN 'A3507012' THEN Qty END), 'A3605000R' = SUM( CASE StyleColor WHEN 'A3605000' THEN Qty END), 'A3605200R' = SUM( CASE StyleColor WHEN 'A3605200' THEN Qty END), 'A3607000R' = SUM( CASE StyleColor WHEN 'A3607000' THEN Qty END), 'A3607012R' = SUM( CASE StyleColor WHEN 'A3607012' THEN Qty END), 'A3607200R' = SUM( CASE StyleColor WHEN 'A3607200' THEN Qty END), 'A3607700R' = SUM( CASE StyleColor WHEN 'A3607700' THEN Qty END), 'A3707325R' = SUM( CASE StyleColor WHEN 'A3707325' THEN Qty END), 'A3805200R' = SUM( CASE StyleColor WHEN 'A3805200' THEN Qty END), 'A4807000R' = SUM( CASE StyleColor WHEN 'A4807000' THEN Qty END), 'A4807250R' = SUM( CASE StyleColor WHEN 'A4807250' THEN Qty END), 'A5207000R' = SUM( CASE StyleColor WHEN 'A5207000' THEN Qty END), 'A5207250R' = SUM( CASE StyleColor WHEN 'A5207250' THEN Qty END), 'A5307500R' = SUM( CASE StyleColor WHEN 'A5307500' THEN Qty END), 'A5407250R' = SUM( CASE StyleColor WHEN 'A5407250' THEN Qty END), 'A5707200R' = SUM( CASE StyleColor WHEN 'A5707200' THEN Qty END), 'A5905000R' = SUM( CASE StyleColor WHEN 'A5905000' THEN Qty END), 'A5907000R' = SUM( CASE StyleColor WHEN 'A5907000' THEN Qty END), 'A5907200R' = SUM( CASE StyleColor WHEN 'A5907200' THEN Qty END), 'A5907211R' = SUM( CASE StyleColor WHEN 'A5907211' THEN Qty END), 'A5907604R' = SUM( CASE StyleColor WHEN 'A5907604' THEN Qty END), 'A5913200R' = SUM( CASE StyleColor WHEN 'A5913200' THEN Qty END), 'A6105200R' = SUM( CASE StyleColor WHEN 'A6105200' THEN Qty END), 'A6105325R' = SUM( CASE StyleColor WHEN 'A6105325' THEN Qty END), 'A6107200R' = SUM( CASE StyleColor WHEN 'A6107200' THEN Qty END), 'A6107325R' =
    As you can see there's something missing at the end of the above code.

    Code:
    PRINT(LEN(@sql)) returns:
    11020
    Less is more.
    How long is now?
    http://www.lesouterrain.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •