DCOM error sometimes, when try to execute Stored procedure

[Cowboy (Gregory A. Beamer)]

Most likely the account used for one of the SQL Server services does not
have DCOM rights. We had a similar problem with a SQL Server job.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
Author: ADO.NET and XML: ASP.NET on the Edge

****************************************************************************
****
Think outside the box!
****************************************************************************
****
"Gabor" <prof...@axelero.hu> wrote in message
news:eqB5zuziCHA.2672@tkmsftngp09...
> Hi everybody,
>
> Sometimes, when my ASP.net app. try to execute an sp, that uses the
> sp_xml_preparedocument, I give the following error:
>
> Error while executing a batch 'A cmd at line 0;SQL error 0: "The server
> could not load DCOM"
>
> Restarting the computer sometimes eliminates the problem, but it occurs
> again.
>
> Does anybody experienced thi problem?
>
> Thanks in advance
>
> Gabor
>
>
>

[Richard T. Edwards]

When you first create an object, its runing under the I_USER account in asp.
But once its running as a COM object, it runs under a different security
account -- like the system account. So, when you query the interface for the
second call, to use the method or property -- in this case, setting the
properties for the command object to make your SP call, you get an access
denied because that object's not accessible and DCOM raises an error.

[Gabor]

Thanks Richard, Gregory for the quick answer.

But why the app. works well after restarting the computer? In my case every
components are in the same developer's machine. Moreover the same problem
occurs, if I try to execute the sp from within query analyzer under
administrator user account.

The problem occurs only for sp that uses sp_xml_preparedocument, which is
in a transaction with another insert statement.

What have I do?

Thanks in advance,

Gabor

"Richard T. Edwards" <r.t.e...@attbi.com> wrote in message
news:MnzA9.19774$ME6.11566@rwcrnsc53...

[Li-Yan Zhang [MS]]

If you are running the job directly on the SQL Server check the following:

1. COM+ event system service is started.
2. Click Start->Run and type dcomcnfg
3. Click the Default Properties tab and make sure "enable DCOM on this
computer"is checked.

Thanks,
Li-Yan Zhang
VS.NET, Visual C++
Microsoft

This posting is provided "AS IS" with no warranties, and confers no rights.
Got .Net? http://www.gotdotnet.com

[Li-Yan Zhang [MS]]

Can you post related code snippet here? I have the interest to test locally.

[Gabor]

Thanks for the answer, here is the sp code:

--------------------------------------------------------------

-- Insert datas into the templates and templateFields tables

--------------------------------------------------------------

ALTER PROC spAddNewSablon

(

@treeID int,

@source varchar(50),

@desc varchar(100),

@savePath varchar(50),

@macro varchar(20),

@autoMacro int,

@ToWord int,

@fields ntext

)

AS

set NOCOUNT ON

set XACT_ABORT on

declare @error int

declare @iratID int

declare @hDoc int

------------------------------------------------------------------

BEGIN TRANSACTION

------------------------------------------------------------------

-----------------------------------------------

-- DELETING THE ROW WITH THE TREEID

-----------------------------------------------

DELETE FROM templates

WHERE (treeID = @treeID)

-----------------------------------------------

-- INSERT DATAS INTO THE TEMPLATES TBL

-----------------------------------------------

INSERT INTO templates

(treeID, desc, Path, SavePath, Macro, AutoMacro, WordreLep)

VALUES

(@treeID,@source,@desc,@savePath,@macro,@autoMacro,@ToWord)

if @@rowcount=0

select @error=1

else

select @error=@@error

-------------------------------------------------

-- GET DETAIL'S DATAS FROM THE XML

-------------------------------------------------

if @error=0

EXEC sp_xml_preparedocument @hDoc OUTPUT, @fields

-----------------------------------------------

-- INSERT DATAS INTO TEMPLATEFIELDS

-----------------------------------------------

INSERT INTO templateFields

(treeID, fieldNames, intoTbl, db, sql, otherFields)

SELECT @treeID, fieldNames, intoTbl,db,sql, otherFields

FROM OPENXML(@hDoc, '/NewDataSet/Table1')

WITH (fieldNames varchar(40) 'fieldNames',

intoTbl bit 'intoTables',

db varchar(50) 'db',

sql varchar(2000) 'sql',

otherFields varchar(1000) 'otherFields')

if @@rowcount=0

select @error=1

else

select @error=@@error

if @error=0

------------------------------------------------------------------

COMMIT TRANSACTION

------------------------------------------------------------------

EXEC sp_xml_removedocument @hDoc

"Li-Yan Zhang [MS]" <ly...@online.microsoft.com> wrote in message
news:a2Vnxh5iCHA.1676@cpmsftngxa08...

[Li-Yan Zhang [MS]]

Can you try the following T-SQL script snippet and let me know the result?

declare @idoc int
declare @doc varchar(1000)
set @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity')

[Richard T. Edwards]

Gabor:

Run regedt32, goto HKEY_LOCAL_MACHINE 's window Click on software inside
that window and the ODBC. Then go up to the menu, to security and then
permissions. See if the I_USER and the I_WAM accounts are there. If they
aren't, add them and assign the appropriate permissions. Generally, read
access will do it.

HTH

.
"Gabor" <prof...@axelero.hu> wrote in message

news:OsJtFI#iCHA.2748@tkmsftngp11...
> Hi,
>
> The script works well executing from within query analyzer.
>
> Gabor

>
> "Li-Yan Zhang [MS]" <ly...@online.microsoft.com> wrote in message

> news:U6BzZK8iCHA.1964@cpmsftngxa06...

[Gabor]

Hi,

The script works well executing from within query analyzer.

Gabor

"Li-Yan Zhang [MS]" <ly...@online.microsoft.com> wrote in message
news:U6BzZK8iCHA.1964@cpmsftngxa06...

[Gabor]

Thank You for the replay,

Unortunately I periodicaly get the same error.

At the time of error, if I execute the batch of the Mr. Zhang from within
query analyzer, I get the following error:

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

I can't imagine, why this error I espect sometimes, but don't other times.
The machine configuration is the same.

Does any suggestion?

"Richard T. Edwards" <r.t.e...@attbi.com> wrote in message

news:LwPA9.958$Xo.273@rwcrnsc53...

[Li-Yan Zhang [MS]]

I have been researching on this issue recently. Unfortunately, I still
couldn't reproduce the problem at my side. I suggest you to open a web
response incident for PSS support professionals to further follow up on
this problem.

I am sorry for the inconvenience.