Run-time error 80040e07

Question

Run-time error 80040e07

AUribe

Old Hand

Points: 397
More actions
March 7, 2007 at 10:57 am

#123139

Hi..!!!
I have two sql servers, one for production data and one for testing purposes.
The following sentence works perfectly on the production server, but generates and error on the testing server, I think there is a problem in the testing server configuration's, can any one help me...?
Sentence:
INSERT INTO Vacaciones_Hist VALUES ( 'EMPRESA', 3, 2007, 'AV', convert(varchar(10),'2007/01/01',120), '2007-2008', convert(varchar(10),'2007/12/31',120), 16, current_timestamp , current_timestamp ,'TEST')
Table design:
Cia_Id           nvarchar 10
Empleado_Id   int 4
Ano_Vac        int 4
Tipo_Mov      nvarchar 50
Fecha_Inicial smalldatetime 4
Periodo_Ano   nvarchar 10
Fecha_Final    smalldatetime 4
Vac_Valor       real 4
Date_Cre        smalldatetime 4
Date_Mod       smalldatetime 4
User_Id          nvarchar 10
thanks for your help...!!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

that error is usually when you try and stick 10 characters into a field that is smaller than 10 for example...check the TEST server's DDL for the table...i would bet that production's table is a different definition than your test server.

form a google:

Why do I get 80040E57 / 80040E07 errors?

When dealing with CHAR/VARCHAR columns, and not validating user input, you may have come across this error:

Microsoft OLE DB Provider for SQL Server error '80040e57'

String or binary data would be truncated.

or

Microsoft JET Database Engine (0x80040E57)

The field is too small to accept the amount of data you attempted to add.

Try inserting or pasting less data.

or

Microsoft OLE DB Provider for ODBC Drivers error '80040e57'

[Microsoft][ODBC Microsoft Access Driver]Invalid string or buffer length.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 2

You may have to provide field names when values you provided do not match the default.

SQLBill SSC Guru Points: 51440 More actions · Answer 3

Are you using SQL Server 2005 or SQL Server 2000? current_timestamp only exists in SQL Server 2000.

If you are using SQL Server 2005, you might want to check your 'compatibility level' and make sure its not set for SQL Server 2000.

-SQLBill

SDM SSCommitted Points: 1581 More actions · Answer 4

Your table definition is shown as:

Cia_Id nvarchar 10

Empleado_Id int 4

Ano_Vac int 4

Tipo_Mov nvarchar 50

Fecha_Inicial smalldatetime 4

Periodo_Ano nvarchar 10

Fecha_Final smalldatetime 4

Vac_Valor real 4

Date_Cre smalldatetime 4

Date_Mod smalldatetime 4

User_Id nvarchar 10

That number on the end looks like a data length - is that right? If so, the number of characters able to be stored in Cia_Id is 5, not 10 (nvarchar takes 2 bytes per character) The value supplied would be truncated.

Are you positive the tables have the columns declared in both environments (with the same data type and scale)?

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 5

It is a data length problem.. the field in which ur inserting the data has less size difined..

Please check the values which ur inserting..

Regards ,

Amit Gupta..

AUribe Old Hand Points: 397 More actions · Answer 6

First, thanks all for your coments...

I have reviewed the table definitions and they are the same.

SQLBill you were right i'm using SQL 2000

I don't think there is a length problem since for production database works fine and inserts the data.

I try changing the date format in the insert intruction and tested on the testing database and it works fine, the change was:

from : convert(varchar(10),'01/01/2007',112)

to: convert(varchar(10),'01/01/2007',112)

I don't know if this is goint to work on production server, i will schedule a test tonight.

Maybe with this new information it is possible to determine if the testing database were not created with the same parameters..?

thanks again for your comments...

vadba SSChampion Points: 11133 More actions · Answer 7

One more thing - do your insert statements contain date constants as shown in your example? You don't need to convert the date strings:

INSERT INTO Vacaciones_Hist

VALUES ( 'EMPRESA', 3, 2007, 'AV', '2007/01/01', '2007-2008', '2007/12/31'

, 16, current_timestamp , current_timestamp ,'TEST')

AUribe Old Hand Points: 397 More actions · Answer 8

I found the problem.....

The user "Languaje" parameter were set different in test database, I changed to "English" and now everything works fine...

Thanks everybody....