Custom Functions Available To Linked Databases

I have a linked table in SQL Server to an Oracle database. Thinking I was
tricky and not wanting to rewrite all of my queries for oracle to sql server
(yes I have to do the same application from two different servers one with
direct access to oracle and the other as a linked db in sql server) I wrote
a
few of the more basic functions that I use that are oracle specific so that
I
would not need to change my code much between servers. Stuff like TO_DATE,
TO_CHAR, NVL, and INITCAP anyway when connected with sql query analyzer the
queries work like a champ when called from the web server they crap out and
say that the function doesn't exist (after trying to process which before I
wrote them didn't happen only said not recognized) anyway I guess the point
of all this rambling is to ask if there is a way to make the custom function
s
available to ADO on the webserver when calling that server.They are available if you are using Openquery - it's just a
pass through query to Oracle. If you are using 4 part naming
to access the linked server from your Web server, your
Oracle specific functions won't work - those need to be just
done in T-SQL. Check books online under Openquery function.
-Sue
On Mon, 8 Nov 2004 14:49:09 -0800, "Jason Burr"
<JasonBurr@.discussions.microsoft.com> wrote:

>I have a linked table in SQL Server to an Oracle database. Thinking I was
>tricky and not wanting to rewrite all of my queries for oracle to sql serve
r
>(yes I have to do the same application from two different servers one with
>direct access to oracle and the other as a linked db in sql server) I wrote
a
>few of the more basic functions that I use that are oracle specific so that
I
>would not need to change my code much between servers. Stuff like TO_DATE,
>TO_CHAR, NVL, and INITCAP anyway when connected with sql query analyzer the
>queries work like a champ when called from the web server they crap out and
>say that the function doesn't exist (after trying to process which before I
>wrote them didn't happen only said not recognized) anyway I guess the point
>of all this rambling is to ask if there is a way to make the custom functio
ns
>available to ADO on the webserver when calling that server.|||Sue,
You maam are a godess.
Anyway while that was a much better and easier solution to implement and
does exactly what I need I did have another question.
My original question was how to make a sql server function globaly available
(My orignal approach was writing those oracle functions as sql server
functions) They worked however I wanted to make them available to all db's s
o
I wouldn't have to do something like dbo.db.function(params) So is there a
way to make a function available system wide (as you may have guessed I am
not a big sql server guy)
Thanks again,
Jason
"Sue Hoegemeier" wrote:

> They are available if you are using Openquery - it's just a
> pass through query to Oracle. If you are using 4 part naming
> to access the linked server from your Web server, your
> Oracle specific functions won't work - those need to be just
> done in T-SQL. Check books online under Openquery function.
> -Sue
> On Mon, 8 Nov 2004 14:49:09 -0800, "Jason Burr"
> <JasonBurr@.discussions.microsoft.com> wrote:
>
>|||Jason,
Not any supported way with functions - there are some hacks
but it's unsupported and not recommended.
Qualifying objects is not a bad thing. Scalar UDFs require
calling with a two part name anyway with owner.function.
If wanting them to be global is related to setting up your
security or having them in a central location, you could
create another database for such objects. You'd still call
them by qualifying with database.owner.function.
-Sue
On Tue, 9 Nov 2004 07:20:02 -0800, "Jason Burr"
<JasonBurr@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>You maam are a godess.
>Anyway while that was a much better and easier solution to implement and
>does exactly what I need I did have another question.
>My original question was how to make a sql server function globaly availabl
e
>(My orignal approach was writing those oracle functions as sql server
>functions) They worked however I wanted to make them available to all db's
so
>I wouldn't have to do something like dbo.db.function(params) So is there a
>way to make a function available system wide (as you may have guessed I am
>not a big sql server guy)
>Thanks again,
>Jason
>"Sue Hoegemeier" wrote:
>|||Thanks again Sue. I was worried that was the case. Thanks for confirming.
"Sue Hoegemeier" wrote:

> Jason,
> Not any supported way with functions - there are some hacks
> but it's unsupported and not recommended.
> Qualifying objects is not a bad thing. Scalar UDFs require
> calling with a two part name anyway with owner.function.
> If wanting them to be global is related to setting up your
> security or having them in a central location, you could
> create another database for such objects. You'd still call
> them by qualifying with database.owner.function.
> -Sue
> On Tue, 9 Nov 2004 07:20:02 -0800, "Jason Burr"
> <JasonBurr@.discussions.microsoft.com> wrote:
>
>