SQL Server Stored Procedure

pinbot

Trying to get data from an SQL Server stored procedure.

In "Enter SQL Statement" I entered:

exec alndata.saygoodbyeto 'dfw'

The manual says "In the second step, you have to enter a query in SQL language. Use the language dialect
which is supported by your MSDB."

This works in Query Analyzer.

What is the correct way to call a stored procedure?

pinbot

I also tried this:

{call alndata.saygoodbyeto('dfw')}

Which also works in Query Analyzer but not FR.Net.

I'm sure it's something simple.

SQL Server 2000

AlexTZ

Hello,

I have a test database with stored proc MiProc2 which takes nvarchar(50) parameter. I can succesfully call it using the following SQL statement:

exec MiProc2 "test"

I'm using MS SQL Express.

pinbot

Getting Closer.

I've changed it to:

exec alndata.saygoodbyeto "dfw"

(FR Studio would use the single quotes like QA so I am used to working everything out in QA and then posting into Fast Reports)

When I click next through the Query Wizard, it does not show the columns in the Define Columns Page.

However, I CAN right click on the "table" under Data Sources and click on "View Data...". It shows the data I'm expecting but then throws a Null Pointer Exception. I've attached a screen capture.

If I try to use one of the fields in my Data band (using a column I know is in the result set)

[GoodBye.MgmtCoName]

I get the error "Text5: error CS0103: The name 'GoodBye' does not exist in the current context"

AlexTZ

Sorry, I cannot reproduce this. It works well in my test database. Maybe it is related to your stored proc? Please try this:

Q: Stored proc with temp table does not work in FR (get error message "invalid objectname...")
A: Please read about similar problem and workaround here:
http://objectmix.com/ado-dao-rdo-rds/25615...res-temp-t.html

pinbot

That's probably it. I'm creating a temp table and returning that.

I knew I had read something somewhere about a problem with stored procedures and FR.

I'll look over it and if I cannot get it to work quickly I'll just do the report in FR Studio. I was hoping to make this my first "real" FR.Net report.

AlexTZ

Actually, it's not a FR.Net problem. It just executes SQL that you provide. The problem is that System.Data.SqlClient.SqlDataAdapter cannot execute the GetSchema method if your stored proc uses temp table. This method is used in FR to obtain a list of table columns.

pinbot

Woohoo!

The workaround fixed my problem:

Adding

IF 1=0 BEGIN
SET FMTONLY OFF
END

at the start of my stored procedure let me use it in FR.NET.