DCOM error sometimes, when try to execute Stored procedure

DCOM error sometimes, when try to execute Stored procedure

Post by Gregory A. Beame » Fri, 15 Nov 2002 03:21:18



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!
****************************************************************************
****

Quote:> 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

 
 
 

DCOM error sometimes, when try to execute Stored procedure

Post by Richard T. Edward » Fri, 15 Nov 2002 06:21:48


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.


Quote:> 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


 
 
 

DCOM error sometimes, when try to execute Stored procedure

Post by Gabo » Fri, 15 Nov 2002 19:24:38


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



> 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.



> > 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

 
 
 

DCOM error sometimes, when try to execute Stored procedure

Post by Li-Yan Zhang [M » Fri, 15 Nov 2002 13:44:30


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

 
 
 

DCOM error sometimes, when try to execute Stored procedure

Post by Li-Yan Zhang [M » Fri, 15 Nov 2002 13:45:18


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

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

 
 
 

DCOM error sometimes, when try to execute Stored procedure

Post by Gabo » Fri, 15 Nov 2002 23:59:18


Thanks for the answer, here is the sp code:

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

-- Insert datas into the templates and templateFields tables

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

ALTER PROC spAddNewSablon

(









)

AS

set NOCOUNT ON

set XACT_ABORT on




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

BEGIN TRANSACTION

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

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

-- DELETING THE ROW WITH THE TREEID

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

DELETE FROM templates


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

-- INSERT DATAS INTO THE TEMPLATES TBL

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

INSERT INTO templates

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

VALUES



else


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

-- GET DETAIL'S DATAS FROM THE XML

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



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

-- INSERT DATAS INTO TEMPLATEFIELDS

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

INSERT INTO templateFields

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



WITH (fieldNames varchar(40) 'fieldNames',

      intoTbl bit 'intoTables',

      db varchar(50) 'db',

      sql varchar(2000) 'sql',

      otherFields varchar(1000) 'otherFields')



else



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

COMMIT TRANSACTION

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




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

> 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

 
 
 

DCOM error sometimes, when try to execute Stored procedure

Post by Li-Yan Zhang [M » Fri, 15 Nov 2002 18:47:00


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




<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.

-- SELECT stmt using OPENXML rowset provider
SELECT *






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

 
 
 

DCOM error sometimes, when try to execute Stored procedure

Post by Richard T. Edward » Sat, 16 Nov 2002 00:43:39


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
.

> Hi,

> The script works well executing from within query analyzer.

> Gabor



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




> > <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.

> > -- SELECT stmt using OPENXML rowset provider
> > SELECT *






> > 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

 
 
 

DCOM error sometimes, when try to execute Stored procedure

Post by Gabo » Sat, 16 Nov 2002 07:32:43


Hi,

The script works well executing from within query analyzer.

Gabor



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




> <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.

> -- SELECT stmt using OPENXML rowset provider
> SELECT *






> 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

 
 
 

DCOM error sometimes, when try to execute Stored procedure

Post by Gabo » Tue, 19 Nov 2002 14:51:47


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?



> 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
> .


> > Hi,

> > The script works well executing from within query analyzer.

> > Gabor



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




> > > <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.

> > > -- SELECT stmt using OPENXML rowset provider
> > > SELECT *






> > > 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

 
 
 

DCOM error sometimes, when try to execute Stored procedure

Post by Li-Yan Zhang [M » Sat, 30 Nov 2002 12:22:14


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.
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

 
 
 

1. error while executing store procedure command

hi
i'm getting an error message when trying to execute a store procedure.
i'm using an ODBC driver to access Sybase 11 database server (the ODBC
driver from microsoft)

the error messgae is: "ERROR [HY000] [INTERSOLV][ODBC SQL Server driver][SQL
Server]ct_param(): user api layer: external error: An illegal value of 1024
was placed in the status field of the CS_DATAFMT structure."

the store procedure is:

  CREATE PROC spVerifyRMU


  AS


      RETURN 1
  go

the code to execute the command is:

  Dim conn As New
         Microsoft.Data.Odbc.OdbcConnection
("Provider=MSDASQL;DSN=TWPDB;UID=sa;PWD=")
  conn.Open()
  Dim cmd As New Microsoft.Data.Odbc.OdbcCommand("NexNet..spVerifyRMU ?, ?
", conn)
  cmd.CommandType = CommandType.StoredProcedure
  cmd.Parameters.Add("ReturnValue", Microsoft.Data.Odbc.OdbcType.Int)
  cmd.Parameters("ReturnValue").Direction = ParameterDirection.ReturnValue




  cmd.Connection = conn
  cmd.ExecuteNonQuery() -- geting error here

any body has an idea ???

10x

2. Restoring a drive

3. ADO error with Crystal and EXECUTE within SQL stored procedure

4. windows / USB Port problem?

5. Is there a system stored procedure that can return all stored procedures given a connection string?

6. ioctl(TIOCSCTTY): Operation not permitted

7. Executing of the stored procedure was successful?

8. Create and execute store procedure

9. executing a query which generates mutiple stored procedures

10. Parameter names when executing stored procedures

11. Executing Stored Procedures w/ DB2 Connect on OS/390

12. Executing a DTS package from a stored procedure.