[dba-SQLServer]Sent Wrong Info Regarding Stored Procedure help URGENT.

Offhand, I don't see anything wrong. Have you tried the join as a select
statement instead, just to see how quick it is? If the select is slow, try
creating an index on the two columns comprising the join, and see if that
speeds things up.

A second thought. In some code I wrote recently, it ran fine on my sample
data but timed out on the real data. I was using the DMO library, with a
command object &c. Then I suddenly remembered that the command object has a
Timeout property (that's not its precise name, which eludes me at the
moment, but it's close). The default was too low for the process. Increasing
it fixed the problem. It could work for you.

Arthur

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
paul.hartland at fsmail.net
Sent: June 30, 2003 5:28 AM
To: dba-sqlserver at databaseadvisors.com; accessd at databaseadvisors.com;
dba-vb at databaseadvisors.com
Subject: [dba-SQLServer]Sent Wrong Info Regarding Stored Procedure help
URGENT.


To all,

I have two tables tblAvailabilityAll and tblAvailabilityCodes, in
tblAvailabilityAll I have a payroll number, availability code (A, N, S or H)
and a jobdate, in tblAvailabilityCodes I have an availability code and a
corresponding number i.e

Code     Number
A          1
N          2
S          3
H          4

I have a VB6 form with an employees details on, and when a finish date is
entered I need to convert the availability code in tblAvailabilityAll to the
corresponding number in tblAvailabilityCodes.  I have the following stored
procedure (this is the one thats timing out, not the on in the previous
email).

CREATE PROCEDURE [genesis_update_AvailabilityCode] 
	( @Payroll_1		[nvarchar] (25),
	  @MyDate_2		[datetime] )
AS UPDATE [Genesis].[dbo].[tblAvailabilityAll]
SET AvailabilityCode = tblAvailabilityCodes.CancelledAvailabilityCode
FROM  tblAvailabilityAll INNER JOIN tblAvailabilityCodes
ON AvailabilityCode = tblAvailabilityCodes.LiveAvailabilityCode
WHERE (tblAvailabilityAll.PayrollNo = @Payroll_1 AND
	 tblAvailabilityAll.JobDate >= @MyDate_2)

can anyone see where the stored procedure may be going wrong......I could
really do with some urgent help on this...

Paul

__________________________________________________________________________
Join Freeserve http://www.freeserve.com/time/

Winner of the 2003 Internet Service Providers' Association awards for Best
Unmetered ISP and Best Consumer Application.


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com