Another SQL Bug

In our development environment, I found no one could use SQL Management Studio to update data.

We would get:
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.

And then get:
No row was updated.
The data in row X was not committed.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The updated row has changed or been deleted.
Correct the errors and retry or press ESC to cancel the change(s).


A search of google and MSDN revealed a lot of people with similar errors, but they had no primary key on the table, or actually had other people change the data. There were also a lot of "why do you want to do this" posts - I really hate it when someone uses that as a reply to a technical question.

Then I found:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289535

I hate having SET NOCOUNT ON, SET NOCOUNT OFF in each of our 1000+ stored procedures, and worse, people do not set these consistently. So I set SET NOCOUNT ON by default. Apparently the team that wrote management studio just assumed NOCOUNT would always be false, and relied on the return values without even checking to see if it was on. Worse, they didn't give me anything in the GUI to use any connection settings other than the default. So if you use SQL Server 2005, you can either get the reasonable default setting of NOCOUNT to ON OR use Management Studio to enter data. You cannot do both.

I am really hoping this is fixed in SQL 2008, which I haven't had much time to try. Given the virtually unlimited money and years of time Microsoft invested, these things annoy me.

BTW - NOCOUNT=TRUE is like a double negative - this is a bad name. Why not SET COUNT=OFF insead of SET NOCOUNT=ON?

Both of my coworkers ran into this issue, but ignored it instead of figuring it out.

Science Cafe

Monday, I went to the Science cafe (http://www.greatlakesbrewing.com/communityEvents.php?action=viewEvent&calendar_id=00000084) which had a presentation by Dr. Jerrold Vitek and Dr. Jay Alberts from The Cleveland Clinic. They did a community presentation on deep brain stimulation, which can be effective for diseases like Parkinson's. Some of the things I found fascinating:

1) This and related research was started relatively recently, by randomly lesioning and/or stimulating random parts of the brain. While this technique does have dramatic improvements for some patients, it was essentially created by trial and error with little regard for the theory of what effect the lesioning or stimulating actually had on the brain.

2) One of the presenters (I am still not sure which) had a Physiology degree. I was surprised to hear that advances in our understanding of how the brain works since this technique has started haven't helped much. He said better understanding of what structures do what things has given them ideas for a starting point, but again, progress is largely by trial and error.

3) Lesioning and stimulating seem to have similar effects. Obviously, stimulating is less permanent, and allows better fine tuning.

4) A friend and former coworker had this procedure done, with good results so far, probably by the people doing the presentation.

5) The effect of stimulating is not always immediate. Sometimes turning on/off the stimulation changes the problem like a light switch. In other cases, it can take days before the effect starts, and the effect can last for weeks weeks after stimulation stops.

6) Bilateral stimulation seems to result in a drop of cognitive ability, where unilateral does not. The research gave a vague answer when I asked why, and seemed to be much more interested in helping patients avoid the effect then explaining why this happens.

7) The researchers complained about lack of funding when asked one question about further research. I doubt leading researchers at The Cleveland Clinic are not worried about where their next meal comes from, but still don't understand how we can spend nearly 20 billion/year on NASA exploring outer space, but have little to no funding to understand something far more interesting and relevant. I follow NASA a lot, and their money is far better spent than much of what our government does, but they are an easy target having a large budget publicly available.

8) At least in one case, an insurance company paid for probe's to be implanted in someone's head, but denied the cost of the device to do the stimulation. I guess they thought wearing the probes would somehow help.

GAC

If I am on a computer named "box1" and type "\\box2\c$\WINDOWS\assembly" into my run directory and see a GAC window, you might assume I'd be looking at the GAC on box2. You'd be wrong.

Pulling up this directory on in windows explorer causes Windows to display the contents of the GAC on the local machine instead of the actual directory's contents. Browsing to this folder over the network does the same thing - show the GAC of the LOCAL machine.

Carnegie Mellon

I can finally finish my application to earn a PhD at Carnegie Mellon. I am posting this entry since they ask for a homepage on my application, and I don't even have anything yet on this blog related to what I want to work on. This is not because I haven't been reading and working on anything related to my PhD, or I'd rather write about business software, just that this blog has been about what I am working on, not what I would like to be working on. There are no jobs for researchers in anything interesting with only an M.S. (which I only recently finished anyway), so I do a lot business programming, accounting, sql databases, websites, etc. What I do for a day job is not bad, and certainly could be worse, but I want the PhD to be able to work on what I find interesting, not what employers find profitable.

I may start adding research notes to this blog. So far, despite having a small number of entries, it has been useful, if only just for my own reference. I doubt anyone from CMU is going to look up this page, but if you are from CMU reading this, please post a comment to prove me wrong. A blog is not the best source for determining how good a student is, but it is probably better than just going by GRE scores.

log4net FileNotFoundException

For the longest time I was getting a FileNotFoundException debugging on my local when trying to use log4net in an aspx app. I would always break on ConfigureAndWatch in the Global.asax. Since I knew log4net was working, and the correct version was loaded from the GAC, I aways just hit "continue" and never spent any time on it.

Today I noticed log4net itself appeared in the call stack when this exception was thrown. This is odd, the assembly is throwing an exception looking for itself. I loaded the debug symbols for both log4net and the system assemblies, which is a great thing to be able to do, and found it was thrown from System.Type.GetType which was being used to dynamically load the PatternLayout class from log4net. Somehow this was incorrectly specified in the logging.config file I used to configure log4.net. Somehow log4net was not smart enough to know I incorrectly specified a loader, and the system exception, that the assembly could not be found, was being thrown.

SQL Server Statistics

Today I found a problem with indexed view performance getting worse over time. This is another post I am creating mostly for myself, but this may be useful for others since it was pretty hard to find all the details on the web. One of the most important factors to SQL Server performance is up to date statistics. Using automatic statistics (enabled by default) does a good job, but we found the need to run sp_updatestats during periods of low activity. We use a Prestonia based server (waiting for Nehalem) for a 5 GB database with 100+ active users, so every bit of performance helps. Automatic statistics and sp_updatestats handles everything well except indexed views.

sp_updatestats ignores indexed views. Microsoft claims this is because statistics on indexed views are ignored except when using the WITH (NOEXPAND) hint. You need Enterprise Edition to use indexed views without the (NOEXPAND) hint, so the only explanation I can think of for this behavior is somehow Microsoft thinks screwed up statistics will sell more Enterprise licenses. If they sell more of anything, it will be Oracle licenses. I have never seen automatic updates work on indexed views either, although I haven't seen this documented.

The result of this is that indexed view that sped everything up when you created it is getting further out of sync with its statistics. Eventually this leads to horrible execution plans that just keep getting worse. The solution, first you should run:

SELECT so.name,
si.rowmodctr,
si.rowcnt,
CASE WHEN si.rowcnt > 0 THEN 100.0 * si.rowmodctr / si.rowcnt ELSE 0 END PercentError
FROM sys.objects so
INNER JOIN sys.sysindexes si ON si.id = so.object_id
WHERE is_ms_shipped = 0 AND indid = 1
ORDER BY 4 DESC

Running this periodically will show you the state of all clustered index statistics, including indexed views and regular tables, and is a good thing to pay attention to regardless of if you have Enterprise edition or use indexed views.

You will need to update stats for each indexed view similar to 'UPDATE STATISTICS vAcBalanceBill', I have not found another way to do this. Setting this up in SQL Server Agent mostly takes care of the problem.

Finding the primary key

Here is a script to list the primary key or keys on @tablename in SQL Server:
SELECT sc.name
FROM sys.indexes si
INNER JOIN sys.index_columns sic ON si.index_id = sic.index_id
AND si.object_id = sic.object_id
INNER JOIN sys.columns sc ON sc.column_id = sic.column_id
AND si.object_id = sc.object_id
WHERE si.is_primary_key <> 0
AND si.object_id = OBJECT_ID(@tablename)

I am posting this, since I tried to google it, and most people's answers were way off in the weeds.

Script to fix SQL logins

When copying a database from one server to another server, the logins/users can get really screwed up. This is because users are database specific, and logins are server specific and each user must map to a login. Moving or copying a database brings the users with it, but not the logins.

An intelligent, well designed, database management system would take care of this for you. By default, it would map users to logins that have a login with the name, and create disabled logins for users that don't. What SQL Server does is create all the users that don't have logins as not mapping to anything. Sometimes it maps users that have a login, and sometimes it doesn't (I still haven't found the pattern).

This means after copying a database to a new server, typically people cannot log in, and a good deal of work needs to be done if you haven't seen the problem before. The most frustrating is seeing the user and login both being created correctly, but not mapped together. You can fix this easily with "EXEC sp_change_users_login 'AUTO_FIX', @name", but only if you understand the issue. Even if you know exactly what is wrong, fixing this manually takes too long.

I want to post the scripts I use to fix this, if only so I have a good reference. I hate copying/pasting SQL out of Outlook, and I need a quick way to get this script from any computer across the net. So feel free to use it if it helps you too. This should work in most version and environments with minimal changes.


DECLARE @name varchar(128)

DECLARE login_cursor CURSOR FOR

SELECT name FROM [ServerDBWasFrom].master.dbo.syslogins

WHERE name NOT IN (
SELECT name
FROM master.dbo.syslogins
WHERE name IS NOT NULL
) AND name IS NOT NULL

OPEN login_cursor

FETCH NEXT FROM login_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Creating login for ' + @name
EXEC sp_addlogin @name, 'password1'
EXEC sp_change_users_login 'AUTO_FIX', @name
PRINT ''
FETCH NEXT FROM login_cursor INTO @name
END

CLOSE login_cursor
DEALLOCATE login_cursor
GO



--This script will fix any orphaned logins (such as those not created using the first script)

DECLARE @name varchar(128)

DECLARE login_cursor CURSOR FOR
SELECT name FROM master.dbo.syslogins WHERE name IS NOT NULL

OPEN login_cursor

FETCH NEXT FROM login_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN
PRINT 'Autofixing login for ' + @name
EXEC sp_change_users_login 'AUTO_FIX', @name
PRINT ''
FETCH NEXT FROM login_cursor INTO @name
END

CLOSE login_cursor
DEALLOCATE login_cursor

GO