Return Duplicate Rows

I want to select all my columns that have the same number twice....but i dont know how....I tried doit it this way

Select Distinct sti.parent_topic_id, st.description_short, sti.sort_order, count(st.description_short)

From sam_topic_items sti

Inner Join sam_topic st On sti.topic_id = st.id

Where st.type = 'Topic'

Group By sti.parent_topic_id, st.description_short, sti.sort_order

--Having count(sti.sort_order) > 1

Order By parent_topic_id

I even tried sti.sort_order = sti.sort_order and that does not work eithier

But it returns everything...when i uncomment the having clause it does not return anything....I want my end result to be

2 TheName 10 1

2 SomthingElse 10 1

You see they have the same id and the same sort_order but diffrent description names.......when i run the above query it returns everything for some reason...I am not clear on how i need to set the criteria to only pull those rows with the same sort_order number for a particular ID? any help?

select *
from sam_topic_items sti

inner join sam_topic st

on sti.topic_id = st.id
inner join
(
select sti.topic_id
from sam_topic_items sti
inner join sam_topic st
on sti.topic_id = st.id
where st.type = 'Topic'
group by sti.topic_id
having count(*) > 1
) d
on st.topic_id = d.topic_id|||

Thanks 4 the response but this returns all my rows with the sam ID's instead of the same sort order...I have a table like this

id sort_order

1 2

1 2

1 1

1 3

I only want to return.......

id sort_order

1 2

1 2

I thought if i did it like you had it but tweak it a little it would work....but it still returns all the rows

select *

from sam_topic_items sti

inner join sam_topic st

on sti.parent_topic_id = st.id

inner join

(

select sti.parent_topic_id, sti.sort_order

from sam_topic_items sti

inner join sam_topic st

on sti.parent_topic_id = st.id

where st.type = 'Topic'

group by sti.parent_topic_id, sti.sort_order

having count(*) > 1

) d

on sti.sort_order = d.sort_order

|||

I think you need to join by both sort_order and topic_id.

A more basic question is if you know where the duplication is coming from.

Are there multiple records in the topic table (of type 'topic') which have the same ID?|||There are multiple items in the topic_item tables that have the same number in thier sort order...and I only want to select those id's that have the same number in there sort_order....my topic table has a PK of id and my topic_items table has a PK of topic ID and a PK of parent_topic_id.....|||

When I say primary key I mean a column (or columns) which uniquely identify a single record in the table. That's means there cannot be duplicates in the primary key. I am interested in topic_id vs. parent_topic_id are these the same field or related fields. They look like foreign keys (fields which link to a primary key on another table).

The main issue in your query seems to be multiple records in the topic_items table with the same topic_ID (or parent_topic_ID I'm not sure) and the same sort order.

If this is the case then:

SELECT topic_ID, sort_order
FROM topic_items
GROUP BY topic_ID, sort_order
HAVING count(*) > 1

should give a list of these records (but only 1 instance of each combination). Then joining it back to the other tables should give the record list you want.

SELECT ST.ID, STI.description_short, STI.sort_order
FROM (
SELECT topic_ID, sort_order
FROM sam_topic_items
GROUP BY topic_ID, sort_order
HAVING count(*) > 1
) TS
INNER JOIN sam_topic_items STI
ON (TS.topic_ID = STI.topic_ID) AND
(TS.sort_order = STI.sort_order)
INNER JOIN sam_topic ST
ON (STI.topic_id = ST.id)
WHERE (ST.type = 'Topic')
ORDER BY ST.ID

This is all testing by topic_id. If it should be parent_topic_id then change it to this throughout the query.

There is also still the question of what the type field does in selecting records (as I say do you have records other than type='Topic' in the sam_topic table?). Depending upon exactly what this does it may cause problems with the SQL to pick up the duplicates.

|||

Yes there is other records other than "Topic" there is also "ROOT" but I am just worrying about "Topic" and nothing else....This is how I had to change the query it works now partially

SELECT STI.parent_topic_id, ST.description_short, STI.sort_order

FROM (

SELECT parent_topic_id, sort_order

FROM sam_topic_items

GROUP BY parent_topic_id, sort_order

HAVING count(sort_order) > 1

) TS

INNER JOIN sam_topic_items STI

ON (TS.sort_order = STI.sort_order)

INNER JOIN sam_topic ST

ON (STI.topic_id = ST.id)

WHERE (ST.type = 'Topic')

ORDER BY STI.parent_topic_id

but it still does not return only the rows with duplicate sort order for a particular parent_topic_id this query returns

id description_short sort_order

2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1.....thanks 4 the help though!!!

See how it returns all these results for the same thing?...in my table there is only one "<1mm" for the parent_topic_id of 2131 but this returns like 20 of them...I dont know how it did that?

|||

Change your having clause to:

HAVING count(*) >1

|||Yea i did that I still get the same result though?|||

Yeah I get it. I didn't see the whole thing.

You see they have the same id and the same sort_order but diffrent description names.......when i run the above query it returns everything for some reason...I am not clear on how i need to set the criteria to only pull those rows with the same sort_order number for a particular ID? any help?

You need to take the description name out of the group by, otherwise you will count the values with the same id but different descriptions as different rows. To count the number of occurences of a different description for the same id you need to take the description out of your group.

|||Ok here is a better question this query

Select Distinct sti.parent_topic_id, st.Type, sti.sort_order, count(sti.sort_order)

From sam_topic_items sti

Inner Join sam_topic st On sti.topic_id = st.id

Where st.type = 'Topic'

Group By sti.parent_topic_id, sti.sort_order, st.Type

Having count(sti.sort_order) > 1

Order By parent_topic_id

Returns this data exactly

parent_topic_id type sort_order the last column is the number of occurences that the sort_order has.....like if you look at the first row of data.....there are 3 occurences of 0 for parent_topic_id 2334...

2334 TOPIC 0 3
2398 TOPIC 32 2
2399 TOPIC 16 2
2430 TOPIC 22 2
2447 TOPIC 26 2
2447 TOPIC 12 2
2447 TOPIC 15 2
2452 TOPIC 1 3
2505 TOPIC 37 2
2505 TOPIC 52 2
2505 TOPIC 3 2
2507 TOPIC 6 2
2526 TOPIC 32 2
2549 TOPIC 54 2
2549 TOPIC 52 2
2562 TOPIC 0 2
2722 TOPIC 2 2
2880 TOPIC 0 2
2995 TOPIC 0 2
2997 TOPIC 0 2
3000 TOPIC 0 2
3001 TOPIC 0 2
3040 TOPIC 6 2
3152 TOPIC 26 2
3244 TOPIC 0 3
3524 TOPIC 0 2
3605 TOPIC 0 2
3638 TOPIC 0 2
3721 TOPIC 2 2
3723 TOPIC 1 2
4082 TOPIC 4 2
4100 TOPIC 1 3
4181 TOPIC 11 2
4220 TOPIC 0 2
4261 TOPIC 2 2
4262 TOPIC 1 2
4302 TOPIC 3 2
4302 TOPIC 1 2
4302 TOPIC 2 2
4439 TOPIC 0 2
5029 TOPIC 2 3
5032 TOPIC 2 4
5042 TOPIC 2 6
5042 TOPIC 11 2
5071 TOPIC 2 3
5297 TOPIC 2 2
5297 TOPIC 3 2
5297 TOPIC 1 2
5368 TOPIC 52 2
5368 TOPIC 3 2
5368 TOPIC 36 2
5368 TOPIC 48 2
5368 TOPIC 37 2
5368 TOPIC 54 2
5849 TOPIC 45 2
5882 TOPIC 16 2
5882 TOPIC 42 2
5882 TOPIC 54 2
5882 TOPIC 11 2
5882 TOPIC 55 3
5882 TOPIC 60 2
5882 TOPIC 40 2
6032 TOPIC 0 3
6137 TOPIC 0 2
8897 TOPIC 3 2
9461 TOPIC 2 2
9461 TOPIC 1 2
9598 TOPIC 11 2
9608 TOPIC 54 2
10345 TOPIC 0 2
10366 TOPIC 2 2
10627 TOPIC 17 2
11992 TOPIC 42 2
12081 TOPIC 4 2
12083 TOPIC 3 3
12298 TOPIC 20 2
12701 TOPIC 0 2
12716 TOPIC 9 4
12717 TOPIC 4 2
12750 TOPIC 6 2
12755 TOPIC 3 2
12760 TOPIC 1 2
12760 TOPIC 3 2
12760 TOPIC 4 2
12760 TOPIC 2 2
12926 TOPIC 5 3
12990 TOPIC 8 6
12998 TOPIC 9 4
12998 TOPIC 6 2
13001 TOPIC 2 2
13069 TOPIC 5 2
13069 TOPIC 1 2
13132 TOPIC 8 2
13134 TOPIC 4 2
13349 TOPIC 5 3
13429 TOPIC 4 2
13429 TOPIC 19 3
13429 TOPIC 1 2
13429 TOPIC 3 2
13429 TOPIC 6 2

Now using the above query how would i write a sub query to use the data return in this result to show some other data in the same result set?

|||

Like what? I thought thats what you wanted to show?

Your result set is going to be grouped.

It will only have a single row for each group.

You can do simple tricks like putting an aggregate function like MIN function to show things like the description but you will only show a single description for the grouped row.

What is it exactly that you are trying to show?

Have you considered an sp rather than a single query?

ALSO, please not that you DO NOT need st.Type in the grouping or the field list since your where criteria clearly states that it has to be equal to 'TOPIC'.

|||

thanks liz.....

If you look at the results for the query....and look at the id of 2334 it has 3 instances of 0 in that id....I want to be able to show

2334 TheNAme 0

2334 The OtherName 0

2334 The OtherName 0

I want to show the id the description and the sort order.....Only for those duplicate sort orders and thats it.....I could write a vb or C# app to do it....but i need to learn sql, haha as you can see i dont know it well!!! thanks!!

|||

It is real ugly but, if you are using 2 values you need to convert them to a char string and use them in the where clause together in the outer query...

Select sti.parent_topic_id, st.description_short, sti.sort_order
From sam_topic_items sti
Inner Join sam_topic st On sti.topic_id = st.id
Where convert(char(20), sti.parent_topic_id) + convert(char(20), sti.sort_order) IN
(
Select convert(char(20), sti.parent_topic_id) + convert(char(20), sti.sort_order)
From sam_topic_items sti
Inner Join sam_topic st On sti.topic_id = st.id
Where st.type = 'Topic'
Group By sti.parent_topic_id, sti.sort_order, st.Type
Having count(sti.sort_order) > 1
Order By parent_topic_id
)

I don't know if this will execute properly because I don't have the right tables to test it but it is the general idea...

|||

First off please explain to me what the convert method did? I need to know what that did to the query to make it act like so....

BUT you are a WIZ!!!!!!!!! unless I am just that mentally slow THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!......

Return DISTINCT Values

Hi,

How do I ensure that DISTINCT values of r.GPositionID are returned from the below?

Code Snippet

SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType

FROM @.GResults r

LEFT OUTER JOIN

ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY GCustodian, GCustodianAccount, GAssetType;

Thanks.

You can apply the distinct key word after the select clause – if all the column set values are duplicate,

Code Snippet

SELECT Distinct

CommentImage AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

If column set values (CommentImage, GCustodian, GCustodianAccount, GAssetType) are not unique you can apply group functions – it may cauase some data lose.

Code Snippet

SELECT Distinct

Max(CommentImage) AS ViewComment

, r.GPositionID

, Max(GCustodian)

, Max(GCustodianAccount)

, Max(GAssetType)

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

Group BY

r.GPositionID

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

CommentImage has dataType 'Image'

Using DISTINCT with it gives the below error...

The text, ntext, or image data type cannot be selected as DISTINCT.

|||

The following query might help you,

Select

(select Top 1 CommentImage from ReconComments s where s.GPositionID=data.GPositionID),

, GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

From

(

SELECT Distinct

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

) as data

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

Get this error...

The text, ntext, and image data types are invalid in this subquery or aggregate expression

|||Yes SQL Server 2000 cause this error..let me check the solution for this.|||r.GPositionID,GCustodian,GCustodianAcc ount,GAssetType are all from the one table.
So I want the distinct values of this table returned.

Using SLQ Server 2005

thanks.

|||

If you really use SQL Server 2005 (check using => print @.@.version) database then the following query work fine,

MS Recommandation: Change your Image datatype to varbinary(max)

Code Snippet

SELECT Distinct

Cast(CommentImage as varbinary(max)) AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

When I click the 'Help' > 'About' link it tells me its Microsoft SQL Server 2005.

Using

print @.@.version

tells me this...

Microsoft SQL Server 2000 - 8.00.878 (Intel X86)

|||

That means you connected SQL Server 2000 server from the Management Studio (2005 Client tool).

Let me clarify where the images are stored - is it in different table (ReconComments) .

Is there any possibilty to have duplicate images for one GPositionID.

|||Sorry - it is possible for one GPositionID to have duplicate images.|||

GPositionID and GCustodian are in the one table.
CommentImage is from a related table.

There is a M:M relation.

Here's the tables structure:

RComments Tbl:

RCommentsID int PK,
CommentImage image,
GPositionID int FK

@.GResults Tbl:

GPositionID int PK,
GCustodian varchar(250),
GCustodianAccount varchar(250),
GAssetType varchar(250)

Return DISTINCT Values

Hi,

How do I ensure that DISTINCT values of r.GPositionID are returned from the below?

Code Snippet

SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType

FROM @.GResults r

LEFT OUTER JOIN

ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY GCustodian, GCustodianAccount, GAssetType;

Thanks.

You can apply the distinct key word after the select clause – if all the column set values are duplicate,

Code Snippet

SELECT Distinct

CommentImage AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

If column set values (CommentImage, GCustodian, GCustodianAccount, GAssetType) are not unique you can apply group functions – it may cauase some data lose.

Code Snippet

SELECT Distinct

Max(CommentImage) AS ViewComment

, r.GPositionID

, Max(GCustodian)

, Max(GCustodianAccount)

, Max(GAssetType)

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

Group BY

r.GPositionID

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

CommentImage has dataType 'Image'

Using DISTINCT with it gives the below error...

The text, ntext, or image data type cannot be selected as DISTINCT.

|||

The following query might help you,

Select

(select Top 1 CommentImage from ReconComments s where s.GPositionID=data.GPositionID),

, GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

From

(

SELECT Distinct

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

) as data

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

Get this error...

The text, ntext, and image data types are invalid in this subquery or aggregate expression

|||Yes SQL Server 2000 cause this error..let me check the solution for this.|||r.GPositionID,GCustodian,GCustodianAcc ount,GAssetType are all from the one table.
So I want the distinct values of this table returned.

Using SLQ Server 2005

thanks.

|||

If you really use SQL Server 2005 (check using => print @.@.version) database then the following query work fine,

MS Recommandation: Change your Image datatype to varbinary(max)

Code Snippet

SELECT Distinct

Cast(CommentImage as varbinary(max)) AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

When I click the 'Help' > 'About' link it tells me its Microsoft SQL Server 2005.

Using

print @.@.version

tells me this...

Microsoft SQL Server 2000 - 8.00.878 (Intel X86)

|||

That means you connected SQL Server 2000 server from the Management Studio (2005 Client tool).

Let me clarify where the images are stored - is it in different table (ReconComments) .

Is there any possibilty to have duplicate images for one GPositionID.

|||Sorry - it is possible for one GPositionID to have duplicate images.|||

GPositionID and GCustodian are in the one table.
CommentImage is from a related table.

There is a M:M relation.

Here's the tables structure:

RComments Tbl:

RCommentsID int PK,
CommentImage image,
GPositionID int FK

@.GResults Tbl:

GPositionID int PK,
GCustodian varchar(250),
GCustodianAccount varchar(250),
GAssetType varchar(250)

return big string

Hello,
How can I return more then 8000 characters from a store proc.?
When I do this
CREATE PROCEDURE SP_GetClassCode
AS
SELECT 'very long string'
GO
It work's
But when I do this
CREATE PROCEDURE SP_GetClassCode
@.Replace AS VARCHAR(50)
AS
SELECT 'very long ' + @.Replace
GO
This time it only return the 8000 first characters
What do I have to do to return more then 8000 characters from a Store Proc?
Thank you
Marc R.Marc Robitaille wrote:
> Hello,
> How can I return more then 8000 characters from a store proc.?
> When I do this
> CREATE PROCEDURE SP_GetClassCode
> AS
> SELECT 'very long string'
> GO
> It work's
> But when I do this
> CREATE PROCEDURE SP_GetClassCode
> @.Replace AS VARCHAR(50)
> AS
> SELECT 'very long ' + @.Replace
> GO
> This time it only return the 8000 first characters
> What do I have to do to return more then 8000 characters from a Store
> Proc?
> Thank you
> Marc R.
You could return the values as two or more columns and concatenate on
the client. The data type you are working with (char/varchar) is limited
to 8000 bytes.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Is there an other datatype that I could use to return what I need?
"David Gugick" <david.gugick-nospam@.quest.com> a écrit dans le message de
news: %235Wco9uhFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Marc Robitaille wrote:
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
> You could return the values as two or more columns and concatenate on the
> client. The data type you are working with (char/varchar) is limited to
> 8000 bytes.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||How are you determining the length returned? Query Analyzer has a limit of
8000 bytes per column. And you should avoid using sp_ as a prefix to stored
procedures.
--
Andrew J. Kelly SQL MVP
"Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> How can I return more then 8000 characters from a store proc.?
> When I do this
> CREATE PROCEDURE SP_GetClassCode
> AS
> SELECT 'very long string'
> GO
> It work's
> But when I do this
> CREATE PROCEDURE SP_GetClassCode
> @.Replace AS VARCHAR(50)
> AS
> SELECT 'very long ' + @.Replace
> GO
> This time it only return the 8000 first characters
> What do I have to do to return more then 8000 characters from a Store
> Proc?
> Thank you
> Marc R.
>|||I try to build a VB.NET class with a Store proc. I specify the name of the
table to my SP then it return's a string. If I Copy/Paste the string in a
VB file, I have a fully fonctional class that represent my table. So, I have
writen a template that I use in my SP. My template has 38000 characters. In
some place, in my template, there are speacials words that are going to be
replace when the SP is execute. When I execute the SP without parameters, my
template is return entirely but with no modification in the template. When I
execute the SP with a parameter, only the 8000 first characters are return
with the modification. I don't run the SP in Query analyser but in a VB.NET
programm that I did. How can I return a big string with parameters?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message de
news: O%23Q81JvhFHA.1044@.tk2msftngp13.phx.gbl...
> How are you determining the length returned? Query Analyzer has a limit
> of 8000 bytes per column. And you should avoid using sp_ as a prefix to
> stored procedures.
> --
> Andrew J. Kelly SQL MVP
>
> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
> news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
>|||It looks like SQL Server is implicitly converting your string to the
datatype of the variable you are concatenating with it. You can create a
table variable with a text column and build your string in the text column.
The issue a select from the table variable at the end to get the full
output.
--
Andrew J. Kelly SQL MVP
"Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
news:e4PyfcvhFHA.3124@.TK2MSFTNGP12.phx.gbl...
>I try to build a VB.NET class with a Store proc. I specify the name of the
>table to my SP then it return's a string. If I Copy/Paste the string in a
>VB file, I have a fully fonctional class that represent my table. So, I
>have writen a template that I use in my SP. My template has 38000
>characters. In some place, in my template, there are speacials words that
>are going to be replace when the SP is execute. When I execute the SP
>without parameters, my template is return entirely but with no modification
>in the template. When I execute the SP with a parameter, only the 8000
>first characters are return with the modification. I don't run the SP in
>Query analyser but in a VB.NET programm that I did. How can I return a big
>string with parameters?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message de
> news: O%23Q81JvhFHA.1044@.tk2msftngp13.phx.gbl...
>> How are you determining the length returned? Query Analyzer has a limit
>> of 8000 bytes per column. And you should avoid using sp_ as a prefix to
>> stored procedures.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
>> news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
>>
>|||Great idea
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message de
news: eFA9CewhFHA.1052@.TK2MSFTNGP10.phx.gbl...
> It looks like SQL Server is implicitly converting your string to the
> datatype of the variable you are concatenating with it. You can create a
> table variable with a text column and build your string in the text
> column. The issue a select from the table variable at the end to get the
> full output.
> --
> Andrew J. Kelly SQL MVP
>
> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
> news:e4PyfcvhFHA.3124@.TK2MSFTNGP12.phx.gbl...
>>I try to build a VB.NET class with a Store proc. I specify the name of
>>the table to my SP then it return's a string. If I Copy/Paste the string
>>in a VB file, I have a fully fonctional class that represent my table. So,
>>I have writen a template that I use in my SP. My template has 38000
>>characters. In some place, in my template, there are speacials words that
>>are going to be replace when the SP is execute. When I execute the SP
>>without parameters, my template is return entirely but with no
>>modification in the template. When I execute the SP with a parameter, only
>>the 8000 first characters are return with the modification. I don't run
>>the SP in Query analyser but in a VB.NET programm that I did. How can I
>>return a big string with parameters?
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message
>> de news: O%23Q81JvhFHA.1044@.tk2msftngp13.phx.gbl...
>> How are you determining the length returned? Query Analyzer has a
>> limit of 8000 bytes per column. And you should avoid using sp_ as a
>> prefix to stored procedures.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
>> news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
>>
>>
>

return all rows

Hello,
Here is my query for my report. Can you make this query fetch all rows if
@.myID parameter is null?
SELECT FName, MName, LName, ID
FROM MyTable
WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
Thanks,
Jim.write a stored procedure and use an if condition
"JIM.H." wrote:
> Hello,
> Here is my query for my report. Can you make this query fetch all rows if
> @.myID parameter is null?
> SELECT FName, MName, LName, ID
> FROM MyTable
> WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> Thanks,
> Jim.
>|||is it possible without stored procedure?
"NI" wrote:
> write a stored procedure and use an if condition
> "JIM.H." wrote:
> > Hello,
> > Here is my query for my report. Can you make this query fetch all rows if
> > @.myID parameter is null?
> >
> > SELECT FName, MName, LName, ID
> > FROM MyTable
> > WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> >
> > Thanks,
> > Jim.
> >|||Hello Jim
Try to set your "is null" clause in the report settings itself
Ruud Boots
Holland
> SELECT FName, MName, LName, ID
> FROM MyTable
> WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
"JIM.H." wrote:
> Hello,
> Here is my query for my report. Can you make this query fetch all rows if
> @.myID parameter is null?
> SELECT FName, MName, LName, ID
> FROM MyTable
> WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> Thanks,
> Jim.
>|||Here is a technique I like for the WHERE clause so that null means "all":
SELECT FName, MName, LName, ID
FROM MyTable
WHERE (ID = @.myID or @.myID is null)
AND myDate BETWEEN @.StartDate AND @.EndDate
I am currently trying to find how to pass a null parameter to a report. If
you know how to pass a null in RS, I would appreciate the feedback.
Thanks.
Randy Howie
--
"Ruud" wrote:
> Hello Jim
> Try to set your "is null" clause in the report settings itself
> Ruud Boots
> Holland
> > SELECT FName, MName, LName, ID
> > FROM MyTable
> > WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> "JIM.H." wrote:
> > Hello,
> > Here is my query for my report. Can you make this query fetch all rows if
> > @.myID parameter is null?
> >
> > SELECT FName, MName, LName, ID
> > FROM MyTable
> > WHERE (ID = @.myID) AND (myDate BETWEEN @.StartDate AND @.EndDate)
> >
> > Thanks,
> > Jim.
> >sql

return all ESNID one time, the most recent

This is more ASP SElect .

I need to return all the rows. Where the ESNnumber only returns the most recent one that is associsted with the Asset.

Basically, I need the info most current ESN number only.
They are 19,00 rows of each ESN number but it returns 40,000. Duplicates.

SELECT TOP (100) PERCENT dbo.AssetType.Description, dbo.AssetCustomAttributeDef.Name AS [Custom Asset], dbo.ESN.EsnNumber AS [ESN #],
dbo.AssetAttribute.AssetDescription AS [Description Detail], dbo.Asset.Barcode, dbo.Asset.SKU,
dbo.InventoryOrigin.WarehouseDescription AS [Inventory (W/H)], dbo.ESN.DateImplemented, dbo.ESNTracking.TraceTime,
dbo.ESNTracking.PreviousTraceTime, dbo.ESNTracking.HasMoved, dbo.ESNTracking.DistanceMiles, dbo.ESNTracking.Direction,
dbo.ESNTracking.Landmark, dbo.ESNTracking.FemaLocation AS Fema, dbo.ESNTracking.ReportTime AS [Report Time],
dbo.ESNTracking.CurrLocStreet AS Address, dbo.ESNTracking.CurrLocCity AS City, dbo.ESNTracking.CurrLocState AS State,
dbo.ESNTracking.CurrLocZip AS Zipcode, dbo.ESNTracking.CurrLocCounty AS County, dbo.ESNTracking.MapUrl AS [Map Link],
dbo.ESNTracking.ReplaceByDate AS [Replace Batt.], dbo.ESNTracking.CurrMileFromStratix AS [From Stratix Now],
dbo.ESNTracking.PrevMileFromStratix AS [From STratix Then]
FROM dbo.AssetType INNER JOIN
dbo.Asset ON dbo.AssetType.AssetTypeId = dbo.Asset.AssetTypeId INNER JOIN
dbo.InventoryOrigin ON dbo.Asset.WarehouseId = dbo.InventoryOrigin.WarehouseId INNER JOIN
dbo.AssetAttribute ON dbo.Asset.AssetAttributeId = dbo.AssetAttribute.AssetAttributeId INNER JOIN
dbo.EsnAsset ON dbo.Asset.AssetId = dbo.EsnAsset.AssetId INNER JOIN
dbo.ESN ON dbo.EsnAsset.EsnId = dbo.ESN.EsnId LEFT OUTER JOIN
dbo.ESNTracking ON dbo.EsnAsset.EsnId = dbo.ESNTracking.EsnId LEFT OUTER JOIN
dbo.AssetVehicle ON dbo.EsnAsset.AssetId = dbo.AssetVehicle.AssetId LEFT OUTER JOIN
dbo.AssetCustomAttribute ON dbo.EsnAsset.AssetId = dbo.AssetCustomAttribute.AssetId LEFT OUTER JOIN
dbo.AssetCustomAttributeDef ON dbo.AssetCustomAttribute.AssetTypeId = dbo.AssetCustomAttributeDef.AssetTypeId

ORDER BY dbo.AssetType.Description

If I have understood this correctly...

1. Drop a MULTICAST into your flow.

2. On one output, use AGGREGATE to work out the max ESN Number per asset.

3. Join that back to the other output using MERGE JOIN, joining on ESN Number and Asset

Does that work?

-Jamie

|||

Could you help me please.

I need help.

writiing the query with thos parameters

Return a value from EXEC

Hi everybody

How to return a value like that
I dont want to use Stored proc.
Thanks

DECLARE @.cpt as integer
DECLARE @.pTable as varchar(40)

Select @.pTable = 'Salaires'
SELECT @.cpt = EXEC('Select Count(*) FROM ' + @.pTable)

Print @.cptdeclare @.i int
declare @.sql nvarchar(1000)
select @.sql = 'Select @.i = Count(*) FROM ' + @.pTable
exec sp_executesql @.sql, N'@.i int out', @.i out
select @.i

see
http://www.nigelrivett.net/sp_executeSQL.html

Return a type

Hello All!
I would like to thank everyone for all the help, but.. (there is always a
but) i have another question.
I would like in my select a column displaying if the current line is a
Company or a Person, something like that:
SELECT *, "(Company or Person) As Type" FROM Client
LEFT JOIN Person ON Client.ID = Person.ID
LEFT JOIN Company ON Client.ID = Company.ID
I anyone could help me, i raelly would appreciate it!
thanks,
Bruno N
CREATE TABLE [Person] (
[Id] [int] NOT NULL ,
[RG] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Person_Customer] FOREIGN KEY
(
[Id]
) REFERENCES [Customer] (
[Id]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
CREATE TABLE [Customer] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [Company] (
[Id] [int] NOT NULL ,
[CNPJ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Company_Customer] FOREIGN KEY
(
[Id]
) REFERENCES [Customer] (
[Id]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]SELECT *,
CASE
WHEN Client.ID = Person.ID THEN 'Person'
WHEN Company.ID = Person.ID THEN 'Company'
ELSE ''
END
FROM
...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Bruno N" <nylren@.hotmail.com> wrote in message
news:u31HkjMJFHA.3136@.TK2MSFTNGP15.phx.gbl...
> Hello All!
> I would like to thank everyone for all the help, but.. (there is always a
> but) i have another question.
> I would like in my select a column displaying if the current line is a
> Company or a Person, something like that:
>
> SELECT *, "(Company or Person) As Type" FROM Client
> LEFT JOIN Person ON Client.ID = Person.ID
> LEFT JOIN Company ON Client.ID = Company.ID
>
> I anyone could help me, i raelly would appreciate it!
> thanks,
> Bruno N
>
> CREATE TABLE [Person] (
> [Id] [int] NOT NULL ,
> [RG] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Person_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>
> CREATE TABLE [Customer] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
> CREATE TABLE [Company] (
> [Id] [int] NOT NULL ,
> [CNPJ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Company_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>|||SELECT *,
Case When Person.ID Is Not Null then 'Person'
When Company.ID Is Not Null then 'Company'
When Customer.ID Is Not Null then 'Customer' ENd As Type
FROM Client
LEFT JOIN Person ON Client.ID = Person.ID
LEFT JOIN Company ON Client.ID = Company.ID
"Bruno N" wrote:

> Hello All!
> I would like to thank everyone for all the help, but.. (there is always a
> but) i have another question.
> I would like in my select a column displaying if the current line is a
> Company or a Person, something like that:
>
> SELECT *, "(Company or Person) As Type" FROM Client
> LEFT JOIN Person ON Client.ID = Person.ID
> LEFT JOIN Company ON Client.ID = Company.ID
>
> I anyone could help me, i raelly would appreciate it!
> thanks,
> Bruno N
>
> CREATE TABLE [Person] (
> [Id] [int] NOT NULL ,
> [RG] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Person_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>
> CREATE TABLE [Customer] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
> CREATE TABLE [Company] (
> [Id] [int] NOT NULL ,
> [CNPJ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Company_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>
>|||SELECT ...,
CASE
WHEN Person.ID IS NOT NULL THEN 'Person'
WHEN Company.ID IS NOT NULL THEN 'Company'
END, ...
You seem to be missing the alternate key on Customer Name. IDENTITY
should never be the only key of a table. Also, your constraints don't
prevent the same entity being entered as both Cutsomer and Company.
David Portas
SQL Server MVP
--

Return a resultset to XML?

Hello I am trying to pass a recordset to XML
Database: pubs
select * from titles for xml auto
but it returns me this
<titles title_id="BU1032" title="The Busy Executive's Database Guide"
type="business " pub_id="1389" price="20.0000" advance="5000.0000"
royalty="10" ytd_sales="4095" notes="An overview of available database
systems with emphasis on common business
royalty="16" ytd_sales="8780" notes="A survey of software for the naive
user, focusing on the 'friendliness' of each."
pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888" title="Secrets of
Silicon Valley" type="popular_comp" pub_id="1389" pr
"psychology " pub_id="0736" price="8.0000" advance="4000.0000" royalty="10"
ytd_sales="3336" notes="Protecting yourself and your loved ones from undue
emotional stress in the modern world. Use of computer and nutritional aids
emphasized." pubdate="1991-06
I dont want that way
I would like more or less
<title>title1</title>
<type>type</type>
and so on.
ThanksHi
Look at the FOR EXPLICITY clause in books online and you may be able to do
what you require.
John
"Luis Esteban Valencia" wrote:
> Hello I am trying to pass a recordset to XML
> Database: pubs
> select * from titles for xml auto
> but it returns me this
> <titles title_id="BU1032" title="The Busy Executive's Database Guide"
> type="business " pub_id="1389" price="20.0000" advance="5000.0000"
> royalty="10" ytd_sales="4095" notes="An overview of available database
> systems with emphasis on common business
> royalty="16" ytd_sales="8780" notes="A survey of software for the naive
> user, focusing on the 'friendliness' of each."
> pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888" title="Secrets of
> Silicon Valley" type="popular_comp" pub_id="1389" pr
> "psychology " pub_id="0736" price="8.0000" advance="4000.0000" royalty="10"
> ytd_sales="3336" notes="Protecting yourself and your loved ones from undue
> emotional stress in the modern world. Use of computer and nutritional aids
> emphasized." pubdate="1991-06
>
> I dont want that way
> I would like more or less
> <title>title1</title>
> <type>type</type>
> and so on.
> Thanks
>
>|||"Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
> Hello I am trying to pass a recordset to XML
> Database: pubs
> select * from titles for xml auto
> but it returns me this
> <titles title_id="BU1032" title="The Busy Executive's Database
> Guide" type="business " pub_id="1389" price="20.0000"
> advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
> of available database systems with emphasis on common business
> royalty="16" ytd_sales="8780" notes="A survey of software for the
> naive user, focusing on the 'friendliness' of each."
> pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
> title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
> "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
> royalty="10" ytd_sales="3336" notes="Protecting yourself and your
> loved ones from undue emotional stress in the modern world. Use of
> computer and nutritional aids emphasized." pubdate="1991-06
>
> I dont want that way
> I would like more or less
> <title>title1</title>
> <type>type</type>
> and so on.
> Thanks
>
>
select * from titles for xml auto, elements
--
Regards
JTC ^..^|||It doesnt return good. it seems to cut the strings
look at this
<titles><title_id>BU1032</title_id><title>The Busy Executive's Database
Guide</title><type>business
</type><pub_id>1389</pub_id><price>20.0000</price><advance>5000.0000</advanc
e><royalty>10</royalty><ytd_sales>4095</ytd_sales><notes>An overview of
/price><
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> escribió en el mensaje
news:Xns968BBE2E5474DdaveJTC@.213.123.26.234...
> "Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
> news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
> > Hello I am trying to pass a recordset to XML
> > Database: pubs
> >
> > select * from titles for xml auto
> > but it returns me this
> > <titles title_id="BU1032" title="The Busy Executive's Database
> > Guide" type="business " pub_id="1389" price="20.0000"
> > advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
> > of available database systems with emphasis on common business
> > royalty="16" ytd_sales="8780" notes="A survey of software for the
> > naive user, focusing on the 'friendliness' of each."
> > pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
> > title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
> > "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
> > royalty="10" ytd_sales="3336" notes="Protecting yourself and your
> > loved ones from undue emotional stress in the modern world. Use of
> > computer and nutritional aids emphasized." pubdate="1991-06
> >
> >
> > I dont want that way
> > I would like more or less
> > <title>title1</title>
> > <type>type</type>
> >
> > and so on.
> >
> > Thanks
> >
> >
> >
> select * from titles for xml auto, elements
> --
> Regards
> JTC ^..^|||It is only Query Analyzer. You can configure where it cut a column (max is 8000). All xml is one
column in QA.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in message
news:%23eMjeSlgFHA.3436@.tk2msftngp13.phx.gbl...
> It doesnt return good. it seems to cut the strings
> look at this
> <titles><title_id>BU1032</title_id><title>The Busy Executive's Database
> Guide</title><type>business
> </type><pub_id>1389</pub_id><price>20.0000</price><advance>5000.0000</advanc
> e><royalty>10</royalty><ytd_sales>4095</ytd_sales><notes>An overview of
> /price><
>
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> escribió en el mensaje
> news:Xns968BBE2E5474DdaveJTC@.213.123.26.234...
>> "Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
>> news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
>> > Hello I am trying to pass a recordset to XML
>> > Database: pubs
>> >
>> > select * from titles for xml auto
>> > but it returns me this
>> > <titles title_id="BU1032" title="The Busy Executive's Database
>> > Guide" type="business " pub_id="1389" price="20.0000"
>> > advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
>> > of available database systems with emphasis on common business
>> > royalty="16" ytd_sales="8780" notes="A survey of software for the
>> > naive user, focusing on the 'friendliness' of each."
>> > pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
>> > title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
>> > "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
>> > royalty="10" ytd_sales="3336" notes="Protecting yourself and your
>> > loved ones from undue emotional stress in the modern world. Use of
>> > computer and nutritional aids emphasized." pubdate="1991-06
>> >
>> >
>> > I dont want that way
>> > I would like more or less
>> > <title>title1</title>
>> > <type>type</type>
>> >
>> > and so on.
>> >
>> > Thanks
>> >
>> >
>> >
>> select * from titles for xml auto, elements
>> --
>> Regards
>> JTC ^..^
>|||Hi
If you still have problems cutting the tags then put in dummy data values
that contain a carriage return. See http://tinyurl.com/e4rla
John
"Luis Esteban Valencia" wrote:
> It doesnt return good. it seems to cut the strings
> look at this
> <titles><title_id>BU1032</title_id><title>The Busy Executive's Database
> Guide</title><type>business
> </type><pub_id>1389</pub_id><price>20.0000</price><advance>5000.0000</advanc
> e><royalty>10</royalty><ytd_sales>4095</ytd_sales><notes>An overview of
> /price><
>
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> escribió en el mensaje
> news:Xns968BBE2E5474DdaveJTC@.213.123.26.234...
> > "Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
> > news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
> >
> > > Hello I am trying to pass a recordset to XML
> > > Database: pubs
> > >
> > > select * from titles for xml auto
> > > but it returns me this
> > > <titles title_id="BU1032" title="The Busy Executive's Database
> > > Guide" type="business " pub_id="1389" price="20.0000"
> > > advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
> > > of available database systems with emphasis on common business
> > > royalty="16" ytd_sales="8780" notes="A survey of software for the
> > > naive user, focusing on the 'friendliness' of each."
> > > pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
> > > title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
> > > "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
> > > royalty="10" ytd_sales="3336" notes="Protecting yourself and your
> > > loved ones from undue emotional stress in the modern world. Use of
> > > computer and nutritional aids emphasized." pubdate="1991-06
> > >
> > >
> > > I dont want that way
> > > I would like more or less
> > > <title>title1</title>
> > > <type>type</type>
> > >
> > > and so on.
> > >
> > > Thanks
> > >
> > >
> > >
> >
> > select * from titles for xml auto, elements
> >
> > --
> > Regards
> > JTC ^..^
>
>

Return a 0 instead of null

I need to verify if I have partial sales of certain items. I request data
from the server and I am getting NULL.
select sum(matrixamount) matrix
from sales
where invid =@.MerchID
and assettype = 2
If there are NO transactions in Sales for invid = @.MerchID I'd love to
return a 0.
I have tried :
CREATE FUNCTION dbo.GetMatrixTotal
( @.iid int )
RETURNS int AS
BEGIN
declare @.ret int
select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
end ) from sales
where invid = @.iid and assettype = 2
return @.ret
END
Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
But how do I flip it to 0 so there is a return back to a data container in
.NET
TIA
__StephenTry
select ISNULL(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID
and assettype = 2
Mike A.
"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:OvYQa73aFHA.1148@.tk2msftngp13.phx.gbl...
>I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> .NET
> TIA
> __Stephen
>
>|||Try,
select isnull(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID and assettype = 2
AMB
"Stephen Russell" wrote:

> I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> ..NET
> TIA
> __Stephen
>
>

Return a 0 instead of null

I need to verify if I have partial sales of certain items. I request data
from the server and I am getting NULL.
select sum(matrixamount) matrix
from sales
where invid =@.MerchID
and assettype = 2
If there are NO transactions in Sales for invid = @.MerchID I'd love to
return a 0.
I have tried :
CREATE FUNCTION dbo.GetMatrixTotal
( @.iid int )
RETURNS int AS
BEGIN
declare @.ret int
select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
end ) from sales
where invid = @.iid and assettype = 2
return @.ret
END
Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
But how do I flip it to 0 so there is a return back to a data container in
.NET
TIA
__StephenTry
select ISNULL(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID
and assettype = 2
Mike A.
"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:OvYQa73aFHA.1148@.tk2msftngp13.phx.gbl...
>I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> .NET
> TIA
> __Stephen
>
>|||Try,
select isnull(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID and assettype = 2
AMB
"Stephen Russell" wrote:
> I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> ..NET
> TIA
> __Stephen
>
>

Return a 0 instead of null

I need to verify if I have partial sales of certain items. I request data
from the server and I am getting NULL.
select sum(matrixamount) matrix
from sales
where invid =@.MerchID
and assettype = 2
If there are NO transactions in Sales for invid = @.MerchID I'd love to
return a 0.
I have tried :
CREATE FUNCTION dbo.GetMatrixTotal
( @.iid int )
RETURNS int AS
BEGIN
declare @.ret int
select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
end ) from sales
where invid = @.iid and assettype = 2
return @.ret
END
Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
But how do I flip it to 0 so there is a return back to a data container in
..NET
TIA
__Stephen
Try
select ISNULL(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID
and assettype = 2
Mike A.
"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:OvYQa73aFHA.1148@.tk2msftngp13.phx.gbl...
>I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> .NET
> TIA
> __Stephen
>
>
|||Try,
select isnull(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID and assettype = 2
AMB
"Stephen Russell" wrote:

> I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> ..NET
> TIA
> __Stephen
>
>

Return 0 rather than Null

i have query which does the following select x from y where t = "House"

x is an integer. If no record is found, how do i get it to return 0 rather than null?

Use a stored procedure and return the affected rows. This will result in 0 if the Select statement you exampled gets no results.|||This is a query within a stored procedure so is there any other way?|||Why can't you add it to the stored procedure?|||Because i would prefer to do it an alternative way not using a stored procedure.|||The only other way is to assign a value of 0 to an int if the calling app detects that no rows were returned to it by the select statement.|||

Are you using this to populate a datatable or a dataset? This would be the most logical way to handle both cases (returning rows OR looking for a record count).

In your code, do:

' assuming table is a System.Data.DataTable that has been' populated with the results of a SQL query.If table.Rows.Count > 0Then' the table has rows.Else' the table has no rows.End If

|||Thanks for your help. It is returned to a variable within a stored procedure. Could I use an IF statement or anything within the SP?|||

Sorry, I didn't see that your select statement was inside an sp.

You can use an if statement if you want. Just like c++ or c#, if it's only one line after the if, you don't need anything else. If there are multiple lines, use BEGIN and END tags.

Also, I'm not sure how to get the number of rows within the last select statement, but you could perform a count before doing the select.

DECLARE @.Countint-- count the number of rows to be selectedSELECT @.Count =Count(*)from MyTableWhere FName ='Jane'IF @.Count > 0BEGIN-- Perform the SQL statement to get rows.SELECT *from MyTableWhere FName ='Jane'ENDELSEBEGIN-- return 0, no rows were returned.Return 0-- or Return @.CountEND

|||

this would work faster:

IF EXISTS(SELECT *from MyTableWhere FName ='Jane')

BEGIN
-- Perform the SQL statement to get rows.
SELECT *from MyTableWhere FName ='Jane'
END
ELSE
BEGIN
-- return 0 in first cell in one row
SELECT count(*)from MyTableWhere FName ='Jane' -- or just SELECT 0
-- and return if you need
RETURN 0

END

Return # of results that would have been returned without TOP

I want to return the first 100 results of a query that would otherwise retur
n
tens of thousands of results. To do this, I use SELECT TOP 100 ...
I'd really like to be able to show the user how many results would have been
returned had I not limited the query to 100 results. Is there a way to do
this in the same query?
The key is in the same query - obviously I could SELECT COUNT (*) and use
the exact same from and where clauses from the original query. That has two
big divantages. First, any time I need to change the query I need to
change two queries, and second, it's two trips to the database, and it
doesn't seem like there should be a need for that.
Thank you.Greg,
Try one of these approaches:
select top 10
OrderID,
(select count(*) from Northwind..Orders)
from Northwind..Orders
order by CustomerID
go
select top 10
OrderID, CustomerID, C.ct
from Northwind..Orders
cross join (
select count(*) as ct
from Northwind..Orders
) as C
order by CustomerID
Steve Kass
Drew University
"Greg Smalter" <GregSmalter@.discussions.microsoft.com> wrote in message
news:E94CC761-3C77-43F0-BDA2-F646D609A837@.microsoft.com...
>I want to return the first 100 results of a query that would otherwise
>return
> tens of thousands of results. To do this, I use SELECT TOP 100 ...
> I'd really like to be able to show the user how many results would have
> been
> returned had I not limited the query to 100 results. Is there a way to do
> this in the same query?
> The key is in the same query - obviously I could SELECT COUNT (*) and use
> the exact same from and where clauses from the original query. That has
> two
> big divantages. First, any time I need to change the query I need to
> change two queries, and second, it's two trips to the database, and it
> doesn't seem like there should be a need for that.
> Thank you.|||Greg Smalter (GregSmalter@.discussions.microsoft.com) writes:
> I want to return the first 100 results of a query that would otherwise
> return tens of thousands of results. To do this, I use SELECT TOP 100
> ...
> I'd really like to be able to show the user how many results would have
> been returned had I not limited the query to 100 results. Is there a
> way to do this in the same query?
> The key is in the same query - obviously I could SELECT COUNT (*) and
> use the exact same from and where clauses from the original query. That
> has two big divantages. First, any time I need to change the query I
> need to change two queries, and second, it's two trips to the database,
> and it doesn't seem like there should be a need for that.
If you are on SQL 2005, you can do this:
WITH CTE (OrderID, CustomerID) AS
(SELECT OrderID, CustomerID
FROM Orders
WHERE OrderDate BETWEEN '19971201' AND '19971231')
SELECT TOP 10 OrderID, CustomerID,
Cnt = (SELECT COUNT(*) FROM CTE)
FROM CTE
This evades the maintenance problem entirely. Whether it is actually the
best from a performance point of view, requires benchmarking.
The alternative is to run the base SELECT into a temp table, and the
run a SELECT TOP and a SELECT COUNT from that one.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||on 2005, you can write this:
select top 10
OrderID,
row_number() over(order by CustomerId desc)
total_in_the_first_row
from Northwind..Orders
order by CustomerID
go
It's possible the performance will suck however|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:
> on 2005, you can write this:
> select top 10
> OrderID,
> row_number() over(order by CustomerId desc)
> total_in_the_first_row
> from Northwind..Orders
> order by CustomerID
> go
> It's possible the performance will suck however
And the output is somewhat incomprehensible:
OrderID total_in_the_first_row
11011 825
10952 826
10835 827
10702 828
10692 829
10643 830
10926 821
10759 822
10625 823
10308 824
The correct answer of 830 is in the middle...
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
Thank you for the correction. Yet I guess something like this should
work if OrderID is a PK:
select top 10
OrderID,
row_number() over(order by CustomerId desc, OrderID desc)
total_in_the_first_row
from Northwind..Orders
order by CustomerID, OrderID
cannot test it until tomorrow however...

Retuning rows as a single string

Is there any way to do this?
Say I have SELECT MyCol FROM MyTable
MyCol is a nvarchar field, but instead of coming back like this in a table
ItemA
ItemB
ItemC
I want it to come back as a stingle string like this
ItemA, ItemB, ItemC
is this possible in T-SQL? thanks!http://www.aspfaq.com/show.asp?id=2529

> Is there any way to do this?
> Say I have SELECT MyCol FROM MyTable
> MyCol is a nvarchar field, but instead of coming back like this in a table
> ItemA
> ItemB
> ItemC
> I want it to come back as a stingle string like this
> ItemA, ItemB, ItemC|||Try this:
Declare @.Results varchar(2000)
SELECT @.Results =
COALESCE(@.Results + ', ', '') + CAST(MyCol AS
varchar(3))
FROM
MyTable
Select @.Results
HTH
Barry|||Sorry that should read:
Declare @.Results nvarchar(2000)
SELECT @.Results =
COALESCE(@.Results + ', ', '') + MyCol
FROM
MyTable
Select @.Results|||didn't know about coalesce, thanks!
"Barry" <barry.oconnor@.manx.net> wrote in message
news:1145990261.449558.38970@.y43g2000cwc.googlegroups.com...
> Sorry that should read:
> Declare @.Results nvarchar(2000)
>
> SELECT @.Results =
> COALESCE(@.Results + ', ', '') + MyCol
> FROM
> MyTable
>
> Select @.Results
>

Retriving data from database to xml

Hi,
I'm using SQL Server 2000 and I'm having some dificulties to produce the
rigth xml document using the FOR XML clause of SELECT.
The SELECT that I'm using is the following:
select 1 as Tag, NULL as Parent, Header.TaxRegistrationNumber as
[Header!1!TaxRegistrationNumber!element], Header.CompanyName as
[Header!1!CompanyName!element], NULL as
[CompanyAddress!2!BuildingNumber!element], NULL as
[CompanyAddress!2!AddressDetail!element], Header.FiscalYear as
[Header!1!FiscalYear!element]
from Header LEFT JOIN CompanyAddress
UNION
select 2, 1, Header.TaxRegistrationNumber, Header.CompanyName,
CompanyAddress.BuildingNumber, CompanyAddress.AddressDetail, Header.FiscalYear
from from Header LEFT JOIN CompanyAddress
FOR XML EXPLICIT
and the xml result is:
<Header>
<TaxRegistrationNumber>123456787</TaxRegistrationNumber>
<CompanyName>dese</CompanyName>
<FiscalYear>2007</FiscalYear>
<CompanyAddress>
<BuildingNumber></BuildingNumber>
<AddressDetail>250 APT 751</AddressDetail>
</CompanyAddress>
</Header>
The problem is with the <FiscalYear> tag that appears before the
<CompanyAddress> tag and I want that the <FiscalYear> tag appears after the
end of the </CompanyAddress> tag, like this:
<Header>
<TaxRegistrationNumber>123456787</TaxRegistrationNumber>
<CompanyName>dese</CompanyName>
<CompanyAddress>
<BuildingNumber></BuildingNumber>
<AddressDetail>250 APT 751</AddressDetail>
</CompanyAddress>
<FiscalYear>2007</FiscalYear>
</Header>
Is there any way to build this kind of xml result using FOR XML clause?
Thanks.
Hello Ferreira,
You will probably need to make a it level 3 value (e.g., [header!3!FiscalYear!Ement)
where 3 is a child of 1.
kt

Retriving data from database to xml

Hi,
I'm using SQL Server 2000 and I'm having some dificulties to produce the
rigth xml document using the FOR XML clause of SELECT.
The SELECT that I'm using is the following:
select 1 as Tag, NULL as Parent, Header.TaxRegistrationNumber as
[Header!1!TaxRegistrationNumber!element]
, Header.CompanyName as
[Header!1!CompanyName!element], NULL as
[CompanyAddress!2!BuildingNumber!element
], NULL as
[CompanyAddress!2!AddressDetail!element]
, Header.FiscalYear as
[Header!1!FiscalYear!element]
from Header LEFT JOIN CompanyAddress
UNION
select 2, 1, Header.TaxRegistrationNumber, Header.CompanyName,
CompanyAddress.BuildingNumber, CompanyAddress.AddressDetail, Header.FiscalYe
ar
from from Header LEFT JOIN CompanyAddress
FOR XML EXPLICIT
and the xml result is:
<Header>
<TaxRegistrationNumber>123456787</TaxRegistrationNumber>
<CompanyName>dese</CompanyName>
<FiscalYear>2007</FiscalYear>
<CompanyAddress>
<BuildingNumber></BuildingNumber>
<AddressDetail>250 APT 751</AddressDetail>
</CompanyAddress>
</Header>
The problem is with the <FiscalYear> tag that appears before the
<CompanyAddress> tag and I want that the <FiscalYear> tag appears after the
end of the </CompanyAddress> tag, like this:
<Header>
<TaxRegistrationNumber>123456787</TaxRegistrationNumber>
<CompanyName>dese</CompanyName>
<CompanyAddress>
<BuildingNumber></BuildingNumber>
<AddressDetail>250 APT 751</AddressDetail>
</CompanyAddress>
<FiscalYear>2007</FiscalYear>
</Header>
Is there any way to build this kind of xml result using FOR XML clause?
Thanks.Hello Ferreira,
You will probably need to make a it level 3 value (e.g., [header!3!FiscalYea
r!Ement)
where 3 is a child of 1.
kt

Retrieving XML Data using FOR XML AUTO into sql variable

hello guru's

I am using SQL server 2000, Version 8.0(SP4)
I need to put XML string returned from SELECT FOR XML AUTO query to a variable, But i can not able to do it in Transact SQL . can you please help me out ?

Declare @.Message varchar(200)

SELECT col1,col2 from table1 FOR XML AUTO

I need to put result of above query to @.Message variable.

I have tried with following but failed ......

SET @.Message = SELECT col1,col2 from table1 FOR XML AUTO

SET @.Message = sp_executesql N'SELECT col1,col2 from table1 FOR XML AUTO'

can anyone suggest me the solution to this .....

Thanks

Which version of SQL Server are you using -- 2000 or 2005?

|||

Parentheses should help e.g.

Code Snippet

SET @.Message = (SELECT col1, col2 FROM table1 FOR XML AUTO);

|||Thanks Kent for pointing me out,

I am using SQL server 2000 Version 8.0(SP4)

I have updated the post.

Thanks

|||Hello Martin,

Parenthesis does not helped ...It is giving syntax error.

Thanks for reply
|||

Sorry, works with SQL Server 2005 I tested with but then obviously not with SQL Server 2000 which you are using.

|||

Hi,

The code you are trying to execute will not work as FOR XML is not supported to work with assignment in SQL Server 2000. This works only with 2005

Retrieving XML Data using FOR XML AUTO into sql variable

hello guru's

I am using SQL server 2000, Version 8.0(SP4)
I need to put XML string returned from SELECT FOR XML AUTO query to a variable, But i can not able to do it in Transact SQL . can you please help me out ?

Declare @.Message varchar(200)

SELECT col1,col2 from table1 FOR XML AUTO

I need to put result of above query to @.Message variable.

I have tried with following but failed ......

SET @.Message = SELECT col1,col2 from table1 FOR XML AUTO

SET @.Message = sp_executesql N'SELECT col1,col2 from table1 FOR XML AUTO'

can anyone suggest me the solution to this .....

Thanks

Which version of SQL Server are you using -- 2000 or 2005?

|||

Parentheses should help e.g.

Code Snippet

SET @.Message = (SELECT col1, col2 FROM table1 FOR XML AUTO);

|||Thanks Kent for pointing me out,

I am using SQL server 2000 Version 8.0(SP4)

I have updated the post.

Thanks

|||Hello Martin,

Parenthesis does not helped ...It is giving syntax error.

Thanks for reply
|||

Sorry, works with SQL Server 2005 I tested with but then obviously not with SQL Server 2000 which you are using.

|||

Hi,

The code you are trying to execute will not work as FOR XML is not supported to work with assignment in SQL Server 2000. This works only with 2005

Retrieving varchar(max) and varbinary(max) fields with VFP9

I have a test database that contains a varbinary(max) field and a varchar(max) field.

when I do a

Select * from test where id = xx

I get the expected results if my connection string uses

'driver=SQL Server;Server'

but these two fields return no data if I use

'driver=SQL Native Client'

the other fields in the record come back with no problems.

Is there anything special I need to do to retrieve these types of fields?

sorry, typo:

The first driver of course it just

'driver= SQL Server'