Solved

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

Posted on 2012-03-27
3
989 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 50 total points
ID: 37772366
How is pUSI defined in your stored procedure?
0
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 450 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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

717 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