December 23, 2007 at 6:24 am
I have a CLR stored procedure (see spAddDocumentVersion below) that inserts a row into a table which contains a VARBINARY(MAX) field called documentImage.
I have another CLR stored procedure (see spGetDocument below) which reads the contents of the documentImage field.
If more than 8000 bytes are written to this field by spAddDocumentVersion then spGetDocument only returns 8000 bytes. I am using VS 2005 and sql server 2005 express.
I have used the VS2005 CLR debugging facility to check the number of bytes input to the documentImage parameter of spAddDocumentVersion, and the VS2005 VB debugger check the number of bytes read by spGetdocument.
Can anyone suggest why this is not working? I have attached samples from the code below.
Public Shared Sub spAddDocumentVersion(ByVal DocumentId As Integer, ByVal DocumentVersion As Decimal, _
ByVal DocumentStatus As String, ByVal DateWritten As Date, _
ByVal DocumentMediaType As String, ByVal DocumentAuthorId As Integer, _
ByVal DocumentImage() As Byte)
Dim conn As SqlConnection = New SqlConnection("context connection=true")
Dim cmd1 As SqlCommand
Dim sbSQL As StringBuilder = New StringBuilder
sbSQL.Append("Insert Into tblDocumentVersion ")
sbSQL.Append("([DocumentId], [DocumentVersion], [DocumentStatus], ")
sbSQL.Append("[DateWritten], [DocumentMediaType],[DocumentAuthorId], [DocumentImage])")
sbSQL.Append("VALUES (@DocumentId, @DocumentVersion, @DocumentStatus, @DateWritten, ")
sbSQL.Append("@DocumentMediaType, @DocumentAuthorId, @DocumentImage")
cmd1 = New SqlCommand(sbSQL.ToString, conn)
cmd1.Parameters.AddWithValue("@DocumentId", DocumentId)
cmd1.Parameters.AddWithValue("@DocumentVersion", DocumentVersion)
cmd1.Parameters.AddWithValue("@DocumentStatus", DocumentStatus)
cmd1.Parameters.AddWithValue("@DateWritten", DateWritten)
cmd1.Parameters.AddWithValue("@DocumentMediaType", DocumentMediaType)
cmd1.Parameters.AddWithValue("@DocumentAuthorId", DocumentAuthorId)
cmd1.Parameters.AddWithValue("@DocumentImage", DocumentImage)
conn.Open()
cmd1.ExecuteNonQuery()
conn.Close()
End Sub
Public Shared Sub spGetDocument(ByVal DocumentVersionId As Integer)
Dim conn As SqlConnection = New SqlConnection("context connection=true")
Dim cmd1 As SqlCommand
Dim sbSQL As StringBuilder = New StringBuilder
sbSQL.Append("select DocumentFileName, DocumentImage ")
sbSQL.Append("from tblDocumentVersion as DV, tblDocument as D ")
sbSQL.Append("where DocumentVersionId = @DocumentVersionId and D.DocumentId = DV.DocumentId")
cmd1 = New SqlCommand(sbSQL.ToString, conn)
cmd1.Parameters.AddWithValue("@DocumentVersionId", DocumentVersionId)
conn.Open()
SqlContext.Pipe.ExecuteAndSend(cmd1)
conn.Close()
End Sub
Public Sub Documents_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs)
If e.CommandName = "ViewDocument" Then
Dim DocumentVersionId = Convert.ToInt32(DocumentsGridView.DataKeys(Convert.ToInt32(e.CommandArgument)).Value)
Dim strFileName As String
Dim reader As SqlDataReader
Dim conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("DocumentsConnectionString").ConnectionString)
Dim cmd As New SqlCommand("spGetDocument", conn)
Dim image() As Byte
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@DocumentVersionId", DocumentVersionId)
conn.Open()
reader = cmd.ExecuteReader()
'Read document filename and contents
If reader.Read() Then
strFileName = reader("DocumentFileName")
image = reader("DocumentImage") 'This only returns 8000 bytes if the number of bytes > 8000
End If
December 23, 2007 at 10:25 am
I can't see anything obviously wrong with the code, but then I'm not a C# expert, so I may have missed something.
What I'm curious about is why you're using a CLR proc at all. There's nothing in there, that I can see, that can't be done with T-SQL.
Could you post the decorations for the 2 CLR procs and also the T-SQL definitions for them. I don't think it would have anything to do with them, but is worth checking.
If you run the 2nd from from a querying tool, do you get 8000 characters or do you get the whole field? If you run the following query, what do you get?
SELECT DocumentName, DATALENGTH(DocumentImage) FROM tblDocumentVersion
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 24, 2007 at 5:09 am
Thanks for your reply
I am using a CLR instead of T-SQL in order to evaluate new features available with sql server 2005. CLR's appear to enable all code to be maintained within a Visual studio project instead of having to separately load stored procedures into the database.
Your suggested query SELECT DocumentName, DATALENGTH(DocumentImage) FROM tblDocumentVersion returned a size of 8000 bytes, and looking directly at the stored procedure parameters for spAddDocumentVersion showed the documentimage parameter type to be varbinary(8000) instead of varbinary(max).
I have now found that if I change the parameter type for DocumentImage in the CLR stored procedure code from Byte to sqlbytes this generates a type of varbinary(max) in the actual stored procedure and fixes my problem.
December 26, 2007 at 11:17 pm
paul holroyd (12/24/2007)
Thanks for your replyI am using a CLR instead of T-SQL in order to evaluate new features available with sql server 2005. CLR's appear to enable all code to be maintained within a Visual studio project instead of having to separately load stored procedures into the database.
Sure. However, all the recommendations around the use of CLR are to use it for things that T-SQL is weak at (file access, registry access, web services, regex) and not for direct data access or manipulation. That should be left in T-SQL. T-SQL is optimised for data manipulation, VB/C# is not.
For procs in a projects (and sourcesafe), have a look at Data Dude (aka Visual Studio Team Edition for Database Professionals)
Your suggested query SELECT DocumentName, DATALENGTH(DocumentImage) FROM tblDocumentVersion returned a size of 8000 bytes, and looking directly at the stored procedure parameters for spAddDocumentVersion showed the documentimage parameter type to be varbinary(8000) instead of varbinary(max).
I have now found that if I change the parameter type for DocumentImage in the CLR stored procedure code from Byte to sqlbytes this generates a type of varbinary(max) in the actual stored procedure and fixes my problem.
So it was getting truncated upon insert into the table. Good to know about the param types.
Glad it's sorted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy