Create a permanent numbers table with a couple thousand rows. It's handy for a number of problems, not just this one.

Somethng like this

CREATE TABLE Numbers (

Number INT PRIMARY KEY

)

GO

INSERT INTO...

It's not.

Discard the inactive portion of...

Easy way to check - once you've done the restart, check the first few lines of the error log and see if you have the entry "Using locked pages for...

<grin> That's the forum ranking, not username.

SQL ignores trailing spaces when comparing strings. It's probably because of the char data type, which is space padded to max size

The proc doesn't assign rights to the DB. It just makes it the login's default database. You'll have to grant any rights you want the login to have.

After running that,...

David Griffiths (7/15/2008)

---

I understand that covering indexes will often give performance benefits. However, I didn't understand that they were a requirement to prevent deadlocks until I discovered this behaviour for...

EXEC sp_defaultdb <Login Name>, <Database Name>

Run it after both the login and the database exist

When a database is set to autoclose, it is shut down when the last user disconnects. That's what's causing the cache flush. SQL's removing anything related to that DB from...

Stu (7/15/2008)

---

although it still comes up with "A significant part of sql server process memory has been paged out" in the error log, but I'm just going to ignore this...

Cumulative update 4. Big collection of hotfixes since SP2. Currently the latest is cumulative update 8 (CU8)

Not possible in SQL 2000. SQL requires exclusive access to its data files

In SQL 2005, if all the databases are readonly (all of them) it's possible to do, though it's...

Could it be permissions?

When you run it at the command prompt, you're running under your security credential. When you run it through xp_cmdshell, you're running it under the SQL Server...

Shouldn't do.

What user and application are you seeing those statements from? What SPID?

NText works just fine in SQL 2005 (although it is deprecated).

The blob datatypes (text, ntext, image) cannot be directly compared (including by a distinct) in any version of SQL that...