Solved

Why does this Insert cause a SQL dump?

Posted on 2014-03-28
11
589 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 33

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 

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 33

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 33

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 33

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

770 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