[Microsoft][ODBC sql Server Driver]Time out expired

Question

Post reply

[Microsoft][ODBC sql Server Driver]Time out expired

mngong

SSCertifiable

Points: 6167
More actions
June 27, 2006 at 9:20 am

#60823

There is a view the users complain cannot be queried .When queried it errs with
[Microsoft][ODBC sql Server Driver]Time out expired ]
and
[Microsoft][ODBC sql Server Driver]Subquery returned more than one value].This is not permitted when the subquery follows =,!=,< ,<=,>,>= or when the subquery is used as an expression.
The writer is logged on to the server and has the same issues ,has rebuilt indexes for some of the tables and problem dissapeared but only briefly.
Any help will be appreciated

Mike

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Balmukund SSCertifiable Points: 6648 More actions · Answer 1

what is view definition?

please post output of

sp_helptext 'view_name'

Can you run only select query inside view definitions. It will be tricky to debug without data. Lets see if we can figure out...

mngong SSCertifiable Points: 6167 More actions · Answer 2

CREATE VIEW dbo.DEFECTS

AS

SELECT DISTINCT

TOP 100 PERCENT CONVERT(SMALLDATETIME, dbo.NH_NONCONFORMANCE.NH_RECORD_DATE, 102) AS [Record Date],

dbo.ND_NC_DEFECT.ND_NC_NUMBER AS [NC Number], dbo.ND_NC_DEFECT.ND_INTERNAL_NO AS [Internal Defect Number],

dbo.ND_NC_DEFECT.ND_LOCATION AS Location, dbo.ND_NC_DEFECT.ND_CLIENT AS [Defect Client],

dbo.NH_NONCONFORMANCE.NH_PART_NUMBER AS [Main Part Number], dbo.ND_NC_DEFECT.ND_PART_NO AS [Component Part Number],

dbo.ND_NC_DEFECT.ND_PART_REVISION AS [Component Part Rev], dbo.ND_NC_DEFECT.ND_DEFECT_LINE_NO AS [Defect Line Number],

dbo.ND_NC_DEFECT.ND_DEFECT AS [Defect Characteristic], dbo.ND_NC_DEFECT.ND_DEFECT_TYPE AS [Defect Type],

dbo.ND_NC_DEFECT.ND_EXPECTED_NOMINAL AS [Expected Nominal], dbo.ND_NC_DEFECT.ND_UNIT AS [Nominal Unit of Measure],

dbo.ND_NC_DEFECT.ND_DEFECT_CATEGORY AS [Defect Category], dbo.ND_NC_DEFECT.ND_MEASURED_NOMINAL AS Measurement,

dbo.ND_NC_DEFECT.ND_NO_OF_DEFECTS AS [Number of Defects], dbo.ND_NC_DEFECT.ND_QUANTIY_AFFECTED AS [Quantity of Parts Affected],

dbo.ND_NC_DEFECT.ND_COST_OF_DEFECT AS [Cost of Defect], dbo.ND_NC_DEFECT.ND_USER_DEFINED_1 AS [Tester Code],

dbo.ND_NC_DEFECT.ND_USER_DEFINED_2 AS [Tester Fault Code], dbo.ND_NC_DEFECT.ND_DESCRIPTION AS [Defect Description],

dbo.[Defect-Resp Client](dbo.ND_NC_DEFECT.ND_NC_NUMBER, dbo.ND_NC_DEFECT.ND_INTERNAL_NO,

dbo.NH_NONCONFORMANCE.NH_RCA_IS_GLOBAL, dbo.NR_NC_ROOT_CAUSE.NR_CAUSED_BY_DPT_CLN) AS [Resp Client],

dbo.[Defect-Root Cause](dbo.ND_NC_DEFECT.ND_NC_NUMBER, dbo.ND_NC_DEFECT.ND_INTERNAL_NO,

dbo.NH_NONCONFORMANCE.NH_RCA_IS_GLOBAL, dbo.NR_NC_ROOT_CAUSE.NR_ROOT_CAUSE) AS [Root Cause],

dbo.GETRNUM(dbo.ND_NC_DEFECT.ND_NC_NUMBER) AS [Warranty Reference Number],

dbo.NH_NONCONFORMANCE.NH_SOURCE_PLANT_AREA AS [Reporting Area],

dbo.NR_NC_ROOT_CAUSE.NR_CAUSED_BY_EMPL AS [Responsible Employee],

dbo.[Rep WC](dbo.NH_NONCONFORMANCE.NH_SOURCE_PLANT_AREA) AS [Reporting WC]

FROM dbo.ND_NC_DEFECT INNER JOIN

dbo.NH_NONCONFORMANCE ON dbo.ND_NC_DEFECT.ND_NC_NUMBER = dbo.NH_NONCONFORMANCE.NH_NC_NUMBER INNER JOIN

dbo.NR_NC_ROOT_CAUSE ON dbo.ND_NC_DEFECT.ND_NC_NUMBER = dbo.NR_NC_ROOT_CAUSE.NR_NC_NUMBER

WHERE (NOT (dbo.[Defect-Root Cause](dbo.ND_NC_DEFECT.ND_NC_NUMBER, dbo.ND_NC_DEFECT.ND_INTERNAL_NO,

dbo.NH_NONCONFORMANCE.NH_RCA_IS_GLOBAL, dbo.NR_NC_ROOT_CAUSE.NR_ROOT_CAUSE) = 'N/A'))

ORDER BY dbo.ND_NC_DEFECT.ND_NC_NUMBER

Looks more like an update issue than a sql server problem

Mike

John Rowan SSC Guru Points: 56440 More actions · Answer 3

The vast majority of the time, a timeout expired message means that an operation is taking longer than the timeout threashhold. This is usually due to resource contention of some sort. The contention could be resources on a web server, network latency, or database server resources. In your case, how are the users querying the view, through QA, through a front end application? If they are using an application, try performing the same query through QA and see if you timeout.

You mentioned that rebuilding indexes buys a bit of time, it sounds like you need to regularly rebuild indexes on the tables involved in your view. How many rows are in the tables in the view?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

mngong SSCertifiable Points: 6167 More actions · Answer 4

The concerned tables were moved to a different server and everything worked just fine.

It is time to look into what patches and stuff were applied on the server .

Thanks

John Rowan SSC Guru Points: 56440 More actions · Answer 5

Does your new server have the same CPU, RAM, and disk subsystem as the offending server? You may want to monitor these three areas and duplicate the problem on your original server. I would bet that it is a resource problem, not a patch problem.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

mngong SSCertifiable Points: 6167 More actions · Answer 6

The present server is a dedicated server with a single database but the other which passed the test has 40 DBs do not ask me why?

That does not eliminate a possible contention problem .I will keep investigating and keep you and everyone who is keeping tab up to date

Thanks

mngong SSCertifiable Points: 6167 More actions · Answer 7

The problem was bad data.

Thanks for all your suggestions and your time