Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I tested in Nov2017 patch 1 desktop and it worked without error.
Interestingly, when I code it up in a Query Window in SQL Management studio, I get a syntax underline with the exact error message you posted. However it executes fine.
-Rob
Hi Rob, based on your feedback I ran the code in question on Qlik Desktop 'November 2017' and the code worked perfectly. This confirms what I suspected. This issue is a known bug with version 3.2 and the solution is to upgrade. https://community.qlik.com/thread/283897 #GoundHogDay #3.2BetaRelease