Delete trigger & COM+

Hi, I'm building a website that interacts with MS SQLserver(v7) via a Buisiness Rule-layer developed in COM+ with VB(v6 sp5). The website is an add-on on a existing client/server app. Now I have delete triggers on almost every table and they work perfectly in the c/s app and when runned in the query analyzer. But when I try to do the same with COM+ I get the the next error message on line 10:
"Cannot use SAVE TRANSACTION within a distributed transaction."

Here follows the code. Hopefully anybody can help me with this problem.

SQL-statement:

Function getDeleteRequestSQL(ByRef strRequestId As String) As String
Dim strSQL As String
strSQL = "DELETE FROM thmld2 WHERE right(hdmcode,8)='" & strRequestId & "'"
getDeleteRequestSQL = strSQL
End Function

And then the place where the error occurs.

GetConnection cnConn
strSQL = getDeleteRequestSQL(reqId)
10 cnConn.Execute strSQL, , adExecuteNoRecords

And finaly the trigger:

create trigger td_thmld2 on thmld2 for delete as
begin
declare
@.numrows int,
@.errno int,
@.errmsg varchar(255)

select @.numrows = @.@.rowcount
if @.numrows = 0
return

select @.numrows = (select count(*) from trigstat with (nolock) )
if @.numrows > 0
return

save transaction trans_td_thmld2 <-- REASON FOR ERROR?

/* delete all children in "thmstat" */
delete thmstat
from thmstat t2, deleted t1
where t2.hdmcode = t1.hdmcode

/* delete all children in "thmldlk1" */
delete thmldlk1
from thmldlk1 t2, deleted t1
where t2.hdmlmldcode = t1.hdmcode

/* errors handling */
error:
raiserror @.errno @.errmsg
rollback transaction trans_td_thmld2
endDon't know what that is, but it has nothing to do with the trigger...

Well that me guessing again...

Sounds more like you're establishing a tran across many servers...|||The components I've created are all located within one dll. The dll is active in the component services on my own webserver and is working fine. So the action isn't distributed over the network. :(

delete tables automatically

How can I delete tables in a SQLServer Database automatically (at the moment I just do it using Micorosft SQl Management Studio Express manually)

But its important that I can do that automatically, the best thing would be if I can do that from a .NET programm.

thanks in advance,

mulata

Does Automatically means just sending a query from you program? DELETE FROM TABLENAME

|||

yes, but how can I implement that in my .NET program?

The Database should simply be cleared totally, so all tables should be deleted.

|||

You would need to explain a little bit more rather than one liners. Do you need to delete all the tables or some of them or just one of them?

You could write a stored proc to drop all the tables and just call the proc from your .NET code.

|||

okay, I just want to delete all tables in a database.

Im sorry but I have no idea how I can write such a proc, which I can call from my .NET code.

It would be great if someone can give me an example of such a proc.

Actually I think what the proc has to do, is just connect to the database, and the make a simple call, but I dont know what there is to do in detail.

thanks in advance

|||

SqlConnection connection =

new

SqlConnection (ConfigurationSettings.AppSettings[

"ConnectionString"]);

try

{

connection.Open ();

SqlCommand cmd =

new SqlCommand ("delete from (select * from sys.tablenames)" )

cmd.ExecuteNonQuery ();

}

finally

{

connection.Close ();

}

|||

Depending on your business requirements if you dont need the data ever, you might look into truncatng the tables. DELETE'ing can bloat the logs. TRUNCATE is a non-logged operation.

Read up the documentation regarding DELETE, TRUNCATE and it wil lhelp you decide which one worls best for you.

CREATE PROC dbo.prcDeleteAllTables

AS

BEGIN

TRUNCATE TABLE tableA

END

Delete record in sysdatabases

Hi,
in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
the DB compare the write: "(6.5 compatible)".
I don't know what this DB is. I want to remove it but by the right button of
the mouse I can't do anything.
I deleted the .mdf and .ldf files but the DB is not removed from the DB
list. It is present in the "sysdatabases" table of "master" database but I
can't remove the record.
It is possibible to delete the record from the table "sysdatabases"?
Thank you very much.Davide Franzoni wrote:
> Hi,
> in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
> the DB compare the write: "(6.5 compatible)".
> I don't know what this DB is. I want to remove it but by the right button
of
> the mouse I can't do anything.
> I deleted the .mdf and .ldf files but the DB is not removed from the DB
> list. It is present in the "sysdatabases" table of "master" database but I
> can't remove the record.
> It is possibible to delete the record from the table "sysdatabases"?
> Thank you very much.
Have you tried dropping it using the DROP DATABASE command?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||sysdatabases is one of the critical table that sql server uses. it is not a
recommended to issue any DML command directly on it. So my answer is NOT TO
delete
vt
"Davide Franzoni" <DavideFranzoni@.discussions.microsoft.com> wrote in
message news:E4864CFC-59FB-4F52-AFD3-3E4FB46ED5FF@.microsoft.com...
> Hi,
> in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
> the DB compare the write: "(6.5 compatible)".
> I don't know what this DB is. I want to remove it but by the right button
> of
> the mouse I can't do anything.
> I deleted the .mdf and .ldf files but the DB is not removed from the DB
> list. It is present in the "sysdatabases" table of "master" database but I
> can't remove the record.
> It is possibible to delete the record from the table "sysdatabases"?
> Thank you very much.|||or try
sp_detach_db
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:455491C7.8090402@.realsqlguy.com...
> Davide Franzoni wrote:
> Have you tried dropping it using the DROP DATABASE command?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Delete record in sysdatabases

Hi,
in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
the DB compare the write: "(6.5 compatible)".
I don't know what this DB is. I want to remove it but by the right button of
the mouse I can't do anything.
I deleted the .mdf and .ldf files but the DB is not removed from the DB
list. It is present in the "sysdatabases" table of "master" database but I
can't remove the record.
It is possibible to delete the record from the table "sysdatabases"?
Thank you very much.
Davide Franzoni wrote:
> Hi,
> in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
> the DB compare the write: "(6.5 compatible)".
> I don't know what this DB is. I want to remove it but by the right button of
> the mouse I can't do anything.
> I deleted the .mdf and .ldf files but the DB is not removed from the DB
> list. It is present in the "sysdatabases" table of "master" database but I
> can't remove the record.
> It is possibible to delete the record from the table "sysdatabases"?
> Thank you very much.
Have you tried dropping it using the DROP DATABASE command?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||sysdatabases is one of the critical table that sql server uses. it is not a
recommended to issue any DML command directly on it. So my answer is NOT TO
delete
vt
"Davide Franzoni" <DavideFranzoni@.discussions.microsoft.com> wrote in
message news:E4864CFC-59FB-4F52-AFD3-3E4FB46ED5FF@.microsoft.com...
> Hi,
> in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
> the DB compare the write: "(6.5 compatible)".
> I don't know what this DB is. I want to remove it but by the right button
> of
> the mouse I can't do anything.
> I deleted the .mdf and .ldf files but the DB is not removed from the DB
> list. It is present in the "sysdatabases" table of "master" database but I
> can't remove the record.
> It is possibible to delete the record from the table "sysdatabases"?
> Thank you very much.
|||or try
sp_detach_db
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:455491C7.8090402@.realsqlguy.com...
> Davide Franzoni wrote:
> Have you tried dropping it using the DROP DATABASE command?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Delete record in sysdatabases

Hi,
in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
the DB compare the write: "(6.5 compatible)".
I don't know what this DB is. I want to remove it but by the right button of
the mouse I can't do anything.
I deleted the .mdf and .ldf files but the DB is not removed from the DB
list. It is present in the "sysdatabases" table of "master" database but I
can't remove the record.
It is possibible to delete the record from the table "sysdatabases"?
Thank you very much.Davide Franzoni wrote:
> Hi,
> in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
> the DB compare the write: "(6.5 compatible)".
> I don't know what this DB is. I want to remove it but by the right button of
> the mouse I can't do anything.
> I deleted the .mdf and .ldf files but the DB is not removed from the DB
> list. It is present in the "sysdatabases" table of "master" database but I
> can't remove the record.
> It is possibible to delete the record from the table "sysdatabases"?
> Thank you very much.
Have you tried dropping it using the DROP DATABASE command?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||sysdatabases is one of the critical table that sql server uses. it is not a
recommended to issue any DML command directly on it. So my answer is NOT TO
delete
vt
"Davide Franzoni" <DavideFranzoni@.discussions.microsoft.com> wrote in
message news:E4864CFC-59FB-4F52-AFD3-3E4FB46ED5FF@.microsoft.com...
> Hi,
> in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
> the DB compare the write: "(6.5 compatible)".
> I don't know what this DB is. I want to remove it but by the right button
> of
> the mouse I can't do anything.
> I deleted the .mdf and .ldf files but the DB is not removed from the DB
> list. It is present in the "sysdatabases" table of "master" database but I
> can't remove the record.
> It is possibible to delete the record from the table "sysdatabases"?
> Thank you very much.|||or try
sp_detach_db
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:455491C7.8090402@.realsqlguy.com...
> Davide Franzoni wrote:
>> Hi,
>> in my SQLServer 2005 I have a DB that I can't remove it. Near the name of
>> the DB compare the write: "(6.5 compatible)". I don't know what this DB
>> is. I want to remove it but by the right button of the mouse I can't do
>> anything.
>> I deleted the .mdf and .ldf files but the DB is not removed from the DB
>> list. It is present in the "sysdatabases" table of "master" database but
>> I can't remove the record.
>> It is possibible to delete the record from the table "sysdatabases"?
>> Thank you very much.
> Have you tried dropping it using the DROP DATABASE command?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com