• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1059
  • Last Modified:

I am getting an error of "microsoft odbc sql server driver string data right truncation


I am getting an error of "microsoft odbc sql server driver string data right truncation" when attempting to execute the following function.  It occurrs on the .execute line of the code.  strUSI is a very long string and the code was originally failing on
Set prm = .CreateParameter("pUSI", advarchar, adParamInput, 8000, strUSI)

I then changed it to
Set prm = .CreateParameter("pUSI", adBSTR, adParamInput, 8000, strUSI).  It is now failing on the .execute line.  I am executing this code from an access 2007 front-end.  The back end is a ms sql 2005 server

Private Function ExecuteRadarStoredProcedure(strUSI As String, strReviewType As String, intStatus As Integer, strProcName As String)
Dim cmd As ADODB.Command
Dim prm  As ADODB.Parameter
Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
'Set cnn.ConnectionString = cStrConnectionString
cnn.Open (cStrConnectionString)

Debug.Print strUSI

Set cmd = New ADODB.Command

cmd.CommandTimeout = 0

Set cmd.ActiveConnection = cnn

With cmd
    Set prm = .CreateParameter("pUSI", adBSTR, adParamInput, 8000, strUSI)
    .Parameters.Append prm
    Set prm = .CreateParameter("pReviewType", adVarChar, adParamInput, 8000, strReviewType)
    .Parameters.Append prm
    Set prm = .CreateParameter("pStatus", adInteger, adParamInput, , intStatus)
    .Parameters.Append prm
    .CommandType = adCmdStoredProc
    .CommandText = strProcName
    .Parameters.Delete ("pUSI")
    .Parameters.Delete ("pReviewType")
    .Parameters.Delete ("pStatus")
End With
Set cnn = Nothing
Set cmd = Nothing

End Function
2 Solutions
Paul JacksonSoftware EngineerCommented:
How is pUSI defined in your stored procedure?
Brendt HessSenior DBACommented:
Generically, this indicates that strUSI contains more than 8,000 characters when the data is appended.  You can test this simply by adding a trimming function (say, LEFT(strUSI, 8000)) when adding the value to the parameter.

I have also seen where leaving the provider name off of a connection string can result in this behavior.  Try adding PROVIDER=SQLOLEDB; or PROVIDER=SQLCLNI; to your connection string if it does not exist.
chtullu135Author Commented:
Thanks everyone,

I did some additional research based on the input I received and from other posts.  I went to the stored procedure and changed the input parameter varchar(max) to nvarchar(max) .  I still had some problems and after reading the following blog post
after first reading the comment by bhess1, I changed the connection string from
Public Const cStrConnectionString As String = "Driver={SQL Server};Server=xxx2;Database=Aithex;Uid=xxxx;Pwd=xxxxxx>" to the following

Public Const cStrOLEDBConnectionString As String = "Provider=SQLOLEDB;Data Source=xxx2;Initial Catalog=Aithex;Uid=xxx;Pwd=xxxx>"

Basically I changed the provider from SQL Server to SQLOLEDB

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now