• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

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
0
polynominal
Asked:
polynominal
  • 14
  • 10
  • 4
  • +1
1 Solution
 
ispalenyCommented:
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.
0
 
polynominalAuthor Commented:
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

0
 
rafranciscoCommented:
Do you indexes on the following:

complete.engcomplete
job.jobid
complete.jobid
vw_job_abd_nodcr.jobid
contract.contractid
job.contractid
vw_catanotesfilter.jobid
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
polynominalAuthor Commented:
Do you indexes on the following:

How do I check or do that?
0
 
rafranciscoCommented:
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?
0
 
rafranciscoCommented:
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
0
 
polynominalAuthor Commented:
hi the other two are views which narrow the selection
0
 
polynominalAuthor Commented:
When I execute the query in Analyser it takes 2 seconds to execute but pass it through Crystal and it falls over
0
 
ispalenyCommented:
Of what datatype is column complete.engcomplete ?
0
 
rafranciscoCommented:
>> 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.
0
 
polynominalAuthor Commented:
complete.engcomplete is smalldatetime
0
 
polynominalAuthor Commented:
the tables are indexed, however how do I check the index on a view, I would doubt they have been indexed
0
 
rafranciscoCommented:
Check if the tables being used by the view is indexed.
0
 
polynominalAuthor Commented:
They have all been indexed
0
 
ispalenyCommented:
And job.rbd column datatype?
0
 
polynominalAuthor Commented:
smalldatetime
0
 
rafranciscoCommented:
>> 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?
0
 
ispalenyCommented:
It is an ODBC driver error, maybe you need some ODBC fix update.
0
 
polynominalAuthor Commented:
When it runs properly in Crystal it should be bringing in 30 - 40 recors in total
0
 
rafranciscoCommented:
Have you checked your Crystal report if you've specified the correct data types in the report itself?
0
 
polynominalAuthor Commented:
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

0
 
rafranciscoCommented:
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.
0
 
polynominalAuthor Commented:
Hi I am doing no formatting as the numbers from the stored procedure are fine as they are
0
 
rafranciscoCommented:
Hmmmm... have you checked out what ispaleny suggested :

>> It is an ODBC driver error, maybe you need some ODBC fix update. <<
0
 
Brendt HessSenior DBACommented:
What version of Crystal Reports are you using?
0
 
polynominalAuthor Commented:
Im using v 10
0
 
polynominalAuthor Commented:
My ODBC is fine too
0
 
polynominalAuthor Commented:
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
0
 
rafranciscoCommented:
>> [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. <<

I don't know if you're still looking for an answer to this but just wondering if you ever tried entering a full date and time in the parameters.  It may be possible that since you are not entering a full date and time in the parameters, the stored procedure may not actually being executed yet.  To test this theory, you can create a temporary log table and in your stored procedure at the start, insert the parameters received by the stored procedure into the temporary log table.  Given this, we will be able to determine if the error is inside the sp or outside.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 14
  • 10
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now