Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Heterogeneous query against SQL Server sproc

    Hi

    I will start with the solution I think I need and then outline my business requirements so you can tell me the solution I really need

    Anyone know how to create an heterogeneous query that returns the resultset of a SQL Server sproc. For example, the below works super against a table:
    Code:
    SELECT *
    FROM Table1 IN '' [ODBC;DRIVER=SQL Server;SERVER=(Local);DATABASE=Board;]
    Anyone know the code (if there is such a thing) for a sproc? Of course, I could use a pass through (in fact, at present I do) however I would prefer to run this stuff through code rather than have unencrypted connection strings littering my app (currently I am still using SQL Server authentication ).

    I have bodged together a "create pass through, use it and then delete" procedure but it seems ever so cumbersome.

    Ok - business requirement - I want to dump some sproc output data locally because it is accessed frequently during a session and it seems silly to pass it across the network regularly. The upside is that this is read only. The killer is that some of the data sets are reasonably large (not so wide but ~9k records deep in one case). As such, putting into a recordset and writing a line at a time to a local table is not an option.

    The only other option I have considered is: instantiate and populate a client side recordset at the beginning of the session. Assign this recordset to the combos\ forms as and when required during the session, keeping it open all the way through. Basically, cache the data in memory rather than write to disk. This would reduce IO of course but what would the affect be on memory? By my (approximate but erring high) calculations the data in one recordset would be a little under 1 megabyte.

    Anyway - if anyone has any good thoughts on how best to access big sproc outputs n times during a session for use in combos and forms (not queries or reports) please let me know
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    BOL on OPENQUERY looks promising for the sp thing, never used it tho.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Consider using temp tables?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by izyrider
    BOL on OPENQUERY looks promising for the sp thing, never used it tho.
    Hmm - I think I've used OPENQUERY - isn't that kind of how you would do this sort of thing but from SQL Server (e.g. you need to get the output of an Oracle sproc into SQL Server so you use OPENQUERY)? I will have a nose though - cheers
    Quote Originally Posted by teddy
    Consider using temp tables?
    Where? If in Access then that is what I hope to do - the question is how to efficently populate them from a sproc output? If in SQL Server then it probably wouldn't be much use.... I am using a disconnected mdb so the session opens and closes.... even session scope temp tables would get created and destroyed multiple times while the app is open. And the whole idea is to try to get the data once to the client rather than passing muliple times across the network.

    Thanks for replying guys - I'm kind of hoping one of you experienced disconnected folk will have come across this problem some time or at least have a few more strings to your bow than me
    Last edited by pootle flump; 02-18-06 at 06:14.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Izy

    Double checked - yep - I am after the Access SQL\ VBA equivelent of OPENQUERY. T-SQL Syntax:
    Code:
    SELECT *
    FROM OPENQUERY(MyLinkedServer, 'EXEC MySproc @Param1 = 'SomeData'')
    (you would probably need to double quote but I don't have a spare SQL Server instance lying around to link to and test, it being weekend and all ).

    Not a million miles from the Access heterogeneous query however it passes an unparsed string to the remote server to execute. This is really what a pass through does. So - is there a way to mimic the affect of a pass through without actually creating a pass through query object?
    In the same way that it is possible to execute a bog standard DML statement using VBA without having a query object.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ooo - oo - oooh!
    Izy - you got me thinking laterally. I think I have the solution to my (typically) idiosyncratic requirement. I will post the code when done
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - I think I am happy with this (only done a proof of concept so far but...).

    Decided to push rather than pull (T-SQL code):
    Code:
    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON
    GO
     
    CREATE PROCEDURE [dbo].[sproc_SendData]
    @DBPAth as VarChar(100)
     
    AS
     
    DECLARE @SQL AS NVarChar(1000)
     
    SELECT @SQL = N'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',' + 
    '''' + @dbpath + '''' + ';''admin'';'''', TheTable) (TheData) SELECT ''ONE'''
     
    EXECUTE sp_executesql @SQL
    GO
    OPENROWSET is the same as Izy's OPENQUERY but will accept connection information rather than reference to a persistantly linked server.

    To call, I need to pass the database path (and possibly the remaining SQL including the destination table & columns and also source data if I decide to make it generic). Obviously, I need to cover myself against SQL Injection (especially if I decide to make the sproc more generic) and speed for the real deal.

    Now I've finished writing I'm not so sure I like it anymore. Feels at least as clumsy as creating, using and destroying a pass through. Ah well - you don't know if you don't try
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •