Insert Error: Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)

SQL Server 2005 :

Firstly, don't ask why I am still dealing with SQL Server 2005 in 2013. Sometimes it's difficult to upgrade the version because of financial as well as development constraints. But we are here to discuss the technical problems and not financial so let's get back to the problem on hand.

So for a while now I had snapshot replication running for SQL Server 2005 and had to set up a snapshot replication for one more database. Once I set up the replication I took a new snapshot but while delivering the snapshot I kept getting this error.

To fix this problem all I had to do was install Service Pack 4 and wolla!!! it started to work. If you are interested to know more about this then keep reading......

The problem was while creating a SP as part of the snapshot scripts. The Stored procedure had an INSERT statement. Since the Primary key was defined as IDENTITY columns (for that table) on the publisher, the column was also defined as 'NOT FOR REPLICATION' when set up for replication.

This means that on the subscriber also the column was defined similarly. But since the subscriber knows that it is set up for replication, it expects values for that columns to be explicitly mentioned in any INSERT statement. But this is not possible since the SP is on the publication and there you can not explicitly insert values for the IDENTITY column.

Alas, Microsoft had realized this long time ago and had provided the fix in SP4. So applied that and everything was working fine.

Non-SysAdmins have been denied permission

Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.  The step failed.

Yesterday, I scheduled some maintenance plans for one of my new servers and today I see that all of them failed with the same error like the one mentioned above. There are various work arounds for this error but the simplest according to me would be to change the ownership of the job to the SA account.

First I had the SQL Server Agent service account added to the local admin group. But still the jobs kept failing so as the next step I changed the ownership of the job to SA. Just go to the properties of the job and change that.

This should fix the problem otherwise you will have to go through the process of creating proxy accounts and credentials to run the jobs.

Error: 18486, Severity: 14, State: 1

SQL Server 2005/2008: 
How to find locked out user and unlock them?
How to unlock a login without changing password?

Login failed for user 'xxx' because the account is currently locked out. The system administrator can unlock it.  [CLIENT: <local machine>]
Error: 18486, Severity: 14, State: 1

Today I had a client who asked me how to unlock a SQL Server Login. So firstly I listed out all the logins that were locked out using the following query.

select name, 'Is Locked' from sys.server_principals  WHERE LOGINPROPERTY (name , 'IsLocked') = 1

However, when I tried to unlock the password I received the following error. 

'Reset password for the login while unlocking'

The normal process to unlock the login would be change password and also force the user to change the password on first login. This can be done using the following TSQL.


ALTER LOGIN [yourloginname] WITH PASSWORD=N'xxxxx' UNLOCK MUST_CHANGE

Instead of this you can do the following too to unlock the login but still maintain the password.

USE [master]

ALTER LOGIN [yourloginname] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

ALTER LOGIN [yourloginname] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

GO

Just by disabling and then enabling the password check policy, you can unlock the account.