December 18, 2009 at 3:16 am
Hi,
We have hosted a sql 2000 database on sql 2005 server with compatibility level as 80. One of the procedures in the database calls a dll object using the sp_OACreate xtended procedure.
This dll has been registered on the server and its function is to download the files to the local system from the FTP site.
When this procedure is scheduled it sometimes runs fine and but sometimes it hangs the SQL server and stops the services.
On researching on this issue, we found that it is better to run the proc out-of-context bny specifying the parameter 4 whilc calling the proc sp_OACreate.
After this change although the server hang problem got solved but the dll stopped working, i.e. it is no longer downloading the files from th FTP site and gives the message "Class not registered odsole extended stored procedure".
On further analysis we found a MS link on how to to call a dll using out-of-process parameter
http://support.microsoft.com/kb/198891
But we hadn't had any luck solving this.
I have really no idea how to resolve this please let me know if anyone has faced the same before.
Any help would be really appreciated.
Note: My server has both SQL Server 2000 and SQL Server 2005 instances installed and running.
Thanks
Vibhu
December 24, 2009 at 3:02 pm
Not familiar with this particular problem, however, my first guess would be that the FTP connection is getting "hosed" (timed-out, interrupted, disconnected, etc.) and because of the highly synchronous nature of the sp_OA COM interface, it isn't recovering appropriately or getting cleared. My suggestion would be to just try again.
If it seems to always fail/hang from SQL Server after it fails once, but seems to work find from DOS or otherwise outside of SQL Server (but still from the same Server), then try re-booting. Yes, I know that sucks, but try it at least once for diagnostic reasons.
If that clears it up, then you are probably S-O-L with respect to sp_OACreate, you will either have to reboot every time it happens, or re-write it in either SQL CLR, or with Service Broker and the External Activator.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply