Solved

Unanticipated Results when Executing a SQL stored procedure from Access 2010

Posted on 2013-12-02
9
447 Views
Last Modified: 2013-12-07
I’ve created a pass-through query in Access which executes a stored procedure that searches for a string across all tables in my SQL database.  The stored procedure on the SQL server runs as expected with results that show all the tables that contain the value in my search string.  However, when I run that same SP using the pass-through query in Access, the Access “view” returns only one table instead of multiple tables that contain my value.  I know the Access interface behaves differently than SQL, but I have a form which is bound to the search result of the pass-through query and I need the users to view all of the tables which contain their search string.
0
Comment
Question by:cartb4horse
9 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 39690921
did you check the privileges that the access connection have when you run the query across the sql server? This may be a privilege problem
0
 
LVL 1

Author Comment

by:cartb4horse
ID: 39690938
I'm connecting via trust connection to the SQL server as a domain admin so privileges aren't an issue.  There are no error messages and the query executes just fine except it returns only one table.
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39693318
I don't use stored procedures that return multiple recordsets for this very reason.  You can probably get to the other recordsets if you work with the results in code but not when it is bound to a form.

Create a separate sp for each recordset.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:cartb4horse
ID: 39694067
Sorry, I misworded my question and should have said that my form is not bound to the search result, I am using the .Connect and .sql properties in code to list the results in an unbound control, but I think I’m incorrectly using the pass-through query.  I will try your approach and let you know how it goes.
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 500 total points
ID: 39702832
Hi,

I would not create one sp for each result table, the simplest thing is to concatenate the result in the sp using the "UNION ALL" method or by creating a temp table and copying the wanted result data into it and give that back to Access.

If you use ADO in Access you can also use the MARS setting in the connection string, ADO has methods to jump from one recordset to the next (but unfortunately that feature is marked as deprecated) using only one call to the sp. I would prefer the first way because it is simpler to handle that in Access with a normal recordset query as usual.

Cheers,

Christian
0
 
LVL 1

Author Comment

by:cartb4horse
ID: 39703370
Christian,

Thanks for this.  I changed my method and decided to not use a pass-through query and just call the SP from SQL using VBA:
   'Stored procedure + parameters called from Form
    strSQL = "Exec sqlsp_searchalltables " & Me.txtTables & _
       ", " & "'%" & Me.txtSearchTerm & "%'"

Open in new window

But I think I can now still use your suggestion and use the UNION ALL method in my SP to concatenate the results.  Not sure how to do that, but I'll post a new question if I get stuck.

Darlyne
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39703391
Hi,

if you use "Execute" in a SQL string you can't use DAO and you must use ADO, that means: It is also a "pass-through" query as you are working with T-SQL and not with Access SQL...;-)

So there would be no difference if you used a "real" pass-through query or ADO, it's both T-SQL. PT Queries are read-only, with ADO you can also write to the result if needed.

A "UNION ALL" query means that you must have the exact number of columns in both (all) SELECTs which are part of the query. For example:

SELECT a,b,c FROM MyTable WHERE ....
UNION ALL
SELECT x,y,z FROM MyOtherTable WHERE....

Open in new window


They also should have the same datatype in the corresponding columns (here: a and x, b and y, c and z) or you must cast them to the same datatype.

Cheers,

Christian
0
 
LVL 1

Author Comment

by:cartb4horse
ID: 39703734
Thanks for the additional information on pass-through queries, I didn't know the correct terminology.  I am using ADO, but I may have a problem using the UNION ALL because although my column count can be equal, I not sure I can have the same datatypes.  I'll check.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39703792
Hi,

the datatype can be cast to the wanted one, any datatype can be cast as a string for example.

If all other methods fail you could also add an additional parameter to your sp which you can use to only output the wanted return table. In this case you would need to call the same sp as often as needed with a different parameter so that the code in the sp remains in one place but the result set can be switched using this additional parameter.

Cheers,

Christian
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now