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

Admin Version of WITH(UPDLOCK)?

Is there anything I can do from the Enterprise Manager console or from
within a JDBC connection to achieve the same effect as WITH(UPDLOCK)?

Yes, I could change all of my SQL statements to include the lock... but
isn't there any way to set or tweak something in SQL Server so that I won't
have to hack a lot of code* to make things concurrent? Perhaps a way to set
UPDLOCK as the default behaviour for the server, or schema, or table, or
something?

Jerry H.

* == The existing SQL has to remain as generic as possible so that it can be
implemented for four other databases.Jerry Hewett (jerhewet@.yahoo.com) writes:

Quote:

Originally Posted by

Is there anything I can do from the Enterprise Manager console or from
within a JDBC connection to achieve the same effect as WITH(UPDLOCK)?
>
Yes, I could change all of my SQL statements to include the lock... but
isn't there any way to set or tweak something in SQL Server so that I
won't have to hack a lot of code* to make things concurrent? Perhaps a
way to set UPDLOCK as the default behaviour for the server, or schema,
or table, or something?

You can use SET TRANSACTION ISOLATION LEVEL to change the default isolation
level for the transaction. However, UPDLOCK does not map to an isolation
level, and in any case I don't think you would want that lock on every
SELECT statement. Maybe you can tell us a little more on what you want to
achieve?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns980C634CC98A6Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Maybe you can tell us a little more on what you want to achieve?

Right now I'd be happy with even a minimal level of concurrency from SQL
Server via multiple JDBC connections. :-(

The code/SQL we're using works like a charm when stress- / load-tested
against multiple users / threads under Oracle, Derby, MySQL, etc. but
deadlocks constantly and consistently under SQL Server.

So far WITH(UPDLOCK) doesn't seem to be doing much. Neither does
selectMethod=cursor. I'm not a DBA, so I figure there must be something I'm
not doing right (or not doing at all).

Either that, or every other SQL database on the planet is a *LOT* more
forgiving and/or better equipped to deal with deadlocks than SQL Server is.
More than happy at this point to be proved completely wrong. ;-)

Jerry H.|||Jerry Hewett (jerhewet@.yahoo.com) writes:

Quote:

Originally Posted by

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns980C634CC98A6Yazorman@.127.0.0.1...
>

Quote:

Originally Posted by

>Maybe you can tell us a little more on what you want to achieve?

>
Right now I'd be happy with even a minimal level of concurrency from SQL
Server via multiple JDBC connections. :-(
>
The code/SQL we're using works like a charm when stress- / load-tested
against multiple users / threads under Oracle, Derby, MySQL, etc. but
deadlocks constantly and consistently under SQL Server.
>
So far WITH(UPDLOCK) doesn't seem to be doing much. Neither does
selectMethod=cursor. I'm not a DBA, so I figure there must be something
I'm not doing right (or not doing at all).
>
Either that, or every other SQL database on the planet is a *LOT* more
forgiving and/or better equipped to deal with deadlocks than SQL Server
is. More than happy at this point to be proved completely wrong. ;-)

Different DB-engines have different architectures, and what works well on
one engine may not work well on another, even if the code as such is
portable.

Since you did not include any information of what you are actually doing,
it's impossible to assist further.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx