?
Solved

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

Posted on 2012-03-27
3
Medium Priority
?
1,010 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 200 total points
ID: 37772366
How is pUSI defined in your stored procedure?
0
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 1800 total points
ID: 37772575
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
ID: 37776981
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

771 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