Archive for August, 2011

SQL Server Permissions - Aaarrgghh!

Thursday, August 11th, 2011

I recently ran into a situation where I could attach to our SQL Server server but had no permission to do anything.  I couldn’t create a database, couldn’t change my permissions to allow myself access, nothing.  I couldn’t even attach to the server as sa because we were configured for Windows authentication only.  It seemed as though I was well and truly screwed.

A little (a lot actually) time spent with the programmer’s secret weapon, Google, revealed the solution.  It turns out to be a long and involved process but basically you need to enable both Windows authentication and SQL server authentication.  Set up a new sysadmin user that you can use to fix sa and then, finally, give yourself permission to do what you need to do. (more…)

The Case of the Disappearing Rows

Friday, August 5th, 2011

I just lost a week of my life trying to track down a problem with a program that did a mass insert of records into a SQL Server database.  The program is written in Delphi 2007 and uses dbExpress components for access to SQL Server.

The problem first manifested after an 8 hour run when, lo and behold, the database only had 11K rows rather then the expected 884K.  I knew that the program was sound because I had just run the same program to load the same file into a Firebird database without a problem.  (That only took 3 hours by the way).

Now, the source file had some bogus data in it.  I knew this and the program made allowances for it by trapping the resulting exceptions, writing them to a log file and dropping the offending record.

It turns out that these data conversion errors fall into a class of SQL Server error known as “batch aborting” errors.  Whenever a batch aborting error occurs the current transaction is rolled back without so much as a “by your leave”.  You would think that this would be documented somewhere, in BIG BOLD LETTERS.  If it is, I can’t find it.  If it weren’t for Google I would still be scratching my head.

I found this table in an article on another web site:

Error Aborts
Duplicate primary key. Statement
NOT NULL violation. Statement
Violation of CHECK or FOREIGN KEY constraint. Statement
Most conversion errors, for instance conversion of non-numeric string to a numeric value. BATCH
Attempt to execute non-existing stored procedure. Statement
Missing or superfluous parameter to stored procedure to a procedure with parameters. Statement
Superfluous parameter to a parameterless stored procedure. BATCH
Exceeding the maximum nesting-level of stored procedures, triggers and functions. BATCH
Being selected as a deadlock victim. BATCH
Permission denied to table or stored procedure. Statement
ROLLBACK or COMMIT without any active transaction. Statement
Mismatch in number of columns in INSERT-EXEC. BATCH
Declaration of an existing cursor Statement
Column mismatch between cursor declaration and FETCH statement. Statement.
Running out of space for data file or transaction log. BATCH

The entire article, which is a good review of SQL Server error handling techniques, can be found here.