|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
OLE Automation Failure Executing DTS via stored procedureOur IT group has been running a number of DTS packages via stored
procedures (the sp_OA... procedures) for some time. Recently, we've started having a lot of problems with the OLE automation procedures failing. The sp_OACreate procedure seems to work all the time, but the sp_OAMethod @hPkg, 'LoadFromSQLServer',... method ALWAYS fail. It will be running fine for a couple of days, and then suddenly, everything starts failing. So far, the only way we've been able to solve the problem is by rebooting the server. Does anyone know if there is a service that is responsible for OLE automation that can be stopped and restarted, rather than having to shutdown the entire server? The server is our OLTP database server, so shutting down and restarting in the middle of the day is a bit problematic. C. Pegasus40218 (Pegasus40***@yahoo.com) writes:
Show quote > Our IT group has been running a number of DTS packages via stored If you run the OLE components in-process, rebooting SQL Server is the > procedures (the sp_OA... procedures) for some time. Recently, we've > started having a lot of problems with the OLE automation procedures > failing. The sp_OACreate procedure seems to work all the time, but the > sp_OAMethod @hPkg, 'LoadFromSQLServer',... method ALWAYS fail. It will > be running fine for a couple of days, and then suddenly, everything > starts failing. So far, the only way we've been able to solve the > problem is by rebooting the server. > > Does anyone know if there is a service that is responsible for OLE > automation that can be stopped and restarted, rather than having to > shutdown the entire server? The server is our OLTP database server, so > shutting down and restarting in the middle of the day is a bit > problematic. only way to cure them, if the components have problems with memory leaks etc. If you run them out-of-process, you should be able to get by unloading the DLL, I think. (I'm a little foggy on exactly how you do that; I'm not a Windows programmer.) You can control whether you run in or out process with the @context parameter to sp_OAcreate. Check Books Online for details. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Erland Sommarskog wrote:
Show quote > Pegasus40218 (Pegasus40***@yahoo.com) writes: =====> > Our IT group has been running a number of DTS packages via stored > > procedures (the sp_OA... procedures) for some time. Recently, we've > > started having a lot of problems with the OLE automation procedures > > failing. The sp_OACreate procedure seems to work all the time, but the > > sp_OAMethod @hPkg, 'LoadFromSQLServer',... method ALWAYS fail. It will > > be running fine for a couple of days, and then suddenly, everything > > starts failing. So far, the only way we've been able to solve the > > problem is by rebooting the server. > > > > Does anyone know if there is a service that is responsible for OLE > > automation that can be stopped and restarted, rather than having to > > shutdown the entire server? The server is our OLTP database server, so > > shutting down and restarting in the middle of the day is a bit > > problematic. > > If you run the OLE components in-process, rebooting SQL Server is the > only way to cure them, if the components have problems with memory > leaks etc. > > If you run them out-of-process, you should be able to get by unloading > the DLL, I think. (I'm a little foggy on exactly how you do that; I'm > not a Windows programmer.) > > You can control whether you run in or out process with the @context > parameter to sp_OAcreate. Check Books Online for details. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thanks for the references. I've started going over the stored procedures, and it looks like the original programmers failed to explicitly destroy the objects once they were done with them...I guess the assumption was that when they were somehow associated with the connection/session, and once the session ended, they would be removed from memory. Pegasus40218 wrote:
Show quote > Erland Sommarskog wrote: OK...Now I'm running into a different problem. Apparently, the> > Pegasus40218 (Pegasus40***@yahoo.com) writes: > > > Our IT group has been running a number of DTS packages via stored > > > procedures (the sp_OA... procedures) for some time. Recently, we've > > > started having a lot of problems with the OLE automation procedures > > > failing. The sp_OACreate procedure seems to work all the time, but the > > > sp_OAMethod @hPkg, 'LoadFromSQLServer',... method ALWAYS fail. It will > > > be running fine for a couple of days, and then suddenly, everything > > > starts failing. So far, the only way we've been able to solve the > > > problem is by rebooting the server. > > > > > > Does anyone know if there is a service that is responsible for OLE > > > automation that can be stopped and restarted, rather than having to > > > shutdown the entire server? The server is our OLTP database server, so > > > shutting down and restarting in the middle of the day is a bit > > > problematic. > > > > If you run the OLE components in-process, rebooting SQL Server is the > > only way to cure them, if the components have problems with memory > > leaks etc. > > > > If you run them out-of-process, you should be able to get by unloading > > the DLL, I think. (I'm a little foggy on exactly how you do that; I'm > > not a Windows programmer.) > > > > You can control whether you run in or out process with the @context > > parameter to sp_OAcreate. Check Books Online for details. > > > > > > -- > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > > > Books Online for SQL Server 2005 at > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > > Books Online for SQL Server 2000 at > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > ===== > Thanks for the references. I've started going over the stored > procedures, and it looks like the original programmers failed to > explicitly destroy the objects once they were done with them...I guess > the assumption was that when they were somehow associated with the > connection/session, and once the session ended, they would be removed > from memory. ===== security context under which the procedure is run must be a member of the sysadmin server role to run the sp_OADestroy procedure. The procedures I'm dealing with are user-initiated data transfers that run under a number of different user contexts. I probably could grant execute permissions to that procedure in Master, but I'm reluctant to do so. I've gone ahead and granted execute permissions for the time being, but I'm hoping to find a more secure solution. Any thoughts? Pegasus40218 (Pegasus40***@yahoo.com) writes:
> OK...Now I'm running into a different problem. Apparently, the You are right that granting public access to these procedures is risky. > security context under which the procedure is run must be a member of > the sysadmin server role to run the sp_OADestroy procedure. The > procedures I'm dealing with are user-initiated data transfers that run > under a number of different user contexts. I probably could grant > execute permissions to that procedure in Master, but I'm reluctant to > do so. I've gone ahead and granted execute permissions for the time > being, but I'm hoping to find a more secure solution. Any thoughts? sp_OADestroy is probably the least risky, but I assume that someone before you already had granted access to sp_OAmethod. With access to that procedure, a user can create whichever OLE object he wants, and it will run with the privileges of the service account for SQL Server. A possible way to address this, is to make sa the owner of the database, and then enable cross-db chaining with sp_configure. But there are security issues with this as well, if there are multiple database owners on the server. Yet a possibility is write a stored procedure in the master database that performs all the sp_OA stuff, and then grant access to that procedure. This is possibly the best from a security perspective, but having application code in master is not palatable, and it's more difficult to maintain. SQL 2005 offers far better options in this area, including the possibility to write stored procedures in the CLR, waiving the requirement to use sp_OAmethod & friends. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Have you made certain that all objects are destroyed after use? Even the
implicitly created objects (such as collections etc.) need to be explicitly destroyed to prevent memory leaks. ML --- http://milambda.blogspot.com/  |
|||||||||||||||||||||||
Other interesting topics