CODEWITHSUNDEEP
sql-server
Chad
Chad

Reputation: 24699

The server could not load DCOM error

I have a local SQL 2000 database. My Winform app suddenly died when trying to all a stored proc that has not changed. Here's teh error message:

Server: Msg 7404, Level 16, State 2, Procedure RecordCurrentUser2, Line 45 The server could not load DCOM.

Then, I noticed that when I try to alter the SP using Query Analyzer, I would get the same error when I tried to execute the ALTER command. I trimmed down the stored procedure, leaving the core code that causes the error when executed:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER PROCEDURE dbo.RecordCurrentUser2
(
 @xmlUser           varchar(8000)
)
AS 

BEGIN

    DECLARE @Now                datetime 
    DECLARE @ChangedRecordCount int

    --Normally this is commented out, but for testing, we'll hard code a param value:
    SET @xmlUser = '<User>
  <User>     
    <EmailId>[email protected]</EmailId>
    <LastName>Chad</LastName>
    <FirstName>Smith</FirstName>
    <Initials />
    <DomainName>NA</DomainName>
    <Account>SMITH</Account>
    <TelephoneNumber>179-1458</TelephoneNumber>
    <PeoplesoftId>031X45</PeoplesoftId>
    <Department>Order to Collect BI Reporting</Department>
    <StreetAddress>58 Hill Road</StreetAddress>
    <PostalCode>06333</PostalCode>
    <Location>MAIN</Location>
    <State>AK</State>
    <Country>United States</Country>
  </User>
</User>'


    DECLARE @DocHandle      int

    SET @Now = GETUTCDATE()

     -- Create an internal representation of the XML document.

    EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xmlUser

     --Put the contents of the XML string into a temp table

    SELECT 
          EmailId          
         ,LastName           
         ,FirstName       
         ,Initials As Initials
         ,DomainName        
         ,Account           
         ,TelephoneNumber    
         ,PeoplesoftId       
         ,Department        
         ,StreetAddress      
         ,PostalCode         
         ,Location           
         ,State              
         ,Country            
         ,OtherTelephone
         ,NonUnisysDomainName
         ,NonUnisysAccount
    INTO
        #TempItems
    FROM
        OPENXML (@DocHandle, '/User/User', 2)
            WITH 
            (
             EmailId                nvarchar(80)
            ,LastName               nvarchar(50)
            ,FirstName              nvarchar(50)
            ,Initials               nvarchar(30)  
            ,DomainName             nvarchar(10)
            ,Account                nvarchar(10)
            ,TelephoneNumber        nvarchar(50)
            ,PeoplesoftId           nvarchar(50)
            ,Department             nvarchar(50)
            ,StreetAddress          nvarchar(50)
            ,PostalCode             nvarchar(50)
            ,Location               nvarchar(80)
            ,State                  char(2)
            ,Country                nvarchar(30)
            ,OtherTelephone         nvarchar(50)
            ,NonUnisysDomainName    nvarchar(10)
            ,NonUnisysAccount       nvarchar(10)
            )

END

The following service are running in my machine, although they may not be relevant: Distributed Transaction Coordinator (note-running a local db) COM++

I didn't intentional install anything recently, although I recall seeing an MS patch get automatically applied by company policy, not sure what it was or how to find out.

How can I resolve this error?

Upvotes: 0

Views: 4697

Answers (2)

gbn
gbn

Reputation: 432551

Where is your sp_xml_removedocument call to stop memory leaks?

A parsed document is stored in the internal cache of SQL Server. The MSXML parser (Msxmlsql.dll) uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory.

Edit: applies to SQL Server 2000 to 2008 at least

Upvotes: 1

Chad
Chad

Reputation: 24699

I was already running SQL Server under an Admin account, not the Local System Account, but my personal domain account, which is an administrator on the box. There is no change here.

I did reboot however and the problem went away. The patch I mention must have required a reboot but did not indicate so. Surprisingly, the patch installed with no warning and w/o telling me a reboot was required after it was done installing.

One up vote for the reasonable suggestion to try an admin acct. Thanks! And thanks to Rocket Surgeon, too.

Upvotes: 0

Related Questions