Named sets and Existing

I have a very large Account dimension (> 2,000,000 members). I would like to create a named set for the top most profitable accounts. I plan to use this named set in the SSRS Report Builder to compensate for its lack of aTop N feature. The problem is that the user can change the fiscal period and SSAS needs to reevaluate the set. Using Generate function won't work for performance reasons. So, I'm trying to use Existing to force the set generation. e.g.

CREATE SET CURRENTCUBE.[Top 5 Profitable Accounts]

AS

Order(TopCount(

(EXISTING [Account].[Account].[Account].Members, [Period].[Period].CurrentMember), 5, [Measures].[Profit]),

[Measures].[Profit], DESC);

However, this gives me "The Period hierarchy already appears in the Axis1 axis." error when I test the set with the following query

select non empty [Measures].[Profit] on 0,

non empty [Top 5 Profitable Accounts] on 1

from [RPM]

WHERE [Period].[Period].&[20030228]

and duplicated dimensionality error in the Report Builder.

Does anyone know how this could be implemented?

There are a couple of issues in the Named Set:

The error can be eliminated by not explicitly specifying [Period].[Period].CurrentMember with Existing.|||

Deepak,

Thank you for helping. True, the modified set doesn't error out. However, as you pointed out, it is not dynamic. It only works with the default time period which is where I started. In other words, EXISTING doesn't help here. Changing the query slicer to a different time period doesn't produce any results. It looks like we cannot change the context of standard named sets defined with CREATE SET in the cube script. This pretty much leaves me with no options to simulate TopN with the Report Builder.

Named Set with TOPCOUNT Not Working Correctly

I need to create reports that contain our top 15 customers at any given point in time. I created the following Named Set using the TopCount function in SSAS 2005:

TOPCOUNT([Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

When I bring this set into the client (Excel 2007 pivot table), its shows our top 15 customers for all time. When I add a date filter for just 2007, it doesn't update the list to just show the top customers for 2007. It just keeps the same "all time" group.

When I create this named set in a browser query, it works correctly. That is, it shows the top 15 customers for 2007. Is there any way I can get the Named Set created in SSAS to show the correct data based on the filtered date in the client?

Here is the browser query:

WITHSET

[Top Performers]

AS

'TOPCOUNT([Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])'

SELECT

{[Measures].[Net Ship Dollars]} ONCOLUMNS,

{[Top Performers]}ONROWS

FROM Shipments

Where ([Date].[Calendar Year].&[2007])

Thank you.

David

Are you using AS 2005 SP2? This problem has been fixed there.|||

The problem here is because the named set in the MDX Script gets resolved when it is first parsed at which time all the other dimensions will be set to their default members (which is usually the "All" member). Using the EXISTING statement in your set definition should force it to be re-evaluated with the current context each time it is used.

eg

TOPCOUNT(EXISTING [Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

|||I am using SP2 but still experiencing the problem.|||

Darren,

I tried this but the same problem is occuring. Apparently, the default member of the time hierarchy determines what customer members to return. When I changed the default member to 2007, the named set showed the top customers for 2007. I came across another post which confirmed this as a problem. Does anybody have any workarounds? Thyank you.

David

|||

Hi David,

It seems that I did not read your full question and missed some information. So, just want to clarify. Named sets in AS 2005 are static named sets, which means that they are calculated just once in the context of the current member awailable at that moment. They are not reevaluated during execution of the select statement. Daren is right in the fact that named sets created in the cube scope or session scope (using Create statement) are evaluated when the CREATE statement is executed, therefore TopCount is calculated in the context of the default member. Sets created in the query scope using With clause are evaluated right after the WHERE clause and therefore take current member defined by the where clause in concideration (this is why your query with WITH statement works as you expect and query with named set from CREATE statement return different results). There are it's own pros on having static named sets (mostly performance), but in your case you should try to avoid using TopCount in the named set, if you want the members on other axis to affect results of the topcount.

Irina

P.S.

>>> I came across another post which confirmed this as a problem.

Could you please point me to this thread?

|||

Irina Gorbach wrote:

Named sets in AS 2005 are static named sets, which means that they are calculated just once in the context of the current member awailable at that moment. They are not reevaluated during execution of the select statement.

Of course! sorry for leading you slightly astray. The EXISTING statement will work if you have a set in a calculation

eg

CREATE MEMBER CurrentCube.Measures.CustomersInTop10Pcnt AS COUNT(TOPPERCENT(EXISTING Customer.Customer.Members, 150 Measures.Sales)

This would re-evaluate the set used in the toppercent function based on the current slicing conditions, which is similar, but different.

|||so is this the solution then?

TOPCOUNT(EXISTING [Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

or is there another way?

Also, how do you get it to sort correctly in excel, if you pull over the named set it just shows you the top X sorted by the SET Expression, whereas if you do an MDX query it sorts it by the Numeric Expression

Named Set with TOPCOUNT Not Working Correctly

I need to create reports that contain our top 15 customers at any given point in time. I created the following Named Set using the TopCount function in SSAS 2005:

TOPCOUNT([Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

When I bring this set into the client (Excel 2007 pivot table), its shows our top 15 customers for all time. When I add a date filter for just 2007, it doesn't update the list to just show the top customers for 2007. It just keeps the same "all time" group.

When I create this named set in a browser query, it works correctly. That is, it shows the top 15 customers for 2007. Is there any way I can get the Named Set created in SSAS to show the correct data based on the filtered date in the client?

Here is the browser query:

WITH SET

[Top Performers]

AS

'TOPCOUNT([Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])'

SELECT

{[Measures].[Net Ship Dollars]} ON COLUMNS,

{[Top Performers]}ON ROWS

FROM Shipments

Where ([Date].[Calendar Year].&[2007])

Thank you.

David

Are you using AS 2005 SP2? This problem has been fixed there.|||

The problem here is because the named set in the MDX Script gets resolved when it is first parsed at which time all the other dimensions will be set to their default members (which is usually the "All" member). Using the EXISTING statement in your set definition should force it to be re-evaluated with the current context each time it is used.

eg

TOPCOUNT(EXISTING [Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

|||I am using SP2 but still experiencing the problem.|||

Darren,

I tried this but the same problem is occuring. Apparently, the default member of the time hierarchy determines what customer members to return. When I changed the default member to 2007, the named set showed the top customers for 2007. I came across another post which confirmed this as a problem. Does anybody have any workarounds? Thyank you.

David

|||

Hi David,

It seems that I did not read your full question and missed some information. So, just want to clarify. Named sets in AS 2005 are static named sets, which means that they are calculated just once in the context of the current member awailable at that moment. They are not reevaluated during execution of the select statement. Daren is right in the fact that named sets created in the cube scope or session scope (using Create statement) are evaluated when the CREATE statement is executed, therefore TopCount is calculated in the context of the default member. Sets created in the query scope using With clause are evaluated right after the WHERE clause and therefore take current member defined by the where clause in concideration (this is why your query with WITH statement works as you expect and query with named set from CREATE statement return different results). There are it's own pros on having static named sets (mostly performance), but in your case you should try to avoid using TopCount in the named set, if you want the members on other axis to affect results of the topcount.

Irina

P.S.

>>> I came across another post which confirmed this as a problem.

Could you please point me to this thread?

|||

Irina Gorbach wrote:

Named sets in AS 2005 are static named sets, which means that they are calculated just once in the context of the current member awailable at that moment. They are not reevaluated during execution of the select statement.

Of course! sorry for leading you slightly astray. The EXISTING statement will work if you have a set in a calculation

eg

CREATE MEMBER CurrentCube.Measures.CustomersInTop10Pcnt AS COUNT(TOPPERCENT(EXISTING Customer.Customer.Members, 150 Measures.Sales)

This would re-evaluate the set used in the toppercent function based on the current slicing conditions, which is similar, but different.

|||so is this the solution then?

TOPCOUNT(EXISTING [Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

or is there another way?

Also, how do you get it to sort correctly in excel, if you pull over the named set it just shows you the top X sorted by the SET Expression, whereas if you do an MDX query it sorts it by the Numeric Expression
sql

Named Set Question

I want to create a named set that includes a list of some of our major customers. As part of the set, I also want to add a calculated member that represents a subtotal of customers. For example:

{[Customer].[Customer Hierarchy].[Store].&[Smith Stores],[Customer].[Customer Hierarchy].[Store].&[Williams Stores],[Customer].[Customer Hierarchy].[Store].&[Lion Stores]}

represents a named set of three customers. In the named set, I want to add Smith Stores and Williams Stores together to create a subtotal called Midwest Group. So, my revised set would look like:

{[Customer].[Customer Hierarchy].[Store].&[Smith Stores],[Customer].[Customer Hierarchy].[Store].&[Williams Stores],[Customer].[Customer Hierarchy].[Store].&[Lion Stores], [Customer].[Customer Hierarchy].[Store].&[Midwest Group]}.

First, can I create a calculation such as [Customer].[Customer Hierarchy].[Store].&[Smith Stores] + [Customer].[Customer Hierarchy].[Store].&[Williams Stores] AS 'Midwest Group' and add it directly to the named set. Or can I create this as a calculated member and add this member to the named set.

Is what I would like to do possible with named sets?

Thank you.

David

You will have to first create calculated member and then add it to the set. Alternative approach is to add new attribute to the Customer dimension with members Major/Minor. This way you will be able to get your Major customer by slicing on that attribute. You also will be able to do any grouping you want. This is more scalable approach once your company grows and you have more than 3 major customers |||

Mosha, when adding the calculated member to the named set, are there any restrictions. For example, does the calculated member need to be in the same hierarchy as the other members of the set. Also, are there any syntax differences when adding the calculated member. For example, is it treated like any other tuple and I would just separate it from the other members with a comma? Thank you.

David

|||There are no special restrictions. The calculated member will have to be from the same hierarchy as other members, but this is not special requirements for the calculated members, all tuples in the set must have same dimensionality (hierarchility). Syntax is the same - if you enumerate the members, than use comma as separator.|||

Mosha, Thank you for the answer. One problem remains. I get the named set working correctly when I run an MDX query in Management Studio. I have the named set appearing in the row axis and everything displays correctly. When I use the same named set in an Excel 2007 pivot table, I get an error message -- "A set has been encountered that cannot contain calculated members". I do have "show calculated members from OLAP Server" box checked in Pivot Table Options in Excel. Is this a bug with Excel 2007?

David

|||I am not sure what exactly you do in Excel - but it is possible that it is limitation of either Excel or AS (the error message is actually from AS, not from Excel).|||

Mosha,

Is it unusual that an MDX query would work correctly in SQL Server Management Studio but yield an error in a front end client?

David

|||Well, obviously the query that you wrote in Management Studio and the query that Excel generated are different queries.

Named Set in Excel Pivot Table Displays as Enumeration

Pardon me if this is really an Excel 2007 Pivot table question. I have a named set defined in the cube as:

CREATE SET CURRENTCUBE. [Team Building Overall]

AS {[Question].[Short Name].[Q-02],

[Question].[Short Name].[Q-03],

[Question].[Short Name].[Q-05],

[Question].[Short Name].[Q-06],

[Question].[Short Name].[Q-08],

[Question].[Short Name].[Q-13],

[Question].[Short Name].[Q-17],

[Question].[Short Name].[Q-18],

[Question].[Short Name].[Q-21],

[Question].[Short Name].[Q-30],

[Question].[Short Name].[Q-31]};

And can display measure values for this set as whole in Proclarity with:

SELECT { [Measures].[Average]} ON COLUMNS ,

{ [Corporate Hierarchy].[Hierarchy].DEFAULTMEMBER } ON ROWS

FROM [360 Training Survey]

WHERE ([Team Building Overall], [Test Month].[Test Month].&[200704] )

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

In Excel 2007, unfortunately I can not use this set as a filter as I can in Proclarity. Rather, the best I can accomplish is to display this as 11 different columns, one for each member of this named set.

Any suggestions ?

Anyone ?

I figure I can create a calculated measure in the cube named [Team Building Overall Average] which is defined as ([Team Building Overall],[measures].[Average]), but this seems a bit klutzy, especially given that I have eight sets and six measures, thus resulting in a total of 48 calculate measures I need to create.

|||

You could create a measure independant calculated member in the Question dimension as follows.

CREATE MEMBER CurrentCube.[Question].[Short Name].[Team Building Overall] AS AGGREGATE([Team Building Overall])

If the only reason that you have the sets is for filtering you could put the set definition inline in this memeber, otherwise you could leave them separate.

Named query as a pivot

Hello,

Is it possible to create a named query in the DSV that is the result of a pivot (e.g. cross tab?). The number of columns as a result of the pivot are based the number of records in one of the driving tables - in other words, it is not fixed.

You got to go dynamic SQL and use some fancy report datasets and expressions to display or not the columns.

Then you would have to create a report were you define as many columns as possible and control the visibility of these columns based on some expression stored in the Column Visibity hidden property.

Lot of work. May be you would be better off sourcing your report out of a cube.

Philippe

Named query as a pivot

Hello,

Is it possible to create a named query in the DSV that is the result of a pivot (e.g. cross tab?). The number of columns as a result of the pivot are based the number of records in one of the driving tables - in other words, it is not fixed.

You got to go dynamic SQL and use some fancy report datasets and expressions to display or not the columns.

Then you would have to create a report were you define as many columns as possible and control the visibility of these columns based on some expression stored in the Column Visibity hidden property.

Lot of work. May be you would be better off sourcing your report out of a cube.

Philippe

Named and default instance in SQL Server 2k

Hi,

I am trying to create a DB in a instance of sql server 2k. But the problem is, i have two instances, one default and the other named instance. So when i create a db in default instance, the same is getting
created automatically in named instance. Same happens when i try to do this in named instance. I mean both are acting like mirror instances. Can anyone try to sort this out.

thanks in advance,
bobbyWhat are the names of both instances?
Check SQL Error log for any information. Have you installed named instance seperately or tried a mirrored copy.

Name of report in head dynamically

Hi everybody,
does anybody knows if it is possible to create a report where the name
changes. I want to dynamically add a subtitle with data from a database.
E.g. (Static: Adress List, Dynamic: of Town XY). Apparently it is not
possible to insert fields from a dataset in the head of a report, so what
would be a good workaraound?
Thx for your support.
Regards
MichaelMichael,
Place a textbox in the body of the report. For the value of the
textbox, put the data from your DB, (ie
=First(Field!NameOfField.Value)).
Once this textbox has the data you want to display, refer to the
textbox in the header (ie =ReportItems!NameOfTextboxWithDataIWanttoDisplay.Value).

Name Flat file at run time

Hi,

I create a flat file in my package using Dataflow task and then I need to send that text file on some FTP site but the problem I am facing is I need to generate file name at run time like file name should be like "abc.yymmdd.txt" where yymmdd are Year,month and date.

Is there any way to give name to a flat file?

Thanks,

Pooja

your Connection Manager has a Property Collection called Expressions. Expand that, and set the ConnectionString to a variable expression.|||

"ABC." + (DT_WSTR,8) ( (YEAR(@.[User:ateTimeVar]) * 10000) + (MONTH(@.[User:ateTimeVar]) * 100) + DAY(@.[User:ateTimeVar]) ) + ".TXT"

Change the variable reference (@.[User:ateTimeVar]) to GETDATE() if you want today's date to be used.

sql

N UP style reports

Is there a way to create N UP style reports in Reporting Services. N UP
style reports have a newspaper column style of appearance where the same
database column with different data is displayed in multiple columns across
the same page of a report.See if the multi-column functionality meets your need. A fuller discussion
about multi-column reports can be found at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_layout_v1_4uwj.asp
NOTE: Use must render multi_column reports to PDF, TIFF, or Print Preview to
see them a multi-column. Multi-column reports are rendered as a single
column if you use HTML, Excel, or Preview.
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Reporting Services Developer - Atlanta" <Reporting Services Developer -
Atlanta@.discussions.microsoft.com> wrote in message
news:D216D411-55A0-4014-A787-ACFA5A165C15@.microsoft.com...
> Is there a way to create N UP style reports in Reporting Services. N UP
> style reports have a newspaper column style of appearance where the same
> database column with different data is displayed in multiple columns
> across
> the same page of a report.|||Bruce, thanks for the reply. These reports are typically not printed or
exported. The data is highly dynamic and is viewed/used by our support staff
online (in HTML).
I'll read the information on the link but I'm still interested in knowing if
there is some way to detect the context of a drilldown/row group.
Thanks,
Glenn
"Bruce Johnson [MSFT]" wrote:
> See if the multi-column functionality meets your need. A fuller discussion
> about multi-column reports can be found at
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_layout_v1_4uwj.asp
> NOTE: Use must render multi_column reports to PDF, TIFF, or Print Preview to
> see them a multi-column. Multi-column reports are rendered as a single
> column if you use HTML, Excel, or Preview.
>
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Reporting Services Developer - Atlanta" <Reporting Services Developer -
> Atlanta@.discussions.microsoft.com> wrote in message
> news:D216D411-55A0-4014-A787-ACFA5A165C15@.microsoft.com...
> > Is there a way to create N UP style reports in Reporting Services. N UP
> > style reports have a newspaper column style of appearance where the same
> > database column with different data is displayed in multiple columns
> > across
> > the same page of a report.
>
>|||The link below provides good information -
but is there a way to overide the "snaking" behavior that is discussed ?
Crystal allows you to specify whether data for these types of reports is
displayed down the page or across the page.
When converting reports from Cyrstal to SRS, it is important to make them
look exactly the same to improve my organization's confidence in SRS. Most
people will see this as a limitation of SRS.
"Bruce Johnson [MSFT]" wrote:
> See if the multi-column functionality meets your need. A fuller discussion
> about multi-column reports can be found at
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_layout_v1_4uwj.asp
> NOTE: Use must render multi_column reports to PDF, TIFF, or Print Preview to
> see them a multi-column. Multi-column reports are rendered as a single
> column if you use HTML, Excel, or Preview.
>
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Reporting Services Developer - Atlanta" <Reporting Services Developer -
> Atlanta@.discussions.microsoft.com> wrote in message
> news:D216D411-55A0-4014-A787-ACFA5A165C15@.microsoft.com...
> > Is there a way to create N UP style reports in Reporting Services. N UP
> > style reports have a newspaper column style of appearance where the same
> > database column with different data is displayed in multiple columns
> > across
> > the same page of a report.
>
>

Mysterious Views

I have two views in a production database that I did not create and I cannot account for. From the names used and the syntax of the views, it seems that the system (SQL Server) generated them.

Name: _hypmv_0_5771
Name: _hypmv_0

The syntax crushes the entire SELECT statement (4357 characters) onto a single line of T-SQL (not something I'm usually in the habit of doing).

I have read on kbAlertz that there was a bug in the original release of SQL server where the Index Tuning Wizard did not always remove hypothetical indices used to calculate performance improvements. I have not found anything related to hypothetical views. I do have indexed views, but none that reference these views.

I have checked dependencies; nothing depends on these views and they, in turn, do not depend on anything else.

Has anyone else encountered this problem? Aside from saving the definitions and then dropping them (just to see what may break), are there any suggestions for how to deal with this?

The database is medium-large. About 25GB.

Thanks,

Hugh ScottHere is the article on hypothetical indexes...

http://support.microsoft.com/?id=290414

mysqladmin: connect to server at localhost failed - MySQL

hi all,

I got the error like
[sait6@.localhost treestructure]$ mysqladmin -h localhost -u root -p create bedrock
Enter password: ******
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@.'localhost' (using password: YES)'

[sait6@.localhost treestructure]$ mysqladmin --no-defaults -u root ver
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@.'localhost' (using password: NO)'

and when i type mysql and start to change some password i got

mysql> INSERT INTO user (Host,User,Password)
-> VALUES('%','sait6','sait123');
ERROR 1046 (3D000): No database selected

please give suggestion to eradicate the above problem.

thanyou.
-RAJU

Quote:

Originally Posted by rajbala

hi all,

I got the error like
[sait6@.localhost treestructure]$ mysqladmin -h localhost -u root -p create bedrock
Enter password: ******
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@.'localhost' (using password: YES)'

[sait6@.localhost treestructure]$ mysqladmin --no-defaults -u root ver
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@.'localhost' (using password: NO)'

and when i type mysql and start to change some password i got

mysql> INSERT INTO user (Host,User,Password)
-> VALUES('%','sait6','sait123');
ERROR 1046 (3D000): No database selected

please give suggestion to eradicate the above problem.

thanyou.
-RAJU

I need your help............

MySQL 5.0 Views wont work with crystal reports

I can access my view data through the application, but when i try to create the report in crytal reports and set up the database through an odbc driver 3.51.

If i click on a normal table its fine, but with the views it says

"Error Opening File: There are no fields in the file: "[View name]".Hi,

I've got a similar problem but using a stored proc. (See earlier posting for more info)

After much hair-pulling and soul searching i think that their might be a compatibility problem with MySQL5, MyODBC 3.51 and Crystal.

Have you tried MyODBC 5.0 yet? If it installs and works on yr side that might take yr problem away. And if it does, please let us know what you did cause it dont wanna install here.

Thanks and good luck|||Thanks

I have tried the 5.0 ODBC Connector, but unfortunatly i had no joy. Still the same error.

MyLittleAdmin Error -2147217900 - Incorrect syntax near ''(''.

Hi,
I'm trying to create various tables on a hosted environment. Unfortunately the hosting company (lets just say 1 + 1 don't = 2) don't support remote connections so I can't access the DB using MS SQL Management Studio. So I have to use a 3rd party tool - MyLittleAdmin.

I can import scripts, and the majority of the time its ok, but I can't create certain tables that uses the generated scripts from Management Studio.

The error I received is:

Error -2147217900

Line 14: Incorrect syntax near '('.

The Script is:

Code Snippet

USE
[dbName]
GO
/****** Object: Table [dbo].[tfs_AreaManager] Script Date: 08/06/2007 14:29:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tfs_AreaManager](
[armId] [bigint] IDENTITY(1,1) NOT NULL,
[armUserId] [uniqueidentifier] NOT NULL,
[armAreaName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[armRgmId] [bigint] NULL,
[armFName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[armSName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[armEmail] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[armPhone] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tfs_AreaManagers] PRIMARY KEY CLUSTERED
(
[armId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[tfs_AreaManager] WITH CHECK ADD CONSTRAINT [FK_tfs_AreaManager_tfs_RegionManager] FOREIGN KEY([armRgmId])
REFERENCES [dbo].[tfs_RegionManager] ([rgmId])
GO
ALTER TABLE [dbo].[tfs_AreaManager] CHECK CONSTRAINT [FK_tfs_AreaManager_tfs_RegionManager]

I think line 14 refers to the 14th line counting from the 3rd GO instruction (code highlighted in RED).

Can anyone advise me as to what the problem is. I can create the table if I only use the script before the constraint operation. If I create the table, and then just script the constraints after, then they fail too. So I know it is the constraint opertaion that is the problem. Any help would be greatly appreciated!

Seems that you create the scripts with a SQL Server 2005 instance and the target one is a SQL Server 2000< one (or pretends to be in terms of compability level). So either change the compat level (if the target instance is a 2005 server) or create the scripts with compat level of your target database.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Oh that could be it! Do you know how I generate scripts for a SQL Server 2000 target?

Thanks for the help.

|||

If you are using the script generator you can use the "Script for Server Version" > SQL Server 2000

Jens K. Suessmeyer

http://www.sqlserver2005.de

My table design - where did I go wrong?

CREATE TABLE Sales1
(
varchar (10) CustID,
varchar (10) TransID,
datetime SaleDate,
money S1,
money S2,
money S3,
money S4,
numeric V1
)
CREATE NONCLUSTERED INDEX Sales1_CustID ON Sales1 (CustID)
CREATE NONCLUSTERED INDEX Sales1_SaleDate ON Sales1 (SaleDate)
"money" is just the right size for my fields. This table has 9,500,000
records.
Although I need to do this select in less than a half second, it takes 1
full minute:
SELECT MAX(S1) FROM Sales1 WHERE SaleDate > '1/1/2005'
And I need to do this select in less than a half second, but it takes 3
minutes:
SELECT AVG(S1 / S2) FROM Sales1 WHERE S3 > S4
Am I supposed to create a new field with these values pre-calculated? I hope
not, because I have several other formulas - up to 500 different types of
selects which are all similar.
I am testing with SQL 2000 Developer. XEON CPU, fast SATA hard drive and 2GB
of memory.
ThanksHi
The 1st query can use the index your created on SaleDate.
The 2nd query has no option, but to read every row (9.5 million) in the
table to find where S3 > S4, and then based on that subset, so the S1/S2
evaluation.
Either add and index, to restrict the select to something that can use one
of the indexes.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" <no@.spam.invalid> wrote in message
news:G_uGe.53624$4o.5775@.fed1read06...
> CREATE TABLE Sales1
> (
> varchar (10) CustID,
> varchar (10) TransID,
> datetime SaleDate,
> money S1,
> money S2,
> money S3,
> money S4,
> numeric V1
> )
> CREATE NONCLUSTERED INDEX Sales1_CustID ON Sales1 (CustID)
> CREATE NONCLUSTERED INDEX Sales1_SaleDate ON Sales1 (SaleDate)
> "money" is just the right size for my fields. This table has 9,500,000
> records.
> Although I need to do this select in less than a half second, it takes 1
> full minute:
> SELECT MAX(S1) FROM Sales1 WHERE SaleDate > '1/1/2005'
> And I need to do this select in less than a half second, but it takes 3
> minutes:
> SELECT AVG(S1 / S2) FROM Sales1 WHERE S3 > S4
> Am I supposed to create a new field with these values pre-calculated? I
> hope
> not, because I have several other formulas - up to 500 different types of
> selects which are all similar.
> I am testing with SQL 2000 Developer. XEON CPU, fast SATA hard drive and
> 2GB
> of memory.
> Thanks
>
>|||Shoot... SQL server might not work for me. I have to be able to calculate
sums, averages, etc. based on a number of different fields, and there are
millions of records. I have to get results within milliseconds. Is there a
way I can tell SQL server to load *everything* into memory?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OVlgKgHlFHA.1440@.TK2MSFTNGP14.phx.gbl...
> Hi
> The 1st query can use the index your created on SaleDate.
> The 2nd query has no option, but to read every row (9.5 million) in the
> table to find where S3 > S4, and then based on that subset, so the S1/S2
> evaluation.
> Either add and index, to restrict the select to something that can use one
> of the indexes.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rich" <no@.spam.invalid> wrote in message
> news:G_uGe.53624$4o.5775@.fed1read06...
of
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
But based on your weird, vague personal language:
1) You have no key because in NULL-able
2) You don't know the proper format for dates in SQL
3) You don't know about the math errors in the deprecated MONEY data
type - use DECIMAL(s,p) instead.
4) You don't know how to declare NUMERIC (s,p) data
5) Either you were sloppy or you don't know what a repeating group and
1NF are.
6) You don't know that rows are not records and columns are not fields.
if you cared about the people helping your for free, would your DDL
look like this?
CREATE TABLE SalesTransactions
(trans_nbr CHAR(10) NOT NULL PRIMARY KEY,
cust_id CHAR(10) NOT NULL
REFERENCES Customers (cust_id)
ON UPDATE CASCADE,
sales_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
CHECK (<<SET to 00:00:00 Hrs >> ),
repeating_grp_1 DECIMAL (12,4) DEFAULT 0.00 NOT NULL,
repeating_grp_2 DECIMAL (12,4) DEFAULT 0.00 NOT NULL,
repeating_grp_3 DECIMAL (12,4) DEFAULT 0.00 NOT NULL,
repeating_grp_4 DECIMAL (12,4) DEFAULT 0.00 NOT NULL,
vague_thing_1 NUMERIC(12,4) DEFAULT 0.00 NOT NULL);
You can use a CASE expession with aggregates to do all of these things
in one statement. You will simply have to make a table scan for some
of them, so indexing will not help. Do them all at once or put them in
a VIEW.
CREATE VIEW Godzilla (..)
AS
SELECT MAX(CASE WHEN sales_date SaleDate > '2005-01-01'
THEN repeating_grp_1 ELSE NULL END)
AS max_1,
AVG (CASE WHEN repeating_grp_3 > repeating_grp_S4
THEN repeating_grp_1/repeating_grp_2)
ELSE NULL END
AS foobar_avg,
etc.
FROM SalesTransactions1;
But this table feels like a set of design flaws.|||How about thinking a little outside the norm. Denormalisation may be useful
here.
The idea is that you have a table which contains the results of the query
already done.
Then you could add triggers or maybe a SQL Job to re-calculate the
aggregates, and fill in a table.
Averages (or more correctly the mean value) can be calculated easily, during
the insert. Update's and delete's may be a little more tricky, but then
that's there the JOB comes in. If it's not critical that the aggregate is
calculated NOW, but can be done lets say once a day then a SQL Job could be
setup to run re-calculate every so often. Does it really matter if it takes
a minute if no one is sat there waiting for it? OK, not what you wanted to
hear I know, but it's a possibility.
Another thought is that you may want to take a look into using composite
indexes. For you second query, try this index it may help to improve the
performance (my SQL box is at work so I can't try it)
Before you create the index, run the query and get SQL to display the Actual
Execution plan. You'll notice that the Query is performing a Table Scan,
(or if you've now created a clustered index, as Clustered Index scan). Use
SQL Profiler to take a reading of the Number of Reads that SQL needs to
execute your query.
Now Create the following index
Create NonClustered Index Sales1_S3_S4_S1_S2 On Sales1 ( S3, S4, S1, S2 )
If I've got it right, when you re-execute the query, the execution plan will
show that it's performing an Index Scan Or possibly even and Index S (but
I don't think this will happen) Now use SQL Profiler to get the number of
reads, you'll notice that it was alot lower. Also the execution time should
be lower too. Now turn off the Execution Plan, and stop the profiler to
get the production speed of the query.
I know that I've rambled on a bit, but I hope it gives you some ideas.
Regards
Colin Dawson
www.cjdawson.com
"Rich" <no@.spam.invalid> wrote in message
news:EjxGe.53631$4o.43600@.fed1read06...
> Shoot... SQL server might not work for me. I have to be able to calculate
> sums, averages, etc. based on a number of different fields, and there are
> millions of records. I have to get results within milliseconds. Is there a
> way I can tell SQL server to load *everything* into memory?
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:OVlgKgHlFHA.1440@.TK2MSFTNGP14.phx.gbl...
> of
>|||Thanks I'll give that a try. But I have to recalculate those 500 fields
every 5 minutes... do you suppose that "Real Time OLAP" would help in this
case?
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:iJxGe.79457$G8.17569@.text.news.blueyonder.co.uk...
> How about thinking a little outside the norm. Denormalisation may be
useful
> here.
> The idea is that you have a table which contains the results of the query
> already done.
> Then you could add triggers or maybe a SQL Job to re-calculate the
> aggregates, and fill in a table.
> Averages (or more correctly the mean value) can be calculated easily,
during
> the insert. Update's and delete's may be a little more tricky, but then
> that's there the JOB comes in. If it's not critical that the aggregate is
> calculated NOW, but can be done lets say once a day then a SQL Job could
be
> setup to run re-calculate every so often. Does it really matter if it
takes
> a minute if no one is sat there waiting for it? OK, not what you wanted
to
> hear I know, but it's a possibility.
> Another thought is that you may want to take a look into using composite
> indexes. For you second query, try this index it may help to improve the
> performance (my SQL box is at work so I can't try it)
> Before you create the index, run the query and get SQL to display the
Actual
> Execution plan. You'll notice that the Query is performing a Table Scan,
> (or if you've now created a clustered index, as Clustered Index scan).
Use
> SQL Profiler to take a reading of the Number of Reads that SQL needs to
> execute your query.
> Now Create the following index
> Create NonClustered Index Sales1_S3_S4_S1_S2 On Sales1 ( S3, S4, S1, S2 )
> If I've got it right, when you re-execute the query, the execution plan
will
> show that it's performing an Index Scan Or possibly even and Index S
(but
> I don't think this will happen) Now use SQL Profiler to get the number of
> reads, you'll notice that it was alot lower. Also the execution time
should
> be lower too. Now turn off the Execution Plan, and stop the profiler to
> get the production speed of the query.
> I know that I've rambled on a bit, but I hope it gives you some ideas.
> Regards
> Colin Dawson
> www.cjdawson.com
> "Rich" <no@.spam.invalid> wrote in message
> news:EjxGe.53631$4o.43600@.fed1read06...
calculate
are
a
the
S1/S2
9,500,000
takes
3
I
types
>|||Honestly, I've not looked at OLAP yet. I really think that I should do at
some point soon.
re-calculating 500 fields every five mins! ouch. I know that this idea
could end up in a complete tangle, but maybe it would be worth splitting the
workload down. For example if you're working out averages, use the real
data to work out the averages for portions of the data, then to get the
average for the whole, use the results from the portions to calculate the
whole. Also does the entire table have to be re-calculated? If not, don't
do the whole table. For example...
Say that you're talking about sales for the last w. Today is Sunday, and
as the information is entered into the system each day, and the previous
days information doesn't get altered, there's no need to re-calulate
yesterdays average, it's static.
Calculating the average for the w so far is as simple as calculating the
average for monday to saturday - i.e. The six seperate averages for added
together, then divided by 6. Then all you need to do is include todays
average, which will only ever be on a small dataset compared to the entire
table. Bet this can be calulated in a matter of ms.
I can't give you any specifics on how to get the best performance from your
database, for the simple reasong that I don't have a full and complete
understanding of your data. This is the key to getting maximum performance
out of any database.
Regards
Colin Dawson
www.cjdawson.com
"Rich" <no@.spam.invalid> wrote in message
news:exyGe.53638$4o.28608@.fed1read06...
> Thanks I'll give that a try. But I have to recalculate those 500 fields
> every 5 minutes... do you suppose that "Real Time OLAP" would help in this
> case?
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:iJxGe.79457$G8.17569@.text.news.blueyonder.co.uk...
> useful
> during
> be
> takes
> to
> Actual
> Use
> will
> (but
> should
> calculate
> are
> a
> the
> S1/S2
> 9,500,000
> takes
> 3
> I
> types
>

MY SQL trigger(PK generation problem)

hi all,
i have a good knowledge of Oracle but quite new to Ms-SQL, i want to create PK generation trigger in MS-SQL

Will somebody help me?

Thanks in Advance

MadhaviHeya, Madhavi. Welcome to TSDN!

I'm going to go ahead and move this thread to the MSSQL forum, where our resident Experts will be better able to help you out.

My SQL Provider

Hello to all,
I have a problem in connecting in a MySQL database. I can create some ODBC
connection, but I can't use it to manipulate data.
I like Know if Existe a MySQL Provider for SQL Server 2005 that alow me to
create connections and manipulate date withe this connections.
Thank you in advance.
It's not clear but I would guess you are trying to connect
from SQL Server and query MySQL?
First - what do you mean you can create an odbc connection
but can't manipulate data? Where did you create the
connection - linked server or what?
What errors did you get trying to do this? Can you execute
any selects?
To download MySQL drivers and providers, try the drivers and
connectors section at following site:
http://www.mysql.org/downloads/
-Sue
On Wed, 25 Apr 2007 07:08:01 -0700, malek_che
<malekche@.discussions.microsoft.com> wrote:

>Hello to all,
>I have a problem in connecting in a MySQL database. I can create some ODBC
>connection, but I can't use it to manipulate data.
>I like Know if Existe a MySQL Provider for SQL Server 2005 that alow me to
>create connections and manipulate date withe this connections.
>Thank you in advance.

My SQL Provider

Hello to all,
I have a problem in connecting in a mysql database. I can create some ODBC
connection, but I can't use it to manipulate data.
I like Know if Existe a mysql Provider for SQL Server 2005 that alow me to
create connections and manipulate date withe this connections.
Thank you in advance.It's not clear but I would guess you are trying to connect
from SQL Server and query MySQL?
First - what do you mean you can create an odbc connection
but can't manipulate data? Where did you create the
connection - linked server or what?
What errors did you get trying to do this? Can you execute
any selects?
To download mysql drivers and providers, try the drivers and
connectors section at following site:
http://www.mysql.org/downloads/
-Sue
On Wed, 25 Apr 2007 07:08:01 -0700, malek_che
<malekche@.discussions.microsoft.com> wrote:

>Hello to all,
>I have a problem in connecting in a mysql database. I can create some ODBC
>connection, but I can't use it to manipulate data.
>I like Know if Existe a mysql Provider for SQL Server 2005 that alow me to
>create connections and manipulate date withe this connections.
>Thank you in advance.

My Reports folder

Hi everyone,
I reinsralled my reporting services (on sql 2000) and i have a problem:
i added users and if one user create folder everyone else can see his
folder. also the my reports folder is not created auto like it was.
cant i create a folder that only i can see?
thanksThe folder that all of the My Reports are in is Users... If everyone can
see this, then either everyone is an administrator, or you need to reduce the
permissions on the Users folder.
To have the My Reports folders created automatically you must
Check the Enable My Reports box from the SIte Settings menu item on the top
left of Report Manager. There you can also setup the permissions that
individuals have on their My Reports folder.
Hope this helps
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Ruby Nadler" wrote:
> Hi everyone,
> I reinsralled my reporting services (on sql 2000) and i have a problem:
> i added users and if one user create folder everyone else can see his
> folder. also the my reports folder is not created auto like it was.
> cant i create a folder that only i can see?
> thanks
>