| Author |
Topic |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2002-06-07 : 16:12:43
|
| SQL Server 7, sp 3 or 4 . . .A developer writes an in-process com object (.dll) that he needs me to instantiate and run a method from.If I use sp_OACreate then that object will reside in the memory space of SQL Server.Is the same statement true if I instantiate the object as a ActiveX Scripting Task of a DTS package....or what about as an ActiveX step of a job?And what happens if the .dll crashes. Does sp_OADestory release it? Do I need to run sp_OAStop? Do I need to restart the SQL Server Service?<O> |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-07 : 16:22:08
|
| As far as DTS, I don't think it would necessarily run in the SQL Server process space, because you can create a DTS package on a machine wholly separate from the data sources it uses. For example, data is transferred from Server A to Server B, but the DTS package actually runs on Server C. In that case it would have to instantiate on Server C. Furthermore, you can run a DTS package using dtsrun from the command line, and I think you can do it without even installing SQL Server on Server C (dtsrun and some DLL's at least) Certainly it can be done without the MSSQLServer service running.If it was part of an ActiveX job task, I would think it runs in the SQL Server or SQL Agent process. If the DLL crashes, the only way to be certain that it's released is to reboot the machine. I'm pretty sure that stopping and starting SQL Server will do it too, but if it's a super buggy DLL it may not. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-10 : 10:18:35
|
| I'd be gitty if someone could tell me that sp_OAStop at least should release a haywire .dll from sql's memory space...<O> |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-10 : 10:42:25
|
| What if that DLL overwrote parts of SQL Server's memory? Then the whole process needs to be shitcanned and restarted. That's why sp_OAStop is only useful if the DLL is well-behaved. The only thing you can guarantee is that that DLL won't crash other processes on the machine.IMHO if you have these reservations about the ActiveX controls, DON'T USE THEM. Why exactly do you think they might cause problems? |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-10 : 10:51:02
|
| Cause the developers first version crapped out and I had to reboot clients dev box...Trust me Rob, you would bust a nut if I told you what this whole process is doing. Lets just say I will be instantiating this object and calling a method before and after the job runs that truncates and reloads from a file, a table used by a live web page . . . You see once cold fusion gets blocked and hung up on more than 6 connections, it crashes....blah,cough,blah....So I guess if I wanna do it right, I need to create 2 single task dts packages, each one spinning up or down the object and dtsrun the pkgs before and after my job....chaos begets chaos<O> |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-10 : 11:00:27
|
Page I think all your problems would instantly be solved by (nolock) 'ing every single query you've got ...  setBasedIsTheTruepath<O> |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-10 : 11:05:01
|
| Would that fix all the problems? Or just the exponential ones?<O> |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-10 : 11:11:51
|
quote:
Trust me Rob, you would bust a nut if I told you what this whole process is doing. Lets just say I will be instantiating this object and calling a method before and after the job runs that truncates and reloads from a file, a table used by a live web page . . . You see once cold fusion gets blocked and hung up on more than 6 connections, it crashes....blah,cough,blah....
Ohhhhhhhhhhhhh, man, you just brought back some SWEET memories! I did something like this once. I had to get UPS tracking numbers from a web site that used CF. It was basically an HTML screen-scrape, and then took that and dumped it into a SQL Server table, which then updated an Access database with a DTS transform. It worked, but nothing involved custom controls or DLL's, just ActiveX tasks and vanilla ADO and XMLHTTP object calls.It CAN be done, but you might want to consider a suggestion that a custom DLL may not be the best way to do it.Email me if you wanna chat more about it. I don't have the code anymore, but I have the idea still in my head. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-10 : 11:19:46
|
quote:
...but you might want to consider a suggestion that a custom DLL may not be the best way to do it....
as a contractor I am constantly balancing my desire to do things right the first time with the knowledge that I get paid by the hour no matter how many times I re-work the same process...over and over and over...I made the suggest...infact, I don't believe truncating a several hundred meg mission-critical-to-a-website table is the best idea. I've advocated over and over a insert (from staging) where not exists, update where changed, delete where not exists (in staging table) approach for quite some time...one of these iterations through, the client will decide to use my (brilliant :)) idea...until then I'll keep handing them rolls of duct tape, cause its all the same dollar to me....Thanks for the offer though . . .<O>Edited by - Page47 on 06/10/2002 11:20:18 |
|
|
|
|
sp_OACreate and in-process com object