April 21, 2014 at 12:18 am
OK,
Found it!!!
I have just been following this thread because I have the same problem as the poster. I stripped my failing proc right down to doing nothing other than populating a #temp table, then selecting * from the #temp table. FAIL. Tried changing to a table variable. FAIL.
Just selecting the records without putting them into the temp table works.
Then I found this article - http://support.microsoft.com/kb/235340/en-us
In a nutshell, after loading all the params into your cmd object, you stick...
rst.ActiveConnection = cn
rst.Open "SET NOCOUNT ON"
*before* your Set rst = cmd.Execute() statement, and wullah! Problem solved. Pleeeeze tell me this works for you also.
Cheers,
Dave
April 21, 2014 at 9:59 am
Oh very cool. Thank you Stan!
April 22, 2014 at 12:29 am
You're welcome Lisa. While I'm here I might be able to save you or anyone else who gets this far and then battles with wondering why they can't use MoveFirst or MoveLast even when they open their recordset with adOpenKeyset.
First off, adOpenKeyset may not be a constant in VBA, so you should check or just use 3. The other problem is that it's defaulting to a server-side cursor in which you can't do anything other than move row by row. You need a (duh!) client-side (Excel) cursor so you can use MoveFirst and MoveLast. Set your CursorLocation = 3 as below. And lastly, if you want to transfer the recordset to your spreadsheet easily, create a range and use CopyFromRecordset. Here's my proc to get a recordset from a SQL Server 2005 proc and populate a dashboard with the history data and then the values from the current month (the last row in the dataset)...
'----------------------------------------------------------------------------------------------------------------
Public Sub GetUsageData(asDateFrom As String, asDateTo As String, aiRepGroup As Integer, asDatePeriod As String)
'----------------------------------------------------------------------------------------------------------------
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim prm As ADODB.Parameter
Dim rng As Excel.Range
Dim rowCount As Long
'----------------------------------------------------------------------------------------------------------------
cnn.ConnectionString = C_CONNSTRING
cnn.CursorLocation = 3
cnn.Open
Set rng = Range("B17:E17") 'recordset has 4 columns, so this is the row *under* the titles of the columns where the data will go
cmd.CommandText = C_SQL_SP_USAGE: cmd.CommandType = adCmdStoredProc: cmd.ActiveConnection = cnn
'set the param values
Set prm = cmd.CreateParameter("@ReportDateFrom", adDBDate, adParamInput, , asDateFrom): cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ReportDateTo", adDBDate, adParamInput, , asDateTo): cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@CostCtrStr", adInteger, adParamInput, , aiRepGroup): cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@DatePeriod", adChar, adParamInput, 1, asDatePeriod): cmd.Parameters.Append prm
'
'have to put this in when you use temporary tables
rst.ActiveConnection = cnn
rst.CursorType = 3
rst.Open "SET NOCOUNT ON", cnn, 3
Set rst = cmd.Execute()
'copy data to Excel
Range("B17:E400").ClearContents
rowCount = rng.CopyFromRecordset(rst)
rst.MoveLast
Range("B5").Value = rst.Fields(1).Value
Range("B8").Value = rst.Fields(2).Value
Range("B11").Value = rst.Fields(3).Value
Range("B17:B400").NumberFormat = "MMM yyyy" 'CopyFromRecordset removes the formatting, so put it back
Range("C17:E400").NumberFormat = "###,###" 'ditto
'cleanup
rst.Close
cnn.Close
End Sub
knock yourself out! :Whistling:
Viewing 3 posts - 46 through 47 (of 47 total)
You must be logged in to reply to this topic. Login to reply