ADO Stream from Stored Procedure with JScript fails mysteriously

Hi all, I've seen a couple of references to this problem, but only one
solution which had no appreciable effect.
I have a stored procedure which uses FOR XML EXPLICIT. The stored
procedure works quite happily and I can access the DB perfectly well
using VBScript.
Being of a religious bent, I'd rather not use VBScript.
I have the following *working* function, ported directly from the
broken JScript version to VBScript. The Jscript version fails on
Execute telling me that my procedure wasn't expecting any parameters.
Handy that, 'cos it doesn't get any.
I've tried using oCmd.Execute(0,0,1024),
oCmd.Execute(null,null,0x400), oCmd.Execute('', '', 0x404) without a
commandType, oCmd.Execute(N, N, 1024) where N is a null variable and
all points in between.
The error varies amusingly if I give it a nonsensical constant, but
provided I stick to something sensible, I get an 0x80040E21 error.
Finally, there are no differences between the two versions other than
the obvious ones.
Any idea how I can get this working in JScript?
******** begin code ********
function fetchXML()
set oCmd = Server.CreateObject("ADODB.Command")
oCmd.CommandType = 4
oCmd.CommandText = "prcMenu_FetchMenu"
set stmOut = Server.CreateObject("ADODB.Stream")
stmOut.Open()
set cn = getConnection()
oCmd.ActiveConnection = cn
oCmd.Properties("Output Stream") = stmOut
oCmd.Execute , , 1024
set cn = nothing
set oCmd = nothing
sOut = "<menu>"+stmOut.ReadText()+"</menu>"
set stmOut = nothing
fetchXML = sOut
End Function
******** end code *******
Cheers,
-- Bob
0x80040E21 is DB_E_ERRORSOCCURRED. Its description is: "Multiple-step
operation generated errors. Check each status value. No work was done."
Check your stored procedure text.
This procedure works (MS SQL Server 2000, local, database pubs, Windows
security):
//----
var conn = new ActiveXObject("ADODB.Connection");
conn.Open("Provider=SQLOLEDB;Integrated Security=SSPI;Initial
catalog=pubs");
var cmd = new ActiveXObject("ADODB.Command");
cmd.ActiveConnection = conn;
cmd.CommandText = "SELECT * FROM authors FOR XML AUTO";
var stream = new ActiveXObject("ADODB.Stream");
stream.Open();
cmd.Properties("Output Stream") = stream;
cmd.Execute(null, null, 0x400);
WScript.Echo(stream.ReadText());
//----
//--
Regards,
Vassiliev V. V.
http://www-sharp.com -
Scripting/HTA/.Net Framework IDE
"Bob Gregory" <bobgregory@.ppsltd.net> ??/?? ? ?? ??:
news:aba9c4c4.0407200859.7a64e94b@.posting.google.c om...
> Hi all, I've seen a couple of references to this problem, but only one
> solution which had no appreciable effect.
> I have a stored procedure which uses FOR XML EXPLICIT. The stored
> procedure works quite happily and I can access the DB perfectly well
> using VBScript.
> Being of a religious bent, I'd rather not use VBScript.
> I have the following *working* function, ported directly from the
> broken JScript version to VBScript. The Jscript version fails on
> Execute telling me that my procedure wasn't expecting any parameters.
> Handy that, 'cos it doesn't get any.
> I've tried using oCmd.Execute(0,0,1024),
> oCmd.Execute(null,null,0x400), oCmd.Execute('', '', 0x404) without a
> commandType, oCmd.Execute(N, N, 1024) where N is a null variable and
> all points in between.
> The error varies amusingly if I give it a nonsensical constant, but
> provided I stick to something sensible, I get an 0x80040E21 error.
> Finally, there are no differences between the two versions other than
> the obvious ones.
> Any idea how I can get this working in JScript?
> ******** begin code ********
> function fetchXML()
> set oCmd = Server.CreateObject("ADODB.Command")
> oCmd.CommandType = 4
> oCmd.CommandText = "prcMenu_FetchMenu"
> set stmOut = Server.CreateObject("ADODB.Stream")
> stmOut.Open()
> set cn = getConnection()
> oCmd.ActiveConnection = cn
> oCmd.Properties("Output Stream") = stmOut
> oCmd.Execute , , 1024
> set cn = nothing
> set oCmd = nothing
> sOut = "<menu>"+stmOut.ReadText()+"</menu>"
> set stmOut = nothing
> fetchXML = sOut
> End Function
> ******** end code *******
> Cheers,
> -- Bob
|||"Viatcheslav V. Vassiliev" <msnewsgroup@.www-sharp.com> wrote in message news:<O63yoqobEHA.4092@.TK2MSFTNGP10.phx.gbl>...
> 0x80040E21 is DB_E_ERRORSOCCURRED. Its description is: "Multiple-step
> operation generated errors. Check each status value. No work was done."
> Check your stored procedure text.
> This procedure works (MS SQL Server 2000, local, database pubs, Windows
> security):
<snip />
I submitted the system in VBScript thanks to deadline constraints so
this is now a purely academic issue. I know the stored proc is
absolutely fine, because I return useful results from VBScript with
the same procedure. The only difference is the language, and
presumably the underlying mechanics of speaking to COM therewith.
-- Bob