Parsing values from sp_spaceused stored proc.

I am using sp_spaceused stored procedure to get the database_size result.
The result comes back as a string such as "512 KB" or "100 MB". I really
need a number value representation of the size. I though about parsing the
string, but I was not sure if there were any other results of the string
that I may not account for. Is there any standard way to parse this string,
or should I just assume that the format of the string can either have KB or
MB at the end and parse it based on that assumption?
--
Ken Varn
Senior Software Engineer
Diebold Inc.
EmailID = varnk
Domain = Diebold.com
--You can look at the contents of the stored proc via the Enterprise Manager
or
via
use master
go
sp_Helptext sp_spaceused
I don't see any values represented other than KB and MB
"Ken Varn" <nospam> wrote in message
news:eXe6n$6WFHA.1468@.tk2msftngp13.phx.gbl...
> I am using sp_spaceused stored procedure to get the database_size result.
> The result comes back as a string such as "512 KB" or "100 MB". I really
> need a number value representation of the size. I though about parsing
the
> string, but I was not sure if there were any other results of the string
> that I may not account for. Is there any standard way to parse this
string,
> or should I just assume that the format of the string can either have KB
or
> MB at the end and parse it based on that assumption?
> --
> --
> Ken Varn
> Senior Software Engineer
> Diebold Inc.
> EmailID = varnk
> Domain = Diebold.com
> --
>|||Ken
You can create a table and store an output from sp_spaceused there.
If I understood you need the number only. So see if this helps you.
CREATE FUNCTION dbo.CleanChars
(@.str VARCHAR(8000), @.validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
']%',@.str), 1) ,'')
RETURN @.str
END
GO
CREATE TABLE sometable
(namestr VARCHAR(20) PRIMARY KEY)
INSERT INTO sometable VALUES ('AB-C123')
INSERT INTO sometable VALUES ('A,B,C')
SELECT namestr,
dbo.CleanChars(namestr,'A-Z 0-9')
FROM sometable
"Ken Varn" <nospam> wrote in message
news:eXe6n$6WFHA.1468@.tk2msftngp13.phx.gbl...
> I am using sp_spaceused stored procedure to get the database_size result.
> The result comes back as a string such as "512 KB" or "100 MB". I really
> need a number value representation of the size. I though about parsing
the
> string, but I was not sure if there were any other results of the string
> that I may not account for. Is there any standard way to parse this
string,
> or should I just assume that the format of the string can either have KB
or
> MB at the end and parse it based on that assumption?
> --
> --
> Ken Varn
> Senior Software Engineer
> Diebold Inc.
> EmailID = varnk
> Domain = Diebold.com
> --
>|||Ken,
To be sure, see the sp code from master database.
exec master..sp_helptext sp_spaceused
go
AMB
"Ken Varn" wrote:

> I am using sp_spaceused stored procedure to get the database_size result.
> The result comes back as a string such as "512 KB" or "100 MB". I really
> need a number value representation of the size. I though about parsing th
e
> string, but I was not sure if there were any other results of the string
> that I may not account for. Is there any standard way to parse this strin
g,
> or should I just assume that the format of the string can either have KB o
r
> MB at the end and parse it based on that assumption?
> --
> --
> Ken Varn
> Senior Software Engineer
> Diebold Inc.
> EmailID = varnk
> Domain = Diebold.com
> --
>
>

Partitioning - logicall transparent?

In Oracle we have implemented database table partitioning for a table
exceeding 130 million rows, to alleviate maintenance issues (update stats,
back up etc). The partitions are by date such that after a month a new table
partition is created and inserts commence on the new partition. In this way
we can update stats on the last partition as required, the older partitions
being effectively static do not need further maintenance. All of this is
transparent to the application the database is supporting, we have also seen
good improvements in query performance.
Is there a way to do this for SQL 2000? Would it be transparent to the
application the database is supporting?
I would expect this table in a particular implementation to exceed 400
million rows per year once in production!
Hi Andy
SQL 2000 cannot partition a table directly - it uses an alternative approach
based on views. It is generally transparent to applications except where the
table being partitioned was using an identity (sequence).
You can read more on partitioned views here:
http://msdn.microsoft.com/library/en...es_06_17zr.asp
SQL Server 2005 includes direct table partitioning, although it's only in
Beta at this stage. It's partitioning support includes range which should
support your date ranges, but not hashlist or composite partitioning.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!
|||Right now SQL Server 2000 has "distributed partitioned views" to partition
data. While it can have some advantages in the areas you are addressing it
is not as convenient as you would hope in regards to maintenance and such.
SQL 2005 will totally address this issue with some really great partitioning
functionality but it is currently only in Beta. Check out BOL under
"distributed partitioned views" for more information on the current
capabilities and have a look at http://www.microsoft.com/sql/2005/ for 2005
features.
Andrew J. Kelly SQL MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!

Partitioning - logicall transparent?

In Oracle we have implemented database table partitioning for a table
exceeding 130 million rows, to alleviate maintenance issues (update stats,
back up etc). The partitions are by date such that after a month a new table
partition is created and inserts commence on the new partition. In this way
we can update stats on the last partition as required, the older partitions
being effectively static do not need further maintenance. All of this is
transparent to the application the database is supporting, we have also seen
good improvements in query performance.
Is there a way to do this for SQL 2000? Would it be transparent to the
application the database is supporting?
I would expect this table in a particular implementation to exceed 400
million rows per year once in production!Hi Andy
SQL 2000 cannot partition a table directly - it uses an alternative approach
based on views. It is generally transparent to applications except where the
table being partitioned was using an identity (sequence).
You can read more on partitioned views here:
http://msdn.microsoft.com/library/e...des_06_17zr.asp
SQL Server 2005 includes direct table partitioning, although it's only in
Beta at this stage. It's partitioning support includes range which should
support your date ranges, but not hashlist or composite partitioning.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!|||Right now SQL Server 2000 has "distributed partitioned views" to partition
data. While it can have some advantages in the areas you are addressing it
is not as convenient as you would hope in regards to maintenance and such.
SQL 2005 will totally address this issue with some really great partitioning
functionality but it is currently only in Beta. Check out BOL under
"distributed partitioned views" for more information on the current
capabilities and have a look at http://www.microsoft.com/sql/2005/ for 2005
features.
Andrew J. Kelly SQL MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!

Partitioning - logicall transparent?

In Oracle we have implemented database table partitioning for a table
exceeding 130 million rows, to alleviate maintenance issues (update stats,
back up etc). The partitions are by date such that after a month a new table
partition is created and inserts commence on the new partition. In this way
we can update stats on the last partition as required, the older partitions
being effectively static do not need further maintenance. All of this is
transparent to the application the database is supporting, we have also seen
good improvements in query performance.
Is there a way to do this for SQL 2000? Would it be transparent to the
application the database is supporting?
I would expect this table in a particular implementation to exceed 400
million rows per year once in production!Hi Andy
SQL 2000 cannot partition a table directly - it uses an alternative approach
based on views. It is generally transparent to applications except where the
table being partitioned was using an identity (sequence).
You can read more on partitioned views here:
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_06_17zr.asp
SQL Server 2005 includes direct table partitioning, although it's only in
Beta at this stage. It's partitioning support includes range which should
support your date ranges, but not hashlist or composite partitioning.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!|||Right now SQL Server 2000 has "distributed partitioned views" to partition
data. While it can have some advantages in the areas you are addressing it
is not as convenient as you would hope in regards to maintenance and such.
SQL 2005 will totally address this issue with some really great partitioning
functionality but it is currently only in Beta. Check out BOL under
"distributed partitioned views" for more information on the current
capabilities and have a look at http://www.microsoft.com/sql/2005/ for 2005
features.
--
Andrew J. Kelly SQL MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!

Paramter posting back?

I have a report with four paramters.
A "Start Year" and "End Year" that are strings with values from a query, so
available valuse are 2003,2004. 2005, and 2006.
Two datetime for a "Start Transaction Date" and "End Transaction Date".
When I set the "Start Year" parameter, the report seems to post back and I
get the error:
The value provided for the report parameter 'TransStart' is not valid
for its type. (rsReportParameterTypeMismatch) Get Online Help
The paramter was empty, so of course it is not valid. How do I stop this
behavior? The paramters are not dependant on each other and do not use
dynamic SQL.
Any help would be appreciated.
Thanks Shannondid you set allow blank for the parameter that you allow it to be empty?

Parameters in select clause

Hi all,
My query needs to work with different back ends say oracle, sql server,db2
etc. so i need to make use of only the ansi sql. I need something like
="select col1,col2,"&_
"(case when parameters!gender.value='m' then 'some calculation'"&_
"when parameters!gender.value='f' then 'some other calculation'"&_
"end) as calc_name,"&
"from table1" & parameters!where_clause.value
but i could not make the above code work. Please let me the correct syntax
to get the above kind of query work.
Thanks,
RSUserWhat you should do is set that expression to a textbox first so you can see
what you are getting. Otherwise any little thing gets the same result, it
doesn't work but you don't know why. I notice below you have an comma right
before from.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RSUser" <RSUser@.discussions.microsoft.com> wrote in message
news:C0AA0793-2606-4D38-AC7A-C37162EA4EDE@.microsoft.com...
> Hi all,
> My query needs to work with different back ends say oracle, sql server,db2
> etc. so i need to make use of only the ansi sql. I need something like
> ="select col1,col2,"&_
> "(case when parameters!gender.value='m' then 'some calculation'"&_
> "when parameters!gender.value='f' then 'some other calculation'"&_
> "end) as calc_name,"&
> "from table1" & parameters!where_clause.value
> but i could not make the above code work. Please let me the correct syntax
> to get the above kind of query work.
> Thanks,
> RSUser
>|||Hi, RSUser
you can use an immediate if :
="select col1,col2," & IIF(Parameters!Gender.Value ='M', "some
calculation","some other calculation") & " as calc_name from table1" &
Parameters!where_clause.value"
Just copy and paste it.
Please, vote.
ProJester, MSN Programmer.
"RSUser" wrote:
> Hi all,
> My query needs to work with different back ends say oracle, sql server,db2
> etc. so i need to make use of only the ansi sql. I need something like
> ="select col1,col2,"&_
> "(case when parameters!gender.value='m' then 'some calculation'"&_
> "when parameters!gender.value='f' then 'some other calculation'"&_
> "end) as calc_name,"&
> "from table1" & parameters!where_clause.value
> but i could not make the above code work. Please let me the correct syntax
> to get the above kind of query work.
> Thanks,
> RSUser
>|||Use the Immediate If as Projester indicates. The reason is that the dynamic
query is parsed and fixed up as a VBScript first. Then the results are sent
to SQL ... The SQL Case you are using should be replaced by IIF so that the
VBScript can generate the correct SQL.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"RSUser" <RSUser@.discussions.microsoft.com> wrote in message
news:C0AA0793-2606-4D38-AC7A-C37162EA4EDE@.microsoft.com...
> Hi all,
> My query needs to work with different back ends say oracle, sql server,db2
> etc. so i need to make use of only the ansi sql. I need something like
> ="select col1,col2,"&_
> "(case when parameters!gender.value='m' then 'some calculation'"&_
> "when parameters!gender.value='f' then 'some other calculation'"&_
> "end) as calc_name,"&
> "from table1" & parameters!where_clause.value
> but i could not make the above code work. Please let me the correct syntax
> to get the above kind of query work.
> Thanks,
> RSUser
>|||Hi all,
Thanks for your replies. My case statement is so complex as i have to check
for 10 different values and do different calculations for each case unlike
the example i provided where there was just two, male and female. Thus i need
further help as to decide if it is good to use iif or to use external code
like vb.net or c# and solve the problem.
Thanks,
RSUser
"Wayne Snyder" wrote:
> Use the Immediate If as Projester indicates. The reason is that the dynamic
> query is parsed and fixed up as a VBScript first. Then the results are sent
> to SQL ... The SQL Case you are using should be replaced by IIF so that the
> VBScript can generate the correct SQL.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "RSUser" <RSUser@.discussions.microsoft.com> wrote in message
> news:C0AA0793-2606-4D38-AC7A-C37162EA4EDE@.microsoft.com...
> > Hi all,
> >
> > My query needs to work with different back ends say oracle, sql server,db2
> > etc. so i need to make use of only the ansi sql. I need something like
> >
> > ="select col1,col2,"&_
> > "(case when parameters!gender.value='m' then 'some calculation'"&_
> > "when parameters!gender.value='f' then 'some other calculation'"&_
> > "end) as calc_name,"&
> > "from table1" & parameters!where_clause.value
> >
> > but i could not make the above code work. Please let me the correct syntax
> > to get the above kind of query work.
> >
> > Thanks,
> > RSUser
> >
>
>