Foreign Key + Index

Imagine 2 tables:
AUTHORS
author_id (int) (PK)
author_name (varchar)
BOOKS
book_id (int) (PK)
book_author_id (int) (FK from AUTHOR)
book_title (varchar)
book_author_id is already declared as a foreign key.
If I want better performance when querying SELECT * FROM BOOKS WHERE
book_author_id = 1234
do I have to set a index on book_author_id,
or is it unecessary as a FK is already set?
I think that as a FK is a constraint and not an index, it's still necessary
but I want to be sure.
Can you answer my question?
Thanks
Henria Foreign Key is NOT automatically indexed in SQL Server.
you'll need to index it.
Greg Jackson
Portland, OR|||Thanks for your answer Greg :-)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> a écrit dans le message de
news:OMI0r%234EFHA.3200@.TK2MSFTNGP10.phx.gbl...
> a Foreign Key is NOT automatically indexed in SQL Server.
> you'll need to index it.
>
> Greg Jackson
> Portland, OR
>
>

foreign key - relationship

I have two tables and I'm trying to create a one to many relationship (master table can have many records in the details table)

I created a column in my details table with with ID of the primary key in the master table.

The primary key ID isn't inserted as a foreign key when I insert a record. I specifed the relationship in EM.

Not sure why the primary key ID isn't inserted as a foreign key into my details table?

Any help is greatly appreciated. Thanks.
-Dman100-Explain how you are doing it?

ohhh you do realise you need to insert the value into the table -- it does not do it automatically|||Okay, my mistake, I thought it would be automatic.

Can I do this within my sql statement, using a join or insert or whatever?? to pass the primary key value from the master table into the details table as a foreign key?

Thanks for your help! I appreciate it.
-Dman100-

foreign key

hi friends,
I want to check the relationship between tables before migration.
so i wrote a procedure which will push the unrelated data from the source
db(@.i_oldDB) to the error database(@.i_errorDb).
alter procedure TransactionValidation
(
@.i_oldDb varchar(100),
@.i_errorDb varchar(100),
@.i_ParentTable varchar(100),
@.i_ChildTable varchar(100),
@.i_PrimaryKey varchar(100),
@.i_ForeignKey varchar(100)
)
as
begin
Declare @.SQL nvarchar(4000)
select @.SQL = 'if exists (select * from ' + @.i_errorDb +
'.INFORMATION_SCHEMA.TABLES where ' +
'Table_Name like ' + CHAR(39) + @.i_ChildTable + CHAR(39) + ') drop table ' +
@.i_errorDb + '..'+ @.i_ChildTable
exec sp_executesql @.sql
select @.sql = 'SELECT * into ' + @.i_errorDb + '..' + @.i_ChildTable + ' from
'
+ @.i_oldDb + '..'+ @.i_ChildTable + ' where ' + @.i_oldDb + '..'+
@.i_ChildTable + '.' + @.i_ForeignKey + ' not in
(select ' + @.i_PrimaryKey + ' from ' + @.i_oldDb + '..'+ @.i_ParentTable + ')'
exec sp_executesql @.sql
select @.sql = 'delete from ' + @.i_oldDb + '..'+ @.i_ChildTable + ' where ' +
@.i_oldDb + '..'+ @.i_ChildTable + '.'
+ @.i_ForeignKey + ' not in
(select ' + @.i_PrimaryKey + ' from ' + @.i_oldDb + '..'+ @.i_ParentTable + ')'
exec sp_executesql @.sql
end
now my problem is, if i have multiple relationship column in the table...
this will not work. how to do this?
its very urgent.
pls help me to solve this.
thanks
vanithaThere are a couple of alternatives to NOT IN for composite keys. You could
use NOT EXISTS or an OUTER JOIN. The generated SQL would be something like
the untested examples below.
SELECT *
INTO MyErrorTable
FROM MyChildTable
WHERE NOT EXISTS
(
SELECT *
FROM MyParentTable
WHERE MyParentTable.Col1 = MyChildTable.Col1 AND
MyParentTable.Col2 = MyChildTable.Col2
)
SELECT MyChildTable.*
INTO MyErrorTable
FROM MyChildTable
LEFT OUTER JOIN MyParentTable ON
MyParentTable.Col1 = MyChildTable.Col1 AND
MyParentTable.Col2 = MyChildTable.Col2
WHERE MyParentTable.Col1 IS NULL
Hope this helps.
Dan Guzman
SQL Server MVP
"vanitha" <vanitha@.discussions.microsoft.com> wrote in message
news:710A7736-41F8-478F-BFFF-3AA86A31FFE0@.microsoft.com...
> hi friends,
> I want to check the relationship between tables before migration.
> so i wrote a procedure which will push the unrelated data from the source
> db(@.i_oldDB) to the error database(@.i_errorDb).
> alter procedure TransactionValidation
> (
> @.i_oldDb varchar(100),
> @.i_errorDb varchar(100),
> @.i_ParentTable varchar(100),
> @.i_ChildTable varchar(100),
> @.i_PrimaryKey varchar(100),
> @.i_ForeignKey varchar(100)
> )
> as
> begin
> Declare @.SQL nvarchar(4000)
> select @.SQL = 'if exists (select * from ' + @.i_errorDb +
> '.INFORMATION_SCHEMA.TABLES where ' +
> 'Table_Name like ' + CHAR(39) + @.i_ChildTable + CHAR(39) + ') drop table '
> +
> @.i_errorDb + '..'+ @.i_ChildTable
> exec sp_executesql @.sql
> select @.sql = 'SELECT * into ' + @.i_errorDb + '..' + @.i_ChildTable + '
> from
> '
> + @.i_oldDb + '..'+ @.i_ChildTable + ' where ' + @.i_oldDb + '..'+
> @.i_ChildTable + '.' + @.i_ForeignKey + ' not in
> (select ' + @.i_PrimaryKey + ' from ' + @.i_oldDb + '..'+ @.i_ParentTable +
> ')'
>
> exec sp_executesql @.sql
> select @.sql = 'delete from ' + @.i_oldDb + '..'+ @.i_ChildTable + ' where '
> +
> @.i_oldDb + '..'+ @.i_ChildTable + '.'
> + @.i_ForeignKey + ' not in
> (select ' + @.i_PrimaryKey + ' from ' + @.i_oldDb + '..'+ @.i_ParentTable +
> ')'
> exec sp_executesql @.sql
>
> end
> now my problem is, if i have multiple relationship column in the table...
> this will not work. how to do this?
> its very urgent.
> pls help me to solve this.
> thanks
> vanitha
>

foreign key

I want to make a foreign key relationship between two tables but the key is
multiple fields. I am getting an error message when I try.
'''''?Can you post DDL for your tables and the code you're trying to use to create
the foreign key constraint?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sql" <sql@.discussions.microsoft.com> wrote in message
news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
> I want to make a foreign key relationship between two tables but the key
is
> multiple fields. I am getting an error message when I try.
> '''''?|||ALTER TABLE SecondaryTableName
ADD CONSTRAINT ConstraintName
FOREIGN KEY (ForeignKeyColumns)
REFERENCES dbo.PrimaryTable (PrimaryKeyColumnName)
Be sure to list the composite columnc in the same order.
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
Enterprise Data Architect, www.Compassion.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23S3OJUNyEHA.2788@.TK2MSFTNGP15.phx.gbl...
> Can you post DDL for your tables and the code you're trying to use to
> create
> the foreign key constraint?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "sql" <sql@.discussions.microsoft.com> wrote in message
> news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
>> I want to make a foreign key relationship between two tables but the key
> is
>> multiple fields. I am getting an error message when I try.
>> '''''?
>|||Script and error message
ALTER TABLE MNP_MINE_PROD
ADD CONSTRAINT FK_TEST
FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
'FK_TEST'. The conflict occurred in database 'S_DEV', table 'MOR_MINE_OP_RPT'.
"sql" wrote:
> I want to make a foreign key relationship between two tables but the key is
> multiple fields. I am getting an error message when I try.
> '''''?|||You have some rows in MNP_MINE_PROD that aren't in MOR_MINE_OP_RPT. So the
FK can't be created... try this:
SELECT *
FROM MNP_MINE_PROD A
WHERE NOT EXISTS
(SELECT *
FROM MOR_MINE_OP_RPT B
WHERE A.MNE_ID = B.MNE_ID
AND A.MOR_YEAR=B.MOR_YEAR
AND A.ORT_ID = B.ORT_ID)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sql" <sql@.discussions.microsoft.com> wrote in message
news:57509E6C-7118-4B0C-A0DF-B3EF4FCF9464@.microsoft.com...
> Script and error message
> ALTER TABLE MNP_MINE_PROD
> ADD CONSTRAINT FK_TEST
> FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
> REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
> 'FK_TEST'. The conflict occurred in database 'S_DEV', table
'MOR_MINE_OP_RPT'.
>
> "sql" wrote:
> > I want to make a foreign key relationship between two tables but the key
is
> > multiple fields. I am getting an error message when I try.
> > '''''?

foreign key

I want to make a foreign key relationship between two tables but the key is
multiple fields. I am getting an error message when I try.
??????
Can you post DDL for your tables and the code you're trying to use to create
the foreign key constraint?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"sql" <sql@.discussions.microsoft.com> wrote in message
news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
> I want to make a foreign key relationship between two tables but the key
is
> multiple fields. I am getting an error message when I try.
> ??????
|||ALTER TABLE SecondaryTableName
ADD CONSTRAINT ConstraintName
FOREIGN KEY (ForeignKeyColumns)
REFERENCES dbo.PrimaryTable (PrimaryKeyColumnName)
Be sure to list the composite columnc in the same order.
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
Enterprise Data Architect, www.Compassion.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23S3OJUNyEHA.2788@.TK2MSFTNGP15.phx.gbl...
> Can you post DDL for your tables and the code you're trying to use to
> create
> the foreign key constraint?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "sql" <sql@.discussions.microsoft.com> wrote in message
> news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
> is
>
|||Script and error message
ALTER TABLE MNP_MINE_PROD
ADD CONSTRAINT FK_TEST
FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
'FK_TEST'. The conflict occurred in database 'S_DEV', table 'MOR_MINE_OP_RPT'.
"sql" wrote:

> I want to make a foreign key relationship between two tables but the key is
> multiple fields. I am getting an error message when I try.
> ??????
|||You have some rows in MNP_MINE_PROD that aren't in MOR_MINE_OP_RPT. So the
FK can't be created... try this:
SELECT *
FROM MNP_MINE_PROD A
WHERE NOT EXISTS
(SELECT *
FROM MOR_MINE_OP_RPT B
WHERE A.MNE_ID = B.MNE_ID
AND A.MOR_YEAR=B.MOR_YEAR
AND A.ORT_ID = B.ORT_ID)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"sql" <sql@.discussions.microsoft.com> wrote in message
news:57509E6C-7118-4B0C-A0DF-B3EF4FCF9464@.microsoft.com...
> Script and error message
> ALTER TABLE MNP_MINE_PROD
> ADD CONSTRAINT FK_TEST
> FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
> REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
> 'FK_TEST'. The conflict occurred in database 'S_DEV', table
'MOR_MINE_OP_RPT'.[vbcol=seagreen]
>
> "sql" wrote:
is[vbcol=seagreen]

foreign key

I want to make a foreign key relationship between two tables but the key is
multiple fields. I am getting an error message when I try.
'''''?Can you post DDL for your tables and the code you're trying to use to create
the foreign key constraint?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sql" <sql@.discussions.microsoft.com> wrote in message
news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
> I want to make a foreign key relationship between two tables but the key
is
> multiple fields. I am getting an error message when I try.
> '''''?|||ALTER TABLE SecondaryTableName
ADD CONSTRAINT ConstraintName
FOREIGN KEY (ForeignKeyColumns)
REFERENCES dbo.PrimaryTable (PrimaryKeyColumnName)
Be sure to list the composite columnc in the same order.
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
Enterprise Data Architect, www.Compassion.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23S3OJUNyEHA.2788@.TK2MSFTNGP15.phx.gbl...
> Can you post DDL for your tables and the code you're trying to use to
> create
> the foreign key constraint?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "sql" <sql@.discussions.microsoft.com> wrote in message
> news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
> is
>|||Script and error message
ALTER TABLE MNP_MINE_PROD
ADD CONSTRAINT FK_TEST
FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
'FK_TEST'. The conflict occurred in database 'S_DEV', table 'MOR_MINE_OP_RPT
'.
"sql" wrote:

> I want to make a foreign key relationship between two tables but the key i
s
> multiple fields. I am getting an error message when I try.
> '''''?|||You have some rows in MNP_MINE_PROD that aren't in MOR_MINE_OP_RPT. So the
FK can't be created... try this:
SELECT *
FROM MNP_MINE_PROD A
WHERE NOT EXISTS
(SELECT *
FROM MOR_MINE_OP_RPT B
WHERE A.MNE_ID = B.MNE_ID
AND A.MOR_YEAR=B.MOR_YEAR
AND A.ORT_ID = B.ORT_ID)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sql" <sql@.discussions.microsoft.com> wrote in message
news:57509E6C-7118-4B0C-A0DF-B3EF4FCF9464@.microsoft.com...
> Script and error message
> ALTER TABLE MNP_MINE_PROD
> ADD CONSTRAINT FK_TEST
> FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
> REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
> 'FK_TEST'. The conflict occurred in database 'S_DEV', table
'MOR_MINE_OP_RPT'.[vbcol=seagreen]
>
> "sql" wrote:
>
is[vbcol=seagreen]sql

foreign and primary key question

OK - I have a two tables in a database. Table one contains an ID, 'oneID', field as the primary key. It is auto-incremented. Table two has ID field, 'twoID', as the primary key. This field also auto-increments. Table two also has 'oneID' as the foreign key.

Now, my question is, how do I get the foreign key in table two to auto-increment in conjuction with table one's primary key? They are after all the same data. Do I have to manually code to get table one data and save it to table two data?

thanks

Yes you'd have to manually INSERT the data into the other table. By setting up the PL-FK constraint you are just setting up a "relation" between the tables so any inserts/updates/deletes into the tables are checked for their data consistency.

Assuming your first INSERT is going through a stored proc, get the ID of the value just inserted via SCOPE_IDENTITY() and immediately do the INSERT into the second table. You could also do this via triggers but I dont recommend it. they are a big performance overhead and drag your system.

Forecast Model

Hi

I am new to Data mining in SQL Server. I am using SQL 2005 to create a forecast model for Product sales. I two fact tables that I am using. One has all the Orders historically with the line item details. The other table is a time dimension table which has the value of each of the time values referenced in the Orders. So the time dimesion has multiple values for one day as it goes down to the hour the order was placed.

Can I create a forecast using the time series algorithm in the Data mining module. When I tried to use this algorithm, I got an error that the time is not synchronized with starting series "Unknown" and I should try to set the missing_value_Substitution parameter to previous.

Can some one explain to me how this will resolve my issue. I read in one of the articles on Data mining that in order to use the time series algorithm , I need to have unique set of values for the time. Can some one help me with this

Thanks

AY

You should probably do some upfront data preparation before you build your time series model. Do you want to do a daily sales forecast per product? In that case, you should aggregate sales figures per item at that level.

If you have data for multiple products, then you have a series for each one and each series needs to have values for all time slices present in the data. For example, if you have daily data, you need to have a sales figure for each product for each day and all the series need to begin/end at the same point. The error you're seeing is due to this issue. Specifying MISSING_VALUE_SUBSTITUTION will allow missing data points for a time slice across multiple series to be filled in with the specified value.

|||Thanks for the reply. Yes it is a Daily Sales Forecast that I am trying to build. I will try your suggestion

Foreach loop over Excel files seems 'fragile'

All,

I have a package that loops over ~60 Excel files in a directory. Each
file has three named ranges in it, which I import into different
tables. Sometimes the package runs without a hitch, sometimes it
chokes. But it is intermittent.

If I pull the control flow components out of the foreach loop and
point the Excel connection manager to the specific Excel file that has
caused the package to choke, I get a message in the dataflow component
pointing to the named range that "the metadata of the following output
columns does not match the metadata of the external columns......Do
you want to replace the metadata of the output columns with the
metadata of the external columns?" When I choose 'yes', then the
file will be loaded. then I can put the control flow components back
into the foreach loop and the file will run again, successfully, along
with some more, until it chokes again....

So, first of all, does anyone have any insight into this? Sometimes,
somedays, these files will load with no problems. These exact files;
I am having to reload constantly... Other times, like today, it is a
battle.

Otherwise, is there a way to get Integration Svcs to handle the
metadata issue on the fly?

Any ideas, resources, references, war stories, or good clean jokes
would be appreciated,
Kathryn

Metadata cannot change... Do you have changing metadata in your Excel documents, or does SSIS just think it is changing?|||

Phil,

Thanks for the quick reply. It seems that SSIS thinks the metadata is changing..

As far as I can tell, the problem is caused when a field in the file does/does not have a hyphen in it. For example, some files give us EIN with a hyphen and some don't. The package will chug along until it gets an EIN with a hyphen, then it will choke. I will pull the control flow components out of the foreach, point the excel source at the file that's causing it to choke, then i will answer yes to the metadata warning. Then I'll put the control flow components back into the foreach and it will chug along until it gets to a file WITH a hyphen in the EIN, when it will choke again....

All fields are defined to be strings. I even put a Data Conversion component after the Excel Source component to strip out hyphens, but the data flow doesn't get to the Data Conversion; it chokes on the Excel Source.

Kathryn

|||

Hey Kathryn,

Try this... I don't know if it'll work or if you've already tried this, but try to process the erroneous file first (if possible) in the loop. I don't know if you can control that or not. Here's what I'm thinking. I think that SSIS looks at the first file, sees that FieldA1 is a numeric, and sets the metadata to numeric for that field. When you encounter a text value for that same field in a subsequent file, it bombs. So I'm wondering if you can process a file first that contains the text value of that field, for example. Then it'll think that field is a text field and process it the same for the rest? It's just a thought!

Rebecca

|||Maybe setting IMEX=1 in the excel connection string is the answer here as well.|||

Phil,

Thanks for the suggestion. Unfortunately, it didn't work, though it seems that that should be the answer....

Kathryn

|||

I'm very surprised that IMEX=1 did not work, since forcing everything to be loaded as a string should avoid the issue with the mixed data types that you otherwise have in your EIN column (numeric values when there's no dash, string values when there is one).

The only potential issue that comes to mind is the difference between string and memo fields, for which there must be at least 1 row with a memo value in the rows sampled by the driver for the driver to recognize that column as a memo column.

Let's remember that Excel has no column metadata. The driver can only guess.

-Doug

sql

ForEach Loop or For Loop?

I have source and destination table names in the database(one table) and I need to read the source and destination tables one by one...

My Lookp table is like the following...

Srn srctable desttable

1 SRC1 DEST1

2 SRC2 DEST2

3 SRC3 DEST3

Now I want one package to load from source to destination.. how do I do it.. I dont know how to use....

How do I run the pacakge for each of the rows... ..............................

Is the metadata the same for each data transfer? If not then you're going to have to build as many data-flows as there are rows in your lookup table and if that is teh case - what's the point in looping over them?

-Jamie

Forcing Primary Keys

Hi all,

As our DB has no primary keys or indexes ive taken a copy of all populated tables and tried to force primary keys within a new DB.

the problem is all off the tables have multiple datasets within them, a dataset for each year. This causes all instances of ID numbers to not be unique as they are replicated for every year they are active.

Its a school database so a student who has been here for 3 years will have 3 instances of his ID number, one for each years' data set.

So how do i force primary keys if there is no unique identifier? ive been highlighting both data set and ID columns and setting that combination as the primary key.

Essentially i need to analyse the relationships between the tabls in a diagram and also run some speed tests to see how fast the db works when it has indexes and primary keys.

the reason im writing is that ive done this on ten tables and with another 160 to do im just checking im doing the right thing?

gregCreate a composite primary key of student ID and year number.|||thought so,
ta

greg|||Why do you keep enrollment info (a record for each year of enrollment) in the master table? StudentID should be the only PK in StudentsMaster, and Enrollment should have StudentID as FK.|||Could you create views for each year and put a unique index on each view?|||Yes, you CAN.
No, you SHOULDN'T.|||rdjabarov its not my design, its just the way the company programmed it, its a very bad system, ive alreday had to weed out 400+ tables that werent being used, and it seems instead of introducing foreign keys to child tables they used the studentId and the SetId,

peterlemonjello, i didnt know you could do that, well at least in sql server 2000, thought it was a 2005 feature...ill look into that

blindman, i had read it wasn't a good idea...ill think of an alternative

greg|||Where ever did you read that? Tables need primary keys, and if they don't have a natural unary key then you either create a surrogate key or use a composite key. Creating indexed views would be an odd alternative.|||well this is the thing, im not trying to fix the db so it functions- im just truying to analyse the relationships between tables and see how much faster introducing keys and indexes make my queries run...

as you can imagine the company released the software with no primary keys and expect it to work but im not about to try and fix there mistakes...its purely for my own use...

i really cant believe they have released software like this but i have to work with what i inhereted off my predecessor

greg|||It will run faster if it is indexed, especially clustered indexes as associated with primary keys.
No need to test this concept...

What's more, you can throw indexes on it without affecting the functioning of the operation. You cannot throw constraints on the tables (unique indexes, for example, or primary keys) without potentially causing failures in the crappy code which is doubtless used to access the data.|||Hmmm, really? I wouldn't be so certain, especially without seeing the database, and without knowing what indexes are to be created and what their definitions are. I've seen "index seek" being more expensive than table scan on multiple occasions (of course because of the poor db and/or query design).|||Nothing is certain in life except death and taxes, but the benefits of indexing a table come damn close.|||In general that might be true, but then you find a table with 947 indexes, all of which have the first seven columns... Then discover that only the leftmost index column is ever used in queries!

-PatP|||Yeah, yeah,...|||I've seen "index seek" being more expensive than table scan on multiple occasions (of course because of the poor db and/or query design).The only time I've seen this is as a result of parameter sniffing. Are there other reasons this can occur? ... actually thinking about it now I guess a poorly chosen index (e.g. low selectivity) and an equally poor plan on the part of the optimiser might cause this.

BTW - I am probably just being a pedant but if there are no primary keys then there are no relationships. You will not be investigating the relationships of the tables - you will be creating the relationships. I imagine this is not helpful to the issue in hand at all :)|||Hi all,

yes bit of a can of worms here, to summarize it is the relationships im interested in, i wanna see how the tables should be connected by matching up similar indexes so although ill be cretaing the relationships, as most tables only have one index, it should be pretty close to the original design...

the problem is i need to prove to the management that my systems (access mde's,ade's accessing sql backend) are faster than the db we pay for because there is no primary keys or relationships..and was hoping that by recreating the relationships i could run speed tests to compare against...

cheers

greg|||Relationships don't affect the speed of your db directly. Relationships are logical constraints - they merely ensure your data conforms to certain constraints. As such - you are quite likely to find a fair slew of invalid intries in your tables since these contraints have not existed previously.

However - relationships are typically between primary and foreign keys. Both of these should be indexed. It is these indexes that should be likely to improve the speed of your queries.

HTH

forcing a truncate

I have a script that dumps data from several tables into one, where the
source tables are not always the same data length. They are all varchar but
some are 30 chars, some 255, etc. Below is my insert query, which errors
because it won't truncate the 255 character data into 30. Is there a simple
way to automatically truncate that data that doesn't fit? All source tables
are different, so I don't want to have to go through 10 or more fields to
determine what their length is.
insert into tblcontact (firstname, lastname, streetaddress,
organizationname, city, statecode,
postalcode, homephone, businessphone,
mobilephone, faxnumber, emailaddress, username,
datechanged)
(select r_firstname, r_lastname, r_address, r_organization, r_city,
r_state,
r_zip, r_phone_h, r_phone_o,
r_phone_m, r_phone_f, r_email, Username, change_date from SourceTable1
where contactid is null and ...[query truncated])
Thanks for your help.dew,
The problem is not so much the source tables, but the destination table.
Your SELECT statement indicates one source to one destination table and so
it's (reasonably) simple.
The simple answer is to use the LEFT function as such::
INSERT INTO tblcontact (firstname, lastname, streetaddress,
organizationname, city, statecode,
postalcode, homephone, businessphone,
mobilephone, faxnumber, emailaddress, username,
datechanged)
(select LEFT(r_firstname, 30), LEFT(r_lastname, 30), LEFT(r_address, 30),
LEFT(r_organization, 30), LEFT(r_city, 30), LEFT(r_state, 30), LEFT(r_zip,
30), LEFT(r_phone_h, 30), LEFT(r_phone_o, 30), LEFT(r_phone_m, 30),
LEFT(r_phone_f, 30), LEFT(r_email, 30), LEFT(Username, 30), LEFT(change_date
,
30)
FROM SourceTable1
WHERE contactid IS NULL
AND ...[query truncated])
This will work in the main, but, of course, the LEFT unstion only needs to
be used on those columns that are obviously (or likely) to have in excess of
30 characters at the source table.
Obviously, the other way is simply to alter the destination table(s) to
accomodate the larger size.
Hope this assists,
Tony
"dew" wrote:

> I have a script that dumps data from several tables into one, where the
> source tables are not always the same data length. They are all varchar b
ut
> some are 30 chars, some 255, etc. Below is my insert query, which errors
> because it won't truncate the 255 character data into 30. Is there a simp
le
> way to automatically truncate that data that doesn't fit? All source tabl
es
> are different, so I don't want to have to go through 10 or more fields to
> determine what their length is.
> insert into tblcontact (firstname, lastname, streetaddress,
> organizationname, city, statecode,
> postalcode, homephone, businessphone,
> mobilephone, faxnumber, emailaddress, username,
> datechanged)
> (select r_firstname, r_lastname, r_address, r_organization, r_city,
> r_state,
> r_zip, r_phone_h, r_phone_o,
> r_phone_m, r_phone_f, r_email, Username, change_date from SourceTable1
> where contactid is null and ...[query truncated])
> Thanks for your help.
>
>

Force Uniqueness on one column

HI:
If when joining parent and child tables, a query returns multiple entries
for a given parent, how can I limit query to showing only first child? Kind
of like grouping on one field in result set.
Thanks,
CharlieDefine "first".
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:eIegtNg6FHA.632@.TK2MSFTNGP10.phx.gbl...
> HI:
> If when joining parent and child tables, a query returns multiple entries
> for a given parent, how can I limit query to showing only first child?
> Kind
> of like grouping on one field in result set.
> Thanks,
> Charlie
>|||Hi Tom, let me restate..
If query joins a parent table with a child table in a one-to-many relation
the results set will show the parent id repeating for each child. I want
the query to show only one child despite having many. How do I filter join
to limit result set to only one child per parent even though it a parent may
have many child records.
Thanks,
charlie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:unojjUg6FHA.636@.TK2MSFTNGP10.phx.gbl...
> Define "first".
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Charlie@.CBFC" <charle1@.comcast.net> wrote in message
> news:eIegtNg6FHA.632@.TK2MSFTNGP10.phx.gbl...
entries
>|||SELECT
Parent.ID
, Child.ID
, Child.Data
FROM
Parent
INNER JOIN
(
SELECT
Child.Parent_ID
, Child.ID
, Child.Data
FROM
Child
INNER JOIN
(
SELECT Parent_ID , MIN( ID ) AS ID FROM Child GROUP BY
Parent_ID
) LowestChildForParent
ON
Child.Parent_ID = LowestChildForParent.Parent_ID
AND
Child.ID = LowestChildForParent.ID
) Child
ON
Parent.ID = Child.Parent_ID
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:ucEjkag6FHA.744@.TK2MSFTNGP10.phx.gbl...
> Hi Tom, let me restate..
> If query joins a parent table with a child table in a one-to-many relation
> the results set will show the parent id repeating for each child. I want
> the query to show only one child despite having many. How do I filter
join
> to limit result set to only one child per parent even though it a parent
may
> have many child records.
> Thanks,
> charlie
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:unojjUg6FHA.636@.TK2MSFTNGP10.phx.gbl...
> entries
>|||Again, define "first". You haven't posted your DDL. We have no idea which
of the child rows is the "first" for a given parent ID.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:ucEjkag6FHA.744@.TK2MSFTNGP10.phx.gbl...
> Hi Tom, let me restate..
> If query joins a parent table with a child table in a one-to-many relation
> the results set will show the parent id repeating for each child. I want
> the query to show only one child despite having many. How do I filter
> join
> to limit result set to only one child per parent even though it a parent
> may
> have many child records.
> Thanks,
> charlie
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:unojjUg6FHA.636@.TK2MSFTNGP10.phx.gbl...
> entries
>|||Using min() or max() value for a set of keys in grouping should work. This
will first or last child.
Thanks
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:437a10a3$0$133$7b0f0fd3@.mistral.news.newnet.co.uk...
> SELECT
> Parent.ID
> , Child.ID
> , Child.Data
> FROM
> Parent
> INNER JOIN
> (
> SELECT
> Child.Parent_ID
> , Child.ID
> , Child.Data
> FROM
> Child
> INNER JOIN
> (
> SELECT Parent_ID , MIN( ID ) AS ID FROM Child GROUP BY
> Parent_ID
> ) LowestChildForParent
> ON
> Child.Parent_ID = LowestChildForParent.Parent_ID
> AND
> Child.ID = LowestChildForParent.ID
> ) Child
> ON
> Parent.ID = Child.Parent_ID
>
> "Charlie@.CBFC" <charle1@.comcast.net> wrote in message
> news:ucEjkag6FHA.744@.TK2MSFTNGP10.phx.gbl...
relation
want
> join
> may
child?
>

Force Row Level locking in SQLServer 2000 ?

Hi

Is it possible to force row level locking in one or more tables in
some database. We have some problems when SQL Server decides to choose
page- or table-level locking.
We are using SQL Server 2000.

Best regards

AarnoArska (aarno.autio@.bof.fi) writes:
> Is it possible to force row level locking in one or more tables in
> some database. We have some problems when SQL Server decides to choose
> page- or table-level locking.
> We are using SQL Server 2000.

You can add a locking hint

SELECT * FROM tbl (ROWLOCK) WHERE col = 32

However, SQL Server may disregard that hint if row locks are possible
to achieve.

You may need to review you indexing strategy. For instance, in the example
above, I would not expect the hint to help if there is no index on col.
SQL Server will have to scan the entire table, so a tablock is called for.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

FOR XML query with more than 2 levels of data

I'm having trouble getting a FOR XML query to get the relationships correct when there are 3 levels of data.

In this example, I have 3 tables, GG_Grandpas, DD_Dads, KK_Kids. As you would expect, the Dads table is a child of the Grandpas table, and the Kids table is a child of the Dads table.

I'm using the Bush family in this example, these are the relationships:
- George SR
-- George JR
-- Jenna
-- Barbara
-- Jeb
-- Jeb JR
-- Noelle

These statements will create and populate the tables for the example with the above relationships:

SET NOCOUNT ON
DROP TABLE KK_Kids, DD_Dads, GG_Grandpas
CREATE TABLE GG_Grandpas ( GG_Grandpa_Key varchar(20) NOT NULL, GG_GrandpaName varchar(20))
CREATE TABLE DD_Dads ( DD_Dad_Key varchar(20) NOT NULL, DD_Grandpa_Key varchar(20) NOT NULL, DD_DadName varchar(20))
CREATE TABLE KK_Kids ( KK_Kid_Key varchar(20) NOT NULL, KK_Dad_Key varchar(20) NOT NULL, KK_KidName varchar(20))

ALTER TABLE GG_Grandpas ADD CONSTRAINT PK_GG PRIMARY KEY (GG_Grandpa_Key)
ALTER TABLE DD_Dads ADD CONSTRAINT PK_DD PRIMARY KEY (DD_Dad_Key)
ALTER TABLE KK_Kids ADD CONSTRAINT PK_KK PRIMARY KEY (KK_Kid_Key)
ALTER TABLE DD_Dads ADD CONSTRAINT FK_DD FOREIGN KEY (DD_Grandpa_Key) REFERENCES GG_Grandpas (GG_Grandpa_Key)
ALTER TABLE KK_Kids ADD CONSTRAINT FK_KK FOREIGN KEY (KK_Dad_Key) REFERENCES DD_Dads (DD_Dad_Key)

INSERT INTO GG_Grandpas VALUES ('GG_GEORGESR_KEY', 'GEORGE SR')
INSERT INTO DD_Dads VALUES ('DD_GEORGEJR_KEY', 'GG_GEORGESR_KEY', 'GEORGE JR')
INSERT INTO DD_Dads VALUES ('DD_JEB_KEY', 'GG_GEORGESR_KEY', 'JEB')
INSERT INTO KK_Kids VALUES ( 'KK_Jenna_Key', 'DD_GEORGEJR_KEY', 'Jenna' )
INSERT INTO KK_Kids VALUES ( 'KK_Barbara_Key', 'DD_GEORGEJR_KEY', 'Barbara' )
INSERT INTO KK_Kids VALUES ( 'KK_Noelle_Key', 'DD_JEB_KEY', 'Noelle' )
INSERT INTO KK_Kids VALUES ( 'KK_JebJR_Key', 'DD_JEB_KEY', 'Jeb Junior' )

So the question is, how do I get it to maintain the proper relationships between the records when I do an FOR XML query? Here is the query I am trying to get to work. Right now it puts all the Kids under a single Dad, rather than having them under their correct dads.
I am getting this, which is not what I want:

- George SR
-- George JR
-- Jeb
-- Jenna
-- Barbara
-- Jeb JR
-- Noelle

SELECT 1 as Tag,
NULL as Parent,
GG_GrandpaName as [GG_Grandpas!1!GG_GrandpaName],
GG_Grandpa_Key as [GG_Grandpas!1!GG_Grandpa_Key!id],
NULL as [DD_Dads!2!DD_DadName],
NULL as [DD_Dads!2!DD_Dad_Key!id],
NULL as [DD_Dads!2!DD_Grandpa_Key!idref],
NULL as [KK_Kids!3!KK_KidName],
NULL as [KK_Kids!3!KK_Dad_Key!idref]
FROM GG_Grandpas
UNION ALL
SELECT 2 ,
1 ,
NULL ,
GG_Grandpa_Key ,
DD_DadName ,
DD_Dad_Key ,
DD_Grandpa_Key ,
NULL ,
NULL
FROM GG_Grandpas, DD_Dads
WHERE GG_Grandpa_Key = DD_Grandpa_Key
UNION ALL
SELECT 3 ,
2 ,
NULL ,
GG_Grandpa_Key ,
NULL ,
DD_Dad_Key ,
NULL ,
KK_KidName ,
KK_Dad_Key
FROM GG_Grandpas, DD_Dads , KK_Kids
WHERE GG_Grandpa_Key = DD_Grandpa_Key
AND DD_Dad_Key = KK_Dad_Key

FOR XML EXPLICIT

I've tried it all different ways, but no luck so far.
Any ideas?I'm having trouble getting a FOR XML query to get the relationships correct when there are 3 levels of data.

Check this out..

SELECT dbo.GG_Grandpas.GG_GrandpaName, dbo.DD_Dads.DD_DadName, dbo.KK_Kids.KK_KidName
FROM dbo.DD_Dads

INNER JOIN dbo.GG_Grandpas
ON dbo.DD_Dads.DD_Grandpa_Key = dbo.GG_Grandpas.GG_Grandpa_Key

INNER JOIN dbo.KK_Kids
ON dbo.DD_Dads.DD_Dad_Key = dbo.KK_Kids.KK_Dad_Key

GROUP BY dbo.GG_Grandpas.GG_GrandpaName, dbo.DD_Dads.DD_DadName, dbo.KK_Kids.KK_KidName

for xml auto

result in xml

<dbo.GG_Grandpas GG_GrandpaName="GEORGE SR">
<dbo.DD_Dads DD_DadName="GEORGE JR">
<dbo.KK_Kids KK_KidName="Barbara" />
<dbo.KK_Kids KK_KidName="Jenna" />
</dbo.DD_Dads>
<dbo.DD_Dads DD_DadName="JEB">
<dbo.KK_Kids KK_KidName="Jeb Junior" />
<dbo.KK_Kids KK_KidName="Noelle" />
</dbo.DD_Dads>
</dbo.GG_Grandpas>

if you need those ids just include those ...|||You can't do a GROUP BY with a FOR XML query, at least not in the version I'm running. I get this message:
GROUP BY and aggregate functions are currently not supported with FOR XML AUTO.

Turns out a simple query does work for my example though:

SELECT GG_GrandpaName, DD_DadName, KK_KidName
FROM GG_Grandpas
LEFT OUTER JOIN DD_Dads ON DD_Grandpa_Key = GG_Grandpa_Key
LEFT OUTER JOIN KK_Kids ON DD_Dads.DD_Dad_Key = KK_Kids.KK_Dad_Key
for xml auto , elements

I think I simplified it too much for my example though, because it's still not working for my real world case.|||I believe I have it now. The trick is in the orderby clause. You have to order the results such that the children fall right after their parents in the result table or else it won't get the relationships correct.
I added another child to my previous example so that there is a separate Sons and Daughters table to fit my realworld problem better. This example might be easier to follow than the one in BOL, so I thought I'd post it.

Here is the code to setup the example:

SET NOCOUNT ON
DROP TABLE SS_Sons, DD_Daughters, FF_Fathers, GG_Grandpas

CREATE TABLE GG_Grandpas ( GG_Grandpa_Key varchar(20) NOT NULL, GG_Name varchar(20))
CREATE TABLE FF_Fathers ( FF_Father_Key varchar(20) NOT NULL, FF_Grandpa_Key varchar(20) NOT NULL, FF_Name varchar(20))
CREATE TABLE SS_Sons ( SS_Son_Key varchar(20) NOT NULL, SS_Father_Key varchar(20) NOT NULL, SS_Name varchar(20))
CREATE TABLE DD_Daughters ( DD_Daughter_Key varchar(20) NOT NULL, DD_Father_Key varchar(20) NOT NULL, DD_Name varchar(20))

ALTER TABLE GG_Grandpas ADD CONSTRAINT PK_GG PRIMARY KEY (GG_Grandpa_Key)
ALTER TABLE FF_Fathers ADD CONSTRAINT PK_FF PRIMARY KEY (FF_Father_Key)
ALTER TABLE SS_Sons ADD CONSTRAINT PK_SS PRIMARY KEY (SS_Son_Key)
ALTER TABLE DD_Daughters ADD CONSTRAINT PK_DD PRIMARY KEY (DD_Daughter_Key)

ALTER TABLE FF_Fathers ADD CONSTRAINT FK_FF FOREIGN KEY (FF_Grandpa_Key) REFERENCES GG_Grandpas (GG_Grandpa_Key)
ALTER TABLE SS_Sons ADD CONSTRAINT FK_SS FOREIGN KEY (SS_Father_Key) REFERENCES FF_Fathers (FF_Father_Key)
ALTER TABLE DD_Daughters ADD CONSTRAINT FK_DD FOREIGN KEY (DD_Father_Key) REFERENCES FF_Fathers (FF_Father_Key)

INSERT INTO GG_Grandpas VALUES ('GG_GEORGESR_KEY', 'GEORGE H')
INSERT INTO FF_Fathers VALUES ('FF_GEORGEJR_KEY', 'GG_GEORGESR_KEY', 'GEORGE W')
INSERT INTO FF_Fathers VALUES ('FF_JEB_KEY', 'GG_GEORGESR_KEY', 'JEB')
INSERT INTO SS_Sons VALUES ( 'SS_JebJR_Key', 'FF_JEB_KEY', 'Jeb Junior' )
INSERT INTO DD_Daughters VALUES ( 'DD_Jenna_Key', 'FF_GEORGEJR_KEY', 'Jenna' )
INSERT INTO DD_Daughters VALUES ( 'DD_Barbara_Key', 'FF_GEORGEJR_KEY', 'Barbara' )
INSERT INTO DD_Daughters VALUES ( 'DD_Noelle_Key', 'FF_JEB_KEY', 'Noelle' )

and here is the select statement:

SELECT 1 AS Tag,
NULL as Parent,
GG_Name as [GrandPas!1!GrandpaName!element],
NULL as [Fathers!2!FatherName!element],
NULL as [Sons!3!SonName!element],
NULL as [Daughters!4!DaughterName!element]
FROM GG_Grandpas
UNION ALL
SELECT 2 AS Tag,
1 as Parent,
GG_Name ,
FF_Name ,
NULL ,
NULL
FROM GG_Grandpas
LEFT OUTER JOIN FF_Fathers ON ( FF_Grandpa_Key = GG_Grandpa_Key )
UNION ALL
SELECT 3 AS Tag,
2 as Parent,
GG_Name ,
FF_Name ,
SS_Name ,
NULL
FROM GG_Grandpas
LEFT OUTER JOIN FF_Fathers ON ( FF_Grandpa_Key = GG_Grandpa_Key )
LEFT OUTER JOIN SS_Sons ON (SS_Father_Key = FF_Father_Key)
UNION ALL
SELECT 4 AS Tag,
2 as Parent,
GG_Name ,
FF_Name ,
NULL ,
DD_Name
FROM GG_Grandpas
LEFT OUTER JOIN FF_Fathers ON ( FF_Grandpa_Key = GG_Grandpa_Key )
LEFT OUTER JOIN DD_Daughters ON (DD_Father_Key = FF_Father_Key)
ORDER BY [GrandPas!1!GrandpaName!element], [Fathers!2!FatherName!element], [Sons!3!SonName!element], [Daughters!4!DaughterName!element]
FOR XML EXPLICIT|||You can't do a GROUP BY with a FOR XML query, at least not in the version I'm running. I get this message:
GROUP BY and aggregate functions are currently not supported with FOR XML AUTO.

Which version are you running? I have no problem in group by clause,Mine is SQL SERVER 2005 EXPRESS edition.I just gave you the result in xml also...so there is no error in that...|||I'm running 2000, not 2005:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

FOR XML EXPLICIT issue

Hi guys:
I'm in an urgent need to know if we can dynamically alter values in
querying tables using FOR XML EXPLICIT. In the following sample, I have
3 tabels involved vis_Rule, vis_IF, and vis_AND.
Table Structure:
Table: vis_Rule
Column1: RuleId, Column2: Name, Column3: Priority, Column4: Active
Table: vis_IF
Column1: IFId, Column2: RuleId -> Foreign Key to RuleId in vis_Rule
Table: vis_AND
Column1: AndId, Column2: IFId -> Foreign Key to IFId in vis_IF
++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++
In the following stored proc, can we grab the RuleId of the first
SELECT statement and pass it to the next SELECT statement such that it
extracts only the desired rows? In other words, can we dynamically
assign values from one part of the SELECT statement to the next?
SELECT TOP 1
1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id],
NULL as [compare!4!id],
NULL as [compare!4!operator]
FROM vis_Rule r
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL,
NULL,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
NULL,
NULL,
NULL,
NULL,
if.IFId,
[and].AndId,
NULL,
NULL
FROM vis_AND [and], vis_IF if
where [and].IFId = if.IFId
FOR XML EXPLICIT
GO
Thanks in advance.My apologies for the late reply, but I was on vacation the last couple of
ws.
I am not quite sure what you try to acheive. Do you want to only provide the
nesting of the tree for a given ruleID?
In that case try:
CREATE Table vis_Rule (RuleId int,Name nvarchar(40), Priority int, Active
bit)
go
insert into vis_Rule values (1, N'r1', 1, 1)
insert into vis_Rule values (2, N'r2', 2, 1)
go
CREATE Table vis_IF (IFId int, RuleId int --> Foreign Key to RuleId in
vis_Rule
)
go
insert into vis_IF values (1, 1)
insert into vis_IF values (2, 1)
insert into vis_IF values (3, 1)
insert into vis_IF values (4, 2)
go
CREATE Table vis_AND (AndId int, IFId int --> Foreign Key to IFId in vis_IF
)
insert into vis_AND values (1, 1)
insert into vis_AND values (2, 1)
insert into vis_AND values (3, 2)
insert into vis_AND values (4, 2)
insert into vis_AND values (5, 3)
insert into vis_AND values (6, 4)
go
declare @.rid int
set @.rid = 1
SELECT TOP 1
1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id]
FROM vis_Rule r
where r.RuleId = @.rid
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
AND r.RuleId = @.rid
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
[if].RuleId,
NULL,
NULL,
NULL,
[if].IFId,
[and].AndId
FROM vis_AND [and], vis_IF [if]
where [and].IFId = [if].IFId
AND [if].RuleId = @.rid
ORDER BY [rule!1!Id], [if!2!id], Parent
FOR XML EXPLICIT
If you want it for all rules, try:
SELECT 1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id]
FROM vis_Rule r
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
[and].AndId
FROM vis_AND [and], vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
AND r.RuleId = [if].RuleId
AND [and].IFId = [if].IFId
ORDER BY [rule!1!Id], [if!2!id], Parent
FOR XML EXPLICIT
Best regards
Michael
<shamod@.gmail.com> wrote in message
news:1122527565.227837.240190@.g43g2000cwa.googlegroups.com...
> Hi guys:
> I'm in an urgent need to know if we can dynamically alter values in
> querying tables using FOR XML EXPLICIT. In the following sample, I have
> 3 tabels involved vis_Rule, vis_IF, and vis_AND.
> Table Structure:
> Table: vis_Rule
> Column1: RuleId, Column2: Name, Column3: Priority, Column4: Active
> Table: vis_IF
> Column1: IFId, Column2: RuleId -> Foreign Key to RuleId in vis_Rule
> Table: vis_AND
> Column1: AndId, Column2: IFId -> Foreign Key to IFId in vis_IF
> ++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++
> In the following stored proc, can we grab the RuleId of the first
> SELECT statement and pass it to the next SELECT statement such that it
> extracts only the desired rows? In other words, can we dynamically
> assign values from one part of the SELECT statement to the next?
> SELECT TOP 1
> 1 AS Tag,
> NULL AS Parent,
> r.RuleId as [rule!1!Id],
> r.Name as [rule!1!name],
> r.Priority as [rule!1!priority],
> r.Active as [rule!1!active],
> NULL as [if!2!id],
> NULL as [and!3!id],
> NULL as [compare!4!id],
> NULL as [compare!4!operator]
> FROM vis_Rule r
> UNION ALL
> SELECT 2 AS Tag,
> 1 AS Parent,
> r.RuleId,
> NULL,
> NULL,
> NULL,
> [if].IFId,
> NULL,
> NULL,
> NULL
> FROM vis_IF [if], vis_Rule r
> WHERE [if].RuleId = r.RuleId
> UNION ALL
> SELECT 3 AS Tag,
> 2 AS Parent,
> NULL,
> NULL,
> NULL,
> NULL,
> if.IFId,
> [and].AndId,
> NULL,
> NULL
> FROM vis_AND [and], vis_IF if
> where [and].IFId = if.IFId
> FOR XML EXPLICIT
> GO
> Thanks in advance.
>

FOR XML EXPLICIT issue

Hi guys:
I'm in an urgent need to know if we can dynamically alter values in
querying tables using FOR XML EXPLICIT. In the following sample, I have
3 tabels involved vis_Rule, vis_IF, and vis_AND.
Table Structure:
Table: vis_Rule
Column1: RuleId, Column2: Name, Column3: Priority, Column4: Active
Table: vis_IF
Column1: IFId, Column2: RuleId -> Foreign Key to RuleId in vis_Rule
Table: vis_AND
Column1: AndId, Column2: IFId -> Foreign Key to IFId in vis_IF
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++
In the following stored proc, can we grab the RuleId of the first
SELECT statement and pass it to the next SELECT statement such that it
extracts only the desired rows? In other words, can we dynamically
assign values from one part of the SELECT statement to the next?
SELECT TOP 1
1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id],
NULL as [compare!4!id],
NULL as [compare!4!operator]
FROM vis_Rule r
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL,
NULL,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
NULL,
NULL,
NULL,
NULL,
if.IFId,
[and].AndId,
NULL,
NULL
FROM vis_AND [and], vis_IF if
where [and].IFId = if.IFId
FOR XML EXPLICIT
GO
Thanks in advance.
My apologies for the late reply, but I was on vacation the last couple of
weeks.
I am not quite sure what you try to acheive. Do you want to only provide the
nesting of the tree for a given ruleID?
In that case try:
CREATE Table vis_Rule (RuleId int,Name nvarchar(40), Priority int, Active
bit)
go
insert into vis_Rule values (1, N'r1', 1, 1)
insert into vis_Rule values (2, N'r2', 2, 1)
go
CREATE Table vis_IF (IFId int, RuleId int --> Foreign Key to RuleId in
vis_Rule
)
go
insert into vis_IF values (1, 1)
insert into vis_IF values (2, 1)
insert into vis_IF values (3, 1)
insert into vis_IF values (4, 2)
go
CREATE Table vis_AND (AndId int, IFId int --> Foreign Key to IFId in vis_IF
)
insert into vis_AND values (1, 1)
insert into vis_AND values (2, 1)
insert into vis_AND values (3, 2)
insert into vis_AND values (4, 2)
insert into vis_AND values (5, 3)
insert into vis_AND values (6, 4)
go
declare @.rid int
set @.rid = 1
SELECT TOP 1
1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id]
FROM vis_Rule r
where r.RuleId = @.rid
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
AND r.RuleId = @.rid
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
[if].RuleId,
NULL,
NULL,
NULL,
[if].IFId,
[and].AndId
FROM vis_AND [and], vis_IF [if]
where [and].IFId = [if].IFId
AND [if].RuleId = @.rid
ORDER BY [rule!1!Id], [if!2!id], Parent
FOR XML EXPLICIT
If you want it for all rules, try:
SELECT 1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id]
FROM vis_Rule r
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
[and].AndId
FROM vis_AND [and], vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
AND r.RuleId = [if].RuleId
AND [and].IFId = [if].IFId
ORDER BY [rule!1!Id], [if!2!id], Parent
FOR XML EXPLICIT
Best regards
Michael
<shamod@.gmail.com> wrote in message
news:1122527565.227837.240190@.g43g2000cwa.googlegr oups.com...
> Hi guys:
> I'm in an urgent need to know if we can dynamically alter values in
> querying tables using FOR XML EXPLICIT. In the following sample, I have
> 3 tabels involved vis_Rule, vis_IF, and vis_AND.
> Table Structure:
> Table: vis_Rule
> Column1: RuleId, Column2: Name, Column3: Priority, Column4: Active
> Table: vis_IF
> Column1: IFId, Column2: RuleId -> Foreign Key to RuleId in vis_Rule
> Table: vis_AND
> Column1: AndId, Column2: IFId -> Foreign Key to IFId in vis_IF
> ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++
> In the following stored proc, can we grab the RuleId of the first
> SELECT statement and pass it to the next SELECT statement such that it
> extracts only the desired rows? In other words, can we dynamically
> assign values from one part of the SELECT statement to the next?
> SELECT TOP 1
> 1 AS Tag,
> NULL AS Parent,
> r.RuleId as [rule!1!Id],
> r.Name as [rule!1!name],
> r.Priority as [rule!1!priority],
> r.Active as [rule!1!active],
> NULL as [if!2!id],
> NULL as [and!3!id],
> NULL as [compare!4!id],
> NULL as [compare!4!operator]
> FROM vis_Rule r
> UNION ALL
> SELECT 2 AS Tag,
> 1 AS Parent,
> r.RuleId,
> NULL,
> NULL,
> NULL,
> [if].IFId,
> NULL,
> NULL,
> NULL
> FROM vis_IF [if], vis_Rule r
> WHERE [if].RuleId = r.RuleId
> UNION ALL
> SELECT 3 AS Tag,
> 2 AS Parent,
> NULL,
> NULL,
> NULL,
> NULL,
> if.IFId,
> [and].AndId,
> NULL,
> NULL
> FROM vis_AND [and], vis_IF if
> where [and].IFId = if.IFId
> FOR XML EXPLICIT
> GO
> Thanks in advance.
>

for xml explicit

Hello,
I have been using for xml Explicit for a little while but this one has got
me stumped. I am return several tables that will each end up on a different
excel worksheet. A portion of the query is:
SELECT
1 as Tag,--metadata
Null as Parent,
isnull(@.project,'MISC') as [ReportData!1!Project],
Recid as [ReportData!1!Recid],
tnum as [ReportData!1!tnum],
null as [Metadata!2!WorksheetName!Element], --optional
Null AS [Metadata!2!Title!Element],
Null as [Metadata!2!FirstSubTitle!Element], --optional
Null as [Metadata!2!SecondSubTitle!Element], --optional
Null as [Metadata!2!Asofdate!Element],
Null as [Metadata!2!Rundate!Element]
from @.tblrecid as ReportData
union all
SELECT
2 as tag, --metadata
1 as parent, -- subset of Reportdata
Null, --Project
Reportdata.Recid as [ReportData!1!Recid],
Reportdata.tnum as [ReportData!1!tnum],
isnull(lu.worksheetname,left(rtrim(l.type1),8)+'_'+left(rtrim(l.type2),10)),
l.Title,
lu.Title2 as FirstSubTitle,
lu.Subtitle1 as SecondSubTitle,
convert(char(10),l.Asofdate,121) as Asofdate,
convert(char(10),l.rundatetime,121) as Rundate
FROM tblReportLog l join tblReportLU lu
on l.tnum = lu.tnum join @.tblRecid Reportdata
on l.recid = Reportdata.recid
for xml explicit
The recid is the identifyer for each table.
what i get is:
<ReportData Project="MISC" Recid="1111" tnum="11">
<Metadata>
<WorksheetName></WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate>2004-09-30</Asofdate>
<Rundate>2004-10-14</Rundate>
</Metadata>
<Metadata>
<WorksheetName>R_Freq</WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate>2004-09-30</Asofdate>
<Rundate>2004-11-01</Rundate>
</Metadata>
</ReportData>
<ReportData Project="MISC" Recid="2222" tnum="22"/>
What I want is (the root is added later):
<ReportData Project="MISC" Recid="1111" tnum="11">
<Metadata>
<WorksheetName></WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate>2004-09-30</Asofdate>
<Rundate>2004-10-14</Rundate>
</Metadata>
</ReportData>
<ReportData Project="MISC" Recid="2222" tnum="22">
<Metadata>
<WorksheetName></WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate></Asofdate>
<Rundate></Rundate>
</Metadata>
</ReportData>
Any ideas?Are you missing the order by that will group the children rows to its parent
row? Your excerpt does not show one...
Adding something like
order by [ReportData!1!Recid]
should help.
Best regards
Michael
PS: Another good case where using FOR XML PATH in SQL Server 2005 will make
writing such queries so much easier...
"michanne" <michanne@.discussions.microsoft.com> wrote in message
news:6D0DDE55-012A-4287-96C6-56C483880857@.microsoft.com...
> Hello,
> I have been using for xml Explicit for a little while but this one has got
> me stumped. I am return several tables that will each end up on a
> different
> excel worksheet. A portion of the query is:
> SELECT
> 1 as Tag,--metadata
> Null as Parent,
> isnull(@.project,'MISC') as [ReportData!1!Project],
> Recid as [ReportData!1!Recid],
> tnum as [ReportData!1!tnum],
> null as [Metadata!2!WorksheetName!Element], --optional
> Null AS [Metadata!2!Title!Element],
> Null as [Metadata!2!FirstSubTitle!Element], --optional
> Null as [Metadata!2!SecondSubTitle!Element], --optional
> Null as [Metadata!2!Asofdate!Element],
> Null as [Metadata!2!Rundate!Element]
> from @.tblrecid as ReportData
> union all
> SELECT
> 2 as tag, --metadata
> 1 as parent, -- subset of Reportdata
> Null, --Project
> Reportdata.Recid as [ReportData!1!Recid],
> Reportdata.tnum as [ReportData!1!tnum],
> isnull(lu.worksheetname,left(rtrim(l.type1),8)+'_'+left(rtrim(l.type2),10)
),
> l.Title,
> lu.Title2 as FirstSubTitle,
> lu.Subtitle1 as SecondSubTitle,
> convert(char(10),l.Asofdate,121) as Asofdate,
> convert(char(10),l.rundatetime,121) as Rundate
> FROM tblReportLog l join tblReportLU lu
> on l.tnum = lu.tnum join @.tblRecid Reportdata
> on l.recid = Reportdata.recid
> for xml explicit
> The recid is the identifyer for each table.
> what i get is:
> <ReportData Project="MISC" Recid="1111" tnum="11">
> <Metadata>
> <WorksheetName></WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate>2004-09-30</Asofdate>
> <Rundate>2004-10-14</Rundate>
> </Metadata>
> <Metadata>
> <WorksheetName>R_Freq</WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate>2004-09-30</Asofdate>
> <Rundate>2004-11-01</Rundate>
> </Metadata>
> </ReportData>
> <ReportData Project="MISC" Recid="2222" tnum="22"/>
> What I want is (the root is added later):
> <ReportData Project="MISC" Recid="1111" tnum="11">
> <Metadata>
> <WorksheetName></WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate>2004-09-30</Asofdate>
> <Rundate>2004-10-14</Rundate>
> </Metadata>
> </ReportData>
> <ReportData Project="MISC" Recid="2222" tnum="22">
> <Metadata>
> <WorksheetName></WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate></Asofdate>
> <Rundate></Rundate>
> </Metadata>
> </ReportData>
> Any ideas?
>|||I had an order clause just like that but i took it out in one of the
iterations. I just tested it again to be sure but the result was the same. :
-(
As much as i'd prefer 2005, it isn't going to be available to me for a long
time.
"Michael Rys [MSFT]" wrote:

> Are you missing the order by that will group the children rows to its pare
nt
> row? Your excerpt does not show one...
> Adding something like
> order by [ReportData!1!Recid]
> should help.
> Best regards
> Michael
> PS: Another good case where using FOR XML PATH in SQL Server 2005 will mak
e
> writing such queries so much easier...
> "michanne" <michanne@.discussions.microsoft.com> wrote in message
> news:6D0DDE55-012A-4287-96C6-56C483880857@.microsoft.com...
>
>|||Ok - i needed to also order by one of the fields in tag 2.
Thanks!
"michanne" wrote:
> I had an order clause just like that but i took it out in one of the
> iterations. I just tested it again to be sure but the result was the same.
:-(
> As much as i'd prefer 2005, it isn't going to be available to me for a lon
g
> time.
> "Michael Rys [MSFT]" wrote:
>

for xml explicit

Hello,
I have been using for xml Explicit for a little while but this one has got
me stumped. I am return several tables that will each end up on a different
excel worksheet. A portion of the query is:
SELECT
1 as Tag,--metadata
Null as Parent,
isnull(@.project,'MISC') as [ReportData!1!Project],
Recid as [ReportData!1!Recid],
tnum as [ReportData!1!tnum],
null as [Metadata!2!WorksheetName!Element], --optional
Null AS [Metadata!2!Title!Element],
Null as [Metadata!2!FirstSubTitle!Element], --optional
Null as [Metadata!2!SecondSubTitle!Element], --optional
Null as [Metadata!2!Asofdate!Element],
Null as [Metadata!2!Rundate!Element]
from @.tblrecid as ReportData
union all
SELECT
2 as tag, --metadata
1 as parent, -- subset of Reportdata
Null, --Project
Reportdata.Recid as [ReportData!1!Recid],
Reportdata.tnum as [ReportData!1!tnum],
isnull(lu.worksheetname,left(rtrim(l.type1),8)+'_' +left(rtrim(l.type2),10)),
l.Title,
lu.Title2 as FirstSubTitle,
lu.Subtitle1 as SecondSubTitle,
convert(char(10),l.Asofdate,121) as Asofdate,
convert(char(10),l.rundatetime,121) as Rundate
FROM tblReportLog l join tblReportLU lu
on l.tnum = lu.tnum join @.tblRecid Reportdata
on l.recid = Reportdata.recid
for xml explicit
The recid is the identifyer for each table.
what i get is:
<ReportData Project="MISC" Recid="1111" tnum="11">
<Metadata>
<WorksheetName></WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate>2004-09-30</Asofdate>
<Rundate>2004-10-14</Rundate>
</Metadata>
<Metadata>
<WorksheetName>R_Freq</WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate>2004-09-30</Asofdate>
<Rundate>2004-11-01</Rundate>
</Metadata>
</ReportData>
<ReportData Project="MISC" Recid="2222" tnum="22"/>
What I want is (the root is added later):
<ReportData Project="MISC" Recid="1111" tnum="11">
<Metadata>
<WorksheetName></WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate>2004-09-30</Asofdate>
<Rundate>2004-10-14</Rundate>
</Metadata>
</ReportData>
<ReportData Project="MISC" Recid="2222" tnum="22">
<Metadata>
<WorksheetName></WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate></Asofdate>
<Rundate></Rundate>
</Metadata>
</ReportData>
Any ideas?
Are you missing the order by that will group the children rows to its parent
row? Your excerpt does not show one...
Adding something like
order by [ReportData!1!Recid]
should help.
Best regards
Michael
PS: Another good case where using FOR XML PATH in SQL Server 2005 will make
writing such queries so much easier...
"michanne" <michanne@.discussions.microsoft.com> wrote in message
news:6D0DDE55-012A-4287-96C6-56C483880857@.microsoft.com...
> Hello,
> I have been using for xml Explicit for a little while but this one has got
> me stumped. I am return several tables that will each end up on a
> different
> excel worksheet. A portion of the query is:
> SELECT
> 1 as Tag,--metadata
> Null as Parent,
> isnull(@.project,'MISC') as [ReportData!1!Project],
> Recid as [ReportData!1!Recid],
> tnum as [ReportData!1!tnum],
> null as [Metadata!2!WorksheetName!Element], --optional
> Null AS [Metadata!2!Title!Element],
> Null as [Metadata!2!FirstSubTitle!Element], --optional
> Null as [Metadata!2!SecondSubTitle!Element], --optional
> Null as [Metadata!2!Asofdate!Element],
> Null as [Metadata!2!Rundate!Element]
> from @.tblrecid as ReportData
> union all
> SELECT
> 2 as tag, --metadata
> 1 as parent, -- subset of Reportdata
> Null, --Project
> Reportdata.Recid as [ReportData!1!Recid],
> Reportdata.tnum as [ReportData!1!tnum],
> isnull(lu.worksheetname,left(rtrim(l.type1),8)+'_' +left(rtrim(l.type2),10)),
> l.Title,
> lu.Title2 as FirstSubTitle,
> lu.Subtitle1 as SecondSubTitle,
> convert(char(10),l.Asofdate,121) as Asofdate,
> convert(char(10),l.rundatetime,121) as Rundate
> FROM tblReportLog l join tblReportLU lu
> on l.tnum = lu.tnum join @.tblRecid Reportdata
> on l.recid = Reportdata.recid
> for xml explicit
> The recid is the identifyer for each table.
> what i get is:
> <ReportData Project="MISC" Recid="1111" tnum="11">
> <Metadata>
> <WorksheetName></WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate>2004-09-30</Asofdate>
> <Rundate>2004-10-14</Rundate>
> </Metadata>
> <Metadata>
> <WorksheetName>R_Freq</WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate>2004-09-30</Asofdate>
> <Rundate>2004-11-01</Rundate>
> </Metadata>
> </ReportData>
> <ReportData Project="MISC" Recid="2222" tnum="22"/>
> What I want is (the root is added later):
> <ReportData Project="MISC" Recid="1111" tnum="11">
> <Metadata>
> <WorksheetName></WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate>2004-09-30</Asofdate>
> <Rundate>2004-10-14</Rundate>
> </Metadata>
> </ReportData>
> <ReportData Project="MISC" Recid="2222" tnum="22">
> <Metadata>
> <WorksheetName></WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate></Asofdate>
> <Rundate></Rundate>
> </Metadata>
> </ReportData>
> Any ideas?
>
|||I had an order clause just like that but i took it out in one of the
iterations. I just tested it again to be sure but the result was the same. :-(
As much as i'd prefer 2005, it isn't going to be available to me for a long
time.
"Michael Rys [MSFT]" wrote:

> Are you missing the order by that will group the children rows to its parent
> row? Your excerpt does not show one...
> Adding something like
> order by [ReportData!1!Recid]
> should help.
> Best regards
> Michael
> PS: Another good case where using FOR XML PATH in SQL Server 2005 will make
> writing such queries so much easier...
> "michanne" <michanne@.discussions.microsoft.com> wrote in message
> news:6D0DDE55-012A-4287-96C6-56C483880857@.microsoft.com...
>
>
|||Ok - i needed to also order by one of the fields in tag 2.
Thanks!
"michanne" wrote:
[vbcol=seagreen]
> I had an order clause just like that but i took it out in one of the
> iterations. I just tested it again to be sure but the result was the same. :-(
> As much as i'd prefer 2005, it isn't going to be available to me for a long
> time.
> "Michael Rys [MSFT]" wrote:

FOR XML AUTO SQL 2K vs 2K5

The upgrade adviser for for 2k5 says something about derived tables being handled differently between 2k and 2K5 and it says to query the tables directly but this does not seem to make much sense because I thought FOR XML AUTO just created some generic XML for presentation purposes. These 2 stored procedures that it is complaining about do query the tables directly and they use the FOR XML AUTO to control the output.

Does anyone know if I have to worry about this? I am tempted to let this slide and check out this part of the application after the migration happens tomorrow for QA to start testing.

Yes I have been googling, checking my books and digging around in BOL. I am not seeing anything.

DISREGARD: I found my derived table. It appears to change the output of the XML. Perfect.I have never used FOR XML AUTO before. Looking over the descriptions of both the SQL 2K and 2K5 versions, the articles read almost identically. If the queries are relatively simple (not based on views, or subqueries), then I think you can just compare the 2K output to a similar 2K5 dataset, to make sure the XML is formed the same way.|||the difference is explained in the obvious location of sp_dbcmptlevel article in BOL 2k5. Off to the doctors office I go. Good thing I have a laptop so I can code in the waiting room.