DoCmd.OpenStoredProcedure

[doco]

I have an Access project using Access 2007 utilizing a form with a ComboBox that is populated by a user defined view from the backend SQL Server 2000 db. The list box contains a list of stored procedures that are in the backend. The user selects a proc and the change event of the List reads the user selection and instances

        DoCmd.OpenStoredProcedure szProcName, acViewNormal, acReadOnly
    [\code]

The process works well with the exception of one stored procedure that works as expected from SQL Server Management Studio but does not work from Access. The params are entered via access prompts and the following error results:

[quote]
The stored procedure executed successfully but did not return records.
[\quote]

All other procs in the list run as expected except this one. There would be a return recordset for any possible param entry but no records return.

Any Ideas?
TIA

 

[boblarson]

How about posting the actual code of the stored procedure?

 

[Khalid_Afridi]

Check your store procedure and the wild card in the pram if you are using any. I did not use any store procedure but it seems that wild card [%,?,*] etc in your pram is producing this error.

These are quiet different in Ms-Access.

Visit: Store Procedures
wildcards MS-Access VS SQL

Using the Right Wildcard Characters in SQL Statements

 

[doco]

CREATE PROCEDURE dbo.udspAccountOwnershipAddress
@tax_year varchar(4)      --  tax year 
--@nbhd int               --  neighborhood
AS
select 
    p.id as property_id, 
    p.parcel_number, 
    p.alt_parcel_nr, 
    pt.id as ppi_id,
    pt.party_id,
    pt.address_id,
    pt.prop_role_cd,
    pt.role_percentage,
    pt.eff_from_date,
    pt.eff_to_date,
    pt.change_reason_cd
into #prop_prop_invlmnt 
from    AscendWheelerMain..property p INNER JOIN
        AscendWheelerMain..party_prop_invlmnt pt ON
        p.id = pt.property_id 
    and pt.eff_to_date is null 
    and pt.prop_role_cd = 524 
    and pt.party_id = 
    ( select max( id.party_id )
      from AscendWheelerMain..party_prop_invlmnt id
      where id.eff_to_date is null
        and id.prop_role_cd = pt.prop_role_cd
        and id.property_id = pt.property_id )        
where             
    p.eff_to_date is null
order by p.id;
--  --------------------------------------------------------------------------
select
    p.property_id,
    p.parcel_number,
    left( alt_parcel_nr, 16 ) as map_lot,
    tca.tca_number as area,
    cast( sz.value as decimal(10,2) ) as acreage,
    o.org_name,
    ad.line_1,
    coalesce( ad.line_2, '' ) as line_2,
    zip.city,
    zip.state,
    zip.zipcode,
    zip.country
from
    tempdb.#prop_prop_invlmnt p LEFT OUTER JOIN
    AscendWheelerMain..organization o ON
    p.party_id = o.party_id LEFT OUTER JOIN
    AscendWheelerMain..prop_valuation pv ON
    p.property_id = pv.property_id
        and pv.tax_year = @tax_year
        and pv.taxable_ind = 'Y' LEFT OUTER JOIN
    AscendWheelerMain..tax_code_area tca ON
    tca.id = pv.tca_id
        and tca.id != 999 LEFT OUTER JOIN
    AscendWheelerMain..property_char sz ON
    p.property_id = sz.property_id
        and sz.tax_year = pv.tax_year
        and sz.prop_char_typ_code = 'SIZE' LEFT OUTER JOIN
    AscendWheelerMain..address ad ON
    p.address_id = ad.id LEFT OUTER JOIN
    AscendWheelerMain..zipzip as zip ON
    ad.zip_postal_code = zip.zipcode
        and ad.city = zip.city
--where zipcode is null
order by p.property_id;

--exec udspAccountOwnershipAddress 2010

It works as expected in SQL Server Editor - but not from Access

 

[boblarson]

I see your input parameters but yet I see nothing that returns a value. You have a bunch of select statements in there but nothing that assigns the value of them to an output variable (at least that I can see).

 

[boblarson]

I see your input parameters but yet I see nothing that returns a value. You have a bunch of select statements in there but nothing that assigns the value of them to an output variable (at least that I can see).

Never mind, it has been a while since I've had to dig into SP's.

 

[doco]

Apparently, DoCmd.OpenStoredProcedure from Access did not like the fact that I was creating a temp table and using temp table in a join. I made the change to

IF OBJECT_ID ( 'dbo.udspAcctOwnershipAddress', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.udspAccountOwnershipAddress;
GO
/*
--  ---------------------------------------------------------------------------
if exists (
    select  * from tempdb.dbo.sysobjects o
    where o.xtype in ('U')  
   and o.id = object_id(N'tempdb..#prop_prop_invlmnt')
)
DROP TABLE #prop_prop_invlmnt;
GO
--  ---------------------------------------------------------------------------
*/
CREATE PROCEDURE dbo.udspAccountOwnershipAddress
@tax_year varchar(4)      --  tax year 
--@nbhd int               --  neighborhood
AS
--  --------------------------------------------------------------------------*/
    select
        p1.property_id,
        p1.parcel_number,
        left( alt_parcel_nr, 16 ) as map_lot,
        tca.tca_number as area,
        cast( sz.value as decimal(10,2) ) as acreage,
        o.org_name,
        ad.line_1,
        coalesce( ad.line_2, '' ) as line_2,
        zip.city,
        zip.state,
        zip.zipcode,
        zip.country
    from
        (
            select 
                p.id as property_id, 
                p.parcel_number, 
                p.alt_parcel_nr, 
                pt.id as ppi_id,
                pt.party_id,
                pt.address_id,
                pt.prop_role_cd,
                pt.role_percentage,
                pt.eff_from_date,
                pt.eff_to_date,
                pt.change_reason_cd
            --into #prop_prop_invlmnt 
            from    AscendWheelerMain..property p INNER JOIN
                    AscendWheelerMain..party_prop_invlmnt pt ON
                    p.id = pt.property_id 
                and pt.eff_to_date is null 
                and pt.prop_role_cd = 524 
                and pt.party_id = 
                ( select max( id.party_id )
                  from AscendWheelerMain..party_prop_invlmnt id
                  where id.eff_to_date is null
                    and id.prop_role_cd = pt.prop_role_cd
                    and id.property_id = pt.property_id )        
            where             
                p.eff_to_date is null
        ) p1 LEFT OUTER JOIN
    AscendWheelerMain..organization o ON
    p1.party_id = o.party_id LEFT OUTER JOIN
    AscendWheelerMain..prop_valuation pv ON
    p1.property_id = pv.property_id
        and pv.tax_year = 2009 -- @tax_year
        and pv.taxable_ind = 'Y' LEFT OUTER JOIN
    AscendWheelerMain..tax_code_area tca ON
    tca.id = pv.tca_id
        and tca.id != 999 LEFT OUTER JOIN
    AscendWheelerMain..property_char sz ON
    p1.property_id = sz.property_id
        and sz.tax_year = pv.tax_year
        and sz.prop_char_typ_code = 'SIZE' LEFT OUTER JOIN
    AscendWheelerMain..address ad ON
    p1.address_id = ad.id LEFT OUTER JOIN
    AscendWheelerMain..zipzip as zip ON
    ad.zip_postal_code = zip.zipcode
        and ad.city = zip.city
--where zipcode is null
order by p1.property_id;

--exec udspAccountOwnershipAddress 2010

and it now works great. I will be glad when we FINALLY get switched over to 2005 so I can use Common Table Expressions.

Thanks All