Why does this Insert cause a SQL dump?

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
tayloraiAsked:
Who is Participating?
 
ste5anConnect With a Mentor Senior DeveloperCommented:
@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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<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
 
ste5anSenior DeveloperCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
tayloraiAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
ZberteocCommented:
0
 
tayloraiAuthor Commented:
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
 
ZberteocCommented:
That error has nothing to do with your query. It is a system error.
0
 
ste5anSenior DeveloperCommented:
@Zberteoc: my AV sense is tingling..
0
 
tayloraiAuthor Commented:
@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
 
ste5anSenior DeveloperCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.