Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009

    Unanswered: .adp no longer getting output params from SQLServer sproc

    Access 2000 .adp file format in Access 2003
    SQL Server 2005 SP2 recently applied update package 7

    I've been beating my head against the wall for several hours trying to understand why the .adp project I've been tinkering with off and on for the last few months no longer gets return values when it calls a sproc.

    Code that used to work has ceased to work--only because my output param is always returned NULL. Having checked, double-checked, and triple-checked the code on both the .adp and the spoc (which runs fine in SQL Server) I'm now suspicious of some recent upgrade work done on the server.

    Any thoughts? Has anyone else had this break when catching up SQL Server on patches?


  2. #2
    Join Date
    Mar 2009
    Just verified MDAC version: Version 2.82.1830.0

    This should be OK for 2005. Hmmmm.

  3. #3
    Join Date
    Mar 2009
    Here's the code in question. The sproc runs, it creates a table, imports a file, yadda, yadda, yadda. The output param @msg is set to 'begin' immediately on the start of the TSQL code and set to 'Success' if it finishes. However, it is always a null string to the VBA code.

    Set objCmd.ActiveConnection = objCnn
    objCmd.CommandType = adCmdStoredProc
    objCmd.CommandText = "p_campaign_import_ao_data"

    ' Refresh the parameter list

    ' Set up output params
    objCmd.Parameters("@msg").Direction = adParamOutput
    objCmd.Parameters("@ao_stage_ct").Direction = adParamOutput
    objCmd.Parameters("@ao_error_ct").Direction = adParamOutput
    objCmd.Parameters("@table_name").Direction = adParamOutput

    ' Add values for input params
    objCmd("@user").Value = fOSUserName()
    objCmd("@campaign_id").Value = Me!txtCampaignID
    objCmd("@alterian_output_file").Value = Me!txtAlterianOutputFile

    ' Execute the command

    ' Pick up msg param
    strMsg = objCmd("@msg").Value

  4. #4
    Join Date
    Mar 2009
    Product Version: 9.00.3152.00
    Product Level: SP2
    Edition: Enterprise Edition (64-bit)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts