none
Excel 2016 - unable to call stored procedure using data connection. RRS feed

  • Question

  • Hi Folks,

    I have a stored procedure in SQL Server 2014 that accepts two strings (VARCHAR(1024) each).  I have created a data connection and set the command type to SQL and the command text  to  Execute dbo.myproc '',''  this works and I get a table of data back.

    However, if I change the command to Execute dbo.myproc 'abc','def'  I get an error message:-

    OLE DB or ODBC error: Conversion failed when converting date and/or time from character string: 22007

    Does anyone know how to make this work?   I need to pass two strings to the stored procedure and I DONT want excel to do any date manipulation on them.

    Regards

    Andy

    Monday, January 18, 2016 2:16 AM

Answers

  • Hi Andy,

    Based on my test with Excel 2016, it works fine, so I don’t think the issue is related to excel 2016.

    Store Procedure:

    create procedure GetUserInfoByDateSP
    @CreateDate date,
    @UserName nvarchar(50)
    As
    Set NOCOUNT ON;
    select Id,Username,Pwd,CreateDate from dbo.UserInfo where CreateDate>@CreateDate
    go

    VBA code:

    Dim conn As ADODB.connection
    Dim recordset As ADODB.recordset
    Dim cmd As ADODB.Command
    Set conn = CreateObject("ADODB.Connection")
    Set cmd = CreateObject("ADODB.Command")
    'conn.ConnectionString = "Provider=SQLNCLI11;" _
    '         & "Server=minint-7co030m;" _
    '         & "Database=MessageInfo;" _
    '         & "Integrated Security=SSPI;" _
    '         & "DataTypeCompatibility=80;" _
    '         & "MARS Connection=True;"
    conn.ConnectionString = "Provider=SQLNCLI11;" _
             & "Server=minint-7co030m;" _
             & "Database=MessageInfo;" _
             & "User ID=XXX;Password=XXX;" _
             & "DataTypeCompatibility=80;" _
             & "MARS Connection=True;"
    conn.Open
    cmd.ActiveConnection = conn
    'cmd.CommandText = "select Id,Username,Pwd,CreateDate from dbo.UserInfo where CreateDate>'5/5/2013'"
    cmd.CommandText = "GetUserInfoByDateSP"
    With cmd
    .Parameters.Append .CreateParameter("CreateDate", adDate, adParamInput, , "5/2/2013")
    .Parameters.Append .CreateParameter("UserName", adLongVarChar, adParamInput, 50, "test")
    .CommandType = adCmdStoredProc
    End With
    Set recordset = cmd.Execute
    ActiveSheet.Range("A27").CopyFromRecordset recordset
    recordset.Close
    conn.Close

    Regards

    Starain


    Tuesday, January 19, 2016 6:12 AM
    Moderator

All replies

  • Hi Andy,

    Based on my test with Excel 2016, it works fine, so I don’t think the issue is related to excel 2016.

    Store Procedure:

    create procedure GetUserInfoByDateSP
    @CreateDate date,
    @UserName nvarchar(50)
    As
    Set NOCOUNT ON;
    select Id,Username,Pwd,CreateDate from dbo.UserInfo where CreateDate>@CreateDate
    go

    VBA code:

    Dim conn As ADODB.connection
    Dim recordset As ADODB.recordset
    Dim cmd As ADODB.Command
    Set conn = CreateObject("ADODB.Connection")
    Set cmd = CreateObject("ADODB.Command")
    'conn.ConnectionString = "Provider=SQLNCLI11;" _
    '         & "Server=minint-7co030m;" _
    '         & "Database=MessageInfo;" _
    '         & "Integrated Security=SSPI;" _
    '         & "DataTypeCompatibility=80;" _
    '         & "MARS Connection=True;"
    conn.ConnectionString = "Provider=SQLNCLI11;" _
             & "Server=minint-7co030m;" _
             & "Database=MessageInfo;" _
             & "User ID=XXX;Password=XXX;" _
             & "DataTypeCompatibility=80;" _
             & "MARS Connection=True;"
    conn.Open
    cmd.ActiveConnection = conn
    'cmd.CommandText = "select Id,Username,Pwd,CreateDate from dbo.UserInfo where CreateDate>'5/5/2013'"
    cmd.CommandText = "GetUserInfoByDateSP"
    With cmd
    .Parameters.Append .CreateParameter("CreateDate", adDate, adParamInput, , "5/2/2013")
    .Parameters.Append .CreateParameter("UserName", adLongVarChar, adParamInput, 50, "test")
    .CommandType = adCmdStoredProc
    End With
    Set recordset = cmd.Execute
    ActiveSheet.Range("A27").CopyFromRecordset recordset
    recordset.Close
    conn.Close

    Regards

    Starain


    Tuesday, January 19, 2016 6:12 AM
    Moderator
  • Hi,

     Thanks for your reply,  after much testing I found out that the error was in a table function that was being called by the stored procedure. 

    I used the connection wizard to create the connection and insert the table, so my VBA code just refreshes the connection.

    The issue ws that while I was passing strings to the stored procedure it was calling a table function internally that accepted NULL or a valid date as a parameter. When I passed in an empty string to the stored procedure, the table function worked - when a valid string was passed in (even if formatted as a date) the table function failed.

    The 'conversion failed' message was being returned by SQL server, but the OLE DB error code was added by Excel which is why I thought it was a client side issue.

    I have fixed the stored procedure to correctly handle the date and all works fine now - it will return an empty recordset if an internal error occurs.


    • Edited by AndyW2007 Tuesday, January 19, 2016 7:44 PM
    Tuesday, January 19, 2016 7:44 PM