none
BIDS 2005 cannot find Stored Procedure

    Question

  • I have read how to do this in multiple places but my report is not able to use a stored procedure for retrieving data. When I select command type 'stored procedure' to populate my dataset, and then switch to the GUI query designer, I only see a partial list of system SPs, not my user defined SPs. My SP does return a single dataset.
    If I try to manually use the SP, with or without doublequotes and EXEC, the report designer simply tells me that the SP cannot be found.
    Monday, October 19, 2009 8:45 PM

Answers

  • Hi DevDells,

    Based on the error message and sp sql text, the error occurs becaused the SQL command "drop table #rowcount" drops the temp table. You can do a little change for your SP script like this,

    --------------------------------

    IF Object_ID('tempdb.dbo.#rowcount') is not null
        Drop table #rowcount
    create table #rowcount (tablename varchar(128), rowcnt int)
    exec sp_MSforeachtable
    'insert into #rowcount select ''?'', count(*) from ?'
    select * from #rowcount
    where tablename like '%MC_Amb%'
    order by tablename


    ---------------------------------

    This change can not only drop the temp table before creating it, but also keep it available on the report avoiding the "Invalid object name '#rowcount'" error.

    By the way, you did not show any SQL script about the table '#qtemp', i think it's the same to do it as the '#rowcount'.

    Please try and let me know if you have more questions.

    thanks,
    Jerry




    Thursday, October 22, 2009 1:46 AM
  • Hi There - I've seen similar errors when executing procedures that use #temp tables, the error is related to how the designer gets the metadata from SQL Server for the stored procedure. If you start SQL Profiler on your datasource server, and then use the "Refresh fields" option in the designer, and then look at what the designer executed in SQL Profiler, you will see a pattern similar to:

    SET FMTONLY OFF; SET FMTONLY ON;

    exec TestProcedure @a=NULL

    SET FMTONLY OFF;

    If you execute these same statements in SQL Mgmt Studio, you will see the same error as in the designer:

    Msg 208, Level 16, State 0, Procedure TestProcedure, Line 7

    Invalid object name '#temp'.

     

    The designer needs to know what to expect from the query/procedure, so the goal of "SET FMTONLY ON" from the designer is to execute the TSQL query in a special way that returns just the metadata about the result set (column names and datatypes and not the actual result set data). So in the process of evaluating the stored procedure without fully executing it, SQL Server apparently does not actually create the temp table referenced in the procedure.

    Since #temp tables are local to the context of the stored procedure and are dropped automatically when the stored procedure exits, you should not need to drop it before or after creating it in the procedure, and dropping it at the beginning or the end of the procedure does not seem to affect the SET FMTONLY mode.

    To get around this, I commonly use table variables instead of temp tables, and this seems to work fine with SET FMTONLY in the designer. For example, if your procedure looks like this:

    create procedure TestProcedure @a int as
    create table #temp (a int)
    insert into #temp (a) values (@a)
    select a from #temp

    The table variable approach with the exact same semantics would look like this:

    create procedure TestProcedure @a int as
    declare @temp as table (a int)
    insert into @temp (a) values (@a)
    select a from @temp


    This will work fine with the SET FMTONLY affects in the designer and SQL Mgmt Studio. I'm a bit rusty on the detailed tradeoffs of temp tables vs table variables, but it sems to work fine for me in report data sets.

    I hope this helps - Dean

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, October 22, 2009 4:36 PM

All replies

  • Hi There - It sounds like you need to set the default database in the data source to the one with your stored procedure.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, October 19, 2009 10:22 PM
  • I am using BIDS 2005 and the data source does have a default database. Connection string as follows:
    Data Source=wismdcn6;Initial Catalog=WSP_Forms;Integrated Security=True

    Version of SQL Server is 2005, build 9.0.4262

    After reading your response I decided to start a new dataset in the designer for the report. I created a brand new datasource and used windows authentication as in the connection string above (my previous datasource was SQL authentication). And I used the dialog for building the new dataset by choosing Stored Procedure in the first tab and typing in the name of my SP, no qualifiers, just the name. 

    Lo and behold, different results. And it makes sense to me that on closing the dialog, I was prompted with this error:

    TITLE: Microsoft Report Designer
    ------------------------------

    Could not generate a list of fields for the query.
    Check the query syntax, or click Refresh Fields on the query toolbar.
    ------------------------------
    ADDITIONAL INFORMATION:

    Invalid object name '#qtemp'.
    Invalid object name '#rowcount'. (Microsoft SQL Server, Error: 208)

    ------------------------------

    My SP is as follows:

    create table #rowcount (tablename varchar(128), rowcnt int)
    exec sp_MSforeachtable
    'insert into #rowcount select ''?'', count(*) from ?'
    select * from #rowcount
    where tablename like '%MC_Amb%'
    order by tablename
    drop table #rowcount

    Two conclusions:
    1) I may see a difference between datasources returning SPs when I choose SQL authentication over Windows Authentication. The limited privilege account I have used in my connection string did not have 'execute' permission granted for the SP in question.
    2) My SP written to take advantage of the undocumented MSforeachtable is not returning an understandable resultset to the report designer

    My remaining question then is what format should an SP be returning a resultset in so that BIDS 2005 will work appropriately when 'Stored Procedure' is chosen for the dataset?


    Tuesday, October 20, 2009 1:47 PM
  • Hi DevDells,

    Based on the error message and sp sql text, the error occurs becaused the SQL command "drop table #rowcount" drops the temp table. You can do a little change for your SP script like this,

    --------------------------------

    IF Object_ID('tempdb.dbo.#rowcount') is not null
        Drop table #rowcount
    create table #rowcount (tablename varchar(128), rowcnt int)
    exec sp_MSforeachtable
    'insert into #rowcount select ''?'', count(*) from ?'
    select * from #rowcount
    where tablename like '%MC_Amb%'
    order by tablename


    ---------------------------------

    This change can not only drop the temp table before creating it, but also keep it available on the report avoiding the "Invalid object name '#rowcount'" error.

    By the way, you did not show any SQL script about the table '#qtemp', i think it's the same to do it as the '#rowcount'.

    Please try and let me know if you have more questions.

    thanks,
    Jerry




    Thursday, October 22, 2009 1:46 AM
  • Hi There - I've seen similar errors when executing procedures that use #temp tables, the error is related to how the designer gets the metadata from SQL Server for the stored procedure. If you start SQL Profiler on your datasource server, and then use the "Refresh fields" option in the designer, and then look at what the designer executed in SQL Profiler, you will see a pattern similar to:

    SET FMTONLY OFF; SET FMTONLY ON;

    exec TestProcedure @a=NULL

    SET FMTONLY OFF;

    If you execute these same statements in SQL Mgmt Studio, you will see the same error as in the designer:

    Msg 208, Level 16, State 0, Procedure TestProcedure, Line 7

    Invalid object name '#temp'.

     

    The designer needs to know what to expect from the query/procedure, so the goal of "SET FMTONLY ON" from the designer is to execute the TSQL query in a special way that returns just the metadata about the result set (column names and datatypes and not the actual result set data). So in the process of evaluating the stored procedure without fully executing it, SQL Server apparently does not actually create the temp table referenced in the procedure.

    Since #temp tables are local to the context of the stored procedure and are dropped automatically when the stored procedure exits, you should not need to drop it before or after creating it in the procedure, and dropping it at the beginning or the end of the procedure does not seem to affect the SET FMTONLY mode.

    To get around this, I commonly use table variables instead of temp tables, and this seems to work fine with SET FMTONLY in the designer. For example, if your procedure looks like this:

    create procedure TestProcedure @a int as
    create table #temp (a int)
    insert into #temp (a) values (@a)
    select a from #temp

    The table variable approach with the exact same semantics would look like this:

    create procedure TestProcedure @a int as
    declare @temp as table (a int)
    insert into @temp (a) values (@a)
    select a from @temp


    This will work fine with the SET FMTONLY affects in the designer and SQL Mgmt Studio. I'm a bit rusty on the detailed tradeoffs of temp tables vs table variables, but it sems to work fine for me in report data sets.

    I hope this helps - Dean

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, October 22, 2009 4:36 PM