calculated fields w/user defined functions

Hi,
In the new application we are extensively using calculated fields that
consist of user defined functions that query multiple tables in the
database. Does anybody know of any limitations or drowbacks of using
user-defined functions in calculated fields (performance, locking, etc) Here
is an example of the most complex user defined function we have:
CREATE FUNCTION dbo.PayrollDetail_Subtotal
(
@.PayrollDetailID int,
@.CoverageType varchar(25)
)
RETURNS decimal(19,4) AS
BEGIN
DECLARE
@.AmountCap AS decimal(19,2),
@.AmountEffective as decimal (19,2),
@.AssignmentID as int,
@.PolicyID1 as int,
@.PolicyID2 as int,
@.PolicyTo1 as datetime,
@.PolicyFrom2 as datetime,
@.WorkCode AS int,
@.Result AS decimal(19,2),
@.PayrollFrom AS datetime,
@.PayrollTo AS datetime,
@.PayrollType AS varchar(50),
@.PayrollHeaderID AS int,
@.Composite1 AS decimal(19,2),
@.Composite2 AS decimal(19,2),
@.Territory1 AS int,
@.TerritoryMod1 as decimal(10,4),
@.WCRateID1 AS int,
@.WCRate1 AS decimal(19,4),
@.WCFactor1 AS varchar(50)
SET @.Result = 0
/* Payroll Header ID and dates*/
/* NULL for PayrollHeader is not allowed in tblPayrollDetail */
SELECT @.PayrollHeaderID = PayrollHeaderID, @.WorkCode=lkWorkCodeID,
@.AmountCap=AmountCap, @.AmountEffective = AmountEffective from
tblPayrollDetail where PayrollDetailID = @.PayrollDetailID;
/* NULL is not allowed for DateFrom, DateTo in tblPayrollHeader */
SELECT @.PayrollFrom = DateFrom, @.PayrollTo = DateTo, @.PayrollType = PayrollType, @.AssignmentID = AssignmentID from tblPayrollHeader WHERE
PayrollHeaderID = @.PayrollHeaderID
SELECT @.PolicyID1=CE.CoverageEntryID, @.Composite1 = CE.CompositeRating,
@.PolicyTo1 = CE.DateExpiration from tblCoverageAssignment A INNER JOIN
tblCoverageEntry CE ON A.CoverageEntryID = CE.CoverageEntryID AND
A.CoverageType=@.CoverageType WHERE
CE.DateEffective<=@.PayrollFrom AND @.PayrollFrom<=CE.DateExpiration
SELECT @.PolicyID2=CE.CoverageEntryID, @.Composite2 = CE.CompositeRating,
@.PolicyFrom2 = CE.DateEffective from tblCoverageAssignment A INNER JOIN
tblCoverageEntry CE ON A.CoverageEntryID = CE.CoverageEntryID AND
A.CoverageType=@.CoverageType WHERE
CE.DateEffective<=@.PayrollTo AND @.PayrollTo<=CE.DateExpiration
/* Composite Rating in tblCoverageEntry has default of 0 */
if (@.PolicyID1 is null) OR (@.Composite1 = 0) OR (@.PolicyID2 is null) OR
(@.Composite2=0)
return 0
ELSE
BEGIN
IF (@.PolicyID1 = @.PolicyID2)
/* ++++++CASE 1 : Payroll dates fall within the same policy year
+++++++*/
BEGIN
/* work code rate */
SELECT @.WCRateID1 = WorkCodeRateID, @.WCRate1 = RateAmount, @.WCFactor1 = Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID1 and
lkWorkCodeID = @.WorkCode
IF (@.WCRateID1 is null)
return 0
ELSE
BEGIN
IF @.WCFactor1 = 'Rate/100'
SELECT @.Result = @.WCRate1*0.01
ELSE
BEGIN
IF @.WCFactor1='Rate/1000'
SELECT @.Result = @.WCRate1*0.001
ELSE
SELECT @.Result = @.WCRate1
END
/* base */
IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
SELECT @.Result = @.Result*@.AmountEffective
IF (@.CoverageType = 'WC')
BEGIN
IF @.AmountCap =0
SELECT @.Result = @.Result * @.AmountEffective
ELSE
SELECT @.Result = @.Result * @.AmountCap
END
/* Territory */
SELECT @.Territory1 = A.lkTerritoryID from tblPayrollHeader PH INNER
JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
PH.PayrollHeaderID = @.PayrollHeaderID
if (@.Territory1 is null)
SELECT @.Result = @.Result * @.Composite1
ELSE
BEGIN
SELECT @.TerritoryMod1= Modifier from tblTerritoryModifier WHERE
lkTerritoryID = @.Territory1 and WorkCodeRateID = @.WCRateID1
IF (@.TerritoryMod1 is NULL) OR (@.TerritoryMod1=0)
SELECT @.Result = @.Result * @.Composite1
ELSE
SELECT @.Result = @.Result * @.Composite1 * @.TerritoryMod1
END
END
END
ELSE
/* IF Payroll falls under 2 policy years */
BEGIN
DECLARE
@.Territory2 AS int,
@.TerritoryMod2 as decimal(10,4),
@.WCRateID2 AS int,
@.WCRate2 AS decimal(19,4),
@.WCFactor2 AS varchar(50),
@.Result1 AS decimal(19,4),
@.Result2 AS decimal(19,4),
@.NoDays1 AS int,
@.NoDays2 AS int,
@.PayrollLength AS int
/* +++POLICY YEAR 1+++ */
/* work code rate */
SELECT @.WCRateID1 = WorkCodeRateID, @.WCRate1 = RateAmount, @.WCFactor1 = Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID1 and
lkWorkCodeID = @.WorkCode
IF (@.WCRateID1 is null)
SELECT @.Result1 = 0
ELSE
BEGIN
IF @.WCFactor1 = 'Rate/100'
SELECT @.Result1 = @.WCRate1*0.01
ELSE
BEGIN
IF @.WCFactor1='Rate/1000'
SELECT @.Result1 = @.WCRate1*0.001
ELSE
SELECT @.Result1 = @.WCRate1
END
/* base */
SELECT @.NoDays1= DATEDIFF(day,@.PayrollFrom,@.PolicyTo1)
SELECT @.NoDays2= DATEDIFF(day,@.PayrollTo,@.PolicyFrom2)
SELECT @.PayrollLength = @.NoDays1+@.NoDays2
/* no of days for Policy Year 1= DateFrom - DateExpiration for Policy
Year 1 */
IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
SELECT @.Result1 = @.Result1*((@.AmountEffective/@.PayrollLength)*@.NoDays1)
IF (@.CoverageType = 'WC')
BEGIN
IF @.AmountCap =0
SELECT @.Result1 = @.Result1*((@.AmountEffective/@.PayrollLength)*@.NoDays1)
ELSE
SELECT @.Result1 = @.Result1* ((@.AmountCap/@.PayrollLength)*@.NoDays1)
END
/* Territory */
SELECT @.Territory1 = A.lkTerritoryID from tblPayrollHeader PH INNER
JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
PH.PayrollHeaderID = @.PayrollHeaderID
if (@.Territory1 is null)
SELECT @.Result1 = @.Result1 * @.Composite1
ELSE
BEGIN
SELECT @.TerritoryMod1= Modifier from tblTerritoryModifier WHERE
lkTerritoryID = @.Territory1 and WorkCodeRateID = @.WCRateID1
IF (@.TerritoryMod1 is NULL) OR (@.TerritoryMod1=0)
SELECT @.Result1 = @.Result1 * @.Composite1
ELSE
SELECT @.Result1 = @.Result1 * @.Composite1 * @.TerritoryMod1
END
END
/* +++POLICY YEAR 2+++ */
/* work code rate */
SELECT @.WCRateID2 = WorkCodeRateID, @.WCRate2 = RateAmount, @.WCFactor2 = Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID2 and
lkWorkCodeID = @.WorkCode
IF (@.WCRateID2 is null)
SELECT @.Result2 = 0
ELSE
BEGIN
IF @.WCFactor2 = 'Rate/100'
SELECT @.Result2 = @.WCRate2*0.01
ELSE
BEGIN
IF @.WCFactor2='Rate/1000'
SELECT @.Result2 = @.WCRate2*0.001
ELSE
SELECT @.Result2 = @.WCRate2
END
/* base */
IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
SELECT @.Result2 = @.Result2*((@.AmountEffective/@.PayrollLength)*@.NoDays2)
IF (@.CoverageType = 'WC')
BEGIN
IF @.AmountCap =0
SELECT @.Result2 = @.Result2*((@.AmountEffective/@.PayrollLength)*@.NoDays2)
ELSE
SELECT @.Result2 = @.Result2* ((@.AmountCap/@.PayrollLength)*@.NoDays2)
END
/* Territory */
SELECT @.Territory2 = A.lkTerritoryID from tblPayrollHeader PH INNER
JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
PH.PayrollHeaderID = @.PayrollHeaderID
if (@.Territory2 is null)
SELECT @.Result2 = @.Result2 * @.Composite2
ELSE
BEGIN
SELECT @.TerritoryMod2= Modifier from tblTerritoryModifier WHERE
lkTerritoryID = @.Territory2 and WorkCodeRateID = @.WCRateID2
IF (@.TerritoryMod2 is NULL) OR (@.TerritoryMod2=0)
SELECT @.Result2 = @.Result2 * @.Composite2
ELSE
SELECT @.Result2 = @.Result2 * @.Composite2 * @.TerritoryMod2
END
END
SELECT @.Result = @.Result1 + @.Result2
END
END
return @.Result
ENDUser defined functions in computed columns perform pretty badly compared to
plain SQL. The reason is that behind the season the function works as a
cursor, all the work is done on a row-by-row basis. You probably won't
notice that much if you only have a few rows, but once you get to a largish
amount (say 1000+) performance will go out of the window.
I did some testing the other day and SELECT * on a table with around 8000
rows had a subsecond return time without a computed column with a UDF, but
20+ seconds with a computed column with a UDF. On that basis I decided to
redesign the solution so it would work without a computed column, which
involved creating a few new tables, and performance is now acceptable.
So, you can use it, but only if your table has a very small number of rows,
or if you have a larger table and you are sure you will never ever have a
table scan on that table.
"ilona" <ieshulman@.sseinc.com> wrote in message
news:etjJPzjRDHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi,
> In the new application we are extensively using calculated fields that
> consist of user defined functions that query multiple tables in the
> database. Does anybody know of any limitations or drowbacks of using
> user-defined functions in calculated fields (performance, locking, etc)
Here
> is an example of the most complex user defined function we have:
>
> CREATE FUNCTION dbo.PayrollDetail_Subtotal
> (
> @.PayrollDetailID int,
> @.CoverageType varchar(25)
> )
> RETURNS decimal(19,4) AS
> BEGIN
> DECLARE
> @.AmountCap AS decimal(19,2),
> @.AmountEffective as decimal (19,2),
> @.AssignmentID as int,
> @.PolicyID1 as int,
> @.PolicyID2 as int,
> @.PolicyTo1 as datetime,
> @.PolicyFrom2 as datetime,
> @.WorkCode AS int,
> @.Result AS decimal(19,2),
> @.PayrollFrom AS datetime,
> @.PayrollTo AS datetime,
> @.PayrollType AS varchar(50),
> @.PayrollHeaderID AS int,
> @.Composite1 AS decimal(19,2),
> @.Composite2 AS decimal(19,2),
> @.Territory1 AS int,
> @.TerritoryMod1 as decimal(10,4),
> @.WCRateID1 AS int,
> @.WCRate1 AS decimal(19,4),
> @.WCFactor1 AS varchar(50)
>
> SET @.Result = 0
> /* Payroll Header ID and dates*/
> /* NULL for PayrollHeader is not allowed in tblPayrollDetail */
> SELECT @.PayrollHeaderID = PayrollHeaderID, @.WorkCode=lkWorkCodeID,
> @.AmountCap=AmountCap, @.AmountEffective = AmountEffective from
> tblPayrollDetail where PayrollDetailID = @.PayrollDetailID;
> /* NULL is not allowed for DateFrom, DateTo in tblPayrollHeader */
> SELECT @.PayrollFrom = DateFrom, @.PayrollTo = DateTo, @.PayrollType => PayrollType, @.AssignmentID = AssignmentID from tblPayrollHeader WHERE
> PayrollHeaderID = @.PayrollHeaderID
> SELECT @.PolicyID1=CE.CoverageEntryID, @.Composite1 = CE.CompositeRating,
> @.PolicyTo1 = CE.DateExpiration from tblCoverageAssignment A INNER JOIN
> tblCoverageEntry CE ON A.CoverageEntryID = CE.CoverageEntryID AND
> A.CoverageType=@.CoverageType WHERE
> CE.DateEffective<=@.PayrollFrom AND @.PayrollFrom<=CE.DateExpiration
> SELECT @.PolicyID2=CE.CoverageEntryID, @.Composite2 = CE.CompositeRating,
> @.PolicyFrom2 = CE.DateEffective from tblCoverageAssignment A INNER JOIN
> tblCoverageEntry CE ON A.CoverageEntryID = CE.CoverageEntryID AND
> A.CoverageType=@.CoverageType WHERE
> CE.DateEffective<=@.PayrollTo AND @.PayrollTo<=CE.DateExpiration
> /* Composite Rating in tblCoverageEntry has default of 0 */
> if (@.PolicyID1 is null) OR (@.Composite1 = 0) OR (@.PolicyID2 is null) OR
> (@.Composite2=0)
> return 0
> ELSE
> BEGIN
> IF (@.PolicyID1 = @.PolicyID2)
> /* ++++++CASE 1 : Payroll dates fall within the same policy year
> +++++++*/
> BEGIN
> /* work code rate */
> SELECT @.WCRateID1 = WorkCodeRateID, @.WCRate1 = RateAmount, @.WCFactor1 => Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID1 and
> lkWorkCodeID = @.WorkCode
> IF (@.WCRateID1 is null)
> return 0
> ELSE
> BEGIN
> IF @.WCFactor1 = 'Rate/100'
> SELECT @.Result = @.WCRate1*0.01
> ELSE
> BEGIN
> IF @.WCFactor1='Rate/1000'
> SELECT @.Result = @.WCRate1*0.001
> ELSE
> SELECT @.Result = @.WCRate1
> END
> /* base */
> IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
> SELECT @.Result = @.Result*@.AmountEffective
> IF (@.CoverageType = 'WC')
> BEGIN
> IF @.AmountCap =0
> SELECT @.Result = @.Result * @.AmountEffective
> ELSE
> SELECT @.Result = @.Result * @.AmountCap
> END
> /* Territory */
> SELECT @.Territory1 = A.lkTerritoryID from tblPayrollHeader PH INNER
> JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
> PH.PayrollHeaderID = @.PayrollHeaderID
> if (@.Territory1 is null)
> SELECT @.Result = @.Result * @.Composite1
> ELSE
> BEGIN
> SELECT @.TerritoryMod1= Modifier from tblTerritoryModifier WHERE
> lkTerritoryID = @.Territory1 and WorkCodeRateID = @.WCRateID1
> IF (@.TerritoryMod1 is NULL) OR (@.TerritoryMod1=0)
> SELECT @.Result = @.Result * @.Composite1
> ELSE
> SELECT @.Result = @.Result * @.Composite1 * @.TerritoryMod1
> END
> END
> END
> ELSE
> /* IF Payroll falls under 2 policy years */
> BEGIN
> DECLARE
> @.Territory2 AS int,
> @.TerritoryMod2 as decimal(10,4),
> @.WCRateID2 AS int,
> @.WCRate2 AS decimal(19,4),
> @.WCFactor2 AS varchar(50),
> @.Result1 AS decimal(19,4),
> @.Result2 AS decimal(19,4),
> @.NoDays1 AS int,
> @.NoDays2 AS int,
> @.PayrollLength AS int
>
> /* +++POLICY YEAR 1+++ */
> /* work code rate */
> SELECT @.WCRateID1 = WorkCodeRateID, @.WCRate1 = RateAmount, @.WCFactor1 => Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID1 and
> lkWorkCodeID = @.WorkCode
> IF (@.WCRateID1 is null)
> SELECT @.Result1 = 0
> ELSE
> BEGIN
> IF @.WCFactor1 = 'Rate/100'
> SELECT @.Result1 = @.WCRate1*0.01
> ELSE
> BEGIN
> IF @.WCFactor1='Rate/1000'
> SELECT @.Result1 = @.WCRate1*0.001
> ELSE
> SELECT @.Result1 = @.WCRate1
> END
> /* base */
> SELECT @.NoDays1= DATEDIFF(day,@.PayrollFrom,@.PolicyTo1)
> SELECT @.NoDays2= DATEDIFF(day,@.PayrollTo,@.PolicyFrom2)
> SELECT @.PayrollLength = @.NoDays1+@.NoDays2
> /* no of days for Policy Year 1= DateFrom - DateExpiration for Policy
> Year 1 */
> IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
> SELECT @.Result1 => @.Result1*((@.AmountEffective/@.PayrollLength)*@.NoDays1)
> IF (@.CoverageType = 'WC')
> BEGIN
> IF @.AmountCap =0
> SELECT @.Result1 => @.Result1*((@.AmountEffective/@.PayrollLength)*@.NoDays1)
> ELSE
> SELECT @.Result1 = @.Result1* ((@.AmountCap/@.PayrollLength)*@.NoDays1)
> END
> /* Territory */
> SELECT @.Territory1 = A.lkTerritoryID from tblPayrollHeader PH INNER
> JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
> PH.PayrollHeaderID = @.PayrollHeaderID
> if (@.Territory1 is null)
> SELECT @.Result1 = @.Result1 * @.Composite1
> ELSE
> BEGIN
> SELECT @.TerritoryMod1= Modifier from tblTerritoryModifier WHERE
> lkTerritoryID = @.Territory1 and WorkCodeRateID = @.WCRateID1
> IF (@.TerritoryMod1 is NULL) OR (@.TerritoryMod1=0)
> SELECT @.Result1 = @.Result1 * @.Composite1
> ELSE
> SELECT @.Result1 = @.Result1 * @.Composite1 * @.TerritoryMod1
> END
>
> END
> /* +++POLICY YEAR 2+++ */
> /* work code rate */
> SELECT @.WCRateID2 = WorkCodeRateID, @.WCRate2 = RateAmount, @.WCFactor2 => Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID2 and
> lkWorkCodeID = @.WorkCode
> IF (@.WCRateID2 is null)
> SELECT @.Result2 = 0
> ELSE
> BEGIN
> IF @.WCFactor2 = 'Rate/100'
> SELECT @.Result2 = @.WCRate2*0.01
> ELSE
> BEGIN
> IF @.WCFactor2='Rate/1000'
> SELECT @.Result2 = @.WCRate2*0.001
> ELSE
> SELECT @.Result2 = @.WCRate2
> END
> /* base */
> IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
> SELECT @.Result2 => @.Result2*((@.AmountEffective/@.PayrollLength)*@.NoDays2)
> IF (@.CoverageType = 'WC')
> BEGIN
> IF @.AmountCap =0
> SELECT @.Result2 => @.Result2*((@.AmountEffective/@.PayrollLength)*@.NoDays2)
> ELSE
> SELECT @.Result2 = @.Result2* ((@.AmountCap/@.PayrollLength)*@.NoDays2)
> END
> /* Territory */
> SELECT @.Territory2 = A.lkTerritoryID from tblPayrollHeader PH INNER
> JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
> PH.PayrollHeaderID = @.PayrollHeaderID
> if (@.Territory2 is null)
> SELECT @.Result2 = @.Result2 * @.Composite2
> ELSE
> BEGIN
> SELECT @.TerritoryMod2= Modifier from tblTerritoryModifier WHERE
> lkTerritoryID = @.Territory2 and WorkCodeRateID = @.WCRateID2
> IF (@.TerritoryMod2 is NULL) OR (@.TerritoryMod2=0)
> SELECT @.Result2 = @.Result2 * @.Composite2
> ELSE
> SELECT @.Result2 = @.Result2 * @.Composite2 * @.TerritoryMod2
> END
> END
> SELECT @.Result = @.Result1 + @.Result2
> END
> END
> return @.Result
>
> END
>sql

Calculated fields in Matrix

Hi!

I have a calculated field in a dataset, Productivity, defined by this expression:

Fields!BCM.Value/Fields!MINUTES.Value

When I use it on a table, everything works fine. But when I try to use it on a matrix, I get an error
(#ERROR) on the field.
Why does it happen? Can't I use calculated fields on matrix?

Thank you!

Dear,

Sure , u will get the error beause u are using the field in matrix but when u define the function u are not passing the matrix refrence.

eg:

=iif(inscope(Sum(Fields!OrderQty.Value, "MatrixSource")),sum(Fields!OrderQty.Value),sum(Fields!OrderQty.Value)/Sum(Fields!OrderQty.Value, "MatrixSource"))

HTH

from

sufian

Calculated field in dataset using Previous() function

Hi,
Iam trying to add a calculated field to my report dataset which based on
value of a field in the previous row. The calculated field is defined as
=IIf(Previous(Fields!Col1.Value)=Fields!Col1.Value,0,CDbl(Fields!Col2.Value)+CDbl(Fields!Col3.Value))
. Iam basically checking the value of Col1 in previous row with value in
current row. Iam getting the following error when tried to build the report
"An internal error occurred on the report server. See the error log for more
details.". I could not find anything in error log.
Can anyone help in resolving this problem ?
Thanks,
RKOn Dec 3, 1:35 am, "S V Ramakrishna"
<ramakrishna.seeth...@.translogicsys.com> wrote:
> Hi,
> Iam trying to add a calculated field to my report dataset which based on
> value of a field in the previous row. The calculated field is defined as
> =IIf(Previous(Fields!Col1.Value)=Fields!Col1.Value,0,CDbl(Fields!Col2.Value-)+CDbl(Fields!Col3.Value))
> . Iam basically checking the value of Col1 in previous row with value in
> current row. Iam getting the following error when tried to build the report
> "An internal error occurred on the report server. See the error log for more
> details.". I could not find anything in error log.
> Can anyone help in resolving this problem ?
> Thanks,
> RK
I found that the Previous(Fields!XYZ.Value) function only works in the
Table and Matrix expressions -- expresions that are evaluated at
render time. Although it would make sense to have them in the DataSet
as a calculated field, I've not been able to get this to work.
Oracle has LAG and LEAD functions that return the previous/next value
of a Field as part of the query results. Microsoft T-SQL does not
have an equivalent.
-- Scottsql

Caching with Dynamic Security

I defined one role in my AS database with dynamic security for each dimension. I am accessing the AS database in an ASP.NET web application which runs under a domain user and uses Form Authentication. Therefore I always connect to the AS database under the same domain user even though security should be based on the user logged into the web application. When I run a MDX query, I pass along the web user's security info as part of the connectionstring and uses dynamic security to get the AllowedSet for each dimension. However, I notice my user defined function is ran only on the first time I run a query, meaning the dimension security is cached based on the domain user in the connection string and not the web user. Sorry if this sounds confusing but I can clarify a bit more if needed. My question boils down to: Is there a way to tell AS database to cache result base on the CustomData property of the connectionstring?

The answer is YES - AS is smart enough to recognize that different values of CustomData were used even though the real identity on the connection is the same. Since the main purpose of CustomData was for custom authentication - it is treated the same as different users. Same is true w.r.t. Roles and UserId properties.

HTH,

Mosha (http://www.mosha.com/msolap)

|||

Mosha,

As always, you are right and thanks for the help. I did a little more testing after my post and realized AS is caching the result base on the CustomData property. Thanks!

C# user defined function: Is there a way to access the name of the sql server?

This is my problem: I do not know how to get the servername from a C# user defined function . Is this possible?

I am writing a User Defined Function (UDF). Inside of this user defined function I need the name of the databaseserver that it is running on. Does anyone have an idea how I might do this? Is there an enviromental variable that I could access within the C# code I use to write the UDF?

I could always use a parameter to pass in the name of the server, but I would like to have as few parameters as possible.

Thanks in advance,

Sean

? Use the context connection and do: SELECT @.@.SERVERNAME -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Sean Finn@.discussions.microsoft..com> wrote in message news:b8ac5ded-8e34-4923-a0d9-2d01409c9c0d_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com This is my problem: I do not know how to get the servername from a C# user defined function . Is this possible? I am writing a User Defined Function (UDF). Inside of this user defined function I need the name of the databaseserver that it is running on. Does anyone have an idea how I might do this? Is there an enviromental variable that I could access within the C# code I use to write the UDF? I could always use a parameter to pass in the name of the server, but I would like to have as few parameters as possible. Thanks in advance, Sean|||Or without issueing a command,you could use just:

SqlConnection conn = new SqlConnection("context connection=true");
conn.Open();
// conn.Database --> hold the string of the database name
conn.Close();

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de