Hello, I would like to create a stored procedure, which would manipulate dat
a
in different databases, where all my databases have the same structure.
I would expect , when this proc is called in DB1 to use tables from DB1,
when called in DB2 .. tables from DB2 .. and so on.
However, when I created my sample stored proc in master..
create proc sp_sample as select * from table1
and then I tried to call this proc in DB1.. even that DB1 had also table1 ,
stored procedure was selecting data from master database. I was expecting it
to use DB1..table1.
I hope I am clear on what I am going to achieve, I would like to have one
copy for each of my stored procedures, stored in master database instead of
10' copies stored across all different databases.
I would appreciate any tips on how this could be done.
LucjanThere's no supported or documented way to achieve what you want to do. Sugge
sted method is to have
the same proc in all databases and use some sw to manage versions. You *can*
achieve what you want
by marking the proc as a system proc using sp_MS_marksystemobject (Google fo
r usage), but again, it
is not supported or documented.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lucjan" <Lucjan@.discussions.microsoft.com> wrote in message
news:BB44926A-3624-4F56-B47C-299338E2F222@.microsoft.com...
> Hello, I would like to create a stored procedure, which would manipulate d
ata
> in different databases, where all my databases have the same structure.
> I would expect , when this proc is called in DB1 to use tables from DB1,
> when called in DB2 .. tables from DB2 .. and so on.
> However, when I created my sample stored proc in master..
> create proc sp_sample as select * from table1
> and then I tried to call this proc in DB1.. even that DB1 had also table1
,
> stored procedure was selecting data from master database. I was expecting
it
> to use DB1..table1.
> I hope I am clear on what I am going to achieve, I would like to have one
> copy for each of my stored procedures, stored in master database instead
of
> 10' copies stored across all different databases.
> I would appreciate any tips on how this could be done.
> Lucjan
>|||The supported behavior of special procedures (sp_ in master) is to resolve
only system tables in the current database, but user tables in master.
There's an undocumented "feature" that causes the proc to resolve user
tables in the current database as well. To achieve this, you run:
EXEC dbo.sp_MS_marksystemobject 'dbo.sp_procname'
Tough I'd be careful from relying on such undocumented behavior in
production systems. You never know when support for such a "feature" will be
dropped.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Lucjan" <Lucjan@.discussions.microsoft.com> wrote in message
news:BB44926A-3624-4F56-B47C-299338E2F222@.microsoft.com...
> Hello, I would like to create a stored procedure, which would manipulate
> data
> in different databases, where all my databases have the same structure.
> I would expect , when this proc is called in DB1 to use tables from DB1,
> when called in DB2 .. tables from DB2 .. and so on.
> However, when I created my sample stored proc in master..
> create proc sp_sample as select * from table1
> and then I tried to call this proc in DB1.. even that DB1 had also table1
> ,
> stored procedure was selecting data from master database. I was expecting
> it
> to use DB1..table1.
> I hope I am clear on what I am going to achieve, I would like to have one
> copy for each of my stored procedures, stored in master database instead
> of
> 10' copies stored across all different databases.
> I would appreciate any tips on how this could be done.
> Lucjan
>|||Lucjan (Lucjan@.discussions.microsoft.com) writes:
> Hello, I would like to create a stored procedure, which would manipulate
> data in different databases, where all my databases have the same
> structure.
> I would expect , when this proc is called in DB1 to use tables from DB1,
> when called in DB2 .. tables from DB2 .. and so on.
> However, when I created my sample stored proc in master..
> create proc sp_sample as select * from table1
> and then I tried to call this proc in DB1.. even that DB1 had also
> table1 , stored procedure was selecting data from master database. I was
> expecting it to use DB1..table1.
> I hope I am clear on what I am going to achieve, I would like to have
> one copy for each of my stored procedures, stored in master database
> instead of 10' copies stored across all different databases.
You can do this in SQL 2000, but it is not supported. And I don't think
you can do it at all in SQL 2005, since there is a radical change how
stored procedures, system tables etc are stored in SQL 2005.
Keep your code under version control and write a script that can update
one or more databases. The script can be Perl, VB, VBscript or even a
BAT file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Thursday, March 22, 2012
Tuesday, March 20, 2012
Custom SQL library ?
Hi All
Does anyone know of a library for .Net that would allow to execute SQL
statements and scripts against SQL server and Oracle databases. The
general purpose will be to execute DDL statements, create procedures
tables etc, currently we use osql and sqlplus invocations with
parameters from .Net environment.
The ADO .Net has some limitations and I am unable to use it.
Tom.I guess it depends on what your reasons are for not being able to use
ADO.Net. I imagine that any other library would connect in a very similar
way.
"Tom" <mutobo@.interia.pl> wrote in message
news:1149579157.093610.119170@.i40g2000cwc.googlegroups.com...
> Hi All
> Does anyone know of a library for .Net that would allow to execute SQL
> statements and scripts against SQL server and Oracle databases. The
> general purpose will be to execute DDL statements, create procedures
> tables etc, currently we use osql and sqlplus invocations with
> parameters from .Net environment.
> The ADO .Net has some limitations and I am unable to use it.
> Tom.
>|||Tom
Well, you are going to use SELLECT/UPDATE/DELETE statements ONLY , I mean
not to use stored procedures.
BL passes "SELECT <> FROM Table" on DAL and it sends the statement to the
database
"Tom" <mutobo@.interia.pl> wrote in message
news:1149579157.093610.119170@.i40g2000cwc.googlegroups.com...
> Hi All
> Does anyone know of a library for .Net that would allow to execute SQL
> statements and scripts against SQL server and Oracle databases. The
> general purpose will be to execute DDL statements, create procedures
> tables etc, currently we use osql and sqlplus invocations with
> parameters from .Net environment.
> The ADO .Net has some limitations and I am unable to use it.
> Tom.
>|||Well as I said I will mainly use the library to transport DDL
statements. I would like to create procedures (oracle packages), tables
etc. This is not possible in ADO.NET due to for example script length
limitations ( 2000 chars, any table definition can be longer ) and for
oracle there is no support for ';' character as a statement
separator. I would like to take a script file that for example creates
a table with all references and successfully run it to the database ?
Tom.
Does anyone know of a library for .Net that would allow to execute SQL
statements and scripts against SQL server and Oracle databases. The
general purpose will be to execute DDL statements, create procedures
tables etc, currently we use osql and sqlplus invocations with
parameters from .Net environment.
The ADO .Net has some limitations and I am unable to use it.
Tom.I guess it depends on what your reasons are for not being able to use
ADO.Net. I imagine that any other library would connect in a very similar
way.
"Tom" <mutobo@.interia.pl> wrote in message
news:1149579157.093610.119170@.i40g2000cwc.googlegroups.com...
> Hi All
> Does anyone know of a library for .Net that would allow to execute SQL
> statements and scripts against SQL server and Oracle databases. The
> general purpose will be to execute DDL statements, create procedures
> tables etc, currently we use osql and sqlplus invocations with
> parameters from .Net environment.
> The ADO .Net has some limitations and I am unable to use it.
> Tom.
>|||Tom
Well, you are going to use SELLECT/UPDATE/DELETE statements ONLY , I mean
not to use stored procedures.
BL passes "SELECT <> FROM Table" on DAL and it sends the statement to the
database
"Tom" <mutobo@.interia.pl> wrote in message
news:1149579157.093610.119170@.i40g2000cwc.googlegroups.com...
> Hi All
> Does anyone know of a library for .Net that would allow to execute SQL
> statements and scripts against SQL server and Oracle databases. The
> general purpose will be to execute DDL statements, create procedures
> tables etc, currently we use osql and sqlplus invocations with
> parameters from .Net environment.
> The ADO .Net has some limitations and I am unable to use it.
> Tom.
>|||Well as I said I will mainly use the library to transport DDL
statements. I would like to create procedures (oracle packages), tables
etc. This is not possible in ADO.NET due to for example script length
limitations ( 2000 chars, any table definition can be longer ) and for
oracle there is no support for ';' character as a statement
separator. I would like to take a script file that for example creates
a table with all references and successfully run it to the database ?
Tom.
Thursday, March 8, 2012
custom group count and suppressed data leaving blank space
I wil set you up with my scenario.
I am making a CR for a medical facility. I need to pull data from our databases that includes patients over 65 years of age that are chronically ill and how many chronic conditions they have. I grouped the report by age, with the name of the patients and their chronic conditions listed under the drilled down age. So, for example, it shows 65 YEARS and once clicked a seperate report will open with the patient info in it.
My boss wants to know how many patients have chronic conditions and how old they are.
I need help with this little problem. I know it is somthing small. I have some suppressed data that wont fully disappear. It leaves a blank space in between the group sections. I was wondering how can I get rid of that blank space. Also I noticed that when you click on the blank space you can still open up the suppressed data section even if there is no shown data.
How can I make the data suppressed and not have that blank space? How can I get the count to show only the patients that have chronic conditions that are 65 years or older? If you can help please do so, THANKS!If you want to remove the blank space when suppressing, try suppressing the section instead of the field.
I am making a CR for a medical facility. I need to pull data from our databases that includes patients over 65 years of age that are chronically ill and how many chronic conditions they have. I grouped the report by age, with the name of the patients and their chronic conditions listed under the drilled down age. So, for example, it shows 65 YEARS and once clicked a seperate report will open with the patient info in it.
My boss wants to know how many patients have chronic conditions and how old they are.
I need help with this little problem. I know it is somthing small. I have some suppressed data that wont fully disappear. It leaves a blank space in between the group sections. I was wondering how can I get rid of that blank space. Also I noticed that when you click on the blank space you can still open up the suppressed data section even if there is no shown data.
How can I make the data suppressed and not have that blank space? How can I get the count to show only the patients that have chronic conditions that are 65 years or older? If you can help please do so, THANKS!If you want to remove the blank space when suppressing, try suppressing the section instead of the field.
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:
>
>
|||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 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:
|||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:
>
>
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:
>
>
|||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 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:
|||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:
>
>
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:
>
>
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:
>
>
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.
> >>
> >>
>
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.
> >>
> >>
>
Subscribe to:
Posts (Atom)