sp_executesql does not recompile on every execution. The execution plans for adhoc SQL are also cached along with procs. The difference is that the seperate pieces of sql executed by...

I wrote the following a while back when I needed to drop and recreate primary keys. I've just included the foreign key parts.

Warning, this is rough code and needs to...

Can't see any reason, unless there are no rows that satisfy the conditions "FINISHTIME = '---'" and 'RMJOB.RMJOBID = PREPLOG.RMJOBID'

If you do a select for those conditions, what do you...

It does, but the governor is enabled either a server level or per connection. All the governor will do is prevent very expensive queries from been run, depending on the...

Could you post the procedures and the details of the deadlock graph?

Dynamic SQL. Build up the create table statement as you un through the cursor, then execute the statement. Something like the following (rough pseudo-code)

SET @Creation = 'CREATE TABLE MYTable ('

Fetch...

From http://www.sommarskog.se/grantperm.html

SqlContext.WindowsIdentity.Name will return domain and Windows user name, if the user logged in through Windows authentication. For an SQL login, WindowsIdentity is Null, so access to...

Short answer, in an order determined by the execution plan that the optimiser comes up with. that order may vary with server load, data volumes and other factors.

It's possible that...

ExecuteNonQuery is the .Net method to execute a command and not return any results.

Alternatives are ExecuteScalar, ExecuteReader and ExecuteXMLReader. (At least in .Net 2.0)

If you do a select from that table with the same where clause, do you get any rows back?

Unrelated to your question, but serious anyway. Are you aware that your code is...

833 is indicative of a overloaded IO subsystem or a misbehaving IO filter driver (like an anti virus)

Are the drives local, NAS or SAN?

Run performance monitor on the server during...

The local variables work because the query optimiser can't sniff their values when it compiles the proc, hence it will generate an average-case plan, optimised for no particular values

Possibly, but it is not simple. Would require that your master call out to the command prompt and run SQLCMD, passing it the files to run. Something like this might work...

The best strategy depends greatly on the queries that you use. Some points

Put seperate indexes on seperate columns when queries use different columns. an index on 2 columns ColA, ColB...

With 5 GB memory on the server (that's a rather weird amount) I'd probably set min to 3.5 and max to 4, providing there are no other apps that run...