Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Unanswered: SQL 2000 -> SQL 2005 Nesting Error

    I have sort of a weird error when attempting to create a database in SQL 2005 (migrating from SQL 2000 to SQL 2005).

    I am able to run the CREATE PROC script just fine in SQL 2000, but I get this error in SQL 2005.

    Quote Originally Posted by SQL 2005

    Msg 191, Level 15, State 1, Procedure udf_JCMS_ConcurrencyCheck, Line 2518
    Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries
    I can't easily past up the whole script(it's 2655 lines), but it basically takes the following form:
    Code:
    CREATE FUNCTION dbo.Ugly (
      @PK1 int, 
      @PK2 int, 
      @PK3 int, 
      @TableID int, 
      @UpdateBy char(30),
      @LastUpdate datetime ) RETURNS BIT
    
    DECLARE @UpdatedStatus as bit
    DECLARE @RecordCount as smallint
    
    SET @RecordCount = 0
    If @TabelID = 1	-- Comment about TableName
    BEGIN
    
    	SELECT
    		@RecordCount =	count(UNIQUE_ID)
    	FROM
    		dbo.MyTable
    	WHERE
    		UNIQUE_ID = @PK1
    	AND	PROXY_ID = @PK2	
    	AND	LTRIM(RTRIM(ISNULL(LAST_UPDATE_BY,'')))	= LTRIM(RTRIM(ISNULL(@UpdatedBy,'')))
    	AND	
    		(
    			isnull(LAST_UPDATE_DATE,'') = isnull(@UpdatedDt,'')
    		or
    			convert(datetime,convert(varchar(10),LAST_UPDATE_DATE,101) 
    			+ ' ' + convert(char(8), LAST_UPDATE_DATE,8)) = @UpdatedDt
    		)
    END
    
    If @TableID = 2 -- Comment about TableName
    BEGIN
    .
    .
    .
    END
    
    If @TableID = 3 -- Comment about TableName
    BEGIN
    .
    .
    .
    END
    
    ...
    
    If @TableID = 156 -- Comment about TableName
    BEGIN
    .
    .
    .
    END
    
    
    IF @RecordCount = 0
    	SET @UpdatedStatus = 1
    ELSE
    	SET @UpdatedStatus = 0
    
    RETURN @UpdatedStatus

    First, THIS IS NOT MY CODE.

    Second, it appears that they are trying to create a function to test if a record has been updated (I cut out a bit of that).

    I don't really get why SQL 2005 throws an error here. Yes, the code is hideous and not something that I would write myself. But it's not like the function is calling itself recursively or anything.

    Can anyone shed any light?

    Regards,

    hmscott
    Have you hugged your backup today?

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I would try commenting out blocks of code until you isolate the chunk that's causing sql server to barf.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by jezemine
    I would try commenting out blocks of code until you isolate the chunk that's causing sql server to barf.
    Thanks for that mental image...

    I tried your suggestion; it boiled down to the following block (although it's the same block repeated 123 times).

    Code:
    convert(datetime,convert(varchar(10),LAST_UPDATE_DATE,101) + ' ' + convert(char(8), LAST_UPDATE_DATE,8)) = @UpdatedDt
    From somewhere else, I learned that there is an issue with converting smalldatetime values. Now I gotta go find that link again.

    In any case, I think I was able to pursuade the developers that
    Code:
    convert(varchar(10),LAST_UPDATE_DATE,101) + ' ' + convert(char(8), LAST_UPDATE_DATE,8)
    is the same as convert
    Code:
    (varchar(19), LAST_UPDATE_DATE, 120)
    I am hoping to convince them that:
    Code:
    LAST_UPDATE_DATE = @UpdatedDt
    But I seem to recall that datetime values in VB don't go all the way down to the millisecond like in SQL.

    In any case, for now I was able to use the shortened convert block to get the function to compile.

    Regards,

    hmscott
    Have you hugged your backup today?

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I like the error msg: "rewrite the query". it's as if sql server knew the proc was a mess.

    sort of reminds me of one time I generated a query so big that it blew the parser's stack. that was a neat error msg: "The query processor ran out of stack space during query optimization. Please simplify the query."

Posting Permissions

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