quote:

---

Originally posted by chadbryant5

We have a query that keeps blowing up and giving an internal sql server error. Our application calling the SP that runs the query gets the error and if we call it from Query Analyzer it gets the error. This is a SQL Server 2000 server with all the latest service packs applied (according to our DBA).

The funny thing is the query is fine (99.9% of the time). We have a data warehouse that we query to get data from based on user inputs to the data they want. It takes this data and builds a snapshot table in another database dynamically so that they can then do other queries using a custom tool we wrote in their own world (they get the data from the warehouse then they don't hit the warehouse database again).

When they get data for a certain set of parameters they get the error otherwise the query runs fine and they do not get the sql server internal error.

I cannot figure out what could be going on. And to make it more interesting, when they originally build their snapshot table of data from the warehouse database, it works fine. It is only when they run a "refresh" that it blows up but the refresh just drops their snapshot table and rebuilds it by running the exact same queries that it did initially to build the table in the first place. But the refreshing bombs where as the initial run of the query against the same data does not. I can reproduce this everytime, but the error message is not helpful at all. We're not using aggregates or querying views which is what I've seen in Microsoft KB articles about this error message.

We build a dynamic query which is below based on parameters passed into the stored proc (name of the warehouse prefix to go to snapshot table name, snapshot database prefix...ie snapshotdb.dbo. etc) I hope this query will kind of make sense... at least enough for you to see what I'm doing with subqueries etc.

Thanks for any input!!!! I'm completely stumped!

Here's a sample of the query from SQL Profiler with all the variables built instead of the source query in the EXEC statement from the stored proc. This should be easier to follow:
Update SnapshotLive.dbo.s0000001514_ColumnValueNameDetail
Set ColumnValueNameID =
Select ColumnValueNameID From SnapshotLive.dbo.s0000001514_ColumnValueName CVN1
Where CVN1.ModuleColumnID = 50 And
CVN1.ValueID =
( Select WarehouseLive.dbo.UbDrg.UbServiceLineID From WarehouseLive.dbo.UbDrg
Where WarehouseLive.dbo.UbDrg.UbDrgID = SnapshotLive.dbo.s0000001514_ColumnValueNameDetail.ValueID)
Where ColumnValueNameDetailID IN
(Select CVND2.ColumnValueNameDetailID
From SnapshotLive.dbo.s0000001514_ColumnValueNameDetail CVND2 Inner join SnapshotLive.dbo.s0000001514_ColumnValueName CVN2
On CVND2.ColumnValueNameID = CVN2.ColumnValueNameID
Where CVN2.ModuleColumnID = 50 And CVN2.ValueID Not In
(Select UbServiceLineID From WarehouseLive.dbo.UbDrg Where UbDrgID = SnapshotLive.dbo.s0000001514_ColumnValueNameDetail.ValueID))

---