adjust my code

Hi,
I've created this code to determine the dates for an exam when a
certification must be met within a X number of months or years. The last
deadline date for an exam is the enddate. The marge where one can
schedule his time to study depends on how many exams he must take. So
based on the enddate i calculate backwards to the startdate to get
intervals of when the next examdate should be.
In my testscenario i used 12 months and the number of exams are 4. The
code gave me the results i needed, but when i tried e.g. 18 months the
results are not what i expected. The dates are somewhat correct but
there were too many dates.
Can someone see the error in my code?
declare @.exams int,
@.begindate datetime,
@.enddate datetime,
@.examdate datetime,
@.intervals int,
set @.begindate = '2005-11-23'
set @.enddate = dateadd(mm,12,@.begindate)
set @.exams = 4
set @.intervals = datediff(mm,@.begindate,@.enddate)/@.exams
create table #examdates(id int identity(1,1) , examdate datetime)
--i inserted the enddate as startingpoint, but this could be done
--better, i think
insert into #examdates(examdate)
values(@.enddate)
while (select count(*) from #examdates) <= @.intervals
begin
set @.examdate = (select examdate from #examdates where id in
(select max(id) from #examdates))
insert into #examdates(examdate)
select dateadd(mm,-@.exams,@.examdate) as examdate
end
select examdate from #examdates order by examdateJason
> results are not what i expected. The dates are somewhat correct but
> there were too many dates.
>
What do you want to return if you put 18 months in?
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:eonG0vfEGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I've created this code to determine the dates for an exam when a
> certification must be met within a X number of months or years. The last
> deadline date for an exam is the enddate. The marge where one can schedule
> his time to study depends on how many exams he must take. So based on the
> enddate i calculate backwards to the startdate to get intervals of when
> the next examdate should be.
> In my testscenario i used 12 months and the number of exams are 4. The
> code gave me the results i needed, but when i tried e.g. 18 months the
> results are not what i expected. The dates are somewhat correct but
> there were too many dates.
> Can someone see the error in my code?
> declare @.exams int,
> @.begindate datetime,
> @.enddate datetime,
> @.examdate datetime,
> @.intervals int,
> set @.begindate = '2005-11-23'
> set @.enddate = dateadd(mm,12,@.begindate)
> set @.exams = 4
> set @.intervals = datediff(mm,@.begindate,@.enddate)/@.exams
> create table #examdates(id int identity(1,1) , examdate datetime)
> --i inserted the enddate as startingpoint, but this could be
> done --better, i think
> insert into #examdates(examdate)
> values(@.enddate)
> while (select count(*) from #examdates) <= @.intervals
> begin
> set @.examdate = (select examdate from #examdates where id in
> (select max(id) from #examdates))
> insert into #examdates(examdate)
> select dateadd(mm,-@.exams,@.examdate) as examdate
> end
> select examdate from #examdates order by examdate|||You are creating a variable called intervals which is really the number of
months that you have to take each exam based on the way that you set it.
datediff(mm,@.begindate,@.enddate)/@.exams
So for 12 months this would be 3 but for 18 months it would be 4.5.
You then use this as the loop constraint for adding in your exam dates.
It seems to me like your look constraint should just be the number of exams
that you need to take since you are trying to find a date when to take each
of the exams.
This is the core of your problem. I didn't put any thought into the rest of
the algorithm and how you might be able to accomplish your goal easier.
HTH
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Jason" wrote:

> Hi,
> I've created this code to determine the dates for an exam when a
> certification must be met within a X number of months or years. The last
> deadline date for an exam is the enddate. The marge where one can
> schedule his time to study depends on how many exams he must take. So
> based on the enddate i calculate backwards to the startdate to get
> intervals of when the next examdate should be.
> In my testscenario i used 12 months and the number of exams are 4. The
> code gave me the results i needed, but when i tried e.g. 18 months the
> results are not what i expected. The dates are somewhat correct but
> there were too many dates.
> Can someone see the error in my code?
> declare @.exams int,
> @.begindate datetime,
> @.enddate datetime,
> @.examdate datetime,
> @.intervals int,
> set @.begindate = '2005-11-23'
> set @.enddate = dateadd(mm,12,@.begindate)
> set @.exams = 4
> set @.intervals = datediff(mm,@.begindate,@.enddate)/@.exams
> create table #examdates(id int identity(1,1) , examdate datetime)
> --i inserted the enddate as startingpoint, but this could be done
> --better, i think
> insert into #examdates(examdate)
> values(@.enddate)
> while (select count(*) from #examdates) <= @.intervals
> begin
> set @.examdate = (select examdate from #examdates where id in
> (select max(id) from #examdates))
> insert into #examdates(examdate)
> select dateadd(mm,-@.exams,@.examdate) as examdate
> end
> select examdate from #examdates order by examdate
>|||Ryan Powers wrote:
> You are creating a variable called intervals which is really the number of
> months that you have to take each exam based on the way that you set it.
> datediff(mm,@.begindate,@.enddate)/@.exams
> So for 12 months this would be 3 but for 18 months it would be 4.5.
> You then use this as the loop constraint for adding in your exam dates.
> It seems to me like your look constraint should just be the number of exam
s
> that you need to take since you are trying to find a date when to take eac
h
> of the exams.
>
> This is the core of your problem. I didn't put any thought into the rest
of
> the algorithm and how you might be able to accomplish your goal easier.
> HTH
>
Hi Ryan,
I want to calculate a date for taking an exam. The intervals are just
the number of months when the next exam must be taken.
Could you point out to me where i should better my code?|||Uri Dimant wrote:
> Jason
>
>
> What do you want to return if you put 18 months in?
>
> "Jason" <jasonlewis@.hotmail.com> wrote in message
> news:eonG0vfEGHA.2040@.TK2MSFTNGP14.phx.gbl...
>
>
>
Hi Uri,
I want to return 4 examdates because the number of exams to be taken are
4, the higher the months the longer someone may study until the next
date occurs.
In case of adding 18 months to the begindate, divide that with the
number of exams, you'll get 4.5 months. So knowing the enddate (is also
the last examdate deadline) i substract the 4.5 months from the enddate
which will give me the examdate before that and so on.
Can you find the mistake i have made in the code?|||Try the following script. I have given some hint about changes made in your
script. Inside the While loop, I have removed the select statement from the
#examdates table. This will enhance performance.
declare @.exams int,
@.begindate datetime,
@.enddate datetime,
@.examdate datetime,
@.intervals int
-- Added script
declare @.NextExamDate datetime, @.LastExamDate datetime
--
set @.begindate = '20051101'
set @.enddate = dateadd(mm,12,@.begindate)
-- Added script
set @.LastExamDate = @.enddate
--
set @.exams = 4
--[replaced with below line] set @.intervals =
datediff(mm,@.begindate,@.enddate)/@.exams
set @.intervals = datediff(dd,@.begindate,@.enddate)/@.exams
--create table #examdates(id int identity(1,1) , examdate datetime)
--i inserted the enddate as startingpoint, but this could be done
--better, i think
insert into #examdates(examdate)
values(@.enddate)
-- While loop script with lots of changes
while @.exams > 1
begin
set @.NextExamDate = dateadd(dd,-@.intervals,@.LastExamDate)
insert into #examdates(examdate) values(@.NextExamDate)
set @.LastExamDate = @.NextExamDate
set @.exams = @.exams -1
end
--
select examdate from #examdates order by examdate
"Jason" wrote:

> Ryan Powers wrote:
> Hi Ryan,
> I want to calculate a date for taking an exam. The intervals are just
> the number of months when the next exam must be taken.
> Could you point out to me where i should better my code?
>|||Sure. You don't need the intervals variable, since you are actually
recalcing it on the fly within the loop. I am going to just keep your base
logic, and show you how you can correct it so it works.
I changed it slightly to find days between exams because I'm thinking that
you can evenly divide the months by the number of exams is not correct. Wha
t
I did will not necessarily give you your last exam on your end date due to
integer math. But, it should be close. We could put in a condition that
checks if we are setting the last date and just set it as the enddate. Let
me know if you need help with that.-
declare @.exams int,
@.begindate datetime,
@.enddate datetime,
@.examdate datetime,
@.intervals int,
@.months int,
@.daysBetweenExams
set @.begindate = '2005-11-23'
set @.exams = 4
set @.months = 12
set @.enddate = dateadd(mm,@.months,@.begindate)
set @.daysBetweenExams = datediff(dd, @.begindate, @.enddate)/@.exams
create table #examdates(id int identity(1,1) , examdate datetime)
set @.examdate = @.begindate
while (select count(*) from #examdates) <= @.exams
begin
SELECT @.examdate = dateadd(dd, @.daysBetweenExams, @.examdate)
insert into #examdates(examdate)
values(@.examdate)
end
select examdate from #examdates order by examdate
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Jason" wrote:

> Ryan Powers wrote:
> Hi Ryan,
> I want to calculate a date for taking an exam. The intervals are just
> the number of months when the next exam must be taken.
> Could you point out to me where i should better my code?
>|||On Thu, 05 Jan 2006 14:28:42 +0100, Jason wrote:

>Hi,
>I've created this code to determine the dates for an exam when a
>certification must be met within a X number of months or years. The last
> deadline date for an exam is the enddate. The marge where one can
>schedule his time to study depends on how many exams he must take. So
>based on the enddate i calculate backwards to the startdate to get
>intervals of when the next examdate should be.
>In my testscenario i used 12 months and the number of exams are 4. The
>code gave me the results i needed, but when i tried e.g. 18 months the
>results are not what i expected. The dates are somewhat correct but
>there were too many dates.
>Can someone see the error in my code?
Hi Jason,
Why not use a set-based query instead of looping?
-- inputs:
DECLARE @.begindate datetime,
@.enddate datetime,
@.exams int
SET @.begindate = '2005-11-23'
SET @.enddate = '2007-05-23'
SET @.exams = 4
-- generate exam dates
--INSERT INTO @.examdate(examdate)
SELECT DATEADD(month,
Number * DATEDIFF(month, @.begindate, @.enddate) / @.exams,
@.begindate)
FROM dbo.Numbers
WHERE Number BETWEEN 1 AND @.exams
Note: this requires the use of a numbers table. See www.aspfaq.com/2516.
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis wrote:
> On Thu, 05 Jan 2006 14:28:42 +0100, Jason wrote:
>
>
> Hi Jason,
> Why not use a set-based query instead of looping?
> -- inputs:
> DECLARE @.begindate datetime,
> @.enddate datetime,
> @.exams int
> SET @.begindate = '2005-11-23'
> SET @.enddate = '2007-05-23'
> SET @.exams = 4
> -- generate exam dates
> --INSERT INTO @.examdate(examdate)
> SELECT DATEADD(month,
> Number * DATEDIFF(month, @.begindate, @.enddate) / @.exams,
> @.begindate)
> FROM dbo.Numbers
> WHERE Number BETWEEN 1 AND @.exams
> Note: this requires the use of a numbers table. See www.aspfaq.com/2516.
>
Hi hugo,
your solution did the job. Thnx!

AddNew then getting Unique ID

I have an auto-incremental field in my sql database table. After I add a
new record I need to get that ID. My below code adds the record with no
problems but the ID field I request always comes back empty. If I look in
the table the new record is there with the auto ID field.
hr = pConnection->Open(strCnn,"","",adConnectUnspecified);
hr= pRstPubInfo.CreateInstance(__uuidof(Recordset));
hr = pRstPubInfo->Open("messages",
_variant_t((IDispatch*)pConnection,true)
,
adOpenKeyset,adLockOptimistic,adCmdTable
);
pRstPubInfo->AddNew();
hr = pRstPubInfo->Fields->GetItem("message")->AppendChunk(varChunk);
hr = pRstPubInfo->Update();
_variant_t DBID = pRstPubInfo->Fields->Item["id"]->GetValue();
//DBID = EMPTY.Use a stored procedure, not add new. Optimistic recordsets and ad hoc SQL
are not optimal for performing inserts!
Anyway, then you could do this in one transaction and retrieve the output
variable:
CREATE PROCEDURE dbo.AddRow
@.value VARCHAR(32),
@.idOut INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT table(column) SELECT @.value;
SELECT @.idOut = SCOPE_IDENTITY();
END
GO
(Alternatively, you could use a scalar/resultset or, since it is an INT, you
could buck standard practice and use the return value.)
A
"Bob" <msgdev@.hotmail.com> wrote in message
news:%237C%23PnI4FHA.3000@.TK2MSFTNGP12.phx.gbl...
>I have an auto-incremental field in my sql database table. After I add a
>new record I need to get that ID. My below code adds the record with no
>problems but the ID field I request always comes back empty. If I look in
>the table the new record is there with the auto ID field.
>
> hr = pConnection->Open(strCnn,"","",adConnectUnspecified);
> hr= pRstPubInfo.CreateInstance(__uuidof(Recordset));
> hr = pRstPubInfo->Open("messages",
> _variant_t((IDispatch*)pConnection,true)
,
> adOpenKeyset,adLockOptimistic,adCmdTable
);
> pRstPubInfo->AddNew();
> hr = pRstPubInfo->Fields->GetItem("message")->AppendChunk(varChunk);
> hr = pRstPubInfo->Update();
> _variant_t DBID = pRstPubInfo->Fields->Item["id"]->GetValue();
> //DBID = EMPTY.
>|||I am adding a binary object to the database. It could be very large so I
thought using AddChunk would be better. Is there a way to add binary data
using stored procedures? Is there a way to add chunks? May be I am doing
this all wrong. Any help would be appreciated.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OwRUM4I4FHA.636@.TK2MSFTNGP10.phx.gbl...
> Use a stored procedure, not add new. Optimistic recordsets and ad hoc SQL
> are not optimal for performing inserts!
> Anyway, then you could do this in one transaction and retrieve the output
> variable:
> CREATE PROCEDURE dbo.AddRow
> @.value VARCHAR(32),
> @.idOut INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT table(column) SELECT @.value;
> SELECT @.idOut = SCOPE_IDENTITY();
> END
> GO
> (Alternatively, you could use a scalar/resultset or, since it is an INT,
> you could buck standard practice and use the return value.)
> A
>
> "Bob" <msgdev@.hotmail.com> wrote in message
> news:%237C%23PnI4FHA.3000@.TK2MSFTNGP12.phx.gbl...
>|||AppendChunk can be used for Parameter objects as well as Field objects.
Bob wrote:
> I am adding a binary object to the database. It could be very large
> so I thought using AddChunk would be better. Is there a way to add
> binary data using stored procedures? Is there a way to add chunks?
> May be I am doing this all wrong. Any help would be appreciated.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OwRUM4I4FHA.636@.TK2MSFTNGP10.phx.gbl...
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||CREATE PROCEDURE sp_InsertBLOB
@.ID int = NULL OUT,
@.BLOB image = NULL
AS
SET NOCOUNT ON
INSERT INTO BLOBTable ( BLOB ) VALUES( @.BLOB )
SELECT @.ID = SCOPE_IDENTITY()
END
GO
The strange thing to me is that you are worried about the efficiency of
sending a "large binary object" to the server, but you are willing to pull
down an entire table full of them just to perform an insert?
John
"Bob" wrote:

> I am adding a binary object to the database. It could be very large so I
> thought using AddChunk would be better. Is there a way to add binary data
> using stored procedures? Is there a way to add chunks? May be I am doing
> this all wrong. Any help would be appreciated.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:OwRUM4I4FHA.636@.TK2MSFTNGP10.phx.gbl...
>
>

Adding user accounts programagically

Hello,

I need to a a large list of users to an MSAS 2000 cube, where can I look to find code to do this? If you have no code to do this I would appreicate if you could advise which objects I can use to accomplish this.

TIA

Hi Tia,

Try start working with the following objects: Role, CubePermission, DatabasePermission.

Yan

|||Thanks Yan,

TIA = Thanks in Advance

Adding the Data Flow Task Programmatically

Is it possible to add a Fuzzy Grouping Transformation in a Data flow task by Programmatically ? If it possible, what is the C# or VB .net code for that ?Yes, you can. If you see this topic in Books Online it explains how to do it, and how to get the appropriate ProgID. "Adding Data Flow Components Programmatically"

Adding SubTotol of a Group to group

Here is my Table Structure ( from Oracle database)
Team | Customer Code | Amount | Credit Limit
1 , a, 100, 1000
1 , a , 200, 1000
1 , b, 100, 100
1, b, 1000, 100
1, b, 2000, 100
2, a, 100, 2000

For the Report, I want to group the Team and Sum each customer total Amount and Show the Exceed limit amount.
Here I want to present
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 3000

2 a 100 2000 0
Team Total 100 0

Total 3400 3000

BUT it turn out..
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 2300 ( Problem here a )
2 a 100 2000 0
Team Total 100 0 ( Problem here a )
Total 3400 2400 ( Problem here b)

I Grouped the Custoer Code and Team I can preform the sum
however I can't Do the Exceed total
becoz the value should be
iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0)
but for the team total in team 1 the result is 2300 ( 3300 - customer a 's limit) not add from exceed amount

And the finial total it turns out 2400 (3400 - 1000)

I have tried use the coding to sum up the exceed
but I found that the group total is sumup first than the sum up the detail :

Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 0

2 a 100 2000 0
Team Total 100 3000 ( The Total from Team 1 ! )
Total 3400 0 ( Problem here b)

this situration , I can't change the query statement
I can do the good result for CR report
but for reporting service 2005, I can't to the first report result
Any one can help me ?
thank youAre you using "InScope"?|||

Not Really

Now the Problems should be on "Team Total of Exceed "

The Reporting service Cannot just sum up the Exceed for each customer in a Team

I want a solution for it thank you

|||Ok either you are using Inscope or not.

'Not really' doesn't tell me this.|||

adolf garlic wrote:

Ok either you are using Inscope or not.

'Not really' doesn't tell me this.

Sorry,

I 'm not using "Inscope"

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

What is the expression that you use in Exceed column? Is it

" iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0) " as mentioned in your post?

Why are you using First(Creditlimt) in the expression? First(Creditlimt) will always return the first value in the group.

Try using this expression.

iif (Sum(amount)>Sum(Creditlimt) , Sum(amount)-Sum(Creditlimt), 0)

|||

Sorry this Creditlimit is per customer at a period of time. therefore It may not sum up the Creditlimit. since I grouped from the customer, frist( Credit limit ) will be get the one of the value of creditlimt by each customer comparing with the sum of amount.

thank you I may try this expression tomorrow

|||

Even if you can't change the source query, you can actually add calculated fields to the dataset.

Go to the data tab, then from the dataset window (next to toolbox on the left, display this by choosing View Menu -> Datasets)

Right Click Dataset and choose Add

Select Calculated Field and give it a name

Use the following as the expression:
=Iif(Fields!amount.Value > Fields!Creditlimit.Value, Fields!amount.Value - Fields!Creditlimit.Value, 0)

Adding SubTotol of a Group to group

Here is my Table Structure ( from Oracle database)
Team | Customer Code | Amount | Credit Limit
1 , a, 100, 1000
1 , a , 200, 1000
1 , b, 100, 100
1, b, 1000, 100
1, b, 2000, 100
2, a, 100, 2000

For the Report, I want to group the Team and Sum each customer total Amount and Show the Exceed limit amount.
Here I want to present
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 3000

2 a 100 2000 0
Team Total 100 0

Total 3400 3000

BUT it turn out..
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 2300 ( Problem here a )
2 a 100 2000 0
Team Total 100 0 ( Problem here a )
Total 3400 2400 ( Problem here b)

I Grouped the Custoer Code and Team I can preform the sum
however I can't Do the Exceed total
becoz the value should be
iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0)
but for the team total in team 1 the result is 2300 ( 3300 - customer a 's limit) not add from exceed amount

And the finial total it turns out 2400 (3400 - 1000)

I have tried use the coding to sum up the exceed
but I found that the group total is sumup first than the sum up the detail :

Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 0

2 a 100 2000 0
Team Total 100 3000 ( The Total from Team 1 ! )
Total 3400 0 ( Problem here b)

this situration , I can't change the query statement
I can do the good result for CR report
but for reporting service 2005, I can't to the first report result
Any one can help me ?
thank youAre you using "InScope"?|||

Not Really

Now the Problems should be on "Team Total of Exceed "

The Reporting service Cannot just sum up the Exceed for each customer in a Team

I want a solution for it thank you

|||Ok either you are using Inscope or not.

'Not really' doesn't tell me this.|||

adolf garlic wrote:

Ok either you are using Inscope or not.

'Not really' doesn't tell me this.

Sorry,

I 'm not using "Inscope"

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

What is the expression that you use in Exceed column? Is it

" iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0) " as mentioned in your post?

Why are you using First(Creditlimt) in the expression? First(Creditlimt) will always return the first value in the group.

Try using this expression.

iif (Sum(amount)>Sum(Creditlimt) , Sum(amount)-Sum(Creditlimt), 0)

|||

Sorry this Creditlimit is per customer at a period of time. therefore It may not sum up the Creditlimit. since I grouped from the customer, frist( Credit limit ) will be get the one of the value of creditlimt by each customer comparing with the sum of amount.

thank you I may try this expression tomorrow

|||

Even if you can't change the source query, you can actually add calculated fields to the dataset.

Go to the data tab, then from the dataset window (next to toolbox on the left, display this by choosing View Menu -> Datasets)

Right Click Dataset and choose Add

Select Calculated Field and give it a name

Use the following as the expression:
=Iif(Fields!amount.Value > Fields!Creditlimit.Value, Fields!amount.Value - Fields!Creditlimit.Value, 0)

Adding SubTotol of a Group to group

Here is my Table Structure ( from Oracle database)
Team | Customer Code | Amount | Credit Limit
1 , a, 100, 1000
1 , a , 200, 1000
1 , b, 100, 100
1, b, 1000, 100
1, b, 2000, 100
2, a, 100, 2000

For the Report, I want to group the Team and Sum each customer total Amount and Show the Exceed limit amount.
Here I want to present
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 3000

2 a 100 2000 0
Team Total 100 0

Total 3400 3000

BUT it turn out..
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 2300 ( Problem here a )
2 a 100 2000 0
Team Total 100 0 ( Problem here a )
Total 3400 2400 ( Problem here b)

I Grouped the Custoer Code and Team I can preform the sum
however I can't Do the Exceed total
becoz the value should be
iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0)
but for the team total in team 1 the result is 2300 ( 3300 - customer a 's limit) not add from exceed amount

And the finial total it turns out 2400 (3400 - 1000)

I have tried use the coding to sum up the exceed
but I found that the group total is sumup first than the sum up the detail :

Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 0

2 a 100 2000 0
Team Total 100 3000 ( The Total from Team 1 ! )
Total 3400 0 ( Problem here b)

this situration , I can't change the query statement
I can do the good result for CR report
but for reporting service 2005, I can't to the first report result
Any one can help me ?
thank youAre you using "InScope"?|||

Not Really

Now the Problems should be on "Team Total of Exceed "

The Reporting service Cannot just sum up the Exceed for each customer in a Team

I want a solution for it thank you

|||Ok either you are using Inscope or not.

'Not really' doesn't tell me this.|||

adolf garlic wrote:

Ok either you are using Inscope or not.

'Not really' doesn't tell me this.

Sorry,

I 'm not using "Inscope"

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

What is the expression that you use in Exceed column? Is it

" iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0) " as mentioned in your post?

Why are you using First(Creditlimt) in the expression? First(Creditlimt) will always return the first value in the group.

Try using this expression.

iif (Sum(amount)>Sum(Creditlimt) , Sum(amount)-Sum(Creditlimt), 0)

|||

Sorry this Creditlimit is per customer at a period of time. therefore It may not sum up the Creditlimit. since I grouped from the customer, frist( Credit limit ) will be get the one of the value of creditlimt by each customer comparing with the sum of amount.

thank you I may try this expression tomorrow

|||

Even if you can't change the source query, you can actually add calculated fields to the dataset.

Go to the data tab, then from the dataset window (next to toolbox on the left, display this by choosing View Menu -> Datasets)

Right Click Dataset and choose Add

Select Calculated Field and give it a name

Use the following as the expression:
=Iif(Fields!amount.Value > Fields!Creditlimit.Value, Fields!amount.Value - Fields!Creditlimit.Value, 0)

adding sql parameter in VB

I have a details view with several parameters set up in myasp.net 2.0 code, I want to add a parameter before the sql parameter is executed. I need to use the find control of the details view because I am using items/edit item templates in my details view control.
I tried this(see below) as well as the detailsview item command event args to no avail. It doesn't see the other parameters that have already been declared in my asp.net code. I don't want to have to declare all my varibles that are already in my asp.net code. I just want to add another parameter.

Sub InsertNew(ByVal sender As Object, ByVal e As DetailsViewInsertEventArgs) _
Handles dvEvents.ItemInserting

Dim dvr As DetailsViewRow

For Each dvr In dvEvents.Rows

Dim CatIDup As Integer = CType(dvr.FindControl("ddlCat"), DropDownList).SelectedValue
sdsevents.InsertParameters.Add("evCatID", CatIDup)
sdsevents.Insert()

Handle the sdsevents.Inserting event.

e.Command.Parameters.Add("@.evCatID",SqlDbType.Int).Value={something}

Also, in your prior code, it's confusing, because you are iterating a set of rows in dvr, and dvr hasn't been set. Even if it was, why would you want to add a parameter for each row?

|||

I tried what you suggested and i get sqldbtype is not declared. see below

As far as iiterating through the rows, I could not find a better way to do that if i did not iterate the rows i would get the error

about my dvr not being set if i did the for each i did not get that error.
I am happy to hear a better suggestion of how to do this.
Thanks

Sub InsertNew(ByVal senderAsObject,ByVal eAs SqlDataSourceCommandEventArgs) _

Handles sdsevents.Inserting

Dim dvrAs DetailsViewRow

ForEach dvrIn dvEvents.Rows

' Find the Selected Category Name ID Value

Dim CatIDupAsInteger =CType(dvr.FindControl("ddlCat"), DropDownList).SelectedValue

e.Command.Parameters.Add("@.evCatID",SqlDbType.Int).Value={CatIDup}

Next

EndSub

|||Sub InsertNew(ByVal senderAsObject,ByVal eAs SqlDataSourceCommandEventArgs) _

Handles sdsevents.Inserting

Dim dvAs DetailsView=detailsview1

' Find the Selected Category Name ID Value

Dim CatIDupAsInteger =CType(dv.FindControl("ddlCat"), DropDownList).SelectedValue

e.Command.Parameters.Add("@.evCatID",System.Data.SqlDbType.Int).Value=CatIDup

EndSub

That assumes your detailsview is called detailsview1.

You can also do it from the detailsview events, but you need to reference the parameter collection that is passed to you. In the ItemInserting event, it would be e.Values. In the ItemUpdating event, it would be e.NewValues. Normally I would declaritively define the parameter (In design view... To assign the type, nullability, default value, etc etc). Then I would either set the value in e.Values/e.NewValues or e.Command.Parameters("@.evCatID").Value= to set the value of the already existing parameter.

|||

I got it I did it this way

thanks for your help!!

PublicSub InsertNew(ByVal senderAsObject,ByVal eAs DetailsViewInsertEventArgs) _

Handles dvEvents.ItemInserting

Dim dvAs DetailsView = dvEvents

' 'Find the Selected Category Name ID Value

Dim CatIDupAsInteger =CType(dv.FindControl("ddlCat"), DropDownList).SelectedValue

sdsevents.InsertParameters.Add("evCatID", CatIDup)

EndSub

adding some rows to a select

Hi folks,

I've a sql query problem I was wondering if you all had a quick and
dirty solution for. I've a query:

Select code, value from table_a where date in
(2004) and a_code in ('1000','2000') and b_code in ('01000','02000')

This returns a table that looks like:

A_CODE B_CODE VALUE
-- -- --

1000 01000 $500
1000 02000 $750

What I'd like to see is:

A_CODE B_CODE VALUE
-- -- --

1000 01000 $500
1000 02000 $750
2000 01000 $0
2000 02000 $0

Any suggestions on how to rewrite my query so the results show A_CODE
2000 with a VALUE of 0 or null?

Thank much in advance!

MarcMarc (brownjenkn@.aol.com) writes:
> I've a sql query problem I was wondering if you all had a quick and
> dirty solution for. I've a query:
> Select code, value from table_a where date in
> (2004) and a_code in ('1000','2000') and b_code in ('01000','02000')
> This returns a table that looks like:
> A_CODE B_CODE VALUE
> -- -- --
> 1000 01000 $500
> 1000 02000 $750
> What I'd like to see is:
> A_CODE B_CODE VALUE
> -- -- --
> 1000 01000 $500
> 1000 02000 $750
> 2000 01000 $0
> 2000 02000 $0
> Any suggestions on how to rewrite my query so the results show A_CODE
> 2000 with a VALUE of 0 or null?

CREATE TABLE a_code (a_code char(4) NOT NULL
CREATE TABLE b_code (b_code char(5) NOT NULL

go
INSERT a_code (a_code) VALUES ('1000')
INSERT a_code (a_code) VALUES ('2000')
INSERT b_code (b_code) VALUES ('01000')
INSERT b_code (b_code) VALUES ('02000')
go
SELECT a.a_code, b.b_code, coalesce(t.value, 0)
FROM (a_code a
CROSS JOIN b_code b)
LEFT JOIN table_a t ON a.a_code = t.a_code
AND b.b_code = t.b_code
ABD t.date = '2004'

Here I am handling a_code and b_code in the same way, so you will
get output for missing b_codes as well.

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

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

Adding ScriptTask programatically

Hi,

I'm developing tool for generating SSIS packages.

I need to add ScriptTask to package programatically and set its script code.

There is no problem for adding package, but I don't know how to set its script code, programatically.

Can anyone help me?

Thanks in advance, Borko

The trick is to use ScriptTaskCodeProvider class, PutSourceCode method.

Monicker argument is build from ScriptTask.VsaProjectName property.

It is usefull to analyze valid package XML during this action.

Regards, Borko

Adding Roles/Users using SQL Procedures...

Hi everyone out there in ASP.NET land.

Have a quick question...

How do I add a role and/or a user through code (Specifically SQL Statements). If not through SQL, then maybe VB.NET?

Thanks ahead of time,
DenvasCheck in this link a couple of PageDowns in the sectionCreate a New Login

The Sql syntax for adding a role member:

EXEC sp_addrolemember N'db_owner', N'SomeUserName'

|||Thank you so much for the info. Going to apply it tomorrow.

-Denvas

Adding Reference to App_Code of current website

I know it's possible to add a reference to a report to a custom class, but I was wondering if it was possible to refer to the code in the App_Code directory of the current website?

We have a lot of built in functionality that I could take advantage of, if I can make a call to the classes in the current site. If I have to create a new class, there's a lot of things that would have to be duplicated to get it to work and we definately don't want to have to duplicate efforts.

TIA

Well, you can reference custom assemblies from within your reports (it could be private or signed ones)

herehttp://msdn2.microsoft.com/en-us/library/aa179513(SQL.80).aspx you gonna find all you need to do so.

Hope this helps

Adding Reference and Importing .NET into Script Transformation

I have a .NET component that I want to import into a Script Transformation of a Data Flow. Going into the script code (editing Script transformation and clocking "Design Script" button), I try to "Add Reference" to the component (Add Reference selection under "Project" menu), but I do not see it - nor do I have the option to Browse for the component.

How do I establish a reference to an external .NET component so I can use it in my transformation? It seems unnecessary to have to add all of the class modules for the .NET component into the transformation or Copy/Paste the code from those same class modules just to execute that code?

Any ideas?

Many thanks...

You'll need to put the assembly into the framework folder for it to be found by the Add Reference dialog-

C:\WINDOWS\Microsoft.NET\Framework\v2.0.<whatever>\

You will also need to GAC it for runtime.

|||Here is the current BOL comment on this subject, from the updated topic "Using the .NET Framework and Other Assemblies in the Script Component:"

The .NET tab of the Add Reference dialog box in Microsoft Visual Studio for Applications is largely limited to assemblies from the Microsoft .NET Framework class library. The contents of the list are determined by file location and not by installation in the global assembly cache (GAC) or by other assembly properties. The Add Reference dialog box in VSA does not include the Browse button that is present in Microsoft Visual Studio for locating and referencing other managed assemblies, and does not include the COM tab for referencing COM components. Furthermore, you cannot cause assemblies from other locations to be displayed in this list in VSA by adding other folder names under the AssemblyFolders registry key, as described in the Microsoft Knowledge Base for use with Visual Studio. For more information, see How to display an assembly in the Add Reference dialog box.|||

I have a very simple .dll with a method that returns a string.

I can see it, and therefore reference it through the script. However, when I run the package I get the following error:

Could not load file assembly ‘Test_dll’, version=1.0.0.0,Culture=neutral, Publickey=null’ or one of its dependencies. The system cannot find the file specified.

What does it mean?

Thanks.
-w

|||I see that publickey is null, hence it's not strong named, hence it could not have been placed in the GAC. You need to strong name it (look up docs for sn.exe) and place it in the GAC (gacutil /if mydll.dll). Hope this helps.|||

Where do I add the DLL and GAC it - on the Client machine performing the development or on the local SQL Server machine?

|||Wherever you execute the package, so both.

I assume you will execute the package on the workstation during development, so it will need to be GAC'd there just for testing the package, and when you deploy to the server it will also need to be in the server's GAC.|||If the package is saved in File System as opposed to SQL Server, am I correct in assuming that the package, even initiated from a command prompt or BAT/CMD file, still "runs" on the SQL Server?|||The package runs on the machine that is running the bat file (dtexec).|||

DouglasL wrote:

Here is the current BOL comment on this subject, from the updated topic "Using the .NET Framework and Other Assemblies in the Script Component:"

The .NET tab of the Add Reference dialog box in Microsoft Visual Studio for Applications is largely limited to assemblies from the Microsoft .NET Framework class library. The contents of the list are determined by file location and not by installation in the global assembly cache (GAC) or by other assembly properties. The Add Reference dialog box in VSA does not include the Browse button that is present in Microsoft Visual Studio for locating and referencing other managed assemblies, and does not include the COM tab for referencing COM components. Furthermore, you cannot cause assemblies from other locations to be displayed in this list in VSA by adding other folder names under the AssemblyFolders registry key, as described in the Microsoft Knowledge Base for use with Visual Studio. For more information, see How to display an assembly in the Add Reference dialog box.

So is there a way to refence other assemblies from a VSA script task. For example we have a web service that uses WSE and need to reference this from our script. We also have a generic proxy which uses this which we need to reference. How do we do this?

The reason I'm asking is that I'm working with one of our .Net guys on this and he recoiled and squirmed his face when I talked about putting DLLs into Windows\Microsoft.Net\Framework folder. And I can kinda see his point. VSA does seem rather limited in this respect.

-Jamie|||Unfortunately there is not.

I likewise recoiled and squirmed about having individuals (including myself) cluttering the .NET Framework directory with their homemade DLLs, and squirmed even more about saying so in BOL...which is why the paragraph only hints at the solution for others to deduce.

A peculiar restriction indeed of VSA.
|||

DouglasL wrote:

Unfortunately there is not.

I likewise recoiled and squirmed about having individuals (including myself) cluttering the .NET Framework directory with their homemade DLLs, and squirmed even more about saying so in BOL...which is why the paragraph only hints at the solution for others to deduce.

A peculiar restriction indeed of VSA.

Thanks for the confirmation Doug.

We've got 2 options at the moment. Stick them in .NET framework dir or roll our own tasks. Guess which is looking most likely at the moment!!!

-Jamie|||Its worth pointing something out here that I believe to be true.

The DLL does need to get put into the .NET Framework directory as Doug has said - but only to enable you to use it at design-time. The DLL does not need to be there at runtime - VSA can pick it up from the GAC. To prove this - change the name of one of your DLLs that has been put in this folder in order for VSA to use it. You'll get errors at design-time because VSA won't be able to find the DLL but your package will still execute successfully if the DLL is GAC'd. This means that the DLL only has to go into the nasty .NET Framework directory on development machines - not on your machines that execute the packages in a live environment

Perhaps this is obvious to people that know .Net intimately but it was a pleasant surprise to me.

-Jamie|||Just for future reference, a BOL link to the topic Doug mentions. It doesn't highlight the runtime vs design-time GAC requirement though.

Using the .NET Framework and Other Assemblies in the Script Component

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/c3913c15-66aa-4b61-89b5-68488fa5f0a4.htm

Adding Reference and Importing .NET into Script Transformation

I have a .NET component that I want to import into a Script Transformation of a Data Flow. Going into the script code (editing Script transformation and clocking "Design Script" button), I try to "Add Reference" to the component (Add Reference selection under "Project" menu), but I do not see it - nor do I have the option to Browse for the component.

How do I establish a reference to an external .NET component so I can use it in my transformation? It seems unnecessary to have to add all of the class modules for the .NET component into the transformation or Copy/Paste the code from those same class modules just to execute that code?

Any ideas?

Many thanks...

You'll need to put the assembly into the framework folder for it to be found by the Add Reference dialog-

C:\WINDOWS\Microsoft.NET\Framework\v2.0.<whatever>\

You will also need to GAC it for runtime.

|||Here is the current BOL comment on this subject, from the updated topic "Using the .NET Framework and Other Assemblies in the Script Component:"

The .NET tab of the Add Reference dialog box in Microsoft Visual Studio for Applications is largely limited to assemblies from the Microsoft .NET Framework class library. The contents of the list are determined by file location and not by installation in the global assembly cache (GAC) or by other assembly properties. The Add Reference dialog box in VSA does not include the Browse button that is present in Microsoft Visual Studio for locating and referencing other managed assemblies, and does not include the COM tab for referencing COM components. Furthermore, you cannot cause assemblies from other locations to be displayed in this list in VSA by adding other folder names under the AssemblyFolders registry key, as described in the Microsoft Knowledge Base for use with Visual Studio. For more information, see How to display an assembly in the Add Reference dialog box.|||

I have a very simple .dll with a method that returns a string.

I can see it, and therefore reference it through the script. However, when I run the package I get the following error:

Could not load file assembly ‘Test_dll’, version=1.0.0.0,Culture=neutral, Publickey=null’ or one of its dependencies. The system cannot find the file specified.

What does it mean?

Thanks.
-w

|||I see that publickey is null, hence it's not strong named, hence it could not have been placed in the GAC. You need to strong name it (look up docs for sn.exe) and place it in the GAC (gacutil /if mydll.dll). Hope this helps.|||

Where do I add the DLL and GAC it - on the Client machine performing the development or on the local SQL Server machine?

|||Wherever you execute the package, so both.

I assume you will execute the package on the workstation during development, so it will need to be GAC'd there just for testing the package, and when you deploy to the server it will also need to be in the server's GAC.|||If the package is saved in File System as opposed to SQL Server, am I correct in assuming that the package, even initiated from a command prompt or BAT/CMD file, still "runs" on the SQL Server?|||The package runs on the machine that is running the bat file (dtexec).|||

DouglasL wrote:

Here is the current BOL comment on this subject, from the updated topic "Using the .NET Framework and Other Assemblies in the Script Component:"

The .NET tab of the Add Reference dialog box in Microsoft Visual Studio for Applications is largely limited to assemblies from the Microsoft .NET Framework class library. The contents of the list are determined by file location and not by installation in the global assembly cache (GAC) or by other assembly properties. The Add Reference dialog box in VSA does not include the Browse button that is present in Microsoft Visual Studio for locating and referencing other managed assemblies, and does not include the COM tab for referencing COM components. Furthermore, you cannot cause assemblies from other locations to be displayed in this list in VSA by adding other folder names under the AssemblyFolders registry key, as described in the Microsoft Knowledge Base for use with Visual Studio. For more information, see How to display an assembly in the Add Reference dialog box.

So is there a way to refence other assemblies from a VSA script task. For example we have a web service that uses WSE and need to reference this from our script. We also have a generic proxy which uses this which we need to reference. How do we do this?

The reason I'm asking is that I'm working with one of our .Net guys on this and he recoiled and squirmed his face when I talked about putting DLLs into Windows\Microsoft.Net\Framework folder. And I can kinda see his point. VSA does seem rather limited in this respect.

-Jamie|||Unfortunately there is not.

I likewise recoiled and squirmed about having individuals (including myself) cluttering the .NET Framework directory with their homemade DLLs, and squirmed even more about saying so in BOL...which is why the paragraph only hints at the solution for others to deduce.

A peculiar restriction indeed of VSA.|||

DouglasL wrote:

Unfortunately there is not.

I likewise recoiled and squirmed about having individuals (including myself) cluttering the .NET Framework directory with their homemade DLLs, and squirmed even more about saying so in BOL...which is why the paragraph only hints at the solution for others to deduce.

A peculiar restriction indeed of VSA.

Thanks for the confirmation Doug.

We've got 2 options at the moment. Stick them in .NET framework dir or roll our own tasks. Guess which is looking most likely at the moment!!!

-Jamie|||Its worth pointing something out here that I believe to be true.

The DLL does need to get put into the .NET Framework directory as Doug has said - but only to enable you to use it at design-time. The DLL does not need to be there at runtime - VSA can pick it up from the GAC. To prove this - change the name of one of your DLLs that has been put in this folder in order for VSA to use it. You'll get errors at design-time because VSA won't be able to find the DLL but your package will still execute successfully if the DLL is GAC'd. This means that the DLL only has to go into the nasty .NET Framework directory on development machines - not on your machines that execute the packages in a live environment

Perhaps this is obvious to people that know .Net intimately but it was a pleasant surprise to me.

-Jamie|||Just for future reference, a BOL link to the topic Doug mentions. It doesn't highlight the runtime vs design-time GAC requirement though.

Using the .NET Framework and Other Assemblies in the Script Component

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/c3913c15-66aa-4b61-89b5-68488fa5f0a4.htm

Adding New Measure to OLAP Cube

To add a record count measure to the olap cube. Create the cube as
usual then run the following VB code with command line parameters
Step1: Build your cube as usual
Step2: Convert the blow vb code to exe prog
Step3: Run the exe with the <Ananlysis server name> <cube name>
parameters (e.g) OLAPcount.exe <Analysis Server> <Cube Name>
Public Sub main()
Dim dsoServer As New DSO.Server
Dim dsoDB As DSO.MDStore
Dim dsoCube As DSO.MDStore
Dim dsoMea As DSO.Measure
Dim dsoAssFactCube As DSO.Cube
Dim dsoPortAnalyzerCube As DSO.Cube
'for storing initial command line arguments as entered by user
Dim strArgs() As String
'for storing the parsed command line arguments
Dim ParsedArgs As String
'for storing the final array of command line arguments
Dim finalArgs() As String
'Splitting the command line arguments based on a space
strArgs = Split(Command$, " ")
'Parsing the command line arguments to generate the parsed string
For i = 0 To UBound(strArgs)
If Len(Trim(strArgs(i))) > 0 Then
ParsedArgs = ParsedArgs & Trim(strArgs(i)) & " "
End If
Next
'Splitting the parsed string into final array of arguments
finalArgs = Split(ParsedArgs, " ")
'Check for correct number of arguments
If UBound(finalArgs) < 2 Then
MsgBox ("Wrong Syntax..." & "or wrong number of
arguments....Correcet Syntax : OLAPcount.exe <Analysis Server> <Cube
Name> (e.g)OLAPcount.exe livdwqprj03 AIGTMSReport1")
Else
'connect to the server (Analysis Server name)
dsoServer.Connect (finalArgs(0))
'Examine whether all necessary components are present (Cube
name)
If dsoServer.MDStores.Find(finalArgs(1)) = False Then
GoTo err_no_database
End If
'Connect with the data base (CUBE) (Cube name)
Set dsoDB = dsoServer.MDStores(finalArgs(1))
If dsoDB.DataSources.Count = 0 Then
GoTo err_no_datasource
ElseIf dsoDB.Dimensions.Count = 0 Then
GoTo err_no_dimensions
ElseIf dsoDB.MDStores.Find("MSP_ASSN_FACT") = False Then
GoTo err_no_fact_cube
ElseIf dsoDB.MDStores.Find("MSP_PORTFOLIO_ANALYZER") = False
Then
GoTo err_no_analyzer
End If
'Set the cube table to use
Set dsoAssFactCube = dsoDB.MDStores("MSP_ASSN_FACT")
Set dsoPortAnalyzerCube =
dsoDB.MDStores("MSP_PORTFOLIO_ANALYZER")
'Specify the name of the new measure
Set dsoMea = dsoAssFactCube.Measures.AddNew("Total
Assignments")
'Specify the source column based on which the operation need to
be performed
'dsoMea.SourceColumn =
"""MSP_CUBE_ASSN_FACT"".""ENT_ASSIGNMENT_CODE6"""
dsoMea.SourceColumn = """MSP_CUBE_ASSN_FACT"".""PROJ_UID"""
'The datatype for the column
dsoMea.SourceColumnType = ADODB.DataTypeEnum.adDecimal
'The method for the column aggSum or aggCount aggregates the
column by summation or counts.
dsoMea.AggregateFunction = aggCount
'update the cube
dsoAssFactCube.Update
dsoAssFactCube.Process
'dsoAnalyzerCube represents a virtual Cube. the measure of a
virtual Cubes has
'the characteristics of the measure of the material cubes
Set dsoMea = dsoPortAnalyzerCube.Measures.AddNew("Total
Assignments")
'The column is indicated in "more normal" form, since the
measure belongs to the virtual Cube!
'dsoMea.SourceColumn = "MSP_ASSN_FACT.FIXED COST"
dsoMea.SourceColumn = "MSP_ASSN_FACT.Total Assignments"
dsoPortAnalyzerCube.Update
dsoPortAnalyzerCube.Process
dsoDB.Process
leave_now:
UserOLAPUpdate = 0
' Exit Function
err_no_database:
l_errnum = 1
s_errdesc = "Datenbank konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 1
' Exit Function
err_no_datasource:
l_errnum = 1
s_errdesc = "Datenquelle konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 2
' Exit Function
err_no_dimensions:
l_errnum = 1
s_errdesc = "Dimensionen konnten nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 3
' Exit Function
err_no_fact_cube:
l_errnum = 1
s_errdesc = "Cube MSP_ASSN_FACT konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 4
' Exit Function
err_no_analyzer:
l_errnum = 1
s_errdesc = "Cube MSP_PORTFOLIO_ANALYZER konnte nicht gefunden
werden! "
UserOLAPUpdate = vbObjectError + 5
' Exit Function
error_handler:
l_errnum = Err.Number
s_errdesc = Err.Description
UserOLAPUpdate = 1 ' although it could be any non-zero value
' to indicate an error
End If
End SubPerhaps you should head for the ng
http://www.microsoft.com/communitie...sqlserver.olap
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"wilsonjust@.gmail.com" wrote:

> To add a record count measure to the olap cube. Create the cube as
> usual then run the following VB code with command line parameters
> Step1: Build your cube as usual
> Step2: Convert the blow vb code to exe prog
> Step3: Run the exe with the <Ananlysis server name> <cube name>
> parameters (e.g) OLAPcount.exe <Analysis Server> <Cube Name>
> Public Sub main()
> Dim dsoServer As New DSO.Server
> Dim dsoDB As DSO.MDStore
> Dim dsoCube As DSO.MDStore
> Dim dsoMea As DSO.Measure
> Dim dsoAssFactCube As DSO.Cube
> Dim dsoPortAnalyzerCube As DSO.Cube
> 'for storing initial command line arguments as entered by user
> Dim strArgs() As String
> 'for storing the parsed command line arguments
> Dim ParsedArgs As String
> 'for storing the final array of command line arguments
> Dim finalArgs() As String
> 'Splitting the command line arguments based on a space
> strArgs = Split(Command$, " ")
> 'Parsing the command line arguments to generate the parsed string
> For i = 0 To UBound(strArgs)
> If Len(Trim(strArgs(i))) > 0 Then
> ParsedArgs = ParsedArgs & Trim(strArgs(i)) & " "
> End If
> Next
> 'Splitting the parsed string into final array of arguments
> finalArgs = Split(ParsedArgs, " ")
> 'Check for correct number of arguments
> If UBound(finalArgs) < 2 Then
> MsgBox ("Wrong Syntax..." & "or wrong number of
> arguments....Correcet Syntax : OLAPcount.exe <Analysis Server> <Cube
> Name> (e.g)OLAPcount.exe livdwqprj03 AIGTMSReport1")
> Else
> 'connect to the server (Analysis Server name)
> dsoServer.Connect (finalArgs(0))
> 'Examine whether all necessary components are present (Cube
> name)
> If dsoServer.MDStores.Find(finalArgs(1)) = False Then
> GoTo err_no_database
> End If
> 'Connect with the data base (CUBE) (Cube name)
> Set dsoDB = dsoServer.MDStores(finalArgs(1))
> If dsoDB.DataSources.Count = 0 Then
> GoTo err_no_datasource
> ElseIf dsoDB.Dimensions.Count = 0 Then
> GoTo err_no_dimensions
> ElseIf dsoDB.MDStores.Find("MSP_ASSN_FACT") = False Then
> GoTo err_no_fact_cube
> ElseIf dsoDB.MDStores.Find("MSP_PORTFOLIO_ANALYZER") = False
> Then
> GoTo err_no_analyzer
> End If
> 'Set the cube table to use
> Set dsoAssFactCube = dsoDB.MDStores("MSP_ASSN_FACT")
> Set dsoPortAnalyzerCube =
> dsoDB.MDStores("MSP_PORTFOLIO_ANALYZER")
> 'Specify the name of the new measure
> Set dsoMea = dsoAssFactCube.Measures.AddNew("Total
> Assignments")
> 'Specify the source column based on which the operation need to
> be performed
> 'dsoMea.SourceColumn =
> """MSP_CUBE_ASSN_FACT"".""ENT_ASSIGNMENT_CODE6"""
> dsoMea.SourceColumn = """MSP_CUBE_ASSN_FACT"".""PROJ_UID"""
> 'The datatype for the column
> dsoMea.SourceColumnType = ADODB.DataTypeEnum.adDecimal
> 'The method for the column aggSum or aggCount aggregates the
> column by summation or counts.
> dsoMea.AggregateFunction = aggCount
> 'update the cube
> dsoAssFactCube.Update
> dsoAssFactCube.Process
> 'dsoAnalyzerCube represents a virtual Cube. the measure of a
> virtual Cubes has
> 'the characteristics of the measure of the material cubes
> Set dsoMea = dsoPortAnalyzerCube.Measures.AddNew("Total
> Assignments")
> 'The column is indicated in "more normal" form, since the
> measure belongs to the virtual Cube!
> 'dsoMea.SourceColumn = "MSP_ASSN_FACT.FIXED COST"
> dsoMea.SourceColumn = "MSP_ASSN_FACT.Total Assignments"
> dsoPortAnalyzerCube.Update
> dsoPortAnalyzerCube.Process
> dsoDB.Process
> leave_now:
> UserOLAPUpdate = 0
> ' Exit Function
> err_no_database:
> l_errnum = 1
> s_errdesc = "Datenbank konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 1
> ' Exit Function
> err_no_datasource:
> l_errnum = 1
> s_errdesc = "Datenquelle konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 2
> ' Exit Function
> err_no_dimensions:
> l_errnum = 1
> s_errdesc = "Dimensionen konnten nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 3
> ' Exit Function
> err_no_fact_cube:
> l_errnum = 1
> s_errdesc = "Cube MSP_ASSN_FACT konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 4
> ' Exit Function
> err_no_analyzer:
> l_errnum = 1
> s_errdesc = "Cube MSP_PORTFOLIO_ANALYZER konnte nicht gefunden
> werden! "
> UserOLAPUpdate = vbObjectError + 5
> ' Exit Function
> error_handler:
> l_errnum = Err.Number
> s_errdesc = Err.Description
> UserOLAPUpdate = 1 ' although it could be any non-zero value
> ' to indicate an error
> End If
> End Sub
>

Adding new column from inside a store procedure....

My question is...
Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
Using normal code it will be something like this:
ALTER TABLE TableName
ADD ColumnName DataType NULL
But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
ALTER TABLE TableName
ADD 'ColumnName' DataType NULL
The only way you are going to be able to do this is to use execute ( string
). The string can be any sql statement.
So in your stored proc you'd do something like.
declare @.command varchar(8000)
set @.command = 'ALTER TABLE ' + @.table_name + ' ADD ' + @.column_name + ' '
+ @.data_type + ' NULL '
execute ( @.command )
This assumes that @.table_name, @.column_name, @.data_type are coming in
through the stored proc. As you can see this approach allows you to do
customzie the statement to any level you want. I've used the same approach to
write tools to auto generate index and foreignkey creation statements.
Combine this with a loop, and you can scroll through a record set and issues
statements.
One thing to note is that the execute ( ) executes on what seems like a
seperate scope. That is if you delcare a variable in the proc, and then
reference it in the execute statement it will fail. The trick I've found to
get around this is to use temp tables. You can create a really basically
#table to hold your variables, and then grab them as need in the execute
statement.
The 8000 character limit to varchars is also kind of a b. The only way
around this I've found is to concat 8000 varchars together in the execute
statement like
delcare @.cmd1 varchar(8000)
declare @.cmd2 varchar(8000)
.... set em up
execute ( @.cmd1 + @.cmd2 )
Using this approach you can issue really big statements. I've used it to
generate triggers that where larger then 8k.
Also execute can issue more then one statement. So you can have something
like
execute ( 'select junk = 1 select date = getdate()'
and you'll get to result sets.
"Andres Romero" wrote:

> My question is...
> Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
> Using normal code it will be something like this:
> ALTER TABLE TableName
> ADD ColumnName DataType NULL
> But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
> ALTER TABLE TableName
> ADD 'ColumnName' DataType NULL
>
|||> seperate scope. That is if you delcare a variable in the proc, and then
> reference it in the execute statement it will fail. The trick I've found
to
> get around this is to use temp tables.
Or, concatenate? Dynamic SQL doesn't have to know that the value came from
a variable, e.g.
DECLARE @.sql VARCHAR(8000), @.colname VARCHAR(32), @.collength INT
SELECT @.colname = 'newCol1', @.collength = 32
SET @.sql = 'ALTER TABLE table ADD '+@.colname+'
VARCHAR('+RTRIM(@.collength)+')'
PRINT @.sql
http://www.aspfaq.com/
(Reverse address to reply.)

Adding new column from inside a store procedure....

My question is...
Is there any way to add a new column dinamically inside a store procedure wh
it the column name as string?
Using normal code it will be something like this:
ALTER TABLE TableName
ADD ColumnName DataType NULL
But, in my case the ColumnName is unknow until the sp is executed, and I wil
l need something like this;
ALTER TABLE TableName
ADD 'ColumnName' DataType NULLThe only way you are going to be able to do this is to use execute ( string
). The string can be any sql statement.
So in your stored proc you'd do something like.
declare @.command varchar(8000)
set @.command = 'ALTER TABLE ' + @.table_name + ' ADD ' + @.column_name + ' '
+ @.data_type + ' NULL '
execute ( @.command )
This assumes that @.table_name, @.column_name, @.data_type are coming in
through the stored proc. As you can see this approach allows you to do
customzie the statement to any level you want. I've used the same approach t
o
write tools to auto generate index and foreignkey creation statements.
Combine this with a loop, and you can scroll through a record set and issues
statements.
One thing to note is that the execute ( ) executes on what seems like a
seperate scope. That is if you delcare a variable in the proc, and then
reference it in the execute statement it will fail. The trick I've found to
get around this is to use temp tables. You can create a really basically
#table to hold your variables, and then grab them as need in the execute
statement.
The 8000 character limit to varchars is also kind of a b. The only way
around this I've found is to concat 8000 varchars together in the execute
statement like
delcare @.cmd1 varchar(8000)
declare @.cmd2 varchar(8000)
... set em up
execute ( @.cmd1 + @.cmd2 )
Using this approach you can issue really big statements. I've used it to
generate triggers that where larger then 8k.
Also execute can issue more then one statement. So you can have something
like
execute ( 'select junk = 1 select date = getdate()'
and you'll get to result sets.
"Andres Romero" wrote:

> My question is...
> Is there any way to add a new column dinamically inside a store procedure
whit the column name as string?
> Using normal code it will be something like this:
> ALTER TABLE TableName
> ADD ColumnName DataType NULL
> But, in my case the ColumnName is unknow until the sp is executed, and I w
ill need something like this;
> ALTER TABLE TableName
> ADD 'ColumnName' DataType NULL
>|||> seperate scope. That is if you delcare a variable in the proc, and then
> reference it in the execute statement it will fail. The trick I've found
to
> get around this is to use temp tables.
Or, concatenate? Dynamic SQL doesn't have to know that the value came from
a variable, e.g.
DECLARE @.sql VARCHAR(8000), @.colname VARCHAR(32), @.collength INT
SELECT @.colname = 'newCol1', @.collength = 32
SET @.sql = 'ALTER TABLE table ADD '+@.colname+'
VARCHAR('+RTRIM(@.collength)+')'
PRINT @.sql
http://www.aspfaq.com/
(Reverse address to reply.)

Adding new column from inside a store procedure....

My question is...
Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
Using normal code it will be something like this:
ALTER TABLE TableName
ADD ColumnName DataType NULL
But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
ALTER TABLE TableName
ADD 'ColumnName' DataType NULLThe only way you are going to be able to do this is to use execute ( string
). The string can be any sql statement.
So in your stored proc you'd do something like.
declare @.command varchar(8000)
set @.command = 'ALTER TABLE ' + @.table_name + ' ADD ' + @.column_name + ' '
+ @.data_type + ' NULL '
execute ( @.command )
This assumes that @.table_name, @.column_name, @.data_type are coming in
through the stored proc. As you can see this approach allows you to do
customzie the statement to any level you want. I've used the same approach to
write tools to auto generate index and foreignkey creation statements.
Combine this with a loop, and you can scroll through a record set and issues
statements.
One thing to note is that the execute ( ) executes on what seems like a
seperate scope. That is if you delcare a variable in the proc, and then
reference it in the execute statement it will fail. The trick I've found to
get around this is to use temp tables. You can create a really basically
#table to hold your variables, and then grab them as need in the execute
statement.
The 8000 character limit to varchars is also kind of a b. The only way
around this I've found is to concat 8000 varchars together in the execute
statement like
delcare @.cmd1 varchar(8000)
declare @.cmd2 varchar(8000)
... set em up
execute ( @.cmd1 + @.cmd2 )
Using this approach you can issue really big statements. I've used it to
generate triggers that where larger then 8k.
Also execute can issue more then one statement. So you can have something
like
execute ( 'select junk = 1 select date = getdate()'
and you'll get to result sets.
"Andres Romero" wrote:
> My question is...
> Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
> Using normal code it will be something like this:
> ALTER TABLE TableName
> ADD ColumnName DataType NULL
> But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
> ALTER TABLE TableName
> ADD 'ColumnName' DataType NULL
>|||> seperate scope. That is if you delcare a variable in the proc, and then
> reference it in the execute statement it will fail. The trick I've found
to
> get around this is to use temp tables.
Or, concatenate? Dynamic SQL doesn't have to know that the value came from
a variable, e.g.
DECLARE @.sql VARCHAR(8000), @.colname VARCHAR(32), @.collength INT
SELECT @.colname = 'newCol1', @.collength = 32
SET @.sql = 'ALTER TABLE table ADD '+@.colname+'
VARCHAR('+RTRIM(@.collength)+')'
PRINT @.sql
--
http://www.aspfaq.com/
(Reverse address to reply.)

Adding logging to a Data Extension

Hello,
I am attempting to troubleshoot why my custom Data Extension is
failing. To do so, I'd like to add some (log4net) logging to the code
to see what is being accessed and what is going wrong.
Do I need to add special permissions for the extra log4net assembly to
run? The documentation seems very sparse on how to debug these
extensions.
I'm able to attach the VS process and step through the code, but that
isn't really helping - Report Designer only shows a generic "The query
could not be loaded, Verify your connection string" error. My extension
(for testing purposes) isn't making use of either string.A followup question..
If I want to specify appSettings for my Data Processing Extension, what
should the name of the config file be?
I've tried assemblyname.config; but I'm going to guess that isn't
working because the Extension code is being run through another
assembly...

Adding Integers

The code below has this line
SET @.SOGallons = @.ODTGallons

I need it to add the Current value of @.SOGallons to the newly selected value of @.ODTGallons and set that as the new value of @.SOGallons.

I've tried
SET @.SOGallons = @.SOGallons + @.ODTGallons

SET @.SOGalTemp = @.SOGallons
SET @.SOGallons= @.SOGalTemp + @.ODTGallons

Neither Worked

<CODE>
FROM [CSITSS].[dbo].[Orderdt] as ODT LEFT OUTER JOIN [CSITSS].[dbo].[Orddtcom] as OCOM
ON ODT.[Companydiv] = OCOM.[Companydiv] AND ODT.[OrderNumber] = OCOM.[OrderNumber] AND
ODT.[Sequence] = OCOM.[Sequence] WHERE ODT.[Companydiv]= 'GLPC-TRANS' AND ODT.[OrderNumber] = @.OrdNum AND
([LineType] = 'IP' OR [LineType] = 'SO' OR [LineType] = 'DL' OR [LineType] = 'PU')

OPEN TC1

FETCH NEXT FROM TC1 INTO @.LT, @.ODTGallons, @.ODTComm
WHILE @.@.FETCH_STATUS=0
BEGIN
IF @.LT = 'SO'
BEGIN
SET @.SplitTest = 1
SET @.SOGallons = @.ODTGallons
IF @.SOGallons > 0
BEGIN
SET @.SOGalTest = 1
END
ELSE
BEGIN
SET @.SOGalTest = 0
END
IF @.SplitTest <> @.SOGalTest
BEGIN
SET @.SOGalTest = 0
END
END
ELSE
BEGIN
SET @.SOGalTest = 1
END
FETCH NEXT FROM TC1 INTO @.LT, @.ODTGallons, @.ODTComm
END
CLOSE TC1
DEALLOCATE TC1</CODE>LineType Gallons Commodity
DL 4000 #2 ULSD DYED
IP 7000 87 NL / ETH
PU 4000 #2 ULSD DYED
SO 7000 87 NL / ETH

There may be multiple lines of any of the above line types.

IP = Initial Pickup
PU = Additional Pickup
SO = Stop Off
DL = Final Delivery

I need to know if all of the gallons that were picked up where delivered

IP + PU = SO + DL

I'm doing a check on the validity of the commodity type as well but with the forums helps we figured that one out yesterday.|||First, your issue is NULL issue.
You said
I've tried
SET @.SOGallons = @.SOGallons + @.ODTGallons

SET @.SOGalTemp = @.SOGallons
SET @.SOGallons= @.SOGalTemp + @.ODTGallons

But before you use @.SOGallons, you need to initialise it. Otherwise it stays as NULL. And whatever value added to it becomes NULL. This is why it failed. Say, insert this line "SET @.SOGallons = 0" before "SET @.SOGallons = @.SOGallons + @.ODTGallons".

Next, in your WHILE Loop, put "SET @.SplitTest = 1" before the loop started as it is a constant.|||Hey,

Thanks.

I know VB fairly well but am completely new to SQL as of about 3 weeks ago. I always seem to know what I want to do but am continually making small syntax errox that trip me up.