1

Topic: Where did ufn_RtfToPlaintext go?

In SQL Management Studio I used to include the following in my SELECT statement

REPLACE( ISNULL( SUBSTRING(dbo.ufn_RtfToPlaintext( CAST( TAXON_OCCURRENCE.COMMENT AS VARCHAR ) ), 1, 255), ''), '"', '') AS [Comment]

However it looks like th ufn_RtfToPlaintext function has been removed as part of a Recorder 6 update.

What should I be using instead?

Cheers

Paul

2

Re: Where did ufn_RtfToPlaintext go?

What version of Recorder6 do you have installed?  I've checked installations of both 6.26 and 6.28 and that user function is still present.

Andy Foy, Ecologist & IT consultant
Andy Foy Consulting

3 (edited by PaulBarrington 25-10-2018 12:12:30)

Re: Where did ufn_RtfToPlaintext go?

Hi Andy,

Thanks for the prompt reply (and I really enjoyed your workshop last week).

6.26.2. We recently did a fresh install, I'm wondering whether it is removed from the fresh install package as opposed written out by an update now.

Hmmm.

4

Re: Where did ufn_RtfToPlaintext go?

The same thing happened with two functions that we use - dbo.spLCBLSSplitSH and dbo.LCFormatToccDataOnly. They used to be issued as part of the software but then disappeared, but whether it is the new install or an upgrade that loses them we haven't been able to work out. Might be the same problem?
Janet

Janet Simkin
British Lichen Society

5

Re: Where did ufn_RtfToPlaintext go?

Hi
An update has never been used to remove a system supplied udf.  Certainly still apears to be in 6.28

Mike Weideli

6

Re: Where did ufn_RtfToPlaintext go?

Hi Mike,

Thanks for the post. It is really odd.

I go to NBNData > Programmability > Functions > Scalar-valued Functions

Towards the bottom of the list I would expect to see dbo.ufn_RtfToPlaintext

However all I have is GetAddress, GetDobandDod, GetTLIKeyFromSearch

I'm happy to create a new function again. Is the source code available though?

Best wishes,

Paul Barrington (GMLRC)

7

Re: Where did ufn_RtfToPlaintext go?

Are those the only scalar functions you can see?  There should be loads and loads.  I'm not an expert on SQL Server permissions but it sounds like it might be a permissions issue ... are you connected to the database using a user with only 'Public' permission (as that might limit the functions that you can see/execute)?

Andy Foy, Ecologist & IT consultant
Andy Foy Consulting

8

Re: Where did ufn_RtfToPlaintext go?

Hi

My feeling is that the udf must be there, but just not visible to you for some reason. The UDF's are used throughout the main R6 application so if they were really missing then R6 would just not work. There doesn't seem to be any difference between those few you can see and the rest. Most udfs have public permissions so that can be used externally.  The only thing I can find about this suggests that stopping the SQL Service and restarting it will put things right, so this is probably worth a try. Also it might be worth writing a one line query which uses one of the missing udfs just to see if they are actually there.

Mike Weideli

9

Re: Where did ufn_RtfToPlaintext go?

Hi

When I look at the permissions for ufn_RtfToPlaintext compared to one of the udfs that Paul can see (e.g. ufn_GetDobandDod) they are different.  And if I connect to the database using a user with only Public access I can't see all the udfs, only a subset including ufn_GetDobandDod (but excluding ufn_RtfToPlaintext).  So perhaps it's the user that Paul is connecting with?

Andy Foy, Ecologist & IT consultant
Andy Foy Consulting

10

Re: Where did ufn_RtfToPlaintext go?

Hi

Yes the original R6  udf  have restricted permissions but I think that everything added in recent years have public permissions  so not sure if this is the whole story.

Mike Weideli

11

Re: Where did ufn_RtfToPlaintext go?

Hi

Yes. A lot of the original R6 funtions do have restricted permission (mainly those with the ufn prefix), so it could be that Paul is not connecting with sufficient permission to see them. However there are a lot more functions than the few listed by Paul which do have public permission, so this may not be the whole story. 

Paul try running the following  query against NBNdata.

Grant Execute  ON  [dbo].[ufn_RtfToPlaintext] to public

This may bring up an error message which helps to explain things.

Mike Weideli