Maybe this can do the things you want:

http://www.red-gate.com/products/SQL_Log_Rescue/index.htm

First:

Do you really need every DISTINCT and GROUP BY? These are performance killers, so if you don't need it, don't use it. If it is needed, try to first select...

Maybe obvious, but

- did you enable the job (checkbox on: job properties - general)

- did you enable the schedule (checkbox on: job properties - schedule - edit)

Please clearify:

1 You create a new job

2 You schedule the job to run once

3 The job runs without error

4 You re-schedule the job to run once (again)

5 The job runs...

At the end we found the problem is due to a bug in the connection.

See http://forums.microsoft.com/MSDN/ShowPost.aspx?PageIndex=1&SiteID=1&PageID=1&PostID=3055387

One of the transactionID's is timed-out and rolled back, but this is not reported back...

Found the problem:

The statements in the batch-process are executed under different TransactionID's while everyone thought it was under the same one. Sometimes the application ends a transaction without issuing a...

Because of the N-prefix the value is cast to NVarChar. The NVarChar(MAX) can contain a maximum of 4000.

BOL:

nvarchar [ ( n | max ) ]

Variable-length Unicode character data. n...

Hi,

You can find this information about the N-prefix in the BOL:

Unicode string constants that appear in code executed on the server, such as in stored procedures and triggers, must be...

Hi,

You can setup a trace to log the locking. Run this trace during the execution of the SP.

You might want to ensure you are the only user, to prevent too...

The discussion becomes very interesting. I didn't know that NOLOCK can cause so much issues.

But when I read the BOL I read the following "NOLOCK: Does not issue any locks....

Thanks Grant,

Your post made it all clear now. The query is a SELECT so the NOLOCK should apply, but I can now continue analyzing the trace why and on what...

In the design of the foreign key you can specify a delete rule. Like you said the CASCADE option is what you need. The following is from BOL:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/08812343-e9a6-4a0f-91fd-80d95ab4b71f.htm

INSERT and UPDATE...

I have only taken a short look at it. I see in the WHERE clause you often use:

value IN (SELECT xxx FROM yyy WHERE zzz) OR value NOT IN...

You can install both version on the same box. But you'll have to look at your performance if it is workable.

Use Windows Performance to set counters on diskspace/access and memory usage. Also set counters to monitor SQL buffer cache and re-compilations. These counters can show you the direction to look...