"dangerous MSI" Microsoft SQL Server 2000 DTS Designer Components

Microsoft SQL Server 2000 DTS Designer Components has cause a total destruction of my Enterprise Manager.

In my workstation I've got Sql Server 2000 client and Sql Server 2005 client. Everything goes fine up to here.

But when I've installed that MSI I have not been able to open my Enterprise Manager.

From MMC appears: Error initializing component CLSID: {xxx.xxx}

My goal was be able to open 2000 stuff from Sql Management Studio. By the moment, I'm forget of that.

Does anyone have ever faced this issue?

QA is working.

Thanks in advance for any suggestion

is this an issue? I'm not worried about -I have multiple TS-. But I don't know if it's strongly related with my own workstation settings or is some more general.

My idea was to allow anyone use Sql Management Studio for Sql25k stuff and Sql2k stuff to the full (it included dts 2000) and so and cutting out time.

|||hey guys any ideas?|||

I know, such a pain, day in day out but I can't believe that anyone has ever experimented...

|||Install sp4 on the client. That fixed the problem for me.|||SP4 for Sql Server 2000, you mean?|||First, if you had 2000 client tools and 2005 client tools installed you did not need to install the "DTS designer tools". First thing to do is uninstall it. You only need to install that package if you DON'T have 2000 client tools.

Then, figure out why you are getting the error "You need DTS Designer Tools" when you try to open DTS in 2005.

I found I had SQL 7, 2000 and 2005 client tools installed. If I uninstalled the 7 client tools, the DTS started working.

Also, make sure you have SP4 of 2000 installed on your machine.

"Cross Mirroring" - any good?

Hi

I’m about to move all our databases from an old lousy server to a brand new HP setup.
I have 8 databases (total of approx 100Gb) and I get 2 BL25p servers and SAN access for them.

Now, I want high availability and I like the idea of mirroring the databases for achieving that. On the other hand I don’t like the idea of the mirror server doing almost nothing while the principal gets the entire load – seems like a waste of good resources.

But is this really the situation or mirror server also be loaded, just for being the mirror?

Now, if the main load is on the principal would I then benefit from distributing the databases on both servers, so both servers would act like principal and mirror?

What I mean is that server 1 could be principal for DB 1-4 while server 2 would mirror these DB’s and likewise serever 2 would be principal for DB 5-8 and server 1 would mirror them.

Does it make sense? Would I benefit anything from this?

Thx a lot
Nicolaj

In my experience i don't see any value to perform what you've referred, it is an opposite way of working in database mirroring. For the topic as mirror stays in loading state the application cannot be connected to perform the changes.

You might think the root of mirroring and clustering in this case, which I believe another complicated scenario to setup and manage, because you cannot guarantee that mirroring will fail over after a cluster failover. .

|||

Hi Satya

I migt not have explained my idea very well :-)

consider a setup with 2 databases. In order to split the load of these DB's they are installed on 2 different servers: DB1 on server1 and DB2 on server2.

Now, in order to get failover DB1 is mirrored to Server2 and DB2 is mirrored to Server1. This way the load is distributed on 2 servers and the failover is ensured through mirroring.... i guess... (of course both servers should be able to handle the load of all DB running on them in case of a breakdown)

But is this really the case, or will a server be too loaded just for beeing a mirror server?

/Nicolaj

|||Yes, you can do that to get such option but I haven't come across such setup.|||

Hi Nicolaj,

We are currently considering "cross mirroring" as you have described it - two database servers wit a total of 10-15 databases. I was curious if you have made it and do you have some positive exprerience? :-)

Thank you very much,

Regards,

Zdravko

|||

Hi Zdravko

We are still some weeks from the actual implementation , so I can't tell you about experiences yet. Sorry about that

/Nicolaj

|||See this kBA http://support.microsoft.com/kb/926150 for the subject of cross mirroring or transactions basis.

"Cross Mirroring" - any good?

Hi

I’m about to move all our databases from an old lousy server to a brand new HP setup.
I have 8 databases (total of approx 100Gb) and I get 2 BL25p servers and SAN access for them.

Now, I want high availability and I like the idea of mirroring the databases for achieving that. On the other hand I don’t like the idea of the mirror server doing almost nothing while the principal gets the entire load – seems like a waste of good resources.

But is this really the situation or mirror server also be loaded, just for being the mirror?

Now, if the main load is on the principal would I then benefit from distributing the databases on both servers, so both servers would act like principal and mirror?

What I mean is that server 1 could be principal for DB 1-4 while server 2 would mirror these DB’s and likewise serever 2 would be principal for DB 5-8 and server 1 would mirror them.

Does it make sense? Would I benefit anything from this?

Thx a lot
Nicolaj

In my experience i don't see any value to perform what you've referred, it is an opposite way of working in database mirroring. For the topic as mirror stays in loading state the application cannot be connected to perform the changes.

You might think the root of mirroring and clustering in this case, which I believe another complicated scenario to setup and manage, because you cannot guarantee that mirroring will fail over after a cluster failover. .

|||

Hi Satya

I migt not have explained my idea very well :-)

consider a setup with 2 databases. In order to split the load of these DB's they are installed on 2 different servers: DB1 on server1 and DB2 on server2.

Now, in order to get failover DB1 is mirrored to Server2 and DB2 is mirrored to Server1. This way the load is distributed on 2 servers and the failover is ensured through mirroring.... i guess... (of course both servers should be able to handle the load of all DB running on them in case of a breakdown)

But is this really the case, or will a server be too loaded just for beeing a mirror server?

/Nicolaj

|||Yes, you can do that to get such option but I haven't come across such setup.|||

Hi Nicolaj,

We are currently considering "cross mirroring" as you have described it - two database servers wit a total of 10-15 databases. I was curious if you have made it and do you have some positive exprerience? :-)

Thank you very much,

Regards,

Zdravko

|||

Hi Zdravko

We are still some weeks from the actual implementation , so I can't tell you about experiences yet. Sorry about that

/Nicolaj

|||See this kBA http://support.microsoft.com/kb/926150 for the subject of cross mirroring or transactions basis.

% of Total

I have a matrix report built on a database not on the cube.

2006

Q1

Volume MS %

Sub Channel Product

X X1 10

X2 20

Subtotal 30

Y Y1 20

Y2 20

Subtotal 40

I need to calculate the Market Share values which are Volume of the product / Subtotal for example

MS% of Product X1 = Volume of X1 / Subtotal i.e 10/ 30 * 100 = 33.33 %

How can i achieve this within the report..Can somebody help/advise...

Why dont you make 1 more column in your query for the subtotal

it will be like

select subchannel, product, year, quarter, volume,
( select sum(volume)
from table t2
where t2.subchannel=t1.subchannel and t2.year=t1.year and t2.quarter=t1.quarter
group by t2.subchannel, t2.year, t2.quarter
) as subtotal
from table t1

After getting this column, you can use the inscope function for changing the field in the details column in the matrix.

|||

Can you please elaborate how to use the InScope on the details column, please

|||

hi,

i guess you have 2 groups in your table, 1 for subchannel and 1 for product

where subchannel has a footer where the subtotal is calculated

just name the field where "30" stand SubTotal

and the field where "10" stands Volume

and add a new field next to the "10"-field with the forumlar =ReportItems!Volume.value/ReportItems!SubTotal.value

should work, otherwise calculate it in your query in a new column:

SELECT subchannel, product, volume,

(SELECT Sum(Volume)

FROM TableXY

WHERE SubChannel=A.SubChannel) AS SubTotal

FROM TableXY AS A

i would solve it with the query ...

greets

gerhard

|||

Now you have the field subtotal in your dataset.

lets say the group name for the group year is "group_year".

Then instead of using the expression Fields!volume in the details column, use this expression

Inscope("group_year",Fields!volume/Fields!subtotal,Fields!volume)

% of Total

I have a matrix report built on a database not on the cube.

2006

Q1

Volume MS %

Sub Channel Product

X X1 10

X2 20

Subtotal 30

Y Y1 20

Y2 20

Subtotal 40

I need to calculate the Market Share values which are Volume of the product / Subtotal for example

MS% of Product X1 = Volume of X1 / Subtotal i.e 10/ 30 * 100 = 33.33 %

How can i achieve this within the report..Can somebody help/advise...

Why dont you make 1 more column in your query for the subtotal

it will be like

select subchannel, product, year, quarter, volume,
( select sum(volume)
from table t2
where t2.subchannel=t1.subchannel and t2.year=t1.year and t2.quarter=t1.quarter
group by t2.subchannel, t2.year, t2.quarter
) as subtotal
from table t1

After getting this column, you can use the inscope function for changing the field in the details column in the matrix.

|||

Can you please elaborate how to use the InScope on the details column, please

|||

hi,

i guess you have 2 groups in your table, 1 for subchannel and 1 for product

where subchannel has a footer where the subtotal is calculated

just name the field where "30" stand SubTotal

and the field where "10" stands Volume

and add a new field next to the "10"-field with the forumlar =ReportItems!Volume.value/ReportItems!SubTotal.value

should work, otherwise calculate it in your query in a new column:

SELECT subchannel, product, volume,

(SELECT Sum(Volume)

FROM TableXY

WHERE SubChannel=A.SubChannel) AS SubTotal

FROM TableXY AS A

i would solve it with the query ...

greets

gerhard

|||

Now you have the field subtotal in your dataset.

lets say the group name for the group year is "group_year".

Then instead of using the expression Fields!volume in the details column, use this expression

Inscope("group_year",Fields!volume/Fields!subtotal,Fields!volume)

% of Total

I have a matrix report built on a database not on the cube.

2006

Q1

Volume MS %

Sub Channel Product

X X1 10

X2 20

Subtotal 30

Y Y1 20

Y2 20

Subtotal 40

I need to calculate the Market Share values which are Volume of the product / Subtotal for example

MS% of Product X1 = Volume of X1 / Subtotal i.e 10/ 30 * 100 = 33.33 %

How can i achieve this within the report..Can somebody help/advise...

Why dont you make 1 more column in your query for the subtotal

it will be like

select subchannel, product, year, quarter, volume,
( select sum(volume)
from table t2
where t2.subchannel=t1.subchannel and t2.year=t1.year and t2.quarter=t1.quarter
group by t2.subchannel, t2.year, t2.quarter
) as subtotal
from table t1

After getting this column, you can use the inscope function for changing the field in the details column in the matrix.

|||

Can you please elaborate how to use the InScope on the details column, please

|||

hi,

i guess you have 2 groups in your table, 1 for subchannel and 1 for product

where subchannel has a footer where the subtotal is calculated

just name the field where "30" stand SubTotal

and the field where "10" stands Volume

and add a new field next to the "10"-field with the forumlar =ReportItems!Volume.value/ReportItems!SubTotal.value

should work, otherwise calculate it in your query in a new column:

SELECT subchannel, product, volume,

(SELECT Sum(Volume)

FROM TableXY

WHERE SubChannel=A.SubChannel) AS SubTotal

FROM TableXY AS A

i would solve it with the query ...

greets

gerhard

|||

Now you have the field subtotal in your dataset.

lets say the group name for the group year is "group_year".

Then instead of using the expression Fields!volume in the details column, use this expression

Inscope("group_year",Fields!volume/Fields!subtotal,Fields!volume)

% of Report Total

I want to show the line by line % of a Report total in the detail section of
a report e.g.
$25.00 - 25%
$50.00 - 50%
$25.00 - 25%
Report total $100.00 - 100%
What is the best way to do this?Take a look at the runningvalue function. It will give you the demoninator
of your fraction...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Costas" <aexos@.mail.com> wrote in message
news:eYHXEX4eEHA.3428@.TK2MSFTNGP11.phx.gbl...
> I want to show the line by line % of a Report total in the detail section
of
> a report e.g.
> $25.00 - 25%
> $50.00 - 50%
> $25.00 - 25%
> Report total $100.00 - 100%
> What is the best way to do this?
>

#value in Cube Calculation

Hello all

GIven the following calculation

([Measures].[12 Month Total Sales],[Sales History].[Sales YM].[Year].lag(1))

were 12 Month Total Sales is a measure of Sale History why do I get #value for this calculation ?

Thanks for your assistance

My guess is that [Sales History].[Sales YM].[Year] is a level, and lag() only works on a member. Does this do what you want?

([Measures].[12 Month Total Sales],[Sales History].[Sales YM].currentmember.lag(1))

Chris

|||thank you

#Error in total fields

I am getting a #error in my subtotal and total fields,
I have data in a field (on a table) as follows which displays 0.00 if there is a zero in the divide by field, otherwise it performs the divide and gives me a percentage difference from the two fields.
This works fine

=iif(Fields!REPCYINC.Value=0,0,(Fields!REPCYINC.Value) / iif(Fields!REPCYPROD.Value=0,1,Fields!REPCYPROD.Value)) * 100

Yet when i try to get this in a total field by doing an AVG it fails, and gives #error, please help, no matter how many iifs a wrap aroun dit it doesnt help.

This is the statement that #error's

=round(avg(iif(Fields!REPCYINC.Value=0,0,(Fields!REPCYINC.Value) / iif(Fields!REPCYPROD.Value=0,1,Fields!REPCYPROD.Value)) * 100) ,2)

Brian Weckler's blog has a posting related to this.

http://blogs.msdn.com/bwelcker/archive/2006/09/26/End-of-Amnesia-_2800_Avoiding-Divide-By-Zero-Errors_2900_.aspx

cheers,

Andrew

|||

Andy,

I had a similar issue recently when I used this statement:

Format(Sum(IIf(Fields!Product.Value="HEALTH",Fields!ID.Value,0)),"C")

Report Service kept giving me the #error, I located the detail error message and then I change my statement to this

Format(Sum(IIf(Fields!Product.Value="HEALTH",Fields!ID.Value,nothing)),"C") and everything worked okay. I remember the error message about mixing different types or something.

I hope this works for you.

Ham

|||

Unfortunately this post just explains what i have already posted, the issue is not the divide by zero, I have iif's to work around that and my inline divide give 0 result not #error,

I experience the #error in the group totals when i sum (As described in my original post)

If the main iif works and gives 0 then why not the sum?

These things should be straightforward!!

|||

That doesnt help, the inline iif *, 0, 0 works fine, I did try changing it to nothing although i still get the same issue, the group totals when i do a sum doesnt work, it #error's!!!

|||

sorted, regardless of how many iif's you throw around the numbers within the SSRS you MUST format the field as number otherwise #error occurs, seems like Microsft still have some work to do here.....

At LEAST make some kind of error handling reporting so we know where the error is coming from, it could be anything....

## How to acquire the SQL Server 2000 Performance data? ##

I want to write a application monitering program to collect the SQL
Server 2000 performance data,
such as pages/sec, bytes total/sec, etc, BUT I don't know how to do it
, In Oracle , there are the v$ views and DBA view , which I can find
the information I interested, the question is , is there a similar suit
of view in SQL Server 2000 to provide the performance information ?
Thank you very much, I will be mad by this question, for I have googled
all the day , but in vain(wangzhi0417@.gmail.com) writes:
> I want to write a application monitering program to collect the SQL
> Server 2000 performance data,
> such as pages/sec, bytes total/sec, etc, BUT I don't know how to do it
> , In Oracle , there are the v$ views and DBA view , which I can find
> the information I interested, the question is , is there a similar suit
> of view in SQL Server 2000 to provide the performance information ?
> Thank you very much, I will be mad by this question, for I have googled
> all the day , but in vain

Look at the table sysperfinfo.

In SQL 2005 this changes drastically. SQL 2005 exposes a lot more
information of this kind.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx