String truncated in execution of Stored procedure RRS feed

  • Question

  • Hi Team,

    I am using Excel VBA to call a Sql Server - Stored Procedure.

    I have a parameter @XYZ, i am passing 'TOKM', but while debugging the VBA program, it is passing as 'TOKM'.

    But when it is passed to a stored procedure it is truncating to 'TOK'.

    Please help me out.




    • Changed type Tom Phillips Wednesday, August 27, 2014 12:02 PM
    • Moved by Kalman Toth Wednesday, August 27, 2014 2:53 PM Better fit
    • Moved by Paul Ishak Wednesday, August 27, 2014 3:09 PM Better fit
    Wednesday, August 27, 2014 10:25 AM

All replies

  • How is your stored procedure parameter defined?  Can you post the code?  Have you tried running Profiler to see what is getting sent to the engine to determine if it is the way it is getting passed in or inside the procedure code?

    Sam Lester (MSFT)

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Wednesday, August 27, 2014 10:28 AM
  • ALTER PROCEDURE [dbo].[sp_getSalesPlanningValues]
    -- Add the parameters for the stored procedure here
    @Period nvarChar(MAX), @BukrID nvarChar(50), @KAMID nvarchar(50), @SektorID nvarChar(50), @kundeID nvarChar(50), @KTRID nvarChar(50), @KennzID nvarChar(50), @LVMEID nvarChar(MAX), @PI as nvarchar(5)

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

    Declare @stmt varchar(3000);

    --if (@LVMEID ='TO-') Set @LVMEID = 'TO-KM'

        -- Insert statements for procedure here
    set @stmt = 'SELECT * FROM 
    (select NULL as AVG, NULL as SUM, t1.Period, t1.Value
    from tblUmstazPlanning t1
    Left Join tblBuKr t2
    on t1.[Bukr ID] = t2.[BuKr ID]
    Left Join tblKam t3
    on t1.[KAM ID] = t3.[KAM ID]
    Left Join tblSektor t4
    on t1.[Sektor ID] = t4.[Sektor ID]
    Left Join tblKunde t5
    on t1.[Kunde ID] = t5.[Kunde ID]
    Left Join tblKostenträger t6
    on t1.[KTR ID] = t6.[KTR ID]
    where t1.[Bukr ID] ='+ @BukrID + 'and t1.[KAM ID] =' + @KAMID + 'and t1.[Sektor ID] =' + @SektorID + 'and t1.[Kunde ID] =' + @kundeID + 'and t1.[KTR ID] =' + char(39) + @KTRID + char(39) + ' and t1.[Kennzeichen ID] =' + char(39) + @KennzID + char(39) + 'and t1.[LeistungsmengeVertrieb Mengeneinheit ID] =' + char(39) +@LVMEID + char(39)+ ' and t1.[Plan Identifier] =' + char(39) + @PI + char(39) + '
    ) As A
    PIVOT(Max(Value) FOR Period IN (' + @Period + ')) AS p'

    Execute (@Stmt)



    Wednesday, August 27, 2014 10:40 AM
  • I have check by debugging the command execution and it is taking only 3 chars only


    Wednesday, August 27, 2014 10:41 AM
  • I think you're having field length as 3.

    Please Mark This As Answer if it helps to solve the issue Visakh ----------------------------

    Wednesday, August 27, 2014 10:54 AM
  • No i am setting the field length as 50


    Wednesday, August 27, 2014 11:22 AM
  • If VBA is passing only 3 characters to the stored proc, this is a VBA problem, not a SQL Server problem.  Please look at your VBA code.

    Wednesday, August 27, 2014 11:55 AM
  • When we are debugging the VBA is passing the entire string, but when we see the command text of sql stored procedure, it is trunctated.

    what is the equivalent datatype of nvarChar for assigning a parameter through VBA.


    Wednesday, August 27, 2014 1:14 PM
  • Please post your VBA code for the call to the stored procedure. 

    Wednesday, August 27, 2014 2:00 PM
  •  strStoredProcedure = "sp_getSalesPlanningValues"
                'Read the values through stored procedure for mutiple parameters
                Dim cmd As New ADODB.Command
                Dim PA1 As New ADODB.Parameter, PA2 As New ADODB.Parameter, PA3 As New ADODB.Parameter, PA4 As New ADODB.Parameter, PA5 As New ADODB.Parameter, PA6 As New ADODB.Parameter, PA7 As New ADODB.Parameter, PA8 As New ADODB.Parameter, PA9 As New ADODB.Parameter
                cmd.ActiveConnection = objDatabase.conn
                cmd.CommandType = adCmdText
                cmd.CommandText = strStoredProcedure
                'create parameters
                Set PA1 = cmd.CreateParameter("@Period", adLongVarChar, adParamInput)
                Set PA2 = cmd.CreateParameter("@BukrID", adLongVarChar, adParamInput)
                Set PA3 = cmd.CreateParameter("@KAMID", adLongVarChar, adParamInput)
                Set PA4 = cmd.CreateParameter("@SektorID", adLongVarChar, adParamInput)
                Set PA5 = cmd.CreateParameter("@kundeID", adLongVarChar, adParamInput)
                Set PA6 = cmd.CreateParameter("@KTRID", adLongVarChar, adParamInput)
                Set PA7 = cmd.CreateParameter("@KennzID", adLongVarChar, adParamInput)
                Set PA8 = cmd.CreateParameter("@LVMEID", adLongVarChar, adParamInput)
                Set PA9 = cmd.CreateParameter("@PI", adLongVarChar, adParamInput)
                'set the values for all parameters
                PA1.value = strPeriod
                PA2.value = objTransaction.BukrID
                PA3.value = objTransaction.KAMID
                PA4.value = objTransaction.SektorID
                PA5.value = objTransaction.kundeID
                PA6.value = objTransaction.KTRID
                PA7.value = objTransaction.KennzID
                PA8.value = objTransaction.LVMEID
                PA9.value = objTransaction.PlanIDentifier
                'populate the recordset
                Set rs = cmd.Execute(, Array(PA1, PA2, PA3, PA4, PA5, PA6, PA7, PA8, PA9), adCmdStoredProc)
                If Not rs.BOF And Not rs.EOF Then
                    If rs.RecordCount > 0 Then
                        ThisWorkbook.Sheets(shtSales).Range(strColLtr & rwCounter).CopyFromRecordset rs
                    End If
                End If
            Next rwCounter
            'set next plan Identifier column
            lngCol = lngCol + 14
            Call IntroduceSmallDelay
            ufProgressBar.lblProgressBar.Width = CInt(lngCol / rwLast * 100) * ufProgressBar.Width / 100
        Next lngCounter

        Application.ScreenUpdating = False
        Set objTransaction = Nothing
        Call protectUnprotectSheet(ThisWorkbook.Name, shtDeveloper, True)


    Wednesday, August 27, 2014 2:07 PM
  • Try to add a SIZE to your inputparameters: like this:

     Set PA1 = cmd.CreateParameter("@Period", adLongVarChar, adParamInput,50)

    Wednesday, August 27, 2014 2:43 PM
  • Still not working after changing the as said above.


    Thursday, August 28, 2014 5:37 AM