Advertisements
Home > SQL 2005, SQL Errors > Error 191: Some part of your SQL statement is nested too deeply.

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.

Advertisements
Categories: SQL 2005, SQL Errors Tags:
  1. Santhosh Ch
    October 7, 2014 at 4:24 am

    Hi Folks,

    I got the same error when i am trying to insert 10-15 values through simple insert statement, it is throwing above error.

    Does this error resolved or any work around could help!!!!!

    Thanks,
    Santhosh

  2. October 7, 2014 at 9:42 am

    Can you post your script here? This is an issue with string concatenation.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: