November 11, 2002 at 4:21 pm
Hi:
Is it legal to combine Action queries with one or more SELECT queries in a single Stored Procedure when the goal is to return a result set to an ADO Command object?
Here is the task:
Using ADO Command object to prepare a recordset to transfer data to an EXCEL spreadsheet.
The stored procedure:
1) creates a table (CREATE TABLE)
2) populates it (INSERT INTO)
3a) IDs min and max dates (SELECT MAX, SELECT MIN)
3b) Modifies it (ALTER TABLE) by adding columns, one column for every month/year in a date range found in 3a. The month/year columns are along the lines of ([11/2002], [12/2002], [1/2004], etc.)
4) Updates it (UPDATE) with calculated values in the added month/year columns
Everything works as expected to this point.
Now I want to close the stored procedure out with a SELECT query, the results of which I want to pass back to an ADO recordset object via the ADO Command Object that executes the stored procedure.
The SELECT query is dynamic SQL. It is dynamic because it uses similar information in its column list as was used to modify the table earlier to add the month/year columns:
ALTER TABLE Afred ADD [11/2002] Money Null, [12/2002] Money Null,...
SELECT [11/2002], [12/2002], [1/2003],...
There are a lot of ways to work around the problem, but I'm surprised I can't do this as I structured it. I can't find anything that says result sets cannot be returned from a stored procedure when the stored procedure uses action queries.
I've run some simple tests and the results appear to prove that I can't pass a result set back to an ADO command object if the command object calls a stored procedure which includes Action queries. I had some concern that the dynamic nature of the SELECT query was a problem, but, I tried to test that idea also. It doesn't seem to be a problem if it's dynamic SQL.
The failure is that the recordset object is closed (.state = 0)
Is the problem that none of the changes by the action queries are committed until the stored procedure completes?
I don't think it will be much help, but I've included my simple test stored procedure.
Any insight on this subject would be appreciated.
Thanks,
JK
Alter Procedure "Awilma"
@intTest INT OUTPUT
As
DECLARE @strSQL AS VARCHAR(50)
CREATE TABLE AwilmaT (RecordID INT Identity NOT NULL PRIMARY KEY, Serial_No VARCHAR(12) NULL)
INSERT INTO AwilmaT (Serial_No) VALUES ( 'Doowah')
SET @intTest = 45
-- SELECT * FROM AwilmaT
SET @strSQL = 'SELECT * FROM AwilmaT'
EXEC(@strSQL)
/* set nocount on */
return
November 11, 2002 at 4:25 pm
Hi jking
The first thing that comes to my mind is that you must commit the changes in your stored procedure before you can select from the data you have updated.
I'm interested in knowing what your results are from this post.
Jody
November 11, 2002 at 4:36 pm
I have done things like this in the past without issue. Can you also post your code where you are using ADO to return the data (please alter any security info first)? Helps to know what you were doing first and might possibly show what you missed.
November 11, 2002 at 4:48 pm
Hi:
I think it's pretty straight-forward, but here it is. I'll never claim I don't make mistakes.
I get the same results with the simple test case published in the original posting.
A couple of other pieces of (hopefully) pertinent information:
1) using Access Data Project (.adp) to develop
2) using SQL Server 7.0. SQL Server 2000 isn't considered a priority at the moment.
3) MDAC is 2.6
------------------------------------------
Private Sub Ok_Click()
Dim rsLocal As ADODB.Recordset
Dim SourceFormName As Form
Dim cmdLocal As ADODB.Command
Dim prmAsOfDate As ADODB.Parameter
Dim prmClass As ADODB.Parameter
Dim strClass As String
Dim strAsOfDate As String
Set SourceFormName = Forms!MaintenanceAmortization
SourceFormName!Status = ""
SourceFormName.Repaint
If Not IsNull(SourceFormName!AsofDate) Then
strAsOfDate = CStr(SourceFormName!AsofDate)
SourceFormName!Status = "Finding all support agreements active as of " & SourceFormName.AsofDate
SourceFormName.Repaint
Else
MsgBox "No Invoices As Of Date specified. Exiting."
GoTo NoDateProvided
End If
strClass = "TimingDesigner"
Set cmdLocal = CreateObject("ADODB.Command")
SourceFormName!Status = "Creating temporary table and retrieving data..."
SourceFormName.Repaint
With cmdLocal
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "SupportRevenue"
Set prmAsOfDate = .CreateParameter("@strAsOfDate", adVarChar, adParamInput, 10, strAsOfDate)
Set prmClass = .CreateParameter("@strClass", adVarChar, adParamInput, 21, strClass)
.Parameters.Append prmAsOfDate
.Parameters.Append prmClass
Set rsLocal = .Execute
End With
If rsLocal.State = 0 Then
GoTo ByPass
End If
SourceFormName!Status = "Creating spreadsheet..."
SourceFormName.Repaint
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = 1
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
oSheet.Name = "Detail"
SourceFormName!Status = "Adding column heads..."
SourceFormName.Repaint
Dim intFieldCounter As Integer, intFieldCount As Integer
intFieldCount = rsLocal.Fields.Count
For intFieldCounter = 0 To (intFieldCount - 1)
oSheet.Cells(1, intFieldCounter + 1) = CStr(rsLocal(intFieldCounter).Name)
Next intFieldCounter
SourceFormName!Status = "Transferring recordset..."
SourceFormName.Repaint
oSheet.Range("A2").CopyFromRecordset rsLocal
Set oExcel = Nothing
Set oBook = Nothing
Set oSheet = Nothing
rsLocal.Close
ByPass:
Set rsLocal = Nothing
' Drop the table
SourceFormName!Status = "Dropping the temporary table..."
SourceFormName.Repaint
With cmdLocal
.CommandType = adCmdText
.CommandText = "DROP TABLE Afred"
.Execute
End With
Set cmdLocal = Nothing
NoDateProvided:
SourceFormName!Status = "Done!"
SourceFormName.Repaint
End Sub
November 11, 2002 at 6:16 pm
Try setting nocount on first thing in the proc.
Andy
November 11, 2002 at 6:37 pm
Hello Andy:
Thank you. That did the trick, but what trick did it do? Looking at the books online for SET NOCOUNT, I don't see anything that tells me why what it failed when NOCOUNT was ON, and why setting this to OFF allows it to succeed.
I'll look through some more material, but I'd like to hear what you have to say.
Thanks again.
JK
November 11, 2002 at 6:40 pm
Correction:
it succeeded with NOCOUNT set to ON.
Sorry for the confusion
JK
November 11, 2002 at 11:09 pm
Hi Andy:
I found the following at the MSKB:
http://support.microsoft.com/default.aspx?scid=kb;en-us;197528
The above document lists two workarounds:
1) SET NOCOUNT ON
or
2) Use the .NextRecordset method
I haven't been able to make the .NextRecordset method work in this context.
I also found a previous post of yours regarding SET NOCOUNT OFF:
'ADO tends not to like it either - not sure its 100% consistent, but I've seen a lot of cases where it misinterprets the nocount message as a recordset with no cols and no rows.'
Sounds like what I ran into.
Anyway, thanks again.
JK
November 12, 2002 at 2:51 am
The reason is that messages can get sent back to the client these messages can be rowcounts, warnings, errors and messages (i.e. print statements.) When ADO receives a rowcount it assumes a recordset therefore to get the next recordset use .nextrecordset. The other option is as has been said to use SET NOCOUNT ON.
You will also find this occurs if you have lots of PRINT statements being returned to the client. ADO halts the execution of the SP until you hit .nextrecordset.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 12, 2002 at 10:45 am
Hi everyone:
OK, I understand. I wasn't successful with the .NextRecordset workaround because I didn't take it far enough. I used the .NextRecordset method only once. If I loop it until rsLocal Is Nothing, while testing for rsLocal.State = 1, it eventually finds a real recordset.
Interesting.
Thank you all very much. I'm not sure I would have found this information on my own. I certainly had no reason to suspect SET NOCOUNT. Once that was known, I found that can cause a number of apparent anomolies.
I've run into some of those anomalies before but didn't take the time to try and run down because they involved unrequired capabilities or the work around was simple enough that it wasn't worth it. Eventually, though, those catch up to you.
Again, thanks everyone.
JK
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply