Data truncation in UDF but not in ISQL

I have the following statement which works without error:

   DECLARE  @FormattedLicense NVARCHAR(255)

   SELECT   @FormattedLicense = CAST (
            CustomerID + '|' +
            AppName + '|' +
            MachineName + '|' +
            CAST (ExpirationDate AS VarChar(10)) + '|' +
            LicenseType
            AS NVARCHAR(255))
   FROM     RegisteredMachines
      INNER JOIN Licenses ON
            Licenses.LicenseID = RegisteredMachines.LicenseID
   WHERE    RegistrationID = 1

   SELECT   @FormattedLicense

I would like to use this as a user-defined function, so I created one like this:

ALTER FUNCTION "DBA"."fFormatLicense" (@RegistrationID Unsigned BigInt)
RETURNS NVARCHAR(255)
AS
BEGIN
   DECLARE  @FormattedLicense NVARCHAR(255)

   SELECT   @FormattedLicense = CAST (
            CustomerID + '|' +
            AppName + '|' +
            MachineName + '|' +
            CAST (ExpirationDate AS VarChar(10)) + '|' +
            LicenseType
            AS NVARCHAR(255))
   FROM     RegisteredMachines
      INNER JOIN Licenses ON
            Licenses.LicenseID = RegisteredMachines.LicenseID
   WHERE    RegistrationID = @RegistrationID

   RETURN   @FormattedLicense
END

Unfortunately, I get errors when I try to call this function.

DECLARE @Requested Unsigned BigInt
SET @Requested = 1
SELECT fFormatLicense (@Requested)

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Cursor not in a valid state
SQLCODE=-853, ODBC 3 State="24000"

Right truncation of string data
SQLCODE=-638, ODBC 3 State="22001"

I don't understand why my UDF doesn't work. Can anyone show me what to fix and how to fix it?

cast right-truncation-of-strin

asked 19 Feb '13, 18:48

Jason 'Bug' ...
158●6●6●9
accept rate: 50%

edited 20 Feb '13, 08:05

Mark Culp
24.9k●10●141●297

flat view

2 Answers:

active answers oldest answers newest answers popular answers

Right truncation of string data is an indicator that the server thinks you push a long string in a too small variable. I thought explict CAST prevents this.

Try to set the connection level option string_rtruncation option [compatibility] to off to verify if this is the cause of the problem.

permanent link

answered 20 Feb '13, 04:29

Thomas Dueme...
2.7k●29●39●65
accept rate: 17%

I thought explict CAST prevents this.

No, AFAIK you will have to use LEFT() or something alike to prevent that error if string_rtruncation is on.

(20 Feb '13, 04:48) Volker Barth

Every Day you can learn something in this Forum ;-)

(20 Feb '13, 04:55) Thomas Dueme...

Replies hidden

Sure - I think it's really helpful to share one's own ~~wounds~~experiences - me, I had had the impression that CAST would be sufficient, too, until reality proved me wrong:)

(20 Feb '13, 05:50) Volker Barth

I tried the LEFT() trick, but that didn't work. Changing the string_rtruncation option fixed it though. Thanks!

(20 Feb '13, 10:56) Jason 'Bug' ...

Replies hidden

Where did you apply the Left() in your script?

(20 Feb '13, 11:20) Thomas Dueme...

flat view

You Should you ether

   SELECT   CAST (
            CustomerID + '|' +
            AppName + '|' +
            MachineName + '|' +
            CAST (ExpirationDate AS VarChar(10)) + '|' +
            LicenseType
            AS NVARCHAR(255))
   INTO @FormattedLicense

   FROM     RegisteredMachines
      INNER JOIN Licenses ON
            Licenses.LicenseID = RegisteredMachines.LicenseID
   WHERE    RegistrationID = @RegistrationID;

set @FormattedLicense = ( select ... )

HTH

permanent link

answered 20 Feb '13, 03:26

Thomas Dueme...
2.7k●29●39●65
accept rate: 17%

Oh, the joys of "Transact-SQL compatibility"... though "SELECT @localVar = ..." seems to be valid in SA, too, otherwise Jason's original statement batch should not work, either, methinks.

(20 Feb '13, 03:41) Volker Barth

Sadly, I tried both suggestions and neither work.

(20 Feb '13, 10:53) Jason 'Bug' ...

Replies hidden

Have you tried to turn off the string_rtruncation option? (BTW, what SA version and build no. are you using?)

(20 Feb '13, 10:57) Volker Barth

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

right-truncation-of-strin ×8
cast ×6

question asked: 19 Feb '13, 18:48

question was seen: 2,887 times

last updated: 20 Feb '13, 11:20

SAP SQL Anywhere

Data truncation in UDF but not in ISQL

Follow this question

Related questions