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 + 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
Henri
a 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 + 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
>
>

Forcing size of a field

Hi,
I have a field x varchar(6)
I want force the values at least at 4 char no less
how can i do it?
NULL must be still valid.
Thanks, FilippoUse a CHECK constraint:
CREATE TABLE #t(c1 varchar(6) NULL)
ALTER TABLE #t ADD CONSTRAINT cnstname CHECK (LEN(c1) >= 4)
GO
INSERT INTO #t (c1) VALUES('1234')
GO
INSERT INTO #t (c1) VALUES('123')
GO
INSERT INTO #t (c1) VALUES(NULL)
GO
SELECT * FROM #t
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Filippo Bettinaglio" <anonymous@.discussions.microsoft.com> wrote in message
news:1ed701c53e80$f4885ac0$a601280a@.phx.gbl...
> Hi,
> I have a field x varchar(6)
> I want force the values at least at 4 char no less
> how can i do it?
> NULL must be still valid.
> Thanks, Filippo|||Use a CHECK constraint:
ALTER TABLE Filippo
ADD CONSTRAINT CK_Filippo__len_x_gte_4
CHECK (LEN(x) >=4)
Note that the LEN of NULL is NULL, and NULL>=4 returns UNKNOWN, and that
doesn't violate the CHECK constraint. CHECK constraints (and constraints in
general) are only violated if the expression they check returns FALSE.
Jacco Schalkwijk
SQL Server MVP
"Filippo Bettinaglio" <anonymous@.discussions.microsoft.com> wrote in message
news:1ed701c53e80$f4885ac0$a601280a@.phx.gbl...
> Hi,
> I have a field x varchar(6)
> I want force the values at least at 4 char no less
> how can i do it?
> NULL must be still valid.
> Thanks, Filippo

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 fields upper-case

Almost all of our character fields are stored in upper-case. Is there an easy way to force SQL Server char and varchar fields to upper-case? Something I can do in SQL Server instead of in the client? It needs to apply to any new records.

There are some exceptions (email addresses for one). I don't mind going through each field and changing something.

Thanks!

You could define an INSTEAD OF Insert trigger, and apply the UPPER() function to the columns you want in upper case.|||

Dale,

Is there a way to INSERT INTO <mytable> all fields, but also force the text ones to uppercase? I'm not sure how to do it without listing each field individually.

Brian

|||

I know, tedious.

I thought maybe COLLATE would provide something, but I've not been able to find an answer through that either.

for xml to local variable

Is there a reason why the following does not work...
declare @.s varchar(1024)
set @.s = (select * from validTable for xml auto)
...assuming I know for a fact that the returned xml stream will fit inside my declared variable.
I suspect it's just the pre-compiler getting it's knickers in a knot over the * when this technically meets the requirements for setting a local variable.
... i think...
Does any one have any comments or ideas on how to achieve the same effect inside an SQL stored Proc.?
WR
Unfortunately, this can't be done in SQL Server 2000 - what actually gets
returned is a single column/single row resultset containing the XML stream.
The client-side components of SQLXML can extract that as a stream but
there's no way to do it in T-SQL.
In SQL Server 2005, you can use the xml data type to do what you're
suggesting.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"WildRide" <WildRide@.discussions.microsoft.com> wrote in message
news:06CEF1F3-F947-45B9-93BE-BE8FEF4E3DF6@.microsoft.com...
Is there a reason why the following does not work...
declare @.s varchar(1024)
set @.s = (select * from validTable for xml auto)
...assuming I know for a fact that the returned xml stream will fit inside
my declared variable.
I suspect it's just the pre-compiler getting it's knickers in a knot over
the * when this technically meets the requirements for setting a local
variable.
... i think...
Does any one have any comments or ideas on how to achieve the same effect
inside an SQL stored Proc.?
WR
|||sorry but i have testing this code with sqlserver 2000
and it doesent work
Cdlt
Query:
declare @.s varchar(1024)
set @.s = (select * from USERPROFILE for xml auto)
Result:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'xml'.

>--Original Message--
>Unfortunately, this can't be done in SQL Server 2000 -
what actually gets
>returned is a single column/single row resultset
containing the XML stream.
>The client-side components of SQLXML can extract that as
a stream but
>there's no way to do it in T-SQL.
>In SQL Server 2005, you can use the xml data type to do
what you're
>suggesting.
>Cheers,
>Graeme
>--
>--
>Graeme Malcolm
>Principal Technologist
>Content Master Ltd.
>www.contentmaster.com
>www.microsoft.com/mspress/books/6137.asp
>
>"WildRide" <WildRide@.discussions.microsoft.com> wrote in
message
>news:06CEF1F3-F947-45B9-93BE-BE8FEF4E3DF6@.microsoft.com...
>Is there a reason why the following does not work...
>declare @.s varchar(1024)
>set @.s = (select * from validTable for xml auto)
>...assuming I know for a fact that the returned xml
stream will fit inside
>my declared variable.
>I suspect it's just the pre-compiler getting it's
knickers in a knot over
>the * when this technically meets the requirements for
setting a local
>variable.
>... i think...
>Does any one have any comments or ideas on how to achieve
the same effect
>inside an SQL stored Proc.?
>WR
>
>.
>
|||"Boss Hog" <anonymous@.discussions.microsoft.com> wrote in message
news:74c001c4764f$9c4d6bb0$a301280a@.phx.gbl...
> sorry but i have testing this code with sqlserver 2000
> and it doesent work
It won't work because it isn't supported.
Bryant

for xml hierarchy

Hi

I have a table that looks like this

declare @.VC table ( V varchar(100), VC int, depth tinyint)
insert into @.VC values ( 'TN', 1, 1)
insert into @.VC values ( 'TN', 2, 2);

and I have for xml query

select
V as @.value,
(
select VC as '@.value' from @.VC pe where pe.V = n.V
for xml path ('Value'), root('Values'), type
) as ME
from ( select distinct VC from @.VC ) n
for xml path ('Value'), root('Values')

that gives me something like this

<Values>
<Value value="TN">
<ME>
<Values>
<Value value="1" />
<Value value="2" />
</Values>
</ME>
</Value>
</Values>

However I need to reorder the xml to look like this according to depth

<Values>
<Value value="TN">
<ME>
<Values depth = 1>
<Value value="1" />
</Values>
</ME>
<ME>
<Values depth =2>
<Value value="2" />
</Values>
</ME>
</Value>
</Values>

the problem I am having is that depth 2 is not below depth 1 node, is on the same depth with the depth attribute diff.

Is there some way to write the for xml to do this

thanks

P

select
V as "@.value",
(
select pe.depth as "Values/@.depth",
pe.VC as "Values/Value/@.value"
from @.VC pe where pe.V = n.V
for xml path('ME'),type
)
from ( select distinct V from @.VC ) n
for xml path ('Value'), root('Values')

For XML Explicit

Hello,
Suppose this table:
Master_Plan
(Master_Plan_Id int,
Community_Id int,
County_Id int,
Market_Id int,
Location_Description varchar(127) )
I would like to have the XML presented as follows:
<Company>
<Data>
<ProjectData
Master_Plan_Id="1">
<Community_Id
valueid="1792"/>
<County_Id
valueid="12"/>
<Market_Id
valueid="2"/>
<Location_Description
value="This Is A Test"/>
</ProjectData>
</Data>
</Company>
The following query returns the result without the sub-
elements "valueid" - data is one level "flatter"
SELECT 1 as Tag
,NULLas Parent
,Master_Plan_Idas [ProjectData!1!
Master_Plan_Id]
,Community_Idas [ProjectData!1!
Community_Id!element]
,County_Idas [ProjectData!1!
County_Id!element]
,Market_Idas [ProjectData!1!
Market_Id!element]
,Location_Descriptionas [ProjectData!1!
Location_Description!element]
FROM Master_Plan
FOR XML EXPLICIT
How can I code the query to present the values as sub-
elements of the corresponding column as explained above?
Thanks!
You need a UNION for each new tag - here's my (not very elegant) solution -
someone else out there might have some better ideas!
SELECT 1 as Tag ,NULL as Parent
,Master_Plan_Id as [ProjectData!1!Master_Plan_Id]
,NULL as [Community_id!2!Valueid]
,NULL as [County_id!3!Valueid]
,NULL as [Market_id!4!Valueid]
,NULL as [Location_Description!5!Valueid]
FROM Master_Plan
UNION ALL
SELECT 2, 1,
Master_Plan_Id,
Community_Id,
NULL,
NULL,
NULL
FROM Master_Plan
UNION ALL
SELECT 3, 1,
Master_Plan_Id,
NULL,
County_Id,
NULL,
NULL
FROM Master_Plan
UNION ALL
SELECT 4, 1,
Master_Plan_Id,
NULL,
NULL,
Market_Id,
NULL
FROM Master_Plan
UNION ALL
SELECT 5, 1,
Master_Plan_Id,
NULL,
NULL,
NULL,
Location_Description
FROM Master_Plan
ORDER BY [ProjectData!1!Master_Plan_Id],
[Location_Description!5!Valueid],
[Market_Id!4!Valueid],
[County_id!3!Valueid],
[Community_Id!2!Valueid]
FOR XML EXPLICIT
Hope that helps!
G
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:1c44d01c421f4$e9d6ecd0$a301280a@.phx.gbl...
> Hello,
> Suppose this table:
> Master_Plan
> (Master_Plan_Id int,
> Community_Id int,
> County_Id int,
> Market_Id int,
> Location_Description varchar(127) )
> I would like to have the XML presented as follows:
> <Company>
> <Data>
> <ProjectData
> Master_Plan_Id="1">
> <Community_Id
> valueid="1792"/>
> <County_Id
> valueid="12"/>
> <Market_Id
> valueid="2"/>
> <Location_Description
> value="This Is A Test"/>
> </ProjectData>
> </Data>
> </Company>
>
> The following query returns the result without the sub-
> elements "valueid" - data is one level "flatter"
> SELECT 1 as Tag
> ,NULL as Parent
> ,Master_Plan_Id as [ProjectData!1!
> Master_Plan_Id]
> ,Community_Id as [ProjectData!1!
> Community_Id!element]
> ,County_Id as [ProjectData!1!
> County_Id!element]
> ,Market_Id as [ProjectData!1!
> Market_Id!element]
> ,Location_Description as [ProjectData!1!
> Location_Description!element]
> FROM Master_Plan
> FOR XML EXPLICIT
> How can I code the query to present the values as sub-
> elements of the corresponding column as explained above?
> Thanks!
|||Instead of giving you the ugly FOR XML explicit query (see Graeme's post), I
would like to know why you want to use attributes to represent the value of
the element. This is in my opinion adding too much complexity to your XML
format (at least based on what you have presented here).
If you really need this format, the following will be the more elegant FOR
XML PATH query possible in Yukon...
SELECT Master_Plan_Id as [ProjectData/@.Master_Plan_Id]
,Community_Id as [ProjectData/Community_Id/@.valueid]
,County_Id as [ProjectData/County_Id/@.valueid]
,Market_Id as [ProjectData/Market_Id/@.valueid]
,Location_Description as [ProjectData/Location_Description/@.value]
FROM Master_Plan
FOR XML PATH('Data'), ROOT('Company')
However, I really would recommend using a format that makes the values
element content instead of a value of a "value" attribute.
Best regards
Michael
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:1c44d01c421f4$e9d6ecd0$a301280a@.phx.gbl...
> Hello,
> Suppose this table:
> Master_Plan
> (Master_Plan_Id int,
> Community_Id int,
> County_Id int,
> Market_Id int,
> Location_Description varchar(127) )
> I would like to have the XML presented as follows:
> <Company>
> <Data>
> <ProjectData
> Master_Plan_Id="1">
> <Community_Id
> valueid="1792"/>
> <County_Id
> valueid="12"/>
> <Market_Id
> valueid="2"/>
> <Location_Description
> value="This Is A Test"/>
> </ProjectData>
> </Data>
> </Company>
>
> The following query returns the result without the sub-
> elements "valueid" - data is one level "flatter"
> SELECT 1 as Tag
> ,NULL as Parent
> ,Master_Plan_Id as [ProjectData!1!
> Master_Plan_Id]
> ,Community_Id as [ProjectData!1!
> Community_Id!element]
> ,County_Id as [ProjectData!1!
> County_Id!element]
> ,Market_Id as [ProjectData!1!
> Market_Id!element]
> ,Location_Description as [ProjectData!1!
> Location_Description!element]
> FROM Master_Plan
> FOR XML EXPLICIT
> How can I code the query to present the values as sub-
> elements of the corresponding column as explained above?
> Thanks!
|||Hi Michael,
The XML will be used to populate data entry forms, and
will contain other attributes for each element that were
not included in the example. It will also be used in
reverse to populate a set of tables with a subset of the
attributes (which were included in the example).
It looks like the Yukon features will do exactly what I
need - which looks exactly like the OpenXML I'm generating
to populate the tables.
I guess I'll home-grow a solution until the features are
available.
Thanks for the response.
Kevin

>--Original Message--
>Instead of giving you the ugly FOR XML explicit query
(see Graeme's post), I
>would like to know why you want to use attributes to
represent the value of
>the element. This is in my opinion adding too much
complexity to your XML
>format (at least based on what you have presented here).
>If you really need this format, the following will be the
more elegant FOR
>XML PATH query possible in Yukon...
>SELECT Master_Plan_Id as [ProjectData/@.Master_Plan_Id]
>,Community_Id as [ProjectData/Community_Id/@.valueid]
>,County_Id as [ProjectData/County_Id/@.valueid]
>,Market_Id as [ProjectData/Market_Id/@.valueid]
>,Location_Description as
[ProjectData/Location_Description/@.value]
>FROM Master_Plan
>FOR XML PATH('Data'), ROOT('Company')
>However, I really would recommend using a format that
makes the values
>element content instead of a value of a "value" attribute.
>Best regards
>Michael
>"Kevin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1c44d01c421f4$e9d6ecd0$a301280a@.phx.gbl...
>
>.
>

FOR EXPLICIT

i don't think the documentation could be more confusing.
Consider:
CREATE TABLE MyNewsEntries(
guid uniqueidentifier,
title varchar(200),
description text,
pubDate datetime,
imageFilename varchar(260),
imageMimeType varchar(100) )
Desired output:
<rss>
<item>
<title>MyNewsEntries.title</title>
<description>MyNewsEntries.description</description>
<pubDate>MyNewsEntries.pubDate</pubDate>
<guid>MyNewsEntries.guid</guid>
<enclosure url="[MyNewsEntires.imageFilename]"
type="[MyNewsEntries.imageMimeType]" />
</item>
<item>
..
</item>
</rss>
Ordered by MyNewsEntries.pubDate DESC
Now, after much cursing and swearing, i managed to vomit up:
SELECT
1 AS Tag, NULL AS Parent,
NULL AS [rss!1!],
NULL AS [item!2!title!element],
NULL AS [item!2!description!element],
NULL AS [item!2!pubdate!element],
NULL AS [item!2!guid!element],
NULL AS [enclosure!3!url],
NULL AS [enclosure!3!type]
UNION ALL
SELECT
2 AS Tag, 1 AS Parent,
NULL as [rss!1!element],
title AS [item!2!title!element],
description AS [item!2!description!element],
pubDate AS [item!2!pubdate!element],
guid AS [item!2!guid!element],
imageFilename AS [enclosure!2!url],
NULL AS [enclosure!2!type]
FROM MyNewsEntries
UNION ALL
SELECT
3 AS Tag, 2 AS Parent,
NULL AS [rss!1!element],
title AS [item!2!title!element],
NULL AS [item!2!description!element],
pubDate AS [item!2!pubdate!element],
guid AS [item!2!guid!element],
imageFilename AS [enclosure!3!url],
imageMimeType AS [enclosure!3!type]
FROM MyNewsEntries
FOR XML EXPLICIT
Which runs, but the order is wrong. All the enclosures are appearing at the
end.
If i try
ORDER BY [item!2!pubdate!element] DESC
FOR XML EXPLICIT
The it puts the rss entry at the end, and complains:
| Parent tag ID 1 is not among the open tags.
| FOR XML EXPLICIT requires parent tags to be opened first.
| Check the ordering of the result set.
So i try
SELECT * FROM (
My entire query above
) DerivedTable
ORDER BY
CASE
WHEN (Parent IS NULL) THEN 0
WHEN (Parent = 0) THEN 0
ELSE 99999
END,
[item!2!pubdate!element] DESC
But now it is mixing the order of Tag=2 and Tag=3 elements, putting all
<enclosures> in one <item>
So i try:
SELECT * FROM (
My entire query above
) DerivedTable
ORDER BY
CASE
WHEN (Parent IS NULL) THEN 0
WHEN (Parent = 0) THEN 0
ELSE 99999
END,
Tag,
[item!2!pubdate!element] DESC
Which seems to work.
My question is: Is this what i have to do to get SQL Server to return XML in
an explicit format?Go back to your original query, change
NULL AS [rss!1!element],
to
1 AS [rss!1!element],
everywhere *except* under tag 1 (the first)
which should be left as null.
Now change your order by to
ORDER BY [rss!1!],[item!2!pubdate!element] DESC,Tag
FOR XML EXPLICIT

FOR EXPLICIT

i don't think the documentation could be more confusing.
Consider:
CREATE TABLE MyNewsEntries(
guid uniqueidentifier,
title varchar(200),
description text,
pubDate datetime,
imageFilename varchar(260),
imageMimeType varchar(100) )
Desired output:
<rss>
<item>
<title>MyNewsEntries.title</title>
<description>MyNewsEntries.description</description>
<pubDate>MyNewsEntries.pubDate</pubDate>
<guid>MyNewsEntries.guid</guid>
<enclosure url="[MyNewsEntires.imageFilename]"
type="[MyNewsEntries.imageMimeType]" />
</item>
<item>
...
</item>
</rss>
Ordered by MyNewsEntries.pubDate DESC
Now, after much cursing and swearing, i managed to vomit up:
SELECT
1 AS Tag, NULL AS Parent,
NULL AS [rss!1!],
NULL AS [item!2!title!element],
NULL AS [item!2!description!element],
NULL AS [item!2!pubdate!element],
NULL AS [item!2!guid!element],
NULL AS [enclosure!3!url],
NULL AS [enclosure!3!type]
UNION ALL
SELECT
2 AS Tag, 1 AS Parent,
NULL as [rss!1!element],
title AS [item!2!title!element],
description AS [item!2!description!element],
pubDate AS [item!2!pubdate!element],
guid AS [item!2!guid!element],
imageFilename AS [enclosure!2!url],
NULL AS [enclosure!2!type]
FROM MyNewsEntries
UNION ALL
SELECT
3 AS Tag, 2 AS Parent,
NULL AS [rss!1!element],
title AS [item!2!title!element],
NULL AS [item!2!description!element],
pubDate AS [item!2!pubdate!element],
guid AS [item!2!guid!element],
imageFilename AS [enclosure!3!url],
imageMimeType AS [enclosure!3!type]
FROM MyNewsEntries
FOR XML EXPLICIT
Which runs, but the order is wrong. All the enclosures are appearing at the
end.
If i try
ORDER BY [item!2!pubdate!element] DESC
FOR XML EXPLICIT
The it puts the rss entry at the end, and complains:
| Parent tag ID 1 is not among the open tags.
| FOR XML EXPLICIT requires parent tags to be opened first.
| Check the ordering of the result set.
So i try
SELECT * FROM (
My entire query above
) DerivedTable
ORDER BY
CASE
WHEN (Parent IS NULL) THEN 0
WHEN (Parent = 0) THEN 0
ELSE 99999
END,
[item!2!pubdate!element] DESC
But now it is mixing the order of Tag=2 and Tag=3 elements, putting all
<enclosures> in one <item>
So i try:
SELECT * FROM (
My entire query above
) DerivedTable
ORDER BY
CASE
WHEN (Parent IS NULL) THEN 0
WHEN (Parent = 0) THEN 0
ELSE 99999
END,
Tag,
[item!2!pubdate!element] DESC
Which seems to work.
My question is: Is this what i have to do to get SQL Server to return XML in
an explicit format?
Go back to your original query, change
NULL AS [rss!1!element],
to
1 AS [rss!1!element],
everywhere *except* under tag 1 (the first)
which should be left as null.
Now change your order by to
ORDER BY [rss!1!],[item!2!pubdate!element] DESC,Tag
FOR XML EXPLICIT

FOR EACH LOOP in T-SQL

Hello,
I am inserting a list of database names from sysdatabases into a temp
table, below is the T-SQL.
CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY KEY)
INSERT INTO ##SpringClean
SELECT DISTINCT dbo.sysdatabases.name
FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
AND dbo.sysdatabases.name NOT IN
('master','model','msdb','Northwind','pubs','tempdb')
I would like to code a loop in T-SQL that will cycle through each database
name in the above temp table and execute the following select
USE (db name from temp table)
SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
FROM sysfiles
GO
I am trying to get an accurate query of the size of my databases. Any help
would be greatly appreciated.
JoeYou can use a cursor for that, and loop the cursor. See DECLARE (CURSOR) in
Books Online. You would not need a temptable for this.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Joe G" <invalid@.invalid.com> wrote in message
news:3fb2525c@.usenet01.boi.hp.com...
> Hello,
> I am inserting a list of database names from sysdatabases into a temp
> table, below is the T-SQL.
> CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY KEY)
> INSERT INTO ##SpringClean
> SELECT DISTINCT dbo.sysdatabases.name
> FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> AND dbo.sysdatabases.name NOT IN
> ('master','model','msdb','Northwind','pubs','tempdb')
> I would like to code a loop in T-SQL that will cycle through each database
> name in the above temp table and execute the following select
> USE (db name from temp table)
> SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> FROM sysfiles
> GO
> I am trying to get an accurate query of the size of my databases. Any
help
> would be greatly appreciated.
> Joe
>
>|||declare @.sql varchar(4000)
declare @.db varchar(64)
set @.db=''
SELECT @.db=min(name)
FROM sysdatabases
WHERE name NOT IN ('master','msdb','tempdb') -- all system databases
and name > @.db
while @.db is not null
begin
set @.sql='use '+@.db+'
SELECT SUM(sysfiles.size * 8/1024) AS "Database Size
'+@.db+'"
FROM sysfiles'
exec (@.sql)
SELECT @.db=min(name)
FROM sysdatabases
WHERE name NOT IN ('master','msdb','tempdb')
and name > @.db
end
Hope this helps,
Gert-Jan
Joe G wrote:
> Hello,
> I am inserting a list of database names from sysdatabases into a temp
> table, below is the T-SQL.
> CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY KEY)
> INSERT INTO ##SpringClean
> SELECT DISTINCT dbo.sysdatabases.name
> FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> AND dbo.sysdatabases.name NOT IN
> ('master','model','msdb','Northwind','pubs','tempdb')
> I would like to code a loop in T-SQL that will cycle through each database
> name in the above temp table and execute the following select
> USE (db name from temp table)
> SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> FROM sysfiles
> GO
> I am trying to get an accurate query of the size of my databases. Any help
> would be greatly appreciated.
> Joe|||Wow,
Thanks very much, this was extremely helpful. I am now going to try and
figure out what you did in your code. I appreciate the effort.
Joe
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3FB27693.F0B27DA1@.toomuchspamalready.nl...
> declare @.sql varchar(4000)
> declare @.db varchar(64)
> set @.db=''
> SELECT @.db=min(name)
> FROM sysdatabases
> WHERE name NOT IN ('master','msdb','tempdb') -- all system databases
> and name > @.db
> while @.db is not null
> begin
> set @.sql='use '+@.db+'
> SELECT SUM(sysfiles.size * 8/1024) AS "Database Size
> '+@.db+'"
> FROM sysfiles'
> exec (@.sql)
> SELECT @.db=min(name)
> FROM sysdatabases
> WHERE name NOT IN ('master','msdb','tempdb')
> and name > @.db
> end
> Hope this helps,
> Gert-Jan
>
> Joe G wrote:
> >
> > Hello,
> >
> > I am inserting a list of database names from sysdatabases into a temp
> > table, below is the T-SQL.
> >
> > CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY KEY)
> > INSERT INTO ##SpringClean
> > SELECT DISTINCT dbo.sysdatabases.name
> > FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> > WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> > AND dbo.sysdatabases.name NOT IN
> > ('master','model','msdb','Northwind','pubs','tempdb')
> >
> > I would like to code a loop in T-SQL that will cycle through each
database
> > name in the above temp table and execute the following select
> >
> > USE (db name from temp table)
> > SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> > FROM sysfiles
> > GO
> >
> > I am trying to get an accurate query of the size of my databases. Any
help
> > would be greatly appreciated.
> >
> > Joe|||PSS.
It worked, I just want to figure out what you did now.
Joe
"Joe G" <invalid@.invalid.com> wrote in message
news:3fb283fc@.usenet01.boi.hp.com...
> Wow,
> Thanks very much, this was extremely helpful. I am now going to try and
> figure out what you did in your code. I appreciate the effort.
> Joe
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:3FB27693.F0B27DA1@.toomuchspamalready.nl...
> > declare @.sql varchar(4000)
> > declare @.db varchar(64)
> > set @.db=''
> >
> > SELECT @.db=min(name)
> > FROM sysdatabases
> > WHERE name NOT IN ('master','msdb','tempdb') -- all system databases
> > and name > @.db
> >
> > while @.db is not null
> > begin
> >
> > set @.sql='use '+@.db+'
> > SELECT SUM(sysfiles.size * 8/1024) AS "Database Size
> > '+@.db+'"
> > FROM sysfiles'
> > exec (@.sql)
> >
> > SELECT @.db=min(name)
> > FROM sysdatabases
> > WHERE name NOT IN ('master','msdb','tempdb')
> > and name > @.db
> > end
> >
> > Hope this helps,
> > Gert-Jan
> >
> >
> > Joe G wrote:
> > >
> > > Hello,
> > >
> > > I am inserting a list of database names from sysdatabases into a
temp
> > > table, below is the T-SQL.
> > >
> > > CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY KEY)
> > > INSERT INTO ##SpringClean
> > > SELECT DISTINCT dbo.sysdatabases.name
> > > FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> > > WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> > > AND dbo.sysdatabases.name NOT IN
> > > ('master','model','msdb','Northwind','pubs','tempdb')
> > >
> > > I would like to code a loop in T-SQL that will cycle through each
> database
> > > name in the above temp table and execute the following select
> > >
> > > USE (db name from temp table)
> > > SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> > > FROM sysfiles
> > > GO
> > >
> > > I am trying to get an accurate query of the size of my databases. Any
> help
> > > would be greatly appreciated.
> > >
> > > Joe
>|||Joe, another method is this single command... Bruce
exec sp_MSforeachDB @.command1="SELECT SUM(size * 8/1024)
AS 'Database Size', '?' FROM ?.dbo.sysfiles"
>--Original Message--
>Hello,
> I am inserting a list of database names from
sysdatabases into a temp
>table, below is the T-SQL.
>CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY
KEY)
>INSERT INTO ##SpringClean
>SELECT DISTINCT dbo.sysdatabases.name
>FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
>WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
>AND dbo.sysdatabases.name NOT IN
>('master','model','msdb','Northwind','pubs','tempdb')
>I would like to code a loop in T-SQL that will cycle
through each database
>name in the above temp table and execute the following
select
>USE (db name from temp table)
>SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
>FROM sysfiles
>GO
>I am trying to get an accurate query of the size of my
databases. Any help
>would be greatly appreciated.
>Joe
>
>.
>|||I can't seem to run this against a remote server, only my personal copy of
SQL Server located on my laptop. Is this a requirement for this stored
proc?
By the way, this was an amazing command none the less.
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:045301c3a960$5bbeeea0$a301280a@.phx.gbl...
> Joe, another method is this single command... Bruce
> exec sp_MSforeachDB @.command1="SELECT SUM(size * 8/1024)
> AS 'Database Size', '?' FROM ?.dbo.sysfiles"
>
>
> >--Original Message--
> >Hello,
> >
> > I am inserting a list of database names from
> sysdatabases into a temp
> >table, below is the T-SQL.
> >
> >CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY
> KEY)
> >INSERT INTO ##SpringClean
> >SELECT DISTINCT dbo.sysdatabases.name
> >FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> >WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> >AND dbo.sysdatabases.name NOT IN
> >('master','model','msdb','Northwind','pubs','tempdb')
> >
> >I would like to code a loop in T-SQL that will cycle
> through each database
> >name in the above temp table and execute the following
> select
> >
> >USE (db name from temp table)
> >SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> >FROM sysfiles
> >GO
> >
> >I am trying to get an accurate query of the size of my
> databases. Any help
> >would be greatly appreciated.
> >
> >Joe
> >
> >
> >
> >.
> >|||Actually, it has nothing to do with me executing it locally, when I execute
it on other databases I get the following error
"Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_MSforeachDB'."
Does anyone know why?
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:045301c3a960$5bbeeea0$a301280a@.phx.gbl...
> Joe, another method is this single command... Bruce
> exec sp_MSforeachDB @.command1="SELECT SUM(size * 8/1024)
> AS 'Database Size', '?' FROM ?.dbo.sysfiles"
>
>
> >--Original Message--
> >Hello,
> >
> > I am inserting a list of database names from
> sysdatabases into a temp
> >table, below is the T-SQL.
> >
> >CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY
> KEY)
> >INSERT INTO ##SpringClean
> >SELECT DISTINCT dbo.sysdatabases.name
> >FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> >WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> >AND dbo.sysdatabases.name NOT IN
> >('master','model','msdb','Northwind','pubs','tempdb')
> >
> >I would like to code a loop in T-SQL that will cycle
> through each database
> >name in the above temp table and execute the following
> select
> >
> >USE (db name from temp table)
> >SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> >FROM sysfiles
> >GO
> >
> >I am trying to get an accurate query of the size of my
> databases. Any help
> >would be greatly appreciated.
> >
> >Joe
> >
> >
> >
> >.
> >|||I'm running it on SQL 2000. I THINK it's available on
SQL 7 also? are you on SQL 2000? Can you see that
proc in the master database? If it's there and you have
permission to run it, not sure why you get that message.
I use the DB and TABLE ForEach procs all the time for
short commands like that... Bruce
>--Original Message--
>Actually, it has nothing to do with me executing it
locally, when I execute
>it on other databases I get the following error
> "Server: Msg 2812, Level 16, State 62, Line 1
>Could not find stored procedure 'sp_MSforeachDB'."
>Does anyone know why?
>
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:045301c3a960$5bbeeea0$a301280a@.phx.gbl...
>> Joe, another method is this single command... Bruce
>> exec sp_MSforeachDB @.command1="SELECT SUM(size *
8/1024)
>> AS 'Database Size', '?' FROM ?.dbo.sysfiles"
>>
>>
>> >--Original Message--
>> >Hello,
>> >
>> > I am inserting a list of database names from
>> sysdatabases into a temp
>> >table, below is the T-SQL.
>> >
>> >CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY
>> KEY)
>> >INSERT INTO ##SpringClean
>> >SELECT DISTINCT dbo.sysdatabases.name
>> >FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
>> >WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
>> >AND dbo.sysdatabases.name NOT IN
>> >('master','model','msdb','Northwind','pubs','tempdb')
>> >
>> >I would like to code a loop in T-SQL that will cycle
>> through each database
>> >name in the above temp table and execute the following
>> select
>> >
>> >USE (db name from temp table)
>> >SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
>> >FROM sysfiles
>> >GO
>> >
>> >I am trying to get an accurate query of the size of my
>> databases. Any help
>> >would be greatly appreciated.
>> >
>> >Joe
>> >
>> >
>> >
>> >.
>> >
>
>.
>|||Perhaps the SQL Server is case sensitive? The name of the procedure is
sp_MSforeachdb.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:069801c3a98f$5f32fb60$a401280a@.phx.gbl...
> I'm running it on SQL 2000. I THINK it's available on
> SQL 7 also? are you on SQL 2000? Can you see that
> proc in the master database? If it's there and you have
> permission to run it, not sure why you get that message.
> I use the DB and TABLE ForEach procs all the time for
> short commands like that... Bruce
> >--Original Message--
> >Actually, it has nothing to do with me executing it
> locally, when I execute
> >it on other databases I get the following error
> >
> > "Server: Msg 2812, Level 16, State 62, Line 1
> >Could not find stored procedure 'sp_MSforeachDB'."
> >
> >Does anyone know why?
> >
> >
> >
> >
> >
> >"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
> >news:045301c3a960$5bbeeea0$a301280a@.phx.gbl...
> >> Joe, another method is this single command... Bruce
> >>
> >> exec sp_MSforeachDB @.command1="SELECT SUM(size *
> 8/1024)
> >> AS 'Database Size', '?' FROM ?.dbo.sysfiles"
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >Hello,
> >> >
> >> > I am inserting a list of database names from
> >> sysdatabases into a temp
> >> >table, below is the T-SQL.
> >> >
> >> >CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY
> >> KEY)
> >> >INSERT INTO ##SpringClean
> >> >SELECT DISTINCT dbo.sysdatabases.name
> >> >FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> >> >WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> >> >AND dbo.sysdatabases.name NOT IN
> >> >('master','model','msdb','Northwind','pubs','tempdb')
> >> >
> >> >I would like to code a loop in T-SQL that will cycle
> >> through each database
> >> >name in the above temp table and execute the following
> >> select
> >> >
> >> >USE (db name from temp table)
> >> >SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> >> >FROM sysfiles
> >> >GO
> >> >
> >> >I am trying to get an accurate query of the size of my
> >> databases. Any help
> >> >would be greatly appreciated.
> >> >
> >> >Joe
> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Another save by the good of the community. I was so deep into the issue at
hand yesterday I didn't even think to check the case sensitivity. That was
the issue. I remember inspecting all of the databases it was running
against and finding the stored proc but I couldn't figure out why it
wouldn't run. Case sensitivity.
Thanks a million.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OgiVv7bqDHA.2488@.TK2MSFTNGP12.phx.gbl...
> Perhaps the SQL Server is case sensitive? The name of the procedure is
> sp_MSforeachdb.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Bruce de Freitas" <bruce@.defreitas.com> wrote in message
> news:069801c3a98f$5f32fb60$a401280a@.phx.gbl...
> > I'm running it on SQL 2000. I THINK it's available on
> > SQL 7 also? are you on SQL 2000? Can you see that
> > proc in the master database? If it's there and you have
> > permission to run it, not sure why you get that message.
> > I use the DB and TABLE ForEach procs all the time for
> > short commands like that... Bruce
> >
> > >--Original Message--
> > >Actually, it has nothing to do with me executing it
> > locally, when I execute
> > >it on other databases I get the following error
> > >
> > > "Server: Msg 2812, Level 16, State 62, Line 1
> > >Could not find stored procedure 'sp_MSforeachDB'."
> > >
> > >Does anyone know why?
> > >
> > >
> > >
> > >
> > >
> > >"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
> > >news:045301c3a960$5bbeeea0$a301280a@.phx.gbl...
> > >> Joe, another method is this single command... Bruce
> > >>
> > >> exec sp_MSforeachDB @.command1="SELECT SUM(size *
> > 8/1024)
> > >> AS 'Database Size', '?' FROM ?.dbo.sysfiles"
> > >>
> > >>
> > >>
> > >>
> > >> >--Original Message--
> > >> >Hello,
> > >> >
> > >> > I am inserting a list of database names from
> > >> sysdatabases into a temp
> > >> >table, below is the T-SQL.
> > >> >
> > >> >CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY
> > >> KEY)
> > >> >INSERT INTO ##SpringClean
> > >> >SELECT DISTINCT dbo.sysdatabases.name
> > >> >FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> > >> >WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> > >> >AND dbo.sysdatabases.name NOT IN
> > >> >('master','model','msdb','Northwind','pubs','tempdb')
> > >> >
> > >> >I would like to code a loop in T-SQL that will cycle
> > >> through each database
> > >> >name in the above temp table and execute the following
> > >> select
> > >> >
> > >> >USE (db name from temp table)
> > >> >SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> > >> >FROM sysfiles
> > >> >GO
> > >> >
> > >> >I am trying to get an accurate query of the size of my
> > >> databases. Any help
> > >> >would be greatly appreciated.
> > >> >
> > >> >Joe
> > >> >
> > >> >
> > >> >
> > >> >.
> > >> >
> > >
> > >
> > >.
> > >
>