Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dlh
Starting Member
28 Posts |
 Posted - 2010-05-27 : 17:26:05
|
| I've written a multi-step query with multiple temporary tables & select statements. It works fine when run from Microsoft SQL Server Management Studio, returning the result of the final SELECT statement. I'd like to enclose the query in a familiar wrapper for easy dissemenation, ideally in a Excel file.I've set up an Excel sheet and, with VBA and ADO, I can retrieve the results of a simple SELECT-query. But my complicated query is returning nothing.Is there some limitation I'm not understanding? Is it possible to use a procedural T-SQL query employing multiple select statements in the same way as simple SELECT-query? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-05-27 : 17:32:22
|
| Why not just put it into a stored procedure? |
 |
|
|
dlh
Starting Member
28 Posts |
Posted - 2010-05-27 : 17:37:23
|
| I'm not allowed to actually write anything to the database, so stored procedures aren't possbile.I'm trying to get around the fact that the users who want to run the query have read permission on the database, but do not have access to the Management Studio software. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-05-27 : 21:07:07
|
| The only suggestion I have is to investigate an Excel VBA macro. You can use ADO (ActiveX Data Objects) to run your entire statement and retrieve the results into a RecordSet object. From there you can copy the results to an Excel Range (I believe the method is called CopyFromRecordset but it's been a few years for me). It's a little more work but should overcome the limitations of Excel's built-in data retrieval methods. You may have to work with or around Excel's macro security settings. |
|
|
|
dlh
Starting Member
28 Posts |
Posted - 2010-05-28 : 17:37:30
|
| I very much appreciate the reply. That is exactly what I've attempted to do. I have successfully written a VBA subroutine to connect to the database, get a Recordset, assign that Recordset to a QueryTable, and then refresh that QueryTable to show the results. This works with a basic SQL statement (SELECT * FROM MyTable). But my multi-stage query is not working using the exact same setup. Maybe the difference has something to do with procedural T-SQL, but that's just a guess. |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-05-28 : 20:56:16
|
| add set nocount on at the beginning of your query and see if that does that the trick.JimEveryday I learn something that somebody else already knew |
|
|
|
dlh
Starting Member
28 Posts |
Posted - 2010-06-01 : 14:14:54
|
| Thank you! SET NOCOUNT ON was the concept I was missing.Is there some reason why this wasn't necessary with the single SELECT statement? |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-06-01 : 14:27:06
|
| I believe it's because ff your multi-stage query is updating or inserting records, without the SET NOCOUNT ON, each update/insert will produce a message of "X rows affected" AFTER your final select statement, which "breaks your recordset. If you get the error message of "Can't perform operation on a closed recordset" this is usually the cause.JimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
|
|
wrapper for complicated T-SQL query