Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662

    Unanswered: Internal SQL Server error.

    Can someone pleplease run the script below in SQL2K environment with SP3+ applied and see if you get the same error:
    Code:
    create view dbo.vw_INTERNAL_ERROR as
    select top 100 percent * from(
    select [DateField]=getdate()) x1
    	 order by x1.[DateField]
    go
    if exists(select 1 from dbo.vw_INTERNAL_ERROR)
    print 'Exists'
    else
    print 'Doesn''t Exist'
    go
    drop view dbo.vw_INTERNAL_ERROR
    go
    
    I am getting this:

    Msg 8624, Level 16, State 23, Line 1
    Internal SQL Server error.


    But in SQL2K5 it prints 'Exists' correctly.

    Thanks in advance.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Version:
    Code:
    Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)   
    May  3 2005 23:18:38   
    Copyright (c) 1988-2003 Microsoft Corporation  
    Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
    I get:
    Code:
    Server: Msg 8624, Level 16, State 23, Line 1
    Internal SQL Server error.
    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    As a side note, I did not think that Order By was a valid clause inside of a view [edit: my bad, order by is permitted when TOP is specified]. When I remove the 'Order By' from the view definition, then I get 'Exists' as the response:

    Code:
    alter view dbo.vw_INTERNAL_ERROR as
    select top 100 percent * from(
    select [DateField]=getdate()) x1
    --	 order by x1.[DateField]
    go
    if exists(select 1 from dbo.vw_INTERNAL_ERROR)
    print 'Exists'
    else
    print 'Doesn''t Exist'
    go
    drop view dbo.vw_INTERNAL_ERROR
    go

    Regards,

    hmscott
    Last edited by hmscott; 10-22-05 at 00:27.
    Have you hugged your backup today?

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That was my experience as well. I also moved ORDER BY from outer query into sub-query, and the script didn't fail. The test for existence appears to be the only situation where this error manifests itself. Of course this script is hypothetical, the actual code has nothing to do with GETDATE(), but structurally is identical to the code I posted. The IF...ELSE construct is used to form a BCP...OUT statement that would use this view if records can be found, and a different view against the same table if no records can be returned.

    As a workaround, without having to change the syntax of the view in question, I assigned the record count to a variable, and then tested its value.

    Do you think it qualifies to be declared as a bug, or I am the only one that would use something like this?

    Thanks for the reply.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...Just found another workaround...If you add UNION ALL with a WHERE clause that is guaranteed not to return any rows, in order not to distort the logic, - no error either:
    Code:
    create view dbo.vw_INTERNAL_ERROR as
    select top 100 percent * from (
    	 select [DateField]=getdate()
    	 union all 
    	 select getdate() where 1=2) x1
    	order by x1.[DateField]
    go
    if exists(select 1 from dbo.vw_INTERNAL_ERROR)
    print 'Exists'
    else
    print 'Doesn''t Exist'
    go
    drop view dbo.vw_INTERNAL_ERROR
    go
    Yet another workaround is to include TOP to the subquery, while retaining ORDER BY for the outer query, like this:
    Code:
    create view dbo.vw_INTERNAL_ERROR as
    select top 100 percent * from (
    	 select top 100 percent [DateField]=getdate()) x1
    		 order by x1.[DateField]
    go
    if exists(select 1 from dbo.vw_INTERNAL_ERROR)
    print 'Exists'
    else
    print 'Doesn''t Exist'
    go
    drop view dbo.vw_INTERNAL_ERROR
    go
    Go figure...
    Last edited by rdjabarov; 10-22-05 at 10:36.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Feb 2004
    Posts
    492
    I'm not sure how to take this:
    Code:
    create view dbo.vw_INTERNAL_ERROR as
    select top 99 percent * from(
    select [DateField]=getdate()) x1
    	 order by x1.[DateField]
    go
    works for me.

    As a workaround, without having to change the syntax of the view in question, I assigned the record count to a variable, and then tested its value.
    Same here, selecting from the view itself is no problem. So it might be an exists problem?

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by rdjabarov
    Do you think it qualifies to be declared as a bug, or I am the only one that would use something like this?

    Thanks for the reply.
    Hee, hee. I rarely have time to try to report a "bug". If I can fix the problem (with code, with a tweak or with a hammer), I tend to document the issue and drive on.

    If I can't fix it, I come here.

    If Brett can't fix it...well, isn't that what margarita's are for? :-)

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

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