SQL: Parameters from excel generate error: String data, right truncation

Question

After adding parameter2 to the query, I got an error message: SQL Server Error “String Data, Right Truncation”.

The parameters are entered by the user in Excel 2010 spreadsheet and used in the SQL statement to update the Report.

Example:

Fiscal year (parameter1): 2014, GL Date (parameter2) yyyymmdd: 20140228

The error is generated by: "GLPOST.JRNLDATE<=?" which is parameter2

WHERE GLAMF.ACCTID = GLPOST.ACCTID AND GLACGRP.ACCTGRPCOD = GLAMF.ACCTGRPCOD AND 
GLPOST.DRILLDWNLK = BKTRAND.DDLINK AND GLACGRP.SORTCODE='1000' AND   
GLPOST.FISCALYR>=? AND GLPOST.JRNLDATE<=?

In the database, GLPOST.FISCALYR is (char(4), not null) and works fine.
GLPOST.JRNLDATE is (decimal(9,0), not null) causes an error. If I enter a hardcoded value such as: 20140228, 20131231, etc... it works but it doesn't seem to pick up the value from the cell.

I did try different formattings but with no success, any idea? thanks!!

attila · Accepted Answer · 2014-03-12 21:44:52Z

1

Maybe try casting the parameter like this:

GLPOST.FISCALYR>=? AND GLPOST.JRNLDATE<=cast(? as decimal(9,0))

answered Mar 12, 2014 at 21:44

attila

2,2291 gold badge13 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Seva Over a year ago

I did try casting but it didn't work, however if I remove the parameter1 (Fiscal year) the query runs well. Maybe the value intended for parameter2 (char(4)) was pushed into parameter1...

attila Over a year ago

Can you post the code you are using to add the parameters?

Seva Over a year ago

The code is exactly as I posted above, it's an ODBC connection, the code is in the: "Command text" and then each "?" is associated with a cell in the Excel spreadsheet. I'm guessing the issue is somewhere there

Collectives™ on Stack Overflow

SQL: Parameters from excel generate error: String data, right truncation

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related