BinaryStream.Write ByteArray erroring with Code 800A0BB9, Source ADODB.Stream

[thefab...@gmail.com]

Hello,

I am new to VBScript and am trying to troubleshoot a script. The
script is reaching out to a SQLServer database and grabbing some data
and trying to drop each record into a file on my local Windows Server
2003 file system. I have been Googling for the solution, but am
having no luck. Can someone point to some possible solutions,
please? This is the error I am getting:

Line: 63 (BinaryStream.Write ByteArray)
Char: 5
Error: Arguments are of the wrong type, are out of acceptable range,
or are in conflict with one another
Code: 800A0BB9
Source: ADODB.Stream

option explicit

const ADTYPETEXT = 2
const ADOPENKETSET = 1
const ADLOCKOPTIMISTIC = 3

dim oCn
dim oRS
dim o St

set oCn = Wscript.CreateObject("ADODB.Connection")
oCn.ConnectionString = "Driver=
{SQLServer};Server=<ipaddress>;Database=<dbname>;Uid=<userid>;Pwd=<password>;"
oCn.Open

set oRs = oCn.execute("EXEC ABC.GetDocumentsByDateRange '10/25/2008',
'1/1/2009''")
'wscript.echo(oRs(0)) 'id
'wscript.echo(oRs(1)) ' category
'wscript.echo(oRs(2)) 'type
'wscript.echo(oRs(3)) 'title
'wscript.echo(oRs(4)) 'filename
'wscript.echo(oRs(5)) 'binary
'wscript.echo(oRs(6)) 'date
'wscript.echo(oRs(7)) 'date
'wscript.echo(oRs(8)) 'date
'wscript.echo(oRs(9)) 'short classification
'wscript.echo(oRs(10)) 'long classification

while not oRs.eof
SaveBinaryData oRs(4), oRs(5), "ABC-" & oRs(1) & "-" & oRs(2)
oRs.MoveNext
wend

oRs.Close
oCn.Close
Set oRs = Nothing
Set oSt = Nothing
Set oCn = Nothing
wscript.echo("Work Complete")

Function SaveBinaryData(Filename,ByteArray,DirName)
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2

dim fso
DirName = replace(DirName,"\","")
DirName = replace(DirName,"'","")
DirName = replace(DirName,"/","")
DirName = replace(FileName, "\","")
DirName = replace(FileName, "'","")
DirName = replace(FileName, "/","")
set fso = CreateObject("Scripting.FileSystemObject")
if fso.FolderExists("e:\data\" & DirName) = False then
fso.CreateFolder("e:\data\" & DirName)
end if

dim BinaryStream
Set BinaryStream = CreateObject("ADODB.Stream")

BinaryStream.Type = adTypeBinary
BinaryStream.Open
BinaryStream.Write ByteArray <== ******this is Line 63,
where it's failing
BinaryStream.SaveToFile "e:\data\ABC\" & FileName,
adSaveCreateOverWrite
End Function

[Richard Mueller [MVP]]

<thefab...@gmail.com> wrote in message
news:12a8d0e6-8559-43a1...@y13g2000yqn.googlegroups.com...

I would check to make sure that TypeName(oRs(5)) = "Byte()", and if so,
check with UBound that the array is not empty.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

[thefab...@gmail.com]

On Apr 8, 9:56 am, "Richard Mueller [MVP]" <rlmueller-
nos...@ameritech.nospam.net> wrote:
> <thefabulou...@gmail.com> wrote in message

> --- Hide quoted text -
>
> - Show quoted text -

Hi Richard,

I have never looked at VBScript before today. How do I check to make
sure that TypeName(oRs(5)) = "Byte()"? Could you also explain in more
detail about UBound? Are these things I need to look at in SQLServer?

Thank you.

[Richard Mueller [MVP]]

<thefab...@gmail.com> wrote in message
news:79971d8f-0eb5-4d1d...@v19g2000yqn.googlegroups.com...

Hi Richard,

Thank you.

-----------

Your error message indicates an argument of the wrong type. The
BinaryStream.Write method expects a byte array, since you specified
adTypeBinary. I was suggesting that you make sure that oRs(5), or ByteArray
in the function, is datatype Byte(), which is a byte array. I think it can
also be Variant(), which is a variant array, as long as each element is an
integer in the proper range (say between 0 and 255). In any case, the
VBScript function TypeName returns the datatype of any variable reference.
In addition, if the variable refers to an array, the UBound function returns
the upper bound of the array. For example:
========

set oCn = Wscript.CreateObject("ADODB.Connection")
oCn.ConnectionString =
"Driver={SQLServer};Server=<ipaddress>;Database=<dbname>;Uid=<userid>;Pwd=<password>;"
oCn.Open

set oRs = oCn.execute("EXEC ABC.GetDocumentsByDateRange '10/25/2008',
'1/1/2009''")

while not oRs.eof
strType = TypeName(oRs(5).Value)
If (strType = "Byte()") Or (strType = "Variant()") Then
Wscript.Echo "ID: " & oRs(0).Value & ";" & strType _
& " (" & UBound(oRs(5).Value) & ")"
Else
Wscript.Echo "ID: " & oRs(0).Value & ";" & strType
End If
oRs.MoveNext
wend
=======
The above loops through the rescordset and checks the datatype of the
"binary" on every row.

[thefab...@gmail.com]

On Apr 8, 6:12 pm, "Richard Mueller [MVP]" <rlmueller-

Hi again Richard,

Thank you for explaining in more detail what you were asking me to
try. I made the changes to the script using your code, and the result
I am getting is a pop-up box that loops through the recordset and
displays (example): ID: 27654; Date

This leads me to believe that oRs(5) is not a "Byte()", but rather a
Date datatype. I have been executing this script weekly for five
months straight and never had any problems with it until a few weeks
ago. The first thing I did was ask the owners of the SQLServer
database if anything on their end had changed that would affect my
script, and they said NO. I am going to go check with them again now
to verify the columns and order that these columns are being returned
to me in my record set. Something must have changed on their end,
right??!!

Laura

[Richard Mueller [MVP]]

<thefab...@gmail.com> wrote in message
news:60c334d9-fcb7-4efa...@f30g2000vbf.googlegroups.com...

On Apr 8, 6:12 pm, "Richard Mueller [MVP]" <rlmueller-

<<.. Snip

Hi again Richard,

Thank you for explaining in more detail what you were asking me to
try. I made the changes to the script using your code, and the result
I am getting is a pop-up box that loops through the recordset and
displays (example): ID: 27654; Date

This leads me to believe that oRs(5) is not a "Byte()", but rather a
Date datatype. I have been executing this script weekly for five
months straight and never had any problems with it until a few weeks
ago. The first thing I did was ask the owners of the SQLServer
database if anything on their end had changed that would affect my
script, and they said NO. I am going to go check with them again now
to verify the columns and order that these columns are being returned
to me in my record set. Something must have changed on their end,
right??!!

Laura
---------

Yes, something seems to have changed. If the field is datatype Date, that
would account for your error. I assume GetDocumentsByDateRange is a stored
procedure. Possibly that has been recently modified. For example, if a
return value was added or deleted from the recordset, the binary value may
no longer be the sixth value (the first is oRs(0)). It might be oRs(4) or
oRs(6). Simply changing the order of the values returned by
GetDocumentsByDateRange would cause this. Or a field in the database table
could have been modified so the datatype is different.

You are retrieving values from the recordset by index number. For example,
oRs(5) refers to the sixth column (field) in the recordset. I generally
refer to fields by name, using syntax similar to
oRs.Fields("FieldName").Value. This way the order of the fields doesn't
matter. The name of the field is less likely to change, plus it helps
indicate what the value is. Someone could have modified the stored procedure
to change the order of the values returned, or added a value, thinking this
was a minor change that would not affect anyone. As long as nothing else
changed, your program would still work if you referred to fields by name.
You would need to be told the field names, or be able to view the stored
procedure.

[thefab...@gmail.com]

On Apr 8, 6:12 pm, "Richard Mueller [MVP]" <rlmueller-

So....I talked to the SQLServer folks, and they in fact did delete
their Binary column. I cleaned up my script a little bit, referencing
the columns by their actual names. The script started to run fine,
pulled about 3,000+ records (of maybe 100,000 records), dropping them
into my local folder, "e:\data\ABC\", as expected, but then crashed
again with the same error I was originally getting (now on Line 51).
Any ideas what might now be the problem?

Error:

Line: 51 (BinaryStream.Write ByteArray)

Char: 5
Error: Arguments are of the wrong type, are out of acceptable range,
or are in conflict with one another
Code: 800A0BB9
Source: ADODB.Stream

New code:

option explicit

const ADTYPETEXT = 2
const ADOPENKETSET = 1
const ADLOCKOPTIMISTIC = 3

dim oCn
dim oRS
dim o St

set oCn = Wscript.CreateObject("ADODB.Connection")
oCn.ConnectionString = "Driver=
{SQLServer};Server=<ipaddress>;Database=<dbname>;Uid=<userid>;Pwd=<password­
>;"
oCn.Open

set oRs = oCn.execute("EXEC ABC.GetDocumentsByDateRange '10/25/2008',
'1/1/2009''")

'wscript.echo(oRs(0)) 'ID (BIGINT)
'wscript.echo(oRs(1)) 'Document_Category (NVARCHAR(50))
'wscript.echo(oRs(2)) 'Document_Type (NVARCHAR(50))
'wscript.echo(oRs(3)) 'Document_Name (NVARCHAR(100))
'wscript.echo(oRs(4)) 'Document_FileName (NVARCHAR(100))
'wscript.echo(oRs(5)) 'Binary_Content (IMAGE)
'wscript.echo(oRs(6)) 'Document_DateOfInformation (DATETIME)
'wscript.echo(oRs(7)) 'Document_DateUploaded (DATETIME)
'wscript.echo(oRs(8)) 'Document_DateLastUpdated (DATETIME)
'wscript.echo(oRs(9)) 'Classification_Short (NVARCHAR(50))
'wscript.echo(oRs(10)) 'Classification_Long (NVARCHAR(50))

while not oRs.eof
SaveBinaryData oRs.Fields("Document_FileName").Value, oRs.Fields
("Binary_Content").Value
oRs.MoveNext
wend

oRs.Close
oCn.Close
Set oRs = Nothing
Set oSt = Nothing
Set oCn = Nothing
wscript.echo("Work Complete")

Function SaveBinaryData(Filename,ByteArray,DirName)
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2

dim BinaryStream

Set BinaryStream = CreateObject("ADODB.Stream")

BinaryStream.Type = adTypeBinary
BinaryStream.Open
BinaryStream.Write ByteArray

[thefab...@gmail.com]

On Apr 9, 2:50 pm, "Richard Mueller [MVP]" <rlmueller-
nos...@ameritech.nospam.net> wrote:
> <thefabulou...@gmail.com> wrote in message

[thefab...@gmail.com]

> > --- Hide quoted text -
>
> - Show quoted text -

I'm sorry...I meant to say in the first paragraph that the SQLServer
folks deleted their Binary column, but then re-added it when I went to
talk to them. So the columns are back in place like they were to
begin with before this problem started appearing.

[Richard Mueller [MVP]]

<thefab...@gmail.com> wrote in message
news:1c869c4c-c2ac-451c...@v6g2000vbb.googlegroups.com...

>>... Snip.

Error:

New code:

option explicit

------------

Now we know the datatype in the field "Binary_Content" is correct and the
function works to write the values, at least most of the time. Instead the
same error is raised when we get to a particular row of the recordset. The
best theory (I can think of) is that the problem row has no value assigned
for that field. We should be able to check if the value is Null. If the
value is Null, we can skip the Write and SaveToFile methods, or even write
some text instead. For example, the following only writes to the file if
there is a value:

Function SaveBinaryData(Filename,ByteArray,DirName)
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2

dim BinaryStream
If (IsNull(ByteArray) = False) Then

Set BinaryStream = CreateObject("ADODB.Stream")

BinaryStream.Type = adTypeBinary
BinaryStream.Open
BinaryStream.Write ByteArray
BinaryStream.SaveToFile "e:\data\ABC\" & FileName,
adSaveCreateOverWrite

End If
End Function

[Richard Mueller [MVP]]

"Richard Mueller [MVP]" <rlmuelle...@ameritech.nospam.net> wrote in
message news:uQ7LyIXu...@TK2MSFTNGP06.phx.gbl...

For completeness I should mention that there is an alternative solution,
which might be necessary if my "theory" (that the field is Null in certain
rows) is not correct. That is to trap the error and possibly to report on it
and continue. The function could be coded as follows:
======

Function SaveBinaryData(Filename,ByteArray,DirName)
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2

dim BinaryStream
Set BinaryStream = CreateObject("ADODB.Stream")

BinaryStream.Type = adTypeBinary
BinaryStream.Open
' Trap error.
On Error Resume Next
BinaryStream.Write ByteArray
If (Err.Number <> 0) Then
' Error raised, report on the error.
Call MsgBox("Error with filename " & Filename _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Description: " & Err.Description, _
vbOKOnly + vbCritical, "Cannot write binary value")
On Error GoTo 0
Exit Function
End If
' Restore normal error handling.
On Error GoTo 0

BinaryStream.SaveToFile "e:\data\ABC\" & FileName,
adSaveCreateOverWrite
End Function

========
I include the Filename in the error message, to give some indication of
which row raised the error. You might instead be able to include the ID
value in the error message. For example:

Call MsgBox("Error with ID: " & oRs.Fields("ID").Value _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Description: " & Err.Description, _
vbOKOnly + vbCritical, "Cannot write binary value")

Since oRs is Set in the main program, it is globally available. If the field
name is not "ID" you can use oRs(0).Value. If there are too many bad rows,
you can get too many error messages. In that case, either skip the step that
displays the error message, or instead write the error message to the file.
To write text to the file use:

Const adTypeText = 2

BinaryStream.Type = adTypeText
BinaryStream.Open
BinaryStream.Write "Error with ID:" & oRs.Fields("ID").Value _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Description: " & Err.Description

[thefab...@gmail.com]

On Apr 9, 8:49 pm, "Richard Mueller [MVP]" <rlmueller-
nos...@ameritech.nospam.net> wrote:
> "Richard Mueller [MVP]" <rlmueller-nos...@ameritech.nospam.net> wrote in
> messagenews:uQ7LyIXu...@TK2MSFTNGP06.phx.gbl...
>
>
>
>
>
>
>
> > <thefabulou...@gmail.com> wrote in message

> > Hilltop Lab -http://www.rlmueller.net

> Hilltop Lab -http://www.rlmueller.net
> --- Hide quoted text -
>
> - Show quoted text -

Richard,
Thank you for the tips on NULL. That fixed my problem and I can now
pull all the data I need to pull!!! Here's my final working script:

option explicit

oCn.Open

Function SaveBinaryData(Filename,ByteArray,DirName)

BinaryStream.Type = adTypeBinary
BinaryStream.Open

'Check to see if there is any data in the ByteStream before
attempting to write it.
'If there is no data, it means that the file is empty
If Not IsNull(ByteArray) Then
BinaryStream.Write ByteArray
End If