Amazon Deals

Monday, April 7, 2014

SQL Server Error: 1222 - Lock request time out period exceeded

This is one annoying error which prevents you from doing anything on SQL Server Management Studio.

When you work on Management studio, your actions may create temporary tables, call number of system stored procedures and if there are any other actions happening on the database your management studio connection may be blocked. After sometime you will get this error.

Old version of SQL Server enterprise manager would simply freeze, so this is kind of an improvement.


In one situation, I had a script running which failed. The script had many DDLs and DMLs interspersed, so when I started to look into changes that were made so far, I was hit with this error.

To come out of this, I simply ran a ROLLBACK on the last query window. The reason I hit this one was due to a bug in the script which created a nested transaction and there was no corresponding COMMIT or ROLLBACK. The script terminated with an error and one OPEN TRANSACTION.

The problem was GO statement after BEGIN TRANSACTION like

BEGIN TRANSACTION
DELETE dbo.ApplicationTable WHERE AppID = 3
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.ApplicationTable ADD Note Char (255)
UPDATE dbo.ApplicationTable set Note='Nothing'

Running SELECT @@TRANCOUNT would return 1. The developer intended to put GO after COMMIT, but had it after BEGIN TRANSACTION.
































Monday, March 31, 2014

SQL Server ALTER AUTHORIZATION vs ALTER SCHEMA

The goal is to change the schema of a table from dbo to TestUser.

In pre-SQL Server 2005 world I could have used sp_changeobjectowner to change owner of the table. However with SQL Server 2005, the concept of SCHEMA and OWNER comes into picture.

SCHEMA and OWNER may sound like the same thing and you may expect to behave same but they are not.

For example when I run this statement

ALTER AUTHORIZATION ON dbo.TestTable TO TestUser

The TestTable is still in dbo schema, however TestUser becomes the owner with all privileges on the table. You can see the privileges TestUser has by running

EXECUTE AS USER='TestUser';
  SELECT * FROM sys.fn_my_permissions('dbo.TestTable','OBJECT');
REVERT;


But if you check the sys.sysobjects table the TestTable is still under dbo schema.

select name, USER_NAME(uid) from sys.sysobjects where name='TestTable'

To move the table to TestUser schema, you will have to use ALTER SCHEMA command as

ALTER SCHEMA TestUser TRANSFER dbo.TestTable

Please keep in mind that when you change the object owner, any other users who had GRANTs on that object will be revoked.

Thursday, March 27, 2014

ORA-01031 Insufficient Privilege when using Toad







You can get ORA-01031 for valid reason if you do not have SYSDBA or SYSOPER privilege and you try to connect to a database as those user type. 




However I ran into this error using Toad 11.6 64-bit on Windows 7 PC when connecting as Normal user. The difference here was I could click OK button and it would open Toad. I could not figure out what was going on. I had another PC with Toad 10.6 that worked fine with the same user and database.

Upgrading to version 12.1 fixed the issue. The database I was trying to connect to was 12c as well, not sure if this error is only happening on 12c with Toad 11.6. Since I already upgraded I can't tell now.