InfoSet Wizard not Autocreating from a stored procedure

Thomas,

You can actually run a stored procedure from an InfoSet. Just be sure to set that at the top of Step 1. If thats unacceptable you could also try putting a SELECT statement at the very end. This will allow you to define one column in the infoset and save i, although, it may be that you cant use control structure SQL (IF THE ELSE) in the InfoSet editor.

hth
matt

Matt,

tried the stored procedure approach first, actually. And that got me the same error.

I also restructured the SQL in the query to make sure the very last statement was a SELECT FROM... - but no luck there either.

However, there is an IF THEN structure at the very end, to pick between two different versions of the last SELECT. I'll try removing that and see if we get the proper result.

Thanks!
-- Thomas

And... same result from making sure there is a single SELECT as the last statement. A puzzler!

Ok, I will test this out tonight. Its tricky, I remember that I just dont recall off the top of my head what the solution is.

Matt,

thanks! We greatly appreciate that.

ok looks like the way I got it to work was to actually select the "Query" option but still run your stored proc like normal. Then just define one column as a key. My SP didnt return any result sets.

Let me know if this works.

When you say, select "Query" but run your stored procedure like normal you mean:

EXEC PMM.dbo.up_rpt_InvDiscr '',0,'', 0.0,0.0,0

I set that up, then add one of the columns in the expected result set as a KEY row in the InfoSet, then click AutoCreate... and still get the same error. But I'd guess I'm doing that part incorrectly... how are you doing this:

Then just define one column as a key

well, its just defined as a single column of a string type. Now that I think about it, I believe what I had to do was trick it. Try defining the infoset using this SQL:

SELECT 1

Save it, update it, then go in and put your stored proc. My stored proc ends with and IF THEN ELSE too so that shouldnt be the problem.

Finally got this to work by taking a more brute-force approach:

1. Dispensed with the stored procedure all together. Took the code from the sp and used it as a straight query.
2. Replaced all temp tables in the query (there were four) with table variables; aliased the table variables like regular tables.
3. Made sure there were no field names in square brackets ( [] ) which apparently the JDBC driver did not like.
4. Made sure the final statement in the query was the SELECT against the table variables. There had been an IF/THEN structure switching between two different versions of the final query (based on a parameter from the stored procedure, which was also dispensed with).

Then that worked fine in the InfoSet wizard, etc.

Wow, thats intereesting. Just an FYI, InfoSets will treat anything with square braces as a parameter and prompt you for values when you update the InfoSet