Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Internal SQL Server error on view whose query runs OK

Posted on 2007-07-22
4
Medium Priority
?
206 Views
Last Modified: 2008-02-01
I have created the view shown below. Basically it gets totals of taxed and taxed deferred purchases. When I run the query itself (i.e. not creating the view), it works fine and I get about 1100 rows. The create view works fine (it creates the view). But when I try select * from _vwContribByRate I get:

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

What's my problem?

create view _vwContribByRate as
select 'T' as taxable, c.socialSecurityNo, c.contribYear, rr.effDate, sum(c.payAmount) as purchAmount, sum(c.serviceCredit) as purchCredit
from tblPaActiveContrib c
join tblPaOHPRSrates rr on  rr.rateType = 'M'
    and rr.effDate = (select max(effDate) from tblPaOHPRSrates where rateType = 'M' and effDate < c.payDate)
left join tblPaServiceCreditPurchase p on p.purchaseId = c.purchaseId
    and (p.taxDeferred is null or p.taxDeferred <> 'Y')
where c.contribType in (2,4,6,7)
  group by c.socialSecurityNo, c.contribYear, rr.effDate
union all
select 'D' as taxable, c.socialSecurityNo, c.contribYear, rr.effDate, sum(c.payAmount) as purchAmount, sum(c.serviceCredit) as purchCredit
from tblPaActiveContrib c
join tblPaOHPRSrates rr on  rr.rateType = 'M'
    and rr.effDate = (select max(effDate) from tblPaOHPRSrates where rateType = 'M' and effDate < c.payDate)
join tblPaServiceCreditPurchase p on p.purchaseId = c.purchaseId
    and (p.taxDeferred is not null and p.taxDeferred = 'Y')
where c.contribType in (2,4,6,7)
  group by c.socialSecurityNo, c.contribYear, rr.effDate
0
Comment
Question by:jmarkfoley
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19542967
what version/sp of sql server?
what data types are the queried fields?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19542994
can u try from a new Query analyser
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 1500 total points
ID: 19544193
Does it come back with the error immediately or does it take some time?

You'll basically need to cut chunks of your view out and deduce which part is causing the error. For example try it without certain fields, without the union, without the correlated subquery... eventually it'll work and that will point you at the part that is causing the error.


0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 19583739
angelIII: I'm using SQL Server 2000. The payAmount is smallmoney, serviceCredit is decimal 5(9,6), socialSecurityNo is varchar, contribYear is integer and the dates are datetime.

aneeshattingal: I don't have access to any other query analyzer.

nmcdermaid: basically, I went with you suggestion which is essentially: rewrite the query! I did that in a completely different way, so I didn't really do the debug steps you suggested.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question