asked on

Lock Out Error On Stored Procedure

Hi,

I have a stored procedure below

@startdate smalldatetime,
@enddate smalldatetime
AS
SELECT A.clientcontractno,A.Met, A.Less, B. MetABD, B.LessABD, C.Delivery, C.Complete, D.OriginalValue, D.ActualValue, D.Jobs, E.Orders, E.Value, F.Stoppages, F.svalue, G.Job, G.sjob
FROM
(SELECT contract.clientcontractno, SUM(CASE WHEN datediff(dd, complete.engcomplete, job.rbd) >= 0 THEN 1 ELSE 0 END) AS Met, COUNT(job.jobid) AS Less
FROM
job
INNER JOIN complete ON job.jobid = complete.jobid
INNER JOIN vw_job_abd_nodcr ON job.jobid = vw_job_abd_nodcr.jobid
INNER JOIN contract ON job.contractid = contract.contractid
INNER JOIN vw_catanotesfilter ON job.jobid = vw_catanotesfilter.jobid
WHERE complete.engcomplete BETWEEN @startdate AND @enddate
GROUP BY contract.clientcontractno) A
LEFT OUTER join
(SELECT contract.clientcontractno, SUM(CASE WHEN datediff(dd, complete.engcomplete, vw_job_abd.abd) >= 0 THEN 1 ELSE 0 END) AS MetABD, COUNT(job.jobid) AS LessABD
FROM
job INNER JOIN complete ON job.jobid = complete.jobid INNER JOIN
contract ON job.contractid = contract.contractid INNER JOIN
vw_job_abd_withdcr ON job.jobid = vw_job_abd_withdcr.jobid INNER JOIN
vw_catanotesfilter ON job.jobid = vw_catanotesfilter.jobid INNER JOIN
vw_job_abd ON job.jobid = vw_job_abd.jobid
WHERE complete.engcomplete BETWEEN @startdate AND @enddate
GROUP BY contract.clientcontractno) B
On A.clientcontractno = B.clientcontractno
Left outer join
(SELECT contract.clientcontractno, SUM(CASE WHEN datediff(dd, complete.engcomplete, vw_job_abd.abd) >= 0 THEN 1 ELSE 0 END) AS Delivery, COUNT(job.jobid) AS Complete
FROM job INNER JOIN
complete ON job.jobid = complete.jobid INNER JOIN
contract ON job.contractid = contract.contractid INNER JOIN
vw_catanotesfilter ON job.jobid = vw_catanotesfilter.jobid INNER JOIN
vw_job_abd ON job.jobid = vw_job_abd.jobid
WHERE complete.engcomplete BETWEEN @startdate AND @enddate
GROUP BY contract.clientcontractno) C
On A.clientcontractno = C.clientcontractno
Left outer join
(SELECT contract.clientcontractno, SUM(vw_job_originaljobvalue.value) as OriginalValue, SUM(job.autval) as Actualvalue, count(job.jobid) as Jobs
FROM job INNER JOIN
contract ON job.contractid = contract.contractid INNER JOIN
originator ON job.originatorid = originator.originatorid INNER JOIN
vw_catanotesfilter ON job.jobid = vw_catanotesfilter.jobid INNER JOIN
complete ON job.jobid = complete.jobid LEFT OUTER JOIN
vw_job_originaljobvalue ON job.jobid = vw_job_originaljobvalue.jobid LEFT OUTER JOIN
vw_job_jobscancelled ON job.jobid = vw_job_jobscancelled.jobid
WHERE complete.engcomplete BETWEEN @startdate AND @enddate and contract.clientcontractno in (65583611,65583612,67325921, 67325922)
GROUP BY contract.clientcontractno) D
On A.clientcontractno = D.clientcontractno
Left outer join
(SELECT contract.clientcontractno, count(job.jobid) as Orders, SUM(job.autval) as Actualvalue, count(job.jobid) as Value
FROM job INNER JOIN
contract ON job.contractid = contract.contractid INNER JOIN
vw_catanotesfilter ON job.jobid = vw_catanotesfilter.jobid INNER JOIN
complete ON job.jobid = complete.jobid
WHERE complete.engcomplete BETWEEN @startdate AND @enddate and contract.clientcontractno in (65583611,65583612,67325921, 67325922)
GROUP BY contract.clientcontractno) E
On A.clientcontractno = E.clientcontractno
Left outer join
(SELECT contract.clientcontractno, count(job.jobid) as Stoppages, SUM(job.autval) as svalue
FROM complete INNER JOIN
job ON complete.jobid = job.jobid INNER JOIN
contract ON job.contractid = contract.contractid INNER JOIN
vw_catanotesfilter ON job.jobid = vw_catanotesfilter.jobid INNER JOIN
vw_job_N333 ON job.jobid = vw_job_N333.jobid LEFT OUTER JOIN
vw_job_N08 ON job.jobid = vw_job_N08.jobid
WHERE complete.engcomplete BETWEEN @startdate AND @enddate
GROUP BY contract.clientcontractno) F
On A.clientcontractno = F.clientcontractno
Left outer join
(SELECT contract.clientcontractno, count(job.jobid) as Job, SUM(job.autval) as sjob
FROM complete
INNER JOIN
job ON complete.jobid = job.jobid INNER JOIN
vw_catanotesfilter ON job.jobid = vw_catanotesfilter.jobid INNER JOIN
contract ON job.contractid = contract.contractid
WHERE complete.engcomplete BETWEEN @startdate AND @enddate
GROUP BY contract.clientcontractno) G
On A.clientcontractno = G.clientcontractno
Left outer join
(SELECT contract.clientcontractno, count(job.jobid) as Accrual, SUM(job.autval) as saccrual
FROM job INNER JOIN
vw_catanotesfilter ON job.jobid = vw_catanotesfilter.jobid INNER JOIN
contract ON job.contractid = contract.contractid
WHERE job.statusid = 5 and vw_catanotesfilter.notes is not null
GROUP BY contract.clientcontractno) H
On A.clientcontractno = H.clientcontractno

When I run it or add it to Crystal Reports, I keep getting as lock out error, I then run debur in Query Analyser and got the following error message

[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

Can anybody tell me what this is

Thanks

P

ispaleny

It is too huge query to find it. Replace variables by getdate() and try to run smaller parts. If they are OK, it is in their joins. Maybe you will find a smaller part that fails.

I expect a datetime value problem, because of its conversion is international settings sensitive.

polynominal

ASKER

Even wwhen I break it down to just the first part it takes an age to bring the results back

@startdate smalldatetime,
@enddate smalldatetime
AS
SELECT A.clientcontractno,A.Met, A.Less
FROM
(SELECT contract.clientcontractno, SUM(CASE WHEN datediff(dd, complete.engcomplete, job.rbd) >= 0 THEN 1 ELSE 0 END) AS Met, COUNT(job.jobid) AS Less
FROM
job
INNER JOIN complete ON job.jobid = complete.jobid
INNER JOIN vw_job_abd_nodcr ON job.jobid = vw_job_abd_nodcr.jobid
INNER JOIN contract ON job.contractid = contract.contractid
INNER JOIN vw_catanotesfilter ON job.jobid = vw_catanotesfilter.jobid
WHERE complete.engcomplete BETWEEN @startdate AND @enddate
GROUP BY contract.clientcontractno) A

rafrancisco

Do you indexes on the following:

complete.engcomplete
job.jobid
complete.jobid
vw_job_abd_nodcr.jobid
contract.contractid
job.contractid
vw_catanotesfilter.jobid

polynominal

ASKER

Do you indexes on the following:

How do I check or do that?

rafrancisco

Also, why include these joins

INNER JOIN vw_job_abd_nodcr ON job.jobid = vw_job_abd_nodcr.jobid

INNER JOIN vw_catanotesfilter ON job.jobid = vw_catanotesfilter.jobid

when they are not being used?

rafrancisco

Try this one:

SELECT A.clientcontractno,A.Met, A.Less
FROM
(SELECT contract.clientcontractno, SUM(CASE WHEN datediff(dd, complete.engcomplete, job.rbd) >= 0 THEN 1 ELSE 0 END) AS Met, COUNT(job.jobid) AS Less
FROM
job
INNER JOIN complete ON job.jobid = complete.jobid
INNER JOIN contract ON job.contractid = contract.contractid
WHERE complete.engcomplete BETWEEN @startdate AND @enddate
GROUP BY contract.clientcontractno) A

polynominal

ASKER

hi the other two are views which narrow the selection

polynominal

ASKER

When I execute the query in Analyser it takes 2 seconds to execute but pass it through Crystal and it falls over

ispaleny

Of what datatype is column complete.engcomplete ?

rafrancisco

>> Do you have indexes on the following: How do I check or do that? <<

Go to Enterprise Manager, right-click on the table and select Design Table. On the icons on top, click on the second to the last, the "Manage Indexes/Keys" and see if there are indexes there.

polynominal

ASKER

complete.engcomplete is smalldatetime

polynominal

ASKER

the tables are indexed, however how do I check the index on a view, I would doubt they have been indexed

rafrancisco

Check if the tables being used by the view is indexed.

polynominal

ASKER

They have all been indexed

ispaleny

And job.rbd column datatype?

polynominal

ASKER

smalldatetime

rafrancisco

>> When I execute the query in Analyser it takes 2 seconds to execute but pass it through Crystal and it falls over <<

Is it possible that the problem is with Crystal? How many records are being returned by the stored procedure?

ispaleny

It is an ODBC driver error, maybe you need some ODBC fix update.

polynominal

ASKER

When it runs properly in Crystal it should be bringing in 30 - 40 recors in total

rafrancisco

Have you checked your Crystal report if you've specified the correct data types in the report itself?

polynominal

ASKER

Thanks for the answers so far

I dont understand Have you checked your Crystal report if you've specified the correct data types in the report itself?

I have just placed the fields in the stored procedure and arranged them on the report

rafrancisco

I am not really that familiar with Crystal reports but I was assuming that you specify the data types (or at least the formatting) in your Crystal report for each column returned by the stored procedure.

polynominal

ASKER

Hi I am doing no formatting as the numbers from the stored procedure are fine as they are

rafrancisco

Hmmmm... have you checked out what ispaleny suggested :

>> It is an ODBC driver error, maybe you need some ODBC fix update. <<

Brendt Hess

What version of Crystal Reports are you using?

polynominal

ASKER

Im using v 10

polynominal

ASKER

My ODBC is fine too

polynominal

ASKER

Its strange that this is the only stored procedure out of 70 that I have that locks out and is extremely slow, I have a few that are similar to the one above amd I have no problems with them.

[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification - came up becaue I never entered full date and time when entering the parameters to debug.

However the sp keeps nringing Crystal down

ASKER CERTIFIED SOLUTION

rafrancisco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial