Data truncation in UDF but not in ISQL

[Former Member]

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?

[thomas_duemesnil · Accepted solution]

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.

[VolkerBarth · Accepted solution]

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.

[thomas_duemesnil · Accepted solution]

Every Day you can learn something in this Forum 😉

[VolkerBarth · Accepted solution]

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

[Former Member · Accepted solution]

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

[thomas_duemesnil · Accepted solution]

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

[thomas_duemesnil]

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;

or

set @FormattedLicense = ( select ... )

HTH

[VolkerBarth]

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.

[Former Member]

Sadly, I tried both suggestions and neither work.

[VolkerBarth]

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