Determine Licenses currently in use.

How do I determine total client coneections that are currently being used by
the SQL Server?
We are developing an application that connects to a DB Server and downloads
the information particlular to that workstation, and then never hits the SQL
server again until the next user logs on.
We will have about 130 workstations that will hit the database at one point
via our application.
The question is; Do we need/require a CAL for each workstation? If not then
why? If so, if the server is only registering one connection to the server
at any given time, why have all the CAL's?
Brian CookSounds like you would be better off with per processor licensing (unless you
have a large number of CPUs, or are running enterprise edition)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Brian Cook" <anonymous@.discussions.microsoft.com> wrote in message
news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
> How do I determine total client coneections that are currently being used
by the SQL Server?
> We are developing an application that connects to a DB Server and
downloads the information particlular to that workstation, and then never
hits the SQL server again until the next user logs on.
> We will have about 130 workstations that will hit the database at one
point via our application.
> The question is; Do we need/require a CAL for each workstation? If not
then why? If so, if the server is only registering one connection to the
server at any given time, why have all the CAL's?
> Brian Cook|||Hi,
How do I determine total client coneections that are currently being used
by the SQL Server?
Execute the below script in Query analyzer to identify the mode of license
used and the count.
select serverproperty('LicenseType')
go
select serverproperty('NumLicenses'
The question is; Do we need/require a CAL for each workstation? If not then
why?
I feel that you need to get seperate licenses for each user connectiong even
though they wint access concurrently
Thanks
Hari
MCDBA
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:u9jCUoLHEHA.2744@.TK2MSFTNGP10.phx.gbl...
> Sounds like you would be better off with per processor licensing (unless
you
> have a large number of CPUs, or are running enterprise edition)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Brian Cook" <anonymous@.discussions.microsoft.com> wrote in message
> news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
used
> by the SQL Server?
> downloads the information particlular to that workstation, and then never
> hits the SQL server again until the next user logs on.
> point via our application.
> then why? If so, if the server is only registering one connection to the
> server at any given time, why have all the CAL's?
>|||http://www.microsoft.com/sql/howtobuy/default.asp
There are device CALs and user CALs. I don't know the retail price of an
individual CAL, but if it is even as low as $50, you have already passed
what the cost would be for a one CPU processor license.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Brian Cook" <anonymous@.discussions.microsoft.com> wrote in message
news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
> How do I determine total client coneections that are currently being used
by the SQL Server?
> We are developing an application that connects to a DB Server and
downloads the information particlular to that workstation, and then never
hits the SQL server again until the next user logs on.
> We will have about 130 workstations that will hit the database at one
point via our application.
> The question is; Do we need/require a CAL for each workstation? If not
then why? If so, if the server is only registering one connection to the
server at any given time, why have all the CAL's?
> Brian Cook|||Cost wise, it would be cheaper to purchase licenses for each client. This be
ing an Enterprise application we are designing, and the SQL Servers are inte
nded to be Quad processor computers, it would not be cost effective to run t
he per processor version. A
lthough the unlimited amount of licenses is attractive, I do not see the jus
tification/need for the expense. (god I sound like an accountant! Ack! Ack!
plaaaa!).
Thanks,
Brian
-- Kevin3NF wrote: --
http://www.microsoft.com/sql/howtobuy/default.asp
There are device CALs and user CALs. I don't know the retail price of an
individual CAL, but if it is even as low as $50, you have already passed
what the cost would be for a one CPU processor license.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Brian Cook" <anonymous@.discussions.microsoft.com> wrote in message
news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
> How do I determine total client coneections that are currently being used
by the SQL Server?
downloads the information particlular to that workstation, and then never
hits the SQL server again until the next user logs on.
point via our application.
then why? If so, if the server is only registering one connection to the
server at any given time, why have all the CAL's?|||Hari, thanks for the script, however it does not tell me how many licenses a
re currently in use by the users/computer connections. This tells me how man
y licenses are INSTALLED to the SQL server. I need to know/validate how many
are being used at any give
n time.
Thanks,
Brian
-- Hari Prasad wrote: --
Hi,
How do I determine total client coneections that are currently being used
by the SQL Server?
Execute the below script in Query analyzer to identify the mode of license
used and the count.
select serverproperty('LicenseType')
go
select serverproperty('NumLicenses'
The question is; Do we need/require a CAL for each workstation? If not then
why?
I feel that you need to get seperate licenses for each user connectiong even
though they wint access concurrently
Thanks
Hari
MCDBA
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:u9jCUoLHEHA.2744@.TK2MSFTNGP10.phx.gbl...
> Sounds like you would be better off with per processor licensing (unless
you
> have a large number of CPUs, or are running enterprise edition)
> Kevin Hill
> President
> 3NF Consulting
> news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
used
> by the SQL Server?
> downloads the information particlular to that workstation, and then never
> hits the SQL server again until the next user logs on.
> point via our application.
> then why? If so, if the server is only registering one connection to the
> server at any given time, why have all the CAL's?|||I spoke with Microsoft this morning, and there is no way built into SQL to d
etermine how many current client access licenses, so the answer is that I mu
st have a CAL for each workstation.
Individual CAL's are cheaper than the Per Processor model in this instance.
Thanks for the suggestions guys.
Brian
-- Brian Cook wrote: --
How do I determine total client coneections that are currently being used by
the SQL Server?
We are developing an application that connects to a DB Server and downloads
the information particlular to that workstation, and then never hits the SQL
server again until the next user logs on.
We will have about 130 workstations that will hit the database at one point
via our application.
The question is; Do we need/require a CAL for each workstation? If not then
why? If so, if the server is only registering one connection to the server
at any given time, why have all the CAL's?
Brian Cook

Determine Licenses currently in use.

How do I determine total client coneections that are currently being used by the SQL Server?
We are developing an application that connects to a DB Server and downloads the information particlular to that workstation, and then never hits the SQL server again until the next user logs on.
We will have about 130 workstations that will hit the database at one point via our application.
The question is; Do we need/require a CAL for each workstation? If not then why? If so, if the server is only registering one connection to the server at any given time, why have all the CAL's?
Brian Cook
Sounds like you would be better off with per processor licensing (unless you
have a large number of CPUs, or are running enterprise edition)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Brian Cook" <anonymous@.discussions.microsoft.com> wrote in message
news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
> How do I determine total client coneections that are currently being used
by the SQL Server?
> We are developing an application that connects to a DB Server and
downloads the information particlular to that workstation, and then never
hits the SQL server again until the next user logs on.
> We will have about 130 workstations that will hit the database at one
point via our application.
> The question is; Do we need/require a CAL for each workstation? If not
then why? If so, if the server is only registering one connection to the
server at any given time, why have all the CAL's?
> Brian Cook
|||Hi,
How do I determine total client coneections that are currently being used
by the SQL Server?
Execute the below script in Query analyzer to identify the mode of license
used and the count.
select serverproperty('LicenseType')
go
select serverproperty('NumLicenses'
The question is; Do we need/require a CAL for each workstation? If not then
why?
I feel that you need to get seperate licenses for each user connectiong even
though they wint access concurrently
Thanks
Hari
MCDBA
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:u9jCUoLHEHA.2744@.TK2MSFTNGP10.phx.gbl...
> Sounds like you would be better off with per processor licensing (unless
you
> have a large number of CPUs, or are running enterprise edition)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Brian Cook" <anonymous@.discussions.microsoft.com> wrote in message
> news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
used
> by the SQL Server?
> downloads the information particlular to that workstation, and then never
> hits the SQL server again until the next user logs on.
> point via our application.
> then why? If so, if the server is only registering one connection to the
> server at any given time, why have all the CAL's?
>
|||http://www.microsoft.com/sql/howtobuy/default.asp
There are device CALs and user CALs. I don't know the retail price of an
individual CAL, but if it is even as low as $50, you have already passed
what the cost would be for a one CPU processor license.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Brian Cook" <anonymous@.discussions.microsoft.com> wrote in message
news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
> How do I determine total client coneections that are currently being used
by the SQL Server?
> We are developing an application that connects to a DB Server and
downloads the information particlular to that workstation, and then never
hits the SQL server again until the next user logs on.
> We will have about 130 workstations that will hit the database at one
point via our application.
> The question is; Do we need/require a CAL for each workstation? If not
then why? If so, if the server is only registering one connection to the
server at any given time, why have all the CAL's?
> Brian Cook
|||Cost wise, it would be cheaper to purchase licenses for each client. This being an Enterprise application we are designing, and the SQL Servers are intended to be Quad processor computers, it would not be cost effective to run the per processor version. A
lthough the unlimited amount of licenses is attractive, I do not see the justification/need for the expense. (god I sound like an accountant! Ack! Ack! plaaaa!).
Thanks,
Brian
-- Kevin3NF wrote: --
http://www.microsoft.com/sql/howtobuy/default.asp
There are device CALs and user CALs. I don't know the retail price of an
individual CAL, but if it is even as low as $50, you have already passed
what the cost would be for a one CPU processor license.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Brian Cook" <anonymous@.discussions.microsoft.com> wrote in message
news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
> How do I determine total client coneections that are currently being used
by the SQL Server?
downloads the information particlular to that workstation, and then never
hits the SQL server again until the next user logs on.
point via our application.
then why? If so, if the server is only registering one connection to the
server at any given time, why have all the CAL's?
|||Hari, thanks for the script, however it does not tell me how many licenses are currently in use by the users/computer connections. This tells me how many licenses are INSTALLED to the SQL server. I need to know/validate how many are being used at any give
n time.
Thanks,
Brian
-- Hari Prasad wrote: --
Hi,
How do I determine total client coneections that are currently being used
by the SQL Server?
Execute the below script in Query analyzer to identify the mode of license
used and the count.
select serverproperty('LicenseType')
go
select serverproperty('NumLicenses'
The question is; Do we need/require a CAL for each workstation? If not then
why?
I feel that you need to get seperate licenses for each user connectiong even
though they wint access concurrently
Thanks
Hari
MCDBA
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:u9jCUoLHEHA.2744@.TK2MSFTNGP10.phx.gbl...
> Sounds like you would be better off with per processor licensing (unless
you
> have a large number of CPUs, or are running enterprise edition)
> Kevin Hill
> President
> 3NF Consulting
> news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
used
> by the SQL Server?
> downloads the information particlular to that workstation, and then never
> hits the SQL server again until the next user logs on.
> point via our application.
> then why? If so, if the server is only registering one connection to the
> server at any given time, why have all the CAL's?
|||I spoke with Microsoft this morning, and there is no way built into SQL to determine how many current client access licenses, so the answer is that I must have a CAL for each workstation.
Individual CAL's are cheaper than the Per Processor model in this instance.
Thanks for the suggestions guys.
Brian
-- Brian Cook wrote: --
How do I determine total client coneections that are currently being used by the SQL Server?
We are developing an application that connects to a DB Server and downloads the information particlular to that workstation, and then never hits the SQL server again until the next user logs on.
We will have about 130 workstations that will hit the database at one point via our application.
The question is; Do we need/require a CAL for each workstation? If not then why? If so, if the server is only registering one connection to the server at any given time, why have all the CAL's?
Brian Cook

determine days of the year

Hi there, is that any function in ms sql server 2000 where if i pass a date or a year then it could gives me the total days of year from the parameter?
in mySQL got select DAYOFYEAR(date);
can some one guide me on this please...i need to use it for a leap year function for my SP!There's no built-in function that will return the number of days in a year for a given date.However you can create a function something like

CREATE FUNCTION [dbo].[GetDaysInYear] ( @.pDate DATETIME )
RETURNS INT
AS
BEGIN

DECLARE @.IsLeapYear BIT

SET @.IsLeapYear = 0
IF (YEAR( @.pDate ) % 4 = 0 AND YEAR( @.pDate ) % 100 != 0) OR
YEAR( @.pDate ) % 400 = 0
SET @.IsLeapYear = 1

RETURN 365 + @.IsLeapYear

END
GO

Then you can call your function directly in your SP like

GetDaysInYear (Your date goes here)|||CREATE FUNCTION GetDaysInYear
(
@.pDate DATETIME
)
RETURNS INT
AS
BEGIN
Declare @.days int
Declare @.firstDay varchar(15)
set @.firstDay = '1/1/' + Cast(Year(@.pDate) as varchar)
Select @.days = DATEDIFF(dd, Convert(Varchar, @.firstDay, 101), @.pDate)
RETURN @.days
END
GO

This function behaves similar to MySQL DAYOFYEAR(date) function
(only diff. is I think with 1-Jan)|||mihir, you were close but the calculation is off by 1 for all dates

:)|||Hi Thanx nick.ncs

i think i can just use your :

IF (YEAR( @.pDate ) % 4 = 0 AND YEAR( @.pDate ) % 100 != 0) OR
YEAR( @.pDate ) % 400 = 0

it wats i need after all!
thank you so much nick.ncs :eek:|||another way is to check if the 60th day of the year is in feb. see:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83637|||declare @.dt datetime
set @.dt='2007-06-18'
select datediff(dd,
dateadd(yy,datediff(yy,0,@.dt),0)
,dateadd(yy,datediff(yy,0,@.dt)+1,0)
) DaysInYear|||pdreyer, that is gorgeous

wherever did you learn that?

:)|||select datepart(dy, getdate())|||mcrowley, welcome to the thread

please note your query produces day in year

the problem was days in year

i.e. either 365 or 366

:)|||but your idea was a good one :)

select datepart(dy, dateadd(dd,-1,dateadd(yy,datediff(yy,0,@.dt)+1,0)))|||Doh !|||don't beat yourself up about it, you still had a great idea, but it would only have worked for Dec 31st, so that's what i changed it slightly to do

:)

Detail Section Limit Records

Hi,

I have limited my detail section output to print 20 records per page, but if have total of 108 records ,First 100 records are printing 5 pages and the 6 thpage prints 8 records .

I want that the 6 th page to print 8 records , plus 12 lines filled with dashes (---).Making the page to appear having 20 lines .

Can anybody help

thanks
ShaliniI had resolved something like that by modifying the data source, I mean, generating the extra lines when writing the output file

DESPERATE help needed....

Hello all,

I am a total newbie to SQL. I created this sp and then with C++,
called it. My return value was 100 (which it was)

CREATE PROCEDURE sp_StoreIPs
@.IPSource varchar(16),
@.IPTarget varchar(16),
@.TimeDate varchar(20),
@.Name varchar(250)
as
declare @.iReturn int
Set @.iReturn = 100
return @.iReturn
GO

Then when I added a INSERT statement like ...

CREATE PROCEDURE sp_StoreIPs
@.IPSource varchar(16),
@.IPTarget varchar(16),
@.TimeDate varchar(20),
@.Name varchar(250)
as
declare @.iReturn int
Insert into LookUP (IPSource, IPTarget,TimeDate, Name) Values
(@.IPSource,@.IPTarget,@.TimeDate,@.Name)
Set @.iReturn = 100
return @.iReturn
GO

My return value was 0. I am assuming the the INSERT statement is
returning the 0 but how can I get around this?

Thanks
Ralph Krausse
www.consiliumsoft.com
Use the START button? Then you need CSFastRunII...
A new kind of application launcher integrated in the taskbar!
ScreenShot - http://www.consiliumsoft.com/ScreenShot.jpg"Ralph Krausse" wrote:

<snip
> CREATE PROCEDURE sp_StoreIPs
> @.IPSource varchar(16),
> @.IPTarget varchar(16),
> @.TimeDate varchar(20),
> @.Name varchar(250)
> as
> declare @.iReturn int
> Insert into LookUP (IPSource, IPTarget,TimeDate, Name) Values
> (@.IPSource,@.IPTarget,@.TimeDate,@.Name)
> Set @.iReturn = 100
> return @.iReturn
> GO
>
> My return value was 0. I am assuming the the INSERT statement is
> returning the 0 but how can I get around this?

<snip
Ralph,

I think you're half correct: an insert can return a message about records
affected... but I don't know why that would affect the return value unless
the insert failed: are you checking to insure successful execution? My
guess would be that the INSERT is failing.

In any event, to suppress the record count coming back as a message, you can
use SET NOCOUNT ON/OFF:

CREATE PROCEDURE blah
AS

SET NOCOUNT ON

INSERT SomeTable (fld) VALUES ('a')

SET NOCOUNT OFF

RETURN 100
GO

And the NOCOUNT setting can be handy even if this isn't your problem: it's
always nice to eliminate unnecessary network chitchat :)

Craig|||[posted and mailed, please reply in news]

Ralph Krausse (gordingin@.consiliumsoft.com) writes:
> I am a total newbie to SQL. I created this sp and then with C++,
> called it. My return value was 100 (which it was)

And what API did you use to call the procedure from C++?

> CREATE PROCEDURE sp_StoreIPs

Don't use the sp_ prefix for the names of stored procedure. This prefix
is reserved for system procedures, and SQL Server first looks for
procedures with this prefix in the master database.

> Then when I added a INSERT statement like ...
>...
> My return value was 0. I am assuming the the INSERT statement is
> returning the 0 but how can I get around this?

As Craig said, the INSERT statement generates a kind of result set to
inform of the number of rows consumed. If you don't get that result
set, you will not get the return value, nor the value of output parameter,
since these are not availble until all result sets have been consumed.

Since I don't know which API you are using, I cannot say which methods you
should use, but you should always make it a habit to fetch all result set.

SET NOCOUNT ON, which Craig suggested, is a very good idea, if you are
not interested in these record counts, since they cause extra round-trips
to the server. However, result sets may appear of other reasons, for
instance a trigger with a SELECT statement in it. (Which is poor practice,
but accidents happens.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Design a Tree in a blank Report

I'm creating a report like a Tree structure.

Area1 Total Modifications in Area1
Type1 Total Modifications in Type1 in area1
Sub Type1 Total Modifications in Sub Type1 in Type1 in area1
Sub Type2 Total Modifications in Sub Type2 in Type1 in area1

Type2 Total Modifications in Type2 in area1
Sub Typex Total Modifications in Sub Typex in Type2 in area1
Sub Typex2 Total Modifications in Sub Typex2 in Type2 in area1

Area2
Type1 Total Modifications in Type1 in area2
Sub Type1 Total Modifications in Sub Type1 in Type1 in area2
Sub Type2 Total Modifications in Sub Type2 in Type1 in area2

Type2 Total Modifications in Type2 in area2
Sub Typex Total Modifications in Sub Typex in Type2 in area2
Sub Typex2 Total Modifications in Sub Typex2 in Type2 in area2

I used the Report Wizard, created a Tabular report, did the grouping, and choose the Table Layout as Stepped and enabled the Drilldown and subtotal and it is works perfectly. When clicked on Area1, it shows the Type1 and Type2, and when clicked on Type shows the Sub type..

Now the problem is i have all the complex SQL code for this in a Stored Procedure.

When using Stored Procedure, Report Wizard cannot be used. So, How do i achieve similar Stepped Tabular report with drilldown feature when i create a blank report with Stored Procedure in Dataset ?

I used Table control and added two groups for Area and Type. I made the Area as Parent for Type and tried Hide and visibility Toggle option but i got error
"Toggle items must be text boxes that share the same scope as the hidden item or are in a scope that contains the hidden item, and cannot be contained within the current report item unless current grouping scope has a Parent."

Is this the way to do it or am i missing somthing in this?

Any help with steps appreciated.

Thanks.

Got it.
I created a report in Report Wiz using some query, and then i created a Dataset with Stored Proc and changed the dataset name for Table in Properties and it worked.