[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 696
  • Last Modified:

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
0
taylorai
Asked:
taylorai
  • 4
  • 3
  • 2
  • +1
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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:
@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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now