How do I assign nos for column

I have existing data in a table and would like to
assign auto number to a column.
How can I do?
Thank you for your help in advance!
-Kim
To add an identity column to your existing table you can issue the following
command:
alter table yourtable add newcol int identity
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Kim" <anonymous@.discussions.microsoft.com> wrote in message
news:00af01c49045$a6f16e80$a401280a@.phx.gbl...
> I have existing data in a table and would like to
> assign auto number to a column.
> How can I do?
> Thank you for your help in advance!
> -Kim
|||Greg,
I don't want to add a column, I already have a column,
I want to assign sequence numbers to this column.
Sequence numbers will depend on another field, if
the field value = 'A' it will have one sequence,
if the field value = 'B' then it will have another
sequence, so on...
here is the example -
Col1 Col2 Col3 ...
T123 Test rec1 A
F001 Test Rec2 B
S0001 Test Rec3 A
P001 Test Rec4 A
it should have the following values ...
Col1 Col2 Col3 ...
A00001 Test rec1 A
B00001 Test Rec2 B
A00002 Test Rec3 A
A00003 Test Rec4 A
Hope this helps!
Thank you,
-Kim

>--Original Message--
>To add an identity column to your existing table you can
issue the following
>command:
>alter table yourtable add newcol int identity
>--
>----
--
>----
--
>-
>Need SQL Server Examples check out my website
>http://www.geocities.com/sqlserverexamples
>
>"Kim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00af01c49045$a6f16e80$a401280a@.phx.gbl...
>
>.
>
|||Kim wrote:
> Greg,
> I don't want to add a column, I already have a column,
> I want to assign sequence numbers to this column.
> Sequence numbers will depend on another field, if
> the field value = 'A' it will have one sequence,
> if the field value = 'B' then it will have another
> sequence, so on...
> here is the example -
> Col1 Col2 Col3 ...
> T123 Test rec1 A
> F001 Test Rec2 B
> S0001 Test Rec3 A
> P001 Test Rec4 A
> it should have the following values ...
>
> Col1 Col2 Col3 ...
> A00001 Test rec1 A
> B00001 Test Rec2 B
> A00002 Test Rec3 A
> A00003 Test Rec4 A
> Hope this helps!
> Thank you,
> -Kim
If you know the number of possible col3 values beforehand, you can write
a T-SQL script to move through the table, grab each row, one at a time,
check the col3 value, increment the corresponding counter value in the
script, and update the row using the counter value.
Pseudo-Code Here:
Start all counters at 0
Loop through a cursor on the table (do this off-hours)
Get col3 value
If col3 = 'A' then
CounterA = CounterA + 1
NewCol1 = col3 + Right('0000' + CAST(CounterA as varchar(5)), 5)
If col3 = 'B' Then
CounterB = CounterB + 1
etc.
Update Table
Set Col1 = NewCol1
Where PKVal = WhateverThePKValueIs
David G.
|||David,
I will implement your suggestion - thanks.
In the meantime wanted to find out from you how
can I create sequences for each of the series for future
use? Like for 'A' ... 'A000001' onwards,
for 'B' ... 'B000001' onwards.
'cause after I update my database with these numbers
I would like it to autogenerate while creating records
for each of the series.
Thank you for your help!
-Kim

>--Original Message--
>Kim wrote:
>
>If you know the number of possible col3 values
beforehand, you can write
>a T-SQL script to move through the table, grab each row,
one at a time,
>check the col3 value, increment the corresponding
counter value in the
>script, and update the row using the counter value.
>Pseudo-Code Here:
>Start all counters at 0
>Loop through a cursor on the table (do this off-hours)
> Get col3 value
> If col3 = 'A' then
> CounterA = CounterA + 1
> NewCol1 = col3 + Right('0000' + CAST(CounterA as
varchar(5)), 5)
> If col3 = 'B' Then
> CounterB = CounterB + 1
> etc.
> Update Table
> Set Col1 = NewCol1
> Where PKVal = WhateverThePKValueIs
>--
>David G.
>.
>
|||Kim wrote:[vbcol=seagreen]
> David,
> I will implement your suggestion - thanks.
> In the meantime wanted to find out from you how
> can I create sequences for each of the series for future
> use? Like for 'A' ... 'A000001' onwards,
> for 'B' ... 'B000001' onwards.
> 'cause after I update my database with these numbers
> I would like it to autogenerate while creating records
> for each of the series.
> Thank you for your help!
> -Kim
You can create a trigger on the table or a before trigger if the value
is not part of the PK. You'll have to keep track of the underlying key
values using another table.
I'm not a big fan of these types of intelligent keys because maintance
and implementation are much more difficult than using an indentity
columns. Have you considered using an identity column with the col3 as
the compound PK. That way, you have to do nothing to get the values in
there. You can then create a computed column on the table to return
values to your application in the correct format.
David G.

How do I assign nos for column

I have existing data in a table and would like to
assign auto number to a column.
How can I do?
Thank you for your help in advance!
-KimTo add an identity column to your existing table you can issue the following
command:
alter table yourtable add newcol int identity
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Kim" <anonymous@.discussions.microsoft.com> wrote in message
news:00af01c49045$a6f16e80$a401280a@.phx.gbl...
> I have existing data in a table and would like to
> assign auto number to a column.
> How can I do?
> Thank you for your help in advance!
> -Kim|||Greg,
I don't want to add a column, I already have a column,
I want to assign sequence numbers to this column.
Sequence numbers will depend on another field, if
the field value = 'A' it will have one sequence,
if the field value = 'B' then it will have another
sequence, so on...
here is the example -
Col1 Col2 Col3 ...
T123 Test rec1 A
F001 Test Rec2 B
S0001 Test Rec3 A
P001 Test Rec4 A
it should have the following values ...
Col1 Col2 Col3 ...
A00001 Test rec1 A
B00001 Test Rec2 B
A00002 Test Rec3 A
A00003 Test Rec4 A
Hope this helps!
Thank you,
-Kim
>--Original Message--
>To add an identity column to your existing table you can
issue the following
>command:
>alter table yourtable add newcol int identity
>--
>----
--
>----
--
>-
>Need SQL Server Examples check out my website
>http://www.geocities.com/sqlserverexamples
>
>"Kim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00af01c49045$a6f16e80$a401280a@.phx.gbl...
>> I have existing data in a table and would like to
>> assign auto number to a column.
>> How can I do?
>> Thank you for your help in advance!
>> -Kim
>
>.
>|||Kim wrote:
> Greg,
> I don't want to add a column, I already have a column,
> I want to assign sequence numbers to this column.
> Sequence numbers will depend on another field, if
> the field value = 'A' it will have one sequence,
> if the field value = 'B' then it will have another
> sequence, so on...
> here is the example -
> Col1 Col2 Col3 ...
> T123 Test rec1 A
> F001 Test Rec2 B
> S0001 Test Rec3 A
> P001 Test Rec4 A
> it should have the following values ...
>
> Col1 Col2 Col3 ...
> A00001 Test rec1 A
> B00001 Test Rec2 B
> A00002 Test Rec3 A
> A00003 Test Rec4 A
> Hope this helps!
> Thank you,
> -Kim
If you know the number of possible col3 values beforehand, you can write
a T-SQL script to move through the table, grab each row, one at a time,
check the col3 value, increment the corresponding counter value in the
script, and update the row using the counter value.
Pseudo-Code Here:
Start all counters at 0
Loop through a cursor on the table (do this off-hours)
Get col3 value
If col3 = 'A' then
CounterA = CounterA + 1
NewCol1 = col3 + Right('0000' + CAST(CounterA as varchar(5)), 5)
If col3 = 'B' Then
CounterB = CounterB + 1
etc.
Update Table
Set Col1 = NewCol1
Where PKVal = WhateverThePKValueIs
--
David G.|||David,
I will implement your suggestion - thanks.
In the meantime wanted to find out from you how
can I create sequences for each of the series for future
use? Like for 'A' ... 'A000001' onwards,
for 'B' ... 'B000001' onwards.
'cause after I update my database with these numbers
I would like it to autogenerate while creating records
for each of the series.
Thank you for your help!
-Kim
>--Original Message--
>Kim wrote:
>> Greg,
>> I don't want to add a column, I already have a column,
>> I want to assign sequence numbers to this column.
>> Sequence numbers will depend on another field, if
>> the field value = 'A' it will have one sequence,
>> if the field value = 'B' then it will have another
>> sequence, so on...
>> here is the example -
>> Col1 Col2 Col3 ...
>> T123 Test rec1 A
>> F001 Test Rec2 B
>> S0001 Test Rec3 A
>> P001 Test Rec4 A
>> it should have the following values ...
>>
>> Col1 Col2 Col3 ...
>> A00001 Test rec1 A
>> B00001 Test Rec2 B
>> A00002 Test Rec3 A
>> A00003 Test Rec4 A
>> Hope this helps!
>> Thank you,
>> -Kim
>
>If you know the number of possible col3 values
beforehand, you can write
>a T-SQL script to move through the table, grab each row,
one at a time,
>check the col3 value, increment the corresponding
counter value in the
>script, and update the row using the counter value.
>Pseudo-Code Here:
>Start all counters at 0
>Loop through a cursor on the table (do this off-hours)
> Get col3 value
> If col3 = 'A' then
> CounterA = CounterA + 1
> NewCol1 = col3 + Right('0000' + CAST(CounterA as
varchar(5)), 5)
> If col3 = 'B' Then
> CounterB = CounterB + 1
> etc.
> Update Table
> Set Col1 = NewCol1
> Where PKVal = WhateverThePKValueIs
>--
>David G.
>.
>|||Kim wrote:
> David,
> I will implement your suggestion - thanks.
> In the meantime wanted to find out from you how
> can I create sequences for each of the series for future
> use? Like for 'A' ... 'A000001' onwards,
> for 'B' ... 'B000001' onwards.
> 'cause after I update my database with these numbers
> I would like it to autogenerate while creating records
> for each of the series.
> Thank you for your help!
> -Kim
>> --Original Message--
>> Kim wrote:
>> Greg,
>> I don't want to add a column, I already have a column,
>> I want to assign sequence numbers to this column.
>> Sequence numbers will depend on another field, if
>> the field value = 'A' it will have one sequence,
>> if the field value = 'B' then it will have another
>> sequence, so on...
>> here is the example -
>> Col1 Col2 Col3 ...
>> T123 Test rec1 A
>> F001 Test Rec2 B
>> S0001 Test Rec3 A
>> P001 Test Rec4 A
>> it should have the following values ...
>>
>> Col1 Col2 Col3 ...
>> A00001 Test rec1 A
>> B00001 Test Rec2 B
>> A00002 Test Rec3 A
>> A00003 Test Rec4 A
>> Hope this helps!
>> Thank you,
>> -Kim
>>
>> If you know the number of possible col3 values beforehand, you can
>> write a T-SQL script to move through the table, grab each row, one
>> at a time, check the col3 value, increment the corresponding counter
>> value in the script, and update the row using the counter value.
>> Pseudo-Code Here:
>> Start all counters at 0
>> Loop through a cursor on the table (do this off-hours)
>> Get col3 value
>> If col3 = 'A' then
>> CounterA = CounterA + 1
>> NewCol1 = col3 + Right('0000' + CAST(CounterA as varchar(5)), 5)
>> If col3 = 'B' Then
>> CounterB = CounterB + 1
>> etc.
>> Update Table
>> Set Col1 = NewCol1
>> Where PKVal = WhateverThePKValueIs
>> --
>> David G.
>> .
You can create a trigger on the table or a before trigger if the value
is not part of the PK. You'll have to keep track of the underlying key
values using another table.
I'm not a big fan of these types of intelligent keys because maintance
and implementation are much more difficult than using an indentity
columns. Have you considered using an identity column with the col3 as
the compound PK. That way, you have to do nothing to get the values in
there. You can then create a computed column on the table to return
values to your application in the correct format.
David G.

How do I assign nos for column

I have existing data in a table and would like to
assign auto number to a column.
How can I do?
Thank you for your help in advance!
-KimTo add an identity column to your existing table you can issue the following
command:
alter table yourtable add newcol int identity
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Kim" <anonymous@.discussions.microsoft.com> wrote in message
news:00af01c49045$a6f16e80$a401280a@.phx.gbl...
> I have existing data in a table and would like to
> assign auto number to a column.
> How can I do?
> Thank you for your help in advance!
> -Kim|||Greg,
I don't want to add a column, I already have a column,
I want to assign sequence numbers to this column.
Sequence numbers will depend on another field, if
the field value = 'A' it will have one sequence,
if the field value = 'B' then it will have another
sequence, so on...
here is the example -
Col1 Col2 Col3 ...
T123 Test rec1 A
F001 Test Rec2 B
S0001 Test Rec3 A
P001 Test Rec4 A
it should have the following values ...
Col1 Col2 Col3 ...
A00001 Test rec1 A
B00001 Test Rec2 B
A00002 Test Rec3 A
A00003 Test Rec4 A
Hope this helps!
Thank you,
-Kim

>--Original Message--
>To add an identity column to your existing table you can
issue the following
>command:
>alter table yourtable add newcol int identity
>--
>----
--
>----
--
>-
>Need SQL Server Examples check out my website
>http://www.geocities.com/sqlserverexamples
>
>"Kim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00af01c49045$a6f16e80$a401280a@.phx.gbl...
>
>.
>|||Kim wrote:
> Greg,
> I don't want to add a column, I already have a column,
> I want to assign sequence numbers to this column.
> Sequence numbers will depend on another field, if
> the field value = 'A' it will have one sequence,
> if the field value = 'B' then it will have another
> sequence, so on...
> here is the example -
> Col1 Col2 Col3 ...
> T123 Test rec1 A
> F001 Test Rec2 B
> S0001 Test Rec3 A
> P001 Test Rec4 A
> it should have the following values ...
>
> Col1 Col2 Col3 ...
> A00001 Test rec1 A
> B00001 Test Rec2 B
> A00002 Test Rec3 A
> A00003 Test Rec4 A
> Hope this helps!
> Thank you,
> -Kim
If you know the number of possible col3 values beforehand, you can write
a T-SQL script to move through the table, grab each row, one at a time,
check the col3 value, increment the corresponding counter value in the
script, and update the row using the counter value.
Pseudo-Code Here:
Start all counters at 0
Loop through a cursor on the table (do this off-hours)
Get col3 value
If col3 = 'A' then
CounterA = CounterA + 1
NewCol1 = col3 + Right('0000' + CAST(CounterA as varchar(5)), 5)
If col3 = 'B' Then
CounterB = CounterB + 1
etc.
Update Table
Set Col1 = NewCol1
Where PKVal = WhateverThePKValueIs
David G.|||David,
I will implement your suggestion - thanks.
In the meantime wanted to find out from you how
can I create sequences for each of the series for future
use? Like for 'A' ... 'A000001' onwards,
for 'B' ... 'B000001' onwards.
'cause after I update my database with these numbers
I would like it to autogenerate while creating records
for each of the series.
Thank you for your help!
-Kim

>--Original Message--
>Kim wrote:
>
>If you know the number of possible col3 values
beforehand, you can write
>a T-SQL script to move through the table, grab each row,
one at a time,
>check the col3 value, increment the corresponding
counter value in the
>script, and update the row using the counter value.
>Pseudo-Code Here:
>Start all counters at 0
>Loop through a cursor on the table (do this off-hours)
> Get col3 value
> If col3 = 'A' then
> CounterA = CounterA + 1
> NewCol1 = col3 + Right('0000' + CAST(CounterA as
varchar(5)), 5)
> If col3 = 'B' Then
> CounterB = CounterB + 1
> etc.
> Update Table
> Set Col1 = NewCol1
> Where PKVal = WhateverThePKValueIs
>--
>David G.
>.
>|||Kim wrote:[vbcol=seagreen]
> David,
> I will implement your suggestion - thanks.
> In the meantime wanted to find out from you how
> can I create sequences for each of the series for future
> use? Like for 'A' ... 'A000001' onwards,
> for 'B' ... 'B000001' onwards.
> 'cause after I update my database with these numbers
> I would like it to autogenerate while creating records
> for each of the series.
> Thank you for your help!
> -Kim
>
You can create a trigger on the table or a before trigger if the value
is not part of the PK. You'll have to keep track of the underlying key
values using another table.
I'm not a big fan of these types of intelligent keys because maintance
and implementation are much more difficult than using an indentity
columns. Have you considered using an identity column with the col3 as
the compound PK. That way, you have to do nothing to get the values in
there. You can then create a computed column on the table to return
values to your application in the correct format.
David G.

how do I assign a string to a parameter Im passing to a select statement?

Hello,

I'm needing to pass a variable length number of values to a select statement so I can populate a result list with items related to all the checkboxlist items that were selected by the user. for example, the user checks products x, y and z, then hits submit, and then they see a list of all the tests they need to run for each product.

I found a UDF that parses a comma delimited string and puts the values into a table. I learned how to do this here:

http://codebetter.com/blogs/darrell.norton/archive/2003/07/01/361.aspx

I have a checkboxlist that I'm generating the string from, so the string could look like this: "1,3,4,5,7" etc.

I added the function mentioned in the URL above to my database, and if I understand right, I should be able to pass the table it creates into the select statement like so:

WHERE (OrderStatus IN ((select value from dbo.fn_Split(@.StatusList,','))) OR @.StatusList IS NULL)

but now I don't know how to assign the string value to the parameter, say to '@.solution_id'.

my current select statement which was generated by Visual Studio 2005 looks like this:

SELECT [test], [owner], [date] FROM [test_table] WHERE ([solution_ID] = @.solution_ID)

...but this only pulls results for the first item checked in the checkboxlist.

Does anyone know how this is done? I'm sure it's simple, but I'm new to ASP .NET so any help would be greatly appreciated.

hi

First make sure you have createddbo.fn_Split .

SELECT [test], [owner], [date]FROM [test_table]WHERE ([solution_ID]IN ((select valuefrom dbo.fn_Split(@.solution_ID,',')))OR @.solution_IDISNULL)

I am not sure "OR @.solution_IDISNULL" should be added,you have to decide it according to your logic.

You are required to pass @.solution_ID to the statement(1,3,4,6 etc) then you can get corresponding test.

Hope this helps.

|||

Thanks for your response. If I'm following you, I do understand that I need to pass @.solution_ID to the select statement like you showed. I have a string of values that I created from iterating through CheckBoxList to find selected boxes. My question is, how do I assign the value of this string to @.solution_ID?

Regards,

Daniel

|||

Assume you have checkboxlist Check1, using following code to get @.solution_ID :

for (int i = 0; i < Check1.Items.Count; i++)
{
if (Check1.Items[i].Selected)
{
// List the selected items
solution_ID = solution_ID + Check1.Items[i].Text;
solution_ID = solution_ID +",";
}
}

Then connect with DB:

SqlCommand sqlcmd = new SqlCommand("SELECT [test], [owner], [date]FROM [test_table]WHERE
([solution_ID]IN ((select valuefrom dbo.fn_Split(@.solution_ID,',')))OR @.solution_IDISNULL)", sqlconn);
sqlcmd.Parameters.AddWithValue("@.solution_ID",solution_ID);
sqlconn.Open();
SqlDataReader sdr = sqlcmd.ExecuteReader();

.............

You 'd bette put bold sql script into a stored procedure.

hope this helps.

How do I add a Percentage Column after the Total on a Matrix?

Hi. I am looking to create a Matrix-based Report in SQL Server 2005 Reporting Services. I have my query, and a dynamic number of both rows and columns. I have Totals on the "bottom" and "right".

What I'd like to do is add a Percentage column to the right of the right-based Totals column. So the columns would read Col1, Col2, .., ColN, Total, Percentage.

Is this possible?

Thanks.

You could do something like - matrixcoltotal / matrixtotal and set your Format Code to P0 on the properties window.

Your expression will look something like this. =Sum(Fields!ReportField.Value,"MatrixGroup")/Sum(Fields!ReportField.Value,"dataset"). Dont forget to change the FormatCode.

|||How would I place this column to the RIGHT of the Subtotal column?

|||

Add an invisible column group which will be grouped by the percentage expression and add the subtotal to this colum group have the which will give you the sum of all individual percentages.

Shyam

|||Here is the error message I am receiving:

"A group expression for the matrix 'matrix1' includes an aggregate function. Aggregate functions cannot be used in group expressions."

I created a new Column group, outside of my month-by-month column group, and made it invisible. I do see a new "Total" to the right....however when I put in my sum(value, "InnerRowGroup")/sum(value, "OuterRowGroup") as the group expression, I get the above error.

Thank you both greatly for your help. I feel about 75% towards getting this to work.

Jason

|||

No, you dont have to group by the aggregate which is an obvious logical error. Just group by the expression without the sum function which would be:

Fields!InnerGroupField/Fields!OuterGroupField

Then using the subtoal would automatically mean a sum which is what you want I guess.

Shyam

|||What if the percentage I want to calculate is the ratio between two columns? Is this possible, or will I have to pivot the data in the database before I send it to SSRS? This would suck since I have to completely revamp the stored proc.

How do I add a Percentage Column after the Total on a Matrix?

Hi. I am looking to create a Matrix-based Report in SQL Server 2005 Reporting Services. I have my query, and a dynamic number of both rows and columns. I have Totals on the "bottom" and "right".

What I'd like to do is add a Percentage column to the right of the right-based Totals column. So the columns would read Col1, Col2, .., ColN, Total, Percentage.

Is this possible?

Thanks.

You could do something like - matrixcoltotal / matrixtotal and set your Format Code to P0 on the properties window.

Your expression will look something like this. =Sum(Fields!ReportField.Value,"MatrixGroup")/Sum(Fields!ReportField.Value,"dataset"). Dont forget to change the FormatCode.

|||How would I place this column to the RIGHT of the Subtotal column?

|||

Add an invisible column group which will be grouped by the percentage expression and add the subtotal to this colum group have the which will give you the sum of all individual percentages.

Shyam

|||Here is the error message I am receiving:

"A group expression for the matrix 'matrix1' includes an aggregate function. Aggregate functions cannot be used in group expressions."

I created a new Column group, outside of my month-by-month column group, and made it invisible. I do see a new "Total" to the right....however when I put in my sum(value, "InnerRowGroup")/sum(value, "OuterRowGroup") as the group expression, I get the above error.

Thank you both greatly for your help. I feel about 75% towards getting this to work.

Jason

|||

No, you dont have to group by the aggregate which is an obvious logical error. Just group by the expression without the sum function which would be:

Fields!InnerGroupField/Fields!OuterGroupField

Then using the subtoal would automatically mean a sum which is what you want I guess.

Shyam

|||What if the percentage I want to calculate is the ratio between two columns? Is this possible, or will I have to pivot the data in the database before I send it to SSRS? This would suck since I have to completely revamp the stored proc.

How do I add a Percentage Column after the Total on a Matrix?

Hi. I am looking to create a Matrix-based Report in SQL Server 2005 Reporting Services. I have my query, and a dynamic number of both rows and columns. I have Totals on the "bottom" and "right".

What I'd like to do is add a Percentage column to the right of the right-based Totals column. So the columns would read Col1, Col2, .., ColN, Total, Percentage.

Is this possible?

Thanks.

You could do something like - matrixcoltotal / matrixtotal and set your Format Code to P0 on the properties window.

Your expression will look something like this. =Sum(Fields!ReportField.Value,"MatrixGroup")/Sum(Fields!ReportField.Value,"dataset"). Dont forget to change the FormatCode.

|||How would I place this column to the RIGHT of the Subtotal column?|||

Add an invisible column group which will be grouped by the percentage expression and add the subtotal to this colum group have the which will give you the sum of all individual percentages.

Shyam

|||Here is the error message I am receiving:

"A group expression for the matrix 'matrix1' includes an aggregate function. Aggregate functions cannot be used in group expressions."

I created a new Column group, outside of my month-by-month column group, and made it invisible. I do see a new "Total" to the right....however when I put in my sum(value, "InnerRowGroup")/sum(value, "OuterRowGroup") as the group expression, I get the above error.

Thank you both greatly for your help. I feel about 75% towards getting this to work.

Jason|||

No, you dont have to group by the aggregate which is an obvious logical error. Just group by the expression without the sum function which would be:

Fields!InnerGroupField/Fields!OuterGroupField

Then using the subtoal would automatically mean a sum which is what you want I guess.

Shyam

|||What if the percentage I want to calculate is the ratio between two columns? Is this possible, or will I have to pivot the data in the database before I send it to SSRS? This would suck since I have to completely revamp the stored proc.

How determine the number of CALs installed

How can I determine the number of CALs installed on SQL Server 7? I
need to determine the number of CALs that must be purchased to upgrade.
..Bill.
"Bill" <no@.no.com> wrote in message
news:ui6MnwoOGHA.1532@.TK2MSFTNGP12.phx.gbl...
> How can I determine the number of CALs installed on SQL Server 7? I
> need to determine the number of CALs that must be purchased to upgrade.
> --
> .Bill.
CALs are licences only. They are not installed anywhere so your question
doesn't really make sense.
The proper way to determine your licence requirement is to count your users
or installed devices. SQL Server's CAL requirement is measured by those
metrics, not by concurrent usage. There is a licence figure that you can set
using the SQL Server Licensing Setup utility in Control Panel so I expect
you'll want to check that as well.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

how create mining model

i have a problem, because i have 3 tables.
One name "CallPair" contain: id- identity number, from - phone number, to - phone number.
Second name "Talk" contain: id - identity number, callPairId - id from "CallPair" table, date
Third name "Transaction" contain: id - identity number is a number of transaction, idTalk - id from Talk table, callNr - number of talk in transaction

I want mining model to sequence clustering, but i don't know how build case and nested table

If you are trying to build a model across all phone numbers to see what number a customer is likely to call next, or use perform any anaysis across all numbers, sequence clustering is not likely to work very well.

Sequence Clustering works well when you have less than 100 possible sequence states. You could categorize calls potentially and sequence those - e.g. user recieves call, doesn't answer, sends text message, makes free call, etc. and then use Sequence Clustering.

In any case, I would need to know more about the problem you are trying to solve to advise how to build a model.

How can you use a variable tablename and retrieve the output from the Insert?

We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing the table as a parameter. In this example we are setting the table.

When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @.tTemp.

How can you use a variable table name and retrieve the output from the Insert?

declare @.tTestTable varchar (20)

set @.tTestTable = 'mis.test_sequence'

--DECLARE @.tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime)

declare @.testsql varchar (4000), @.testseq int

DECLARE @.tTemp table (mainpk int)

set @.testsql = 'DECLARE @.tTemp table (mainpk int) INSERT ' + @.tTestTable + ' OUTPUT INSERTED.sqVal into @.tTemp VALUES (getdate() ) SELECT @.testseq=mainpk FROM @.tTemp'

select @.testsql

EXECUTE sp_executesql @.testsql, N'@.testseq int output,@.tTemp table (mainpk int),@.tTemp table (mainpk int) ',@.tTemp,@.tTemp,@.testseq output,@.tTemp

SELECT * FROM @.tTemp

Please help

Thanks Tim.

Why not to create an sp per each table, instead trying to come with a general one?

Code Snippet

use tempdb

go

create table #t (c1 int not null identity, c2 datetime)

declare @.tTestTable varchar (20)

set @.tTestTable = '#t'

declare @.testsql nvarchar (4000), @.testseq int

set @.testsql = 'INSERT into' + quotename(@.tTestTable) + '(c2) values(getdate()); set @.testseq = scope_identity()'

select @.testsql

EXECUTE sp_executesql @.testsql, N'@.testseq int output',@.testseq output

SELECT @.testseq

drop table #t

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB|||

Thankyou hunchback,

Your Code Snippet helped me solve my problem.

Tim.

Here's my final code.

USE [TestDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create proc GetNext (@.sequenceName varchar(40) , @.nextVal int output)

as

begin

declare @.sqlStmt nvarchar (4000)

declare @.tTemp table (mainpk int)

set nocount on

-- This option works using the OUTPUT clause.

set @.sqlStmt = 'DECLARE @.tTemp table (mainpk int) Insert ' + @.sequenceName + ' OUTPUT INSERTED.sqVal into @.tTemp DEFAULT VALUES SELECT @.nextVal=mainpk FROM @.tTemp'

execute sp_executesql @.sqlStmt, N'@.nextVal int output',@.nextVal output

set nocount off

end

How can we get the round value of a number by MDX?

Hi, all,

How can I get the round value of a number (e.g 4.95, I want to get its closest value of 5), how can I achieve this by MDX?

Thanks a lot and I am looking forward to hearing from you.

With best regards,

Yours sincerely,

go to menu report -> report properties and select code. then paste following code there.

Public Function Round(d as double) AS Double
Dim roundValue AS Double
roundValue =System.Math.Round(d)

If (System.Math.Abs(d - roundValue) - 0.5>0) Then
If (roundValue Mod 2) = 0 Then
roundValue = roundValue + 1
End If
End If

RETURN roundValue
End Function

then in ur designer

Code.Round(Field!Amount.value)

think this wil b ok
|||

Hi, thanks for your help. But i dont understand as what i am trying to do is for the KPI in cube designer, i want to get a round value for the KPI.

Thanks.

With best regards,

Yours sincerely,

|||

Helen,

In MDX you can achieve Rounding by using VBA!Round Function as following

WITH MEMBER [Measures].[My Rounded Measure]

AS VBA!ROUND([Measures].[My Measure],0)

SELECT

{

[Measures].[My Rounded Measure]

} ON 0,

NON EMPTY{

[Customer].[Customer].[Customer]

} ON 1

FROM [My Cube]

Bhudev

how can it automatically generating a ordered number

hi,

i am a newcomer and a freshman in asp.net. i am now writing a web-based system for SME as my final year project. i am going to use sql server and asp.net in C# to perform my final year project.

as asp.net is new for me, i would have some simple problems to ask.

1. in the project, i would like the system can automatically generate the enquiry number for each new order input to the system. for example today is 05 July 2006, the enquiry number would like 2006211xxxx, where 2006 is year, 211 is the day count start from 1 Jan and xxxx is the random number/ ordered number. how can i implement this? i even don't know how to generate the ordered number. could anyone help me

2. if there is an unknown test sample in each order input. as the sample number for each order is different, how can i set a flexible table that can have different number of rows for user to input the test result.

thanks

Rgds, universe

1. I suggest you can have two columns: one used to record the inserted data for the row, another for row ID. So you can create a table as following:

create table tbl_test(id int identity(1,1),RecDate smalldatetime default getdate(), Description varchar(200))
create table tbl_test1(id uniqueidentifier default newid(),RecDate smalldatetime default getdate(), Description varchar(200))

insert into tbl_test(description) select 'This is a test row'

insert into tbl_test1(description) select 'This is a test row'

2. Sorry I'm not clear about this issue

How can I verify a GUID number ?

Dear SQL,

I need to SELECT something from a database that has a UNIQUEIDENTIFIER (GUID) field,

If the number is wrong (has some other than A-Z 0-9) than theASP page just freaks out and gets "error converting from a character string to uniqueidentifier"

How can I check that the GUID is OK before I SELECT ?
this is the number format:
{7A9B5F81-4936-4A31-B4E2-9168AAB75A0}

I tried to cast this "error" number with no successs:
"WHERE Deceased_ID = cast('"& "---4936-4A31-B4E2-9168AAB75A0" &"' as uniqueidentifier)"

Thanks in advance, Yovav.?? The string you're trying to cast is not a valid GUID, whats with the concats and the "--"? If a GUID is in the database then it will always be valid.|||I know it's not valid - that's the idea...

I'm getting a GUID as a parameter (using GET) and I want to avoid getting an error
if the user will change the GUID I'm sending to something illegal like "--+..."

The problem is that if I'm trying to SELECT some stuff WHERE someGUID = '" & givenGUID & "'
it will get a nasty error on the ASP page (error converting from a character string to uniqueidentifier)

I also tried to cast it into varchar(38) - which is working - BUT not if the GUID contains some illegal characters...

I guess I will have to write a function to validate GUID number - I could not find a function like that anywhere :-(|||Solved|||but GUID is a datatype so it will error if you try to put an invalid item into it, can't you rely on that?|||Solved

How can I use SMO within a CLR stored procedure

Hi,

I have a CLR stored procedure that I wish to use SMO.

However this caused a number of problems :

1. Add reference didnt show SMO. So I tracked down a redistributable i.e. SQL Server 2005 Feature pack to install - still didnt show up. However, in a normal .net project they do show up so I erhm hacked the SQL server database project file to manually add the references.

Great have the reference, added the include statements - intellisense working code away - even builds :)

using Microsoft.SqlServer.Management;
using Microsoft.SqlServer.Management.Smo;

2. Tried to deploy and came up with the error :

Error 1 Assembly 'microsoft.sqlserver.connectioninfo, version=9.0.242.0, culture=neutral, publickeytoken=89845dcd8080cc91.' was not found in the SQL catalog.

:(

Shot down in flames..

Would very much appreciate if someone could point me in the right direction to utilising SMO or point out where I am going wrong - other than dont hack the project file :)

Note the reason why I wish to use SMO is so I can read sql files from disk and execute them in the database i.e. create stored procedures etc.

The standard mechanism I have found i.e. a server connection and using ExectueNonQuery fails as the script files use batch seperators in the text i.e. 'GO'.

I have also tried using xp_cmdshell but any error reporting is virtually non existant other than parsing the output which will be truly horrific.

Open to suggestions :)SMO is not supported inside SQL Server. Normally what you can do in a scenario when a system dll (like SMO) isn't supported, is to manually catalog the dll in the database (that way it would show up in th VS SQL Server project when you try to reference it) and be able to use it.

In the case of SMO however it won't work period.

Niels|||Ah, thanks Niels.|||

Hi,

(warning: CLR newbie)

I am trying to use Microsoft.Office.Interop.Excel within my CLR stored procedure. I added <Reference Include="Microsoft.Office.Interop.Excel" /> as an <ItemGroup> in the project file. That allowed me to have access to the namespace and build the stored procedure successfully. However, when I try to deploy the stored procedure, I get the same error as the initial post "Assembly 'microsoft.office.interop.excel, version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.' was not found in the SQL catalog."

So how do you "manually catalog the dll in the database" so that you can successfully deploy? Are you refering to the "CREATE ASSESSMBLY" command?

Any help is greatly appreciated.

Rocco

|||

Rocco Mastrangelo wrote:

Hi,

(warning: CLR newbie)

I am trying to use Microsoft.Office.Interop.Excel within my CLR stored procedure. I added <Reference Include="Microsoft.Office.Interop.Excel" /> as an <ItemGroup> in the project file. That allowed me to have access to the namespace and build the stored procedure successfully. However, when I try to deploy the stored procedure, I get the same error as the initial post "Assembly 'microsoft.office.interop.excel, version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.' was not found in the SQL catalog."

So how do you "manually catalog the dll in the database" so that you can successfully deploy? Are you refering to the "CREATE ASSESSMBLY" command?

Yes, you have to run CREATE ASSEMBLY. Be aware that you probably have to create the assembly with a permission set of UNSAFE. This in turn requires certain permissions/settings in the database. See this thread for more info.

Niels

How can I use SMO within a CLR stored procedure

Hi,

I have a CLR stored procedure that I wish to use SMO.

However this caused a number of problems :

1. Add reference didnt show SMO. So I tracked down a redistributable i.e. SQL Server 2005 Feature pack to install - still didnt show up. However, in a normal .net project they do show up so I erhm hacked the SQL server database project file to manually add the references.

Great have the reference, added the include statements - intellisense working code away - even builds :)

using Microsoft.SqlServer.Management;
using Microsoft.SqlServer.Management.Smo;

2. Tried to deploy and came up with the error :

Error 1 Assembly 'microsoft.sqlserver.connectioninfo, version=9.0.242.0, culture=neutral, publickeytoken=89845dcd8080cc91.' was not found in the SQL catalog.

:(

Shot down in flames..

Would very much appreciate if someone could point me in the right direction to utilising SMO or point out where I am going wrong - other than dont hack the project file :)

Note the reason why I wish to use SMO is so I can read sql files from disk and execute them in the database i.e. create stored procedures etc.

The standard mechanism I have found i.e. a server connection and using ExectueNonQuery fails as the script files use batch seperators in the text i.e. 'GO'.

I have also tried using xp_cmdshell but any error reporting is virtually non existant other than parsing the output which will be truly horrific.

Open to suggestions :)SMO is not supported inside SQL Server. Normally what you can do in a scenario when a system dll (like SMO) isn't supported, is to manually catalog the dll in the database (that way it would show up in th VS SQL Server project when you try to reference it) and be able to use it.

In the case of SMO however it won't work period.

Niels|||Ah, thanks Niels.|||

Hi,

(warning: CLR newbie)

I am trying to use Microsoft.Office.Interop.Excel within my CLR stored procedure. I added <Reference Include="Microsoft.Office.Interop.Excel" /> as an <ItemGroup> in the project file. That allowed me to have access to the namespace and build the stored procedure successfully. However, when I try to deploy the stored procedure, I get the same error as the initial post "Assembly 'microsoft.office.interop.excel, version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.' was not found in the SQL catalog."

So how do you "manually catalog the dll in the database" so that you can successfully deploy? Are you refering to the "CREATE ASSESSMBLY" command?

Any help is greatly appreciated.

Rocco

|||

Rocco Mastrangelo wrote:

Hi,

(warning: CLR newbie)

I am trying to use Microsoft.Office.Interop.Excel within my CLR stored procedure. I added <Reference Include="Microsoft.Office.Interop.Excel" /> as an <ItemGroup> in the project file. That allowed me to have access to the namespace and build the stored procedure successfully. However, when I try to deploy the stored procedure, I get the same error as the initial post "Assembly 'microsoft.office.interop.excel, version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.' was not found in the SQL catalog."

So how do you "manually catalog the dll in the database" so that you can successfully deploy? Are you refering to the "CREATE ASSESSMBLY" command?

Yes, you have to run CREATE ASSEMBLY. Be aware that you probably have to create the assembly with a permission set of UNSAFE. This in turn requires certain permissions/settings in the database. See this thread for more info.

Niels

How can I 'unfold' a count to 1, 2, 3,..., n records?

Hi all,
I have a simple query that retrieves the number of articles that an order
has:
SELECT ArticleId, ArticleCount FROM TOrderDetails WHERE OrderId = @.MyOrder
Both ArticleId and ArticleCount are integers. A sample set result woud be
like this one:
ArticleId ArticleCount
1 3
2 2
3 5
4 1
I need to print the labels for each single article that is sold. I need to
expand/unfold that result set into something like:
ArticleId ArticleCountId
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
3 4
3 5
4 1
So, there will be 3 records for ArticleId = 1: Since 3 units are sold, I
will need 3 labels (named 1, 2 and 3), and so on.
I've been thinking about how to acomplish this but the only thing that goes
to my mind is passing a table to a sp to iterate for i=1 to n for each Id.
However, since passing a table to a SP is not possible I'm a little
stuck-in-the-mud :(
PD: I'm using Reporting Services so it is a little hard to do job with a
simple FOR statement since RS is not very good for being programmed the way
we were used with VB. Of course I could do an assembly and do the
programming there but I would rather do the job in the query/sql server and
let RS as neat/simple as possible.
Any ideas? Regards and thanks in advance.Hi
If you have a number table or derived table of numbers, then something like
the following would work:
SELECT A.ArticleId, C.Num
FROM ( SELECT 1 As Num
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5 ) C
JOIN (SELECT 1 AS ArticleId, 3 AS ArticleCount
UNION ALL SELECT 2, 2
UNION ALL SELECT 3, 5
UNION ALL SELECT 4, 1 ) A ON C.Num <= A.ArticleCount
ORDER BY A.ArticleId, C.Num
John
"David Lightman Robles" wrote:

> Hi all,
> I have a simple query that retrieves the number of articles that an order
> has:
> SELECT ArticleId, ArticleCount FROM TOrderDetails WHERE OrderId = @.MyOrd
er
> Both ArticleId and ArticleCount are integers. A sample set result woud be
> like this one:
> ArticleId ArticleCount
> 1 3
> 2 2
> 3 5
> 4 1
> I need to print the labels for each single article that is sold. I need to
> expand/unfold that result set into something like:
> ArticleId ArticleCountId
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 1
> 3 2
> 3 3
> 3 4
> 3 5
> 4 1
> So, there will be 3 records for ArticleId = 1: Since 3 units are sold, I
> will need 3 labels (named 1, 2 and 3), and so on.
> I've been thinking about how to acomplish this but the only thing that goe
s
> to my mind is passing a table to a sp to iterate for i=1 to n for each Id.
> However, since passing a table to a SP is not possible I'm a little
> stuck-in-the-mud :(
>
> PD: I'm using Reporting Services so it is a little hard to do job with a
> simple FOR statement since RS is not very good for being programmed the wa
y
> we were used with VB. Of course I could do an assembly and do the
> programming there but I would rather do the job in the query/sql server an
d
> let RS as neat/simple as possible.
> Any ideas? Regards and thanks in advance.
>
>|||David
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
CREATE TABLE #Test
(
col1 INT,
col2 INT
)
INSERT INTO #Test VALUES (1,3)
INSERT INTO #Test VALUES (2,2)
SELECT col1,n FROM #Test LEFT JOIN Numbers
ON #Test.col2 >= Numbers.n
"David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
news:%23vwfl43WFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have a simple query that retrieves the number of articles that an order
> has:
> SELECT ArticleId, ArticleCount FROM TOrderDetails WHERE OrderId =
@.MyOrder
> Both ArticleId and ArticleCount are integers. A sample set result woud be
> like this one:
> ArticleId ArticleCount
> 1 3
> 2 2
> 3 5
> 4 1
> I need to print the labels for each single article that is sold. I need to
> expand/unfold that result set into something like:
> ArticleId ArticleCountId
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 1
> 3 2
> 3 3
> 3 4
> 3 5
> 4 1
> So, there will be 3 records for ArticleId = 1: Since 3 units are sold, I
> will need 3 labels (named 1, 2 and 3), and so on.
> I've been thinking about how to acomplish this but the only thing that
goes
> to my mind is passing a table to a sp to iterate for i=1 to n for each Id.
> However, since passing a table to a SP is not possible I'm a little
> stuck-in-the-mud :(
>
> PD: I'm using Reporting Services so it is a little hard to do job with a
> simple FOR statement since RS is not very good for being programmed the
way
> we were used with VB. Of course I could do an assembly and do the
> programming there but I would rather do the job in the query/sql server
and
> let RS as neat/simple as possible.
> Any ideas? Regards and thanks in advance.
>|||You can create a digits table and cross join it.
e.g.
select top 8000 digit=identity(int,1,1)
into digits
from sysobjects,syscolumns
SELECT ArticleId, ArticleCount=digit
FROM TOrderDetails cross join digits
WHERE OrderId = @.MyOrder
and ArticleCount>=digit
ORDER BY ArticleId
-oj
"David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
news:%23vwfl43WFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have a simple query that retrieves the number of articles that an order
> has:
> SELECT ArticleId, ArticleCount FROM TOrderDetails WHERE OrderId =
> @.MyOrder
> Both ArticleId and ArticleCount are integers. A sample set result woud be
> like this one:
> ArticleId ArticleCount
> 1 3
> 2 2
> 3 5
> 4 1
> I need to print the labels for each single article that is sold. I need to
> expand/unfold that result set into something like:
> ArticleId ArticleCountId
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 1
> 3 2
> 3 3
> 3 4
> 3 5
> 4 1
> So, there will be 3 records for ArticleId = 1: Since 3 units are sold, I
> will need 3 labels (named 1, 2 and 3), and so on.
> I've been thinking about how to acomplish this but the only thing that
> goes to my mind is passing a table to a sp to iterate for i=1 to n for
> each Id. However, since passing a table to a SP is not possible I'm a
> little stuck-in-the-mud :(
>
> PD: I'm using Reporting Services so it is a little hard to do job with a
> simple FOR statement since RS is not very good for being programmed the
> way we were used with VB. Of course I could do an assembly and do the
> programming there but I would rather do the job in the query/sql server
> and let RS as neat/simple as possible.
> Any ideas? Regards and thanks in advance.
>|||Instead of counting try ranking:
DROP TABLE #article_sales
CREATE TABLE #article_sales ( as_id INT IDENTITY, article_id TINYINT,
sale_date DATETIME )
INSERT INTO #article_sales SELECT 1, '1 Apr 2005'
INSERT INTO #article_sales SELECT 1, '2 Apr 2005'
INSERT INTO #article_sales SELECT 1, '3 Apr 2005'
INSERT INTO #article_sales SELECT 2, '1 Apr 2005'
INSERT INTO #article_sales SELECT 2, '2 Apr 2005'
INSERT INTO #article_sales SELECT 3, '1 Apr 2005'
INSERT INTO #article_sales SELECT 3, '2 Apr 2005'
INSERT INTO #article_sales SELECT 3, '3 Apr 2005'
INSERT INTO #article_sales SELECT 3, '4 Apr 2005'
INSERT INTO #article_sales SELECT 3, '5 Apr 2005'
INSERT INTO #article_sales SELECT 4, '1 Apr 2005'
-- Your count query
SELECT article_id, COUNT(*)
FROM #article_sales
GROUP BY article_id
-- Instead of counting, try ranking
SELECT
as1.article_id,
( SELECT COUNT(*) FROM #article_sales as2 WHERE as1.article_id =
as2.article_id AND as2.as_id < as1.as_id ) + 1 rank
FROM #article_sales as1
Let me know how you get on.
Damien
"David Lightman Robles" wrote:

> Hi all,
> I have a simple query that retrieves the number of articles that an order
> has:
> SELECT ArticleId, ArticleCount FROM TOrderDetails WHERE OrderId = @.MyOrd
er
> Both ArticleId and ArticleCount are integers. A sample set result woud be
> like this one:
> ArticleId ArticleCount
> 1 3
> 2 2
> 3 5
> 4 1
> I need to print the labels for each single article that is sold. I need to
> expand/unfold that result set into something like:
> ArticleId ArticleCountId
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 1
> 3 2
> 3 3
> 3 4
> 3 5
> 4 1
> So, there will be 3 records for ArticleId = 1: Since 3 units are sold, I
> will need 3 labels (named 1, 2 and 3), and so on.
> I've been thinking about how to acomplish this but the only thing that goe
s
> to my mind is passing a table to a sp to iterate for i=1 to n for each Id.
> However, since passing a table to a SP is not possible I'm a little
> stuck-in-the-mud :(
>
> PD: I'm using Reporting Services so it is a little hard to do job with a
> simple FOR statement since RS is not very good for being programmed the wa
y
> we were used with VB. Of course I could do an assembly and do the
> programming there but I would rather do the job in the query/sql server an
d
> let RS as neat/simple as possible.
> Any ideas? Regards and thanks in advance.
>
>|||Thansk Uri & Oj for your hint about using an auxiliary table with numbers
and a cross join. It really did the job much easier than I thought it would
be.
Regards.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribi en el mensaje
news:%23vwfl43WFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have a simple query that retrieves the number of articles that an order
> has:
> SELECT ArticleId, ArticleCount FROM TOrderDetails WHERE OrderId =
> @.MyOrder
> Both ArticleId and ArticleCount are integers. A sample set result woud be
> like this one:
> ArticleId ArticleCount
> 1 3
> 2 2
> 3 5
> 4 1
> I need to print the labels for each single article that is sold. I need to
> expand/unfold that result set into something like:
> ArticleId ArticleCountId
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 1
> 3 2
> 3 3
> 3 4
> 3 5
> 4 1
> So, there will be 3 records for ArticleId = 1: Since 3 units are sold, I
> will need 3 labels (named 1, 2 and 3), and so on.
> I've been thinking about how to acomplish this but the only thing that
> goes to my mind is passing a table to a sp to iterate for i=1 to n for
> each Id. However, since passing a table to a SP is not possible I'm a
> little stuck-in-the-mud :(
>
> PD: I'm using Reporting Services so it is a little hard to do job with a
> simple FOR statement since RS is not very good for being programmed the
> way we were used with VB. Of course I could do an assembly and do the
> programming there but I would rather do the job in the query/sql server
> and let RS as neat/simple as possible.
> Any ideas? Regards and thanks in advance.
>