Skip to main content
Solved

Using tsf_user() in a default value expression

  • November 30, 2022
  • 8 replies
  • 144 views

Forum|alt.badge.img+3

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?

Best answer by Freddy

André Romijn wrote:

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. 

View original
Did this topic help you find an answer to your question?
This topic has been closed for comments

8 replies

Mark Jongeling
Administrator
Forum|alt.badge.img+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).


Harm Horstman
Superhero
Forum|alt.badge.img+21

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

 


Freddy
Forum|alt.badge.img+16
  • Thinkwise Local Partner Brasil
  • 528 replies
  • Answer
  • November 30, 2022
André Romijn wrote:

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. 


Forum|alt.badge.img+3
  • Author
  • Warrior
  • 33 replies
  • December 1, 2022

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

Using the solution from @Freddy did the job


Mark Jongeling
Administrator
Forum|alt.badge.img+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. 


Forum|alt.badge.img+3
  • Author
  • Warrior
  • 33 replies
  • December 1, 2022

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


Harm Horstman
Superhero
Forum|alt.badge.img+21

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.


Freddy
Forum|alt.badge.img+16
  • Thinkwise Local Partner Brasil
  • 528 replies
  • December 1, 2022
Harm Horstman wrote:

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. 


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings