Sure...a view works well for these things.  It's a good way to encapsulate some complicated join syntax; with the added benefit of "compiling" the execution plan.

Now, the where clause CAN...

I've run into exactly this problem.  Setting rowcount performed to slowly....look at the estimated execution plan for a hint as to why this is (BOL: "The setting of SET ROWCOUNT...

Seems like the best place to find this is in the Office SDK (under samples or something).  I don't have it, though; and Microsoft is not known for making good...

Actually, you can capture what proc the spid is running:

DBCC INPUTBUFFER(@@spid)

put that in a trigger and you should be able to figure this out.  Don't the "Dependencies" show up...

OK, I hesitate to even offer this solution.  Nevertheless, it's saved my bacon, and is useful although not optimal as far as performance is concerned:

---TEST Table

if object_ID('tempdb..##Test') is not null...

OK, this was a little harder than I thought, but this works:

declare @Date datetime,

  @Hour int,

  @isPM bit

select @Date = '2004-08-27 00:00:00.000',

  @hour = datepart(hh, @Date),

  @isPM = Case when @Hour > 12 then 1...

Interesting article...gosh that is complex.

good job!

Yup, that's the way to do it...JDBC to TSQL.  The article referenced says to use xp_cmdShell.  Personally, I schedule the DTS package, then use "sp_start_job" proc.

cl 

This is probably to little to late, but did you look up xp_cmdshell on BOL?  It's pretty clear about permissions.  If you want to give a non-sysadmin rights to xp_cmdshell...

Yeah, I usually use a self-join for something like this.  I tested it out, though, and using the group by costs half of using the self-join.

Nice technique!  I would not...

I realize Mahesh's proc is more generic, but it doesn't appear to be working properly.  I'm not to interested in debugging it, though, too tell you the truth.

He's using the...

Jim,

We'll need a sample of your data plus the sp (if changed).  Also, who's method are you using?

cl

I really like the fact that you separated the data transfer and the SQL transaction.  Using alerts to do this is an ingenious way to make these independent. 

I would have...

What you really need here is client side error handling.  Unfortunately, you're using Acess, so you're stuck with what it offers (plus the SQL error handling, which is not that...

Warning on that, though; potential performance problems, as you're making the completion of a transaction dependent on external processes completing.

cl