ASP ADO Connection to return XML from SQL Proc

[NickyJ]

Hi there,

I have the following SQL Stored Procedure

CREATE PROCEDURE pr_GetJobInfo2
@JobType int,
@CntryID int,
@JobTitle varchar(8000) OUTPUT
AS
SELECT @JobTitle = ltrim(rtrim(JobTitle))
FROM dbo.JOBS
WHERE JobType = @JobType
AND CntryID = @CntryID
FOR XML AUTO
GO


I wish to call this Procedure through an ASP page and return the XML

Currently I'm getting the following error

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

The FOR XML clause is not allowed in a ASSIGNMENT statement.

/xml_folder/Nick6.asp, line 34


Code at the moment is :

<% dim cn
dim cmd
dim p
dim pJobID
dim pJobTitle
dim retvalue
dim rs

cn = "Provider=SQLOLEDB.1;Data Source=lon-nt-sq02; Initial Catalog=hncom; user id = 'sa';password='passw0rd'"

Set cmd = Server.CreateObject ("ADODB.Command")

cmd.ActiveConnection = cn
cmd.CommandText = "pr_GetJobInfo2"
cmd.CommandType = adCmdStoredProc

set p = cmd.Parameters
p.Append cmd.CreateParameter("JobType", adInteger, adParamInput)
p.Append cmd.CreateParameter("CntryID", adInteger, adParamInput)
p.Append cmd.CreateParameter("JobTitle", adVarChar, adParamOutput,500)

cmd("JobType")= 1
cmd("CntryID")= 6

Set rs = cmd.Execute

Response.write rs.Fields.Item("XML_F52E2B61-18A1-11d1-B105-00805F49916B").Value

' clean up
If rs.State = adStateOpen then
rs.Close
End If
If cmd.State = adStateOpen then
cmd.Close
End If

Set rs = Nothing
Set cmd = Nothing

%>


Thanks all

[Trebz]

Hi

I reckon that error just means what it says, in that you cannot assign the results from FOR XML to a @variable.

Can you try doing:

CREATE PROCEDURE pr_GetJobInfo2
@JobType int,
@CntryID int,
@JobTitle varchar(8000) OUTPUT
AS
SELECT @JobTitle = (
SELECT [field] = ltrim(rtrim(JobTitle))
FROM dbo.JOBS
WHERE JobType = @JobType
AND CntryID = @CntryID
FOR XML AUTO
)

?

[NickyJ]

No joy there