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?

asked 19 Feb '13, 18:48

Jason%20%27Bug%27%20Fenter's gravatar image

Jason 'Bug' ...
158669
accept rate: 50%

edited 20 Feb '13, 08:05

Mark%20Culp's gravatar image

Mark Culp
23.0k9130270


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%20Duemesnil's gravatar image

Thomas Dueme...
2.5k243560
accept rate: 15%

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 woundsexperiences - 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...

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

permanent link

answered 20 Feb '13, 03:26

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.5k243560
accept rate: 15%

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
Your answer
toggle preview

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

Question tags:

×8
×2

question asked: 19 Feb '13, 18:48

question was seen: 1,368 times

last updated: 20 Feb '13, 11:20