?
Solved

ntext truncated using ado.net

Posted on 2006-03-20
2
Medium Priority
?
866 Views
Last Modified: 2008-01-09
hello experts,
i was told the following by my DBA:

"Dennis, in setting up the DB on the production server I noticed a potential issue. The ‘Checks’ table is currently designed too big. That is, the max row size is over 8060 bytes (actually almost double).
Looking into the table structure you have a BLOB field that is a varchar 8000. Is there any way you can either use a binary field for this function, or move the BLOB field into a related table so we don’t blow out the size constraints? Inserts/Updates will fail if the row size exceeds 8060 bytes as that is the max page size in SQL."

i have setup the BLOB field as ntext 16 and modified my insert and select stored procedures to include the new datatype for the BLOB field.  i have insert and get methods that call the stored procedures.  the problem is the BLOB now field defined as ntext 16 gets truncated when data is inserted into the table.  i tried setting the textsize to a big number in each stored procedure with no success.  below is my code.  any help is greatly appreciated.

Stored Procedures
============
CREATE PROCEDURE [dbo].[VBA1351_00_AddNewCheck]
     @TRANSACTION_ID          VARCHAR(255),    
     @AT_V                    VARCHAR(200),
     @CLEBO_REGIONID             VARCHAR(200),
     @CLEBO_OPERINIT              VARCHAR(200),
     @CLEBO_BATCHNO               VARCHAR(200),
     @CLEBO_TRANSDT               VARCHAR(200),
     @CLEBO_TRANSTM          VARCHAR(200),
     @CLSA_ACCTNO                 VARCHAR(200),
     @CLSA_NAME                   VARCHAR(200),
     @Y_BOTH                      VARCHAR(200),
     @CLSA_FAXNO                  VARCHAR(200),
     @CLSA_EMAIL                  VARCHAR(200),
     @Y_PAYEE                     VARCHAR(200),
     @V_FAXNO                     VARCHAR(200),
     @V_EMAIL                     VARCHAR(200),
     @CLSB_NAME                   VARCHAR(200),
     @CLSB_ADDRESS1               VARCHAR(200),
     @CLSB_ADDRESS2               VARCHAR(200),
     @CLSB_CITY                   VARCHAR(200),
     @CLSB_STATE                  VARCHAR(200),
     @CLSB_ZIPCD                 VARCHAR(200),
     @CLSB_ABAROUTENO1       VARCHAR(200),
     @CLSR_BANKACCT               VARCHAR(200),
     @AT_C                        VARCHAR(200),
     @CLETC_DATE                  VARCHAR(200),
     @CHECKNO                     VARCHAR(200),
     @CLET_TOTAMT                 VARCHAR(200),
     @V_WORDAMT                   VARCHAR(200),
     @V_NAME                      VARCHAR(200),
     @V_ADDRESS1                  VARCHAR(200),
     @V_ADDRESS2                  VARCHAR(200),
     @V_CITY                      VARCHAR(200),
     @V_STATE                     VARCHAR(200),
     @V_ZIPCD                     VARCHAR(200),
     @CLSB_ABAROUTENO2       VARCHAR(200),
     @CLETC_PAYBANKACCT      VARCHAR(200),
     @BLOB                    NTEXT,
     @TYPE                    VARCHAR(50)
AS
     SET TEXTSIZE 64512
     INSERT INTO CHECKS
     (
          TRANSACTION_ID,    
          AT_V,
          CLEBO_REGIONID,
          CLEBO_OPERINIT,    
          CLEBO_BATCHNO,    
          CLEBO_TRANSDT,    
          CLEBO_TRANSTM,    
          CLSA_ACCTNO,      
          CLSA_NAME,        
          Y_BOTH,            
          CLSA_FAXNO,        
          CLSA_EMAIL,        
          Y_PAYEE,          
          V_FAXNO,          
          V_EMAIL,          
          CLSB_NAME,        
          CLSB_ADDRESS1,    
          CLSB_ADDRESS2,    
          CLSB_CITY,        
          CLSB_STATE,        
          CLSB_ZIPCD,        
          CLSB_ABAROUTENO1,  
          CLSR_BANKACCT,          
          AT_C,              
          CLETC_DATE,        
          CHECKNO,          
          CLET_TOTAMT,      
          V_WORDAMT,        
          V_NAME,            
          V_ADDRESS1,        
          V_ADDRESS2,        
          V_CITY,            
          V_STATE,          
          V_ZIPCD,          
          CLSB_ABAROUTENO2,  
          CLETC_PAYBANKACCT,
          BLOB,
          TYPE
     )
     VALUES
     (
          @TRANSACTION_ID,
          @AT_V,              
          @CLEBO_REGIONID,    
          @CLEBO_OPERINIT,    
          @CLEBO_BATCHNO,    
          @CLEBO_TRANSDT,    
          @CLEBO_TRANSTM,    
          @CLSA_ACCTNO,      
          @CLSA_NAME,        
          @Y_BOTH,            
          @CLSA_FAXNO,        
          @CLSA_EMAIL,        
          @Y_PAYEE,          
          @V_FAXNO,          
          @V_EMAIL,          
          @CLSB_NAME,        
          @CLSB_ADDRESS1,    
          @CLSB_ADDRESS2,    
          @CLSB_CITY,        
          @CLSB_STATE,        
          @CLSB_ZIPCD,        
          @CLSB_ABAROUTENO1,  
          @CLSR_BANKACCT,          
          @AT_C,              
          @CLETC_DATE,        
          @CHECKNO,          
          @CLET_TOTAMT,      
          @V_WORDAMT,        
          @V_NAME,            
          @V_ADDRESS1,        
          @V_ADDRESS2,        
          @V_CITY,            
          @V_STATE,          
          @V_ZIPCD,          
          @CLSB_ABAROUTENO2,  
          @CLETC_PAYBANKACCT,
          @BLOB,
          @TYPE
     )
GO
============
CREATE PROCEDURE [dbo].[VBA1351_00_AddNewLoan]
     @TRANSACTION_ID          VARCHAR(255),    
     @LOAN_NUMBER                VARCHAR(200),
     @LOAN_DESCRIPTION          VARCHAR(200),
     @LOAN_AMOUNT          VARCHAR(200)
AS
     INSERT INTO LOANS
     (
          TRANSACTION_ID,
          LOAN_NUMBER,
          LOAN_DESCRIPTION,
          LOAN_AMOUNT
     )
     VALUES
     (
          @TRANSACTION_ID,
          @LOAN_NUMBER,
          @LOAN_DESCRIPTION,
          @LOAN_AMOUNT
     )
GO

============
CREATE PROCEDURE [dbo].[VBA1351_00_GetCheckBlob]
     @TRANSACTION_ID VARCHAR(255)
AS
     SET TEXTSIZE 64512
     SELECT BLOB
     FROM CHECKS
     WHERE TRANSACTION_ID = @TRANSACTION_ID
     RETURN
GO



VB.NET Code
========
  Public Sub ExportCheckCollection(ByVal oCheckIds As Collection)
    Dim oCmd As New SqlCommand
    Dim oWrt As StreamWriter = Nothing
    Dim oObj As Object

    With oCmd
      Try
        .Connection = New SqlConnection(SystemServices.DbConnectionString)
        .Connection.Open()
        .CommandType = CommandType.StoredProcedure
        oWrt = New StreamWriter(New FileStream(SystemServices.ExportPath + "\" + _
                                               Format(Now, "hhmmssffmmddyyyy") + SystemServices.EXPORT_FILE_EXT, _
                                               FileMode.CreateNew, _
                                               FileAccess.Write))

        For Each sCheckId As String In oCheckIds
          .CommandText = "VBA1351_00_GetCheckBlob"

          With .Parameters
            .Clear()
            .Add("@TRANSACTION_ID", SqlDbType.VarChar, 255)
            .Item("@TRANSACTION_ID").Value = sCheckId
          End With

          oObj = .ExecuteScalar

          If Not IsDBNull(oObj) Then
            oWrt.WriteLine(oObj)
          End If

        Next sCheckId

      Catch ex As SqlException
        ex.Source = "DatabaseServices.ExportCheckCollection"
        Throw ex

      Finally

        If .Connection.State <> ConnectionState.Closed Then
          .Connection.Close()
          .Connection.Dispose()
        End If

        If Not oWrt Is Nothing Then
          oWrt.Close()
          oWrt.Dispose()
        End If

        .Dispose()
      End Try
    End With

  End Sub
===============
  Public Sub ImportCheckCollection(ByRef oChecks As Collection)
    Dim oCmd As New SqlCommand
    Dim sTransactionId As String = Nothing

    With oCmd
      Try
        .Connection = New SqlConnection(SystemServices.DbConnectionString)
        .Connection.Open()
        .CommandType = CommandType.StoredProcedure

        '// Loop through each check and insert its lines
        For Each oCheck As Check In oChecks
          .CommandText = "VBA1351_00_AddNewCheck"

          With .Parameters
            .Clear()
            .Add("@TRANSACTION_ID", SqlDbType.VarChar, 255)
            .Add("@AT_V", SqlDbType.VarChar, 200)
            .Add("@CLEBO_REGIONID", SqlDbType.VarChar, 200)
            .Add("@CLEBO_OPERINIT", SqlDbType.VarChar, 200)
            .Add("@CLEBO_BATCHNO", SqlDbType.VarChar, 200)
            .Add("@CLEBO_TRANSDT", SqlDbType.VarChar, 200)
            .Add("@CLEBO_TRANSTM", SqlDbType.VarChar, 200)
            .Add("@CLSA_ACCTNO", SqlDbType.VarChar, 200)
            .Add("@CLSA_NAME", SqlDbType.VarChar, 200)
            .Add("@Y_BOTH", SqlDbType.VarChar, 200)
            .Add("@CLSA_FAXNO", SqlDbType.VarChar, 200)
            .Add("@CLSA_EMAIL", SqlDbType.VarChar, 200)
            .Add("@Y_PAYEE", SqlDbType.VarChar, 200)
            .Add("@V_FAXNO", SqlDbType.VarChar, 200)
            .Add("@V_EMAIL", SqlDbType.VarChar, 200)
            .Add("@CLSB_NAME", SqlDbType.VarChar, 200)
            .Add("@CLSB_ADDRESS1", SqlDbType.VarChar, 200)
            .Add("@CLSB_ADDRESS2", SqlDbType.VarChar, 200)
            .Add("@CLSB_CITY", SqlDbType.VarChar, 200)
            .Add("@CLSB_STATE", SqlDbType.VarChar, 200)
            .Add("@CLSB_ZIPCD", SqlDbType.VarChar, 200)
            .Add("@CLSB_ABAROUTENO1", SqlDbType.VarChar, 200)
            .Add("@CLSR_BANKACCT", SqlDbType.VarChar, 200)
            .Add("@AT_C", SqlDbType.VarChar, 200)
            .Add("@CLETC_DATE", SqlDbType.VarChar, 200)
            .Add("@CHECKNO", SqlDbType.VarChar, 200)
            .Add("@CLET_TOTAMT", SqlDbType.VarChar, 200)
            .Add("@V_WORDAMT", SqlDbType.VarChar, 200)
            .Add("@V_NAME", SqlDbType.VarChar, 200)
            .Add("@V_ADDRESS1", SqlDbType.VarChar, 200)
            .Add("@V_ADDRESS2", SqlDbType.VarChar, 200)
            .Add("@V_CITY", SqlDbType.VarChar, 200)
            .Add("@V_STATE", SqlDbType.VarChar, 200)
            .Add("@V_ZIPCD", SqlDbType.VarChar, 200)
            .Add("@CLSB_ABAROUTENO2", SqlDbType.VarChar, 200)
            .Add("@CLETC_PAYBANKACCT", SqlDbType.VarChar, 200)
            .Add("@BLOB", SqlDbType.NText, 16)
            .Add("@TYPE", SqlDbType.VarChar, 50)
          End With

          sTransactionId = DatabaseServices.GetNewTransactionId()
          .Parameters("@TRANSACTION_ID").Value = sTransactionId
          .Parameters("@AT_V").Value = oCheck.GetField(Check.eCheckFields.at_V)
          .Parameters("@CLEBO_REGIONID").Value = oCheck.GetField(Check.eCheckFields.clebo_regionid)
          .Parameters("@CLEBO_OPERINIT").Value = oCheck.GetField(Check.eCheckFields.clebo_operinit)
          .Parameters("@CLEBO_BATCHNO").Value = oCheck.GetField(Check.eCheckFields.clebo_batchno)
          .Parameters("@CLEBO_TRANSDT").Value = oCheck.GetField(Check.eCheckFields.clebo_transdt)
          .Parameters("@CLEBO_TRANSTM").Value = oCheck.GetField(Check.eCheckFields.clebo_transtm)
          .Parameters("@CLSA_ACCTNO").Value = oCheck.GetField(Check.eCheckFields.clsa_acctno)
          .Parameters("@CLSA_NAME").Value = oCheck.GetField(Check.eCheckFields.clsa_name)
          .Parameters("@Y_BOTH").Value = oCheck.GetField(Check.eCheckFields.y_both)
          .Parameters("@CLSA_FAXNO").Value = oCheck.GetField(Check.eCheckFields.clsa_faxno)
          .Parameters("@CLSA_EMAIL").Value = oCheck.GetField(Check.eCheckFields.clsa_email)
          .Parameters("@Y_PAYEE").Value = oCheck.GetField(Check.eCheckFields.y_payee)
          .Parameters("@V_FAXNO").Value = oCheck.GetField(Check.eCheckFields.v_faxno)
          .Parameters("@V_EMAIL").Value = oCheck.GetField(Check.eCheckFields.v_email)
          .Parameters("@CLSB_NAME").Value = oCheck.GetField(Check.eCheckFields.clsb_name)
          .Parameters("@CLSB_ADDRESS1").Value = oCheck.GetField(Check.eCheckFields.clsb_address1)
          .Parameters("@CLSB_ADDRESS2").Value = oCheck.GetField(Check.eCheckFields.clsb_address2)
          .Parameters("@CLSB_CITY").Value = oCheck.GetField(Check.eCheckFields.clsb_city)
          .Parameters("@CLSB_STATE").Value = oCheck.GetField(Check.eCheckFields.clsb_state)
          .Parameters("@CLSB_ZIPCD").Value = oCheck.GetField(Check.eCheckFields.clsb_zipcd)
          .Parameters("@CLSB_ABAROUTENO1").Value = oCheck.GetField(Check.eCheckFields.clsb_abarouteno1)
          .Parameters("@CLSR_BANKACCT").Value = oCheck.GetField(Check.eCheckFields.clsr_bankacct)
          .Parameters("@AT_C").Value = oCheck.GetField(Check.eCheckFields.at_C)
          .Parameters("@CLETC_DATE").Value = oCheck.GetField(Check.eCheckFields.cletc_date)
          .Parameters("@CHECKNO").Value = oCheck.GetField(Check.eCheckFields.checkno)
          .Parameters("@CLET_TOTAMT").Value = oCheck.GetField(Check.eCheckFields.clet_totamt)
          .Parameters("@V_WORDAMT").Value = oCheck.GetField(Check.eCheckFields.v_wordamt)
          .Parameters("@V_NAME").Value = oCheck.GetField(Check.eCheckFields.v_name)
          .Parameters("@V_ADDRESS1").Value = oCheck.GetField(Check.eCheckFields.v_address1)
          .Parameters("@V_ADDRESS2").Value = oCheck.GetField(Check.eCheckFields.v_address2)
          .Parameters("@V_CITY").Value = oCheck.GetField(Check.eCheckFields.v_city)
          .Parameters("@V_STATE").Value = oCheck.GetField(Check.eCheckFields.v_state)
          .Parameters("@V_ZIPCD").Value = oCheck.GetField(Check.eCheckFields.v_zipcd)
          .Parameters("@CLSB_ABAROUTENO2").Value = oCheck.GetField(Check.eCheckFields.clsb_abarouteno2)
          .Parameters("@CLETC_PAYBANKACCT").Value = oCheck.GetField(Check.eCheckFields.cletc_paybankacct)
          .Parameters("@BLOB").Value = oCheck.Blob
          .Parameters("@TYPE").Value = oCheck.Type
          .ExecuteNonQuery()

          '// Import any loans that exist on the check
          If oCheck.GetLoanCount > 0 Then
            .CommandText = "VBA1351_00_AddNewLoan"

            With .Parameters
              .Clear()
              .Add("@TRANSACTION_ID", SqlDbType.VarChar, 255)
              .Add("@LOAN_NUMBER", SqlDbType.VarChar, 200)
              .Add("@LOAN_DESCRIPTION", SqlDbType.VarChar, 200)
              .Add("@LOAN_AMOUNT", SqlDbType.VarChar, 200)
            End With

            oCheck.ResetLoanPointer()

            Do While oCheck.MoveNextLoan
              .Parameters("@TRANSACTION_ID").Value = _
                sTransactionId
              .Parameters("@LOAN_NUMBER").Value = _
                oCheck.GetLoanField(Check.eLoanFields.eLoanNumber)
              .Parameters("@LOAN_DESCRIPTION").Value = _
                oCheck.GetLoanField(Check.eLoanFields.eLoanDescription)
              .Parameters("@LOAN_AMOUNT").Value = _
                oCheck.GetLoanField(Check.eLoanFields.eLoanAmount)
              .ExecuteNonQuery()
            Loop

          End If

        Next oCheck

      Catch ex As SqlException
        ex.Source = "DatabaseServices.ImportCheckCollection"
        Throw ex

      Finally

        If .Connection.State <> ConnectionState.Closed Then
          .Connection.Close()
          .Connection.Dispose()
        End If

        .Dispose()
      End Try

    End With '// With oCmd

  End Sub
0
Comment
Question by:comppgmr
2 Comments
 
LVL 7

Accepted Solution

by:
jaanth earned 2000 total points
ID: 16237680
It looks like in you VBA code where you setting the ochecks parameter to size to 16, which is going to trim the data on the way into the stored procedure. The 16 byte Ntext pointer may be stored in the table, but you still need to pass the stored procedure the entire blob.

But, I think what your DBA is getting at is to move the blob, out of the checks table into a separate table of just blobs, with  just a pointer to it. Since transactionID is your key, you can use that as pointer and drop the blob from Checks table.

so it would be something like

============
CREATE PROCEDURE [dbo].[VBA1351_00_GetCheckBlob]
     @TRANSACTION_ID VARCHAR(255)
AS
     SET TEXTSIZE 64512
     SELECT BLOB
     FROM CHECKBlobs
     WHERE TRANSACTION_ID = @TRANSACTION_ID
     RETURN
GO

============
CREATE PROCEDURE [dbo].[VBA1351_00_AddNewCheckBlob]
     @TRANSACTION_ID          VARCHAR(255),    
     @BLOB                    NTEXT
AS
     SET TEXTSIZE 64512
     INSERT INTO CHECKBlobs
     (
          TRANSACTION_ID,    
          BLOB
     )
     VALUES
     (
          @TRANSACTION_ID,
          @BLOB
     )
GO

--add to the vb code loop
         .CommandText = "VBA1351_00_AddNewCheckBlobs"

          With .Parameters
            .Clear()
            .Add("@TRANSACTION_ID", SqlDbType.VarChar, 255)
            .Add("@BLOB", SqlDbType.NText)
          End With

          sTransactionId = DatabaseServices.GetNewTransactionId()
          .Parameters("@TRANSACTION_ID").Value = sTransactionId
          .Parameters("@BLOB").Value = oCheck.Blob
          .ExecuteNonQuery()
0
 

Author Comment

by:comppgmr
ID: 16239570
thanks,
not passing the 16 to the stored procedure did the trick.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question