About concurrency....

Twice, in the last week, concurrency issues have jumped up to bite me.

The first time round, the server had 'NO COUNT' set to on. This confused the .Net software, which couldn't tell if any rows had actually changed before it updated the table: LINQ to SQL uses @@ROWCOUNT after updates to issue an optimistic automated concurrency check. If 'NO COUNT' is on, the @@ROWCOUNT value is always zero, and the concurrency check fails.

The second occurred while using the SSMS GUI to try and edit data in a table: right click on the table, pick 'edit top 200 rows'. The error we got was

Data has changed since the Results pane was last retrieved. Do you want to save your changes now?
(Optimistic Concurrency Control Error)
Click Yes to commit your changes to database anyway.
Click No to discard your change and retrieve the current data for this row.
Click Cancel to continue editing.

Again: concurrency issues. So, the first thing I did was to check whether 'NO COUNT' was on. It wasn't. Then I went in for a bit of google. It turns out the problem was in the data.

Several rows in the table were identical, and contained text fields. Despite having an 'id' column, the table didn't have a primary key. Via the GUI, SQL couldn't work out which row to delete, and, of course, running a DELETE T-SQL statement would have deleted all the rows. We wanted to keep one of them (natch).

To tidy up the data, I ran an INSERT, based upon a SELECT TOP 1 T-SQL statement (it was not possible to SELECT DISTINCT - there were text fields involved), but with a new ID value. Then I DELETEd the duplicate data, and finally UPDATEd the new row to the original ID value.

At this point, we tried changing some other data in the table. We had the same error message, despite the fact that the data was, this time, unique.

Unique, but without a primary key. Redesigning the table to include a primary key on the 'id' column (and why there wasn't one on there in the first place is anyone's guess) has made it possible to update the data via the GUI.

There's one last reason why this particular concurrency issue may happen: data. Columns containing '%', '_' or '[' can also cause issues.

OSQL -E

Sometimes, you get a black-box installation by a third party supplier: and, when you politely enquire as to whether you can install SSMS on the server so that you can pull out pertinent information for your SQL Estate Audit (because, naturally, the installation on the server is such that you cannot connect remotely: you just know that the server is there), the answer is no. Grrr.

But not the end of the world. Via the command prompt, we can open a connection to that instance of SQL, and run whatever queries we fancy with a local administrator's account (because, let's face it, if they haven't installed SSMS, and they're a third party supplier, the likelihood is that they haven't disabled the BuiltIn\Administrators group....).

Start off by checking the Services on the Server, to get an indication of what you've got: also check SQL Server Configuration. This will let you know how many instances of SQL are on the server, and whether they have a name other than the default servername. On the server I'm looking at, I've got two instances of SQL Express. I can connect to one by typing the following in the command prompt:

OSQL -E

For the second, I need to be a bit more specific, and type

OSQL -E -S ServerName\InstanceName

Once I've got OSQL running, I can query merrily. Every T-SQL statement that I can run in SSMS or Query Analyzer can be run in OSQL. I can execute stored procedures. I can change databases. I just need to remember that instead of hitting 'F5' to run my statement, I need to end my statement with 'GO', and press enter. It's worthwhile not running 'SELECT *', since the formatting leaves something to be desired, and it's a bit tricky....

So, what might my useful statements be? I've used a selection of the following today

To list my databases
SELECT name
FROM sys.databases
GO

To check authentication mode

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
WHEN 0 THEN 'Windows and SQL Server Authentication'
ELSE 'Unknown'
END
GO

To check recovery mode

SELECT recovery_model_desc
FROM sys.databases
GO

To check collation

SELECT name, collation_name
FROM sys.databases
GO

To check database owner

SELECT name, SUSER_SNAME(owner_sid)FROM sys.databases
GO

To check SQL Version, Windows Version and SQL Service Pack

SELECT @@VERSION
GO
SELECT ServerProperty('ProductLevel')
GO

To check database size

USE dbname
GO
EXEC sp_spaceused
GO

It's pretty easy to find out what you need by searching for the criteria, with an additional search of 'T-SQL' slapped at the front of the string. More additions will be made here as I come to need them, however, this got me what I wanted for my spreadsheet....

It's the little things

Me, I like to faff about with my resultsets in Excel. This probably makes me incredibly old fashioned, and I'm sure various of my friends would call me a flat-earther. Tant pis.

However, I got all overexcited just now. Instead of highlighting the resultset, copying it using ctrl+c and pasting it into the spreadsheet at row #2, and then swearing greatly while I transferred the column names individually, I right clicked on the resultset after highlighting it.

Now, I've been using SSMS for SQL 2008 for, oh, six months. How did I fail to notice that there was an option to "Copy with Headers"?! If this has been an option in SQL 2005, well, please don't tell me. I'd rather not know that I missed it for, oh, two years.