11 Replies Latest reply: Jan 23, 2018 3:40 PM by William Christensen RSS

    SQL Server User Defined Function (UDF) generates error in load script

    William Christensen

      When I attempt to use SQL Server User Defined Function in my SQL code in load script (qlik sense) it generated a cryptic error. I have ensured that the connection account has access to the udf and everything runs perfectly under the same account in SSMS. I have tried other udfs which generate similar errors. For the sake of this example, I used the validate email function below:


      Create FUNCTION [dbo].[udf_ValidateEmail] (@email varChar(255))


      RETURNS bit

      AS

      begin

      return

      (

      select

      Case

      When @Email is null then 0                 --NULL Email is invalid

      When charindex(' ', @email) <> 0 or --Check for invalid character

      charindex('/', @email) <> 0 or --Check for invalid character

      charindex(':', @email) <> 0 or --Check for invalid character

      charindex(';', @email) <> 0 then 0 --Check for invalid character

      When len(@Email)-1 <= charindex('.', @Email) then 0--check for '%._' at end of string

      When @Email like '%@%@%'or

      @Email Not Like '%@%.%'  then 0--Check for duplicate @ or invalid format

      Else 1

      END

      )

      end


      UDF_Mystery.png