Error 17182: SQL Service Error

While fixing SQL Server configuration I ran into some rather interseting/unique errors:

Server local connection provider failed to listen on [ \\.\pipe\sql\query ]. Error: 0×50
Error: 17182, Severity: 16, State: 1.

TDSSNIClient initialization failed with error 0×50, status code 0×50
Error: 17182, Severity: 16, State: 1.

TDSSNIClient initialization failed with error 0×50, status code 0×1.

As the error indicates that it can’t listen to named piped connection.  [1] pointed me to looking if multiple instances were installed which was not the case.  But looking at the network protocols in SQL Server Manager found Named Pipes were disabled.  Enabled the Named Pipes *poof* went the error.

References

1. SQL Protocols. Microsft SQL Server Protocol Team. TDSSNIClient initialization failed with error 0×50, status code 0×50. Link.

Error 191: Some part of your SQL statement is nested too deeply.

When inserting string into SQL Server table using the INSERT predicate that uses string concatenation there seems to be an issue with in SQL Server 2005. There seems to be an upper limit of about 480+ concaenations before the insert fails and you get following error messsge:

Msg 191, Level 15, State 1, Line 1
Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.
What does that mean? I mean it was a simple insert statement no loops just two brackets; well it seems to be a bug and has been fixed in next major release of SQL Server.

To produce the error, create a new database and try executing the following SQL Statement:

… to create test table …
CREATE TABLE [dbo].[T1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TValue] [varchar](MAX) NULL,
CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

… test insert statements …

-- Good SQL Statement
INSERT INTO T1 VALUES ('A' + ... + 'A')
GO

-- Bad SQL Statement
INSERT INTO T1 VALUES ('A' + ... + 'A' + 'EXTRA')
GO

Please click here for full statement.

Only difference between the good statement and bad statement? The bad statement has ONE extra concatenation.

Microsoft Connect Article, Link.

Error 14274: Cannot add, update, or delete a job (or its steps or schedule) that originated from an MSX server.

You can get this error when trying to modify the properties of a Job after the SQL Server has been renamed or cloned into another computer. When we create a job on SQL Server it creates an entry in sysjobs table logging the job and server originated from. We can fix this issue by updating the sysjobs table:

USE [msdb]
GO

UPDATE sysjobs
   SET originating_server = 'NewServerName'
GO

In addition to jobs not working now the @@ServerName variable can also return invalid information, old server name. Because the @@ServerName gets the server information from the sysservers system table. We can fix this issue with following script:

USE [master]
GO

sp_dropserver 'OldServerName'
GO

sp_addserver 'NewServerName', 'local'
GO

You will have to restart the SQL Server to see the new changes.

Error 18456, Severity 14: Login Failed States

The document referenced below explains all the states, this is just a summary.

State	Description
1	Only state returned in SQL 2000. Seems to be catch-all where no other states apply; for example Account Disabled shows up as State 1(2005).
2	Invalid Userid
5	SQL Account provided does not exist on SQL Server. (SQL2005)
6	Attempt to use a windows login name with SQL Authentication
7	Login disabled and password mismatch
8	Password mismatch
9	Invalid Password (i.e. did not meet minimum password criteria)
10	Read Ref Link #2
11	Windows account does not exist on SQL Server or CONNECT permissions is set to DENY for the login. (SQL2005)
12	Valid login but server access failure; CONNECT permissions is set to DENY for the login.
13	SQL Server service paused
14	????
16	User doesn’t have permission to target (connection string) or default database (SQL Server login setting) (SQL 2005)
18	Change password required
23	Server in process of shutting down, and user attempted to login.
27	Initial database could not be determined for session
38	Initial database could not be determined for session (SQL 2008)
40	Default database could not be accessed (SQL 2008)
58	SQL Server is set to Windows Only Authentication, SQL Login not allowed.(SQL “Denali”)

In SQL 2005 it reports as state 1, user is not associated with a trusted account.

Ref Link #1: States Explained, http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
Ref Link #2: KB925744, Issue with State 10, http://support.microsoft.com/?id=925744
Ref Link #3: Troubleshooting: Login Failed for User ‘x’, http://msdn.microsoft.com/en-us/library/ms366351.aspx

Updated: March 6th, added state 38/40 for SQL 2008.
Updated: March 12th, added a bit more detail about state 11/12 and added state 1.
Updated: April 14, 2011, added state 58. Updated state 5, 11, & 12. If I added (Version); it means I have confirmed/found it on that version.
Updated: November 22, 2011, updated the state 8, what is invalid password?