December 12, 2004 at 8:12 pm
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!
December 13, 2004 at 12:37 am
IIRC there were memory problems with these sp_OA* procedures, but that should have ben fixed. I think, to say you're on the right track or completely off, it would help to see this procedure. Also, what version of Word are you using and what service pack level?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 13, 2004 at 6:01 am
I've come across a similar problem myself. It was something to do with the sp_OA CreateObject procedure, but we were having problems with a self-created DLL that was running in the context of the SQLServer.EXE process.
We then compiled the DLL as an EXE file and registered it with the -REGISTER option. Our problems then vanished.
Might help. Good luck.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply