Create a user defined function in a user agnostic way

All,
I have two functions where one calls the other:
-- simplified example
create function plus(@.a as integer, @.b as integer)
returns integer
begin
return @.a + @.b
end
go
create function t(@.a as integer)
returns integer
begin
return cr_user.plus(10, @.a)
end
go
Is there any way to create those without explicitely mentioning the
database user ("cr_user" in this case)? Ideally I would like to put them
into a file and execute it with osql and those functions are created for
the current user. Until now I could not find a working placeholder to use
in "t" that will call the current user's "plus". (SUSER_SNAME does not
work.)
The only alternative seems to be ATM to always create functions for dbo
which is always defined.
Any ideas? TIA
Kind regards
robertWhy would you need functions that are owned by specific users? Does the
purpose of these functions depend on individual users?
Make it a practice to always qualify object names with the owner.
ML|||ML wrote:
> Why would you need functions that are owned by specific users?
You can only use functions with the owner as prefix and other parts of the
software insert the user as prefix when generating queries.

> Does
> the purpose of these functions depend on individual users?
No, other than that it is only used by a single user.

> Make it a practice to always qualify object names with the owner.
Why? To avoid name clashes? I prefer the unqualified version. It avoids
a lot of hassle and we don't have to take care of name clashes.
Regards
robert|||"Name clashes"?
There are no name clashes if common objects have the default owner (dbo). If
you created these obviously common functions with a qualified name (using db
o
as the owner), you could prevent any problems.
The pros of using qualified names are simply too numerous to mention here.
In SQL 2005 the owner is replaced by a more powerful quaifier - the schema -
maybe you should look into it to better understand its purpose.
I'm not criticizing your standards, but maybe they aren't as perferct as
you'd like to believe.
Please explain with an example why you need to create objects for every
user, when all of the objects are equal in purpose? And what do you mean by
"only used by a single user"? Is this a security issue?
ML|||ML wrote:
> "Name clashes"?
> There are no name clashes if common objects have the default owner
> (dbo).
But there would be if multiple objects with the same name but different
owners existed. I assumed you were referring to this situation when
promoting qualified names.

> If you created these obviously common functions with a
> qualified name (using dbo as the owner), you could prevent any
> problems.
> The pros of using qualified names are simply too numerous to mention
> here.
Impressive. Care to reveal some detail here? All I found so far was
faster access to execution plans and better reuse of execution plans of
stored procedures when called with qualified names.

> In SQL 2005 the owner is replaced by a more powerful quaifier - the
> schema - maybe you should look into it to better understand its
> purpose.
I might if I have the time. But ATM we're on 2k so I wouldn't want to
complicate things by mixing in concepts we can't use anyway. :-)

> I'm not criticizing your standards, but maybe they aren't as perferct
> as you'd like to believe.
I'm far from believing they are perfect. They just exist. And there is
some effort attached to changing them. Hence my conservatism. :-)

> Please explain with an example why you need to create objects for
> every user, when all of the objects are equal in purpose? And what do
> you mean by "only used by a single user"? Is this a security issue?
I don't create the same objects for every user. There is just one (1)
database user (apart from dbo). All tables, views, procedures and
functions are created unqualified and thus belong to this single user.
I'd like to stick with that simple policy. Unfortunately you can create
functions unqualified but you have to invoke them qualified. I don't see
why this is or has to be but apparently it's the way things are. Do you
have an idea why they did it this way?
I'll check whether the privileges our app requires are sufficient to
create the function for user dbo. That seems the second best alternative.
Hm, it seems creating is ok but invoking is prohibited. Darn. Seems like
I have to go down the preprocessing road.
Cheers
robert|||Ok, simply put most benefits of using qualified names are related to
performance. As you've mentioned. Another might be context of use, yet in
your case I see no need for that, since you in fact deal with a single user
(owner). In a multi role environment it could provide more clarity if method
s
that basically serve the same purpose for a variety of roles need to provide
special context-related features, which can be simplified using different
owners while keeping one name. E.g. a procedure to get product details might
return a different result for owner Engineering and a different one for owne
r
Marketing.
If you are using a single owner, why not use dbo?
But on the other hand, your only issue is with invoking functions, so either
way using a qualified name is mandatory. Simply because MS SQL Server says
so. :)
ML|||ML wrote:
> Ok, simply put most benefits of using qualified names are related to
> performance. As you've mentioned. Another might be context of use,
> yet in your case I see no need for that, since you in fact deal with
> a single user (owner). In a multi role environment it could provide
> more clarity if methods that basically serve the same purpose for a
> variety of roles need to provide special context-related features,
> which can be simplified using different owners while keeping one
> name. E.g. a procedure to get product details might return a
> different result for owner Engineering and a different one for owner
> Marketing.
Ok, thanks for clarifying.

> If you are using a single owner, why not use dbo?
I would but it seems the permissions we require for the DB user are
sufficient to create a function with owner dbo but not to invoke it later.
For the time being I'll stick with the preprocessing (i.e. the actual user
name is injected before SQL is executed).

> But on the other hand, your only issue is with invoking functions, so
> either way using a qualified name is mandatory. Simply because MS SQL
> Server says so. :)
Yep. Still I don't have an idea why this is so - I suspect the words
"historic" and "legacy" would be used in an explanation... :-)
Again, thanks for helping out!
Kind regards
robert

create a local (portable) app plus data

We have an Access app that uses tables from an SQL server. Is it possible to
copy the SQL tables localy into the Access mdb file and run the app locally?
What about table relationships? How can I tell if they might be an issue?
Some relationships are built in the diagram tool on the server, and some are
coded into the app itself.
Thanks a heap - Randy
Randy,
Why don't you use MSDE (http://www.microsoft.com/sql/msde/default.mspx )
instead of Access? MDSE is SQL Server, desktop edition, and it is free. This
way you could have exactly the same structure in local databases as you have
on SQL Server.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"RandyNesst" <RandyNesst@.discussions.microsoft.com> wrote in message
news:1382ED81-4064-4B05-956C-7DD5EF2D08FA@.microsoft.com...
> We have an Access app that uses tables from an SQL server. Is it possible
> to
> copy the SQL tables localy into the Access mdb file and run the app
> locally?
> What about table relationships? How can I tell if they might be an issue?
> Some relationships are built in the diagram tool on the server, and some
> are
> coded into the app itself.
> Thanks a heap - Randy
|||That sounds like a really good idea! I did a little looking around at the
link you gave me, but I have maybe a simple question - what is the process
like to create the MSDE localized database? Just an overview - is it about a
1 hour thing or about a 1 or more day thing? Is there a tool to do it?
Thanks again,
Randy
"Dejan Sarka" wrote:

> Randy,
> Why don't you use MSDE (http://www.microsoft.com/sql/msde/default.mspx )
> instead of Access? MDSE is SQL Server, desktop edition, and it is free. This
> way you could have exactly the same structure in local databases as you have
> on SQL Server.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
> "RandyNesst" <RandyNesst@.discussions.microsoft.com> wrote in message
> news:1382ED81-4064-4B05-956C-7DD5EF2D08FA@.microsoft.com...
>
>
|||Randy,
I am not quite sure whether I understand the question. If you mean the
process of creating a local copy of the server database, it is quite simple:
you can script all objects from the server db in Enterprise Manager and then
execute the script in Query Analyzer on the MSDE, and you have a local copy
with the same structure, but without data. if you need a copy of the data as
well, you can use Data Transformation Services, Merge Replication, ...
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"RandyNesst" <RandyNesst@.discussions.microsoft.com> wrote in message
news:62706E3A-F34B-4ABF-AA75-11600E920014@.microsoft.com...[vbcol=seagreen]
> That sounds like a really good idea! I did a little looking around at the
> link you gave me, but I have maybe a simple question - what is the process
> like to create the MSDE localized database? Just an overview - is it
> about a
> 1 hour thing or about a 1 or more day thing? Is there a tool to do it?
> Thanks again,
> Randy
> "Dejan Sarka" wrote:
|||At this point, you can download SQL Express 2005 (free also) and distro that
with your app.
William Stacey [MVP]
"RandyNesst" <RandyNesst@.discussions.microsoft.com> wrote in message
news:62706E3A-F34B-4ABF-AA75-11600E920014@.microsoft.com...[vbcol=seagreen]
> That sounds like a really good idea! I did a little looking around at the
> link you gave me, but I have maybe a simple question - what is the process
> like to create the MSDE localized database? Just an overview - is it
> about a
> 1 hour thing or about a 1 or more day thing? Is there a tool to do it?
> Thanks again,
> Randy
> "Dejan Sarka" wrote:

Counting parent records and displaying Total?

We have an helpdesk sytem on SQL 2000. I am trying to show how much calls have been assigned to each parent category plus it's child categories in a single row. The thing is when I run my query it display parent and child categories and each on it's own rows. I do get the call totals for each row, but I would like to add the totals of the rows together and display it on row. The new table must have then 6 rows (because there is 6 parent categories) with the total of all calls for that parent category, as well as it's childs. Example:

parameter # Calls
---- ---
desktop\pp 5
desktop\qq 6 {This is what I am getting at this stage}
desktop\tt 4
network\rr 9
network\gg 10
software\vv 3

This is what I would like to have:

parameter # Calls
---- ---
desktop 15
network 19
software 3

Please Help!select p.*, c.*, (p.amt+c.amt) as "Total Amt"
from parent p
INNER JOIN
(select id, sum(amt) as amt
from child c
group by id) c ON
p.id = c.id;|||Sorry, but I Forgot to mention that the second column is a count of the occurances in the 1st column. The query has to be run on only one field.

I am attaching my query:

SELECT workitem_category_tree_value,
count(*) as '# Work Items'

FROM dbo.workitem_detail_view hd1
WHERE hd1.[workitem_is_last] = 1
AND UPPER(hd1.[workitem_category_tree_value]) LIKE UPPER('%')

GROUP BY hd1.[workitem_category_tree_value]

Then this result is diplayed:

Desktop 8
Desktop\Administration 12
Desktop\Administration\Reset Password 42
Desktop\Administration\Unlock Account 30
Desktop\Notebook\LAN 1
Desktop\Notebook\LAN\Join to Domain 2
Desktop\Notebook\LAN\Network Connection 1
Desktop\Notebook\RAS 1
Desktop\Notebook\RAS\Configure 5