Can not change index INCLUDE fields order....

Hi...
I was in the process of synchronzing the indexes in two seperate databases;
production and development. I ran into a problem with one table.
The current production index looks like this:
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
[Field6], [Field7])
The current development database index looks like this
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
[Field6], [Field7])
I need the make changes on the production database so it uses the same
structure as the development database, so I delete the index on the
production database (successful).
I then execute the development index command on the production server.
What is hard to believe is I get the same structure as was there BEFORE. No
matter what I do, (including change the index name), the field
order in the "INCLUDE" portion never changes.
Has anyone seen this before? I realize that the order of the fields in the
"include" area isn't important, but the synchronization software reports
differences (which is a real PITA).
Thanks,
ForchHi
"Forch" wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE. No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in the
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
>
I thought this may be that on the develop machine the column [Field3] had
been dropped and re-created at some point, although my tests on a SP2 machine
have not replicated this!
John|||Are you sure that Field3 and Field4 have the same data definition in
both databases?
Are you sure the clustered index has the same definition in both
databases?
Are you sure Field3 comes before Field4 in the table definition in both
databases?
If you have table modification (such as changing a column's definition)
you could try to reindex the clustered index and recreate the
nonclustered index.
HTH,
Gert-Jan
Forch wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE. No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in the
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch

Can not change index INCLUDE fields order....

Hi...
I was in the process of synchronzing the indexes in two seperate databases;
production and development. I ran into a problem with one table.
The current production index looks like this:
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], &
#91;Field4], [Field5],
[Field6], [Field7])
The current development database index looks like this
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], &
#91;Field3], [Field5],
[Field6], [Field7])
I need the make changes on the production database so it uses the same
structure as the development database, so I delete the index on the
production database (successful).
I then execute the development index command on the production server.
What is hard to believe is I get the same structure as was there BEFORE. No
matter what I do, (including change the index name), the field
order in the "INCLUDE" portion never changes.
Has anyone seen this before? I realize that the order of the fields in the
"include" area isn't important, but the synchronization software reports
differences (which is a real PITA).
Thanks,
ForchHi
"Forch" wrote:

> Hi...
> I was in the process of synchronzing the indexes in two seperate databases
;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3],
[Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4],
[Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE.
No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in th
e
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
>
I thought this may be that on the develop machine the column [Field3] ha
d
been dropped and re-created at some point, although my tests on a SP2 machin
e
have not replicated this!
John|||Are you sure that Field3 and Field4 have the same data definition in
both databases?
Are you sure the clustered index has the same definition in both
databases?
Are you sure Field3 comes before Field4 in the table definition in both
databases?
If you have table modification (such as changing a column's definition)
you could try to reindex the clustered index and recreate the
nonclustered index.
HTH,
Gert-Jan
Forch wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases
;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3],
[Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4],
[Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE.
No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in th
e
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch

Can not change index INCLUDE fields order....

Hi...
I was in the process of synchronzing the indexes in two seperate databases;
production and development. I ran into a problem with one table.
The current production index looks like this:
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
[Field6], [Field7])
The current development database index looks like this
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
[Field6], [Field7])
I need the make changes on the production database so it uses the same
structure as the development database, so I delete the index on the
production database (successful).
I then execute the development index command on the production server.
What is hard to believe is I get the same structure as was there BEFORE. No
matter what I do, (including change the index name), the field
order in the "INCLUDE" portion never changes.
Has anyone seen this before? I realize that the order of the fields in the
"include" area isn't important, but the synchronization software reports
differences (which is a real PITA).
Thanks,
Forch
Hi
"Forch" wrote:

> Hi...
> I was in the process of synchronzing the indexes in two seperate databases;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE. No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in the
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
>
I thought this may be that on the develop machine the column [Field3] had
been dropped and re-created at some point, although my tests on a SP2 machine
have not replicated this!
John

Can I set DrillThrough Details column order?

AS2005 ... is there a way to set the order for drillthrough column details?

as far as I can see I can only select which attributes will be included ... is there a way to set the order?

would i have to redesign the dimension?

In the dimension desgin, in the tab attributes, select the field you want to sort, and right-click and Properties... there is the property orderBY and OrderByAttribute! change it!

regards!

Can I query XML data in SQL Server 2005 and get aggregate information? If so, how?

Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
say, an XML order per record (unshredded to extract the original data )
and then querying the XML to get aggregate information on individual
orders (e.g. sum of cost of all items in order) and then further
aggregating the information to get, say, quarterly results (e.g. sum of
cost of all items ordered from January 2005 to March 2005)? If the
answer to this is yes, and I don't expect it to be, how is this done
(i.e. can someone point me to information on how I could implement such
a thing)? Believe it or not, we can only get this information in XML
format. We would have to pay someone to design a relational database to
take this information, write a script to unshred the data from the XML
and populate the database tables, and then write queries to extract
aggregate data from the database.
You can store the data as XML by using the new xml data type. This data
type has various methods that you can use to query the data. The methods
include some XQuery based functions to extract information from an xml
column or variable (the "query", "value", and "exist" methods), which might
help if your aggergated data also needs to be in XML format. Alternatively,
theres a "nodes" method that you can use to extract a rowset from an xml
value (or you could use OPENXML). You can even create indexes on the xml
columns to improve XQuery performance.
All of this is described in SQL Server Books Online, and
http://msdn.microsoft.com/sql/learn/...l/default.aspx is a good place to
start.
-
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1132716530.406874.269410@.f14g2000cwb.googlegr oups.com...
> Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
> say, an XML order per record (unshredded to extract the original data )
> and then querying the XML to get aggregate information on individual
> orders (e.g. sum of cost of all items in order) and then further
> aggregating the information to get, say, quarterly results (e.g. sum of
> cost of all items ordered from January 2005 to March 2005)? If the
> answer to this is yes, and I don't expect it to be, how is this done
> (i.e. can someone point me to information on how I could implement such
> a thing)? Believe it or not, we can only get this information in XML
> format. We would have to pay someone to design a relational database to
> take this information, write a script to unshred the data from the XML
> and populate the database tables, and then write queries to extract
> aggregate data from the database.
>
|||"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in
news:1132716530.406874.269410@.f14g2000cwb.googlegr oups.com:

> Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
> say, an XML order per record (unshredded to extract the original data
> ) and then querying the XML to get aggregate information on individual
> orders (e.g. sum of cost of all items in order)
If the XML you store in a column in a row have order items nodes, then
sure no prob. You use the built-in xquery functionality on the xml
datatype to do these aggregations.

>and then further
> aggregating the information to get, say, quarterly results (e.g. sum
> of cost of all items ordered from January 2005 to March 2005)?
I assume you with this means you want to do querying/aggregation over
several rows? If so, this is not supported "out of the box", SQL Server
does not support composition on the xml data-type. However it can be
done by selecting in all the data into an xml variable and then do the
query/aggregation against that.

>If the
> answer to this is yes, and I don't expect it to be, how is this done
> (i.e. can someone point me to information on how I could implement
> such a thing)?
Read Books Online about the xml data type and XQuery.
Niels
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************
|||Niels and Graeme, I sincerely thank you for the trouble you have gone
to in addressing my issue and pointing me in the right direction.
|||To clarify, you can do the both aggregations with methods on XML data type.
To calculate prices of all orders when an order is an XML instance with
LineItem-s as XML elements you can write something like:
SELECT
sum(
xml_order.value('sum(/Order/LineItem/@.Price)','FLOAT')
)
FROM Orders
The inner XQuery "sum" would aggregate all price attribute values in an XML
instance and the outer T-SQL "sum" will aggregate across multiple XML
instances.
Best regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1132796074.018648.122550@.o13g2000cwo.googlegr oups.com...
> Niels and Graeme, I sincerely thank you for the trouble you have gone
> to in addressing my issue and pointing me in the right direction.
>
|||Hi Eugene,
Sorry to take so long to get back to you (I had to finalise and finally
finish a VBA for Excel project I've been working on for some months and
that's done now). This is my next project.
Thank you for feedback on the nested sums in the Select statement. I
expect my next project will be to develop a(n) SQL Server 2005 database
for storing XML orders which can be queried to produce a known set of
management reports. Consequently, I expect to be a regular visitor to
this group with my newbie questions.
Thank you again for your help.
Terry R.

Can I query XML data in SQL Server 2005 and get aggregate information? If so, how?

Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
say, an XML order per record (unshredded to extract the original data )
and then querying the XML to get aggregate information on individual
orders (e.g. sum of cost of all items in order) and then further
aggregating the information to get, say, quarterly results (e.g. sum of
cost of all items ordered from January 2005 to March 2005)? If the
answer to this is yes, and I don't expect it to be, how is this done
(i.e. can someone point me to information on how I could implement such
a thing)? Believe it or not, we can only get this information in XML
format. We would have to pay someone to design a relational database to
take this information, write a script to unshred the data from the XML
and populate the database tables, and then write queries to extract
aggregate data from the database.You can store the data as XML by using the new xml data type. This data
type has various methods that you can use to query the data. The methods
include some XQuery based functions to extract information from an xml
column or variable (the "query", "value", and "exist" methods), which might
help if your aggergated data also needs to be in XML format. Alternatively,
theres a "nodes" method that you can use to extract a rowset from an xml
value (or you could use OPENXML). You can even create indexes on the xml
columns to improve XQuery performance.
All of this is described in SQL Server Books Online, and
http://msdn.microsoft.com/sql/learn...ml/default.aspx is a good place to
start.
-
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1132716530.406874.269410@.f14g2000cwb.googlegroups.com...
> Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
> say, an XML order per record (unshredded to extract the original data )
> and then querying the XML to get aggregate information on individual
> orders (e.g. sum of cost of all items in order) and then further
> aggregating the information to get, say, quarterly results (e.g. sum of
> cost of all items ordered from January 2005 to March 2005)? If the
> answer to this is yes, and I don't expect it to be, how is this done
> (i.e. can someone point me to information on how I could implement such
> a thing)? Believe it or not, we can only get this information in XML
> format. We would have to pay someone to design a relational database to
> take this information, write a script to unshred the data from the XML
> and populate the database tables, and then write queries to extract
> aggregate data from the database.
>|||"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in
news:1132716530.406874.269410@.f14g2000cwb.googlegroups.com:

> Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
> say, an XML order per record (unshredded to extract the original data
> ) and then querying the XML to get aggregate information on individual
> orders (e.g. sum of cost of all items in order)
If the XML you store in a column in a row have order items nodes, then
sure no prob. You use the built-in xquery functionality on the xml
datatype to do these aggregations.

>and then further
> aggregating the information to get, say, quarterly results (e.g. sum
> of cost of all items ordered from January 2005 to March 2005)?
I assume you with this means you want to do querying/aggregation over
several rows? If so, this is not supported "out of the box", SQL Server
does not support composition on the xml data-type. However it can be
done by selecting in all the data into an xml variable and then do the
query/aggregation against that.

>If the
> answer to this is yes, and I don't expect it to be, how is this done
> (i.e. can someone point me to information on how I could implement
> such a thing)?
Read Books Online about the xml data type and XQuery.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Niels and Graeme, I sincerely thank you for the trouble you have gone
to in addressing my issue and pointing me in the right direction.|||To clarify, you can do the both aggregations with methods on XML data type.
To calculate prices of all orders when an order is an XML instance with
LineItem-s as XML elements you can write something like:
SELECT
sum(
xml_order.value('sum(/Order/LineItem/@.Price)','FLOAT')
)
FROM Orders
The inner XQuery "sum" would aggregate all price attribute values in an XML
instance and the outer T-SQL "sum" will aggregate across multiple XML
instances.
Best regards,
Eugene
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1132796074.018648.122550@.o13g2000cwo.googlegroups.com...
> Niels and Graeme, I sincerely thank you for the trouble you have gone
> to in addressing my issue and pointing me in the right direction.
>|||Hi Eugene,
Sorry to take so long to get back to you (I had to finalise and finally
finish a VBA for Excel project I've been working on for some months and
that's done now). This is my next project.
Thank you for feedback on the nested sums in the Select statement. I
expect my next project will be to develop a(n) SQL Server 2005 database
for storing XML orders which can be queried to produce a known set of
management reports. Consequently, I expect to be a regular visitor to
this group with my newbie questions.
Thank you again for your help.
Terry R.