Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3305
  • Last Modified:

Error 1222 [Microsoft] [ODBC SQL Server Driver] Lock Request Time Out Period Exceeded

Hi,

I get this message when I'm trying to save new data in the database while a Crystal Reports report is running.  I get this message with only one report. See query below.  Is it possible that this query is just to complicated and that MS SQL Server needs to lock the database in writing to be able to produce the reports? The reports takes about three minutes to run.  

Other than creating another synchronized database used just for reporting, is there possibly a setting that could be changed in MS SQL Server to solve this problem?

Thanks

Francis
SELECT "brnch"."brnch_nme", "brnch"."brnch_id", "wrkordrlg"."wrkordrlgtype_id", "wrkordrlg"."wrkordrlg_dte_lggd", "wrkordr"."wrkordr_dte_opnd", "invcemstr"."invcemstr_dte_effctve", "glaccnt"."glaccnt_id", "glentty"."glentty_id", "jchstry"."jchstry_id", "jchstry"."jchstry_ttl_cst", "jbtype"."jbtype_nme", "clnt"."clnt_id", "srvceaccntmngr"."srvceaccntmngr_id", "glaccnt"."glaccnt_nme", "glentty"."glentty_nme", "jb"."jb_ovrhd", "jchstry"."jchstry_dte_effctve" 
FROM   (((((((((("Carmichael"."dbo"."invcemstr" "invcemstr" INNER JOIN "Carmichael"."dbo"."jchstry" "jchstry" ON "invcemstr"."invcemstr_id"="jchstry"."jchstry_rfrnce_ar_invce_id") INNER JOIN ("Carmichael"."dbo"."wrkordrlg" "wrkordrlg" INNER JOIN "Carmichael"."dbo"."wrkordr" "wrkordr" ON "wrkordrlg"."wrkordr_rn"="wrkordr"."wrkordr_rn") ON "jchstry"."jchstry_wo_id"="wrkordr"."wrkordr_id") INNER JOIN "Carmichael"."dbo"."jbcstcde" "jbcstcde" ON "jchstry"."jbcstcde_rn"="jbcstcde"."jbcstcde_rn") INNER JOIN "Carmichael"."dbo"."jbcstctgry" "jbcstctgry" ON "jchstry"."jbcstctgry_rn"="jbcstctgry"."jbcstctgry_rn") INNER JOIN "Carmichael"."dbo"."glentty" "glentty" ON "jchstry"."glentty_rn"="glentty"."glentty_rn") INNER JOIN "Carmichael"."dbo"."jb" "jb" ON "jbcstcde"."jb_rn"="jb"."jb_rn") INNER JOIN "Carmichael"."dbo"."clnt" "clnt" ON "jb"."clnt_rn"="clnt"."clnt_rn") INNER JOIN "Carmichael"."dbo"."jbtype" "jbtype" ON "jb"."jbtype_rn"="jbtype"."jbtype_rn") INNER JOIN "Carmichael"."dbo"."glaccnt" "glaccnt" ON "jbcstctgry"."gl_expnse_glaccnt_rn"="glaccnt"."glaccnt_rn") INNER JOIN "Carmichael"."dbo"."brnch" "brnch" ON "wrkordr"."brnch_rn"="brnch"."brnch_rn") INNER JOIN "Carmichael"."dbo"."srvceaccntmngr" "srvceaccntmngr" ON "wrkordr"."srvceaccntmngr_rn"="srvceaccntmngr"."srvceaccntmngr_rn" 
WHERE  ("wrkordrlg"."wrkordrlgtype_id"='Active' OR "wrkordrlg"."wrkordrlgtype_id"='Billed' OR "wrkordrlg"."wrkordrlgtype_id"='Cancelled' OR "wrkordrlg"."wrkordrlgtype_id"='Completed' OR "wrkordrlg"."wrkordrlgtype_id"='Credit Hold' OR "wrkordrlg"."wrkordrlgtype_id"='Hold' OR "wrkordrlg"."wrkordrlgtype_id"='Invoice Ready' OR "wrkordrlg"."wrkordrlgtype_id"='New' OR "wrkordrlg"."wrkordrlgtype_id"='Parts Ordered' OR "wrkordrlg"."wrkordrlgtype_id"='Parts Received' OR "wrkordrlg"."wrkordrlgtype_id"='Pending' OR "wrkordrlg"."wrkordrlgtype_id"='Quote Approved' OR "wrkordrlg"."wrkordrlgtype_id"='Wait Paperwork' OR "wrkordrlg"."wrkordrlgtype_id"='WaitCustApprvl' OR "wrkordrlg"."wrkordrlgtype_id"='Waiting Parts' OR "wrkordrlg"."wrkordrlgtype_id"='Waiting Service') AND ("brnch"."brnch_id"='20' OR "brnch"."brnch_id"='24' OR "brnch"."brnch_id"='30') AND ("clnt"."clnt_id">=' ' AND "clnt"."clnt_id"<='ZZZ') AND ("jbtype"."jbtype_nme"='Controls-Construction' OR "jbtype"."jbtype_nme"='HVAC-Construction' OR "jbtype"."jbtype_nme"='Plumbing-Construction' OR "jbtype"."jbtype_nme"='Repair' OR "jbtype"."jbtype_nme"='Scheduled Maintenance' OR "jbtype"."jbtype_nme"='Total Coverage Maintenance') AND "jchstry"."jchstry_ttl_cst"<>0 AND ("glentty"."glentty_id"='20-000' OR "glentty"."glentty_id"='20-001' OR "glentty"."glentty_id"='20-100' OR "glentty"."glentty_id"='20-200' OR "glentty"."glentty_id"='20-300' OR "glentty"."glentty_id"='20-500' OR "glentty"."glentty_id"='20-700' OR "glentty"."glentty_id"='20-900' OR "glentty"."glentty_id"='20-999' OR "glentty"."glentty_id"='30-000' OR "glentty"."glentty_id"='30-200' OR "glentty"."glentty_id"='30-300' OR "glentty"."glentty_id"='30-500') AND ("srvceaccntmngr"."srvceaccntmngr_id"=' ' OR "srvceaccntmngr"."srvceaccntmngr_id"='20-CM1' OR "srvceaccntmngr"."srvceaccntmngr_id"='20-DL1' OR "srvceaccntmngr"."srvceaccntmngr_id"='20-SS1' OR "srvceaccntmngr"."srvceaccntmngr_id"='30-ALYRE' OR "srvceaccntmngr"."srvceaccntmngr_id"='30-JIM' OR "srvceaccntmngr"."srvceaccntmngr_id"='30-JP' OR "srvceaccntmngr"."srvceaccntmngr_id"='30-LOUIS' OR "srvceaccntmngr"."srvceaccntmngr_id"='30-MIKE' OR "srvceaccntmngr"."srvceaccntmngr_id"='30-TIM' OR "srvceaccntmngr"."srvceaccntmngr_id"='Chiller') AND "jb"."jb_ovrhd"='N' AND "jchstry"."jchstry_dte_effctve">'2007-01-01' 
ORDER BY "brnch"."brnch_id"

Open in new window

0
AlainSt-Pierre
Asked:
AlainSt-Pierre
  • 6
  • 4
  • 3
  • +3
2 Solutions
 
mlmccCommented:
Do the pther reports use similarly complex queries?

Is the query built in the database or in Crystal?

mlmcc
0
 
AlainSt-PierreAuthor Commented:
Hi,

Do the other reports use similarly complex queries?

There's no other report. However, the other process consists of adding records to some of the tables that are used to produce the report.

Is the query built in the database or in Crystal?

I tried to only build it in Crystal, and then I tried to use a view in MS SQL Server.  The result is the same for the user that runs the adding record process: Error 1222 [Microsoft] [ODBC SQL Server Driver] Lock Request Time Out

Thanks
0
 
mlmccCommented:
If you do it in the database you should be able to add NOLOCK to the tables.

mlmcc
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
AlainSt-PierreAuthor Commented:
And how can this be achieved?
0
 
mlmccCommented:
Did you try adding NOLOCK to the MS SQL view?

mlmcc
0
 
AlainSt-PierreAuthor Commented:
I just did (see below) and it's not working.  Did I do this properly?
FROM         dbo.jbbllngitm INNER JOIN
                      dbo.jchstry WITH (NOLOCK) INNER JOIN
                      dbo.jb WITH (NOLOCK) INNER JOIN
                      dbo.jbcstcde WITH (NOLOCK) ON dbo.jb.jb_rn = dbo.jbcstcde.jb_rn ON dbo.jchstry.jbcstcde_rn = dbo.jbcstcde.jbcstcde_rn ON 
                      dbo.jbbllngitm.jbbllngitm_rn = dbo.jchstry.jbbllngitm_rn INNER JOIN
                      dbo.invcemstr WITH (NOLOCK) ON dbo.jchstry.jchstry_rfrnce_ar_invce_id = dbo.invcemstr.invcemstr_id INNER JOIN
                      dbo.jbtype WITH (NOLOCK) ON dbo.jb.jbtype_rn = dbo.jbtype.jbtype_rn INNER JOIN
                      dbo.wrkordr WITH (NOLOCK) ON dbo.jchstry.jchstry_wo_id = dbo.wrkordr.wrkordr_id INNER JOIN
                      dbo.clnt WITH (NOLOCK) ON dbo.jb.clnt_rn = dbo.clnt.clnt_rn INNER JOIN
                      dbo.srvceaccntmngr WITH (NOLOCK) ON dbo.wrkordr.srvceaccntmngr_rn = dbo.srvceaccntmngr.srvceaccntmngr_rn INNER JOIN
                      dbo.glentty WITH (NOLOCK) ON dbo.jchstry.glentty_rn = dbo.glentty.glentty_rn INNER JOIN
                      dbo.jbcstctgry WITH (NOLOCK) ON dbo.jchstry.jbcstctgry_rn = dbo.jbcstctgry.jbcstctgry_rn INNER JOIN
                      dbo.glaccnt WITH (NOLOCK) ON dbo.jbcstctgry.gl_expnse_glaccnt_rn = dbo.glaccnt.glaccnt_rn INNER JOIN
                      dbo.brnch WITH (NOLOCK) ON dbo.wrkordr.brnch_rn = dbo.brnch.brnch_rn

Open in new window

0
 
mlmccCommented:
I don't know.

mlmcc
0
 
QlemoC++ DeveloperCommented:
The "lock timeout" message can appear if the client has to wait too long for an answer, it has not to be related to locks. However, you can try the NOLOCK (above code should work).

Did you try the report SQL in Management Studio, too? It should not have any timeout defined as default, and if it is not a locking issue result in your report data, and you will have to increase the query timeout value in the application.

0
 
RiteshShahCommented:
Olemo is right, even try this code...


FROM         dbo.jbbllngitm WITH (NOLOCK) INNER JOIN
                      dbo.jchstry WITH (NOLOCK) INNER JOIN
                      dbo.jb WITH (NOLOCK) INNER JOIN
                      dbo.jbcstcde WITH (NOLOCK) ON dbo.jb.jb_rn = dbo.jbcstcde.jb_rn ON dbo.jchstry.jbcstcde_rn = dbo.jbcstcde.jbcstcde_rn ON 
                      dbo.jbbllngitm.jbbllngitm_rn = dbo.jchstry.jbbllngitm_rn INNER JOIN
                      dbo.invcemstr WITH (NOLOCK) ON dbo.jchstry.jchstry_rfrnce_ar_invce_id = dbo.invcemstr.invcemstr_id INNER JOIN
                      dbo.jbtype WITH (NOLOCK) ON dbo.jb.jbtype_rn = dbo.jbtype.jbtype_rn INNER JOIN
                      dbo.wrkordr WITH (NOLOCK) ON dbo.jchstry.jchstry_wo_id = dbo.wrkordr.wrkordr_id INNER JOIN
                      dbo.clnt WITH (NOLOCK) ON dbo.jb.clnt_rn = dbo.clnt.clnt_rn INNER JOIN
                      dbo.srvceaccntmngr WITH (NOLOCK) ON dbo.wrkordr.srvceaccntmngr_rn = dbo.srvceaccntmngr.srvceaccntmngr_rn INNER JOIN
                      dbo.glentty WITH (NOLOCK) ON dbo.jchstry.glentty_rn = dbo.glentty.glentty_rn INNER JOIN
                      dbo.jbcstctgry WITH (NOLOCK) ON dbo.jchstry.jbcstctgry_rn = dbo.jbcstctgry.jbcstctgry_rn INNER JOIN
                      dbo.glaccnt WITH (NOLOCK) ON dbo.jbcstctgry.gl_expnse_glaccnt_rn = dbo.glaccnt.glaccnt_rn INNER JOIN
                      dbo.brnch WITH (NOLOCK) ON dbo.wrkordr.brnch_rn = dbo.brnch.brnch_rn

Open in new window

0
 
James0628Commented:
I assume that this is the same problem you asked about in this question:

 http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24365253.html

 James
0
 
AlainSt-PierreAuthor Commented:
James,

If I accepted your advice, as I said earlier, I have my reasons.  Keep you interested, be nice, make friends, and give my complain new words.

The problem it seems doesn't come from Crystal, but from SQL Server.  It's unfortunate that you lost all that time and friendliness in solving the wrong problem, and I'm being frank here.  See the problem explained in its actual lenght and width:

I think we had a good path with the unlock claim. The thing is, I wanna make sure my syntax is correct which is probably the case, and I'd also like to know if the Unlock statement can be applied to tables, not just views.  This way, I could at least sink this very primary hypothesis.  I know you probably don't know SQL Server very well -nor do I- , but this question is elegible for every BLACK BELTED DBA that can kick just anyone's ass in their field of study.  We didn't get much valuable answers yet.  That would be great to get an answer from a respected Jedie.  What am I' going to give away in exchange? I know a few things and I'm always very grateful to anyone who willing to help me.  

This unlock thing has to do with the Commitment of the data I'm using both for reports and append queries (which save new records to the database by the mean of some accounting software.)  When a query has to run for too long, the server locks data entry to prevent the report from being somehow wrong.  Would we be able to say we give no "shit" about "Dirty Reads", how can we get the users to be able to perform those append queries -throug accounting software- while we are reading the same tables for reporting purposes -through Crystal Reports? The question isn't so complicated after all.  I'll give a definition of "Dirty Read" later if you can all manage this for now.  If we run into a kickass DBA that can propose a correct answer on this, we'll pay him any shooters he will need for a whole day of leisure -even n Hawaï! Our company doesn't have any boundary.

Is "dirty read" unability the root of our problem or does it have to do with something else? What else could there be? I'm thinking about the possible "unsufficiant cache memory" allowed by the Windows Registry to Crystal Reports.  Maybe is there a registry entry we can use to get the appllication to allow the data retrieving temporary file to grow bigger than it's doing right now.  

The view I made in SQL Server gives me all I need for this report, except for two small unimportant tables that, strangely in fact, I couldn't include in the View.  With just those additionnal two secondary tables, the query wouldn't run within five trials. I would get an error message, but this has nothing to do with the issue we're strugling with right now. Those error messages are not permanent, they have to do with the size of our business activity at a specific moment. A proof for this asumption? The Crystal Report using this SQL Server view ALWAYS runs fine.

SQL Server or Crystal Reports or Windows, that are the questions.  Please give us a hand on that one.
 
Thanks

Francis
0
 
RiteshShahCommented:
have you tried my last code I gave you???? isn't it working properly?
0
 
QlemoC++ DeveloperCommented:
AlainSt-Pierre,

I reread your posting, and see that I might have gone the wrong way. This was caused by your accept on the wrong answer before, which followed exactly that path.

The real issue is that reports run without issue, but the append does not work.

To run reports on changing and expanding tables is really normal, as James told you already. I urge you to start a profiler, filter on the report session(s) and locks only, and start the report with nolock statements (you can instead use set tranaction isolation level read uncommitted, nolock is not needed then). You will see if MSSQL has locks in mind - sometimes it has even with dirty reads, if there are a lot of data to read, with many tables involved.
If so, it could help to implement appropriate indexes for the on fields, and maybe with the select fields.
You even could use Materialized Views (now called Indexed Views), that are views with indexes created on them. They build kind of data snapshot of the real world data, and can be "refreshed" manually or automatically.

The target of the indexes is to reduce execution time by having all data "at hand" in indexes which might be much smaller than the tables they belong to.

The target of Indexed Views is to have a data copy for use in report without consequences on the related tables.

0
 
AlainSt-PierreAuthor Commented:
Hi,

I'll be glad to try that first thing tomorrow.

However, could you give incations on how to "set the transaction isolation level read uncommitted".  

Meanwhile, I'll use the Profiler and see if there any data commitment with my current Nolock Instructions.

Thanks very much!
0
 
QlemoC++ DeveloperCommented:
The set transaction is an own command in T-SQL. It is issued before the SQL command, and is valid for the session. Example code is:

set transaction isolation level read uncommtted;
select ...

I do not know if it is feasible in CR to use such a set command in the report SQL. If not, the NOLOCK approach has to be used.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Alain, if you having locks in your query it's because it's taking so long to return values. Only NOLOCK can't solve your problem. You need to improve your query speed and for that nothing is better than indexes.
Take that query and put it in Index Tuning Wizard and let it analyze and wait for sugestions.

Good luck.
0
 
RiteshShahCommented:
I am 100% agree with above opinion, NOLOCK could be just workaround but final solution will be proper indexes. Worth to do it at all.
0
 
James0628Commented:
The reason I brought up the other question was:

 a) So that anyone looking at this question could see what had already been discussed.

 b) To just point out that it seemed to be a duplicate question, because (I think) duplicate questions are generally frowned upon here.

 c) I really just didn't get why you closed one question, only to ask it again.  :-)


 As for the nolock stuff, I can't help with that, because I've never needed it.  You said that the problem report takes 3 minutes to run.  It's not at all unusual for reports here to take that long or longer - Sometimes much, much, longer (admittedly, the "much, much, longer" reports were mostly "malfunctioning" reports, but the point is, they were accessing the db all that time).  To the best of my knowledge, we've never had a problem like the one you described.

 So, I honestly don't know why/how a CR report would be interfering with a table update.

 James
0
 
AlainSt-PierreAuthor Commented:
RiteshShah,

I did the try the WITH (NOLOCK) code you mentionned, and the problem persists though the report is running faster.

After using SQL Profiler, I could see that the Read Commited instruction came from Crystal Reports (see attached printscreen)

Arguments
READ COMMITTED

Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

READ UNCOMMITTED

Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.

Now the question is as simple as that: how do I get Crystal Report to generate SQL a READ UNCOMMITTED instruction.  James, any idea on this?

Thank you all
Crystal-instruction.bmp
0
 
James0628Commented:
If you can come up with a query that includes the options that you need, you could create a SQL stored procedure or view, or a CR "command" (a query stored in CR, instead of on the server).  That way you can structure the query however you want.

 James
0

Featured Post

Technology Partners: 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!

  • 6
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now