Solved

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

Posted on 2012-03-27
3
959 Views
Last Modified: 2012-03-28
Hello,

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
    .Execute
    .Parameters.Delete ("pUSI")
    .Parameters.Delete ("pReviewType")
    .Parameters.Delete ("pStatus")
End With
   
cnn.Close
Set cnn = Nothing
Set cmd = Nothing

End Function
0
Comment
Question by:chtullu135
3 Comments
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 50 total points
Comment Utility
How is pUSI defined in your stored procedure?
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 450 total points
Comment Utility
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.
0
 

Author Closing Comment

by:chtullu135
Comment Utility
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
http://databases.aspfaq.com/database/what-should-my-connection-string-look-like.html
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
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now