Looping through a table for records in SQL Server 2000 using T-SQL

This will get you the rows for the body in one swoop without a cursor or an expensiveASP loop.  

SELECT
      qtyselected [td]
      , itemSku [td]
      , itemDesc + '<br>' + itemName [td]
FROM sometable [tr]
ORDER BY 3
FOR XML AUTO, ELEMENTS

This should create a long string which you can capture in ASP as an ADO Stream.
it will contain rows of TRs something like:
<tr><td>3</td><td>23421421</td><td>Shoes, Leather, Brown<br>Nike Milano</td></tr>

Oh, and if you're new to using FOR XML and are using Qery Analyzer, try putting a DBCC TRACEON(257) as the first line in query analyzer.  Also set the output mode to text, rather than grid and the XML will be pretty-printed.  This makes it quite a bit easier to read.

rherguth,
Thanks for your comment.... that is great!  I never knew about that command but I had done so much looping through asp.
Would this work the same if everything was in SQL?  

All I plan on doing is sending an orderid from asp.... I want the email creation and everything else done in SQL Server.
The only thing I would need returned to the ASP page is success or failure.  
So if I did

SELECT
     qtyselected [td]
     , itemSku [td]
     , itemDesc + '<br>' + itemName [td]
FROM sometable [tr]
ORDER BY 3
FOR XML AUTO, ELEMENTS

How would I use that formatted string as a variable within SQL Server?  

Also, is there anyway to unmask the '<BR>'  it always prints out
&lt;br&gt;
I tried using
char(60) + 'br' + char(62)

but it prints out the same way.  

I think this is the way to go but I am having some difficulty... especially when joining tables:

My code looks like this:

DBCC TRACEON(257)
SELECT
     orderDetail.itemQty [td]
     , orderDetail.itemSku [td]
     , products.Item_Desc + char(60) + 'br' + char(62) + products.Item_LDesc [td]
     , convert(varchar, orderDetail.itemPrice) [td]
     , convert(varchar, orderDetail.totalPrice) [td]
FROM (orderDetail left join products
on
orderDetail.itemSku = products.sku)
where orderDetail.orderNumber = 'BC0C005D-D4F3-4BAD-A8E5-57C02008FEA5'
FOR XML AUTO, ELEMENTS

I cant figure out where to put the [tr], it seems like everywhere I put it I get the error " Incorrect syntax near 'tr' "

Also, some of the in products.Item_desc have an '&' in them and it seems like if they do, the XML string is cut off right there on the output.
Example Output:

<orderDetail>
<td>1</td>
<td>LSTP017</td>
<td>MOZART &lt;br&gt; MICHAELSON </td>
<td>29.99</td>
<td>29.99</td>
</orderDetail>
<orderDetail>
<td>1</td>
<td>ASU25</td>
<td>UNIVERSAL HEADSHELL &

And that is it, it never completes the rest of the xml string.  I have tried using replace(Item_Desc, '&', 'AND') as well as for Item_LDesc  however it still prints out the way above.

I think if I can get the join to work with the [tr] and figure out why the output is truncated this would be the optimum way to go.

Again thanks for your help.

Actually I figured out how to get it to work with the [tr] by using the code as follows:

DBCC TRACEON(257)
SELECT
     [tr].itemQty [td]
     , [tr].itemSku [td]
     , replace(products.VNDR_Item_Desc, '&', 'AND') + char(60) + 'br' + char(62) + replace(products.Item_Desc, '&', 'AND') [td]
     , convert(varchar, [tr].itemPrice) [td]
     , convert(varchar, [tr].totalPrice) [td]
FROM orderDetail [tr], products
where [tr].orderNumber = 'BC0C005D-D4F3-4BAD-A8E5-57C02008FEA5' and [tr].itemSku = products.sku
ORDER by 3
FOR XML AUTO, ELEMENTS

But it still prints out truncated after the & and the <br> tag is still printing out &lt;br&gt;
Thanks again for any help.

Actually trying the above code out on another record, it appears as though it is not truncating after the &...  it is just truncating that line, whatever is on that line.
I can't determine if that is a display thing or if the results are really truncated for some reason.

It's truncating the line.  That's common when strings are concatenated in a select.  Try:
CAST( replace(products.VNDR_Item_Desc, '&', 'AND') + char(60) + 'br' + char(62) + replace(products.Item_Desc, '&', 'AND') AS VARCHAR(1024))

The varchar cast just needs to be the largest size the concatenation of the two strings could produce.

If you have the option of sending mail using an HTML format, the problems you're having with & < > would probably go away.

Let me think how you can capture the html stream as an @ var...

BTW, the explanation for the truncation of the column's contents is that SQL seems to use the length of the first concatenation to guess how large to make the resulting column.  Therefore, the effect you are seeing could ocurr with any concatenation of strings as part of a SELECT.  It's just insideously unnoticeable in many cases, because the data may be very uniform in some cases or there may just be a couple of long strings arbitrarily concatenated first.  No go loook through all your code and find all those :)

Thanks for your help so far,
Ok...
In trying to cast that line as a varchar Im having difficulty, probably because I had to change the line to include a case statement.
My new code looks like this:

DBCC TRACEON(257)
SELECT
     [tr].itemQty [td]
     , [tr].itemSku [td]
     , replace(products.VNDR_Item_Desc, '&', 'AND') + char(60) + 'br' + char(62)
     + CASE
      When products.VNDR_Item_Desc = products.Item_Desc THEN categories.cat_name
      else  products.Item_Desc
      End [td]
     , convert(varchar, [tr].itemPrice) [td]
     , convert(varchar, [tr].totalPrice) [td]
FROM orderDetail [tr], products, categories
where [tr].orderNumber = 'B88184E3-70F1-4D52-BDDA-430C8A26CD38' and [tr].itemSku = products.sku and categories.cat_id = products.Price_Code_3
ORDER by 1
FOR XML AUTO, ELEMENTS

When trying to add the varchar I can successfully add it like the following without errors:

, cast(replace(products.VNDR_Item_Desc, '&', 'AND') + char(60) + 'br' + char(62)
     + CASE
      When products.VNDR_Item_Desc = products.Item_Desc THEN categories.cat_name
      else  products.Item_Desc
      End as varchar(1024)) [td]

However it doesnt seem to have any effect and I really dont think Im doing it right by putting the 'As varchar' after the 'End'... seems like its looking for something for the case statement there.
Also, the output is still printing out truncated, but only on the second record, its very odd if it has to do with the length of that line because if you looking at the following output you will see that the first record has a long string that is not truncated:

Output:
<tr>
<td>1</td>
<td>ANG1.0</td>
<td>NITTY GRITTY/1.0 RECORD CLEANING MA &lt;br&gt;NITTY GRITTY/1.0 RECORD CLEANING MACHINE BLACK</td>
<td>255.00</td>
<td>255.00</td>
</tr>
<tr>
<td>1</td>
<td>ANG21/2GAL</td>
<td>NITTY GRITTY/PURE 2 (1/2 GALLON) &lt;br&gt;N

(2 row(s) affected)


Again I appreciate your help and feedback.

This is getting painful:

Please do yourself a favor and increase the Maximum Characters per Column in the Results tab under Tools|Options, from the default 256 to the maximum 8192.

Also, change:
convert(varchar, [tr].itemPrice) [td]

to:
convert(varchar(n), [tr].itemPrice) [td]

where n is an apropriate length for the column.

Also, the "FOR XML" clause automatically HTML encodes your string, so:
MOZART & MICHAELSON

Becomes:
MOZART &lt;br&gt; MICHAELSON </td>

If this is not what you want, you will have to replace all characters, not only the &

acperkins,
Thanks for your replies.
I have never used "for XML AUTO" and for the most part I have only dealt with SQL in basic terms interacted through ASP.
Thanks for the heads up on the output column size.  

From what your saying I take it to mean that the whole xml string is created and the html encoded.  
I dont quite understand 'replace all characters' but if the above line is true... then I can just search the resultant xml string for &lt;br&gt; and replace it with <br>....

If I could figure out how to return this XML string as a variable, or pass it to another function I could do the replace there but I still am no closer to accomplishing this.
I saw it mentioned how to insert the XML string into another table and return the table but that doesn't get me any closer either.

Am I totally off?

>>From what your saying I take it to mean that the whole xml string is created and the html encoded. <<
Yes.

>>then I can just search the resultant xml string for &lt;br&gt; and replace it with <br>..<<
You don't have to do that.

>>If I could figure out how to return this XML string as a variable<<
This is not possible with T-SQL alone, but when you return it to your front-end (VB or ASP) you can read it in to an ADO Stream object and then asign it to a variable.

Post your VB/ASP code and i will modify appropriately.

>>Am I totally off?<<
Just slightly <g>

Thats the thing, I dont want to do this in asp/vb.... I have it working that way right now getting records and then creating the email and then sending them to the email component.
I thought it would be more reliable if everything was handled in the stored procedure.
I will post my code of the stored procedure so that you have an idea of what is happening:

----------Procedure-------------------
proc spProcessOrder @uID nvarchar(50), @ShipfName nvarchar(255), @ShiplName nvarchar(255), @ShipAddress nvarchar(255), @ShipCity nvarchar(255), @dmState nvarchar(255), @ShipPostCode nvarchar(255), @sCountryName nvarchar(255), @BillfName nvarchar(255), @BilllName nvarchar(255), @BillAddress nvarchar(255), @txtBillCity nvarchar(255), @sBillState nvarchar(255), @txtBillPostCode nvarchar(255), @bCountryName nvarchar(255), @payType nvarchar(50), @txtCCNumber nvarchar(255), @ccSecure char(10), @ccExpDate char(10), @dmShipMethod nvarchar(255), @txtSpecialHandling nvarchar(255), @isGift char(10), @txtGiftCard nvarchar(255), @txtEmail nvarchar(255), @txtDayPhone nvarchar(255), @promoCode nvarchar(255), @shipStock nvarchar(10), @usMail nvarchar(50), @usrSessId nvarchar(255),
@oNum uniqueidentifier OUTPUT

---Save Order------
AS
declare @hBody nvarchar(4000)
declare @fBody nvarchar(4000)
declare @name nvarchar(1000)
declare @rsID uniqueidentifier
set @rsID = NewID()
declare @subject nvarchar (255)
declare @rcpt nvarchar(255)
declare @replyTo nvarchar(255)
set @name = @ShipfName + ' ' + @ShiplName
set @subject = 'New Web Order'

--SAVE ORDER-- This saves the order and all relevant order information.
Insert INTO orders
(orderNumber, customerNumber,  sfName, slName, sAddress1, sCity, sState, sPostalCode, sCountry, bfName, blName, bAddress1, bCity, bState, bPostalCode, bCountry, paymentType, ccNum, ccAuth, ccExpDate, shipping, spcHandling, giftItem, giftMessage, emailAddress, phoneNumber, promoCode, shipStock, usMail)
Values (@rsID, @uID, @ShipfName,@ShiplName,@ShipAddress,@ShipCity, @dmState, @ShipPostCode, @sCountryName, @BillfName, @BilllName, @BillAddress, @txtBillCity, @sBillState, @txtBillPostCode, @bCountryName, @payType, @txtCCNumber, @ccSecure, @ccExpDate, @dmShipMethod, @txtSpecialHandling, @isGift, @txtGiftCard, @txtEmail, @txtDayPhone, @promoCode, @shipStock, @usMail)


--Save Order Details --- This will save the details of the shopping cart to the order details, passed @orderNumber and @usrSessid
Insert into orderDetail (orderNumber, itemSku, itemQty, itemPrice, totalPrice, tstamp)
SELECT  @rsID, c.product_id, c.qty_selected, isNull(pr.prPrice,p.Price_Code_1) as Price_Code_1, convert(money, Price_Code_1 * c.qty_selected), getDate()
FROM s_cart c LEFT JOIN promoCode pr
on (c.promoCode = pr.promoCode AND c.product_id = pr.sku), Products p LEFT JOIN exItems e
ON p.sku = e.sku
LEFT JOIN categories cat ON p.price_code_3 = cat.cat_id
WHERE c.product_id = p.SKU AND c.usrsessid = @usrSessId

--Create the body of the email here--
--This is where I will put the function that retrieves the XML string from the procedure in question.



--END CREATE--


--Email the order---- this is the sp that will send the email functioning perfectly when passed the proper variables.
exec sp_SMTPMail @txtEmail, @rcpt, @hBody, @subject, @rcpt, @txtEmail


--Email the order confirmation
exec sp_SMTPMail @txtEmail, @rcpt, @hBody,  @subject, @txtEmail, @rcpt
-----------end procedure

So what I would like to do is get the results of the XML string into the @hBody variable or some other way to pass it to sp_SMTPMail.
As I said, I can do this without a problem in ASP as my VB/ASP knowledge is much superior to my SQL knowledge but I am attempting to streamline the process and put it all into one transaction.
That is why I asked if I was totally off because I thought it would be a better way of doing it than staying with a bunch of sub routines in asp.

>>Thats the thing, I dont want to do this in asp/vb<<
I think you misunderstand me.  Let me try again:  In order to access the Xml generated by SQL Server in a stored procedure you will have to use ASP code like you are now, but instead use the ADO Stream object to read the Xml.  You simply cannot read the Xml into a T-SQL variable.

>>So what I would like to do is get the results of the XML string into the @hBody variable or some other way to pass it to sp_SMTPMail.<<
Again.  It will not happen directly with SQL Server 2000 (you can of course wait for SQL Server 2005 ...) You will have to return the Xml to your ASP code and then re-submit it to a new Stored Procedures as a parameter.

>>As I said, I can do this without a problem in ASP as my VB/ASP knowledge is much superior to my SQL knowledge but I am attempting to streamline the process and put it all into one transaction.<<
There is nothing wrong with what you are doing and it is by far a better way to use FOR Xml than your original ASP code:
<code>
Do While NOT rs.BOF AND NOT rs.EOF
...

The drawback is that you will have to call two stored procedures.  Trust me the alternatives are worse.

Besides from a purist point of view you should not be mixing retrieving data with sending emails.  In other words, you should have one or more stored procedures to get the data and just one generic stored procedure to send the emails.

acperkins,
Thanks again for your feedback.  I will create something then that does this and try to do it.  Normally I use something along the lines of:
set rs = dcnDB.execute(Sql)

But attempting to work with stored procs that return values, I began using the commandtext method with parameters and kept receiving the following error:
Error Type: ADODB.Command (0x800A0BB9) Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
But thats for another discussion.

I will attempt to create some asp code to retrieve this data and post it here.
Alternatively I saw a way to use the OpenQuery function of SQL server to open the xml string and put it into a table as located here:
http://www.sqlxml.org/faqs.aspx?faq=104
But I could not figure out how to make sense of that.
Thanks again.  
Im going to bump the point value on this, as it has turned out to be more difficult than I expected.

>>Normally I use something along the lines of:
set rs = dcnDB.execute(Sql)<<
In this case, there is no need for the overhead of a Recordset object.  Look up retrieving Xml with the ADO Stream object.

>>Alternatively I saw a way to use the OpenQuery function of SQL server<<
Let me get this clear:  You are going to open another connection to the database to do something as trivial as this.  Why??  Do you honestly expect that is going to be faster than returning the result back to ASP?  

>>as it has turned out to be more difficult than I expected.<<
Trust me, it is quite straightforward and when you have it completed you will see what I mean.

Again, Im having difficulty with this and I am growing more frustrated.

The error I am receiving from the browser is:
Error Type:
Microsoft VBScript runtime (0x800A01F9)
Invalid or unqualified reference
/getXML.asp, line 16

The code I have is this:

<!--#include virtual="/include/adovbs.inc"-->
<%
Set dcnDB = Server.CreateObject("ADODB.Connection")
set objComm = server.CreateObject("ADODB.Command")
set objStream = server.CreateObject("ADODB.Stream")
dcnDB.ConnectionString = "NULL=NULL; Data Source = 127.0.0.1; Initial Catalog = NULL; User Id = NULL; Password = NULL"
dcnDB.CursorLocation = 3
dcnDB.Open
objComm.ActiveConnection = dcnDB
dim sSql
dim orderNumber
orderNumber = "C5756881-5E3D-4D0F-95EE-4C9BEC0113DF"
sSql = "sp_getXML"
objComm.CommandType = 1 'adCmdText
objComm.CommandText = sSql
objComm.Parameters.Append .CreateParameter("@orderNum", adGUID, adParamInput, 72 ,orderNumber)  '------------------------LINE 16
objComm.Properties("Output Stream") = objStream

objStream.Open
objComm.Execute , , 1024 'adExecuteStream

Response.Write objStream.ReadText
%>

When I output the err.description I receive this "Object doesn't support this property or method"

In SQL the sp declaration consists of the following :
proc sp_getXML @orderNum uniqueIdentifier

Any help would again be greatly appreciated.

I suspect you need to add SET NOCOUNT ON at the top of your stored procedure, but post the sp_getXML stored procedure and I can confirm.

Also, are you sure the constants adGUID and adParamInput are defined (either using adovbs.inc. or meta tag).  I ask because you have this line:
objComm.CommandType = 1 'adCmdText
Which makes me thing you do not.

Totally unrelated, but don't forget to close the Stream after you have read it.

acperkins,
adGUID and adParamInput are defined in adovbs.inc.

I copied the instantiating code from a how to that I found when I searched for 'retrieving Xml with the ADO Stream object' that is why some of the literals are still in there and some are the const variables defined in the adovbs.inc file.

Also, I did put SET NOCOUNT ON at the top of my sp but it still results in the same error.  
This is the sp:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER  proc sp_getXML @orderNum uniqueIdentifier
as
      SET NOCOUNT ON       
      SELECT
           [tr].itemQty [td]
           , [tr].itemSku [td]
           , products.VNDR_Item_Desc + '<br>'
           + CASE
            When products.VNDR_Item_Desc = products.Item_Desc THEN categories.cat_name
            else  products.Item_Desc
            End [td]  
           , convert(varchar(25), [tr].itemPrice) [td]
           , convert(varchar(25), [tr].totalPrice) [td]
      FROM orderDetail [tr], products, categories
      where [tr].orderNumber =  @orderNum and [tr].itemSku = products.sku and categories.cat_id = products.Price_Code_3
      ORDER by 1
      FOR XML AUTO, ELEMENTS


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

>>adGUID and adParamInput are defined in adovbs.inc.<<
I realize they are.  I was trying to get you to confirm that you were in fact including the advbs.inc in your page.

Something else and also unrelated, the size of a uniqueidentifier is 16 not 72.

I am still thinking about the real problem.

Also, and in the unrelated category this:
objComm.ActiveConnection = dcnDB

Should be:
Set objComm.ActiveConnection = dcnDB

I suspect the problem is the following line:
objComm.CommandType = 1 'adCmdText

It should be:
objComm.CommandType = adCmdProc

Either that or you need to change the CommandText property.

I mean you can use CommandType = adCmdText, but you will have to change the CommandText to something like:
"{call sp_getXML (?)}"
So that it knows it is a stored procedure.

>>>Something else and also unrelated, the size of a uniqueidentifier is 16 not 72.
I misread on the page http://www.w3schools.com/ado/met_comm_createparameter.asp...  72 is actually the value equivalent to the constant adGUID not the size.

>>It should be:
>>objComm.CommandType = adCmdProc

With that set, and the following code:

<!--#include virtual="/include/adovbs.inc"-->
<%
err.clear
'on error resume next
Set dcnDB = Server.CreateObject("ADODB.Connection")
set objComm = server.CreateObject("ADODB.Command")
set objStream = server.CreateObject("ADODB.Stream")
dcnDB.ConnectionString = "my Connection String"
dcnDB.CursorLocation = 3
dcnDB.Open
set objComm.ActiveConnection = dcnDB
dim sSql
dim orderNumber
orderNumber = "C5756881-5E3D-4D0F-95EE-4C9BEC0113DF"
sSql = "sp_getXML"
objComm.CommandType = adCmdProc
objComm.CommandText = sSql
objComm.Parameters.Append .CreateParameter("@orderNum", adGUID, adParamInput, 16 ,orderNumber)
objComm.Properties("Output Stream") = objStream

objStream.Open
objComm.Execute , , 1024 'adExecuteStream
'if objComm.errors <> 0 then
'response.write err.description
'end if
xmlStr =  objStream.ReadText
objStream.close
response.write(xmlStr)
%>

I am still receiving the error:
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.  
Line 16

which is :
objComm.CommandType = adCmdProc

And it would help if I could spell <g>.  Change:

objComm.CommandType = adCmdProc

To:
objComm.CommandType = adCmdStoredProc

P.S. Option Explicit would help here

I thought option explicit was set in the include file...
I set it within the script itself now.

The latest error:
Microsoft VBScript runtime error '800a01f9'

Invalid or unqualified reference

/getXML.asp, line 18


Line 18 is
objComm.Parameters.Append .CreateParameter("@orderNum", adGUID, adParamInput, 16 ,orderNumber)

Assuming you have declared dcnDB, dcnDB, objStream, xmlStr, etc. it appears you are attempting to use objStream before you even open it. Try it this way (untested) it may be a little clearer:

<!--#include virtual="/include/adovbs.inc"-->
<%

dim sSql
dim orderNumber

sSql = "sp_getXML"
orderNumber = "C5756881-5E3D-4D0F-95EE-4C9BEC0113DF"

err.clear
'on error resume next
Set dcnDB = Server.CreateObject("ADODB.Connection")
dcnDB.ConnectionString = "my Connection String"
dcnDB.CursorLocation = 3
dcnDB.Open

set objStream = server.CreateObject("ADODB.Stream")
' objStream.Type = adTypeText                  ' You may need to do this
objStream.Open

set objComm = server.CreateObject("ADODB.Command")
set objComm.ActiveConnection = dcnDB
objComm.Properties("Output Stream") = objStream
objComm.CommandType = adCmdStoredProc               ' Change this
objComm.CommandText = sSql
objComm.Parameters.Append .CreateParameter("@orderNum", adGUID, adParamInput, 16 ,orderNumber)
objComm.Execute , , 1024 'adExecuteStream
Set objComm = Nothing     ' Add this
'if objComm.errors <> 0 then
'response.write err.description
'end if
xmlStr =  objStream.ReadText
objStream.close
Set objStream = Nothing
response.write(xmlStr)
%>

And while not necessary you may want to get in the habit of setting the default value for properties, so change:
objComm.Properties("Output Stream") = objStream

To:
objComm.Properties("Output Stream").Value = objStream

Using the code specified from above and echoed below, I receive the following error:
Microsoft VBScript runtime error '800a01f9'

Invalid or unqualified reference

/getXML2.asp, line 29


The code is:

<%Option Explicit%>
<!--#include virtual="/include/adovbs.inc"-->
<%
Dim dcnDB
Dim objComm
Dim objStream
dim sSql
dim orderNumber

sSql = "sp_getXML"
orderNumber = "C5756881-5E3D-4D0F-95EE-4C9BEC0113DF"

err.clear
'on error resume next
Set dcnDB = Server.CreateObject("ADODB.Connection")
dcnDB.ConnectionString = "Connectiong String"
dcnDB.CursorLocation = 3
dcnDB.Open

set objStream = server.CreateObject("ADODB.Stream")
' objStream.Type = adTypeText                  ' You may need to do this
objStream.Open

set objComm = server.CreateObject("ADODB.Command")
set objComm.ActiveConnection = dcnDB
objComm.Properties("Output Stream").Value = objStream
objComm.CommandType = adCmdStoredProc               ' Change this
objComm.CommandText = sSql
objComm.Parameters.Append .CreateParameter("@orderNum", adGUID, adParamInput, 16 ,orderNumber)
objComm.Execute , , 1024 'adExecuteStream
Set objComm = Nothing     ' Add this
'if objComm.errors <> 0 then
'response.write err.description
'end if
xmlStr =  objStream.ReadText
objStream.close
Set objStream = Nothing
response.write(xmlStr)
%>

I uncommented the line that contains  objStream.Type = adTypeText but it had no effect.

Im really at a loss on this, I have never had any issues with calling stored procedures to this extent.  
Granted I normally don't use ado commands, but I do have a stored procedure that I call with an ADO command and parameters and it works fine.
I appreciate all your diligence on this.  I have googled as well and it seems like everything I come up with for returning XML strings are things I have already tried, mostly from this thread.

In addition the error on line 29 referenced this line:
objComm.Parameters.Append .CreateParameter("@orderNum", adGUID, adParamInput, 16 ,orderNumber)

Sometimes it is the obvious things that will bite you ...

Let me explain what has happened:
Evidently you copied the code from a site, overlooking the fact that they were using a With clause for the Command object.  So in order to fix it, you must make the following change:
objComm.Parameters.Append .CreateParameter("@orderNum", adGUID, adParamInput, 16 ,orderNumber)

To:
objComm.Parameters.Append objComm.CreateParameter("@orderNum", adGUID, adParamInput, 16 ,orderNumber)

In addition, in ASP (or VB classic for that matter) there is no equivalent to the uniqueidentifier so you will have to use adVarChar instead with the correct length (36 bytes).  There is no need to change the Stored Procedure, it will convert automatically. So the above line would look like this:
objComm.Parameters.Append .CreateParameter("@orderNum", adVarChar, adParamInput, 36 ,orderNumber)

Finally, you need to declare xmlStr

Unbelieveable I actually copied the parameter from my other command object that I had been working with, which as you said had a with clause.

So I no longer get that error.

The new error:

Microsoft OLE DB Provider for SQL Server error '80040e21'

Invalid character value for cast specification.

/getXML2.asp, line 30

Line 30:
objComm.Execute , , 1024 'adExecuteStream


So I figured I would try putting { } around my order number which is passed as the GUID... which now spits out no error.  
However it does not print anything either.  
I have tried it with
objStream.Type = adTypeText
commented and uncommented.

At least I am not receiving any errors now.
I just can't believe I overlooked something so somewhat obvious considering the line number was that line....

>>Invalid character value for cast specification. <<
Did you see my second point.  You need to use adVarChar.

If you have done this already, than post your code as it stands now.

Actually I take that back, it does work using:

orderNumber = "{C5756881-5E3D-4D0F-95EE-4C9BEC0113DF}"
objComm.Parameters.Append objComm.CreateParameter("@orderNum", adGUID, adParamInput, 16, orderNumber)

>>I have tried it with
objStream.Type = adTypeText
commented and uncommented.<<

It does not make any difference since the default value is adTypeText.

Post your code you are currently using.

>>Invalid character value for cast specification. <<
I didnt see that point until now.

I am still not seeing any output written.
The result is the same whether I use adGUID and the {} or the adVarChar.
The code I have now, including your modification, is:

<%Option Explicit%>
<!--#include virtual="/include/adovbs.inc"-->
<%
Dim dcnDB
Dim objComm
Dim objStream
dim sSql
dim orderNumber
Dim xmlStr
sSql = "sp_getXML"
orderNumber = "C5756881-5E3D-4D0F-95EE-4C9BEC0113DF"

err.clear
'on error resume next
Set dcnDB = Server.CreateObject("ADODB.Connection")
dcnDB.ConnectionString = "Connect String"
dcnDB.CursorLocation = 3
dcnDB.Open

set objStream = server.CreateObject("ADODB.Stream")
'objStream.Type = adTypeText                  ' You may need to do this
objStream.Open

set objComm = server.CreateObject("ADODB.Command")
set objComm.ActiveConnection = dcnDB
objComm.Properties("Output Stream").Value = objStream
objComm.CommandText = sSql
objComm.CommandType = adCmdStoredProc               ' Change this
objComm.Parameters.Append objComm.CreateParameter("@orderNum", adVarChar, adParamInput, 36 ,orderNumber)
objComm.Execute , , 1024 'adExecuteStream
Set objComm = Nothing     ' Add this
'if dcnDB.errors.count <> 0 then
'response.write err.description
'end if
xmlStr =  objStream.ReadText
objStream.close
Set objStream = Nothing

%>
<html>
<head></head>
<body>
<%response.write(xmlStr)%>
</body></html>

At this point I think I am done for the night.  Working with this and another sp all day has got me fried.  Im starting to make stupid mistakes that I dont normally make, aside from the ones I've made in this post :-)
Thanks for all the help.

s.

Suggest you test your stored procedure with the same value again in SQL Query Analyzer.

>>At this point I think I am done for the night.<<
You will figure it out.

>>> I suspect you are not getting any output, because there is none.  In other words, there is no matching GUID.  But let me show you what I did to duplicate the problem:

::sigh::

Right again.  I must have really been worn out on friday, when I changed from my testing server to my live server I didnt change the GUID I was using to actually pull valid data.

Thanks again.