locked
sql server reporting and store procedure RRS feed

  • Question

  • User-309740890 posted

    Hi, I have a sp (using sql server 7), created a Dataset, everthing works\I see the data when clicking run on Data layer.

    But when I try to create a report in Layout tab, I dont see my fileds that I can select (drag and drop) from dataset to the report.

    my sp:

    CREATE PROCEDURE [spTEST]
    AS
    select
    ID
    FROM company

    I get an error:

    Could not generate a list of fields for the query.

    Check the query syntax, or click Refresh Fields on the query toolbar.

    Addiional Information:

    Error [42000][Microsoft][ODBC SQL SERVER Driver][SQL SERVER] Line 1: Incorrect syntax near spTEST (SQLSRV32.DLL)

    Any ideas how to make it work? please advice... thank u

    Wednesday, August 10, 2011 3:01 PM

All replies

  • User1269925535 posted

    hi

    check it:

    USE pubs
    IF EXISTS (SELECT name FROM sysobjects 
             WHERE name = 'au_info' AND type = 'P')
       DROP PROCEDURE au_info
    GO
    USE pubs
    GO
    CREATE PROCEDURE au_info 
       @lastname varchar(40), 
       @firstname varchar(20) 
    AS 
    SELECT au_lname, au_fname, title, pub_name
       FROM authors a INNER JOIN titleauthor ta
          ON a.au_id = ta.au_id INNER JOIN titles t
          ON t.title_id = ta.title_id INNER JOIN publishers p
          ON t.pub_id = p.pub_id
       WHERE  au_fname = @firstname
          AND au_lname = @lastname
    GO

    may be u create ur stropro and now u shulde drop it and recreate as above

    see more here:

    http://msdn.microsoft.com/en-us/library/aa258259(v=sql.80).aspx

    Wednesday, August 10, 2011 5:01 PM