ADODB Streams

Does anyone have an example of how to use an ADODB Stream object in a Stored
Procedure to retrieve an image column.What are you going to do with the stream inside the stored procedure?
Did you mean that you would call a stored procedure from ASP or VB and use
the ADODB.Stream object against an IMAGE column that is returned as part of
the resultset of the stored procedure? Could you provide more details?
"Brian" <bfordSPAMOFF@.markem.com> wrote in message
news:eweWRhemFHA.2580@.TK2MSFTNGP09.phx.gbl...
> Does anyone have an example of how to use an ADODB Stream object in a
> Stored
> Procedure to retrieve an image column.
>|||We are trying to extract PDF data held in an image column inserted by a 3rd
party vendor. The vendor has advised that we "Have the stored procedure use
an ADODB Stream object to get the data."
Apparantly there are some extreaneous null characters in the data due to the
method they use to insert it and they advice that the ADODB Stream object
will remove them.
I have not tried this before and so wondering if it was even possible?
Brian
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#KEDYmemFHA.3916@.TK2MSFTNGP10.phx.gbl...
> What are you going to do with the stream inside the stored procedure?
> Did you mean that you would call a stored procedure from ASP or VB and use
> the ADODB.Stream object against an IMAGE column that is returned as part
of
> the resultset of the stored procedure? Could you provide more details?
>
>
> "Brian" <bfordSPAMOFF@.markem.com> wrote in message
> news:eweWRhemFHA.2580@.TK2MSFTNGP09.phx.gbl...
>|||Brian wrote:

> Does anyone have an example of how to use an ADODB Stream object in a
> Stored Procedure to retrieve an image column.
See:
How To Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
http://support.microsoft.com/?id=258038
Ben|||In that case, you would be using ADO.NET to retreive the image from the
stored procedure.
"Brian" <bfordSPAMOFF@.markem.com> wrote in message
news:%23xuPNremFHA.3648@.TK2MSFTNGP10.phx.gbl...
> We are trying to extract PDF data held in an image column inserted by a
> 3rd
> party vendor. The vendor has advised that we "Have the stored procedure
> use
> an ADODB Stream object to get the data."
> Apparantly there are some extreaneous null characters in the data due to
> the
> method they use to insert it and they advice that the ADODB Stream object
> will remove them.
> I have not tried this before and so wondering if it was even possible?
> Brian
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message
> news:#KEDYmemFHA.3916@.TK2MSFTNGP10.phx.gbl...
> of
>|||thanks everyone.
"Ben Amada" <ben@.REpoMOweVErpick.com> wrote in message
news:uwHUO3emFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Brian wrote:
>
> See:
> How To Access and Modify SQL Server BLOB Data by Using the ADO Stream
> Object
> http://support.microsoft.com/?id=258038
> Ben
>

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