2005 Pivot Question - Not returning data..not sure what i did.

Okay, using examples I found on the internet, I managed to piece this
together. Now the part in the data section, works, and i get the data to
come out. Once I add the pivot to it, it stops producing results.
I am guessing that I have 2 or 3 major problems with this.
1. Where do i send the output params, as far as what equals what.
2. How do i reference the columns in the nested query (defined AS Data) in
the outer query.. (not sure if i even have that part in there right)..but i
t
needs to tilt on the account tag, and sum the quantity.
Dataset example:
Forward Date AcctTag Symbol Strike price
Typecode Quantity
9/1/2005 12:00:00 AM MN501 CL-OTC 0 F -25
9/1/2005 12:00:00 AM SU185 CL-OTC 0 F 85
10/1/2005 12:00:00 AM SU185 CL-OTC 0 F -45
3. Probably something else in there, that i can't figure out..but i figure
theres one unseen problem in there.
Use XXXCAPMAN
Go
sp_dbcmptlevel aaacapman, 90 'forces into SQL2005 mode
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sp_POSSheets
(
@.inUSERID varchar(20),
@.inCommodities varchar(250),
@.outSU185 int OUTPUT,
@.outBW685 int OUTPUT,
@.outMN053 int OUTPUT,
@.outSU598 int OUTPUT,
@.outSU959 int OUTPUT,
@.outSU897 int OUTPUT,
@.outSU896 int OUTPUT,
@.outSU797 int OUTPUT,
@.outSU524 int OUTPUT,
@.outSU511 int OUTPUT,
@.outSU397 int OUTPUT,
@.outSU960 int OUTPUT,
@.outSU895 int OUTPUT,
@.outSU935 int OUTPUT,
@.out9SU809 int OUTPUT,
@.out9SU885 int OUTPUT,
@.outMN051 int OUTPUT,
@.outSymbol varchar(20) OUTPUT,
@.outForwardDate varchar(20) OUTPUT,
@.outStrikePrice int OUTPUT,
@.outTypeCode int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT
[1] AS 'SU185'
,[2] AS 'MN053'
,[3] AS 'SU598'
,[4] AS 'SU959'
,[5] AS 'SU897'
,[6] AS 'SU896'
,[7] AS 'SU797'
,[8] AS 'SU524'
,[9] AS 'SU511'
,[10] AS 'SU397'
,[11] AS 'SU960'
,[12] AS 'SU895'
,[13] AS 'SU935'
,[14] AS '9SU809'
,[15] AS '9SU885'
,[16] AS 'MN051'
,[17] AS 'BW685'
From (
SELECT TOP (100) PERCENT dbo.Trades.ForwardDate, dbo.Accounts.[Account
Tag], dbo.Commodity.Symbol, dbo.Trades.StrikePrice, dbo.Trades.TypeCode,
dbo.Trades.Quantity
FROM dbo.Users INNER JOIN
dbo.Commodity INNER JOIN dbo.Accounts INNER JOIN dbo.Trades ON
dbo.Accounts.ID = dbo.Trades.AccountCode ON dbo.Commodity.ID =
dbo.Trades.CommodityCode ON dbo.Users.ID = dbo.Trades.Trader
WHERE (dbo.Commodity.Symbol LIKE N'CL%' OR
dbo.Commodity.Symbol LIKE N'CL-OTC%') AND (dbo.Accounts.SheetAccount = 1)
AND (dbo.Trades.ActiveFlag = 1) AND (dbo.Users.UserShortName = N'Carl')
GROUP BY dbo.Trades.ForwardDate, dbo.Commodity.Symbol,
dbo.Trades.StrikePrice, dbo.Trades.TypeCode, dbo.Accounts.[Account Tag],
dbo.Trades.Quantity
ORDER BY dbo.Trades.ForwardDate ) AS Data
PIVOT(
Sum(Data.Quantity)
For Data.[Account Tag]
IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[
10],[11],[12],[13],[14],[15],[16],[1
7])
) AS PVT
END
GO
www.krushradio.com - Internet Radio for the rest of usLet me know if I was not clear on what i am trying to accomplish...
I would really like to understand what parts are incorrect...or how the
dynamics work for the new pivot function. The examples I've found are
without the datasamples, so i can't really use them to pick apart the actual
queries.
Thanks
Dan Regalia
www.krushradio.com - Internet Radio for the rest of us
"Daniel Regalia" wrote:

> Okay, using examples I found on the internet, I managed to piece this
> together. Now the part in the data section, works, and i get the data to
> come out. Once I add the pivot to it, it stops producing results.
> I am guessing that I have 2 or 3 major problems with this.
> 1. Where do i send the output params, as far as what equals what.
> 2. How do i reference the columns in the nested query (defined AS Data) i
n
> the outer query.. (not sure if i even have that part in there right)..but
it
> needs to tilt on the account tag, and sum the quantity.
> Dataset example:
> Forward Date AcctTag Symbol Strike pri
ce
> Typecode Quantity
> 9/1/2005 12:00:00 AM MN501 CL-OTC 0 F -25
> 9/1/2005 12:00:00 AM SU185 CL-OTC 0 F 85
> 10/1/2005 12:00:00 AM SU185 CL-OTC 0 F -45
> 3. Probably something else in there, that i can't figure out..but i figur
e
> theres one unseen problem in there.
>
> Use XXXCAPMAN
> Go
> sp_dbcmptlevel aaacapman, 90 'forces into SQL2005 mode
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE sp_POSSheets
> (
> @.inUSERID varchar(20),
> @.inCommodities varchar(250),
> @.outSU185 int OUTPUT,
> @.outBW685 int OUTPUT,
> @.outMN053 int OUTPUT,
> @.outSU598 int OUTPUT,
> @.outSU959 int OUTPUT,
> @.outSU897 int OUTPUT,
> @.outSU896 int OUTPUT,
> @.outSU797 int OUTPUT,
> @.outSU524 int OUTPUT,
> @.outSU511 int OUTPUT,
> @.outSU397 int OUTPUT,
> @.outSU960 int OUTPUT,
> @.outSU895 int OUTPUT,
> @.outSU935 int OUTPUT,
> @.out9SU809 int OUTPUT,
> @.out9SU885 int OUTPUT,
> @.outMN051 int OUTPUT,
> @.outSymbol varchar(20) OUTPUT,
> @.outForwardDate varchar(20) OUTPUT,
> @.outStrikePrice int OUTPUT,
> @.outTypeCode int OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> SELECT
> [1] AS 'SU185'
> ,[2] AS 'MN053'
> ,[3] AS 'SU598'
> ,[4] AS 'SU959'
> ,[5] AS 'SU897'
> ,[6] AS 'SU896'
> ,[7] AS 'SU797'
> ,[8] AS 'SU524'
> ,[9] AS 'SU511'
> ,[10] AS 'SU397'
> ,[11] AS 'SU960'
> ,[12] AS 'SU895'
> ,[13] AS 'SU935'
> ,[14] AS '9SU809'
> ,[15] AS '9SU885'
> ,[16] AS 'MN051'
> ,[17] AS 'BW685'
> From (
> SELECT TOP (100) PERCENT dbo.Trades.ForwardDate, dbo.Accounts.[Account
> Tag], dbo.Commodity.Symbol, dbo.Trades.StrikePrice, dbo.Trades.TypeCode,
> dbo.Trades.Quantity
> FROM dbo.Users INNER JOIN
> dbo.Commodity INNER JOIN dbo.Accounts INNER JOIN dbo.Trades ON
> dbo.Accounts.ID = dbo.Trades.AccountCode ON dbo.Commodity.ID =
> dbo.Trades.CommodityCode ON dbo.Users.ID = dbo.Trades.Trader
> WHERE (dbo.Commodity.Symbol LIKE N'CL%' OR
> dbo.Commodity.Symbol LIKE N'CL-OTC%') AND (dbo.Accounts.SheetAccount = 1)
> AND (dbo.Trades.ActiveFlag = 1) AND (dbo.Users.UserShortName = N'Carl')
> GROUP BY dbo.Trades.ForwardDate, dbo.Commodity.Symbol,
> dbo.Trades.StrikePrice, dbo.Trades.TypeCode, dbo.Accounts.[Account Tag],
> dbo.Trades.Quantity
> ORDER BY dbo.Trades.ForwardDate ) AS Data
> PIVOT(
> Sum(Data.Quantity)
> For Data.[Account Tag]
> IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[
10],[11],[12],[13],[14],[15],[16],
[17])
> ) AS PVT
> END
> GO
>
>
> --
> www.krushradio.com - Internet Radio for the rest of us

2005 clr returning xmldocument

I would like to return an xmldocument from a 2005 vb clr stored procedure.

This is my definition for the stored procedure. passing in a string, return xmldoc.

Can I not return an xmldoc as output? The solution will build, but not run.

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Sub SP_Transform(ByVal cc As String, <Out()> ByVal RetValue As XmlDocument)

Error 1 Column, parameter, or variable #2: Cannot find data type XmlDocument. SqlServerProject1

Interesting question. I'll have to try it.

My gut, however, is that you should just return the XML data using the new Xml Data Type in SQL Server 2005 and just use a DataReader like dr.GetSqlXml(index) in ADO.NET to read the XML and load it into an XmlDocument.

I wrote an example of that here-

Reading XML Data Type into XmlDocument Using ADO.NET - SQL Server 2005 Tutorials

Regards,

Dave