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

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

wdchristensen — Tue, 23 Jan 2018 15:24:51 GMT

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

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

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

marcus_sommer — Tue, 23 Jan 2018 15:58:01 GMT

Your select-statement isn't complete - it is missing the marked part:

Select * from "SomeTestDB".....

Further I doubt that you could call the UDF in this way - normally functions are called with:

function(FIELD) as FieldAdjusted

- Marcus

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

snehamahaveer — Tue, 23 Jan 2018 16:16:27 GMT

Hello,

The SQL select generally has the path at the end.

So something like the below might help

SQL Select

From UDf.ValidateEmail;

Select

Field1,

Field2 as TestUDF

From

[testdb].[dbo].[UDF.ValidateEmail];

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

wdchristensen — Tue, 23 Jan 2018 16:17:11 GMT

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

wdchristensen — Tue, 23 Jan 2018 16:23:02 GMT

Thanks for the responses!

In an attempt to keep the example simple, I neglected to include a “from” clause as is unnecessary to illustrate my point. The error is the same if I were to call the udf as follows:

Select *, [testdb].[dbo].[ValidateEmail] ('TestEmail@gmail.com')

from [testdb].[dbo].[EmployeeTbl];

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

marcus_sommer — Tue, 23 Jan 2018 16:27:11 GMT

Ok. It seems to be a valid sql-statement within your database but your used database-driver must support it and return some kind of table to Qlik - I have some doubts that it will return a table else that it is some kind of special feature within your db.

- Marcus

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

marcus_sommer — Tue, 23 Jan 2018 16:31:45 GMT

I would rather try an approach like:

Select *, UDF.ValidateEmail('TestEmail@gmail.com') as Email

from [testdb].[dbo].[EmployeeTbl];

- Marcus

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

wdchristensen — Tue, 23 Jan 2018 16:44:30 GMT

With the revised query syntax I get the same error.

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

marcus_sommer — Tue, 23 Jan 2018 16:52:54 GMT

I would check if a select * from on the table worked. If yes and then afterwards the function-call of your UDF failed meant that either the call-syntax is wrong or the driver didn't support it - maybe there are alternate drivers available.

- Marcus

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

wdchristensen — Tue, 23 Jan 2018 17:38:47 GMT

The sql works in the load script until I add the UDF with a parameter.

I use the Microsoft OLE DB Provider for SQL Server driver with other tools using udfs with no issues so it is looking like a Qlik bug to me. Unfortunately I am on Qlik Sense 3.2 and I am pretty sure this bug has been fixed 'November 2017' so all I really need to do is upgrade, right? #psychic

I appreciate you taking the time to reply to my issue. Thanks

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

rwunderlich — Tue, 23 Jan 2018 18:34:58 GMT

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

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

wdchristensen — Tue, 23 Jan 2018 20:40:53 GMT

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