Default Field Value for DateTime & SmallDateTime

In SQL Server 2000 / Asp.Net I am trying to use default values for all fields; hoping to eliminate nulls.

For number and character fields, the default is pretty obvious, but is there any empty value for a date field? I think a null there might be better than putting in a bogus date, at least it can be tested for.

Are there any more developend ideas on this question?

Many thanks
Mike ThomasHi, Mike.
The choise depends directly on the problem U r solving. Sometimes GETDATE() helps... just analize Ur task and make a corresponding conclusion: what value is permitable as a default one in the definite case...

Alex.sql

Default constraints

Hello

I have about 40 tables where I need to increase the size of the admission number field from smallint to Int . The field used the tables' primary key or part of the key. I am a programmer, but fairly new to SQL Server. I have written some scripts to remove the defaults and primary key constraints off of this field in each table, do the field resize and then put the constraints back on. The scripts get the names and settings of the constraints from system tables before the constraints are dropped, so that they can be reapplied after the field size change.

Is this the best way to do it? Or should I be looking at a DTS package?

I would be grateful for your advice

Shirley

To me (unless you have a database design tool), this sounds like a sufficient way to do it. It is never perfectly easy or anything, but you can get most everything you need to generate a script using the system tables, so I would do that. Sounds messy of course, since you also have to deal with foreign key constraints, but if it is just 40 tables, that probably isn't too bad. (I assume you will make ths size plenty big for the next sixty years this time :)

|||

Thanks very much for that. Yes, Integer will definitely be a big enough field size for the forseeable future for our admission number!

Shirley

decreasing number of records.. is it important?

hi,

If we consider the following scenerio:
a school with 500 students.
On average student takes [x] courses per year, and [y] tests per course.

table of grades:
+----+----+---+---+
| student_id | course_id | term_id | grade |
+----+----+---+---+
| | | | |
+----+----+---+---+
| | | | |
+----+----+---+---+

as average, x = 20, y =6 then
number of records = 500 * 20 * 6 = 120,000 record per year.

is this design correct considering number of records generated per year?
Is it important to reduce the "number of records" or no?

thank you.> as average, x = 20, y =6 then
> number of records = 500 * 20 * 6 = 120,000 record per year.
> is this design correct considering number of records generated per year?
> Is it important to reduce the "number of records" or no?
> thank you.

500 * 20 * 6 = 60 thousand, not 120 thousand.

This is what a database is for - storing information.

If that is the information you want to store, then that is what you should
use.

Many databases will hold many billions of rows.

--

Consider an analagy -
This dictionary has too many words - should I remove some ?

S.

declare programaticly a X number of variables

Hello everyone,
I really need some help; I need to declare dinamicly an number of records to be pulled out of the table.. so I need to declare first
DECLARE @.NumberOfRecords int
then
DECLARE @.i int
set @.i=0
and
WHILE @.i<@.NumberOfReocords
begin
declare
@.Record int <-- Here is my Problem
set @.i= @.i+1
end
all the variables @.NumberOfRecords, and the @.Record are passed to a stored procedure. Can some one help me plese. Any input is appreciated. Thank you.I did not quite understand your question. Are you asking how to declare number of variables dynamically? If so you could use dynamic SQL. See sp_executesql topic in Books Online for more details.|||sometimes i need to pull out 3 records.. other times 5 records, etc... and each time i need to specify the recordID ( e.g. 1000323, 1000356, 1000365) ... so each time i need to have a different number of @.RecordID's @.RecordID1, @.RecordID2.. etc.
Once again thank you|||Perhaps if you tell us the exact problem you are trying to solve, it will be easier to suggest a more efficient solution than using dynamic SQL or relying on multiple variables. For example, TOP clause now takes any expression in SQL Server 2005 that you can use to dynamically retrieve n number of rows. Ex:

select top (select count(*) from tbl1) *
from tbl2
order by keycol;

You could also use row_number() or identity to generate a sequence number and then process rows based on it.|||Basicly, I have a database and I was asket to code a ASP webform in C# in which the user forst enter am integer representing the number of records she/he needs to pull out of the database and the enter the records ID's ... it is for statistical reasons... I thought that the easeast way is to create a store procedure on which to pass the parameters... Any ideeas. Thank you|||

Order of records does not make sense in a table since it is essentially an unordered set of rows. You can only talk about records if the order in which you fetch rows or count rows is deterministic. So to this effect, you have to include an ORDER BY in your query against the table for example to say number records based on the sort order. You can easily number rows in SQL Server 2005 using the ROW_NUMBER function. This allows you to generate a sequential number for each row in a result set based on a particular order. This can be combined with a filter to get specific number of rows based on the order. Ex:

-- fetches 1 to 10 rows sorted by keycol order
with paged_t

as

(

select *, row_number() over(order by keycol) as rownum from table

)

select * from page_t
where rownum between 1 and 10;

-- fetches 11 to 20 rows sorted by keycol order
with paged_t

as

(

select *, row_number() over(order by keycol) as rownum from table

)

select * from page_t
where rownum between 11 and 20;

Note that this doesn't guarantee that you will get distinct rows for each selection since it depends on other transactions against the table. If you are inserting new rows that can appear in the beginning, it is possible to get same row twice and so on. There are ways to avoid this but it will affect concurrency (using serializable isolation level for example). The scenario I described above is a paging scenario.

On the other hand, if you just need some N number of rows sorted by some column every time then you can just use the TOP clause in a SELECT statement. You can number the rows easily on the client-side.

-- @.numrows is parameter to SP:
select top(@.numrows) *
from tbl
order by keycol;

|||

Umachandar Jayachandran - MS wrote:

Order of records does not make sense in a table since it is essentially an unordered set of rows. You can only talk about records if the order in which you fetch rows or count rows is deterministic. So to this effect, you have to include an ORDER BY in your query against the table for example to say number records based on the sort order. You can easily number rows in SQL Server 2005 using the ROW_NUMBER function. This allows you to generate a sequential number for each row in a result set based on a particular order. This can be combined with a filter to get specific number of rows based on the order. Ex:

-- fetches 1 to 10 rows sorted by keycol order
with paged_t

as

(

select *, row_number() over(order by keycol) as rownum from table

)

select * from page_t
where rownum between 1 and 10;

-- fetches 11 to 20 rows sorted by keycol order
with paged_t

as

(

select *, row_number() over(order by keycol) as rownum from table

)

select * from page_t
where rownum between 11 and 20;

Note that this doesn't guarantee that you will get distinct rows for each selection since it depends on other transactions against the table. If you are inserting new rows that can appear in the beginning, it is possible to get same row twice and so on. There are ways to avoid this but it will affect concurrency (using serializable isolation level for example). The scenario I described above is a paging scenario.

On the other hand, if you just need some N number of rows sorted by some column every time then you can just use the TOP clause in a SELECT statement. You can number the rows easily on the client-side.

-- @.numrows is parameter to SP:
select top(@.numrows) *
from tbl
order by keycol;

Hi Jayachandran

excellent, i am also interested but I am very much keen to know how can it(row sequence #) be generated in SQL server 2000 using query.

please help

thanks in advance

|||

Hi,

Can u tell is that equivalent to this query

select * from <table_name> order by column1 limit 0,10 -- iam taking top 10 records from the table.

But iam using this in query in a cursor and i will get the value (0,10) from the result set of an other query

Please Help, Iam new to MYSQL

Thanks,

Murali.V

|||

See this post for more details on how to do it in SQL Server 2000 using identity column and temporary table approach.

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

declare programaticly a X number of variables

Hello everyone,
I really need some help; I need to declare dinamicly an number of records to be pulled out of the table.. so I need to declare first
DECLARE @.NumberOfRecords int
then
DECLARE @.i int
set @.i=0
and
WHILE @.i<@.NumberOfReocords
begin
declare
@.Record int <-- Here is my Problem
set @.i= @.i+1
end
all the variables @.NumberOfRecords, and the @.Record are passed to a stored procedure. Can some one help me plese. Any input is appreciated. Thank you.I did not quite understand your question. Are you asking how to declare number of variables dynamically? If so you could use dynamic SQL. See sp_executesql topic in Books Online for more details.|||sometimes i need to pull out 3 records.. other times 5 records, etc... and each time i need to specify the recordID ( e.g. 1000323, 1000356, 1000365) ... so each time i need to have a different number of @.RecordID's @.RecordID1, @.RecordID2.. etc.
Once again thank you|||Perhaps if you tell us the exact problem you are trying to solve, it will be easier to suggest a more efficient solution than using dynamic SQL or relying on multiple variables. For example, TOP clause now takes any expression in SQL Server 2005 that you can use to dynamically retrieve n number of rows. Ex:

select top (select count(*) from tbl1) *
from tbl2
order by keycol;

You could also use row_number() or identity to generate a sequence number and then process rows based on it.|||Basicly, I have a database and I was asket to code a ASP webform in C# in which the user forst enter am integer representing the number of records she/he needs to pull out of the database and the enter the records ID's ... it is for statistical reasons... I thought that the easeast way is to create a store procedure on which to pass the parameters... Any ideeas. Thank you|||

Order of records does not make sense in a table since it is essentially an unordered set of rows. You can only talk about records if the order in which you fetch rows or count rows is deterministic. So to this effect, you have to include an ORDER BY in your query against the table for example to say number records based on the sort order. You can easily number rows in SQL Server 2005 using the ROW_NUMBER function. This allows you to generate a sequential number for each row in a result set based on a particular order. This can be combined with a filter to get specific number of rows based on the order. Ex:

-- fetches 1 to 10 rows sorted by keycol order
with paged_t

as

(

select *, row_number() over(order by keycol) as rownum from table

)

select * from page_t
where rownum between 1 and 10;

-- fetches 11 to 20 rows sorted by keycol order
with paged_t

as

(

select *, row_number() over(order by keycol) as rownum from table

)

select * from page_t
where rownum between 11 and 20;

Note that this doesn't guarantee that you will get distinct rows for each selection since it depends on other transactions against the table. If you are inserting new rows that can appear in the beginning, it is possible to get same row twice and so on. There are ways to avoid this but it will affect concurrency (using serializable isolation level for example). The scenario I described above is a paging scenario.

On the other hand, if you just need some N number of rows sorted by some column every time then you can just use the TOP clause in a SELECT statement. You can number the rows easily on the client-side.

-- @.numrows is parameter to SP:
select top(@.numrows) *
from tbl
order by keycol;

|||

Umachandar Jayachandran - MS wrote:

Order of records does not make sense in a table since it is essentially an unordered set of rows. You can only talk about records if the order in which you fetch rows or count rows is deterministic. So to this effect, you have to include an ORDER BY in your query against the table for example to say number records based on the sort order. You can easily number rows in SQL Server 2005 using the ROW_NUMBER function. This allows you to generate a sequential number for each row in a result set based on a particular order. This can be combined with a filter to get specific number of rows based on the order. Ex:

-- fetches 1 to 10 rows sorted by keycol order
with paged_t

as

(

select *, row_number() over(order by keycol) as rownum from table

)

select * from page_t
where rownum between 1 and 10;

-- fetches 11 to 20 rows sorted by keycol order
with paged_t

as

(

select *, row_number() over(order by keycol) as rownum from table

)

select * from page_t
where rownum between 11 and 20;

Note that this doesn't guarantee that you will get distinct rows for each selection since it depends on other transactions against the table. If you are inserting new rows that can appear in the beginning, it is possible to get same row twice and so on. There are ways to avoid this but it will affect concurrency (using serializable isolation level for example). The scenario I described above is a paging scenario.

On the other hand, if you just need some N number of rows sorted by some column every time then you can just use the TOP clause in a SELECT statement. You can number the rows easily on the client-side.

-- @.numrows is parameter to SP:
select top(@.numrows) *
from tbl
order by keycol;

Hi Jayachandran

excellent, i am also interested but I am very much keen to know how can it(row sequence #) be generated in SQL server 2000 using query.

please help

thanks in advance

|||

Hi,

Can u tell is that equivalent to this query

select * from <table_name> order by column1 limit 0,10 -- iam taking top 10 records from the table.

But iam using this in query in a cursor and i will get the value (0,10) from the result set of an other query

Please Help, Iam new to MYSQL

Thanks,

Murali.V

|||

See this post for more details on how to do it in SQL Server 2000 using identity column and temporary table approach.

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

declare programaticly a X number of variables

Hello everyone,
I really need some help; I need to declare dinamicly an number of records to be pulled out of the table.. so I need to declare first
DECLARE @.NumberOfRecords int
then
DECLARE @.i int
set @.i=0
and
WHILE @.i<@.NumberOfReocords
begin
declare
@.Record int <-- Here is my Problem
set @.i= @.i+1
end
all the variables @.NumberOfRecords, and the @.Record are passed to a stored procedure. Can some one help me plese. Any input is appreciated. Thank you.I did not quite understand your question. Are you asking how to declare number of variables dynamically? If so you could use dynamic SQL. See sp_executesql topic in Books Online for more details.|||sometimes i need to pull out 3 records.. other times 5 records, etc... and each time i need to specify the recordID ( e.g. 1000323, 1000356, 1000365) ... so each time i need to have a different number of @.RecordID's @.RecordID1, @.RecordID2.. etc.
Once again thank you|||Perhaps if you tell us the exact problem you are trying to solve, it will be easier to suggest a more efficient solution than using dynamic SQL or relying on multiple variables. For example, TOP clause now takes any expression in SQL Server 2005 that you can use to dynamically retrieve n number of rows. Ex:

select top (select count(*) from tbl1) *
from tbl2
order by keycol;

You could also use row_number() or identity to generate a sequence number and then process rows based on it.|||Basicly, I have a database and I was asket to code a ASP webform in C# in which the user forst enter am integer representing the number of records she/he needs to pull out of the database and the enter the records ID's ... it is for statistical reasons... I thought that the easeast way is to create a store procedure on which to pass the parameters... Any ideeas. Thank you|||

Order of records does not make sense in a table since it is essentially an unordered set of rows. You can only talk about records if the order in which you fetch rows or count rows is deterministic. So to this effect, you have to include an ORDER BY in your query against the table for example to say number records based on the sort order. You can easily number rows in SQL Server 2005 using the ROW_NUMBER function. This allows you to generate a sequential number for each row in a result set based on a particular order. This can be combined with a filter to get specific number of rows based on the order. Ex:

-- fetches 1 to 10 rows sorted by keycol order
with paged_t

as

(

select *, row_number() over(order by keycol) as rownum from table

)

select * from page_t
where rownum between 1 and 10;

-- fetches 11 to 20 rows sorted by keycol order
with paged_t

as

(

select *, row_number() over(order by keycol) as rownum from table

)

select * from page_t
where rownum between 11 and 20;

Note that this doesn't guarantee that you will get distinct rows for each selection since it depends on other transactions against the table. If you are inserting new rows that can appear in the beginning, it is possible to get same row twice and so on. There are ways to avoid this but it will affect concurrency (using serializable isolation level for example). The scenario I described above is a paging scenario.

On the other hand, if you just need some N number of rows sorted by some column every time then you can just use the TOP clause in a SELECT statement. You can number the rows easily on the client-side.

-- @.numrows is parameter to SP:
select top(@.numrows) *
from tbl
order by keycol;

|||

Umachandar Jayachandran - MS wrote:

Order of records does not make sense in a table since it is essentially an unordered set of rows. You can only talk about records if the order in which you fetch rows or count rows is deterministic. So to this effect, you have to include an ORDER BY in your query against the table for example to say number records based on the sort order. You can easily number rows in SQL Server 2005 using the ROW_NUMBER function. This allows you to generate a sequential number for each row in a result set based on a particular order. This can be combined with a filter to get specific number of rows based on the order. Ex:

-- fetches 1 to 10 rows sorted by keycol order
with paged_t

as

(

select *, row_number() over(order by keycol) as rownum from table

)

select * from page_t
where rownum between 1 and 10;

-- fetches 11 to 20 rows sorted by keycol order
with paged_t

as

(

select *, row_number() over(order by keycol) as rownum from table

)

select * from page_t
where rownum between 11 and 20;

Note that this doesn't guarantee that you will get distinct rows for each selection since it depends on other transactions against the table. If you are inserting new rows that can appear in the beginning, it is possible to get same row twice and so on. There are ways to avoid this but it will affect concurrency (using serializable isolation level for example). The scenario I described above is a paging scenario.

On the other hand, if you just need some N number of rows sorted by some column every time then you can just use the TOP clause in a SELECT statement. You can number the rows easily on the client-side.

-- @.numrows is parameter to SP:
select top(@.numrows) *
from tbl
order by keycol;

Hi Jayachandran

excellent, i am also interested but I am very much keen to know how can it(row sequence #) be generated in SQL server 2000 using query.

please help

thanks in advance

|||

Hi,

Can u tell is that equivalent to this query

select * from <table_name> order by column1 limit 0,10 -- iam taking top 10 records from the table.

But iam using this in query in a cursor and i will get the value (0,10) from the result set of an other query

Please Help, Iam new to MYSQL

Thanks,

Murali.V

|||

See this post for more details on how to do it in SQL Server 2000 using identity column and temporary table approach.

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

Declare a local var

Hi,
I am trying to do the following but I am stucked. I have a table
workdb..a (column num), I want to get the maximum number of column num
to store it as a local variable.
TABLE WORKDB..A
num
1
2
3
4
5
DECLARE @.max
Thanks a lot!!!
Michael
Michael,
DECLARE @.max
set @.max = (select max(num) from dbo.a)
-- or
select @.max = max(num) from dbo.a
AMB
"Michael" wrote:

> Hi,
> I am trying to do the following but I am stucked. I have a table
> workdb..a (column num), I want to get the maximum number of column num
> to store it as a local variable.
> TABLE WORKDB..A
> num
> 1
> 2
> 3
> 4
> 5
>
> DECLARE @.max
> Thanks a lot!!!
> Michael
>

Declare a local var

Hi,
I am trying to do the following but I am stucked. I have a table
workdb..a (column num), I want to get the maximum number of column num
to store it as a local variable.
TABLE WORKDB..A
num
1
2
3
4
5
DECLARE @.max
Thanks a lot!!!
MichaelMichael,
DECLARE @.max
set @.max = (select max(num) from dbo.a)
-- or
select @.max = max(num) from dbo.a
AMB
"Michael" wrote:

> Hi,
> I am trying to do the following but I am stucked. I have a table
> workdb..a (column num), I want to get the maximum number of column num
> to store it as a local variable.
> TABLE WORKDB..A
> num
> 1
> 2
> 3
> 4
> 5
>
> DECLARE @.max
> Thanks a lot!!!
> Michael
>|||On Apr 9, 12:10 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> Michael,
> DECLARE @.max
> set @.max = (select max(num) from dbo.a)
> -- or
> select @.max = max(num) from dbo.a
> AMB
>
> "Michael" wrote:
>
>
>
>
> - Show quoted text -
Great!! Thank you!!!

Declare a local var

Hi,
I am trying to do the following but I am stucked. I have a table
workdb..a (column num), I want to get the maximum number of column num
to store it as a local variable.
TABLE WORKDB..A
num
1
2
3
4
5
DECLARE @.max
Thanks a lot!!!
MichaelMichael,
DECLARE @.max
set @.max = (select max(num) from dbo.a)
-- or
select @.max = max(num) from dbo.a
AMB
"Michael" wrote:
> Hi,
> I am trying to do the following but I am stucked. I have a table
> workdb..a (column num), I want to get the maximum number of column num
> to store it as a local variable.
> TABLE WORKDB..A
> num
> 1
> 2
> 3
> 4
> 5
>
> DECLARE @.max
> Thanks a lot!!!
> Michael
>

decimal to hex

Is there a way to create a SP or UDF that converts a decimal number to hexidecimal notation?run this in Query Analyzer

SELECT CAST(255 as varbinary)
SELECT CAST(10 as varbinary)
SELECT CAST(26 as varbinary)

cool eh?|||very nice, thanks!|||Can I stick something like this in a Formula field?
For example, say I have table Orders with columns OrderID (of type int) and OrderNumber.
I tried putting in the Formula (under "design table")

SELECT CAST(([OrderID] * 20) as varbinary)

but it apparently it doesn't work. Can I create something like this in the formula field?

Decimal to ASCII conversion

I need to convert a decimal number to 2 ASCII characters using SQL. For example, 13110 (16 bit word which is 2 bytes) would be a decimal value representing 36 in ASCII.
Any ideas?I don't get the transformation fomula.

You get 36, I suppose the characters '3'+'6'. '3' has the ASCII value 51 (decimal) and '6' has the value 54 (decimal). I do not understand how 51 and 54 can be derivated from 13110. Can you help me?|||I'm sorry, actually it's quite straight forward: 51 is the one word, and 54 is the other word, and together they form your 13110 (decimal) or better to see 3134 (hex).

Anyway, the code is:
SELECT char(floor(13110/256)), char(13110-floor(13110/256)*256)

Cheers!|||That worked! You the Man! Thanks Bunches!

decimal precision of number output

I have a field on my report that is getting a number back from the supporting
stored proc. This number is sometimes a whole number, sometimes a decimal
number.
I want the number output to have 2 decimal places, no matter what the input.
I have tried the conditional formatting on the textbox, but it doesn't seem
to work. Is there a bug? Or am I doing something wrong?
I have input into the "format" section of the properties on the textbox:
#####.##
#,##
d2
D2
2D
2d
d#.##
d#,##
and many variations of the supposed formatting that is supposed to work.
Any suggestions?
Thanks!Hi,
Use an expression like FormatNumber(data,2).
Thanks|||The formatcode property of textboxes uses .NET format codes as defined on
MSDN. For numeric values check these links:
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp
In your case, it sounds like you want formatcode N2
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"david boardman" <davidboardman@.discussions.microsoft.com> wrote in message
news:8A5FB147-B81B-40AB-9929-091F141358AD@.microsoft.com...
>I have a field on my report that is getting a number back from the
>supporting
> stored proc. This number is sometimes a whole number, sometimes a decimal
> number.
> I want the number output to have 2 decimal places, no matter what the
> input.
> I have tried the conditional formatting on the textbox, but it doesn't
> seem
> to work. Is there a bug? Or am I doing something wrong?
> I have input into the "format" section of the properties on the textbox:
> #####.##
> #,##
> d2
> D2
> 2D
> 2d
> d#.##
> d#,##
> and many variations of the supposed formatting that is supposed to work.
> Any suggestions?
> Thanks!|||Try N2
"david boardman" wrote:
> I have a field on my report that is getting a number back from the supporting
> stored proc. This number is sometimes a whole number, sometimes a decimal
> number.
> I want the number output to have 2 decimal places, no matter what the input.
> I have tried the conditional formatting on the textbox, but it doesn't seem
> to work. Is there a bug? Or am I doing something wrong?
> I have input into the "format" section of the properties on the textbox:
> #####.##
> #,##
> d2
> D2
> 2D
> 2d
> d#.##
> d#,##
> and many variations of the supposed formatting that is supposed to work.
> Any suggestions?
> Thanks!

decimal point problem

I want to force two places to the right of the decimal even if its a whole number.

vbScript

calculatedScore = FormatNumber((rsQFinal("sumEarned") / rsQFinal("sumPossible")) * 100, 2)

I'm not savy enought to know the SQL equivalent of the above but I'd like SQL Server to perform the above rather than my vbScript code.

heres what I have so far but I don't know the formatNumber equivalent:

CREATE PROCEDURE quarterFinalGradeSA @.nClass INT, @.nQuarter INT, @.nStudent INT AS

SELECT (SUM(tblScores.score) / SUM(tblAssignments.assignmentTotalPoints)) AS returnValue
...oops I should have given more vbScript

If rsQFinal("sumEarned") = 0 Then
calculatedScore = 0
Else
calculatedScore = FormatNumber((rsQFinal("sumEarned") / rsQFinal("sumPossible")) * 100, 2)
End If

It is possibel for sumEarned to be 0 which won't do well in the division problem, is there a way to do the conditional in the SQL as well.|||I'd use:CREATE PROCEDURE quarterFinalGradeSA @.nClass INT, @.nQuarter INT, @.nStudent INT AS

SELECT (CAST CASE WHEN 0 = SUM(tblAssignments.assignmentTotalPoints) THEN 0.0 ELSE 1e2 * SUM(tblScores.score) / SUM(tblAssignments.assignmentTotalPoints) AS NUMERIC(5, 2)) AS returnValue
...-PatP|||I'd use:CREATE PROCEDURE quarterFinalGradeSA @.nClass INT, @.nQuarter INT, @.nStudent INT AS

SELECT (CAST CASE WHEN 0 = SUM(tblAssignments.assignmentTotalPoints) THEN 0.0 ELSE 1e2 * SUM(tblScores.score) / SUM(tblAssignments.assignmentTotalPoints) AS NUMERIC(5, 2)) AS returnValue
...-PatP

Don't you get bored?

I thougt you had a girlfriend...

:D|||I get to see the girlfriend and kids on weekends (notice you rarely see a post from me on Saturday or Sunday, except for early in the mornings?). During the week I have to "batch" it, either on the road or at the Data Center.

-PatP

Decimal places in Node_Description?

There's been several good posts on using the node description of a model as the end user description for a specific cluster. My model uses a number of continuous input columns defined as currency from a fact table in the source cube. After processing, the node description has elements that look like this:

-0.5799759795 <=Interest Expense <=0.8397462488 ,

Since the source data is currency, this makes the node description look a little strange. The data type in the model is set as double. The precision implied by the description is not what I want the model to consider. In the case above, the difference between the numbers listed is not significant.

It would be great to have a better node desciption that doesn't imply so much precision, but the bigger question is why does the cluster model turn currency types into doubles. Should I set the data type to long in the model so that cents are ignored? I know I should probably use discrete inputs, but I don't want to have to discretize the currency values in the cube since this would require me to set up fact dimensions for each currency column in the fact table.

Sorry, this is a limitation in the data mining engine. Changing the DM type in the mining structure to Long is the right workaround if the fractional values are not significant for the model.

Decimal or Float Type in Percent

Below is a sniplet of a select expression that returns a decimal number like
.6153329998
The result is correct, but when I say FormatPercent(objRS("myPercent"),3) in
ASP, I get a type mismatch error.
Both myField1 and myField2 are integer type. I have to CONVERT myField1 into
decimal in order to get a correct return in QA.
Can someone suggest a better data type to convert myField1 so not only will
myPercent render correct in QA, but also allow me to use FormatPercent in
ASP?
CODE:
SUM(CONVERT(DECIMAL(18, 10), myField1)) / SUM(myField2) AS myPercentHi
You may want to use your convert function after summing
CONVERT(DECIMAL(18, 10), SUM(myField1))/ SUM(myField2)
You may want to stick with DECIMAL but change your scale and precision.
John
"Scott" <sbailey@.mileslumber.com> wrote in message
news:O74yeO1AGHA.4080@.TK2MSFTNGP14.phx.gbl...
> Below is a sniplet of a select expression that returns a decimal number
> like .6153329998
> The result is correct, but when I say FormatPercent(objRS("myPercent"),3)
> in ASP, I get a type mismatch error.
> Both myField1 and myField2 are integer type. I have to CONVERT myField1
> into decimal in order to get a correct return in QA.
> Can someone suggest a better data type to convert myField1 so not only
> will myPercent render correct in QA, but also allow me to use
> FormatPercent in ASP?
>
> CODE:
> SUM(CONVERT(DECIMAL(18, 10), myField1)) / SUM(myField2) AS myPercent
>|||what would the synta be to convert to float?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ebALxS%23AGHA.808@.TK2MSFTNGP11.phx.gbl...
> Hi
> You may want to use your convert function after summing
> CONVERT(DECIMAL(18, 10), SUM(myField1))/ SUM(myField2)
> You may want to stick with DECIMAL but change your scale and precision.
> John
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:O74yeO1AGHA.4080@.TK2MSFTNGP14.phx.gbl...
>|||Hi
The same but use float as the data type, the syntax for convert is described
in Books online as:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
John
"Scott" <sbailey@.mileslumber.com> wrote in message
news:eEj4bU$AGHA.3840@.TK2MSFTNGP15.phx.gbl...
> what would the synta be to convert to float?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:ebALxS%23AGHA.808@.TK2MSFTNGP11.phx.gbl...
>

DECIMAL datatype

Can someone help me better understand the DECIMAL datatype? I've
defined a column as colname DEC(9,2). I know that precision is the
total number of digits, and scale is the number of digits to the right
of the decimal point. Yet 'sp_help tablename' indicates that colname
has a 'length' of 5. What does this 'length' refer to? Thanks.Storage bytes
Precision Storage bytes
1 - 9 ........5
10-19 .......9
20-28....... 13
29-38 .........17
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||i believe it's referred to byte.
"Rick Charnes" wrote:

> Can someone help me better understand the DECIMAL datatype? I've
> defined a column as colname DEC(9,2). I know that precision is the
> total number of digits, and scale is the number of digits to the right
> of the decimal point. Yet 'sp_help tablename' indicates that colname
> has a 'length' of 5. What does this 'length' refer to? Thanks.
>

Decimal and Number Formatting

I have a written a function where I am defining the return value as decimal. Now I need to do the formatting
to make the negative number look like (123.34%) and postive numbers as 123.34%. When I try to do this, I am
getting values like (123.3456788). How do I get rid of these extra decimals?
** Format(Fields!<FieldName>.Value,"#,###,##0.00;(#,###,##0.00)") is not working since I am using Sum for aggregation.
Thanks a lot for your help.Go to the Textbox Properties dialog in designer and specify
"#,##0.00%;(#,##0.00%);Zero" (without the double quotes) as the custom
format string.
Ravi Mumulla
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Taps" <Taps@.discussions.microsoft.com> wrote in message
news:89671ED3-88A9-4FE3-B1FA-109DCDE50065@.microsoft.com...
> I have a written a function where I am defining the return value as
decimal. Now I need to do the formatting
> to make the negative number look like (123.34%) and postive numbers as
123.34%. When I try to do this, I am
> getting values like (123.3456788). How do I get rid of these extra
decimals?
> ** Format(Fields!<FieldName>.Value,"#,###,##0.00;(#,###,##0.00)") is not
working since I am using Sum for aggregation.
> Thanks a lot for your help.
>|||It works! Thanks a lot.
"Ravi Mumulla (Microsoft)" wrote:
> Go to the Textbox Properties dialog in designer and specify
> "#,##0.00%;(#,##0.00%);Zero" (without the double quotes) as the custom
> format string.
> Ravi Mumulla
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Taps" <Taps@.discussions.microsoft.com> wrote in message
> news:89671ED3-88A9-4FE3-B1FA-109DCDE50065@.microsoft.com...
> > I have a written a function where I am defining the return value as
> decimal. Now I need to do the formatting
> > to make the negative number look like (123.34%) and postive numbers as
> 123.34%. When I try to do this, I am
> > getting values like (123.3456788). How do I get rid of these extra
> decimals?
> >
> > ** Format(Fields!<FieldName>.Value,"#,###,##0.00;(#,###,##0.00)") is not
> working since I am using Sum for aggregation.
> >
> > Thanks a lot for your help.
> >
> >
>
>|||Taps wrote:
> I have a written a function where I am defining the return value as
> decimal. Now I need to do the formatting to make the negative number
> look like (123.34%) and postive numbers as 123.34%. When I try to do
> this, I am getting values like (123.3456788). How do I get rid of
> these extra decimals?
> ** Format(Fields!<FieldName>.Value,"#,###,##0.00;(#,###,##0.00)") is
> not working since I am using Sum for aggregation.
> Thanks a lot for your help.
Try setting format property at cell level (right click at cell or cells
then y properties set format)

DEC TO HEX CONVERSION WHILE IMPORTING

I HAVE DECIMAL NUMBER IN MY TEST TABLE COLUMN CARD_NUMBER
AS FOLLOWS
2571549730.0
2571549826.0
2571550034.0
2571550210.0
2571550306.0
2571551378.0
2571551586.0
2571551682.0
2571551762.0
2571551874.0
I WANT TO CREATE A TEMP TABLE AND MOVE THIS COLUMN BUT
BEFORE MOVING IT SHOULD CONVERT INTO HEXADECIMAL NUMBER
WHILE INSERTING INTO TEMP TABLE
THANKS
Mustafa,
If your decimal values fit into a bigint, you could write a
user-defined function (SQL Server 2000 required):
create function dec2hex (
@.decimal bigint
) returns varbinary(20) as begin
declare @.b varbinary(20)
set @.b = 0x
while @.decimal > 0 begin
set @.b = cast(cast(@.decimal%256 as tinyint) as binary(1)) + @.b
set @.decimal = @.decimal/256
end
return @.b
end
go
select dbo.dec2hex(1)
select dbo.dec2hex(2571551874.0)
go
drop function dec2hex
-- Steve Kass
-- Drew University
-- Ref: 77AF63E1-A927-40A1-A7B0-6EA31F1C80F2
MUSTAFA wrote:

>I HAVE DECIMAL NUMBER IN MY TEST TABLE COLUMN CARD_NUMBER
>AS FOLLOWS
>2571549730.0
>2571549826.0
>2571550034.0
>2571550210.0
>2571550306.0
>2571551378.0
>2571551586.0
>2571551682.0
>2571551762.0
>2571551874.0
>I WANT TO CREATE A TEMP TABLE AND MOVE THIS COLUMN BUT
>BEFORE MOVING IT SHOULD CONVERT INTO HEXADECIMAL NUMBER
>WHILE INSERTING INTO TEMP TABLE
>
>THANKS
>
|||YES IT WORKS
THANKS Mr. STEVE

>--Original Message--
>Mustafa,
> If your decimal values fit into a bigint, you could
write a
>user-defined function (SQL Server 2000 required):
>create function dec2hex (
> @.decimal bigint
>) returns varbinary(20) as begin
> declare @.b varbinary(20)
> set @.b = 0x
> while @.decimal > 0 begin
> set @.b = cast(cast(@.decimal%256 as tinyint) as binary
(1)) + @.b[vbcol=seagreen]
> set @.decimal = @.decimal/256
> end
> return @.b
>end
>go
>select dbo.dec2hex(1)
>select dbo.dec2hex(2571551874.0)
>go
>drop function dec2hex
>-- Steve Kass
>-- Drew University
>-- Ref: 77AF63E1-A927-40A1-A7B0-6EA31F1C80F2
>MUSTAFA wrote:
CARD_NUMBER
>.
>
|||Dear Mr. Steve
i have created user define function dec2hex in sql server
2000 and when i access it using sql server query analyser
it give me desired result i.e it convert decimal into
hexadecimal number
The query is as follow
select dbo.dec2hex(card_number) as card_number from
tbltest
But when i used it using VB6 recordset object it show ?
rather then showing hexadecimal number
thanks

>--Original Message--
>Mustafa,
> If your decimal values fit into a bigint, you could
write a
>user-defined function (SQL Server 2000 required):
>create function dec2hex (
> @.decimal bigint
>) returns varbinary(20) as begin
> declare @.b varbinary(20)
> set @.b = 0x
> while @.decimal > 0 begin
> set @.b = cast(cast(@.decimal%256 as tinyint) as binary
(1)) + @.b[vbcol=seagreen]
> set @.decimal = @.decimal/256
> end
> return @.b
>end
>go
>select dbo.dec2hex(1)
>select dbo.dec2hex(2571551874.0)
>go
>drop function dec2hex
>-- Steve Kass
>-- Drew University
>-- Ref: 77AF63E1-A927-40A1-A7B0-6EA31F1C80F2
>MUSTAFA wrote:
CARD_NUMBER
>.
>
|||Mustafa,
This function returns a varbinary(20) value, so if you are seeing ?
somewhere, then something in your application is not displaying
varbinary values correctly. I don't know how VB6 recordsets display
information of varbinary type. Can your VB recordset object display
what is in this recordset?
select 0x123456AB as aBinaryValue
It sounds like a VB issue, not a SQL Server issue.
Steve Kass
Drew University
mustafa wrote:
[vbcol=seagreen]
>Dear Mr. Steve
>i have created user define function dec2hex in sql server
>2000 and when i access it using sql server query analyser
>it give me desired result i.e it convert decimal into
>hexadecimal number
>The query is as follow
>select dbo.dec2hex(card_number) as card_number from
>tbltest
>But when i used it using VB6 recordset object it show ?
>rather then showing hexadecimal number
>thanks
>
>
>write a
>
>(1)) + @.b
>
>CARD_NUMBER
>

Debugging?

Hi,
Is there a way to debug an SQL Server stored procedure? I call a number of sp's from my .net app and need a way to step through them.
Thank you,http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/html/vxtskasqldebuggingexample.asp
|||Thanks. Your link also pointed to the following link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/html/vchowapplicationdebugging.asp
that shows, supposedly, how to debug a stored procedure inline with your app. This is what I'm looking for, because I need to be able to see what the stored procedure is receiving in it's parameters. I followed the steps in the link above, put a breakpoing in the stored procedure and another one in my app just before the stored procedure, and, contrary to the claims of the article, I was not able to step into the procedure. Any help getting this to work would be appreciated!
Thanks.|||Is the SQL Server on your local machine? If not you have toinstall the remote debugging components. This article tells youhow to install and configure remote debugging (as well as various othercool debugging tips):
http://www.dbazine.com/sql/sql-articles/cook1
|||It is on my local machine.