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

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
Juan VelasquezAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul JacksonSoftware EngineerCommented:
How is pUSI defined in your stored procedure?
0
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Juan VelasquezAuthor 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
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.