align the parameters in reports

IS it possible to design the Parameters Layout in Reporting Service
reports
miniYou can modify styles in HTMLViewer.css style sheet present in Reporting
Services\ReportServer\Styles folder but you will only affect appearance of
the elements, not the distribution or type of controls you want to show.
If you want to fully customize that layout, you need to use reporting
services API, as I have done. For example, I have used radio buttons to
choose report parameters instead of the default combobox.
Jaime
"mini" wrote:
> IS it possible to design the Parameters Layout in Reporting Service
> reports
> mini
>|||thanks Jaime
can u explain how you use reporting
services API?
Can u reduce the Textbox width displayed in parameter layout?
thanks
mini

Alerts In Ms-sql

Hello Techies!
I am a newbie to MS-SQL. doing MCA final proj ,

Here I want some info about MS-SQL user defined alerts...

Actually I have to design an alert that display message like

"the warranty period has been expired for --- good" based on getdate() and warranty period comparision.

In blank I have to display the ID of product retrieved from table.

I have tried sp_addalert ,but an error is occuring

"Stored procedure 'sp_addalert' not found." Is it reqired to be an administrator to use Stored procedures (i.e. sp_)

how to create user alerts .....

Any help appreciated...

with regards,
Vasavi.

" A good Scientist is a person with original ideas. A good Engineer is a person who makes a design that works with as few original ideas as possible.there are no prima donnas in Engineering"the sp_add_alert stored procedure is located in the msdb database, so you have to make sure that you are connected to the msdb db before executing the procedure.

eg:
use msdb
go
exec sp_add_alert ...|||Originally posted by jora
the sp_add_alert stored procedure is located in the msdb database, so you have to make sure that you are connected to the msdb db before executing the procedure.

eg:
use msdb
go
exec sp_add_alert ...

VASAVI:

Hello Jora,
Thank you for ur help.
So nice of You...
I have used 'Use msdb'
But an error occurred like this
"Error 154 : A USE DATABASE statement is not allowed in a procedure or a tirgger"
Then I tried like this :
EXEC msdb.dbo.myprocedure

where 'myprocedure' is a procedure written by me in msdb ,consisting stmt :
' EXEC sp_addalert @.name='myalert',@.message_id=50010,'@.severity=null'

I have added this 'myalert' from menu Server+Alerts/Operators+add new and I have selected my own database in database option there.

But an error is occuring like this
'Msg 14528, Level 16, State 1
The @.name or @.new_name parameter you have supplied is not unique.'

I have checked the alerts list ,but there it is unique.

And one more thing I have a table like this TabProduct where there are fields startdate,enddate and difference gives warranty period. I have to design an alert to display "Warranty has been expired for -- product",and i have display Productid in blank from tabProduct. Is it posibble to write user alert to check each and every row in tabProd(i.e for each product comparing warranty period with getdate function everyday and display alert when warranty period for that product expires.

Awaiting Help...

Many Regards,
Vasavi.sql

Aggregation utility for AS 2005

I've heard that an aggregation utility for "manual" aggregation design is available after installing SP2 but I did not find it. How can I run it?Hey Maxim,

Download the latest samples for SQLServer 2k5, install them, broswe to the samples directory and you'll find it. (for me it was C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Administrator\AggregationManager).

Chris.
|||Thank you very much!|||Could you please give a short explanation about how this works?|||Check page 117 of the Performance guide.

Aggregation utility for AS 2005

I've heard that an aggregation utility for "manual" aggregation design is available after installing SP2 but I did not find it. How can I run it?Hey Maxim,

Download the latest samples for SQLServer 2k5, install them, broswe to the samples directory and you'll find it. (for me it was C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Administrator\AggregationManager).

Chris.
|||Thank you very much!|||Could you please give a short explanation about how this works?|||Check page 117 of the Performance guide.

Aggregation Design Wizard: functionality questions

Hi,
As an example, I use the Aggregation Design Wizard to specify MOLAP 20% for a partition - this is based on 20 products, 40 sub-products, 100 sub-sub-products. What if my product dimension table increases substantially in size so that I now have 200 products, 400 sub-products and 1000 sub-sub-products - do I need to rerun the aggregation wizard to maintain this "20% performance gain"? Or does MSAS know that I always want 20% performance gain, and design new aggregations when it sees new dimensions data? The main question is: do I have to keep running the aggregation wizard whenever new data arrives to be sure to maintain my desired level of performance increase?

The next question is what setting is recommended for performance gain: why wouldn't I want 100% performance gain? Is this just a matter of disk storage (which I have plenty of), or could too high a performance gain actually mean decreased performance? (then why call it 100% performance gain? :) )

Thanks for your help!
-cf

I can begin from the 2nd question. which solves your problem. Yes, performance costs disk space. So keep it %100 where it is possible.

If you use the Agg Des Wiz it saves the aggregation amounts. When you run the test it tells you how many aggregations it has designed. And these aggregations are saved and applied every time you process the cube or database.

|||"When you run the test it tells you how many aggregations it has designed" - correct, but what if the underlying data has changed? Won't the number of aggregations designed then vary based on data - so I need to run the Aggregation Design Wizard every time I have new data or it will just use the aggregations from the last time I ran the wizard?
Thanks for your help.
|||

Refer to the Books Online --> Index --> Aggregations --> overview.

Aggregation is precalculated structure. If is says "3 aggregations" This doesn't mean it calculated 3 data. It means it calculated 3 fields in a structure. So no matter if you add new data it will again be calculating that data too for the same aggregation as you process it.

Aggregation Design Wizard hangs after Count

After clicking the Count button in this wizard, the counts are entered, but the Next button remains greyed out. This started to happen recently on a small development database. Not sure what development changes caused the wizard to stop working. The wizard still works fine on an AdventureWorks database on the same instance of AS2005.

Any comments/advice appreciated.

Thank you

Aggregation Design Wizard needs to obtain estimated counts of members in a dimensions. For that it will try to connect to relational database and issue query to obtain counts.

If database is not available or it takes long time for it to return counts, you might see delays. You can manually enter EstimatedCount value for your dimension attributes, in this case Aggregation Design Wizard will have information needed to proceed.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.|||

Edward

Thanks for your prompt response. It turns out that it wasn't entering a count for attributes with hierarchies that I had disabled (listed unbolded in the Count dialog with the Partition coulum count cell greyed out). After I overtyped the zero with a number in each of these cases I was able to proceed.

Thank you.

Aggregation design wizard gives zero aggregations

Hi,

I know that there is already a post with this question. but I might have additional info and it might be realted to some other problem of mine.

specs:
ssas 2005 sp1,
backend database connects to a UDB(DB2) set of views,
these UDB views get data from many sybase database tables across different databases.

Cube has one partiton per day for one month
the current week partitions are in days,
and prev week partitions are merged to one single partition.
so as of now, for feb we would have the following partitions:

Feb 22 to 28 > 5 partitions (no weekends, 1 partition per day)
Feb 01 to 21 > 1 partition (merged from other days at the end of the week)

3 dimension> date, customer, product
customer and product having about 25 attributes each.
date is a stereotype time dimension with 2 hierarchies, attribute relationships defined
to form natural hierarchies. multiple pkeys for month, quarter.

the customer dimension has 2 natural hierarchies, one user defined un-natural hierarchy,
about 10 attribute relationships defined.

there are about 65 measures, some are derived as calculations from the DSV itself.

in the cube dimensions, the all hierarchy is set to "default" AggregationUsge
the key attributes, and other frequently used attributes including customer name, customer account no
are set to "unrestricted" AggregationUsage.

I processed the dimensions and one of partition,
dimensions contain about 400,000 and 700,000 rows.
the cube contains about 5,000,000 rows.

When I tried to count this using the "count" feature in agg design wizard ,
it gives a system full error after a long time.
so I did a count(*) from the database and gave appx counts.

when I click on create aggregations, after some time it just gives 0 aggregations.
I have tried 500MB of storage for aggregations, then 30% gain and "until I stop" option.
all give only 0 aggregations.

what could be the problem.
before I had designed attribute relationships,
I had tried this and had got about 9 aggregations,
should I not be gettig more now?

please help me not only to rectify this problem
but also to understand the details

Regards

When you run the agg design wizard, which attributes are bold? It only considers the bold ones.

Do you have any user defined hierarchies (as in multi-level hierarchies you built in the center pane of the dimension designer)? Are they natural hierarchies? (i.e. which the lowest level attribute is related to the next lowest all the way up)

The agg design wizard is simply looking at statistics to determine the most useful aggs. Have you considered looking at usage based optimization to use the query log to determine which aggs would be helpful?

Digest the following links and then post back if you still aren't having any luck:
http://www.sqlskills.com/blogs/liz/2006/07/03/InfluencingAggregationCandidates.aspx
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry

I doubt it will come to it, but the SQL Server Samples that came out with SP2 include a new sample called Aggregation Manager that helps you design your own aggregations. If we can't figure anything else out, that's a last resort.

|||

Hi ,

Elizebeth Vitt's blog helped me,

I was able to get about 8 aggregations with 48% optimization.

the aggregations take about 150MB per partition.

how should I achieve a balance between the two?

my requirement is to have maximum performance, the size of the aggregations do not matter. in this case how should I go about designing the aggregations?

Regards

|||

glad that helped

I can't answer that question other to say that you should just try different aggregation designs and test query performance. Designing too many aggs slows performance during cube processing, and it also might have a negative impact on query performance.

Review the performance guide (see link at top of this forum) for best practices in this area, too.

aggregation design wizard does not design any aggregates

We have designed a fairly big cube - and now we want to add a view aggregates, but the aggregation design wizzard does not built aggregates.

after a view minutes of "Initializing..." the wizard returns with zero aggregates (even if i select 99% performance).

I have already checked some other posts - custom assemblies and so on... but did not find andy hint.

SSAS2005 SP2

Profiler does not show any error...

this may be related to the correct record counts (see post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1329458&SiteID=1)

Thanks for Help, HANNES

Could this be because of the huge amount of attributes in the cube?

- Cube 5 Dimensions

Time: 10 Attributes|||

Its really hard to say exactly why the Aggregation Design Wizard is not building any aggregations. It may be due to the record counts being wrong, the Aggregation Usage property on too many of the attributes being set incorrectly, etc.

I'd suggest you read through the SSAS performance guide as it discusses aggregation design quite extensively and you'll likely have a better idea of what you might need to change in order to get the wizard to consider building aggregations.

HTH,

Dave Fackler

PS. The SSAS performance guide can be found here:

http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc

|||

Hey Hannes,

i've got the same Problem since a few days, but without a Solution. I've also a huge Cube with 30 Dims and 23 MeasureGroup. It works fine so far, until this Problem appear. In my Cube there is a second Time Dim, all MeasureGroups without this Dim in Referenz, the Aggregation Design Wizard works.

If i've found a Solution for that Situation i'll write it down.

Kind Regard

Andy L?wen

|||

Hello!

30 dimensions and 23 measure groups, in one cube, seems to me as a design that you can split up into separate cubes in the same project.

And each attribute that you put into a dimension will increase the size of the cube. Count the members for each individual attribute in each dimension and multply them with all the number of members of each other attribute in the same dimension

I do not think that SSAS2005 is designed for this scenario, with all these dimensions and measure groups in one cube.

It would be interesting to know more about the business case behind this design.

Regards

Thomas Ivarsson

|||

Bug? I don't know, it may see your Dimension and see 150 attributes (is that even right?) and...die? That's huge and aggregation's would not even help at all for performance.

Edit: Second thought, that cube IMO really isn't aggregatable, maybe the wizard agrees with me. I suggest turning those attrributes into measures. Only structures should be in the Dimension.

|||

Thomas Ivarsson wrote:

Hello!

30 dimensions and 23 measure groups, in one cube, seems to me as a design that you can split up into separate cubes in the same project.

I do not think that SSAS2005 is designed for this scenario, with all these dimensions and measure groups in one cube.

It would be interesting to know more about the business case behind this design.

Regards

Thomas Ivarsson

I agree, anymore than 10-15 attributes for a dimension and i'd suggest your cube design is off, let alone 200 attributes. Can you give examples of what attributes you have?

|||

Even when you specify 99% the aggregation design wizard will not design 99% of all possible aggregations, it has some pretty smart (most of the time) algorithms which try to figure out how many aggregations would be "reasonable" for the wizard to consider and then it will design 99% of those. So in your case there must be something in your design that is causing the wizard to conclude that there are no "reasonable" aggregations.

The aggregation design wizard relies heavily on the estimated row counts and on the attribute relationships and other settings in your dimensions. The following are some of the factors that can contribute to aggregations not being designed by the wizard.

lack of or incorrectly defined attribute relationships|||

Hey Thomas,

there are many Dims, MG and so on, that's right. But only a few attributes are Aggregateable.

My Cube has a size of 3GB, and we did not have a Problem with Performance. So that is not the Problem.

My Problem is, that the Aggregation Design Wizard will not work this the last change on the Design of the Cube.

But give me some Hours, i think i'm on the right way.

Kind Regards

Andy L?wen

|||

If only a few attributes are aggregatable, then that means that any aggregations that could be built would be pretty large - they will always have to be at the leaf levels of the non-aggregatable attributes. This in turn means that AS is likely to have decided they are too large to be useful (see the AS Performance Guide, P37, on the 'one-third rule') so it hasn't designed any at all. If you do want to built aggregations then you should either build them manually or deliberately set the counts for your attributes to artificially small values.

HTH,

Chris

|||Andy could you please provide an example of attributes you have? I'm still very curious how it's possible for so many to exist.|||

Hi,

About setting the count to a lower value, dont set it too low, because

Another tip which I got from an microsoft insider is that,

the Aggregtion Wizard does not create aggregations if the count is somewhere near 1024 rows (if I remember correctly).

Regards

|||You're thinking of the fact that AS won't build aggregations or indexes on a partition with fewer than 4096 rows, by default.|||

Hi,

Oh! yes, you are right.

I also believe that this threshold value can be set somewhere in a configuration file.

But then how is the number of rows different from the count?

|||

To answer my question... we have selectivly defined some attributes with aggregation usage "non" (for those attributes which are not propably often used) - while we have set half of the attributes to aggregation usage non - the aggregation wizzard has begun to design aggregates...

Maybe there is a relation to the not correct set estimated rows... - as long as I do not find any code to proper set estimeded rows its impossible for me to say whats the reason... (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1329458&SiteID=1)

Best Regards, HANNES

Aggregation design wizard - A connection cannot be made. Ensure that the server is running ...

In my SSAS SP1, I received an error msg in Aggregation design wizard "A connection cannot be made. Ensure that the server is running ..." while I was tryng to start "Performance gain reaches%" box. I verified that the local source SQL 2005 sample "AdventureWorksDW" is running without a problem.

Make sure your Analysis Server is running.

Aggregation Design Wizard sends commands to Analysis Server that is running aggregation desing algorithm.

Try and open SQL Management Studio and make sure you can connect to Analysis Server.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

While I tried to connect to Analysis Server in SQL Management Studio, I got an error as following -
* I am sure the service of "SQL Server Analysis Services (MSSQLSERVER)" is started on port 2383.

Quotes:

TITLE: Connect to Server

Cannot connect to localhost.

ADDITIONAL INFORMATION:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

DIME protocol error: The required bytes cannot be read from the stream. (Microsoft.AnalysisServices.AdomdClient)

BUTTONS:

OK

|||

Open Service control manager and make sure you've started "SQL Server Analysis Services( MSSQLSERVER)" service. That is in case you installed default instance.

If you installed named instance the service name will contain the instance name.

For troubleshooting connectivity issues you can look at some tips on http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks a lot.
I had carefully read your posted URL, but I had NOT found a way to solve the problem yet.
(1) In Service control manager, SQL Server Analysis Services (MSSQLSERVER) is started.
(2) In DOS command console, C:/>telnet localhost 2383 , output a blank screen, means that - port 2383 is on listening on localhost.
so that, I am SURE that
- SQL Server Analysis Services (MSSQLSERVER)" is started and listening on port 2383 -
but when I try to connect to Analysis Services in Microsoft SQL Server Management Studio , the error popup:

Frank X. Huang wrote:

TITLE: Connect to Server

Cannot connect to localhost.

ADDITIONAL INFORMATION:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

DIME protocol error: The required bytes cannot be read from the stream. (Microsoft.AnalysisServices.AdomdClient)

BUTTONS:

OK

|||

Let's try another thing.

On your server machine:

Create a new text file in one of your folders.

Change the extension of the file from .txt to .udl. Doble click on the file and you'd see data link dialog open up.

Switch to the Provider tab and select "Microsoft OLEDB provider for Analysis Services 90"

Go to the Connection tab and type in the server name for the Data Source. Choose Windows auth.

At this moment you should be able to see list of databases on the server.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I do like this -

(1) on my server machine(localhost): create a new text file and rename to x.udl;
(2) Double click on the file, and In the popup window which titled "Data Link Properties",
(2.1) In "Provider" tab, I select "Microsoft OLE DB Provider for Analysis Services 9.0",
(2.2) In "Connection" tab, I select "Use Windows NT Integrated security"; Fill "Data Source:" textbox with "localhost"; let "Location:" textbox empty; and then
(2.2.1) if I drop down the list of "Enter the initial catalog to use:", there is an error "An error was encountered in the transport layer." with title "Microsoft Data Link Error".

(2.2.1.1) I have no choice but to click "OK" on the error window, and then another window shows "Login failed. Catalog information cannot be retrieved."
(2.2.2) or, if I click "Test Connection" button, an error show "Test connection failed because of an error in initializing provider. An error was encountered in the transport layer."

Further more, I try some other things -
(1) create a data source with "SQL Native Client"(version 2005.90.2047.00), and then retry all steps.
(2) at step 2.1, I select another Provider "SQL Native Client"(version 2005.90.2047.00); at step 2.2 fill the data source with "localhost"; and then "Test Connection"......It's OK!......or, drop down the database name list, It's OK!.......no error!

By the way, "Database Engine", "Integration Services", and "Reporting Services" are ALL can be connected, ONLY "Analysis Services" CANNOT be connected in the "Microsoft SQL Server Management Studio".

|||(1) I removed and re-installed Microsoft SQL Server 2005 Developer Edition on my Windows XP Professional, yet the error remains.
(2) I, then, downloaded and installed Microsoft SQL Server 2005 SP1, and try again, the error remains.
(3) I also remove and reinstall my Network Interface Card on my IBM ThinkPad, and start all services who can be started on my Operating System, the error remains.
Who can help me, God?|||

Another idea.

This could be a network related. Instead of localhost try to use machine name or IP of your server.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks God! I fixed this problem, but I really do not know how and why?

I will do some experiment and try to find it out (this may take me several hours or days or never), but now, I only list what I did -

(1) There is an error "Could not add the requested component.
The error is: ...."(sorry for lost details) when I try to install Microsoft TCP/IP version 6 on my Network Adapter;

(2) There is an error "Unable to start the MSMQ service. Error Code: 0x433
Error description: The dependency service does not exist or has been marked for deletion. "
and/or "Setup could not copy one or more files. The specific error code is 0x4b8..."
and/or "The MSMQTriggers service cannot be started. Error Code: 0x42C Error Description: The dependency service or group failed to start."
When I removed and try to re-install Message Queuing;

(3.1) To resolve 0x4b8, I running this command:
esentutl /p c:\WINDOWS\security\Database\secedit.sdb

(3.2) To resolve 0x42C, I found a very useful message on Etc. Windows.
http://www.google.com/notebook/public/04097591150171922613/BDT6NIgoQlMDj180h
and running these commands:

net stop msdtc

msdtc -uninstall

go into regedit and delete the key HKLM\Software\Microsoft\MSDTC

msdtc -install

net start msdtc

(3.3) Now, I can continue to re-install Message Queuing.

(4) Check and install Microsoft TCP/IP version 6.

(5) Connect to localhost (Analysis Server) in Microsoft SQL Server Management Studio. It is OK now!

I really don't know how to fixed but I finally fiexed it anyhow! If you doubt whether it meets your case or not, please just take a trial; If you want to contact me, please email to huangxiangyao@.hotmail.com.

|||

Thanks God! I fixed this problem, but I really do not know how and why.

I will do some experiment and try to find it out (this may take me several hours or days or never), but now, I only list what I did -

(1) There is an error "Could not add the requested component.
The error is: ...."(sorry for lost details) when I try to install Microsoft TCP/IP version 6 on my Network Adapter;

(2) There is an error "Unable to start the MSMQ service. Error Code: 0x433
Error description: The dependency service does not exist or has been marked for deletion. "
and/or "Setup could not copy one or more files. The specific error code is 0x4b8..."
and/or "The MSMQTriggers service cannot be started. Error Code: 0x42C Error Description: The dependency service or group failed to start."
When I removed and try to re-install Message Queuing;

(3.1) To resolve 0x4b8, I running this command:
esentutl /p c:\WINDOWS\security\Database\secedit.sdb

(3.2) To resolve 0x42C, I found a very useful message on Etc. Windows.
http://www.google.com/notebook/public/04097591150171922613/BDT6NIgoQlMDj180h
and running these commands:

net stop msdtc

msdtc -uninstall

go into regedit and delete the key HKLM\Software\Microsoft\MSDTC

msdtc -install

net start msdtc

(3.3) Now, I can continue to re-install Message Queuing.

(4) Check and install Microsoft TCP/IP version 6.

(5) Connect to localhost (Analysis Server) in Microsoft SQL Server Management Studio. It is OK now!

I really don't know how to fixed but I finally fiexed it anyhow! If you doubt whether it meets your case or not, please just take a trial; If you want to contact me, please email to huangxiangyao@.hotmail.com. (From 1:00 to 17:00 UTC)

Aggregation design wizard - A connection cannot be made. Ensure that the server is running .

In my SSAS SP1, I received an error msg in Aggregation design wizard "A connection cannot be made. Ensure that the server is running ..." while I was tryng to start "Performance gain reaches%" box. I verified that the local source SQL 2005 sample "AdventureWorksDW" is running without a problem.

Make sure your Analysis Server is running.

Aggregation Design Wizard sends commands to Analysis Server that is running aggregation desing algorithm.

Try and open SQL Management Studio and make sure you can connect to Analysis Server.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

While I tried to connect to Analysis Server in SQL Management Studio, I got an error as following -
* I am sure the service of "SQL Server Analysis Services (MSSQLSERVER)" is started on port 2383.

Quotes:

TITLE: Connect to Server

Cannot connect to localhost.

ADDITIONAL INFORMATION:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

DIME protocol error: The required bytes cannot be read from the stream. (Microsoft.AnalysisServices.AdomdClient)

BUTTONS:

OK

|||

Open Service control manager and make sure you've started "SQL Server Analysis Services( MSSQLSERVER)" service. That is in case you installed default instance.

If you installed named instance the service name will contain the instance name.

For troubleshooting connectivity issues you can look at some tips on http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks a lot.
I had carefully read your posted URL, but I had NOT found a way to solve the problem yet.
(1) In Service control manager, SQL Server Analysis Services (MSSQLSERVER) is started.
(2) In DOS command console, C:/>telnet localhost 2383 , output a blank screen, means that - port 2383 is on listening on localhost.
so that, I am SURE that
- SQL Server Analysis Services (MSSQLSERVER)" is started and listening on port 2383 -
but when I try to connect to Analysis Services in Microsoft SQL Server Management Studio , the error popup:

Frank X. Huang wrote:

TITLE: Connect to Server

Cannot connect to localhost.

ADDITIONAL INFORMATION:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

DIME protocol error: The required bytes cannot be read from the stream. (Microsoft.AnalysisServices.AdomdClient)

BUTTONS:

OK

|||

Let's try another thing.

On your server machine:

Create a new text file in one of your folders.

Change the extension of the file from .txt to .udl. Doble click on the file and you'd see data link dialog open up.

Switch to the Provider tab and select "Microsoft OLEDB provider for Analysis Services 90"

Go to the Connection tab and type in the server name for the Data Source. Choose Windows auth.

At this moment you should be able to see list of databases on the server.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I do like this -

(1) on my server machine(localhost): create a new text file and rename to x.udl;
(2) Double click on the file, and In the popup window which titled "Data Link Properties",
(2.1) In "Provider" tab, I select "Microsoft OLE DB Provider for Analysis Services 9.0",
(2.2) In "Connection" tab, I select "Use Windows NT Integrated security"; Fill "Data Source:" textbox with "localhost"; let "Location:" textbox empty; and then
(2.2.1) if I drop down the list of "Enter the initial catalog to use:", there is an error "An error was encountered in the transport layer." with title "Microsoft Data Link Error".

(2.2.1.1) I have no choice but to click "OK" on the error window, and then another window shows "Login failed. Catalog information cannot be retrieved."
(2.2.2) or, if I click "Test Connection" button, an error show "Test connection failed because of an error in initializing provider. An error was encountered in the transport layer."

Further more, I try some other things -
(1) create a data source with "SQL Native Client"(version 2005.90.2047.00), and then retry all steps.
(2) at step 2.1, I select another Provider "SQL Native Client"(version 2005.90.2047.00); at step 2.2 fill the data source with "localhost"; and then "Test Connection"......It's OK!......or, drop down the database name list, It's OK!.......no error!

By the way, "Database Engine", "Integration Services", and "Reporting Services" are ALL can be connected, ONLY "Analysis Services" CANNOT be connected in the "Microsoft SQL Server Management Studio".

|||(1) I removed and re-installed Microsoft SQL Server 2005 Developer Edition on my Windows XP Professional, yet the error remains.
(2) I, then, downloaded and installed Microsoft SQL Server 2005 SP1, and try again, the error remains.
(3) I also remove and reinstall my Network Interface Card on my IBM ThinkPad, and start all services who can be started on my Operating System, the error remains.
Who can help me, God?|||

Another idea.

This could be a network related. Instead of localhost try to use machine name or IP of your server.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks God! I fixed this problem, but I really do not know how and why?

I will do some experiment and try to find it out (this may take me several hours or days or never), but now, I only list what I did -

(1) There is an error "Could not add the requested component.
The error is: ...."(sorry for lost details) when I try to install Microsoft TCP/IP version 6 on my Network Adapter;

(2) There is an error "Unable to start the MSMQ service. Error Code: 0x433
Error description: The dependency service does not exist or has been marked for deletion. "
and/or "Setup could not copy one or more files. The specific error code is 0x4b8..."
and/or "The MSMQTriggers service cannot be started. Error Code: 0x42C Error Description: The dependency service or group failed to start."
When I removed and try to re-install Message Queuing;

(3.1) To resolve 0x4b8, I running this command:
esentutl /p c:\WINDOWS\security\Database\secedit.sdb

(3.2) To resolve 0x42C, I found a very useful message on Etc. Windows.
http://www.google.com/notebook/public/04097591150171922613/BDT6NIgoQlMDj180h
and running these commands:

net stop msdtc

msdtc -uninstall

go into regedit and delete the key HKLM\Software\Microsoft\MSDTC

msdtc -install

net start msdtc

(3.3) Now, I can continue to re-install Message Queuing.

(4) Check and install Microsoft TCP/IP version 6.

(5) Connect to localhost (Analysis Server) in Microsoft SQL Server Management Studio. It is OK now!

I really don't know how to fixed but I finally fiexed it anyhow! If you doubt whether it meets your case or not, please just take a trial; If you want to contact me, please email to huangxiangyao@.hotmail.com.

|||

Thanks God! I fixed this problem, but I really do not know how and why.

I will do some experiment and try to find it out (this may take me several hours or days or never), but now, I only list what I did -

(1) There is an error "Could not add the requested component.
The error is: ...."(sorry for lost details) when I try to install Microsoft TCP/IP version 6 on my Network Adapter;

(2) There is an error "Unable to start the MSMQ service. Error Code: 0x433
Error description: The dependency service does not exist or has been marked for deletion. "
and/or "Setup could not copy one or more files. The specific error code is 0x4b8..."
and/or "The MSMQTriggers service cannot be started. Error Code: 0x42C Error Description: The dependency service or group failed to start."
When I removed and try to re-install Message Queuing;

(3.1) To resolve 0x4b8, I running this command:
esentutl /p c:\WINDOWS\security\Database\secedit.sdb

(3.2) To resolve 0x42C, I found a very useful message on Etc. Windows.
http://www.google.com/notebook/public/04097591150171922613/BDT6NIgoQlMDj180h
and running these commands:

net stop msdtc

msdtc -uninstall

go into regedit and delete the key HKLM\Software\Microsoft\MSDTC

msdtc -install

net start msdtc

(3.3) Now, I can continue to re-install Message Queuing.

(4) Check and install Microsoft TCP/IP version 6.

(5) Connect to localhost (Analysis Server) in Microsoft SQL Server Management Studio. It is OK now!

I really don't know how to fixed but I finally fiexed it anyhow! If you doubt whether it meets your case or not, please just take a trial; If you want to contact me, please email to huangxiangyao@.hotmail.com. (From 1:00 to 17:00 UTC)

Advice on table design which will allow me to enforce integrity

Hi,
I have two tables Table A and B, below with some dummy data...

Table A (contains specific unique settings that can be requested)

Id, SettingName
1, weight
2, length

Table B (contains the setting values, here 3 values relate to weight
and 1 to length)

Id, Brand, SettingValue
1, A, 100
1, B, 200
1, null, 300
2, null, 5.3

(There is also a list of Brands available in another table). No primary
keys / referential integrity has been setup yet.

Basically depending upon the Brand requested a different setting value
will be present. If a particular brand is not present (signified by a
null in the Brand column in table B), then a default value will be
used.
Therefore if I request the weight and pass through a Brand of A, I will
get 100
If I request the weight but do not pass through a brand (i.e. null) I
will get 300.

My question is, what kind of integrity can I apply to avoid the user
specifying duplicate Ids and Brands in table B. I cannot apply a
composite key on these two fields as a null is present. Table B will
probably contain about 50 rows and probably 10 of them will be brand
specific. The reason its done like this is in the calling client code I
want to call some function e.g.
getsetting(weight) ... result = 300
Or if it is brand specific
getsetting(weight,A) .... result = 100

Any advice on integrity or table restructuring would be greatly
appreciated. Its sql 2000 sp3.
Thanks
bradAs you have already realized, TableB isn't even in First Normal Form.
Attribute Value models like this are seldom viable and you haven't
stated any excuse not to use the standard, normalized approach:

CREATE TABLE TableB (id INTEGER NOT NULL, brand CHAR(1) DEFAULT 'Z' NOT
NULL REFERENCES Brands (brand), weight INTEGER NOT NULL, length INTEGER
NOT NULL, PRIMARY KEY (id,brand))

Either add the default ("Z" here) brand to the brands table, or if you
prefer not to do that, put the brand-specific info into a separate
table:

CREATE TABLE TableB (id INTEGER NOT NULL, default_weight INTEGER NOT
NULL, default_length INTEGER NOT NULL, PRIMARY KEY (id))

CREATE TABLE TableBBrands (id INTEGER NOT NULL REFERENCES TableB (id),
brand CHAR(1) NOT NULL REFERENCES Brands (brand), weight INTEGER NOT
NULL, length INTEGER NOT NULL, PRIMARY KEY (id,brand))

Personally, I would lean toward the former design. This meets all the
requirements that you have specified as far as I can see.

--
David Portas
SQL Server MVP
--|||i would go with two tables B1(id ,brand , value) with PK (id,brand) and
B2(id,value) PK (id)
but i think that more appropriate solution is to have separate table
for each "setting type" e.g.
WEIGHTS, LENGTHS, VOLUMES where you can have appropriate data type for
each setting

HTH, Strider|||> i think that more appropriate solution is to have separate table
> for each "setting type" e.g.
> WEIGHTS, LENGTHS, VOLUMES

Do you mean separate COLUMNS? Why would you create separate tables for
each attribute?

--
David Portas
SQL Server MVP
--|||I should have also said that Table A will grow in size over time (i.e.
60 different settings), so you can have many settings, i.e. a user
could add additional settings in here, therefore making these as
columns would not be ideal. If possible I would also prefer to not have
to have a dummy brand.

David Portas wrote:
> > i think that more appropriate solution is to have separate table
> > for each "setting type" e.g.
> > WEIGHTS, LENGTHS, VOLUMES
> Do you mean separate COLUMNS? Why would you create separate tables
for
> each attribute?
> --
> David Portas
> SQL Server MVP
> --|||>Why would you create separate tables for
>each attribute?

i don't know, perhaps transient brain disorder ;)|||Why can't you implement the necessary change control procedures to add
columns as the users require them? Allowing users the unfettered
ability to add new "attributes" to a system is a recipe for creating an
unusable mass of redundant and inconsistent data. Do you really expect
users to go through the process of identifying and eliminating
functional dependencies before they add new attributes? On the whole,
users aren't good database architects. At least if they were they would
work with proper tables and columns, constraints and keys - things
which aren't possible under your model.

Your question was how to enforce integrity but I don't see any
integrity in your design at all.

--
David Portas
SQL Server MVP
--|||Yup, I agree all valid points there David. Thanks for your input on
this one.
Brad

David Portas wrote:
> Why can't you implement the necessary change control procedures to
add
> columns as the users require them? Allowing users the unfettered
> ability to add new "attributes" to a system is a recipe for creating
an
> unusable mass of redundant and inconsistent data. Do you really
expect
> users to go through the process of identifying and eliminating
> functional dependencies before they add new attributes? On the whole,
> users aren't good database architects. At least if they were they
would
> work with proper tables and columns, constraints and keys - things
> which aren't possible under your model.
> Your question was how to enforce integrity but I don't see any
> integrity in your design at all.
> --
> David Portas
> SQL Server MVP
> --|||1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

2) Google EAV and attribute splitting" design flaws before you destroy
your database. It is virtually impossible to have any data integrity
with this "design" becuase of the need to write a complete RDBMS engine
in SQL constraints.

3) The thing falls apart in about one year of operation. That is based
on fixing these things as a consultant.

4) You need help from someone who knows even a little bit about data
modeling. You have not even gottent o the basic normal forms yet.|||[posted and mailed, please reply in news]

(obhayes@.hotmail.com) writes:
> My question is, what kind of integrity can I apply to avoid the user
> specifying duplicate Ids and Brands in table B. I cannot apply a
> composite key on these two fields as a null is present. Table B will
> probably contain about 50 rows and probably 10 of them will be brand
> specific. The reason its done like this is in the calling client code I
> want to call some function e.g.
> getsetting(weight) ... result = 300
> Or if it is brand specific
> getsetting(weight,A) .... result = 100

I would add a dummy primary key to TableB, possibly an IDENTITY column.
When I would add a UNIQUE constraint on (Id, Brand). A UNIQUE constraint
does permit for NULL values, but only one NULL.

(It's a bit amazing that a thread with so many replies did not include
this simple and straigtforward suggestion.)

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

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