Hi
I have been trying out the trial version of FSQL for a few days now trying to solve a specific task I have.
I need to get order data from MSSQL database to Allen Bradley CLX.
I’m currently using a RSview/VBA application to call my stored procedures and then push each value to string/integer objects on a display and then download. What i’m looking for is a way to replace that RSview/VBA solution with a more transparant and “easy-to-change” solution.
I think the biggest issue is that the database and stored procedures are “locked” as a standard now and it would take some heavy arguments to make changes there. And the major stored procedure which is called every 10 sec. to check for new released order returns 5 recordsets each returning different amounts of rows.
Does anyone have experience with FSQL and stored procedures returning multiple recordsets?
mortendc,
I think one way you can get FSQL to work in this situation is to create nested stored procedures. You can wrap an existing stored procedure with a new stored procedure that combines the record sets. This of course, assumes that the columns in the record sets are the same or of a compatible type. You might have to append a new column to identify from which record set a record originated.
I’ve seen a couple of ways of combining multiple record sets from a stored procedure, one that uses “OS files” (http://www.databasejournal.com/features/mssql/article.php/3386661/How-To-Get-Output-Into-SQL-Server-Table.htm) and another that uses temporary tables (http://blog.joggee.com/?p=24). The latter, in my opinion, is likely to be the better and more resource efficient solution. Here’s another reference that has multiple ways of managing multiple record sets from a stored procedure (http://www.sommarskog.se/share_data.html).
Hi MickeyBob
Thanks for your reply.
Your first example as you also mention is not very good. You will end up with a single coloumn table with combined values which will be very hard to seperate.
I’m not sure if I can use the second suggestion because it doesen’t handle multiple recordset from the “nested” SP?
The called SP returns a single reulstset which is then just inserted into a temp table. But please correct me if i’m wrong.
The 3 example you refer to is talking about using a CLR function. And that is basically the setup I’m using at the moment where I use RSview’s VBA to call the stored procedure and then “browse” through the different recordsets and the varying rows of each recordset.
So I can’t se that I will gain much by using a CLR function on my database because I will just move my VBA from a client to the SQL server which doesent make the system more transparant.
I’m getting more and more convinced that I have to choose between to things.
A more transpatrant system where I need to change the already defined SP to make them more simple and return only a single resultset
Or keep the system as it is and live with the vba logic running on a client