Unexpected white space after SQL Server stored procedure

Question

Seem to be getting unexpected white space when calling the stored procedure from VBA in Excel using ADODB object. The issue does not occur when running the procedure from SQL Server Management Studio.

exec usp_testloginalert '2015-11-29 00:00:00','2015-11-30 00:00:00',N'screen'

UPDATE

Code from vba

Function querylogs(ByVal todate As Date, ByVal fromdate As Date, ByVal desc As String)
Dim cn As ADODB.Connection
Dim cmdProd As ADODB.command
Dim prs As New ADODB.Recordset

sQuery = "usp_testloginalert"
Set cn = New ADODB.Connection
With cn
    .Provider = "SQLOLEDB"
    sConnection = "dbconn"
    .ConnectionString = sConnection
    .ConnectionTimeout = 1000
    .CommandTimeout = 1000
End With
cn.Open sConnection

Set cmdProd = New ADODB.command
With cmdProd
    .ActiveConnection = cn
    .CommandType = adCmdStoredProc
    .CommandText = "usp_testloginalert"
    .CommandTimeout = 1000
End With
Dim prmFromDate As ADODB.Parameter
Set prmFromDate = cmdProd.CreateParameter("@FromDate", adDBDate,adParamInput)
prmFromDate.Value = fromdate
cmdProd.Parameters.Append prmFromDate

Dim prmToDate As ADODB.Parameter
Set prmToDate = cmdProd.CreateParameter("@ToDate", adDBDate, adParamInput)
prmToDate.Value = todate
cmdProd.Parameters.Append prmToDate

Dim prmDescritpion As ADODB.Parameter
cmdProd.Parameters.Append cmdProd.CreateParameter("Descritpion",adBSTR,adParamInput, 100, desc)

Set prs = New Recordset
prs.CursorLocation = adUseClient
prs.Open cmdProd.Execute

Application.EnableCancelKey = True
cn.Close

Set querylogs = prs
End Function

@jennous Not helpful, no need to be here if it not solve the problem. SO should stay as clean as possible. — Lukasz Szozda, Dec 29 '15 at 09:59

score 0 · Answer 1 · edited May 23 '17 at 12:31

0

Hard to answer when we not see code of your procedure, but I can suggest you to use LTRIM and RTRIM to remove leading/trailing blanks.

In following: LTRIM(RTRIM( /* character_xpression */ ))

UPDATE

If It's not SQL issue, try to check for related posts:

Remove leading or trailing spaces in an entire column of data

Delete blanks from start and end of a string in vba

http://www.excelitems.com/2009/03/remove-extra-spaces-from-cell-value.html

edited May 23 '17 at 12:31

Community

1
1

answered Dec 29 '15 at 09:17

Stanislovas Kalašnikovas

9,203
4
32
59

1

already using LTRIM and RTRIM in procedure , we have fully tested the stored procedure, the issues seem to be on the vba end – jennous Dec 29 '15 at 09:28

score 0 · Accepted Answer · answered Dec 29 '15 at 10:48

issues resolved, set the parameter length to the length of the variable passed.

Dim lendesc As Integer

lendesc = Len(desc) + 1

Dim prmDescritpion As ADODB.Parameter
cmdProd.Parameters.Append cmdProd.CreateParameter("Descritpion", adBSTR,adParamInput, lendesc, desc)

Unexpected white space after SQL Server stored procedure

2 Answers2