built-in functions - owner required? (newbie)

Hi All,
Ingres has some functions for converting UUIDs. Specifically, they are
uuid_to_char() and uuid_from_char(). I'm new to MS SQL and would like
to write some built-in functions (or any other solution) that will
allow me to use the same code in MS SQL and Ingres.
Example:
select uuid_to_char(xyz_uuid) from xyz
I have written a simple built-in function to do this and it works just
fine. The one fly in the ointment is that the "database owner" seems
to be required to execute it.
"select uuid_to_char(xyz_uuid) from xyz" yields "'uuid_to_char' is not
a recognized function name".
"select dbo.uuid_to_char(xyz_uuid) from xyz" works just fine.
I have found in the documentation where it says that the owner is
required in this case.
Is there some other technique available to eliminate use of the owner
name?
Thanks!
Troy RudolphThere is, but it's undocumented/unsupported, and I guess that you wouldn't
want to rely on such a technique:
- create the function in master
- function name must start with fn_
- owner must be system_function_schema
- name must be all lowercase
- function should be created when the 'allow updates' server configuration
option is turned on
If you follow all the above requirements, you don't need to owner qualify
it, plus you can invoke it from any database. But again, I wouldn't rely on
an unsupported technique in a production environment.
The only supported way to invoke a scalar UDF in a query is to owner qualify
it, I'm afraid.
BG, SQL Server MVP
www.SolidQualityLearning.com
<Troy.Rudolph@.ca.com> wrote in message
news:1125340946.797761.324100@.z14g2000cwz.googlegroups.com...
> Hi All,
> Ingres has some functions for converting UUIDs. Specifically, they are
> uuid_to_char() and uuid_from_char(). I'm new to MS SQL and would like
> to write some built-in functions (or any other solution) that will
> allow me to use the same code in MS SQL and Ingres.
> Example:
> select uuid_to_char(xyz_uuid) from xyz
> I have written a simple built-in function to do this and it works just
> fine. The one fly in the ointment is that the "database owner" seems
> to be required to execute it.
> "select uuid_to_char(xyz_uuid) from xyz" yields "'uuid_to_char' is not
> a recognized function name".
> "select dbo.uuid_to_char(xyz_uuid) from xyz" works just fine.
> I have found in the documentation where it says that the owner is
> required in this case.
> Is there some other technique available to eliminate use of the owner
> name?
> Thanks!
> Troy Rudolph
>|||Thank you for the quick reply.
You mentioned that the function name has to start with "fn_". It that
true for invocation as well as definition? In other words, do I have
to invoke it like this:
select fn_uuid_to_char(xyz_uuid) from uuid
If so, it really won't help.
Thanks again
Troy|||> You mentioned that the function name has to start with "fn_". It that
> true for invocation as well as definition? In other words, do I have
> to invoke it like this:
Yep.

> If so, it really won't help.
Probably for the best. ;-)
BG, SQL Server MVP
www.SolidQualityLearning.com
<Troy.Rudolph@.ca.com> wrote in message
news:1125342504.627559.165260@.o13g2000cwo.googlegroups.com...
> Thank you for the quick reply.
> You mentioned that the function name has to start with "fn_". It that
> true for invocation as well as definition? In other words, do I have
> to invoke it like this:
> select fn_uuid_to_char(xyz_uuid) from uuid
> If so, it really won't help.
> Thanks again
> Troy
>|||Yes, defined as fn_myfunction, then called as fn_myfunction. There is no
shortcut.
I really would not recommend it for any custom database. Maybe, in some
exceptional situations I would use it for very generic functions that
Microsoft should have provided out of the box, never for customer or
business specific functions.
Reasons not to take this road:
- You have to do a lot of 'special' stuff. And everyone who ever has to
maintain this function needs to know all this. It would definitely add
to the cost of maintaining the software
- It is undocumented and therefore unsupported. Although unlikely, its
behavior could change with the next service pack
- Microsoft could decide to add system function and 'accidentally'
choose the same function name as you did
Gert-Jan
Troy.Rudolph@.ca.com wrote:
> Thank you for the quick reply.
> You mentioned that the function name has to start with "fn_". It that
> true for invocation as well as definition? In other words, do I have
> to invoke it like this:
> select fn_uuid_to_char(xyz_uuid) from uuid
> If so, it really won't help.
> Thanks again
> Troy

Building out sums inside of a table (SSRS 2000)

Hi all,
I am converting some Crystal reports... and replacing formulas which
are automatically grouped in Crystal. Essentially, I am comparing two
different date ranges, and need to be able to know how many items were
shipped for each customer/item combination within each date range.
I have created calculated fields to hold the quantity shipped if that
row falls in the appropriate date range... this just adds the quantity
shipped in the field if it falls within the appropriate date range.
Qty2ShipPD1=IIF(( Parameters!startInvoicePD1.Value< Fields!
inv_dt.Value AND Parameters!EndInvoicePD1.Value> Fields!inv_dt.Value),
Fields!qty_to_ship.Value,0.0)
and
Qty2ShipPD2=IIF(( Parameters!startInvoicePD2.Value< Fields!
inv_dt.Value AND Parameters!EndInvoicePD2.Value> Fields!inv_dt.Value),
Fields!qty_to_ship.Value,0.0)
I need to build out a table that looks close to the following example:
Cust No
Item No Total for the Period
{need total quantity here}
PD1 {Qty2ShipPD1}
PD2 {Qty2ShipPD2}
I created a matrix control and dragged cus_no, item_no into the rows,
with item_no in the columns, and then summed the Qty2ShipPD1 in the
Details...
This works, in the sense that I am actually getting grouped
information which is correct for the customer/item combinations...
But the format won't work, and I have no idea how to do the same thing
in a table. Every time I do it in a table, I just get the same,
entire sum for the entire query for the two calculated fields.
Help?
THANKS!
JoshAlternatively - is there a way I can use a Matrix Control with only 1
column? If so, I might be able to live with that.
Thanks,
Josh
On Apr 23, 5:57 pm, rumplyminz <squa...@.gmail.com> wrote:
> Hi all,
> I am converting some Crystal reports... and replacing formulas which
> are automatically grouped in Crystal. Essentially, I am comparing two
> different date ranges, and need to be able to know how many items were
> shipped for each customer/item combination within each date range.
> I have created calculated fields to hold the quantity shipped if that
> row falls in the appropriate date range... this just adds the quantity
> shipped in the field if it falls within the appropriate date range.
> Qty2ShipPD1=IIF(( Parameters!startInvoicePD1.Value< Fields!
> inv_dt.Value AND Parameters!EndInvoicePD1.Value> Fields!inv_dt.Value),
> Fields!qty_to_ship.Value,0.0)
> and
> Qty2ShipPD2=IIF(( Parameters!startInvoicePD2.Value< Fields!
> inv_dt.Value AND Parameters!EndInvoicePD2.Value> Fields!inv_dt.Value),
> Fields!qty_to_ship.Value,0.0)
> I need to build out a table that looks close to the following example:
> Cust No
> Item No Total for the Period
> {need total quantity here}
> PD1 {Qty2ShipPD1}
> PD2 {Qty2ShipPD2}
> I created a matrix control and dragged cus_no, item_no into the rows,
> with item_no in the columns, and then summed the Qty2ShipPD1 in the
> Details...
> This works, in the sense that I am actually getting grouped
> information which is correct for the customer/item combinations...
> But the format won't work, and I have no idea how to do the same thing
> in a table. Every time I do it in a table, I just get the same,
> entire sum for the entire query for the two calculated fields.
> Help?
> THANKS!
> Josh|||Doh! Got it.
In my
Sum statement, I was referencing the entire dataset, and not just the
group. I just didn't know I could reference the group.
*This* works...
=Sum(Fields!qty_to_ship.Value, "table1_Group1")
*This does not (well, it gives me values for the entire dataset)
=Sum(Fields!qty_to_ship.Value, "devdbds")
FYI. Hopefully this will help someone else.
THANKS
On Apr 24, 9:44 am, rumplyminz <squa...@.gmail.com> wrote:
> Alternatively - is there a way I can use a Matrix Control with only 1
> column? If so, I might be able to live with that.
> Thanks,
> Josh
> On Apr 23, 5:57 pm, rumplyminz <squa...@.gmail.com> wrote:
> > Hi all,
> > I am converting some Crystal reports... and replacing formulas which
> > are automatically grouped in Crystal. Essentially, I am comparing two
> > different date ranges, and need to be able to know how many items were
> > shipped for each customer/item combination within each date range.
> > I have created calculated fields to hold the quantity shipped if that
> > row falls in the appropriate date range... this just adds the quantity
> > shipped in the field if it falls within the appropriate date range.
> > Qty2ShipPD1=IIF(( Parameters!startInvoicePD1.Value< Fields!
> > inv_dt.Value AND Parameters!EndInvoicePD1.Value> Fields!inv_dt.Value),
> > Fields!qty_to_ship.Value,0.0)
> > and
> > Qty2ShipPD2=IIF(( Parameters!startInvoicePD2.Value< Fields!
> > inv_dt.Value AND Parameters!EndInvoicePD2.Value> Fields!inv_dt.Value),
> > Fields!qty_to_ship.Value,0.0)
> > I need to build out a table that looks close to the following example:
> > Cust No
> > Item No Total for the Period
> > {need total quantity here}
> > PD1 {Qty2ShipPD1}
> > PD2 {Qty2ShipPD2}
> > I created a matrix control and dragged cus_no, item_no into the rows,
> > with item_no in the columns, and then summed the Qty2ShipPD1 in the
> > Details...
> > This works, in the sense that I am actually getting grouped
> > information which is correct for the customer/item combinations...
> > But the format won't work, and I have no idea how to do the same thing
> > in a table. Every time I do it in a table, I just get the same,
> > entire sum for the entire query for the two calculated fields.
> > Help?
> > THANKS!
> > Josh