custom functions can they be without dbo.

Question

custom functions can they be without dbo.

Davor Geci

SSC-Addicted

Points: 487
More actions
June 1, 2007 at 3:19 am

#179141

Hello guys,
I have sql scripts written for a Access DB. To make them work in SQL 2005 I have written custom functions for IIF, VAL, Format,.....
Now my question:
My custom functions work only if I call them like:
dbo.VAL ('somenumber')
Can it be done that I don't must type the dbo. and that the DB will know what I want.
If I call it like:
VAL ('somenumber')
It says that VAL is not a built-in function.
It will be great if I'll could call it like this and not to rewrite my existing code.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Davor

If you change the default schema of the calling user to dbo then you shouldn't need to qualify the name of the function. Bear in mind that if the user needs to access objects owned by other schemas, then this may break.

John

Anders Pedersen SSChampion Points: 11410 More actions · Answer 2

I thought deterministic functions always had to have the schema infront of the name when called. At least I always get the error:" 'function name' is not a recognized built-in function name" if I do not.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Anders

That'll probably be it, then. A quick fumble in Books Online and I found this: "Scalar-valued functions must be invoked by using at least the two-part name of the function". I couldn't find anything about deterministic functions, but I think either way the answer to Davor's question is "you can't".

John

Davor Geci SSC-Addicted Points: 487 More actions · Answer 4

Thnx, guys for yours reply's

In that case, is there any chance to catch the sql sent by the application to the server, and then to process it to add dbo. in front of this functions.

I have spent a lot of time to find and write this functions so that my app. built for Access (mdb) can work with sql server with no change of code in existing app.

Is there any trigger or something so that I can catch the sql, process it and then process it to database??

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Davor

I can't think of any way of doing that server-side. You'd need to configure your application to do that.

John

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

I pity your successors who would have to maintain that mess. You're asking "Should I rewrite my application, or rewrite SQL Server?". The answer is to rewrite the application, because it desperately needs it.

You have an application full of Access-specific ad-hoc queries. If you had created Access views instead, you would only have to create the equivalent SQL Server views. Or if you had a separate data access layer in your app, the rewrite would be limited.

The easiest rewrite might be to add a function to your app that can parse SQL queries and add the "dbo." prefixes where necessary. Then wrap all the SQL commands in this function wherever they are used. You could even add an Access/SQL Server parameter to tell it whether to modify the command or return it unchanged, so the application could work with either database. This approach might give you a working application, but you still need to address the design issues at some point.