Update of Image data Fails for Large Files

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update of Image data Fails for Large Files

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-09 : 08:14:19

Diane writes "Error:
error # -2147217887 was generated by Microsoft Cursor Engine
Multiple-step operation generated errors. Check each status value.

I am getting this error when I try to load a 300Meg file. I've used the following code successfully on smaller files,(67meg).
Any help would be appreciated.
My VB Code:
sSQL = "SELECT * FROM reports WHERE client_id = '" & sReportClientId & "' " & _
"AND report_name = '" & msReportName & "' " & _
"AND report_date_time = '" & Format$(Now, "mm/dd/yyyy") & "'"

Set rsModules = Nothing

If oSQLAdo.ExecSQLCommand(sSQL, rsModules, , , False) = False Then
msStatus = msStatus & vbCrLf & " Error fromReports.CopyReport Delete process" & oSQLAdo.Error
Exit Function
End If

If rsModules.EOF = True Then
rsModules.AddNew
End If

lFileSize = FileLen(msFileName)
iFile = FreeFile
If lFileSize > lChunkSize Then
ReDim ArrFile(1 To lChunkSize) As Byte
lChunks = lFileSize \ lChunkSize
lFragment = lFileSize Mod lChunkSize
Else
ReDim ArrFile(1 To lFileSize) As Byte
lChunks = 1
lFragment = 0
End If
Close #iFile
Open msFileName For Binary Access Read As #iFile
For lCtr = 1 To lChunks
Get #iFile, , ArrFile
rsModules("binary_tx").AppendChunk ArrFile
Next lCtr
If lFragment > 0 Then
ReDim ArrFile(1 To lFragment) As Byte
Get #iFile, , ArrFile
rsModules("binary_tx").AppendChunk ArrFile
End If
Close #iFile

rsModules("client_id").Value = sReportClientId
rsModules("report_date_time").Value = Format$(Now, "mm/dd/yyyy")
rsModules("report_name").Value = msReportName
rsModules("file_name").Value = Mid$(msFileName, iHoldPos + 1)
rsModules("file_size").Value = CStr(lFileSize)
rsModules.UPDATE

Returns Error:
error # -2147217887 was generated by Microsoft Cursor Engine
Multiple-step operation generated errors. Check each status value."

Kristen
Test

22859 Posts

Posted - 2005-03-10 : 00:02:16

I don't think I know the answer, but I have a few thoughts.

I would change the SELECT * to explicitly define the columns you need, AND put the IMAGE column as the last one in the list (there have been issues with ODBC handling large column data which were solved by having the large column at the end)

Your error message ("Error fromReports.CopyReport Delete process") looks a little misleading, or I'm confused - is this a Delete or an Add/Modify?

I haven't attempted to do something like this before, but I'm surprised that you can pump a SELECT ... into rsModules and then do a .Add or a .Update on it - but maybe that's how this works in VB. I would have expected some sort of UPDATE or INSERT SQL command instead.

I would look at the properties on oSQLAdo and see if there are any Cursor-type or Size restrictions - the defaults may not be enough for what you are trying to do.

And I suppose lastly, do you have to store this file in the database? Can you store it in the File system on the server and store a Path+Filename in the database instead?

The following are for ASP, but might give you some debugging data ideas


With MyDatabaseConnection
	Response.Write("<tr><td>Version</td><td>" & .Version & "</td></tr>")
	Response.Write("<tr><td>Timeout</td><td>" & .CommandTimeout & "</td></tr>")
	Response.Write("<tr><td>Connection String</td><td>" & .ConnectionString & "</td></tr>")
	Response.Write("<tr><td>Connection Timeout</td><td>" & .ConnectionTimeout & "</td></tr>")
	Response.Write("<tr><td>Cursor Location</td><td>" & .CursorLocation & "</td></tr>")
	Response.Write("<tr><td>Default Database</td><td>" & .DefaultDatabase & "</td></tr>")
	Response.Write("<tr><td>Isolation Level</td><td>" & .IsolationLevel & "</td></tr>")
	Response.Write("<tr><td>Mode</td><td>" & .Mode & "</td></tr>")
	Response.Write("<tr><td>Provider</td><td>" & .Provider & "</td></tr>")
	Response.Write("<tr><td>State</td><td>" & .State & "</td></tr>")
End With
Response.Write rkADOCommandPropertyDump(MyDatabaseConnection)

Function rkADOCommandPropertyDump(objCmd)
Dim objProperty
Dim strContent

	strContent = "<TABLE><tr><th>Name</th><th>Value</th></tr>"
	For Each objProperty In objCmd.Properties
		strContent = strContent & "<tr><td>" & objProperty.name _
				& "</td><td>" & objProperty.Value & "</td></tr>"
	Next
	strContent = strContent & "</table>"
	rkADOCommandPropertyDump = strContent
End Function

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources