Return different row sets using for xml?

I am returning rows using "for xml raw" from a stored procedure - works
great. But I'd like to reduce the number of interactions between my client
and the database - for example: returning two sets of different data
requires two calls to two different stored procedures.
But can one stored procedure return both data sets using "for xml"? If it
could then I can reduce the number of calls. The two data sets have
absolutely no relation to each other and bear no similarities in structure.
E.g.
To get data from the person table and the hotel table currently requires two
stored procedure calls to GetPeople and GetHotels. Can it be combined to
GetPeopleAndHotels?
<data>
<people>
<person name = "fred"/>
<person name = "bob"/>
</people>
<hotels>
<hotel country = "uk"/>
<hotel country = "us"/>
</hotel>
</data>
You can call more than one FOR XML query inside a stored proc.
Also, if you need more semantic markup than what the RAW mode can give you,
you can use the AUTO, EXPLICIT, and in SQLServer 2005 the new PATH mode.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:OAqx8qA6EHA.4040@.TK2MSFTNGP14.phx.gbl...
>I am returning rows using "for xml raw" from a stored procedure - works
> great. But I'd like to reduce the number of interactions between my client
> and the database - for example: returning two sets of different data
> requires two calls to two different stored procedures.
> But can one stored procedure return both data sets using "for xml"? If it
> could then I can reduce the number of calls. The two data sets have
> absolutely no relation to each other and bear no similarities in
> structure.
> E.g.
> To get data from the person table and the hotel table currently requires
> two
> stored procedure calls to GetPeople and GetHotels. Can it be combined to
> GetPeopleAndHotels?
> <data>
> <people>
> <person name = "fred"/>
> <person name = "bob"/>
> </people>
> <hotels>
> <hotel country = "uk"/>
> <hotel country = "us"/>
> </hotel>
> </data>
>
|||Do you know if returning multiple xml result sets would be compatible with
the BizTalk 2004 SQL Adapter?
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:u5TKP0J6EHA.2608@.TK2MSFTNGP10.phx.gbl...
> You can call more than one FOR XML query inside a stored proc.
> Also, if you need more semantic markup than what the RAW mode can give
you,[vbcol=seagreen]
> you can use the AUTO, EXPLICIT, and in SQLServer 2005 the new PATH mode.
> Best regards
> Michael
> "Xerox" <anon@.anon.com> wrote in message
> news:OAqx8qA6EHA.4040@.TK2MSFTNGP14.phx.gbl...
client[vbcol=seagreen]
it
>
>
|||Sorry, but I don't know. I would assume that you expose the result through
the stream interface and add the root element through the stream property,
it should look like a single XML document.
You may want to ask somebody over in the BTS newsgroup...
Best regards
Michael
"Xerox" <info@.thinkscape.com> wrote in message
news:%23kdsg8M6EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Do you know if returning multiple xml result sets would be compatible with
> the BizTalk 2004 SQL Adapter?
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:u5TKP0J6EHA.2608@.TK2MSFTNGP10.phx.gbl...
> you,
> client
> it
>
|||Thanks for the help. I tried it out and it does work fine! The two "for xml"
queries return the data to BizTalk as a single XML document.
The only trouble is, and I wonder if you can help me, is that I would like
both queries to return their xml data under a parent tag: ie. return all
<Customer/> tags under <Customers/>.
This is my procedure:
create procedure GetXML as
select 1 as Tag, Null as Parent, EmployeeID as 'Employee!1!EmployeeID' from
employees for xml explicit;
select 1 as Tag, Null as Parent, CustomerID as 'Customer!1!CustomerID' from
customers for xml explicit;
go
which returns xml data like this:
<Employee EmployeeID="3" />
<Employee EmployeeID="4" />
<Customer CustomerID="SPLIR" />
But I would like it to return data like this:
<Employees>
<Employee EmployeeID="3" />
<Employee EmployeeID="4" />
</Employees>
<Customers>
<Customer CustomerID="SPLIR" />
</Customers>
Do you know if it is possible? Thank you!
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e7xLiVX6EHA.2584@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Sorry, but I don't know. I would assume that you expose the result through
> the stream interface and add the root element through the stream property,
> it should look like a single XML document.
> You may want to ask somebody over in the BTS newsgroup...
> Best regards
> Michael
> "Xerox" <info@.thinkscape.com> wrote in message
> news:%23kdsg8M6EHA.3416@.TK2MSFTNGP09.phx.gbl...
with[vbcol=seagreen]
mode.[vbcol=seagreen]
works[vbcol=seagreen]
If
>
sql

Return different row sets using for xml?

I am returning rows using "for xml raw" from a stored procedure - works
great. But I'd like to reduce the number of interactions between my client
and the database - for example: returning two sets of different data
requires two calls to two different stored procedures.
But can one stored procedure return both data sets using "for xml"? If it
could then I can reduce the number of calls. The two data sets have
absolutely no relation to each other and bear no similarities in structure.
E.g.
To get data from the person table and the hotel table currently requires two
stored procedure calls to GetPeople and GetHotels. Can it be combined to
GetPeopleAndHotels?
<data>
<people>
<person name = "fred"/>
<person name = "bob"/>
</people>
<hotels>
<hotel country = "uk"/>
<hotel country = "us"/>
</hotel>
</data>You can call more than one FOR XML query inside a stored proc.
Also, if you need more semantic markup than what the RAW mode can give you,
you can use the AUTO, EXPLICIT, and in SQLServer 2005 the new PATH mode.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:OAqx8qA6EHA.4040@.TK2MSFTNGP14.phx.gbl...
>I am returning rows using "for xml raw" from a stored procedure - works
> great. But I'd like to reduce the number of interactions between my client
> and the database - for example: returning two sets of different data
> requires two calls to two different stored procedures.
> But can one stored procedure return both data sets using "for xml"? If it
> could then I can reduce the number of calls. The two data sets have
> absolutely no relation to each other and bear no similarities in
> structure.
> E.g.
> To get data from the person table and the hotel table currently requires
> two
> stored procedure calls to GetPeople and GetHotels. Can it be combined to
> GetPeopleAndHotels?
> <data>
> <people>
> <person name = "fred"/>
> <person name = "bob"/>
> </people>
> <hotels>
> <hotel country = "uk"/>
> <hotel country = "us"/>
> </hotel>
> </data>
>|||Do you know if returning multiple xml result sets would be compatible with
the BizTalk 2004 SQL Adapter?
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:u5TKP0J6EHA.2608@.TK2MSFTNGP10.phx.gbl...
> You can call more than one FOR XML query inside a stored proc.
> Also, if you need more semantic markup than what the RAW mode can give
you,
> you can use the AUTO, EXPLICIT, and in SQLServer 2005 the new PATH mode.
> Best regards
> Michael
> "Xerox" <anon@.anon.com> wrote in message
> news:OAqx8qA6EHA.4040@.TK2MSFTNGP14.phx.gbl...
client
it
>
>|||Sorry, but I don't know. I would assume that you expose the result through
the stream interface and add the root element through the stream property,
it should look like a single XML document.
You may want to ask somebody over in the BTS newsgroup...
Best regards
Michael
"Xerox" <info@.thinkscape.com> wrote in message
news:%23kdsg8M6EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Do you know if returning multiple xml result sets would be compatible with
> the BizTalk 2004 SQL Adapter?
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:u5TKP0J6EHA.2608@.TK2MSFTNGP10.phx.gbl...
> you,
> client
> it
>|||Thanks for the help. I tried it out and it does work fine! The two "for xml"
queries return the data to BizTalk as a single XML document.
The only trouble is, and I wonder if you can help me, is that I would like
both queries to return their xml data under a parent tag: ie. return all
<Customer/> tags under <Customers/>.
This is my procedure:
create procedure GetXML as
select 1 as Tag, Null as Parent, EmployeeID as 'Employee!1!EmployeeID' from
employees for xml explicit;
select 1 as Tag, Null as Parent, CustomerID as 'Customer!1!CustomerID' from
customers for xml explicit;
go
which returns xml data like this:
<Employee EmployeeID="3" />
<Employee EmployeeID="4" />
<Customer CustomerID="SPLIR" />
But I would like it to return data like this:
<Employees>
<Employee EmployeeID="3" />
<Employee EmployeeID="4" />
</Employees>
<Customers>
<Customer CustomerID="SPLIR" />
</Customers>
Do you know if it is possible? Thank you!
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e7xLiVX6EHA.2584@.TK2MSFTNGP10.phx.gbl...
> Sorry, but I don't know. I would assume that you expose the result through
> the stream interface and add the root element through the stream property,
> it should look like a single XML document.
> You may want to ask somebody over in the BTS newsgroup...
> Best regards
> Michael
> "Xerox" <info@.thinkscape.com> wrote in message
> news:%23kdsg8M6EHA.3416@.TK2MSFTNGP09.phx.gbl...
with
mode.
works
If
>

Return Dates Not in Table

Just wondering if anyone could help with returning dates not in a table eg
Orders table has a number of Orders with OrderDate stored. I would like to
write a query that displays all dates between say 01 October 2005 and 30
October 2005 that did not have a OrderDate, ie if there were no Orders with
an OrderDate recorded on the 5 October then the query would return 5 October.
Tina
Below is one way you could achieve this using a Calender Reference Table:
CREATE TABLE orders
(
OrderID INT,
OrderDate SMALLDATETIME
)
INSERT orders SELECT 1, '1 Oct 2005'
INSERT orders SELECT 2, '2 Oct 2005'
INSERT orders SELECT 3, '3 Oct 2005'
INSERT orders SELECT 4, '4 Oct 2005'
-- Create Calender Reference Table
CREATE TABLE calender
(
CalenderDate SMALLDATETIME
)
DECLARE @.dt SMALLDATETIME
SET @.dt = '1 Jan 2005'
WHILE @.dt < DATEADD(YEAR, 1, '1 Jan 2005')
BEGIN
INSERT calender SELECT @.dt
SET @.dt = DATEADD(DAY, 1, @.dt)
END
SELECT calenderdate
FROM Calender
WHERE calenderdate BETWEEN '1 Oct 2005' AND '31 Oct 2005'
AND NOT EXISTS (SELECT 1 FROM orders WHERE OrderDate = calenderdate)
- Peter Ward
WARDY IT Solutions
"Tina" wrote:

> Just wondering if anyone could help with returning dates not in a table eg
> Orders table has a number of Orders with OrderDate stored. I would like to
> write a query that displays all dates between say 01 October 2005 and 30
> October 2005 that did not have a OrderDate, ie if there were no Orders with
> an OrderDate recorded on the 5 October then the query would return 5 October.
sql

Return Dates Not in Table

Just wondering if anyone could help with returning dates not in a table eg
Orders table has a number of Orders with OrderDate stored. I would like to
write a query that displays all dates between say 01 October 2005 and 30
October 2005 that did not have a OrderDate, ie if there were no Orders with
an OrderDate recorded on the 5 October then the query would return 5 October
.Tina
Below is one way you could achieve this using a Calender Reference Table:
CREATE TABLE orders
(
OrderID INT,
OrderDate SMALLDATETIME
)
INSERT orders SELECT 1, '1 Oct 2005'
INSERT orders SELECT 2, '2 Oct 2005'
INSERT orders SELECT 3, '3 Oct 2005'
INSERT orders SELECT 4, '4 Oct 2005'
-- Create Calender Reference Table
CREATE TABLE calender
(
CalenderDate SMALLDATETIME
)
DECLARE @.dt SMALLDATETIME
SET @.dt = '1 Jan 2005'
WHILE @.dt < DATEADD(YEAR, 1, '1 Jan 2005')
BEGIN
INSERT calender SELECT @.dt
SET @.dt = DATEADD(DAY, 1, @.dt)
END
SELECT calenderdate
FROM Calender
WHERE calenderdate BETWEEN '1 Oct 2005' AND '31 Oct 2005'
AND NOT EXISTS (SELECT 1 FROM orders WHERE OrderDate = calenderdate)
- Peter Ward
WARDY IT Solutions
"Tina" wrote:
[vbcol=seagreen]
> Just wondering if anyone could help with returning dates not in a table eg
> Orders table has a number of Orders with OrderDate stored. I would like t
o
> write a query that displays all dates between say 01 October 2005 and 30
> October 2005 that did not have a OrderDate, ie if there were no Orders wit
h
> an OrderDate recorded on the 5 October then the query would return 5 October.[/vbc
ol]

Return Dates Not in Table

Just wondering if anyone could help with returning dates not in a table eg
Orders table has a number of Orders with OrderDate stored. I would like to
write a query that displays all dates between say 01 October 2005 and 30
October 2005 that did not have a OrderDate, ie if there were no Orders with
an OrderDate recorded on the 5 October then the query would return 5 October.Tina
Below is one way you could achieve this using a Calender Reference Table:
CREATE TABLE orders
(
OrderID INT,
OrderDate SMALLDATETIME
)
INSERT orders SELECT 1, '1 Oct 2005'
INSERT orders SELECT 2, '2 Oct 2005'
INSERT orders SELECT 3, '3 Oct 2005'
INSERT orders SELECT 4, '4 Oct 2005'
-- Create Calender Reference Table
CREATE TABLE calender
(
CalenderDate SMALLDATETIME
)
DECLARE @.dt SMALLDATETIME
SET @.dt = '1 Jan 2005'
WHILE @.dt < DATEADD(YEAR, 1, '1 Jan 2005')
BEGIN
INSERT calender SELECT @.dt
SET @.dt = DATEADD(DAY, 1, @.dt)
END
SELECT calenderdate
FROM Calender
WHERE calenderdate BETWEEN '1 Oct 2005' AND '31 Oct 2005'
AND NOT EXISTS (SELECT 1 FROM orders WHERE OrderDate = calenderdate)
- Peter Ward
WARDY IT Solutions
"Tina" wrote:
> Just wondering if anyone could help with returning dates not in a table eg
> Orders table has a number of Orders with OrderDate stored. I would like to
> write a query that displays all dates between say 01 October 2005 and 30
> October 2005 that did not have a OrderDate, ie if there were no Orders with
> an OrderDate recorded on the 5 October then the query would return 5 October.

Return BIGINT OUTPUT param to VB!?

Please help me on this one.

I need to return a value to VB.
I've tried returning a numeric value NUMERIC(25,20) via an output parameter but this didn't work. I'm know at a point in wich I created a bigint and multiplied the value so that the decimals are gone. However it only returns NULL?!?!?!?!!?!?
Here's part of my stored proc

CREATE PROCEDURE dbo.uspCalcWeightedAverage
@.StartDate2 varchar(10),
@.EndDate2 varchar(10),
@.InMarket nvarchar(50),
@.InProductType int,
@.InWeekDay int,
@.WeightedAverage bigint OUTPUT
AS
...
...
SELECT @.WeightedAverage = cast(10000000000 * (SUM(HHF.FACTOR) / COUNT(PDF.FLAG)) as bigint)
FROM
TBL_PRODUCTDEFS PDF
INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR]
INNER JOIN tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID
AND [PDF].[HOUR] = [HHF].[HOUR]
AND DATEPART(M,DBI.DATE) = [HHF].[Month]
WHERE
PDF.MARKETID = @.InMarketID
AND PDF.PRODUCTTYPEID = @.InProductTypeID
AND [PDF].[WD-WE] = @.InWeekDay
AND HHF.MARKETID = @.InMarketID
AND PDF.FLAG = 1
GROUP BY FLAG

When I retrieve the output param it returns a NULL value. the properties in VB say that the parameter has the following props:
attribute 64 (Long)
NumericScale 0 (Byte)
Precision 19 (Byte)
Size 0 (ADO_LNGPTR)
Type adBigInt
Value Null

I try to return it with the following code (got the code from a friend)

Public Function RunProcedure(ByVal v_strStoredProcName As String, ByRef r_varParamValues() As Variant) As ADODB.Recordset
Dim objAdoRecordset As ADODB.Recordset
Dim objAdoCommand As ADODB.Command
Dim lngCtr As Long
On Error GoTo RunCommand_Error

' Create cmd object
Set objAdoCommand = New ADODB.Command
Set objAdoCommand.ActiveConnection = m_oAdoConnection
objAdoCommand.ActiveConnection = m_oAdoConnection
objAdoCommand.CommandText = v_strStoredProcName
objAdoCommand.CommandType = adCmdStoredProc
Call objAdoCommand.Parameters.Refresh
'Stop
For lngCtr = 0 To UBound(r_varParamValues)
If objAdoCommand.Parameters(lngCtr + 1).Direction = adParamInput Then
objAdoCommand.Parameters(lngCtr + 1).Value = r_varParamValues(lngCtr)
End If
Next

Set objAdoRecordset = New ADODB.Recordset
objAdoRecordset.CursorLocation = adUseClient

Set objAdoRecordset = objAdoCommand.Execute
'Stop
For lngCtr = 0 To objAdoCommand.Parameters.Count - 1
If objAdoCommand.Parameters(lngCtr).Direction = adParamOutput Or objAdoCommand.Parameters(lngCtr).Direction = adParamInputOutput Then
r_varParamValues(lngCtr - 1) = objAdoCommand.Parameters(lngCtr).Value
End If
Next
Set RunProcedure = objAdoRecordset
RunCommand_Exit:
' Collect your garbage here
Exit Function
RunCommand_Error:
' Collect your garbage here
Call g_oGenErr.Throw("WeatherFcst.CDbsConn", "RunCommand")
End Function

PLEASE HELP.

Regards,

Sander

Grrrr...If I add the following line (before the DROP) to the sp it returns the value!

SET @.WeightedAverage = 9587558855

?! Que Pasa ?!

Return all rows works partially

I encounter a problem where returning all rows from a certain table in
SQL server 2000, the records sometimes can be return sometimes not.
In Enterprise Manager, I browse to a table, then I right click on it,
click "Open table > Return all rows" ... it just keep waiting until it
timeout. It didn't return any result. But when I "Return top" ... with
returning top 71, the records will shown. When I try to return top 72,
it happen again, with no result returned and timeout. I'd try a few
times with different value, it always happen when I try to return rows
until row 72.
I'd try it in Query Analyzer, it's the same.
It return rows when "SELECT TOP 71 * FROM theTableName"
but no response when "SELECT TOP 72 * FROM theTableName"
Anyone encounter this problem before? Is it the index of that table
corrupted? Or values inside that rows cannot be return?
Any idea how to solve this?
Thanks.
Peter CCH
Possibly someone has a lock on the row which will be read as row number 72 by the selected execution
plan. Check using sp_lock, sp_who etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133516672.695691.143760@.g14g2000cwa.googlegr oups.com...
>I encounter a problem where returning all rows from a certain table in
> SQL server 2000, the records sometimes can be return sometimes not.
> In Enterprise Manager, I browse to a table, then I right click on it,
> click "Open table > Return all rows" ... it just keep waiting until it
> timeout. It didn't return any result. But when I "Return top" ... with
> returning top 71, the records will shown. When I try to return top 72,
> it happen again, with no result returned and timeout. I'd try a few
> times with different value, it always happen when I try to return rows
> until row 72.
> I'd try it in Query Analyzer, it's the same.
> It return rows when "SELECT TOP 71 * FROM theTableName"
> but no response when "SELECT TOP 72 * FROM theTableName"
> Anyone encounter this problem before? Is it the index of that table
> corrupted? Or values inside that rows cannot be return?
> Any idea how to solve this?
> Thanks.
>
> Peter CCH
>
|||But I'm the only one user who access to that database while I doing
that.
Peter CCH
|||I'd still check for blocking. It could be an open transaction hanging around or something, you never
know. Other possible reasons:
73 vs 72 rows lead to different execution plans. Check using estimated execution plan.
Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133521175.165593.145330@.g49g2000cwa.googlegr oups.com...
> But I'm the only one user who access to that database while I doing
> that.
>
> Peter CCH
>
|||Tibor Karaszi wrote:
> I'd still check for blocking. It could be an open transaction hanging
> around or something, you never know. Other possible reasons:
> 73 vs 72 rows lead to different execution plans. Check using
> estimated execution plan.
> Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.
Could as well be a too low timeout value, couldn't it?
robert

Return all rows works partially

I encounter a problem where returning all rows from a certain table in
SQL server 2000, the records sometimes can be return sometimes not.
In Enterprise Manager, I browse to a table, then I right click on it,
click "Open table > Return all rows" ... it just keep waiting until it
timeout. It didn't return any result. But when I "Return top" ... with
returning top 71, the records will shown. When I try to return top 72,
it happen again, with no result returned and timeout. I'd try a few
times with different value, it always happen when I try to return rows
until row 72.
I'd try it in Query Analyzer, it's the same.
It return rows when "SELECT TOP 71 * FROM theTableName"
but no response when "SELECT TOP 72 * FROM theTableName"
Anyone encounter this problem before? Is it the index of that table
corrupted? Or values inside that rows cannot be return?
Any idea how to solve this?
Thanks.
Peter CCHPossibly someone has a lock on the row which will be read as row number 72 by the selected execution
plan. Check using sp_lock, sp_who etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133516672.695691.143760@.g14g2000cwa.googlegroups.com...
>I encounter a problem where returning all rows from a certain table in
> SQL server 2000, the records sometimes can be return sometimes not.
> In Enterprise Manager, I browse to a table, then I right click on it,
> click "Open table > Return all rows" ... it just keep waiting until it
> timeout. It didn't return any result. But when I "Return top" ... with
> returning top 71, the records will shown. When I try to return top 72,
> it happen again, with no result returned and timeout. I'd try a few
> times with different value, it always happen when I try to return rows
> until row 72.
> I'd try it in Query Analyzer, it's the same.
> It return rows when "SELECT TOP 71 * FROM theTableName"
> but no response when "SELECT TOP 72 * FROM theTableName"
> Anyone encounter this problem before? Is it the index of that table
> corrupted? Or values inside that rows cannot be return?
> Any idea how to solve this?
> Thanks.
>
> Peter CCH
>|||But I'm the only one user who access to that database while I doing
that.
Peter CCH|||I'd still check for blocking. It could be an open transaction hanging around or something, you never
know. Other possible reasons:
73 vs 72 rows lead to different execution plans. Check using estimated execution plan.
Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133521175.165593.145330@.g49g2000cwa.googlegroups.com...
> But I'm the only one user who access to that database while I doing
> that.
>
> Peter CCH
>|||Tibor Karaszi wrote:
> I'd still check for blocking. It could be an open transaction hanging
> around or something, you never know. Other possible reasons:
> 73 vs 72 rows lead to different execution plans. Check using
> estimated execution plan.
> Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.
Could as well be a too low timeout value, couldn't it?
robertsql

Return all rows works partially

I encounter a problem where returning all rows from a certain table in
SQL server 2000, the records sometimes can be return sometimes not.
In Enterprise Manager, I browse to a table, then I right click on it,
click "Open table > Return all rows" ... it just keep waiting until it
timeout. It didn't return any result. But when I "Return top" ... with
returning top 71, the records will shown. When I try to return top 72,
it happen again, with no result returned and timeout. I'd try a few
times with different value, it always happen when I try to return rows
until row 72.
I'd try it in Query Analyzer, it's the same.
It return rows when "SELECT TOP 71 * FROM theTableName"
but no response when "SELECT TOP 72 * FROM theTableName"
Anyone encounter this problem before? Is it the index of that table
corrupted? Or values inside that rows cannot be return?
Any idea how to solve this?
Thanks.
Peter CCHPossibly someone has a lock on the row which will be read as row number 72 b
y the selected execution
plan. Check using sp_lock, sp_who etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133516672.695691.143760@.g14g2000cwa.googlegroups.com...
>I encounter a problem where returning all rows from a certain table in
> SQL server 2000, the records sometimes can be return sometimes not.
> In Enterprise Manager, I browse to a table, then I right click on it,
> click "Open table > Return all rows" ... it just keep waiting until it
> timeout. It didn't return any result. But when I "Return top" ... with
> returning top 71, the records will shown. When I try to return top 72,
> it happen again, with no result returned and timeout. I'd try a few
> times with different value, it always happen when I try to return rows
> until row 72.
> I'd try it in Query Analyzer, it's the same.
> It return rows when "SELECT TOP 71 * FROM theTableName"
> but no response when "SELECT TOP 72 * FROM theTableName"
> Anyone encounter this problem before? Is it the index of that table
> corrupted? Or values inside that rows cannot be return?
> Any idea how to solve this?
> Thanks.
>
> Peter CCH
>|||But I'm the only one user who access to that database while I doing
that.
Peter CCH|||I'd still check for blocking. It could be an open transaction hanging around
or something, you never
know. Other possible reasons:
73 vs 72 rows lead to different execution plans. Check using estimated execu
tion plan.
Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133521175.165593.145330@.g49g2000cwa.googlegroups.com...
> But I'm the only one user who access to that database while I doing
> that.
>
> Peter CCH
>|||Tibor Karaszi wrote:
> I'd still check for blocking. It could be an open transaction hanging
> around or something, you never know. Other possible reasons:
> 73 vs 72 rows lead to different execution plans. Check using
> estimated execution plan.
> Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.
Could as well be a too low timeout value, couldn't it?
robert

RETURING XML AS A PARAMETER .NET

Dear programmers,
I'm having real issues with returning data from an output parameter
from a SQL 2005 stored procedure. I've checked the result by applying
the input directly to the stored procedure and all seems to look fine.
Its simply that when the data returns it looks to be in the wrong
format. I'm getting forward slashes and bits and bobs that do not
deserialize because they don't correspond to the original document
stored in the database.
The sp simply takes in an xml document and returns a response xml in
the output parameter. This is returned the xml such as the
following ...
<result provider_reference=\"iTunes\"><items> ...
when this should be ...
"<result provider_reference="iTunes"><items> ...
this might just be because the result encoding is changed when i look
at the result in the immediate window ... but I'm sure I'm not reading
the result out correctly ... I'd really appreciate it if anyone knows
how to correctly read xml from an output parameters. Any advice most
warmly welcomed.
The code fails when I get to the Deserialize section and it returns
with error ""There is an error in XML document (1, 2)."
My code is as below ...
internal ResponseResult PostRequestToDatabase(ServiceRequest
Request, UserCredentials User)
{
////////////////////////////////////////////////////////////////////////////
//////////////////
/// Description: This method posts a request to the
database for verification
/// Created Date: 28th November 2007
/// Created By: T.O'Donnell
////////////////////////////////////////////////////////////////////////////
///////////////////
// create a local string variable to pass in the xml
string xml_posted;
string xml_returned;
// create a new connection to the database
this.DataAccess_Connection = new SqlConnection();
this.DataAccess_Connection.ConnectionString =
GetConnectionString(User);
try
{
// create a new instance of the serialiser and
textwritter objects
XmlSerializer ser = new
XmlSerializer(typeof(ServiceRequest));
StringWriter swriter = new StringWriter();
// write the xml formated classes to the xml
variable
ser.Serialize(swriter, Request);
xml_posted = swriter.ToString();
// create a command object for the storedprocedure
this.DataAccess_Command = new SqlCommand();
this.DataAccess_Command.CommandType =
System.Data.CommandType.StoredProcedure;
this.DataAccess_Command.Connection =
this.DataAccess_Connection;
this.DataAccess_Command.CommandText =
"symin_get_response_result";
// set return parameter
this.DataAccess_Command.Parameters.Add(new
SqlParameter("@.xml_request", SqlDbType.Xml)).Value =
xml_posted;
this.DataAccess_Command.Parameters.Add(new
SqlParameter("@.xml_result", SqlDbType.Xml, 1)).Direction =
ParameterDirection.Output;
this.DataAccess_Command.Parameters.Add(new
SqlParameter("@.return_value", SqlDbType.Int)).Direction =
ParameterDirection.ReturnValue;
// check to see if the connection is still open
if (this.DataAccess_Connection.State ==
ConnectionState.Closed)
{
// open the connection and submit the query
this.DataAccess_Connection.Open();
}
// execute the query
this.DataAccess_Command.ExecuteNonQuery();
// get the return value to check for errors
if
((Int32)this.DataAccess_Command.Parameters["@.return_value"].Value ==
0)
{
// check that the returning xml is not null
if
(this.DataAccess_Command.Parameters["@.xml_result"].Value !=
DBNull.Value)
{
// get the returning xml object
xml_returned =
(string)this.DataAccess_Command.Parameters["@.xml_result"].SqlValue;
// deserialize the results to a class
structure
XmlSerializer Serializer = new
XmlSerializer(typeof(ResponseResult));
StringReader xmlstream = new
StringReader(xml_returned);
XmlTextReader xmlreader = new
XmlTextReader(xmlstream);
// ***** THIS IS WHERE THE ERROR IS
RETURNED
return
(ResponseResult)Serializer.Deserialize(xmlreader);
}
}
// return nothing to the calling party
return null;
}
catch (Exception ee)
{
Console.WriteLine(ee.Message);
return null;
}
finally
{
// check to see if the connection object has been
initialised
if (this.DataAccess_Connection != null)
{
// check to see if the connection is still
open
if (this.DataAccess_Connection.State ==
ConnectionState.Open)
{
// close the Connection
this.DataAccess_Connection.Close();
}
}
}
}The \" is an escaped quotation mark in C#. VS is probably adding that to
the display when you view it, assuming it's enclosing the entire string in
double quotes. I'd think your problem is probably unrelated to this. Try
printing the results directly to Console and see if the \ still appears
before the ".
"Caspian" <timothy.odonnell@.hotmail.com> wrote in message
news:56c69ddc-ef93-4aad-856c-22cf7392e3d4@.u10g2000prn.googlegroups.com...
> Dear programmers,
> I'm having real issues with returning data from an output parameter
> from a SQL 2005 stored procedure. I've checked the result by applying
> the input directly to the stored procedure and all seems to look fine.
> Its simply that when the data returns it looks to be in the wrong
> format. I'm getting forward slashes and bits and bobs that do not
> deserialize because they don't correspond to the original document
> stored in the database.
> The sp simply takes in an xml document and returns a response xml in
> the output parameter. This is returned the xml such as the
> following ...
> <result provider_reference=\"iTunes\"><items> ...
> when this should be ...
> "<result provider_reference="iTunes"><items> ...
> this might just be because the result encoding is changed when i look
> at the result in the immediate window ... but I'm sure I'm not reading
> the result out correctly ... I'd really appreciate it if anyone knows
> how to correctly read xml from an output parameters. Any advice most
> warmly welcomed.
> The code fails when I get to the Deserialize section and it returns
> with error ""There is an error in XML document (1, 2)."
> My code is as below ...
> internal ResponseResult PostRequestToDatabase(ServiceRequest
> Request, UserCredentials User)
> {
> //////////////////////////////////////////////////////////////////////////
////////////////////
> /// Description: This method posts a request to the
> database for verification
> /// Created Date: 28th November 2007
> /// Created By: T.O'Donnell
> //////////////////////////////////////////////////////////////////////////
/////////////////////
> // create a local string variable to pass in the xml
> string xml_posted;
> string xml_returned;
> // create a new connection to the database
> this.DataAccess_Connection = new SqlConnection();
> this.DataAccess_Connection.ConnectionString =
> GetConnectionString(User);
> try
> {
> // create a new instance of the serialiser and
> textwritter objects
> XmlSerializer ser = new
> XmlSerializer(typeof(ServiceRequest));
> StringWriter swriter = new StringWriter();
> // write the xml formated classes to the xml
> variable
> ser.Serialize(swriter, Request);
> xml_posted = swriter.ToString();
> // create a command object for the storedprocedure
> this.DataAccess_Command = new SqlCommand();
> this.DataAccess_Command.CommandType =
> System.Data.CommandType.StoredProcedure;
> this.DataAccess_Command.Connection =
> this.DataAccess_Connection;
> this.DataAccess_Command.CommandText =
> "symin_get_response_result";
> // set return parameter
> this.DataAccess_Command.Parameters.Add(new
> SqlParameter("@.xml_request", SqlDbType.Xml)).Value =
> xml_posted;
> this.DataAccess_Command.Parameters.Add(new
> SqlParameter("@.xml_result", SqlDbType.Xml, 1)).Direction =
> ParameterDirection.Output;
> this.DataAccess_Command.Parameters.Add(new
> SqlParameter("@.return_value", SqlDbType.Int)).Direction =
> ParameterDirection.ReturnValue;
> // check to see if the connection is still open
> if (this.DataAccess_Connection.State ==
> ConnectionState.Closed)
> {
> // open the connection and submit the query
> this.DataAccess_Connection.Open();
> }
> // execute the query
> this.DataAccess_Command.ExecuteNonQuery();
> // get the return value to check for errors
> if
> ((Int32)this.DataAccess_Command.Parameters["@.return_value"].Value ==
> 0)
> {
> // check that the returning xml is not null
> if
> (this.DataAccess_Command.Parameters["@.xml_result"].Value !=
> DBNull.Value)
> {
> // get the returning xml object
> xml_returned =
> (string)this.DataAccess_Command.Parameters["@.xml_result"].SqlValue;
>
> // deserialize the results to a class
> structure
> XmlSerializer Serializer = new
> XmlSerializer(typeof(ResponseResult));
> StringReader xmlstream = new
> StringReader(xml_returned);
> XmlTextReader xmlreader = new
> XmlTextReader(xmlstream);
> // ***** THIS IS WHERE THE ERROR IS
> RETURNED
> return
> (ResponseResult)Serializer.Deserialize(xmlreader);
> }
> }
> // return nothing to the calling party
> return null;
> }
> catch (Exception ee)
> {
> Console.WriteLine(ee.Message);
> return null;
> }
> finally
> {
> // check to see if the connection object has been
> initialised
> if (this.DataAccess_Connection != null)
> {
> // check to see if the connection is still
> open
> if (this.DataAccess_Connection.State ==
> ConnectionState.Open)
> {
> // close the Connection
> this.DataAccess_Connection.Close();
> }
> }
> }
> }
>|||Thanks for your help Mike ... I'd spent so long looking into this
problem that I couldn't see the wood through the trees. As it happens
for future readers, it would appear that the returning XML read just
fine by looking at it from the console. Additionally, this following
line is incorrect ...
xml_returned =3D
(string)this.DataAccess_Command.Parameters["@.xml_result"].SqlValue;
this should read ...
xml_returned =3D
(string)this.DataAccess_Command.Parameters["@.xml_result"].Value;
The problem was quite simply because I'd removed the
[XmlType("result")] above the class that I was trying to deserialize
against (which incidentally used a different name).
Hope others benefit from this code.
Kind regards,
Tim
On Jan 14, 11:54=A0pm, "Mike C#" <x...@.xyz.com> wrote:
> The \" is an escaped quotation mark in C#. =A0VS is probably adding that t=[/color
]
o
> the display when you view it, assuming it's enclosing the entire string in=[/color
]
> double quotes. =A0I'd think your problem is probably unrelated to this. =
=A0Try
> printing the results directly to Console and see if the \ still appears
> before the ".
> "Caspian" <timothy.odonn...@.hotmail.com> wrote in message
> news:56c69ddc-ef93-4aad-856c-22cf7392e3d4@.u10g2000prn.googlegroups.com...
>sql

RETURING XML AS A PARAMETER .NET

Dear programmers,
I'm having real issues with returning data from an output parameter
from a SQL 2005 stored procedure. I've checked the result by applying
the input directly to the stored procedure and all seems to look fine.
Its simply that when the data returns it looks to be in the wrong
format. I'm getting forward slashes and bits and bobs that do not
deserialize because they don't correspond to the original document
stored in the database.
The sp simply takes in an xml document and returns a response xml in
the output parameter. This is returned the xml such as the
following ...
<result provider_reference=\"iTunes\"><items> ...
when this should be ...
"<result provider_reference="iTunes"><items> ...
this might just be because the result encoding is changed when i look
at the result in the immediate window ... but I'm sure I'm not reading
the result out correctly ... I'd really appreciate it if anyone knows
how to correctly read xml from an output parameters. Any advice most
warmly welcomed.
The code fails when I get to the Deserialize section and it returns
with error ""There is an error in XML document (1, 2)."
My code is as below ...
internal ResponseResult PostRequestToDatabase(ServiceRequest
Request, UserCredentials User)
{
//////////////////////////////////////////////////////////////////////////////////////////////
/// Description: This method posts a request to the
database for verification
/// Created Date: 28th November 2007
/// Created By: T.O'Donnell
///////////////////////////////////////////////////////////////////////////////////////////////
// create a local string variable to pass in the xml
string xml_posted;
string xml_returned;
// create a new connection to the database
this.DataAccess_Connection = new SqlConnection();
this.DataAccess_Connection.ConnectionString =
GetConnectionString(User);
try
{
// create a new instance of the serialiser and
textwritter objects
XmlSerializer ser = new
XmlSerializer(typeof(ServiceRequest));
StringWriter swriter = new StringWriter();
// write the xml formated classes to the xml
variable
ser.Serialize(swriter, Request);
xml_posted = swriter.ToString();
// create a command object for the storedprocedure
this.DataAccess_Command = new SqlCommand();
this.DataAccess_Command.CommandType =
System.Data.CommandType.StoredProcedure;
this.DataAccess_Command.Connection =
this.DataAccess_Connection;
this.DataAccess_Command.CommandText =
"sysadmin_get_response_result";
// set return parameter
this.DataAccess_Command.Parameters.Add(new
SqlParameter("@.xml_request", SqlDbType.Xml)).Value =
xml_posted;
this.DataAccess_Command.Parameters.Add(new
SqlParameter("@.xml_result", SqlDbType.Xml, 1)).Direction =
ParameterDirection.Output;
this.DataAccess_Command.Parameters.Add(new
SqlParameter("@.return_value", SqlDbType.Int)).Direction =
ParameterDirection.ReturnValue;
// check to see if the connection is still open
if (this.DataAccess_Connection.State ==
ConnectionState.Closed)
{
// open the connection and submit the query
this.DataAccess_Connection.Open();
}
// execute the query
this.DataAccess_Command.ExecuteNonQuery();
// get the return value to check for errors
if
((Int32)this.DataAccess_Command.Parameters["@.retur n_value"].Value ==
0)
{
// check that the returning xml is not null
if
(this.DataAccess_Command.Parameters["@.xml_result"].Value !=
DBNull.Value)
{
// get the returning xml object
xml_returned =
(string)this.DataAccess_Command.Parameters["@.xml_r esult"].SqlValue;
// deserialize the results to a class
structure
XmlSerializer Serializer = new
XmlSerializer(typeof(ResponseResult));
StringReader xmlstream = new
StringReader(xml_returned);
XmlTextReader xmlreader = new
XmlTextReader(xmlstream);
// ***** THIS IS WHERE THE ERROR IS
RETURNED
return
(ResponseResult)Serializer.Deserialize(xmlreader);
}
}
// return nothing to the calling party
return null;
}
catch (Exception ee)
{
Console.WriteLine(ee.Message);
return null;
}
finally
{
// check to see if the connection object has been
initialised
if (this.DataAccess_Connection != null)
{
// check to see if the connection is still
open
if (this.DataAccess_Connection.State ==
ConnectionState.Open)
{
// close the Connection
this.DataAccess_Connection.Close();
}
}
}
}
The \" is an escaped quotation mark in C#. VS is probably adding that to
the display when you view it, assuming it's enclosing the entire string in
double quotes. I'd think your problem is probably unrelated to this. Try
printing the results directly to Console and see if the \ still appears
before the ".
"Caspian" <timothy.odonnell@.hotmail.com> wrote in message
news:56c69ddc-ef93-4aad-856c-22cf7392e3d4@.u10g2000prn.googlegroups.com...
> Dear programmers,
> I'm having real issues with returning data from an output parameter
> from a SQL 2005 stored procedure. I've checked the result by applying
> the input directly to the stored procedure and all seems to look fine.
> Its simply that when the data returns it looks to be in the wrong
> format. I'm getting forward slashes and bits and bobs that do not
> deserialize because they don't correspond to the original document
> stored in the database.
> The sp simply takes in an xml document and returns a response xml in
> the output parameter. This is returned the xml such as the
> following ...
> <result provider_reference=\"iTunes\"><items> ...
> when this should be ...
> "<result provider_reference="iTunes"><items> ...
> this might just be because the result encoding is changed when i look
> at the result in the immediate window ... but I'm sure I'm not reading
> the result out correctly ... I'd really appreciate it if anyone knows
> how to correctly read xml from an output parameters. Any advice most
> warmly welcomed.
> The code fails when I get to the Deserialize section and it returns
> with error ""There is an error in XML document (1, 2)."
> My code is as below ...
> internal ResponseResult PostRequestToDatabase(ServiceRequest
> Request, UserCredentials User)
> {
> //////////////////////////////////////////////////////////////////////////////////////////////
> /// Description: This method posts a request to the
> database for verification
> /// Created Date: 28th November 2007
> /// Created By: T.O'Donnell
> ///////////////////////////////////////////////////////////////////////////////////////////////
> // create a local string variable to pass in the xml
> string xml_posted;
> string xml_returned;
> // create a new connection to the database
> this.DataAccess_Connection = new SqlConnection();
> this.DataAccess_Connection.ConnectionString =
> GetConnectionString(User);
> try
> {
> // create a new instance of the serialiser and
> textwritter objects
> XmlSerializer ser = new
> XmlSerializer(typeof(ServiceRequest));
> StringWriter swriter = new StringWriter();
> // write the xml formated classes to the xml
> variable
> ser.Serialize(swriter, Request);
> xml_posted = swriter.ToString();
> // create a command object for the storedprocedure
> this.DataAccess_Command = new SqlCommand();
> this.DataAccess_Command.CommandType =
> System.Data.CommandType.StoredProcedure;
> this.DataAccess_Command.Connection =
> this.DataAccess_Connection;
> this.DataAccess_Command.CommandText =
> "sysadmin_get_response_result";
> // set return parameter
> this.DataAccess_Command.Parameters.Add(new
> SqlParameter("@.xml_request", SqlDbType.Xml)).Value =
> xml_posted;
> this.DataAccess_Command.Parameters.Add(new
> SqlParameter("@.xml_result", SqlDbType.Xml, 1)).Direction =
> ParameterDirection.Output;
> this.DataAccess_Command.Parameters.Add(new
> SqlParameter("@.return_value", SqlDbType.Int)).Direction =
> ParameterDirection.ReturnValue;
> // check to see if the connection is still open
> if (this.DataAccess_Connection.State ==
> ConnectionState.Closed)
> {
> // open the connection and submit the query
> this.DataAccess_Connection.Open();
> }
> // execute the query
> this.DataAccess_Command.ExecuteNonQuery();
> // get the return value to check for errors
> if
> ((Int32)this.DataAccess_Command.Parameters["@.retur n_value"].Value ==
> 0)
> {
> // check that the returning xml is not null
> if
> (this.DataAccess_Command.Parameters["@.xml_result"].Value !=
> DBNull.Value)
> {
> // get the returning xml object
> xml_returned =
> (string)this.DataAccess_Command.Parameters["@.xml_r esult"].SqlValue;
>
> // deserialize the results to a class
> structure
> XmlSerializer Serializer = new
> XmlSerializer(typeof(ResponseResult));
> StringReader xmlstream = new
> StringReader(xml_returned);
> XmlTextReader xmlreader = new
> XmlTextReader(xmlstream);
> // ***** THIS IS WHERE THE ERROR IS
> RETURNED
> return
> (ResponseResult)Serializer.Deserialize(xmlreader);
> }
> }
> // return nothing to the calling party
> return null;
> }
> catch (Exception ee)
> {
> Console.WriteLine(ee.Message);
> return null;
> }
> finally
> {
> // check to see if the connection object has been
> initialised
> if (this.DataAccess_Connection != null)
> {
> // check to see if the connection is still
> open
> if (this.DataAccess_Connection.State ==
> ConnectionState.Open)
> {
> // close the Connection
> this.DataAccess_Connection.Close();
> }
> }
> }
> }
>
|||Thanks for your help Mike ... I'd spent so long looking into this
problem that I couldn't see the wood through the trees. As it happens
for future readers, it would appear that the returning XML read just
fine by looking at it from the console. Additionally, this following
line is incorrect ...
xml_returned =
(string)this.DataAccess_Command.Parameters["@.xml_r esult"].SqlValue;
this should read ...
xml_returned =
(string)this.DataAccess_Command.Parameters["@.xml_r esult"].Value;
The problem was quite simply because I'd removed the
[XmlType("result")] above the class that I was trying to deserialize
against (which incidentally used a different name).
Hope others benefit from this code.
Kind regards,
Tim
On Jan 14, 11:54Xpm, "Mike C#" <x...@.xyz.com> wrote:
> The \" is an escaped quotation mark in C#. XVS is probably adding that to
> the display when you view it, assuming it's enclosing the entire string in
> double quotes. XI'd think your problem is probably unrelated to this. XTry
> printing the results directly to Console and see if the \ still appears
> before the ".
> "Caspian" <timothy.odonn...@.hotmail.com> wrote in message
> news:56c69ddc-ef93-4aad-856c-22cf7392e3d4@.u10g2000prn.googlegroups.com...
>

Retrning XML raw from Stored procedure - (by using dual table)

Hi ,

I have a problem returning the @.CustomerNo from dual for one of my stored procedures. (by the way I do a some manipulations and assign a value to @.CustomerNo everytime this stored procedure runs)

SELECT @.CustomerNo AS CustomerNumber from dual FOR XML RAW

What I figured out is that the reason no values is returned in my XML file is because the dual table does not have any rows. But ,the dual table always have a dummy row by default. Doesnt it.

Alsoif possible please let me know any alternative way to returnthe following XML :

<row>

<CustomerNumber >

value of @.CustomerNo

<CustomerNumber >

</row>

Thanks in advance.

The dual table is an Oracle (shudders) feature.

If you are using SQL Server 2005, you can do the following:

Code Snippet

SELECT @.CustomerNo AS CustomerNumber

FOR XML PATH('row')

--

Peter DeBetta

MVP - SQL Server

http://sqlblog.com

|||Super.. Thanks for the soln.sql

Retrning XML raw from Stored procedure - (by using dual table)

Hi ,

I have a problem returning the @.CustomerNo from dual for one of my stored procedures. (by the way I do a some manipulations and assign a value to @.CustomerNo everytime this stored procedure runs)

SELECT @.CustomerNo AS CustomerNumber from dual FOR XML RAW

What I figured out is that the reason no values is returned in my XML file is because the dual table does not have any rows. But ,the dual table always have a dummy row by default. Doesnt it.

Alsoif possible please let me know any alternative way to returnthe following XML :

<row>

<CustomerNumber >

value of @.CustomerNo

<CustomerNumber >

</row>

Thanks in advance.

The dual table is an Oracle (shudders) feature.

If you are using SQL Server 2005, you can do the following:

Code Snippet

SELECT @.CustomerNo AS CustomerNumber

FOR XML PATH('row')

--

Peter DeBetta

MVP - SQL Server

http://sqlblog.com

|||Super.. Thanks for the soln.