Stored Procedure executed in pass through query return numberic field as text RRS feed

  • Question

  • Hi

    I use Excel to present data pulled from SQL Server. When I link from Excel to stored procedure in SQL Server directly, numeric fields show as numeric field, which is correct.

    If I use pass through query in Access to retrieve the same stored procedure so that I could filter on the returned results to avoid creating many stored procedures in SQL server, the numeric fields all return as text and later when I use excel to link to these queries in Access, the numeric fields are treated as text fields and can't be calculated.

    I know I could create an additional query which sums up all numeric fields first then it will work in Excel; however I wonder if I could avoid this extra step.

    Please let me know if my question doesn't make sense.


    Friday, December 2, 2016 6:27 PM

All replies

  • Can you post your SP and your pass-through query? Maybe the problem lies in how one or both are written.

    Bill Mosca

    Friday, December 2, 2016 8:14 PM
  • The column type data should come through correctly, even when using a pass-through query (that calls a store procedure).

    I do find that when you connect to “some” ODBC sources that when you use a linked table, the data types do come through correctly, but with a pass-though the data types are all text. Thus I have OFTEN seen that data types don’t come through and this is a COMMON occurrence, but I find this NOT the case when using SQL server. So in the case of SQL Server then use of a Pass-through query – even one that calls a store proc should return the CORRECT data types when doing so.

    It not clear how you using or calling the query, but the easiest is to use VBA code like this:

       Dim rst     As DAO.Recordset
       With CurrentDb.QueryDefs("qryPassR")
          .SQL = "exec s1 " & MyParm1
          Set rst = .OpenRecordset
       End With

    So the above will return a recordset with the correct data types.

    And if want the above query for say a report or form, or use for some export command, then just use:

       Dim rst     As DAO.Recordset
       With CurrentDb.QueryDefs("qryPassR")
          .SQL = "exec s1 " & MyParm1
       End With

    Code here to open form/report or export data

    So when using SQL server, then the correct data types are returned from the server.

    It not clear what the store proc looks like, and I can well note that the data types are OFTEN not returned from server database systems when using a pass-through query, but it never been the case when using SQL server. I would try the above second example (run your code to set the pt query, and then do a quick make-table query from the query designer and then look at the resulting table created local in table design mode to “verify” what the data types of each column are.

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Saturday, December 3, 2016 10:10 PM