| Author |
Topic |
|
Pace
Constraint Violating Yak Guru
264 Posts |
 Posted - 2007-02-21 : 07:32:29
|
Hello experts, I have been using my bespoke access front end with sql server2000 in the back no problem for quite some time now. Recently our company had sage mms installed on the same sql box as our bespoke system. Now in order to get the sage application to run, the engineer installed sp3 on our sql server. Ever since this, almost all my recordsets (DAO and ADO) bomb out on Update... I have even tried to write commands as stored procedures, which do work in sql, however from access they fail, everytime. This only seems to have happened since SP3/ I was wondering, does sp3 change things dramatically? I just really dont know what to say or suggest to start trying to work this out. Thanks for your time.Update;Also I notice certain update and insert statements work fine, I was wondering if its something to do with table locking? Im not entirely sure how this bit works, but I have included screens. The first screen shows what happens when the second screen's code is used, and the last screen shows the statement that doesnt seem to be getting through to the box. I was just wondering if these helped identify my issue. Thanks again. Barry   and another example; timeout expired on .UPDATE   "Impossible is Nothing"  |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-02-21 : 09:56:47
|
| What's the connection string for "currentdb"? It might matter if this object is opened server-side or client-side. |
 |
|
|
Pace
Constraint Violating Yak Guru
264 Posts |
Posted - 2007-02-21 : 10:04:58
|
| Thanks for the reply! Im not sure I understand? Is that something I can obtain?I have linked SQL tables from the server. I also have a connection string named "cnnSQL" (to the same server) I call on from time to time when executing a proc or something similar. Its always called from the client."Impossible is Nothing" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-21 : 11:22:31
|
| There were some changes in SP4 that could have impacted your query, but none that I remember in SP3.If you run the query in Query Analyser how long does it take?SELECT Code, Company, Action, ActionDate FROM customeris going to select ALL rows in the table. Are there a lot of them? Is the number growing? Maybe the number of records has just got too large - in which case you will need a WHERE clause to restrict how many you get.Kristen |
|
|
|
Pace
Constraint Violating Yak Guru
264 Posts |
Posted - 2007-02-21 : 11:33:24
|
| Hi Kristen, thanks for the reply, ive been examining the patch and from what I can see, it is SP4.There is a WHERE cluase in there, I pass it the quote no, thats strange as to why it doesnt show the where in the locks query, yet it does get passed. One thing, im not entirely sure this guy knows what he was doing. He may not have shut the services down before applying the update. Ive told all users they cant have the database tomorrow until 9Am, im planning to do a nice fresh install of the service pack. Other than that though can you see why I would get this? Thanks for your assistance."Impossible is Nothing" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-21 : 12:30:20
|
| "He may not have shut the services down before applying the update"The service pack itself should have done that.I believe there can be a performance issue with SP4 if you compare values of different datatypes, and SQL Server has to make an implicit conversion.For example if you sayWHERE MyNumericColumn = '123'or WHERE MyIntegerColumn = 123.456(same thing in the "ON" or "AND" phrase of a JOIN)Might be worth checking if you have any of those. (I think there is a Compatibility Flag you can set to disable this change, but I'm not sure if it is at the Server Level or the Database Level - so it might impact the new Sage stuff too ...There have been a few threads about SP4 on SQL Team, if you want to have a bit of a Google. Can;t remember which ones are linked on this thread, but they may help:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Concerns%20about%20SQL%202000%20SP4Kristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-02-21 : 12:30:49
|
| I would update the statistics on tables/indexes with the sp_updatestats stored procedure. You might also consider rebuilding your indexes.Did you actually go to SP4 or did you go to SP3? SELECT @@VERSION in query analyzer will tell you the build number. Build 760 is SP3, build 2039 is SP4.-ec |
|
|
|
Pace
Constraint Violating Yak Guru
264 Posts |
Posted - 2007-02-22 : 03:43:05
|
| Ok finally I am getting somewhere. What happens in all this is there is a form where a user can pick a quote to work with. This creates the initial lock. Just after they have selected a few items from the quote, after ok, more routines fire which amend this quote. Now. If I load everything up, but before I press OK on the editable routine. If I go in and kill the select process, it removes the lock and the statement works perfectly. So... I need to stop this select once the user has grabbed their quote. Do you know how I can do this in code? I appreciate that this isnt really a SQL problem anymore. I just think the new patch is making things a bit tighter in sql, which in all fairness, is no bad thing. Anyone have any ideas? Thanks for all your help"Impossible is Nothing" |
|
|
|
Pace
Constraint Violating Yak Guru
264 Posts |
Posted - 2007-02-22 : 04:03:00
|
| errMsg = 5 ' Update Quote to show Job Number bSterling = Me!Sterling If Not (IsNull(Choose_Quote)) Then bFromQuote = True strQuoteNo = Me!Choose_Quote strCode = Left(Choose_Quote, 7) & "*" strSQL = "SELECT * FROM Quotes WHERE [Quote No] = '" & strQuoteNo & "'" Set rst = CurrentDb.OpenRecordset((strSQL), dbOpenDynaset, [dbSeeChanges]) ' Check exists and don't overwrite if job has already been completed because ' the user might just be using this particular quote to get the items. If rst.EOF = False Then strOriginator = rst!Originator & ""' rst.Close' New SP, same issue' Dim cmd As New ADODB.Command' cmd.ActiveConnection = cnnSQL' cmd.CommandType = adCmdText' cmd.CommandText = "exec stp_JobFromQuote " & "'" & strQuoteNo & "'" & "'" & nextJob & "'" & "'" & strUserInitials & "'" & "'" & strOriginator & "'"' cmd.Execute End If If IsNull(rst![Job No]) Then rst.Edit rst![Job No] = nextJob rst![Killed] = "A" rst!KilledBy = strUserInitials & "" rst!KillDate = Date rst![Next Followup] = Null rst.Update End If End If rst.Close"Impossible is Nothing" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-22 : 04:59:43
|
"This creates the initial lock"Can you clarify:Does this mean that you are holding a lock whilst the user has a form to work on? (And whilst that lock is being held they could "go to lunch" - or even "fly off on holiday for a week"  )?If so you are going to need to program that differently ... (I have some ideas of "how" if that is indeed the case)Kristen |
|
|
|
Pace
Constraint Violating Yak Guru
264 Posts |
Posted - 2007-02-22 : 06:14:30
|
| Kristen! Sorry when I say craetes the lock. (im not a expert here) if I look in Current Activity, you can see the Object/Locks node? thats where I was seeing a nasty red one = ) Thanks for all your help! I finally got round it this morning. Its much easier to see whats happening when others are off the system. Using a mixture of tools I worked out that on the job form, when someone chooses a quote, various routines happen to get the information. Now digging into these routines, the connections weren't properly being closed. If I debugged to the point where my update should work, then killed the select process, the code ran as normal. So what was left to do was to go in and get those connections closed. Glad we got there and thank you all for your help. Im quite pleased in a way as 1) I saw other areas ive never been in before and 2) most importantly, I understand why the issue was in the first place. ooo the sense of satisfaction. Thanks again all! very happy, Pace"Impossible is Nothing" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-22 : 06:25:14
|
| "go in and get those connections closed"That's a good one to get solved. Bit hard to find in normal running ...Kristen |
|
|
|
|
SP3 woes? - UPDATED with pics