Solved

Why does this Insert cause a SQL dump?

Posted on 2014-03-28
11
563 Views
Last Modified: 2014-03-28
I am trying to insert data to a SQL Server (2008 R2) table using the SQL Management Studio. When I do so with the following statement it works fine:
INSERT INTO TEST.DBO.TRANSLATION
      (SOURCE, TYPEID, PRIORITY, SIGNIFICANT,
      CREATEDATE, CREATEUSER, UPDATEDATE, UPDATEUSER)
VALUES
           ('AD'
           ,3
           ,100
           ,0
           ,GETDATE()
           , 'TEXT'
           ,GETDATE()
           , 'TEXT'
           )
However, it fails when I use this statement:
INSERT INTO TEST.DBO.TRANSLATION
      (SOURCE, TYPEID, PRIORITY, SIGNIFICANT,
      CREATEDATE, CREATEUSER, UPDATEDATE, UPDATEUSER)
SELECT DISTINCT
           'AD'
           ,3
           ,100
           ,0
           ,GETDATE()
           ,'TEXT'
           ,GETDATE()
           , 'TEXT';

The results window shows:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

The Error log contains:
Error: 17310, Severity: 20, State: 1.
and
*   Access Violation occurred reading address 0000000000000260

Can anybody help?
Thanks

Alistair
0
Comment
Question by:taylorai
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39961394
<very wild guess>

The two GETDATE()'s may be calculating with every row that is INSERTed.  To avoid this, use variables..

Declare @dt datetime = GETDATE()

INSERT INTO TEST.DBO.TRANSLATION
      (SOURCE, TYPEID, PRIORITY, SIGNIFICANT, 
      CREATEDATE, CREATEUSER, UPDATEDATE, UPDATEUSER)
SELECT DISTINCT
           'AD'  ,3 ,100,0 ,@dt,'TEXT',@dt, 'TEXT' ;

Open in new window

Also, the above is missing a FROM block.  

If you are inserting a single row of values, without a reference to a table, then use VALUES instead of SELECT

Declare @dt datetime = GETDATE()

INSERT INTO TEST.DBO.TRANSLATION
      (SOURCE, TYPEID, PRIORITY, SIGNIFICANT, 
      CREATEDATE, CREATEUSER, UPDATEDATE, UPDATEUSER)
VALUES('AD'  ,3 ,100,0 ,@dt,'TEXT',GETDATE(), @dt);

Open in new window

0
 
LVL 32

Expert Comment

by:ste5an
ID: 39961437
First of all: Do you have backups?

This is a fatal error. When you have any kind of SA with Microsoft, then you should consider using CSS.

What SQL Server version (SELECT @@version;) do you use?

Have you applied the fix from KB2515286?

Use [PRIORITY] cause it is a reserved word. The DISTINCT is also not necessary.
0
 

Author Comment

by:taylorai
ID: 39961511
Thanks for these suggestions.
I should explain that I am using the SELECT DISTINCT because I am trying to get to the bottom of an error that originally occurred in a stored procedure, selecting from another table, but it still occurs in Management Studio when I select literal/number fields. There is no FROM because all the values are just literals/numbers.
The SQL version is 10.50.1617 and the fix from KB2515286 has not been installed (the only update seems to be the fix from 2494088).
In the actual SQL I am using, the name of the PRIORITY field is about 30 characters long, but I cut it down to make it more readable: sorry I should have noticed that I had made it into a reserved word.

Regarding the DISTINCT, interestingly if I do not use it I do not get a SQL crash, but error
"Arithmetic overflow error converting tinyint to data type numeric."
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39961544
>There is no FROM because all the values are just literals/numbers.
Then use VALUES instead of SELECT DISTINCT.

>Regarding the DISTINCT, interestingly if I do not use it I do not get a SQL crash, but error
"Arithmetic overflow error converting tinyint to data type numeric."
Eyeballeth thy TRANSLATION columns, and the values that you are inserting into them, as tinyint (0 to 255) can't handle a number outside of that range being inserted into it.

Since I don't see any numbers outside of that range in your query, perhaps there is an insert trigger on TRANSLATION that is doing other stuff.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39961545
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:taylorai
ID: 39961579
I wondered about triggers, but there are no triggers on this table. It looks as if the [SIGNIFICANT] field is the problem, since this also causes a crash:
  UPDATE TEST.DBO.TRANSLATION
  SET SIGNIFICANT = 1
  WHERE SOURCEVALUE = 'AD'
[SIGNIFICANT] is a smallint, which does not allow NULLs and is notionally defaulted to 0. However, SQL still crashes when I try this insert:
INSERT INTO TEST.DBO.TRANSLATION
      (SOURCE, TYPEID, PRIORITY, --SIGNIFICANT,
      CREATEDATE, CREATEUSER, UPDATEDATE, UPDATEUSER)
      SELECT DISTINCT
            'AD'
           ,3
           ,100
           --,0
           ,GETDATE()
           ,'TEXT'
           ,GETDATE()
           , 'TEXT'
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39961584
That error has nothing to do with your query. It is a system error.
0
 
LVL 32

Expert Comment

by:ste5an
ID: 39961587
@Zberteoc: my AV sense is tingling..
0
 

Author Comment

by:taylorai
ID: 39961599
@Zberteoc and @ste5an: I understand where you are coming from, but I would love to know why it reliably does not happen with a VALUES insert.
0
 
LVL 32

Accepted Solution

by:
ste5an earned 500 total points
ID: 39961604
@taylorai:

Regarding the DISTINCT, interestingly if I do not use it I do not get a SQL crash, but error
"Arithmetic overflow error converting tinyint to data type numeric."

You may try to CAST() explicitly. Also check your database integrity:

*SQL Server Integrity Check
*SQL SERVER – Check Database Integrity for All Databases of Server
*DBCC CHECKDB / Database Integrity

But you should really consider contacting MS as it is a fatal error.
0
 
LVL 32

Expert Comment

by:ste5an
ID: 39961610
This is a kind of low-level, fatal system error. It should not happen at all.

For details you need MS to debug it.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now