Solved

Using tsf_user() in a default value expression

  • 30 November 2022
  • 8 replies
  • 122 views

Userlevel 2
Badge +1

Using the dbo.tsf_user() in a default value expression (see image below) leads to an error in execution of a model (after an upgrade from SF2021.2 to SF2022.2)

 
We experience the following error during execution of the source code:
 

 

Error: 'Column "dbo" is not allowed in this context, and the user-defined function or aggregate "dbo.tsf_user" could not be found.'.

Any ideas?

icon

Best answer by Freddy 30 November 2022, 12:45

View original

This topic has been closed for comments

8 replies

Userlevel 7
Badge +23

Hi André,

This has been resolved in the upcoming 2023.1 version.

For older versions it is possible to copy paste the contents of the tsf_user function into the default valie query of the desired column(s).

Userlevel 5
Badge +20

Hi André,

I have done it by putting this code as default query, it will not work for AzureSQL

case when context_info() is not null
then replace(convert(varchar(128), context_info()) collate Latin1_General_BIN, char(0), '')
else system_user
end

 

Userlevel 6
Badge +16

Using the dbo.tsf_user() in a default value expression (see image below) leads to an error in execution of a model (after an upgrade from SF2021.2 to SF2022.2)

 
We experience the following error during execution of the source code:
 

 

Error: 'Column "dbo" is not allowed in this context, and the user-defined function or aggregate "dbo.tsf_user" could not be found.'.

Any ideas?

 

Put select dbo.tsf_user() and a proxy function will be made and it will work as a default. 

Userlevel 2
Badge +1

@Freddy, @Harm Horstman, @Mark Jongeling  thanks for your quick responses.

Using the solution from @Freddy did the job

Userlevel 7
Badge +23

Do note that the solution Freddy provides does indeed work, but for every column that uses this default value query, a separate proxy function is created. This means if you use "select dbo.tsf_user()” for 100 columns, that the SF will generate 100 proxy functions.

I highly recommend changing the default value query back to "dbo.tsf_user()” when/after upgrading to 2023.1. 

Userlevel 2
Badge +1

@Mark Jongeling : thanks for the recommendation. We’ll change it back after the upgrade to 2023.1.

For now, it's only used in 6 columns.

Userlevel 5
Badge +20

Hi André,

Are you sure Freddy’s solution will not give problems during the DB creation/update process? Because the tsf_user function is being dropped and created every update cycle and could lead to an error when used in a default proxy.

Userlevel 6
Badge +16

Hi André,

Are you sure Freddy’s solution will not give problems during the DB creation/update process? Because the tsf_user function is being dropped and created every update cycle and could lead to an error when used in a default proxy.

I use it in several projects and I haven't got any creation/upgrade process issues yet.