SQL Server Performance Forum – Threads Archive
Extended SP problem – driving me nuts!
Hi This has been driving me nuts for months now, I just hope somebody on here can point me in the right direction. Recently at work we migrated a couple of DBs from SQL 7.0 to SQL 2000. The migration went smoothly (a couple of minor issues which were quickly resolved) apart from one problem. Our batch procedure, which is run after all user activity has ceased each day, fails at the same point during each run. The bizarre thing is, it runs perfectly on one of the DBs, but not on the other! Errors are logged during the process, and checking through the logs it appears that the batch process is failing when an Extended SP is called that populates Word documents with data (a glorified mail merge). On the DB that works, this process goes throguh without a hitch (we sometimes get errors elsewhere that are usually a result of masses of .tmp files) but on the one that is failing, it fails EVERY SINGLE NIGHT at the same place. I know its not an issue with the batch run code itself, as after a few retries, the procedure eventually executes and the batch runs to completion. My initial response was to chuck memory at the server but this had no effect. We’ve upgraded the server to a new box – higher spec than the one which the functioning DB runs on – this also had no effect. I am now stuck – although I have a few ideas I can’t ever seem to get anywhere with them and was wondering whether anyone on here had any thoughts? My reasoning so far: 1) It has to be an issue with Word. If there were a problem with the Batch procedure itself the run would NEVER complete – and, as stated before, eventually it does. 2) I know there is a theoretical limit to the number of times that SP_OA ExecuteMethod can be called in a loop, and was at first convinced that this was the problem, but analysing the code reveals that it is not looped anywhere within the procedure 3) I lean towards it being something to do with the manner in which Word runs when automated (poorly!), perhaps its an issue with Word hanging waiting for user input which can never come because its being run automated? Does anyone on here think that I am on the right track? Or has anyone any other suggestions that might help me out? Thanks, in advance ZBLB PS: MS was about as much use as windscreen wipers on a submarine!May check MDAC version, collation settings, windows locale settings on the server for this behaviour. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>