yes

Because functions INDEXPROPERTY and OBJECTPROPERTY are executed locally.

Table sysdepends contains all objects used in procedures.

If you don't use ad-hoc SQL and table is not in sysdepends - that table is you candidate.

But take into account: sysdepends records...

SELECT [Table1].PrimaryKeyID

FROM [Table1]

WHERE NOT EXISTS (select 1 from [Table2]

where [Table1].PrimaryKeyID = [Table2].ForeignKeyID

...

Just exclude...

Can you post the error message?

Add top level element around your XML:

{MyXMLDoc}

... your XML here

{/MyXMLDoc}

Sorry, I had to replace proper tag brackets with {} because otherwise site does not display it.

doc_sewell (7/22/2008)

---

I have it working fine now. This is an application being ported from an Oracle Forms application into .Net and SQL Server. My job is to...

Your update statement is just wrong.

It creates cross join of #tmp_416 to itself and updates everything with whatever.

This must work:

update t1

set total_portfolio = t2.amt

from #tmp_416...

doc_sewell,

Did you ever consider a possibility of more than 1 user accessing your database at the same time?

Did you think that while 1st user validates the query 2nd one can...

select OBJECTPROPERTY ( id , 'ExecIsInsteadOfTrigger' ),

OBJECTPROPERTY ( id , 'ExecIsAfterTrigger' ),

OBJECTPROPERTY ( id , 'ExecIsInsertTrigger' ),

* from dbo.sysobjects where xtype='TR'

Check BOL for other OBJECTPROPERTY parameters.

New record create whole new set, so it always affects all columns.

Even if you don't insert anything meaningfull in there.

You may create this procedure as system procedure in master database (that's where prefix "sp_" is required).

Then you may call it like:

EXEC TargetDB.dbo.sp_process_customer

or just call it from TargetDB

USE TargetDB

GO

EXEC dbo.sp_process_customer

and...

jcrawf02,

can you explain this:

ISNULL(count(distinct #PLANDATA_Claim.claimid),0) AS "FFS",

What ISNULL is doing here?

By starting INSERT you begin a transaction.

By getting data for INSERT from remote server you make that transaction distributed.

DTC must inspect all objects used within the transaction, apply necessary locks,...