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 functions
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 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 functions
>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 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:
> 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 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 functions
> >available to ADO on the webserver when calling that server.
>|||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:
>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 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:
>> 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 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 functions
>> >available to ADO on the webserver when calling that server.
>>|||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:
> >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 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:
> >
> >> 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 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 functions
> >> >available to ADO on the webserver when calling that server.
> >>
> >>
>