SQL Server Performance

The buffer is insufficient

Discussion in 'SQL Server 2005 CLR' started by stathis30_2000, Aug 24, 2007.

  1. stathis30_2000 New Member

    Hi all,
    I have a strange problem that I need to solve as soon as possible.I have created two CLR UDTs called point and point_list. Each record of a point_list consists of a list of points. I created a CLR stored procedure which reads some raw data and updates the point_list records.When I execute the stored procedure the following error appears :System.Data.SqlTypes.SqlTypeException: The buffer is insufficient. Read or write operation failed.System.Data.SqlTypes.SqlTypeException:at System.Data.SqlTypes.SqlBytes.Write(Int64 offset, Byte[] buffer, Int32 offsetInBuffer, Int32 count)at System.Data.SqlTypes.StreamOnSqlBytes.Write(Byte[] buffer, Int32 offset, Int32 count)at System.IO.BinaryWriter.Write(Char ch) etc ...
    Does anybody know what should I do ?I read that the max byte size of a utd record is 8000 bytes, is that true? Can I change that? I was also told to use VARCHAR(MAX) instead of my udt.. I need some more information on that.
    Thanks!
  2. satya Moderator

    YourPost on other forums, why not simply read updated BOL in this case.
  3. stathis30_2000 New Member

    What do you mean with the word BOL ? I prefer to write to different forums and collect opinions..
    Thanks
  4. satya Moderator

    Ok, Its Books Online which is vastly used in the forums.#
    A suggested why not paste the TSQL here to get more suggestion from our TSQL experts.
  5. stathis30_2000 New Member

    I am using CLR for my stored procedure. First I read some raw data (trajectory points) and then I update the trajectory records. I use two UDTs, trajectory and point. The code is:
    Using conn As New SqlConnection("context connection=true")

    conn.Open()

    'Variable declatarion
    Dim trajid As Integer 'new point record - trajectory id

    Dim gender As Integer
    Dim prevgender As Integer

    Dim prevtrajid As Integer = -1
    Dim trajectory As Trajectory

    'create sql commands
    Dim getNewPointsComm As New SqlCommand()
    getNewPointsComm.CommandText = "select * from " & pointsTable & " where CHECKED='0' order by TRAJ_ID, TIMESTAMP"
    getNewPointsComm.CommandType = CommandType.Text
    getNewPointsComm.Connection = conn

    Dim getCorrespTraj As New SqlCommand()
    getCorrespTraj.Connection = conn
    getCorrespTraj.CommandType = CommandType.Text

    Dim updateTrajectory As New SqlCommand
    updateTrajectory.Connection = conn
    updateTrajectory.CommandType = CommandType.Text

    Dim getRect As New SqlCommand()
    getRect.Connection = conn

    '------------------------------------

    Dim dsPoints As New DataSet()
    Dim dsTrajectories As New DataSet()
    Dim dsRectangle As New DataSet()

    Dim Adapter As New SqlDataAdapter("get_data", conn)
    Adapter.SelectCommand = getNewPointsComm
    Adapter.Fill(dsPoints, "points")

    'End of SQL commands


    Dim new_traj As Boolean = False
    Dim count As Integer = 0

    For Each point As DataRow In dsPoints.Tables("points").Rows

    Dim point_obj As New Point
    trajid = point("TRAJ_ID")


    point_obj.X = point("EASTINGS_X")
    point_obj.Y = point("NORTHINGS_Y")
    point_obj.D = point("TIMESTAMP")

    gender = point("GENDER_ID")

    If trajid <> prevtrajid Then

    count = 0
    If prevtrajid <> -1 Then

    If new_traj Then
    updateTrajectory.CommandText = "insert into trajectories (TRAJ_OBJ,GENDER_ID,TRAJ_OBJ_ID) values ('" & trajectory.ToString & "','" & Convert.ToString(prevgender) & "','" & prevtrajid & "')"
    Else
    updateTrajectory.CommandText = "update " & trajTable & " set TRAJ_OBJ='" & trajectory.ToString & "' where TRAJ_OBJ_ID='" & prevtrajid & "'"
    End If

    updateTrajectory.ExecuteNonQuery()

    End If

    prevtrajid = trajid
    prevgender = gender

    getCorrespTraj.CommandText = "select * from trajectories where TRAJ_OBJ_ID ='" & trajid & "'"
    Adapter.SelectCommand = getCorrespTraj
    Adapter.Fill(dsTrajectories, "trajectories")

    'SqlContext.Pipe.Send("diff trajid")

    For Each traj As DataRow In dsTrajectories.Tables("trajectories").Rows
    trajectory = traj("TRAJ_OBJ")
    ' SqlContext.Pipe.Send("in here ...")
    Next

    If dsTrajectories.Tables("trajectories").Rows.Count = 0 Then
    trajectory = New Trajectory
    new_traj = True
    'SqlContext.Pipe.Send("in here too ...")
    End If

    count = count + 1
    dsTrajectories.Reset()
    End If


    Try
    trajectory.AddPoint(point_obj)
    Catch ex As Exception
    SqlContext.Pipe.Send(Convert.ToString(count))
    End Try





    Next



    conn.Close()

    End Using

  6. Adriaan New Member

    Looks suspicially like VB. Note that VB's integer type is not the same as T-SQL's integer type - it maxes out at 32 thousand something.

Share This Page