Vista - BinaryStream.Write ByteArray erroring with Code 800A0BB9, SourceADODB.Stream

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

<thefabulouslb@xxxxxx> wrote in message
news:12a8d0e6-8559-43a1-936e-bd7faa2c1661@xxxxxxI 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
--

On Apr 8, 9:56*am, "Richard Mueller [MVP]" <rlmueller-
nos...@xxxxxx> wrote: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.

<thefabulouslb@xxxxxx> wrote in message
news:79971d8f-0eb5-4d1d-8cb8-b5d30c52b5eb@xxxxxx
On Apr 8, 9:56 am, "Richard Mueller [MVP]" <rlmueller-
nos...@xxxxxx> wrote: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.

-----------

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.

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

On Apr 8, 6:12*pm, "Richard Mueller [MVP]" <rlmueller-
nos...@xxxxxx> wrote: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

<thefabulouslb@xxxxxx> wrote in message
news:60c334d9-fcb7-4efa-b754-9b6de5222c71@xxxxxx
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.

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

On Apr 8, 6:12*pm, "Richard Mueller [MVP]" <rlmueller-
nos...@xxxxxx> wrote: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
BinaryStream.SaveToFile "e:\data\ABC\" & FileName,
adSaveCreateOverWrite
End Function

On Apr 9, 2:50*pm, "Richard Mueller [MVP]" <rlmueller-
nos...@xxxxxx> wrote:

On Apr 9, 4:43*pm, thefabulou...@xxxxxx wrote: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.

<thefabulouslb@xxxxxx> wrote in message
news:1c869c4c-c2ac-451c-976f-d4bc17e7f899@xxxxxx
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
BinaryStream.SaveToFile "e:\data\ABC\" & FileName,
adSaveCreateOverWrite
End Function

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

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 Directory Services
Hilltop Lab - http://www.rlmueller.net
--