Saturday, April 2, 2022

Change Linked Measure Groups - blow away partitions in base cube?

Change Linked Measure Groups - blow away partitions in base cube?

If I add or remove a linked measure group in a cube, and then deploy the cube, my original base cube partitions get deleted.

Is this a known feature, and are there any resources out there pertaining to linked measure groups? 

SQLServer2005_ADOMD.MSI and ClickOnce

Hello,

We need to install ADOMD.NET on client workstations and would like to install it via ClickOnce.  Has anyone built a ClickOnce Bootstrapper Prerequisite Package for SQLServer2005_ADOMD.MSI?

Also, when running SQLServer2005_ADOMD.MSI on a client workstation manually, we receive an error stating that an updated version of MSXML 6.0 must be installed as a prerequisite.  We run the MSXML6.MSI (11/7/2005) found in the SS05 November 2005 Feature Pack and then run SQLServer2005_ADOMD.MSI and everything is fine.  This workstation already has Version 2.0 of the .NET Framework installed on it.  Isn't MSXML 6.0 a prerequisite for installing V 2.0 of the .NET framework? 

If we need to install the updated version of MSXML 6.0 prior to installing ADOMD.NET, has anyone built a ClickOnce Bootstrapper Prerequisite Package for the updated version of MSXML 6.0?

We realize these components might be part of the SQL Server 2005 Express Edition Prerequisite Package that is available, but would like to avoid an additional 35 mg download if at all possible.

Thanks in advance for your help.

Wendell G.

 

 

Error when deploying Cube

Hi,

Just wondering if anyone is aware of why this error might occur.  I am deploying a cube to a named instance of Analysis Services (2005).  The user is a sysadmin and apart of the roles of the analysis database.

The following system error occurred:  Logon failure: the user has not been granted the requested logon type at this computer. .

Thanks.

Encryption whitepaper

There is a whitepaper discussing an application of the SQL Server 2005 encryption features at:

http://www.microsoft.com/technet/itsolutions/msit/security/sqldatsec.mspx

Thanks
Laurentiu

Need Enlightenment on an AS2005 Error

Dear Anyone,

File system error: The following error occurred during a file operation:  Insufficient system resources exist to complete the requested service. . 

We're using AS2005. We get this error during our DW process -- specifically during the partition process. Around 3 to 5 partitions are processed in parallel during this operation. We're using a server box with 4CPUs and 8GB of memory. Both SQL Server and Analysis is on a single box. 32 bit is our version of SQL2005.

Can anyone please enlighten me on what is causing this? PLease! =]

 

 

Thanks,

Joseph

Dundas Gantt Chart in Reporting Services

I was wondering if it is possible in RS to have a predecessor arrow drawn from one task to the other, depending on who is dependent of other tasks.

The Dundas website shows exactly what I need but it is in the Dundas Reports for .Net gallery.  The Dundas Gantt in reporting services doesn't seem to have the ability, or I haven't found it yet.

If anyone knows if this can or can't be done in Reporting Services and how, if it can be done.

 

Thanks.

Using AMO to create a Named Set in Analysis Services Database 2005??

Hello,

Does anyone know how to create a named set using AMO? 

The Analysis Services Tutorial DB has a "[Core Products]" named set, but I can't find the code in AMO sample application for the Analysis Services Tutorial that creates the named set.

Thank you very much,

Sincerely,

Hsiao-I

Calculation automatic formatting removes ;

Whenever we go between script view and calculation view in the cube, the semicolon seems to be removed from certain formulas.

The string looks something like this (pseudocode)

CREATE MEMBER

CURRENTCUBE.[measurename] AS '

[Measures].[Measurename] / 100

--,

--NULL

; --)', FORMAT_STRING = '#,#0';

What happens is the semicolon is removed from the end of the format string, which results in a processing error.

Has anyone else encountered this problem?  I have similar problems with the auto-formatting functionality of Reporting Services queries.  Is there any way to disable these features?

Update tracking in BI application [Analysis Server 2005]

Hi All,

 

I have a BI application in which I want to track update/insert and delete. The application that I have developed can track the INSERT to Fact table and any other dimension tables; but I am facing issue with UPDATE and haven't tested the same against DELETE. I have tried this using "SQL Server Notification" and "Scheduled Polling with Enable Incremental Updates". Could you please tell me what is the best way to track updates in BI applications. Is there anything else that I can look for?

 

Thanks a lot J

 

Warm regards;

Rakesh

Setting up roles - what to view when restrctions on a P/C dimension

Hello,

 

I am setting up roles in AS2005. I have come to realize that when you restrrict on a parent Child dimension you cannot enable the visual total as you then get an error.

OK so until visual totals is possible I will have to live with that...

I test the role and when I drag in the dimension I have restricted on it all works fine. I only see certain data, and the total amount in the cube as a TotalTotal in the cube... on the otherhand if I remove that dimension and drag in another normal hierarchy dimension I get to see all data - even the ones I should not...and the sums spent on these dimensions in TotalTotal. In other words I see too much.

I assume that it is enough to set restrictions on one dimension to restrict the viewing on other dimensions... How do I go about setting up the role in order for it to work?? Has it got anything to do with the visual totals?

Can anyone explain this...Please...

Sincerely,

 

The mechanism of notification for "proactive caching"?

There is an notification optioin in proactive cahcing, which can be set notification on update of sql server tables.

When the tables were updated, I used sql profiler to trace the SSAS activities and I got the notification logs on tables updated.

But the problem is I cannot find any notification services or events that handle the notifitcaiton to SSAS on the source database. So could you tell me how the mechanism of notification for "proactive caching" works? Can I trace the process of the notification?

Thanks 

Insufficient System Resources

Hi Guys,

I'm having problem regarding an error "File system error: The following error occurred during a file operation:  Insufficient system resources exist to complete the requested service". This happens during Update Dimension processing. Before the error was generated, reports have been created using pivot table. What I'm doing currently to resolve the problem is to restart analysis services and process update the dimension.

Another problem I encountered is, both pivot table and dimension processing hangs up if both process comes together simultaneouly. Is this a limitation of the SQL server2005 analysis services?

 

Cube Template

So my problem is the following,
i have created a cube without using a data source (from the wizard of cube creation). And i have generated the relationnal schema thanks to the following wizard. But i don't succeed to bind the data from my database to this cube. I found no documentation for this problem,  not in the msdn books onlines eigther.
Thanks

Determining the calculations affecting a particular cell

I currently have an application developed for MSAS2000 that permits users to enter data against a cube.  However, there is a need to prevent users from entering data into cells that are calculated in the cube based on MDX syntax (i.e. calculated members, calculated cells, etc.).  In MSAS2000, the cell evaluation list contains information about a cell's intrinsic properties which includes calculations, but this feature has been deprecated in MSAS2005.  What is the best way to interogate a cell and determine if it is affected by calculations in MSAS2005?  Is there someting I can use that will work in both 2000 and 2005?

Thanks,

SHW

How to bind PivotTable with ChartSpace in C++

I follow some sample code (VB) on internet, but it seem not work in C++. Does anyone tell me what is wrong with my code?

I want to bind PivotTable of OWC with ChartSpace. When user modify the column/row in PivotTable, the ChartSpace will sync automatically.

    mPChart.Clear();
    mPChart.put_ConnectionString(strSRC);
    mPChart.put_DataMember(_T("Adventure Works"));
    mPChart.putref_DataSource(mPTable.get_DataSource());

Thanks for your help.

What to do with a measure of varying data types?

Hi Folks,

I've asked this question to a few guy in the industry and would like to get some feedback and what ya'll think in here regarding this design problem.   Here it is ...

Anyways, one of the architectural issues I'd like to get some input on regards the design of my Answers Fact table.  Each row in this table respresents an answer to a question by a survey user.  The obvious measure (at least I think so) is the answer itself ... the problem is ... the underlying data type for an answer can vary from question to question (e.g. one can be an integer, the next a currency, the next short text, a yes/no, a true/false, a decimal, or a date/time, etc...).  So my question is ... "How should I  factor this idea of a measure with a varying data type into my dimensional model?"

One approach recommended to me was to create a separate column for each possible data type ... so my Answers fact table would look something like below.  Upside seems to be that the data is typed, most measures are addititive ... downside is that it seems like there will be alot of wasted space:

<foreign keys to dimension tables here...>
AnsInteger (int)
AnsMoney (money)
AnsDateTime (datetime)
AnsText (varchar(255))
AnsYesNo (boolean)
AnsTrueFalse (boolean)

Another approach I came up with was to simply create a generic AnswerValue column of type varchar (255) and the indicate the *true* data type of the answer through an attribute in the related Question Dimension.  My thought is that I could simply evaluate this attribute in my calculations to determine how to actually work with the data in the generic column.  Downside of this approach seems to be lack of type-safety and a totally non-additive measure .... up-side seems to be reduction of space in the DW.

Is one approach above preferably over the other? If so, why?

Or is there perhaps a better way to deal with the issue? 

Thanks!
- Wayde

Dimension Security Issue

I am trying to define a proper role (Dimension Security) for the following problem.

I want to have an "Allowed member set" for the attribute REGION:

{[Geography].[REGION].&[North America]}

And an "Allowed member set" for the attribute COUNTRY:

{[Geography].[COUNTRY].&[Germany]}

I would like to see North America and Germany in my Geography dimension.

The result of this two member sets is nothing. Each role alone gives back the expected result.

How to handle this security related issue?

 

SSAS non default instance while disconnected from domain

For those still being plagued by the bug (which is fixed in SP1), that prevents the SQL Browser from resolving SSAS instances on a machine that is disconnected from the domain, the resolution is simple.

There is misleading advice about using local security accounts and other hacks, however all that is required is to connect to the instance directly using the machine:port syntax.

SSAS will by default use a dynamically allocated port number so you would benifit from setting a fixed port number if this is something you need to do regularly.

Project REAL Source Code

I am reading the Project REAL documentation, which is really useful and interesting, but I think it would be more efficient if we had the project (even without the data).

Is the source code of Project REAL gonna be available for download one day ?

Add-in for SQL Server Management Studion

Announcement

New tools for MSSQL administering
Add-in for SQL Server Management Studio
http://www.aris-soft.com
http://www.ssmsaddin.com

Tables Data Scripting extension of SSMS
at No comments:

Data Sources, ImpersonationMode, and AMO

Data Sources, ImpersonationMode, and AMO

We have an ASP.NET application that uses AMO to make cube modifications (adding and removing dimensions and measures) and process cubes, and we're running in to a strange problem with the code that removes dimensions.

The error that's coming back from the AMO code is  "The ImpersonationInfo for datasource 'AEDB' contains an
ImpersonationMode that can only be used by a server administrator."

I checked the impersonation mode setting on the data source, and it's set to  "ImpersonateServiceAccount". The service is running as LocalSystem in this case, and the SSAS and DB Engine are running on the same machine.

Taking the error message at it's word, I added the user to the server role, and the error went away. But I'd really rather avoid that if possible. The user has been granted all the relevant rights for modifying the database through an SSAS role - adding measures and dimensions works fine, as does removing measures. But not removing dimensions.

I tried changing the impersonation mode to "Use the credentials of the current user", and that cured the deletion problem but broke cube processing ("contains an ImpersonationMode that is not supported for processing operations").

So I'm not sure what to try. Is there a solution here that covers both cases (processing and modification)? Am I missing something?

BTW,
The AMO portion of the stack trace is:

Message: The ImpersonationInfo for datasource 'AEDB' contains an
ImpersonationMode that can only be used by a server administrator, .

    Source: Microsoft.AnalysisServices
    Stack Trace:
       at
Microsoft.AnalysisServices.AnalysisServicesClient.SendExecuteAndReadResponse
(ImpactDetailCollection impacts, Boolean expectEmptyResults, Boolean
throwIfError)
       at Microsoft.AnalysisServices.AnalysisServicesClient.Alter
(IMajorObject obj, ObjectExpansion expansion, ImpactDetailCollection impact,
Boolean allowCreate)
       at Microsoft.AnalysisServices.Server.Update(IMajorObject obj,
UpdateOptions options, UpdateMode mode, XmlaWarningCollection warnings,
ImpactDetailCollection impactResult)
       at Microsoft.AnalysisServices.Server.SendUpdate(IMajorObject obj,
UpdateOptions options, UpdateMode mode, XmlaWarningCollection warnings,
ImpactDetailCollection impactResult)
       at Microsoft.AnalysisServices.MajorObject.Update(UpdateOptions
options, UpdateMode mode, XmlaWarningCollection warnings)
       at Microsoft.AnalysisServices.MajorObject.Update(UpdateOptions
options)
       at

how many connection sqlexpress supports

I see this question a lot, so I thought I would go ahead and post the answer here.

Q: How many connections does SQL Server Express support?

A: Unlike MSDE, SQL Server Express does not limit connections.  SQL Server Express supports:

  • 1 gb RAM (note the machine can have more memory, but 1gb will be used by SQL Express)
  • 1 processor (again, the machine can have more, but just 1 proc will be used by SQL Express)
  • 4 gb database size (note, there can be multiple databases, but none of them individually can be more than 4 gb)

Hope this helps,

Thanks< MJ


Reply:
Is it right that only 5 database users can connect to SQL Server express 2005 engine at the same time?

------------------------------------

How to Concatenate a member with a string

Hello MDX experts,

I have a question regarding concetenation of a string within MDX.  You will see 5 products below, but I am trying to add "TestProduct" string to each of the Product members below on the Rows (ie AWC Logo Cap - TestProduct).  The commented lines are the ones giving me trouble.

WITH SET [ChardonnayChablis] AS
   '{ [Product].[Product].Children  }'
   //'{ [Product].[Product].Children + StrToMember("- TestProduct") }'
   //'{ MemberToStr([Product].[Product].CurrentMember) }'
SELECT
   topcount([ChardonnayChablis],5,[Measures].[Order Count]) ON Rows,
   [Measures].[Order Count] ON Columns
FROM [Adventure Works]

Sincerely,

-Lawrence

connect to web service?

Is it possible to connect to a web service from Analysis Services to populate a cube? Can someone point me to sample code or documentation?

Can not connect to SQL Server 2005 Analysis server / browse tutorial cubes

When I try to :

browse the cube or its dimensions from tutorial (lesson 2 from AS 2005 Tutorials )

connect to AS via SQL Server Management Studio.

see or set the remote options of olap servicen in SQL Server Surface Area configuration

And the error is :

Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. (System)

I removed and reinstalled SQL Server . Installation was all rigtht with no errors.

I got the hotfix from : http://support.microsoft.com/kb/912017/EN-US/ and installed the patch.

But it did not help.

Both the olap and browser service run .

I do appreciate any practical advice that can help me solv this issue.

 

XMLA To Create Dimension Attribute

I am looking for an XMLA (or ASSL) script to add an attribute to a dimension.

Can anyone help ?

Unable to add database to Web project - SQL Express eror message

Searched messages with no real help. Even re-installed VS 2005 and SQL Express (from the CD), and reset settings.

1) Created new website.

2) Tried to "add new item" -> SQL Database.

Message: Connections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: http://go.microsoft.com/fwlink/?linkId=49251

Am running SQL Server 2005 and SQLExpress in named instances on localhost.  If I use Server Explorer within Visual Studio 2005 I can create a database for the SQL Express Server, and see its tables, so I know SQL Express is available to Visual Studio, but I still get the message that SQL Express is not installed.

In Visual Studio 2005 -> Help -> About -> Installed products it shows

SQL Server Analysis Services

SQL Server Integration Services

SQL Server Reporting Services - but no direct mention of SQL Express or database engine, if that makes a difference

Any help appreciated.  Thank you.

3/6/2006 SOLVED

Fixed the problem by reinstalling SQLExpress via the VS 2005 disk rather than the Windows->AddPrograms feature that I had used earlier.

Member Name/Value doesn't show anything when the language is set from all to any language

SQL Server 2000

                              I went through the following article

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmemberprops_8ier.asp

To achieve multilingual support but when i changed the Language of member property

from All to any particular language ex.Spanish,I am not able to see that member name/value when the dimension is browsed,Any ideas

Thx and regards

Pankaj

 

OLAP User Access

Having created the OLAP cube and dimensions and deployed the OLAP structures using Business Intelligence Development Studio we are looking for ways to facilitate the OLAP Analysis for users. We have used Oracle Discoverer Plus and Oracle Portal and looking for something that offers similar features.

Our objective is to create a similar portal as in Oracle and allow OLAP stuctures to users so that they can perform drill down, slice and dice on the defined OLAP reports.

Thanks.

Good Books for building weighting table in dimension to resolve many to many relationship

Hi All,

I want to make store proceduce to change data from relational schema to data warehouse as weighting table model in OLAP. That's mean I will reschema and define some goups as if OLAP does.

Do you know any good book, or tool or anything that can help to build algorithm effectively for group dims

Thanks

Nhan.

 

Need general advice on financal reporting (P&L statement)

Hi!

We are implementing a cube that should support our P&L statement.

We have come accross the following issues so far, any input on these would be appreciated.

1) Our P&L statement combines different types of values: Monetary values, percentages, key figures (like average production / day) and other non aggregatable measures such as headcounts. These are intermixed in our reports like:
Total income x$

Income from A customers y$

Number of A customers 32

Income from B customer z$

Any thoughts on how to handle this?

2) We need to sum random accounts and subsums, some of these sums are pure aggregations and others are math like income account X minus income account Y.These sums do not roll up nicely into their parents. Any best practices / experiences on doing this?

3) Any general advice on handling the dimension(s?) that would be put on rows in such a scenario? As i mentioned, we will be showing combinations of sums, costcenters, statistical accounts (ie. key figures), and not the basic accounts (although we will want to drill down into accounts from the report).

 

Any help / suggestions / book references / web references GREATLY appreciated!

- Peter

Incorrect Aggregations

I have a cube displaying incorrect aggregations. 

Can anyone tell me why my aggregations are incorrect? 

Can anyone tell me what I should be looking for?

Here is what it looks like:

 

Assets

   Current Assets

      Cash

         Cash Account 1                   100

         Cash Account 2                   200

         Cash Account 3                   300

      Total                                       700

      Accounts Receivable

          A/R Account 1                    100

          A/R Account 2                        0

      Total                                       700

      Inventory          

          Inventory Account                   0   

      Total                                       700

   Total                                          700

   Non Current Assets 

   Total                                          700

Total                                             700

 

The Total Assets is displaying as the Total at each level of the Dimension but I have no idea why.  Totals for other account types (Liabilities, Revenue, Expense, etc., do the same thing, but display a different amount.

 

Any suggestions would be greatly appreciated.

 

Wendell G

 

          

 

 

Rows to repeat at top

suggestion for msft developers. 

I just learned the <SimplePageHeaders> flag in the config file (to manage the export to excel) and find this functionality extremely useful.  fine job.

Suggestion on new functionality.  Something where I can specify the number of rows to repeat at the top of the page, also in excel, to further improve the integration with excel (would like the column headers to repeat on each page).  Freezing them at the top also would be awesome. 

2005 is good progress

Reporting Services 2000 Accessing SSAS 2005

has anyone tried using Reporting Services 2000 to access a SSAS 2005 database? I'm finding RS 2005 pretty much unusable as far as accessing analysis services goes, crashes, errors, unacceptable performance, etc...

 

I'm sure I'll return to RS 2005 after service pack 1, but at this point, I just need something that works.

Division by NULL

In Analisys Server 2005 the division by NULL is infinity !!!!!!!!!!!

Why this occurrs ?? In 2000 this division is NULL too... Anyone can help ??

Supporting Multiple Character Sets

 

Hi

     Currentely i am using analysis Services with SQL SERVER 2000

OS:- Windows 2000 Server

Datasource:-Oracle

                  We need to show the data in the cubes for multiple countries.

But we are not able to show the data for diferent languges at the same time becoz

in windows 2000 default language on the server can be set to only one language

and the data in the cube can be shown in same language

Ex Let say we wud liket see the data in the cube for Russia and turkey on the same server.If we set the default language of the server as "Rusia" or "Cyrlic"

We are able to see data for Special Russian chars but then we are not abale to see specilal turkey characters.

                                           Please let me know if anybody has the answer for this.

Pl also let me know if same thing can be achived using sql server 2005

 

Thx and regards

Pankaj

Different aggregation based on dimension attribute

I am very new to Analysis Services, so please forgive the relative ignorance.

I *think* what I want is to aggregate a measure differently based on the "level" of a time dimension.

What I am starting with is a transaction table that has (among other things) an amount, a posting date, and a flag. The flag indicates whether the amount of the transaction applies to the current period/month or to the entire year.

I am envisioning that when a user drills down to a certain period, the amount is a sum of the amounts applied directly to that period plus the average of amounts applied to all periods within the year.

Thanks very much for any help.

Regards,
Daniel

Encrypted Data / Fact Dimension

Hi,

I have a requirement to encrypt a column of data within a SQL Server 2005 table however I then need to provide this value in the drill through data set. How do I tell analysis service's that this is an encrypted column and equal how do I prompt for the user to key/pass through the encryption key so that the data set returned is the non-encrypted value????? Is MDX an option? and how do I ensure the de-encryption API is called????? Is this even supported???

Any help or advice would be very much appreciated.

Sunny

Bug or solve_order??? invisible measures, calculated members and calculated cells.

Hi,

In AS2000 I have a problem. I have a calculated member which is a member of the measures-dimension called [measures].[avg daily hrs]. This is calculated by [measures].[total hrs]/[measures].[total days], The measures [measures].[total hrs] and [measures].[total days] are both made invisible by setting the "visible" property to false. The solve_order of the calculated member is 0. Then I have calculated cells, I do this because I have other measures which need currency conversion. The calculated cells have solve_order -1.

My issue is that when my [measures].[total hrs] and [measures].[total days]  are set invisible the calculated cell returns null, when I set them to visible I get the right result. How can the property visible of the measure influence my return values??? I am lost, help.

SSAS2005 currency conversion for calculated measures based on measure error

Hi,

I have a problem whith the currency conversion (I've use the SSAS2005 currency conversion wizard) when it comes to calculated measures based on measures. I have a calculated measure a)

CREATE MEMBER CURRENTCUBE.Measures.[_Only third party] AS Sum({Descendants([Customer].[CustomerGroup].&[1], [Customer].[CustomerGroup])}, [Measures].[Amount])

and another calculated member b) which uses calculated measure a)

CREATE MEMBER CURRENTCUBE.[Account].[AccountIAS].[511.11 - Sales net of taxes - Group] AS [Account].[AccountIAS].[Type].&[T].&[51].&[511].&[511.13] - ([Account].[AccountIAS].[Type].&[T].&[51].&[511].&[511.13],[Measures].[_Only third party]);

When the currency conversion is performed on a calculated member ([511.11 - Sales net of taxes - Group] ) the values in the reporting currencies don't get right. (accidentally it shows the currency rate of the reporting currency)

For reference I have a calculated member c)

CREATE MEMBER CURRENTCUBE.[Account].[AccountIAS].[511.1 - Sales net of taxes] AS [Account].[AccountIAS].[Type].&[T].&[51].&[511].&[511.13]

which is working fine for the reporting currencies. So - what is the problem with calculated measures based on measures regarding the currency conversion - and is there a way to get around this issue?

Br,

Henrik

at No comments:

SQL Server 2005 SP1 Webchat - March 22nd 9:00 am PST

SQL Server 2005 SP1 Webchat - March 22nd 9:00 am PST

We will be hosting a public web chat discussion on March 22nd 9:00 – 10:00 am PST about the March CTP of SP1.  It is the customers' opportunity to engage with the dev team and provide feedback and ask questions. Strong participation from customers will act as a motivator for stronger participation by the dev team and will increase their interest in having these in the future, so we urge you to join and participate. 

 

For more information and sign up for this web chat go to: http://www.microsoft.com/technet/community/chats/default.mspx#06_0322_TN_SQL. 

Error in Report "Memory Consumption" after upgrading with SQL2005 CTP SP1

There's a little bug in the CTP SP1 release of SQL2005.
Opened MS SQL Server Management Studio. Connected to a SQL2005 64bit server with 6GB memory. Choose Summary, Report, Memory Consumption and in the graph for Buffer Pages Distribution (#Pages)  there's no graph, but the following error "Unable to retrieve data for this section of the report. Following error occurred. Msg 8114, Level 16, State 1 Error converting data type bigint to int.". The SQL2005 32bit didn't show this error after upgrading with CTP SP1, but it has less memory (2 GB).

Regards
Peter

Reply:

Peter,

I'm moving this thread to the Tools General forum as they'll be best able to help you.

-Jeffrey


------------------------------------
Reply:
Created a bug report on produkt feedback center.

Peter

------------------------------------

Welcome and Tips for using this Forum

Welcome to the SQL Express Forum

Hello everyone and welcome to the SQL Express forum. One of the primary goals of the SQL Express MSDN Forum is to offer a gathering place for SQL Express users to share experiences, discuss issues related to SQL Express, ask questions and interact with the folks here at Microsoft. Hopefully everyone on the forum will contribute not only questions, but opinions and answers as well. I'm looking forward to seeing an active community of experts grow up around SQL Express.

This post has information to help you understand what questions to post here, and where to post questions about other technologies as well as some tips to help you find answers to your questions more quickly and how to ask a good question. See you in the group.

Mike Wachal
SQL Express team

Be a good citizen of the Forum

When an answer resolves your problem, mark the thread as Answered. This makes it easier for others to find the solution to this problem when they search for it later. If you find a post particularly helpful, click the link indicating that it was helpful

What to post in this forum

It seems obvious, but this forum is for discussion and question of the SQL Express family of products. When you want to discuss something that is specific to SQL Express, this is the place to be. An example of something that is specific to SQL Express is User Instances. Since SQL Express uses the same database components as other SQL Editions, there are several common pieces of functionality and there may be better forums elsewhere to ask about those. Don't worry, this is a great place to start if you're using SQL Express, if there is a better batch of experts to answer your question, we'll just move your post to that Forum so those experts can answer. Any alerts you set up will move with the post, so you'll still get notification. Here are a few of the other forums that you might find interesting:

SQL Server Setup & Upgrade – This is where to ask all your setup and upgrade related questions, even for SQL Express. (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=95&SiteID=1)

SQL Server Tools General – This is the best place to ask Management Studio Express questions. (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1)

SQL Server Reporting Services – If you're working with RS in Express Advanced, check out this forum. (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1)

SQL Server Database Engine – Great forum for general information about engine issues such as performance, FTS, etc. (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1)

SQL Server Data Access – If you're connecting to SQL through almost any protocol this is a SQL forum dedicated to you. (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1) There is also a .NET Data Access forum that covers similar topics, but with a VS focus. (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=45&SiteID=1)

 

There are also some general forum categories that will interested you:

Visual Studio Express Editions (http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=35&SiteID=1)

Visual Web Developer and ASP.net (http://forums.asp.net/)

.NET Development (http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=12&SiteID=1)

How to find your answer faster

There is a wealth of information already available to help you answer your questions. Finding an answer via a few quick searches is much quicker than posting a question and waiting for an answer. Here are some great places to start your research:

SQL Server 2005 Books Online:

Search it online at http://msdn2.microsoft.com
Download the full version of the BOL from here

Microsoft Support Knowledge Base:

Search it online at http://support.microsoft.com

Search the SQL Express Team Blog:

                The blog is located at http://blogs.msdn.com/sqlexpress

Search the MSDN Forums:

Simple search: http://forums.microsoft.com/MSDN/Search/default.aspx?SiteID=1
Advanced search: http://forums.microsoft.com/MSDN/Search/AdvancedSearch.aspx?SiteID=1

Search other SQL Forums and Web Sites:

MSN Search: http://search.msn.com/

Or use your favorite search engine

How to ask a good question

Make sure to give all the pertinent information that people will need to answer your question. Questions like "I'm having problems using SQL Express with C#, any ideas?" will likely go unanswered, or at best just result in a request for more information. Here are some ideas of what to include:

·         The exact error message if you're getting an error. (The SQL Error Logs can be a rich source of information. See the section on error logs below.)

·         Any tools you're using when experiencing the problem. (Management Studio, VS Express product, etc.)

·         Any troubleshooting you've already done. (eg. "I've tried connecting to SQL Express with SQLCmd and that fails with the same error.")

·         If you're having a coding problem, include a sample of the code that cuases the problem. Make sure to identify the line where the problem occurs if you can.

·         Connection strings you're using when you're having connection problems.

·         Is everything installed on the same computer or are you working across a network?

Finding the Error Logs

You will often find more information about an error by looking in the Error logs. There are two sets of logs that are interesting:

Setup Error Log default location: C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG

SQL Error Log default location: C:\Program Files\Microsoft SQL Server\MSSQL.#\MSSQL\LOG (Note: The # changes depending on the ID number for the installed Instance. This is 1 for the first installation of SQL Server, but if you have mulitple instances, you will need to determine the ID number your working with. See the BOL for more information about Instance ID numbers.)

Bug (?) in SQL2005 x64 SP1 CTP

We're testing the same DB (~ 5GB, ~ 500 tables) with
Server32 (Win2003 Standard 32 bit, SQL2005 Developer 32 bit SP1 CTP, 1gb Memory) and with
Server64 (Win2003 x64 Standard 64 bit, SQL2005 Developer 64 bit SP1 CTP, 6gb Memory).

exec sp_MSforeachtable @command1="SET QUOTED_IDENTIFIER ON DBCC DBREINDEX ('?', '', 90)"

On Server32  the SQL above runs without errors. On Server64 there comes the following error message:
Msg 8621, Level 17, State 1, Line 1
The query processor ran out of stack space during query optimization. Please simplify the query.

A 64bit server with 6gb memory has less stack space than a 32bit server with 1gb memory??

Regards
Peter

PS: I know sp_MSforeachtable is not an official stored procedure, but I don't thinkt there's the problem. I got the same error on Server64, when I run the following cursor:
DECLARE tablename CURSOR
READ_ONLY
FOR select [name] from sys.tables where type = 'U'

DECLARE @name varchar(80)
OPEN tablename

FETCH NEXT FROM tablename INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        DECLARE @message varchar(800)
        SELECT @message = 'SET QUOTED_IDENTIFIER ON DBCC DBREINDEX (''' + @name + ''', '''', 90)'
        EXEC (@message)
    END
    FETCH NEXT FROM tablename INTO @name
END

CLOSE tablename
DEALLOCATE tablename
GO

Reply:
Please file a bug using the MSDN Product Feedback Center. And mention the OS platform, server versions, number of tables in the database etc.

------------------------------------
Reply:
Bug report created

Thanks
Peter

------------------------------------

Stepping over the line

I'm running both SQL 2000 and SQL 2005 on the same server.

I got this informational message when running a replication job in SQL 2005. It references the SQLAgent of SQL 2000 instance and says it needs to be upgraded to SQL 2005.

Log  Windows NT (Application)

Source  SQLAgent$My2000Instance

Category  Job Engine
Event  1073742027
Computer  AMWA-011605

Message
SubSystem Message - Job 'MyJobRunninginSQL2005' (0xF6BA7BF3BB3EAD42BF281CA1CA39D2EE), step 2 - The SQL Server version of the Publisher server needs to be upgraded to 'Microsoft SQL Server 9.0'.

I say "hooey". It is none of its business if one of my instances is SQL 2000.


Reply:

Perhaps this is old news.

Perhaps I didn't read the "Can't do that" notices too closely.

I can't be more explicit, but I am getting solid evidence that one can't run SQL 2000 replication on the same XP server as SQL 2005 replication. I received an error message from a SQL 2005 job that references data that only exists on the SQL 2000 server.


------------------------------------
Reply:
I went and looked at my SQL 2000 replication and it was very confused because it could clearly see SQL 2005 objects. That will probably explain some of the really bizarre stuff I've been seeing the past 2 weeks on SQL 2005 replication (I hope).

------------------------------------
Reply:

You may have already solved your own problem, but I'm interested in what you're describing here. Could you describe what you're doing in a little more detail? Let me know what OS you're running on the publisher, distributor and subscriber(s). Also describe the type of replication, what version of SQL you're coming from/to, etc.

Buck


------------------------------------
Reply:

I got rid of any possible interference by removing SQL 2000 from the workstation, except for VirtualPC, leaving only SQL 2005 RTM.

However, fyi, I had both SQL 2000 and SQL 2005 replication up. I found that the SQL 2000 (svcpk 4) replication could see the SQL 2005 publications. The error message indicated that the SQL 2005 could see the SQL 2000 publication. All of this is on a single XP server. I'm trying to get a simple merge and tran replication republishing scenario up on 2005.  To eliminate any more complexities, I had to simplify my environment.   I can't get the tran part working so far. The tran AtoB works, but the tran BtoC fails. I'm commenting on that process in another recent post and not maintaining this post.


------------------------------------

Error connecting Excel (PivotTable) to OLAP services for cube browsing

I have created an excel template by using the pivot table to link to a cube. When i open the template (at user pc) to browse the cube in pivot table, i getting an error message as below:

Connection Failed

SQL State: '28000'

SQL Server Error: 18456

[Microsoft][ODBC SQL Server][SQL Server] Login failed for user 'domain\useridA'

I am using olap services 8.0 & sqlserver 2000.

User pc are with the same domain with server. I checked also this useridA is properly created in the cube role. This role contain of abt 6 user id. Others user id is work fine (by using the same excel template) except for this useridA.

Any ideas to solve this issue?

Thanks in advance.

 

CTP June Dev.Edition:TCP/IP protocol is disabled at default

I have tested Developer Edition CTP June but when I connect to remote SQL Server, "A connection was successfully established with the server, but then an error occurred during the pre-login handshake." error occured.
I find out TCP/IP protocol is disabled at default at CTP June. When you meet with same situation, try enable TCP/IP protocol being enabled on "SQL Server Configration Manager"

It's bug or by design?

Regards

Hajime Gondo

Reply:
This is in fact by design. As part of only making available services that you need we are defaulting the Developer Edition to not listen on TCP/IP.

-Euan

------------------------------------
Reply:
Thanks Euan! I found topic "Network Protocols and Network Libraries".

But I can't accept this design. Bacause developer edition "must" (I think) functionaly works same as enterprise edition. If not, developer must search what is deffernt between Dev. and Ent.(such as me)  This causes needless confusion.

------------------------------------
Reply:
It does work the same as Ent and Std, the defaults are just different(this is the case with SSE as well). We provide a tool to flip the defaults.

-Euan

------------------------------------
Reply:
It's nice that you will provide the tools.  Question is, WHEN, we are waiting....

------------------------------------
Reply:
There are 2 tools, the commandline version of setup allows much of this to be overriden and the surface area config tool allows you to flip al;l of these things asll, both have been in since the product RTM'd last year.

------------------------------------
Reply:

To install via the command line and turn TCP/IP on, specify the DISABLENETWORKPROTOCOLS command line parameter:

;--------------------------------------------------------------------
; The DISABLENETWORKPROTOCOLS switch is used to disable network protocol for SQL Server instance.
; Set DISABLENETWORKPROTOCOLS = 0; for Shared Memory= On, Named Pipe= On, TCP= On
; Set DISABLENETWORKPROTOCOLS = 1; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= Off
; Set DISABLENETWORKPROTOCOLS = 2; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= On

; Note: DISABLENETWORKPROTOCOLS if not specified has the following defaults.
; Default value for SQL Server Express/Evaluation/Developer: DISABLENETWORKPROTOCOLS =1
; Default value for Enterprise/Standard /Workgroup: DISABLENETWORKPROTOCOLS =2

DISABLENETWORKPROTOCOLS=

To configure the TCP/IP after installation, you need to use the Surface Area Configuration tool that Euan mentioned.  This blog entry describes it pretty well for SQLExpress - it applies equally well to other skus. ttp://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Hope this helps.

-Jeffrey


------------------------------------

Nsort for SSIS, a highly parallel sort component

If you are looking for a enterprise-class sort component, try Nsort for SSIS.

Based on the same Nsort technology that has set multiple world records (1 Terabyte in 33 minutes, 40GB in 60 seconds), this SSIS component overcomes the limitations of the built-in sort transformation. Not only does Nsort for SSIS use multiple processors and disks effectively to support for arbitrarily large dataflows, it also allows multiple inputs and outputs to improve parallelism further, removing bottlenecks from the entire dataflow.

Nsort for SSIS is available for X86 and X64.

For better performance than standard SSIS sort transformation, get a free trial now:  http://www.ordinal.com/ssis.html

SqlExpress Setup fails if not installed From C:

I have an win2000 Svr SP4 and was getting "sqlncli.msi" not found errors, also the database services module was failing to install.

It turned out the I was attempting to install from my D: drive ( oh the nerve of not using the C drive!) .  Steps to fix was

Uninstall all SQL modules from add /remove programs ( No reboot required)

Manually extract as auto extract keeps going to d drive:do a "SQLEXPR.EXE /x:c:\sqlexprtmp" ( notice Use C drive)

Hit c:\sql\exprtmp\setup.exe  and sqlexpress  all installed no Problem.

Maybe the install documentation http://download.microsoft.com/download/f/1/0/f10c4f60-630e-4153-bd53-c3010e4c513b/ReadmeSQLEXP2005.htm could be ammended to show this requirement!

 

 

 


Reply:

Hrm.  That's no good.  Phil, can you go ahead and log a bug using: http://lab.msdn.microsoft.com/ProductFeedback/

We'll need the log files from %ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\Log\Files for the failed installations (or you can just zip them all up).  When you log the bug, can you also describe how your system is set up?  We'll need that information to help reproduce your issue in-house.

Thanks,

-Jeffrey


------------------------------------

Slow pivoting operations using Excel client

I recently implemented a BI solution for a large organization. Before this, they had an Excel pivot table based on a big query that fed Excel with flat data worth of 70Mbytes. The new solution is based on SSAS and gives an Excel XP pivot client of about two hundred Kbytes.

Some technical details of the cube are: There are 3 dimensions, one of which is time. The other two dims have 2 hierarchies and about 1.5 thousand members at the leaf level. The fact table consists of 6 facts. Each of them splits to two calculated the negative and the positive values based on a CASE statement. The fact table has about 500 thousand rows (for the time being).

The problem is that the users are experiencing a delay of 5 to 8 seconds for each pivoting operation they do, while the old solution gives almost instant pivoting operations. I know that there are a lot of issues to investigate. I also understand that a slower pivoting operation is expected as Excel requests the data from the SSAS server and this is the trade-off you get for the really small Excel file but I was just wondering if I have overlooked something...

 

New contest - $10,000 and all it takes to start is an idea...

Just announced, a new contest for SQL Server Express.

Do you like to use technology to build cool and useful stuff?  Do you think you could do it with Visual Studio Express and/or SQL Server Express?  Would you like $10,000 cash?  Well then we have a contest for you!  Learn more at www.MadeInExpressContest.com. 

Cool stuff.

MJ

Cube Design Question

I need to compare sales quotas versus actual sales performance.

Target quotas exist for dealers, regions and marketing channels.  Region and marketing channels are attributes of dealers.  Only the top dealers have quotas but all dealers have a region and marketing channel attribute.

Do I create 3 separate small fact tables for each or is there a more efficient solution using the UDM? 

TIA

Intersecting Drillthrough results - an MDX challenge

Hi,

Anyone have any guidelines on how to intersect the results of 2 or more DrillThrough operations?

In other words, say, if I drillthrough on Cell1  and get Factkeys: 1 to 20.

I drillthrough on Cell2, and I get FactKeys: 15  to 30.

Now what I want is to run my MDX Select query, which returns only Cell1 and Cell2. Then I want to do an 'Intersected Drillthrough' operation, which should give me the intersected Factkeys:  15 - 20.

This is the common set of keys from the two drillthrough resultsets (1 - 20) and (15 - 30).

Thanks,

JGP

Best Practice-working with SQL Express Database

Hi,

I am new to working with Database using .net C#. i will really appreciate if someone can point me to a link where i can find best practices of working with sql express database with my c# web service applications.

preferably i would like to have answers for:

- to have one db or to have one for testing and one real db?

- db security

- use windows authentication or db user authentication

etc.


Reply:
Best practices for SqlExpress will depend somewhat on your environment/requirements.  Since you are developing a web service, I suggest asking the question in one of the asp.net forums (forums.asp.net).

SqlExpress has it's own forum here on the MSDN forums.  I'll move the thread over there for more general info on Express best practices.

------------------------------------

Cubesize dependent on number of records?

Experimenting a bit I've constructed two cubes with identical measures, dimensions and aggregation design. The difference is one processes 1 million records and the other one 100 million. The first cube ends up 48,28 MB in size while the other is 4210,30 MB. From where is this difference coming? Considering the number of cells should be equal as they both have same dimensions with same members and each cell have the same measures stored I would've imagined they'd be roughly the same physical size?

Reading the whitepaper about Drillthrough at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_anservdrill.asp

I found that drillthrough detail information is now stored in the cube. And since the orders of magnitude difference between cubesize and nbr of records are roughly equal I was wondering if this is what's showing up? In that case - is there any way to disable this? Or am I barking up the completely wrong tree?

Embed a Data Mining model in ASP.NET

Is it possible to embed a Data Mining model in APS.NET?

Reporting against MDX Queries

I have a report where each line, and sometimes each cell, contains the results of a different MDX query.  These are currently summarized and additional formulas applied using Excel macros and raw text files.

In SQL 2005, using Reporting Services and SSIS, are there any suggestions for improvements to this architecture?  Excel formatting is important, as is the ability to expand and collapse nodes from a web page.

I would like some tips or feedback on your experiences with MDX and Reporting Services, to assist me with further ideas on the new architecture.

thanks,

 

Dimensions with Startdate / Enddate fields

Hi,

I would like to know what is the best practice to manage Dimensions with StartDate / EndDate fields, in SSAS.

Regards

Ayzan


Reply:

Well first, is it possible to build dimensions with time dependance ?

 

Regards

Ayzan


------------------------------------

TechNet Webcast available: Monitoring Replication in SQL Server 2005

On March 3, I gave a webcast about monitoring replication in SQL Server 2005 that includes many demonstrations of Replication Monitor.

Event Description

 

Products: SQL Server.

Recommended Audience: IT Professional.

Language: English-American

Description:

This webcast provides an overview of the new features of Replication Monitor in Microsoft SQL Server 2005. Along with many of the SQL Server tools, Replication Monitor was redesigned and rewritten for SQL Server 2005. Replication Monitor lets you track end-to-end latency for transactional publications and see a detailed analysis of merge performance. You can also define warning thresholds for replication performance that will generate events and alerts. And, for the first time, Replication Management Objects (RMO) provides a programming interface if you want to write your own monitoring tools.

You can watch a recording of the webcast by clicking:

http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032290639&Culture=en-US

To view the recording, click the "Register for event" link on the left. (Granted, it doesn't make much sense to "register" for an event in the past, but that's how the site is set up.)

Phillip Garding

Distinct count MDX question

I am trying to get the distinct number of accounts that have subscribed to recieve emails about cruises, have had a past trip between 2004 and 2006, and have flown out of either the Seattle or Houston airports.

My MDX is given below.  It is returning the number of accounts but not the number of distinct accounts.  Could anyone give me some insite on how to change this query to return distinct accounts?

Thanks. -- Nedra

SELECT
    NON EMPTY
    CROSSJOIN (
              CROSSJOIN (
                            {[Sub_CruisesDim].[All Sub_CruisesDim]}
                            , {filter( {[Departure Date].[All Departure Date]}, [Departure Date].[All Departure Date] >= 2004 or [Departure Date].[All Departure Date] <= 2006) }
                        )
              , {
                    filter ( {[DepartureAirport_Dim].[All DepartureAirport_Dim]}, Not IsEmpty([DepartureAirport_Dim].[All DepartureAirport_Dim].[IAH]) or Not IsEmpty([DepartureAirport_Dim].[All DepartureAirport_Dim].[SEA]))
                }
              )
    ON COLUMNS 

FROM
    [Account_Fact]
WHERE
    ([Measures].[Account Id])
   

 

at No comments:
Subscribe to: Posts (Atom)

Setup is Split Across Multiple CDs

Setup is Split Across Multiple CDs Lately I've seen a bunch of people hitting installation errors that have to do with the fact th...