Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Why does this Insert cause a SQL dump?

Posted on 2014-03-28
11
607 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

839 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