It is currently Fri Aug 01, 2014 5:52 am




Post new topic Reply to topic  [ 3 posts ] 
 Multiple recordsets 
Author Message
Cadet
Cadet

Joined: Thu Oct 08, 2009 10:01 pm
Posts: 2
Post Multiple recordsets
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?


Attachments:
File comment: A rough sketch of the task
Overview.png
Overview.png [ 34.64 KiB | Viewed 1541 times ]
Thu Oct 08, 2009 10:21 pm
Profile
General
General

Joined: Tue Jun 26, 2007 1:10 pm
Posts: 158
Post Re: 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).


Fri Oct 09, 2009 4:28 pm
Profile
Cadet
Cadet

Joined: Thu Oct 08, 2009 10:01 pm
Posts: 2
Post Re: Multiple recordsets
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


Mon Oct 26, 2009 12:05 am
Profile
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to: