Day 25: General Transact sql script errors

        Day 25: General Transact sql script errors

  1. You can create only one identity column in a table.

create
table errors
(i int
identity(1,1),j int
identity(1,1))

 

Msg 2744, Level 16, State 2, Line 1

Multiple identity columns specified for table xxx. Only one identity column per table is allowed.

  1. Can create only one Primary Key

create
table errors
(i int
primary
key,j int
primary
key)

Msg 8110, Level 16, State 0, Line 1

Cannot add multiple PRIMARY KEY constraints to table ‘errors’.

 

  1. When try to insert duplicate record in primary key

 

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint ‘PK__Vinay1__3BD019964E88ABD4’. Cannot insert duplicate key in object ‘dbo.Vinay1’.

The statement has been terminated.

 

  1. When query select statement and specific object does not exists

select
*
from xxx

Msg 208, Level 16, State 1, Line 2

Invalid object name ‘xxx’.

 

exec a

Msg 2812, Level 16, State 62, Line 1

Could not find stored procedure ‘a’.

 

create
table abc
(i int, j varchar(10)
Not
Null,k int)

go

 

  1. When insert statement contains different column/values

Msg 121, Level 15, State 1, Line 2
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

insert
into abc
(i,j,k)values (1,‘a’)

 

Msg 109, Level 15, State 1, Line 1

There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

 

insert
into abc
(i,j)values (1,‘a’,2)

 

Msg 110, Level 15, State 1, Line 1

There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

 

 

insert
into abc values (1,‘a’,2,3)

 

Msg 213, Level 16, State 1, Line 1

Column name or number of supplied values does not match table definition.

 

 

  1. When the incorrect query syntax.

 

select
* fro|m abc ;

 

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ‘fro’.

 

 

  1. When inserting into string(char/varchar.. ) with larger than its specified size.

 

 

insert
into abc
(j)
values (‘Sql server 2008’)

 

Msg 8152, Level 16, State 14, Line 1

String or binary data would be truncated.

The statement has been terminated.

 

  1. When try to insert NULL value for NOTNULL field

insert
into abc
(i)
values (1)

 

Msg 515, Level 16, State 2, Line 1

Cannot insert the value NULL into column ‘j’, table ‘Vinay.dbo.abc’; column does not allow nulls. INSERT fails.

The statement has been terminated.

 

http://www.sql-server-helper.com/error-messages/msg-1-500.aspx

  1. When you enable/set a sp_configure option which is advance without setting advance option you may get below error.

sp_configure
xp_cmdshell
,1

 

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option ‘xp_cmdshell’ does not exist, or it may be an advanced option.

 

Or

 

Configuration option ‘xp_cmdshell’ changed from 0 to 1. Run the RECONFIGURE statement to install.

 

http://msdn.microsoft.com/en-us/library/ms189631.aspx

  1. Delete primary key having more than 253 foreign key – BUG .

When you have foreing keys more the 253 and if you want to delete record, you will get below error:

Msg 8621, Level 17, State 2, Line 1  The query processor ran out of stack space during query optimization. Please simplify the query

 

https://connect.microsoft.com/SQLServer/feedback/details/172561/the-query-processor-ran-out-of-stack-space-during-query-optimization-during-simple-delete

It’s a BUG in sql server

Advertisements
This entry was posted in Others. Bookmark the permalink.

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