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

calculate and return % in Stored Procedure

This is my SP:

SELECT
CAST(id AS varchar(10)) + ' - ' + UserName AS 'User List',
Scanned,
Scripts AS 'Total Scripts',
Processed,
CAST((Processed/ Scripts)* 100.0 as int) AS 'DONE (%)'
FROM tblworkQueue

Processed and Scripts are both DataTypes of int
My DONE(%) column comes back with 0

Any idea where I am going wrong?

thats because you are CASTing it as INT, if the value is < 1 it will be 0. Try to cast it as decimal(10,2).|||

It is could be also if both your fields you use for dividing you useCAST((Processed/ Scripts)* 100.0 as int) processed and Scripts are integer values and I assume that processed is smaller so when you divide integers result is also integer so you get something below 1 and it is rounded to 0. try to use

CAST((100.00 *Processed/ Scripts) as int)

maybe it will work better?

CAL Licensing and user limitations?

Hi to everyone, probably it's a faq but I did not find a sure answer.

A customer has a Sql Server 2000 standard installed in 1server/5CAL
licensing mode, in a windows 2000 server.
Does this type of installation limit the further connections (occourred in
same or distinct sql accounts) that exceed the 5 client/user?
And if this connections aren't limited, are these further connections
penalized by the query governor like MSDE does?

In short, is the CAL licensing mode only a legal issue without affecting or
limiting the performance of the exceeding connections?

Thanks in advance,
Pas!Pashkuale (sorry@.nomail.com) writes:
> Hi to everyone, probably it's a faq but I did not find a sure answer.
> A customer has a Sql Server 2000 standard installed in 1server/5CAL
> licensing mode, in a windows 2000 server.
> Does this type of installation limit the further connections (occourred in
> same or distinct sql accounts) that exceed the 5 client/user?
> And if this connections aren't limited, are these further connections
> penalized by the query governor like MSDE does?
> In short, is the CAL licensing mode only a legal issue without affecting
> or limiting the performance of the exceeding connections?

It's only a legal issue.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

CAL License Question

Under the CAL user licensing model, does each user that has a login to a SQL
Server 2005 database need a CAL, or is one needed for each simultaneous
login? For instance if I have 100 users with access to a SQL database, but
at any given time there are only 25 users accessing a database, do I need 10
0
user CAL's or 25 user CAL's?Wade Bart wrote:
> Under the CAL user licensing model, does each user that has a login to a S
QL
> Server 2005 database need a CAL, or is one needed for each simultaneous
> login? For instance if I have 100 users with access to a SQL database, bu
t
> at any given time there are only 25 users accessing a database, do I need
100
> user CAL's or 25 user CAL's?
It is per user or device, not per concurrent connection.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

CAL License Question

Under the CAL user licensing model, does each user that has a login to a SQL
Server 2005 database need a CAL, or is one needed for each simultaneous
login? For instance if I have 100 users with access to a SQL database, but
at any given time there are only 25 users accessing a database, do I need 100
user CAL's or 25 user CAL's?Wade Bart wrote:
> Under the CAL user licensing model, does each user that has a login to a SQL
> Server 2005 database need a CAL, or is one needed for each simultaneous
> login? For instance if I have 100 users with access to a SQL database, but
> at any given time there are only 25 users accessing a database, do I need 100
> user CAL's or 25 user CAL's?
It is per user or device, not per concurrent connection.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

CAL

I know CAL stands for Client access license, does this mean if I have an
application that logs onto SQL as a generic user, is that one CAL? Do I need
a CAL for each of my application generic users? Des this go for each user I
have in SQL?
TIA,
JoeA CAL covers a connection to the DB, so whether your using a generic login
or user specific logins each is a seperate CAL.|||A CAL covers either a user or a device that connects to SQL. The gotcha is
that only end users or devices count. If you have a web server or other
device that collects end users and feeds them through a single device
connections, then you have to license each end user, not just the single
middle-ware device. Licensed users can have multiple connections so there
is not any relationship between licensed users and connections. If you have
a situation where you cannot enumerate each licensed user, such as a
public-facing web site, then you must license SQLnon a per-processor basis.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:DBCCB86D-7367-4AC5-9B9E-440CA72C31C1@.microsoft.com...
>I know CAL stands for Client access license, does this mean if I have an
> application that logs onto SQL as a generic user, is that one CAL? Do I
> need
> a CAL for each of my application generic users? Des this go for each user
> I
> have in SQL?
> TIA,
> Joe
>|||Thank you.
This was very helpful.
Joe
"Geoff N. Hiten" wrote:
> A CAL covers either a user or a device that connects to SQL. The gotcha is
> that only end users or devices count. If you have a web server or other
> device that collects end users and feeds them through a single device
> connections, then you have to license each end user, not just the single
> middle-ware device. Licensed users can have multiple connections so there
> is not any relationship between licensed users and connections. If you have
> a situation where you cannot enumerate each licensed user, such as a
> public-facing web site, then you must license SQLnon a per-processor basis.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:DBCCB86D-7367-4AC5-9B9E-440CA72C31C1@.microsoft.com...
> >I know CAL stands for Client access license, does this mean if I have an
> > application that logs onto SQL as a generic user, is that one CAL? Do I
> > need
> > a CAL for each of my application generic users? Des this go for each user
> > I
> > have in SQL?
> >
> > TIA,
> > Joe
> >
>
>|||Thank you,
This was very helpful
Joe
"BenUK" wrote:
> A CAL covers a connection to the DB, so whether your using a generic login
> or user specific logins each is a seperate CAL.

CAL

I know CAL stands for Client access license, does this mean if I have an
application that logs onto SQL as a generic user, is that one CAL? Do I nee
d
a CAL for each of my application generic users? Des this go for each user I
have in SQL?
TIA,
JoeA CAL covers a connection to the DB, so whether your using a generic login
or user specific logins each is a seperate CAL.|||A CAL covers either a user or a device that connects to SQL. The gotcha is
that only end users or devices count. If you have a web server or other
device that collects end users and feeds them through a single device
connections, then you have to license each end user, not just the single
middle-ware device. Licensed users can have multiple connections so there
is not any relationship between licensed users and connections. If you have
a situation where you cannot enumerate each licensed user, such as a
public-facing web site, then you must license SQLnon a per-processor basis.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:DBCCB86D-7367-4AC5-9B9E-440CA72C31C1@.microsoft.com...
>I know CAL stands for Client access license, does this mean if I have an
> application that logs onto SQL as a generic user, is that one CAL? Do I
> need
> a CAL for each of my application generic users? Des this go for each user
> I
> have in SQL?
> TIA,
> Joe
>|||Thank you.
This was very helpful.
Joe
"Geoff N. Hiten" wrote:

> A CAL covers either a user or a device that connects to SQL. The gotcha i
s
> that only end users or devices count. If you have a web server or other
> device that collects end users and feeds them through a single device
> connections, then you have to license each end user, not just the single
> middle-ware device. Licensed users can have multiple connections so there
> is not any relationship between licensed users and connections. If you ha
ve
> a situation where you cannot enumerate each licensed user, such as a
> public-facing web site, then you must license SQLnon a per-processor basis
.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:DBCCB86D-7367-4AC5-9B9E-440CA72C31C1@.microsoft.com...
>
>|||Thank you,
This was very helpful
Joe
"BenUK" wrote:

> A CAL covers a connection to the DB, so whether your using a generic login
> or user specific logins each is a seperate CAL.

CAL

I know CAL stands for Client access license, does this mean if I have an
application that logs onto SQL as a generic user, is that one CAL? Do I need
a CAL for each of my application generic users? Des this go for each user I
have in SQL?
TIA,
Joe
A CAL covers a connection to the DB, so whether your using a generic login
or user specific logins each is a seperate CAL.
|||A CAL covers either a user or a device that connects to SQL. The gotcha is
that only end users or devices count. If you have a web server or other
device that collects end users and feeds them through a single device
connections, then you have to license each end user, not just the single
middle-ware device. Licensed users can have multiple connections so there
is not any relationship between licensed users and connections. If you have
a situation where you cannot enumerate each licensed user, such as a
public-facing web site, then you must license SQLnon a per-processor basis.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:DBCCB86D-7367-4AC5-9B9E-440CA72C31C1@.microsoft.com...
>I know CAL stands for Client access license, does this mean if I have an
> application that logs onto SQL as a generic user, is that one CAL? Do I
> need
> a CAL for each of my application generic users? Des this go for each user
> I
> have in SQL?
> TIA,
> Joe
>
|||Thank you.
This was very helpful.
Joe
"Geoff N. Hiten" wrote:

> A CAL covers either a user or a device that connects to SQL. The gotcha is
> that only end users or devices count. If you have a web server or other
> device that collects end users and feeds them through a single device
> connections, then you have to license each end user, not just the single
> middle-ware device. Licensed users can have multiple connections so there
> is not any relationship between licensed users and connections. If you have
> a situation where you cannot enumerate each licensed user, such as a
> public-facing web site, then you must license SQLnon a per-processor basis.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:DBCCB86D-7367-4AC5-9B9E-440CA72C31C1@.microsoft.com...
>
>
|||Thank you,
This was very helpful
Joe
"BenUK" wrote:

> A CAL covers a connection to the DB, so whether your using a generic login
> or user specific logins each is a seperate CAL.

caching problem in sql reporting services

Hi all,
I have developed a web application which displays records stored in the database based on certain criteria supplied by the user. I have used SQL Reporting services for generating a report coresponding to the displayed records. The problem I faces is as follows.
I generated a report for the records displayed in the application. Then, I updated one of the records and again generated the report. But the change I made is not reflected in it. The report is the same as the one previously generated. But the change is reflected if I logged out of the application, again logged in and generated the report.
I checked the 'Execution properties' of the report in the Report Manager and I found that the 'Do not cache temporary copies of this report' option remains selected. But the caching problem exists. Please help if anyone knows a solution. I am desperately in need of a solution for the above problem.

Thanks,

Renju.

Had similar problem with SRS 2000 (are you using same version).

Resolved by creating a dummy parameter that passed the current date/time - forcing a new dataset everytime...

Cache: rsInvalidDataSourceCredentialSetting

Error-Message:
"The current action cannot be completed because the user data source
credentials that are required to execute this report are not stored in the
report server database. (rsInvalidDataSource-CredentialSetting) (Report
Services SOAP Proxy Source)".
Our previous action:
In SQL-Server Management Studio, Connect to Reporting Services, Home,
myReports, someReportName.
Right-Click on this report, Properties.
Here on the left side: Execution
Right Side: Cache the Report, OK
Now happens the Error cited above.
Logged on as Local Adminsitrator, as usual, no Domain configured, standalone
Server in Workgroup.
The whole ReportServer was installed and configured from scratch with all
possible patience:
All the following programs in english:
Win Enterprise 2003 R2, SQL Server 2005 completely, SQL-Server SP1, Post SP1
hotfixes and ALL recommended Security Updates/Fixes by Windows Update.Hi Henry,
Thank you for using MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: You can not
configure the Report Cache and you get the following error message:
"The current action cannot be completed because the user data source
credentials that are required to execute this report are not stored in the
report server database. (rsInvalidDataSource-CredentialSetting) (Report
Services SOAP Proxy Source)".
If I misunderstood your concern, please feel free to let me know.
Since the report cache need a credential to connect to the datasource to
render the report, it will need the credential stored in the report server
database.
To store the credential, please do the following:
1. Open the Management Studio, connect to Reporting Services, go to the
report you want to configure.
2. Expand the left panel, and click the Data Sources, right-click your data
source name and click Properties.
3. Check the Credentials stored securely on the report server and specify a
Login Name and password.
4. Click Ok and try to configure the report cache.
Hope this will be helpful, thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights|||Hi Wei,
thanks to your advice we finally achieved to configure caching.
It follows here:
- our initial security-config
- the way we solved the problem by temporarily changing this config
- ONE Question
All Reports use a shared datasource.
This shared datasource is configured to connect using Windows integrated
security.
Our security-config is as following:
- SQL-Server is configured for "SQL Server and Windows Authentication mode"
- The reportserver machine is in a workgroup, not in a Domain
- Anonymous access to the ReportServer website is allowed via the
IUSR_machine User
- we created a local Server windows-group "Report-Reader" in Computer
Management
- we added the IUSR_machine Account to this group
- we added this windows-group as a "System User" (not System Administrator)
to Report Services
- we gave this windows-group the Browser-Role
- we gave this windows-group the datareader role with explicit rights to
select and execute on the target databases
We achieved enabling caching only after configuring this shared datasource
temporarily with:
- Connection: Credentials stored securely on the report server
- sa, password
standard config before and after: Windows integrated security (= the
IUSR_machine account) did not work
Question:
Me, the SQL Server Admin, with all possible rights, I want to configure the
caching behaviour.
Why does there need to be configured differently any shared datasource
rights to do this?
What has the config of the datasource to do with ME wanting to change a
behaviour?
Muchas Gracias, yours Henry|||Hello Henry,
Thank you for your update and glad to hear the information is helpful.
I would liket to explain that why we need to store the Credential.
Since the Report Cache is an automatical process to render the report, it
will need the credential to connect to the datasource to get the data. If
you use the Windows integrated security, it will be fine if an user try to
access the report. But the Cache can not connect to the datasource because
it does not know which credential it should use to connect to the
datasource. Thus, you need to store the credential in the database.
Don't worry about the security because the database use a symetic key to
encrypt the credential.
Here is an article for your reference:
Specifying Credential and Connection Information
http://msdn2.microsoft.com/en-us/library/ms160330(d=ide).aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Henry,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
OK, resolved.
But one big question:
If I give e.g. the sa-credentials to be stored securely in the data-source,
and if any one user achieves to send some "= 1; drop someDB; --" Code to our
Stored Procedures that sometimes use dynamic SQL, then we're fried.
Seems to be necessary to configure a SQL-Server account with less privileges.
Or let caching out.
Saludos, Henry|||Hi Henry,
Thank you for the update.
The SQL injection is really a big problem. I recommend you to configure a
SQL account which only have select permission on the database since the
reporting service only need to get the data from datasource.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Henry,
How are you doing on this thread? As Wei has been OOF due to some urgent
issues, I'm helping him contact you to see whether you still have any
problems on this. For the security threaten you mentioned earlier, Wei and
I have discussed this with our product team's engineers and their
suggestion is that we recommend the reporting service datasource always use
a readonly permission identity to access database server since reporting
service report only need readonly access. As always, if you have any
further issues, please feel free to post here.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Steven,
thank you very much for your invested time.
We are happy, that SSRS-caching could finally be switched on in our
dev-/test-environment.
We'll investigate it further, when our product is live and running and we
need to optimize it.
Have a nice day, greetings from Peru
Yours Henry

c++ lib

i am looking for c++ libs so that i may connect to sqlserver..(user does not
have client sql installed)
thanks
Hi
Look at
http://msdn.microsoft.com/library/de...asp?frame=true
Current Windows installations come with MDAC installed so you have the OLE
DB libraries.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Abe" <abe_icm@.verizon.net> wrote in message
news:bXAdf.2224$Pa4.1266@.trndny01...
>i am looking for c++ libs so that i may connect to sqlserver..(user does
>not have client sql installed)
> thanks
>

c++ lib

i am looking for c++ libs so that i may connect to sqlserver..(user does not
have client sql installed)
thanksHi
Look at
http://msdn.microsoft.com/library/d...asp?frame=true
Current Windows installations come with MDAC installed so you have the OLE
DB libraries.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Abe" <abe_icm@.verizon.net> wrote in message
news:bXAdf.2224$Pa4.1266@.trndny01...
>i am looking for c++ libs so that i may connect to sqlserver..(user does
>not have client sql installed)
> thanks
>

C# web app connection to SQL 2000

Hello, everyone,
I am trying to connect to a SQL server db with a C# app and when I debug I
get:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user
'(null)'. Reason: Not associated with a trusted SQL Server connection.
Source Error:
Line 27: if (!Page.IsPostBack)
Line 28: {
Line 29: sqlDataAdapter1.Fill(dsStudents1);
Line 30: DataGrid1.DataBind();
Line 31: }
Can somebody give me any suggestions?
Thanks,
Antonio
You are probably using the key word Integrated Security=SSPI or
trusted_connection=yes in your connection string and your Windows credential
(i.e. that of the login that runs the program) has not been granted access to
the SQL Server instance.
I'd first change Integrated Security=SSPI to user=myUser;pwd=myPassword, and
verify that there is no problem with connectivity itself (and there shouldn't
be any per your error message). Then, grant the Windows login access to the
SQL Server instance, and now you can use Integrated Security=SSPI.
Linchi
"Antonio" wrote:

> Hello, everyone,
> I am trying to connect to a SQL server db with a C# app and when I debug I
> get:
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about the error and where it originated in the code.
> Exception Details: System.Data.SqlClient.SqlException: Login failed for user
> '(null)'. Reason: Not associated with a trusted SQL Server connection.
> Source Error:
>
> Line 27: if (!Page.IsPostBack)
> Line 28: {
> Line 29: sqlDataAdapter1.Fill(dsStudents1);
> Line 30: DataGrid1.DataBind();
> Line 31: }
>
> Can somebody give me any suggestions?
> Thanks,
>
> Antonio
>
>
|||You need to grant access to ASPNET user to your database. That is the
account that web application use.
"Antonio" <info@.awfulcards.com> wrote in message
news:uMqrFT2SGHA.5884@.TK2MSFTNGP14.phx.gbl...
> Hello, everyone,
> I am trying to connect to a SQL server db with a C# app and when I debug I
> get:
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about the error and where it originated in the code.
> Exception Details: System.Data.SqlClient.SqlException: Login failed for
> user '(null)'. Reason: Not associated with a trusted SQL Server
> connection.
> Source Error:
>
> Line 27: if (!Page.IsPostBack)
> Line 28: {
> Line 29: sqlDataAdapter1.Fill(dsStudents1);
> Line 30: DataGrid1.DataBind();
> Line 31: }
>
> Can somebody give me any suggestions?
> Thanks,
>
> Antonio
>
|||The ASPNET user is setup in the database (the ASPNET user in the server, not
my machine) with access to the specific database, as owner.
"Shimon Sim" wrote:

> You need to grant access to ASPNET user to your database. That is the
> account that web application use.
> "Antonio" <info@.awfulcards.com> wrote in message
> news:uMqrFT2SGHA.5884@.TK2MSFTNGP14.phx.gbl...
>
>
|||What is your connection string?
It is strange that user is '(null)'.
"Antonio" <Antonio@.discussions.microsoft.com> wrote in message
news:35ADEC88-2C2A-45AB-BFD9-C86607839493@.microsoft.com...[vbcol=seagreen]
> The ASPNET user is setup in the database (the ASPNET user in the server,
> not
> my machine) with access to the specific database, as owner.
> "Shimon Sim" wrote:
|||Didi you grant the appropiate rights to the service account which
starts the worker process (on *your* computer) on the SQL Server ?
Domain\ServiceAccount (Your Computer) -- > has to be prviledged on the
SQL Server
Make sure that you didn=B4t accept anonymous authentication on your
webserver.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de

C# web app connection to SQL 2000

Hello, everyone,
I am trying to connect to a SQL server db with a C# app and when I debug I
get:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user
'(null)'. Reason: Not associated with a trusted SQL Server connection.
Source Error:
Line 27: if (!Page.IsPostBack)
Line 28: {
Line 29: sqlDataAdapter1.Fill(dsStudents1);
Line 30: DataGrid1.DataBind();
Line 31: }
Can somebody give me any suggestions?
Thanks,
AntonioYou are probably using the key word Integrated Security=SSPI or
trusted_connection=yes in your connection string and your Windows credential
(i.e. that of the login that runs the program) has not been granted access t
o
the SQL Server instance.
I'd first change Integrated Security=SSPI to user=myUser;pwd=myPassword, and
verify that there is no problem with connectivity itself (and there shouldn'
t
be any per your error message). Then, grant the Windows login access to the
SQL Server instance, and now you can use Integrated Security=SSPI.
Linchi
"Antonio" wrote:

> Hello, everyone,
> I am trying to connect to a SQL server db with a C# app and when I debug I
> get:
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about the error and where it originated in the code.
> Exception Details: System.Data.SqlClient.SqlException: Login failed for us
er
> '(null)'. Reason: Not associated with a trusted SQL Server connection.
> Source Error:
>
> Line 27: if (!Page.IsPostBack)
> Line 28: {
> Line 29: sqlDataAdapter1.Fill(dsStudents1);
> Line 30: DataGrid1.DataBind();
> Line 31: }
>
> Can somebody give me any suggestions?
> Thanks,
>
> Antonio
>
>|||You need to grant access to ASPNET user to your database. That is the
account that web application use.
"Antonio" <info@.awfulcards.com> wrote in message
news:uMqrFT2SGHA.5884@.TK2MSFTNGP14.phx.gbl...
> Hello, everyone,
> I am trying to connect to a SQL server db with a C# app and when I debug I
> get:
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about the error and where it originated in the code.
> Exception Details: System.Data.SqlClient.SqlException: Login failed for
> user '(null)'. Reason: Not associated with a trusted SQL Server
> connection.
> Source Error:
>
> Line 27: if (!Page.IsPostBack)
> Line 28: {
> Line 29: sqlDataAdapter1.Fill(dsStudents1);
> Line 30: DataGrid1.DataBind();
> Line 31: }
>
> Can somebody give me any suggestions?
> Thanks,
>
> Antonio
>|||The ASPNET user is setup in the database (the ASPNET user in the server, not
my machine) with access to the specific database, as owner.
"Shimon Sim" wrote:

> You need to grant access to ASPNET user to your database. That is the
> account that web application use.
> "Antonio" <info@.awfulcards.com> wrote in message
> news:uMqrFT2SGHA.5884@.TK2MSFTNGP14.phx.gbl...
>
>|||What is your connection string?
It is strange that user is '(null)'.
"Antonio" <Antonio@.discussions.microsoft.com> wrote in message
news:35ADEC88-2C2A-45AB-BFD9-C86607839493@.microsoft.com...[vbcol=seagreen]
> The ASPNET user is setup in the database (the ASPNET user in the server,
> not
> my machine) with access to the specific database, as owner.
> "Shimon Sim" wrote:
>|||Didi you grant the appropiate rights to the service account which
starts the worker process (on *your* computer) on the SQL Server ?
Domain\ServiceAccount (Your Computer) -- > has to be prviledged on the
SQL Server
Make sure that you didn=B4t accept anonymous authentication on your
webserver.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

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

C# and SQL Express

hi Y'All!

I've been having quite some fun doing C#, im doing an accounting system just for the fun of it. I find C# quite user friendly (though Im using the express edition). Its not really that cryptic. Though one must be able to grasp the concept of OOP. Well, enough praise about the language, I know i wont get any freebie anyway. Just some question though.

1. Im planning to use SQL Server express as backend, right now Im using firebird and plan to change backend. What are the limitations of SQL Express edition? like can I use it in providing for example a network of 10 users with an accounting application.

2. I plan later to create a C# program (windows application) that will access a database for example in the internet, the database (SQL) residing in a server in japan? how can this be done?, They say all you have to do is define the connection string, is there a sample for this?

3. If I use SQL server express can it handle my question No. 2?

Thanks to all of you, I have learned a lot in this forum. Some of the questions and answers I could not have learned in a year or so of studies on my own.

Y'all are great!

Omar

you need to enable remote connections in order for other computers around the network to connect to it:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=802873&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=687532&SiteID=1

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

once configured, it should be ok for use!

|||

SQL Express does not support HTTP endpoints, you have to connect to it through a WAN/LAN. Other editions of SQL Server that support HTTP Endpoints can be connected through via the Internet.

Mike

c# against ms sql express - multi user access

I have developed two programs that operates against the same database. One of the program is just a display program that displayes the data put into the other administation program where you put in the data to be displayed (an administration program).

Every time I access the db from the administration program, the display program stops and throws connection pool errors and other database errors. For me, it looks like it does′t do multi-access to a database.

I have tried putting user instance to off in both programs, but this didn′t help.

Connection string also points to same file database.

It sounds like you're trying to attach the same database file to two different SQL instances. Turning User Instances off isn't enough if you still have the database sitting detached in your user profile directory. You will actually need to move the file into the Data directory, attach it to the parent instance of SQL Express and then use a standard connection string from each application to point to it rather than trying to attach it on the fly in each application.

Mike

|||

Yes, you are correct on that. Two applications, one database.

Move it to the MS SQL Server′s datadir is OK. Then, I just remove the "attached-db" attribute from the connection string and only use servername=sqlexpress/localhost, correct? Or is it more I need to do?

|||

I just can′t seen to get around the file-mess. When I use the connection wizard in VS, I only get the option to attach-db file, and it will not let me continue before I have selected that. But I want to use more normal connections using server name so that I can connect to db from both programs.

I have tried to manually edit the connection string it makes: Data Source=XP1\SQLEXPRESS;Integrated Security=True;Connect Timeout=30; Then I get "Invalid object name tablename" etc. I′m using table adapter, but it doesn′t give me connection warnings, strange enough.. Is it something I need to change in tableadapter after changing the connection string in settings?

|||Never mind.. forgot the initial catalog...|||-|||

Looks like you got things going, just wanted to check back to be sure things were working.

Mike

ByYear or By Date Range

I would like to give the User to option of choosing the report by year or by
a To-From Date Range. How would I accomplish this option?
Thanks in advance,
SeanTake a look at the [Employee Sales Summary.rdl] sample report that ships
with the product.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:D05FC8E5-24B4-4142-A1B3-016147DA94DF@.microsoft.com...
> I would like to give the User to option of choosing the report by year or
by
> a To-From Date Range. How would I accomplish this option?
> Thanks in advance,
> Sean