Fatal Error 7987

Pavesa · July 12th, 2006, 05:25 PM

Hi,

I'm controlling a remote SQL Server using an Access Data Project and I'm getting a Fatal Error 7987 when I do a select query between two tables. The first table, Tab1 contains listings of companies with a company ID (CompID) and country code (based on telephone codes) whilst Tab2 contains historical data for these companies.

If I do a select query between them on US, Canada, Japan, UK or French companies - Country code 1, 201, 81, 44 or 33 in Tab1 with today's date - I get no error. But if I select Germany (49) in Tab1 I get the error. Once the error has happened, if I try and run the query again, I get an error box saying "Connection Failure". I have a temporary table with the same data as Tab1 and this gives the same problem which points to the historical data table Tab2 as the source of the problem.

I found this link on the Microsoft Knowledgebase but as a relative novice in SQL Server, I'm not sure what to make of it.

http://support.microsoft.com/kb/828337/en-us

Can anyone give me any guidance on resolving the problem please? It would be much appreciated.

Thanks

Andrew

Peso · July 13th, 2006, 06:04 AM

Maybe if you posted the query here, together with some sample data and table definitions, we might be able to reproduce your problem and find a solution.

Pavesa · July 13th, 2006, 08:41 AM

Hi Peso,

thanks for the suggestion.. I'm submitting the query formulated in the Tab1, Tab2 approach above. It isn't possible to submit sample data as the historical data table Tab2 contains over 8 million rows.

ALTER PROCEDURE UserID1.StoredProcedure28
AS SELECT dbo.Tab2.CompID, dbo.Tab2.Date, dbo.Tab1.Country
FROM dbo.Tab1 INNER JOIN
dbo.Tab2 ON dbo.Tab1.CompID = dbo.Tab2.CompID
WHERE (dbo.Tab2.Date = CONVERT(DATETIME, '2006-07-11 00:00:00', 102)) AND (dbo.Tab1.Country = 49)
ORDER BY dbo.Tab2.CompID

I'm in the UK using the dd/mm/yy date format, hence the CONVERT(DATETIME, ..)

where variable properties are

dbo.Tab2.CompID type:smallint length:2

dbo.Tab2.Date type:datetime length:8

dbo.Tab1.CompID type:real length:4

dbo.Tab1.Country type:tinyint length:1

Regards and thanks

Andrew

Peso · July 13th, 2006, 02:00 PM

If you do a SELECT COUNT(*) only, how many records do you fetch for each country?

Code:

DECLARE    @dt DATETIME

SELECT @dt = 'July 11, 2006'

SELECT        Tab1.Country,
            COUNT(*)
FROM        Tab1
INNER JOIN    Tab2 ON Tab1.CompID = Tab2.CompID
WHERE            Tab2.Date = @dt
                AND Tab1.Country IN (1, 201, 81, 44, 33, 49)
GROUP BY    Tab1.Country
ORDER BY    Tab2.CompID

Maybe the error you get is due to trying to run as dbo? Maybe you lack proper indexes?

Pavesa · July 14th, 2006, 03:51 AM

Hi Peso,

numbers of companies by country are:

33: 641, 49: 298, 81: 1731, 44: 699, 1: 2414, 201: 560

Germany, with the smallest number at 298 is the one giving the error..

I'm not sure why running as dbo would cause an error on Germany but none of the others, although doing something the DB is unhappy with can cause unexpected and rather inexplicable outcomes!

CompID is a primary key in both Tab1 and Tab2 and Date is a primary key in Tab2 - the one with the historical data so I guess they are indexed..