3 / 2=1? Strange result in expression...HELP!

Hi Folks!

I have a strange thing happening; I have a field that Counts the number of
records and another field that shows the number of clients
(Count(RecordID)=3 and NoOfClients=2) When I do a simple expression
"Count(RecordID)/NoOfClients" the expected result should be 1.5. Instead
the result I get is 1. Any ideas?

Thanks!
RickTry converting one of the values to decimal. For example:
select 3/2 results in 1 BUT
select 3/2. should result in 1.5

Difference is in datatypes...

MC

"Rico" <you@.me.comwrote in message news:BP51h.34135$P7.18677@.edtnps90...

Quote:

Originally Posted by

Hi Folks!
>
I have a strange thing happening; I have a field that Counts the number of
records and another field that shows the number of clients
(Count(RecordID)=3 and NoOfClients=2) When I do a simple expression
"Count(RecordID)/NoOfClients" the expected result should be 1.5. Instead
the result I get is 1. Any ideas?
>
Thanks!
Rick
>
>
>

|||That did the trick. Thanks MC. I thought it had something to do with the
data types, but being a newbie didn't think about "Decimal".

Thanks again!
Rick

"MC" <marko_culoNOSPAM@.yahoo.comwrote in message
news:ei2qf1$a13$1@.ss408.t-com.hr...

Quote:

Originally Posted by

Try converting one of the values to decimal. For example:
select 3/2 results in 1 BUT
select 3/2. should result in 1.5
>
Difference is in datatypes...
>
MC
>
>
"Rico" <you@.me.comwrote in message news:BP51h.34135$P7.18677@.edtnps90...

Quote:

Originally Posted by

>Hi Folks!
>>
>I have a strange thing happening; I have a field that Counts the number
>of
>records and another field that shows the number of clients
>(Count(RecordID)=3 and NoOfClients=2) When I do a simple expression
>"Count(RecordID)/NoOfClients" the expected result should be 1.5. Instead
>the result I get is 1. Any ideas?
>>
>Thanks!
>Rick
>>
>>
>>

>
>

|||Hi Rick,

you are using integers and in integer division 3/2 is 1.
Use floats or cast your numbers to floats in the calculation:

float result=((float)Count(RecordID))/((float)NoOfClients);

Regards,
Andy

Rico schrieb:

Quote:

Originally Posted by

Hi Folks!
>
I have a strange thing happening; I have a field that Counts the number of
records and another field that shows the number of clients
(Count(RecordID)=3 and NoOfClients=2) When I do a simple expression
"Count(RecordID)/NoOfClients" the expected result should be 1.5. Instead
the result I get is 1. Any ideas?
>
Thanks!
Rick

|||Forgot to respond. Thanks! That did the trick!

"MC" <marko_culoNOSPAM@.yahoo.comwrote in message
news:ei2qf1$a13$1@.ss408.t-com.hr...

Quote:

Originally Posted by

Try converting one of the values to decimal. For example:
select 3/2 results in 1 BUT
select 3/2. should result in 1.5
>
Difference is in datatypes...
>
MC
>
>
"Rico" <you@.me.comwrote in message news:BP51h.34135$P7.18677@.edtnps90...

Quote:

Originally Posted by

>Hi Folks!
>>
>I have a strange thing happening; I have a field that Counts the number
>of
>records and another field that shows the number of clients
>(Count(RecordID)=3 and NoOfClients=2) When I do a simple expression
>"Count(RecordID)/NoOfClients" the expected result should be 1.5. Instead
>the result I get is 1. Any ideas?
>>
>Thanks!
>Rick
>>
>>
>>

>
>

2-Dimensional reports.

I have a number of reports to implement that have the following
characteristics:
1) column headers come from database tables - these are normally 4 to 10
items. These tables consist of 1 text field and a record-id field.
2) rows come from other tables according to specified selection criteria.
3) Row summaries based on some calculations are included in a last column
4) group and final calculations are required.
I've implemented one report using a multi-join SQL statement with a GROUP BY
statement and a CUBE statement, putting the results into a Grid report. This
worked except that the final total also shows details that I don't want on
the report. I've been able to blank out the rows for these entries, but
wasn't able to get rid of the rows themselves in the report, so that the
Final total follows a number of blank lines after the last Group-level
subtotal.
Is there any technique that would allow me to use a Table-report and fill in
the column headers from one DataSet in an extensible manner, and then fill in
the row data from a different dataset?You should be able to use aggregates from another data set in the headers of
a table. You say =First(Fields!Header.Value, "headerdataset").
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Altair" <Altair@.discussions.microsoft.com> wrote in message
news:6CA5C7DE-E2D2-4968-A070-9B47D2354518@.microsoft.com...
>I have a number of reports to implement that have the following
> characteristics:
> 1) column headers come from database tables - these are normally 4 to 10
> items. These tables consist of 1 text field and a record-id field.
> 2) rows come from other tables according to specified selection criteria.
> 3) Row summaries based on some calculations are included in a last column
> 4) group and final calculations are required.
> I've implemented one report using a multi-join SQL statement with a GROUP
> BY
> statement and a CUBE statement, putting the results into a Grid report.
> This
> worked except that the final total also shows details that I don't want on
> the report. I've been able to blank out the rows for these entries, but
> wasn't able to get rid of the rows themselves in the report, so that the
> Final total follows a number of blank lines after the last Group-level
> subtotal.
> Is there any technique that would allow me to use a Table-report and fill
> in
> the column headers from one DataSet in an extensible manner, and then fill
> in
> the row data from a different dataset?|||In trying your suggestion, I only get the first element from the dataset.
I've not been successfull, in trying to populate the rest of the column
headers with successive values from the header dataset. From what I've found
in the documentation, there are First(), and a Last() aggregate functions,
but nothing to allow you to iterate through the values.
"Brian Welcker [MSFT]" wrote:
> You should be able to use aggregates from another data set in the headers of
> a table. You say =First(Fields!Header.Value, "headerdataset").
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Altair" <Altair@.discussions.microsoft.com> wrote in message
> news:6CA5C7DE-E2D2-4968-A070-9B47D2354518@.microsoft.com...
> >I have a number of reports to implement that have the following
> > characteristics:
> > 1) column headers come from database tables - these are normally 4 to 10
> > items. These tables consist of 1 text field and a record-id field.
> > 2) rows come from other tables according to specified selection criteria.
> > 3) Row summaries based on some calculations are included in a last column
> > 4) group and final calculations are required.
> >
> > I've implemented one report using a multi-join SQL statement with a GROUP
> > BY
> > statement and a CUBE statement, putting the results into a Grid report.
> > This
> > worked except that the final total also shows details that I don't want on
> > the report. I've been able to blank out the rows for these entries, but
> > wasn't able to get rid of the rows themselves in the report, so that the
> > Final total follows a number of blank lines after the last Group-level
> > subtotal.
> >
> > Is there any technique that would allow me to use a Table-report and fill
> > in
> > the column headers from one DataSet in an extensible manner, and then fill
> > in
> > the row data from a different dataset?
>
>

28549Integrity Violation when sync between PDA and SQL server 20

Hi all,
I have a problem with replication. I did a search for error number 28549 in
this group and find no answer that matched my case. :-(
I have used replication for download data from sqlserver to pda. All the
tables a independent with each other. No link between them.
I have set the -ExchangeType 2 so that the synchronization is just one
direction from sql server to pda.
There aren't null data.
I have 50 users synchronize data. Some of users succeeded but most of them
failed.
The error is 28549-Integrity Violation...
I tried to copy the data file (.sdf) from pda of user succeeded in
synchronization. And then paste it to a new pda, synchronize again, and
that's so odd!!!! I met the same error!!!
I was confused, Can anyone help me out...
So appreciated
I have found that server have insert some rows that existing in
subscriptions. How can I find which row is conflict with db at subscriptions
at merge-agents. or at db server.
I have tried to delete merge_contents table, and it seems after that, all
subscriptions can not be regconized. All subscriptions are anonymous
subscriptions
"Mai Thoa" wrote:

> Hi all,
> I have a problem with replication. I did a search for error number 28549 in
> this group and find no answer that matched my case. :-(
> I have used replication for download data from sqlserver to pda. All the
> tables a independent with each other. No link between them.
> I have set the -ExchangeType 2 so that the synchronization is just one
> direction from sql server to pda.
> There aren't null data.
> I have 50 users synchronize data. Some of users succeeded but most of them
> failed.
> The error is 28549-Integrity Violation...
> I tried to copy the data file (.sdf) from pda of user succeeded in
> synchronization. And then paste it to a new pda, synchronize again, and
> that's so odd!!!! I met the same error!!!
> I was confused, Can anyone help me out...
> So appreciated
|||Please provide more detail about your setup, like, version of sql server,
kind of replication (replication over VPN or web sync using https), and in
which replication process are you getting this error?
thansk - Deepak
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mai Thoa" <MaiThoa@.discussions.microsoft.com> wrote in message
news:09DF1BD7-55D6-473A-9FA0-BEA8B021603F@.microsoft.com...
> I have found that server have insert some rows that existing in
> subscriptions. How can I find which row is conflict with db at
subscriptions[vbcol=seagreen]
> at merge-agents. or at db server.
> I have tried to delete merge_contents table, and it seems after that, all
> subscriptions can not be regconized. All subscriptions are anonymous
> subscriptions
> "Mai Thoa" wrote:
in[vbcol=seagreen]
them[vbcol=seagreen]

256 table limit for partitioned views

I have a partitioned view sitting over several tables and I'm slowly
approaching the 256 number. Can anybody confirm if there is such a
limit for the maximum number of tables that a partitioned view can
hold?

If this is true, does anybody have any suggestions or ideas to work
around this max limit?

TIA!karthik (karthiksmiles@.gmail.com) writes:
> I have a partitioned view sitting over several tables and I'm slowly
> approaching the 256 number. Can anybody confirm if there is such a
> limit for the maximum number of tables that a partitioned view can
> hold?

Yes, since the maximum number of tables per query is 256 I would
expect that there is such a limit.

> If this is true, does anybody have any suggestions or ideas to work
> around this max limit?

How big are your tables? Would it be possible to consolidate them?

In SQL 2005 there is partioned tables, which is taking this to another
level. I don't know how many partitions you can have in a table, but
it's a new ballpark.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The limit is 256 tables "per SELECT statement", not per query.
Therefore, a UNION query can have more than 256 tables, but
unfortunately, such a query may not be used in a view. For example:

CREATE TABLE T (X INT)
INSERT INTO T VALUES (1)
DECLARE @.SQL varchar(8000)

SELECT @.SQL=ISNULL(@.SQL+' UNION ALL ','')+'SELECT X FROM T'
FROM (SELECT DISTINCT number FROM master..spt_values
WHERE number BETWEEN 0 AND 256) X

--PRINT LEN(@.SQL)
EXEC(@.SQL)

SET @.SQL='CREATE VIEW V AS '+@.SQL
EXEC (@.SQL)

For more informations, see:
http://groups-beta.google.com/group...885c192f511bd1a

Razvan|||Razvan Socol (rsocol@.gmail.com) writes:
> The limit is 256 tables "per SELECT statement", not per query.
> Therefore, a UNION query can have more than 256 tables, but
> unfortunately, such a query may not be used in a view. For example:

Thanks Razvan. I did notice "per SELECT statement", but I was too lazy
to get a practical interpretation of what that really meant.

> For more informations, see:
> http://groups-beta.google.com/group...885c192f511bd1a

That's a useful link!

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Razvan and Erland...I guess I'm just going to wait for the
Partitioned Tables feature in SQL Server 2005.

200611 to string 'Nov 2006'

Hi
how to conert (number) 200611 to string 'Nov 2006' ?
thanks
This is 'awkward' -but it works. Maybe someone else will have a better idea.
First, recognize that '200611' is not immediately understandable as a valid date datatype.
SELECT left( convert( varchar(15), cast( ( '200611' + '01' ) AS datetime), 107 ), 3 ) + ' ' + left( '200611', 4 )
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"klabu" <noone_at_gmail_dot_com> wrote in message news:12lfgcha53al985@.corp.supernews.com...
> Hi
> how to conert (number) 200611 to string 'Nov 2006' ?
> thanks
>
|||declare @.number int
set @.number = 200611
select left( datename( month, converted_to_date ), 3 ) + ' ' + datename(
year, converted_to_date )
from ( select cast( cast( @.number as char(6) ) + '01' as datetime ) as
converted_to_date ) as q
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"klabu" <noone_at_gmail_dot_com> wrote in message
news:12lfgcha53al985@.corp.supernews.com...
> Hi
> how to conert (number) 200611 to string 'Nov 2006' ?
> thanks
>
|||Hi
select right(convert(char(11),cast( cast( @.number as char(6) ) + '01' as
datetime ),106),8)
"klabu" <noone_at_gmail_dot_com> wrote in message
news:12lfgcha53al985@.corp.supernews.com...
> Hi
> how to conert (number) 200611 to string 'Nov 2006' ?
> thanks
>

200611 to string 'Nov 2006'

Hi
how to conert (number) 200611 to string 'Nov 2006' ?
thanksThis is 'awkward' -but it works. Maybe someone else will have a better idea.
First, recognize that '200611' is not immediately understandable as a valid
date datatype.
SELECT left( convert( varchar(15), cast( ( '200611' + '01' ) AS datetime), 1
07 ), 3 ) + ' ' + left( '200611', 4 )
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"klabu" <noone_at_gmail_dot_com> wrote in message news:12lfgcha53al985@.corp.supernews.com...

> Hi
>
> how to conert (number) 200611 to string 'Nov 2006' ?
>
> thanks
>
>|||>> how to convert (number) 200611 to string 'Nov 2006' ? <<
Some SQLs allow a YEAR TO MONTH data type; SQL Server is not one of
them. This is a shorthand for the range '2006-11-01 00:00:00' to
2006-11-31 23:59:59.99999' in Standard SQL. In this dialect, you will
need to build an auxiliary look up table to get those pairs.|||declare @.number int
set @.number = 200611
select left( datename( month, converted_to_date ), 3 ) + ' ' + datename(
year, converted_to_date )
from ( select cast( cast( @.number as char(6) ) + '01' as datetime ) as
converted_to_date ) as q
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"klabu" <noone_at_gmail_dot_com> wrote in message
news:12lfgcha53al985@.corp.supernews.com...
> Hi
> how to conert (number) 200611 to string 'Nov 2006' ?
> thanks
>|||Hi
select right(convert(char(11),cast( cast( @.number as char(6) ) + '01' as
datetime ),106),8)
"klabu" <noone_at_gmail_dot_com> wrote in message
news:12lfgcha53al985@.corp.supernews.com...
> Hi
> how to conert (number) 200611 to string 'Nov 2006' ?
> thanks
>

200611 to string 'Nov 2006'

Hi
how to conert (number) 200611 to string 'Nov 2006' ?
thanksThis is a multi-part message in MIME format.
--=_NextPart_000_06DA_01C7067D.8CE90AB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
This is 'awkward' -but it works. Maybe someone else will have a better =idea.
First, recognize that '200611' is not immediately understandable as a =valid date datatype.
SELECT left( convert( varchar(15), cast( ( '200611' + '01' ) AS =datetime), 107 ), 3 ) + ' ' + left( '200611', 4 )
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
"klabu" <noone_at_gmail_dot_com> wrote in message =news:12lfgcha53al985@.corp.supernews.com...
> Hi
> > how to conert (number) 200611 to string 'Nov 2006' ?
> > thanks > >
--=_NextPart_000_06DA_01C7067D.8CE90AB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

This is 'awkward' -but it works. Maybe =someone else will have a better idea.
First, recognize that '200611' is not =immediately understandable as a valid date datatype.
SELECT left( convert( =varchar(15), cast( ( '200611' + '01' ) AS datetime), 107 ), 3 ) + ' ' + left( ='200611', 4 )
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
"klabu" =wrote in message news:12lfgcha53al985@.corp.supernews.com...> =Hi> > how to conert (number) 200611 to string 'Nov 2006' ?> => thanks > >

---

--=_NextPart_000_06DA_01C7067D.8CE90AB0--|||>> how to convert (number) 200611 to string 'Nov 2006' ? <<
Some SQLs allow a YEAR TO MONTH data type; SQL Server is not one of
them. This is a shorthand for the range '2006-11-01 00:00:00' to
2006-11-31 23:59:59.99999' in Standard SQL. In this dialect, you will
need to build an auxiliary look up table to get those pairs.|||declare @.number int
set @.number = 200611
select left( datename( month, converted_to_date ), 3 ) + ' ' + datename(
year, converted_to_date )
from ( select cast( cast( @.number as char(6) ) + '01' as datetime ) as
converted_to_date ) as q
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"klabu" <noone_at_gmail_dot_com> wrote in message
news:12lfgcha53al985@.corp.supernews.com...
> Hi
> how to conert (number) 200611 to string 'Nov 2006' ?
> thanks
>|||Hi
select right(convert(char(11),cast( cast( @.number as char(6) ) + '01' as
datetime ),106),8)
"klabu" <noone_at_gmail_dot_com> wrote in message
news:12lfgcha53al985@.corp.supernews.com...
> Hi
> how to conert (number) 200611 to string 'Nov 2006' ?
> thanks
>

200611 to string Nov 2006

Hi

how to conert (number) 200611 to string 'Nov 2006' ?

thanksThis is 'awkward' -but it works. Maybe someone else will have a better idea.

First, recognize that '200611' is not immediately understandable as a valid date datatype.

SELECT left( convert( varchar(15), cast( ( '200611' + '01' ) AS datetime), 107 ), 3 ) + ' ' + left( '200611', 4 )

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf

"klabu" <noone_at_gmail_dot_comwrote in message news:12lfgcha53al985@.corp.supernews.com...

Quote:

Originally Posted by

Hi

how to conert (number) 200611 to string 'Nov 2006' ?

thanks

>

|||>how to convert (number) 200611 to string 'Nov 2006' ? <<

Some SQLs allow a YEAR TO MONTH data type; SQL Server is not one of
them. This is a shorthand for the range '2006-11-01 00:00:00' to
2006-11-31 23:59:59.99999' in Standard SQL. In this dialect, you will
need to build an auxiliary look up table to get those pairs.|||declare @.number int
set @.number = 200611

select left( datename( month, converted_to_date ), 3 ) + ' ' + datename(
year, converted_to_date )
from ( select cast( cast( @.number as char(6) ) + '01' as datetime ) as
converted_to_date ) as q

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"klabu" <noone_at_gmail_dot_comwrote in message
news:12lfgcha53al985@.corp.supernews.com...

Quote:

Originally Posted by

Hi
>
how to conert (number) 200611 to string 'Nov 2006' ?
>
thanks
>

|||Hi

select right(convert(char(11),cast( cast( @.number as char(6) ) + '01' as
datetime ),106),8)

"klabu" <noone_at_gmail_dot_comwrote in message
news:12lfgcha53al985@.corp.supernews.com...

Quote:

Originally Posted by

Hi
>
how to conert (number) 200611 to string 'Nov 2006' ?
>
thanks
>

2005 Table Partitioning

Hi - can anyone tell me how I can query which filegroup a particular
partition number belongs to'
For example I have a date range partition function & the query:
SELECT PartitionNo = $partition.DateRangePFN ('20070401')
Tells me this date would be placed in partition number 2 which should be in
a Filegroup called "April" but I just wanted to check that partition 2 is
actually mapped to filegroup April & I can't see how to do it!!
Thanks!!
MikeOn Jun 8, 3:56 pm, "Michael Knee" <mike...@.hotmail.com> wrote:
> Hi - can anyone tell me how I can query which filegroup a particular
> partition number belongs to'
> For example I have a date range partition function & the query:
> SELECT PartitionNo = $partition.DateRangePFN ('20070401')
> Tells me this date would be placed in partition number 2 which should be in
> a Filegroup called "April" but I just wanted to check that partition 2 is
> actually mapped to filegroup April & I can't see how to do it!!
> Thanks!!
> Mike
select
prv.boundary_id,
prv.value,
fg.name
from
sys.partition_range_values prv
inner join
sys.partition_functions pf on prv.function_id = pf.function_id
inner join
sys.destination_data_spaces dds on dds.destination_id =prv.boundary_id
inner join
sys.filegroups fg on fg.data_space_id = dds.data_space_id
where
pf.name = 'PF_DateRange'
and
prv.boundary_id = $partition.PF_DateRange('20050814')
See http://weblogs.sqlteam.com/dmauri/archive/2005/08/20/7593.aspx|||Exactly what I was after & excellent blog link as well - Thank You!
Mike
"M A Srinivas" <masri999@.gmail.com> wrote in message
news:1181303483.375856.68170@.r19g2000prf.googlegroups.com...
> On Jun 8, 3:56 pm, "Michael Knee" <mike...@.hotmail.com> wrote:
>> Hi - can anyone tell me how I can query which filegroup a particular
>> partition number belongs to'
>> For example I have a date range partition function & the query:
>> SELECT PartitionNo = $partition.DateRangePFN ('20070401')
>> Tells me this date would be placed in partition number 2 which should be
>> in
>> a Filegroup called "April" but I just wanted to check that partition 2 is
>> actually mapped to filegroup April & I can't see how to do it!!
>> Thanks!!
>> Mike
> select
> prv.boundary_id,
> prv.value,
> fg.name
> from
> sys.partition_range_values prv
> inner join
> sys.partition_functions pf on prv.function_id = pf.function_id
> inner join
> sys.destination_data_spaces dds on dds.destination_id => prv.boundary_id
> inner join
> sys.filegroups fg on fg.data_space_id = dds.data_space_id
> where
> pf.name = 'PF_DateRange'
> and
> prv.boundary_id = $partition.PF_DateRange('20050814')
>
> See http://weblogs.sqlteam.com/dmauri/archive/2005/08/20/7593.aspx
>

2005 Table Partitioning

Hi - can anyone tell me how I can query which filegroup a particular
partition number belongs to'
For example I have a date range partition function & the query:
SELECT PartitionNo = $partition.DateRangePFN ('20070401')
Tells me this date would be placed in partition number 2 which should be in
a Filegroup called "April" but I just wanted to check that partition 2 is
actually mapped to filegroup April & I can't see how to do it!!
Thanks!!
MikeOn Jun 8, 3:56 pm, "Michael Knee" <mike...@.hotmail.com> wrote:
> Hi - can anyone tell me how I can query which filegroup a particular
> partition number belongs to'
> For example I have a date range partition function & the query:
> SELECT PartitionNo = $partition.DateRangePFN ('20070401')
> Tells me this date would be placed in partition number 2 which should be i
n
> a Filegroup called "April" but I just wanted to check that partition 2 is
> actually mapped to filegroup April & I can't see how to do it!!
> Thanks!!
> Mike
select
prv.boundary_id,
prv.value,
fg.name
from
sys.partition_range_values prv
inner join
sys.partition_functions pf on prv.function_id = pf.function_id
inner join
sys.destination_data_spaces dds on dds.destination_id =
prv.boundary_id
inner join
sys.filegroups fg on fg.data_space_id = dds.data_space_id
where
pf.name = 'PF_DateRange'
and
prv.boundary_id = $partition.PF_DateRange('20050814')
See http://weblogs.sqlteam.com/dmauri/a...08/20/7593.aspx|||Exactly what I was after & excellent blog link as well - Thank You!
Mike
"M A Srinivas" <masri999@.gmail.com> wrote in message
news:1181303483.375856.68170@.r19g2000prf.googlegroups.com...
> On Jun 8, 3:56 pm, "Michael Knee" <mike...@.hotmail.com> wrote:
> select
> prv.boundary_id,
> prv.value,
> fg.name
> from
> sys.partition_range_values prv
> inner join
> sys.partition_functions pf on prv.function_id = pf.function_id
> inner join
> sys.destination_data_spaces dds on dds.destination_id =
> prv.boundary_id
> inner join
> sys.filegroups fg on fg.data_space_id = dds.data_space_id
> where
> pf.name = 'PF_DateRange'
> and
> prv.boundary_id = $partition.PF_DateRange('20050814')
>
> See http://weblogs.sqlteam.com/dmauri/a...08/20/7593.aspx
>

2005 Max CPU's

I need some clarification on the maximum number of CPUs that will be used by
SQL Server Standard Edition. The documentation states that 4 CPUs is the
maximum for Standard. If those 4 CPUs are dual core, will SQL Server
utilize all 8 logical CPUs? I read some posts that indicated that only 4
of the 8 logical CPUs will be used by SQL Server Standard Edition. Is this
true?
Thanks!
ChrisChris,
CPUs are physical chips, whether single, dual, or quad core. In a
conversation from a few months ago Jens K. Suessmeyer pointed to the
explanation about SQL Server Express over the same issue:
http://support.microsoft.com/kb/914278/en-us
RLF
"Chris Gallelli" <Chris.Gallelli@.hotmail.com> wrote in message
news:uQ25vsfXIHA.4808@.TK2MSFTNGP05.phx.gbl...
>I need some clarification on the maximum number of CPUs that will be used
>by SQL Server Standard Edition. The documentation states that 4 CPUs is
>the maximum for Standard. If those 4 CPUs are dual core, will SQL Server
>utilize all 8 logical CPUs? I read some posts that indicated that only 4
>of the 8 logical CPUs will be used by SQL Server Standard Edition. Is this
>true?
> Thanks!
> Chris
>|||Russell,
Thanks for the quick response. Your answer gives me some more insight into
the Express edition but I am looking specifically at SQL Server 2005
Standard Edition. If a machine running SQL Server 2005 Standard Edition has
4 Dual Core Processors, will SQL Server use all 8 logical CPUs?
The following link contains a discussion that is asking the same question:
http://www.codeprof.com/dev-archive/120/19-95-1201327.shtm
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23UK7czfXIHA.484@.TK2MSFTNGP06.phx.gbl...
> Chris,
> CPUs are physical chips, whether single, dual, or quad core. In a
> conversation from a few months ago Jens K. Suessmeyer pointed to the
> explanation about SQL Server Express over the same issue:
> http://support.microsoft.com/kb/914278/en-us
> RLF
> "Chris Gallelli" <Chris.Gallelli@.hotmail.com> wrote in message
> news:uQ25vsfXIHA.4808@.TK2MSFTNGP05.phx.gbl...
>>I need some clarification on the maximum number of CPUs that will be used
>>by SQL Server Standard Edition. The documentation states that 4 CPUs is
>>the maximum for Standard. If those 4 CPUs are dual core, will SQL Server
>>utilize all 8 logical CPUs? I read some posts that indicated that only 4
>>of the 8 logical CPUs will be used by SQL Server Standard Edition. Is
>>this true?
>> Thanks!
>> Chris
>|||Hi Chris
Yes, SQL 2005 Std Edn definitely uses all 8 CPUs. The restriction is only
for physical CPUs, not logical so it's possible to have 4 Quad Core CPUs
with 16 logical all working under SQL 2005 Std Edn
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Chris Gallelli" <Chris.Gallelli@.hotmail.com> wrote in message
news:%23jBFhMgXIHA.4476@.TK2MSFTNGP06.phx.gbl...
> Russell,
> Thanks for the quick response. Your answer gives me some more insight
> into the Express edition but I am looking specifically at SQL Server 2005
> Standard Edition. If a machine running SQL Server 2005 Standard Edition
> has 4 Dual Core Processors, will SQL Server use all 8 logical CPUs?
> The following link contains a discussion that is asking the same question:
> http://www.codeprof.com/dev-archive/120/19-95-1201327.shtm
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23UK7czfXIHA.484@.TK2MSFTNGP06.phx.gbl...
>> Chris,
>> CPUs are physical chips, whether single, dual, or quad core. In a
>> conversation from a few months ago Jens K. Suessmeyer pointed to the
>> explanation about SQL Server Express over the same issue:
>> http://support.microsoft.com/kb/914278/en-us
>> RLF
>> "Chris Gallelli" <Chris.Gallelli@.hotmail.com> wrote in message
>> news:uQ25vsfXIHA.4808@.TK2MSFTNGP05.phx.gbl...
>>I need some clarification on the maximum number of CPUs that will be used
>>by SQL Server Standard Edition. The documentation states that 4 CPUs is
>>the maximum for Standard. If those 4 CPUs are dual core, will SQL Server
>>utilize all 8 logical CPUs? I read some posts that indicated that only
>>4 of the 8 logical CPUs will be used by SQL Server Standard Edition. Is
>>this true?
>> Thanks!
>> Chris
>>
>|||Sorry. (But I thought the analogy carried through just fine.) - RLF
"Chris Gallelli" <Chris.Gallelli@.hotmail.com> wrote in message
news:%23jBFhMgXIHA.4476@.TK2MSFTNGP06.phx.gbl...
> Russell,
> Thanks for the quick response. Your answer gives me some more insight
> into the Express edition but I am looking specifically at SQL Server 2005
> Standard Edition. If a machine running SQL Server 2005 Standard Edition
> has 4 Dual Core Processors, will SQL Server use all 8 logical CPUs?
> The following link contains a discussion that is asking the same question:
> http://www.codeprof.com/dev-archive/120/19-95-1201327.shtm
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23UK7czfXIHA.484@.TK2MSFTNGP06.phx.gbl...
>> Chris,
>> CPUs are physical chips, whether single, dual, or quad core. In a
>> conversation from a few months ago Jens K. Suessmeyer pointed to the
>> explanation about SQL Server Express over the same issue:
>> http://support.microsoft.com/kb/914278/en-us
>> RLF
>> "Chris Gallelli" <Chris.Gallelli@.hotmail.com> wrote in message
>> news:uQ25vsfXIHA.4808@.TK2MSFTNGP05.phx.gbl...
>>I need some clarification on the maximum number of CPUs that will be used
>>by SQL Server Standard Edition. The documentation states that 4 CPUs is
>>the maximum for Standard. If those 4 CPUs are dual core, will SQL Server
>>utilize all 8 logical CPUs? I read some posts that indicated that only
>>4 of the 8 logical CPUs will be used by SQL Server Standard Edition. Is
>>this true?
>> Thanks!
>> Chris
>>
>

2005 Management studio can't connect to an alternate port?!?

In enterprise manager, I could define my sql server registration with
xxx.xxx.xxx.xxx, pppp where pppp is the non standard port number that
sql server is listening on.
Trying this in 2005 Management studio gives me:
TITLE: New Server Registration
---
Testing the registered server failed. Verify the server name, login
credentials, and database, and then click Test again.
---
ADDITIONAL INFORMATION:
Unknown ProviderConnection string is not valid (Microsoft SQL Server,
Error: 87)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=87&LinkId=20476
Any ideas? It will be bad if sql2k5 management tools can't connect to
servers on alternate ports. Is there some new way to define a port?Probably better to post SQL Server 2005 questions in the dedicated SQL
Server 2005 newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1
On 3/2/05 7:15 PM, in article
1109808938.817178.192760@.z14g2000cwz.googlegroups.com, "Josh Goodwin"
<jsgoods@.gmail.com> wrote:
> In enterprise manager, I could define my sql server registration with
> xxx.xxx.xxx.xxx, pppp where pppp is the non standard port number that
> sql server is listening on.
> Trying this in 2005 Management studio gives me:
> TITLE: New Server Registration
> ---
> Testing the registered server failed. Verify the server name, login
> credentials, and database, and then click Test again.
> ---
> ADDITIONAL INFORMATION:
> Unknown ProviderConnection string is not valid (Microsoft SQL Server,
> Error: 87)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLS
> erver&EvtID=87&LinkId=20476
>
> Any ideas? It will be bad if sql2k5 management tools can't connect to
> servers on alternate ports. Is there some new way to define a port?
>|||Try to creat a client-side Server Alias with the Client Configuration
Utility.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE4BD4ED.1C64%ten.xoc@.dnartreb.noraa...
Probably better to post SQL Server 2005 questions in the dedicated SQL
Server 2005 newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1
On 3/2/05 7:15 PM, in article
1109808938.817178.192760@.z14g2000cwz.googlegroups.com, "Josh Goodwin"
<jsgoods@.gmail.com> wrote:
> In enterprise manager, I could define my sql server registration with
> xxx.xxx.xxx.xxx, pppp where pppp is the non standard port number that
> sql server is listening on.
> Trying this in 2005 Management studio gives me:
> TITLE: New Server Registration
> ---
> Testing the registered server failed. Verify the server name, login
> credentials, and database, and then click Test again.
> ---
> ADDITIONAL INFORMATION:
> Unknown ProviderConnection string is not valid (Microsoft SQL Server,
> Error: 87)
> For help, click:
>
http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLS
> erver&EvtID=87&LinkId=20476
>
> Any ideas? It will be bad if sql2k5 management tools can't connect to
> servers on alternate ports. Is there some new way to define a port?
>

2005 - Remove Old Backups

Is there a way to remove backups older than a certain number of days with a
generic 2005 maintenance plan. I looked through the basic tasks available
and I did not see this option. There is an option to remove old history
(i.e. logs) but I did not see a task to remove old backups.
This functionality was available with the SQL Server 2000 maintenance plans.
Thanks!You didn't look hard enough<g>. There is a task for exactly that, deleting
old backup files. It is called the "Maintenance Cleanup Task".
Andrew J. Kelly SQL MVP
"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl...
> Is there a way to remove backups older than a certain number of days with
> a generic 2005 maintenance plan. I looked through the basic tasks
> available and I did not see this option. There is an option to remove old
> history (i.e. logs) but I did not see a task to remove old backups.
> This functionality was available with the SQL Server 2000 maintenance
> plans.
> Thanks!
>|||Hi Cgal,
As Andrew has mentioned, there does exists the Maintenance Task for
cleaning up backup files in the SQL 2005's Maintenance Plan items... (also
available in sql server 2000 through the maintenance plan creation
wizard...).
Create a new maintenance plan in sql2005 management studio, and in the
"Maintenance Plan Task" toolbox, choose the "Maintenance cleanup task",
there has setting for removing backup files of certain age of time....
#Maintenance Cleanup Task (Maintenance Plan)
http://msdn2.microsoft.com/en-us/library/ms177182.aspx
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com>
| References: <OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl>
| Subject: Re: 2005 - Remove Old Backups
| Date: Tue, 20 Dec 2005 18:07:16 -0500
| Lines: 22
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| X-RFC2646: Format=Flowed; Response
| Message-ID: <e2cIhobBGHA.3156@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: c-24-128-28-245.hsd1.nh.comcast.net 24.128.28.245
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:414948
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| You didn't look hard enough<g>. There is a task for exactly that,
deleting
| old backup files. It is called the "Maintenance Cleanup Task".
|
| --
| Andrew J. Kelly SQL MVP
|
|
| "Cgal" <cgallelli@.newsgroups.nospam> wrote in message
| news:OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl...
| > Is there a way to remove backups older than a certain number of days
with
| > a generic 2005 maintenance plan. I looked through the basic tasks
| > available and I did not see this option. There is an option to remove
old
| > history (i.e. logs) but I did not see a task to remove old backups.
| >
| > This functionality was available with the SQL Server 2000 maintenance
| > plans.
| >
| > Thanks!
| >
| >
|
|
|

2005 - Remove Old Backups

Is there a way to remove backups older than a certain number of days with a
generic 2005 maintenance plan. I looked through the basic tasks available
and I did not see this option. There is an option to remove old history
(i.e. logs) but I did not see a task to remove old backups.
This functionality was available with the SQL Server 2000 maintenance plans.
Thanks!
You didn't look hard enough<g>. There is a task for exactly that, deleting
old backup files. It is called the "Maintenance Cleanup Task".
Andrew J. Kelly SQL MVP
"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl...
> Is there a way to remove backups older than a certain number of days with
> a generic 2005 maintenance plan. I looked through the basic tasks
> available and I did not see this option. There is an option to remove old
> history (i.e. logs) but I did not see a task to remove old backups.
> This functionality was available with the SQL Server 2000 maintenance
> plans.
> Thanks!
>
|||Hi Cgal,
As Andrew has mentioned, there does exists the Maintenance Task for
cleaning up backup files in the SQL 2005's Maintenance Plan items... (also
available in sql server 2000 through the maintenance plan creation
wizard...).
Create a new maintenance plan in sql2005 management studio, and in the
"Maintenance Plan Task" toolbox, choose the "Maintenance cleanup task",
there has setting for removing backup files of certain age of time....
#Maintenance Cleanup Task (Maintenance Plan)
http://msdn2.microsoft.com/en-us/library/ms177182.aspx
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com>
| References: <OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl>
| Subject: Re: 2005 - Remove Old Backups
| Date: Tue, 20 Dec 2005 18:07:16 -0500
| Lines: 22
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| X-RFC2646: Format=Flowed; Response
| Message-ID: <e2cIhobBGHA.3156@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: c-24-128-28-245.hsd1.nh.comcast.net 24.128.28.245
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP12.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:414948
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| You didn't look hard enough<g>. There is a task for exactly that,
deleting
| old backup files. It is called the "Maintenance Cleanup Task".
|
| --
| Andrew J. Kelly SQL MVP
|
|
| "Cgal" <cgallelli@.newsgroups.nospam> wrote in message
| news:OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl...
| > Is there a way to remove backups older than a certain number of days
with
| > a generic 2005 maintenance plan. I looked through the basic tasks
| > available and I did not see this option. There is an option to remove
old
| > history (i.e. logs) but I did not see a task to remove old backups.
| >
| > This functionality was available with the SQL Server 2000 maintenance
| > plans.
| >
| > Thanks!
| >
| >
|
|
|

2005 - Remove Old Backups

Is there a way to remove backups older than a certain number of days with a
generic 2005 maintenance plan. I looked through the basic tasks available
and I did not see this option. There is an option to remove old history
(i.e. logs) but I did not see a task to remove old backups.
This functionality was available with the SQL Server 2000 maintenance plans.
Thanks!You didn't look hard enough<g>. There is a task for exactly that, deleting
old backup files. It is called the "Maintenance Cleanup Task".
--
Andrew J. Kelly SQL MVP
"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl...
> Is there a way to remove backups older than a certain number of days with
> a generic 2005 maintenance plan. I looked through the basic tasks
> available and I did not see this option. There is an option to remove old
> history (i.e. logs) but I did not see a task to remove old backups.
> This functionality was available with the SQL Server 2000 maintenance
> plans.
> Thanks!
>|||Hi Cgal,
As Andrew has mentioned, there does exists the Maintenance Task for
cleaning up backup files in the SQL 2005's Maintenance Plan items... (also
available in sql server 2000 through the maintenance plan creation
wizard...).
Create a new maintenance plan in sql2005 management studio, and in the
"Maintenance Plan Task" toolbox, choose the "Maintenance cleanup task",
there has setting for removing backup files of certain age of time....
#Maintenance Cleanup Task (Maintenance Plan)
http://msdn2.microsoft.com/en-us/library/ms177182.aspx
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com>
| References: <OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl>
| Subject: Re: 2005 - Remove Old Backups
| Date: Tue, 20 Dec 2005 18:07:16 -0500
| Lines: 22
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| X-RFC2646: Format=Flowed; Response
| Message-ID: <e2cIhobBGHA.3156@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: c-24-128-28-245.hsd1.nh.comcast.net 24.128.28.245
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:414948
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| You didn't look hard enough<g>. There is a task for exactly that,
deleting
| old backup files. It is called the "Maintenance Cleanup Task".
|
| --
| Andrew J. Kelly SQL MVP
|
|
| "Cgal" <cgallelli@.newsgroups.nospam> wrote in message
| news:OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl...
| > Is there a way to remove backups older than a certain number of days
with
| > a generic 2005 maintenance plan. I looked through the basic tasks
| > available and I did not see this option. There is an option to remove
old
| > history (i.e. logs) but I did not see a task to remove old backups.
| >
| > This functionality was available with the SQL Server 2000 maintenance
| > plans.
| >
| > Thanks!
| >
| >
|
|
|