Read large binary data from Sql Server 2005

Hi

I've followed a tutorial on how to write and read varbinary(max) data to and from a database. But when i try to read the data i get the error that the data would be truncated, but only when the varbinary(max) is greater then 8kB. I've used a system stored procedure (sp_tableoption) to set the table that holds the data to store data outside rows. To select the data i'm using a stored procedure:

SELECT imageData , MIMETypeFROMPicturesWHERE(imageTitle = @.imageTitle)

And then using an .aspx page to Response.Write the data:

Using conn As New sql.SqlConnection
conn.ConnectionString = ConfigurationManager.ConnectionStrings("myConnectionString").ToString

Dim getLogoCommand As New sql.SqlCommand
getLogoCommand.CommandType = Data.CommandType.StoredProcedure
getLogoCommand.CommandText = "GetPicture"
getLogoCommand.Connection = conn

Dim imageTitleParameter As New sql.SqlParameter("@.imageTitle", Data.SqlDbType.NVarChar, 200)
imageTitleParameter.Value = Request("imageTitle")
imageTitleParameter.Direction = Data.ParameterDirection.Input

getLogoCommand.Parameters.Add(imageTitleParameter)

conn.Open()

Using logoReader As sql.SqlDataReader = getLogoCommand.ExecuteReader
logoReader.Read()
If logoReader.HasRows = True Then
Response.Clear()
Response.ContentType = logoReader("MIMEtype").ToString()
Response.BinaryWrite(logoReader("imageData"))
End If

End Using

conn.Close()

End Using

Can anyone please help me with this?!

Even though you are using SQL Server 2005... the "compatibility mode" is set to "80" (meaning 8.0... meaning SQL Server 2000).

So, until you run "EXEC sp_dbcmptlevel 90"... your MAX will == 8000 :) (you'll only have to run this once btw)

|||

Thanks for your reply! I can see <binary data> when I'm just watching the table in SQL Server Studio Manager but still not through a stored procedure. I, however, can get the data with:

SELECT * FROM Pictures WHERE (imageTitle = @.imagetitle ) as a "text" command on the .aspx page.

So like that i'm able to actually show anything bigger then 8 kB on the page, but now I can't do anything with the stored procedure like, when there is no data for that imageTitle I can't make it return another value.

When I use

---

conn.Open()

Using pictureReader As sql.SqlDataReader = getLogoCommand.ExecuteReader
pictureReader.Read()
If pictureReader.HasRows = True Then
Response.Clear()
Response.ContentType = pictureReader("MIMEtype").ToString()
Response.BinaryWrite(pictureReader("imageData"))
End If

End Using

---

The pictureReader.HasRows = True even when there is no binary data. Is there anyway to get the page not to do anything when there is no data in a row?

Thanks