Retrieve data from SQL stored procedure in MS Excel
-
ok, Lisa. I did exactly what you showed there and I still received the same error message, "Operation is not allowed when the object is closed".
At this point, I believe it is the stored procedure. Would you?
-
it sure looks that way - let's avoid the confusion - post the stored proc so we can look at it
-
Yes, I totally do believe you it's the stored procedure. Sorry for getting off track with the date-conversion possibilities.
As the last poster said, if you want somebody to figure out what's wrong with your sproc, post the sproc. It'll probably turn out to your having to make sure you release, close, or otherwise get rid of resources in an appropriate order.
>L<
-
Ready? Here you go. This is the orig stored proc with the two parameters:
USE Septage
GO
CREATE PROCEDURE dbo.proc_Charge
(
@Start datetime,
@End datetime
)
/*
Object: proc_Charge
Description: Get charges per customer including penalty if any
Usage: proc_Charge @Start, @End
Created: 09/11/2006 Update: 09/27/2006 9:23
*/
AS
BEGIN
SET NOCOUNT ON
DECLARE @StartDte datetime, @Enddte datetime
SELECT @StartDte = CAST (CONVERT (char(8), @Start, 112) AS datetime)
SELECT @EndDte = CAST (CONVERT (char(8), @End, 112) AS datetime)
-- Query if there is any record between that time frame
IF EXISTS (SELECT DischrgSessID FROM tblWasteDischarge WHERE DischrgStartDte BETWEEN @StartDte
AND @EndDte)
BEGIN
DECLARE @Tb table
(
TCustID char(3),
TCustBillCode char(6),
TCustPremCode char(6),
TCustName char(50),
TTruckSzCat tinyint,
TNormal int,
TAfter int,
TWeekend int,
THoliday int,
TImpAmtFilled int,
TImpBottleCleaned int,
TImpFormFilled int
)
DECLARE @TCustID char(3), @TCustBillCode char(6), @TCustPremCode char(6), @TCustName char(50)
DECLARE @TTruckSzCat tinyint, @TNormal int, @TAfter int, @TWeekend int, @THoliday int
DECLARE @TImpAmtFilled int, @TImpBottleCleaned int, @TImpFormFilled int
DECLARE Dischrg_cursor CURSOR FOR
SELECT c.CustID, c.CustBillCode, c.CustPremCode, c.CustName, t.TruckSzCat,
[Business Hr] = COUNT (CASE WHEN d.DischrgTmCat = 1 THEN 1 ELSE NULL END),
[After Hr] = COUNT (CASE WHEN d.DischrgTmCat = 2 THEN 1 ELSE NULL END),
[Weekend] = COUNT (CASE WHEN d.DischrgTmCat = 3 THEN 1 ELSE NULL END),
[Holiday] = COUNT (CASE WHEN d.DischrgTmCat = 4 THEN 1 ELSE NULL END),
[ImpAmtFilled] = COUNT (CASE WHEN d.DischrgImpAmtFilled = 1 THEN 1 ELSE NULL END),
[ImpBottleCleaned] = COUNT (CASE WHEN d.DischrgImpBottleCleaned = 1 THEN 1 ELSE NULL END),
[ImpFormFilled] = COUNT (CASE WHEN m.MnfstImpFormFilled = 1 THEN 1 ELSE NULL END)
FROM tblSepticCustomer c INNER JOIN tblSepticTruck t ON c.CustID = t.CustID
INNER JOIN (tblWasteDischarge d INNER JOIN tblManifest m ON d.DischrgSessID = m.DischrgSessID)
ON t.TruckID = d.TruckID
WHERE d.DischrgStartDte BETWEEN @StartDte AND @EndDte
GROUP BY c.CustID, c.CustBillCode, c.CustPremCode, c.CustName, t.TruckSzCat
ORDER BY c.CustID, t.TruckSzCat
OPEN Dischrg_cursor
FETCH NEXT FROM Dischrg_cursor INTO @TCustID, @TCustBillCode, @TCustPremCode, @TCustName, @TTruckSzCat
, @TNormal, @TAfter, @TWeekend, @THoliday, @TImpAmtFilled, @TImpBottleCleaned
, @TImpFormFilled
WHILE (@@FETCH_STATUS <> -1)
begin
INSERT INTO @Tb (TCustID, TCustBillCode, TCustPremCode, TCustName, TTruckSzCat, TNormal, TAfter
, TWeekend, THoliday, TImpAmtFilled, TImpBottleCleaned, TImpFormFilled)
VALUES (@TCustID, @TCustBillCode, @TCustPremCode, @TCustName, @TTruckSzCat, @TNormal, @TAfter
, @TWeekend, @THoliday, @TImpAmtFilled, @TImpBottleCleaned, @TImpFormFilled)
FETCH NEXT FROM Dischrg_cursor INTO @TCustID, @TCustBillCode, @TCustPremCode, @TCustName, @TTruckSzCat
, @TNormal, @TAfter, @TWeekend, @THoliday, @TImpAmtFilled, @TImpBottleCleaned
, @TImpFormFilled
end
CLOSE Dischrg_cursor
DEALLOCATE Dischrg_cursor
SELECT T.TCustID, T.TCustBillCode, T.TCustPremCode, T.TCustName, T.TTruckSzCat, ts.TruckSzChrgAmt, T.TNormal, T.TAfter, T.TWeekend, T.THoliday
, T.TImpAmtFilled, T.TImpBottleCleaned, T.TImpFormFilled
FROM @Tb T INNER JOIN tblTruckSizeCategory ts ON T.TTruckSzCat = ts.TruckSzCat
ORDER BY T.TCustID, T.TTruckSzCat
END
SET NOCOUNT OFF
END
GO
-
The sproc looks ok and if you say it works, then the problem has to be the parameters - your code is not passing the correct values. Your sproc is looking for @Start and @End to be like 2006-10-02 16:38:22.873
If your code is passing something like "10/2/2006", try making your parameters varchars. You convert them to DateTime values inside yoursproc anyway.
-
Remember earlier I modifed my sproc so that it does not require any parameter input. Instead I hardcoded the parameters inside my sproc just to test.
Here is the modified portion of the sproc while the remaining code is the same:
USE Septage
GO
CREATE PROCEDURE dbo.proc_Charge2
/*
Object: proc_Charge2
Description: Get charges per customer including penalty if any
Usage: proc_Charge2
Created: 09/11/2006 Update: 09/27/2006 9:23
*/
AS
BEGIN
SET NOCOUNT ON
DECLARE @StartDte datetime, @Enddte datetime
DECLARE @Start datetime, @End datetime
SELECT @Start = '9/1/2006'
SELECT @End = '9/30/2006'
SELECT @StartDte = CAST (CONVERT (char(8), @Start, 112) AS datetime)
SELECT @EndDte = CAST (CONVERT (char(8), @End, 112) AS datetime)
....
This still does not work in Excel but it will work in QA. I know what you were saying there, cje...but it really doesn't make sense even after I removed the parameters.
-
OK, look, KC, I am *far* from an expert on the subject of cursors, but there are two things I would consider here that might be different when you run this cursor from an outside connection such as you're creating in Excel:
1) You're using WHILE @@FETCH_STATUS <> -1.
I know I have seen that in examples, too, but I don't use it, because there is also a -2 possible status. So I use WHILE @@FETCH_STATUS = 0 instead. This *probably* isn't making any difference, unless things are slower from the outside connection in which case I guess the data could be changing out from under you.
Still that wouldn't make it fail every time. The next thing, I think, might be more relevant:
2) @@FETCH_STATUS is global to the connection. Docs say:
Because @@FETCH_STATUS is global to all cursors on a connection, use @@FETCH_STATUS carefully. After a FETCH statement is executed, the test for @@FETCH_STATUS must occur before any other FETCH statement is executed against another cursor. The value of @@FETCH_STATUS is undefined before any fetches have occurred on the connection.
For example, a user executes a FETCH statement from one cursor, and then calls a stored procedure that opens and processes the results from another cursor. When control is returned from the called stored procedure, @@FETCH_STATUS reflects the last FETCH executed in the stored procedure, not the FETCH statement executed before the stored procedure is called.
They go on to suggest "To retrieve the last fetch status of a specific cursor, query the fetch_status column of the sys.dm_exec_cursors dynamic management function." So, without knowing exactly what the connection might be, I would try following this advice. It can't hurt, it's probably good practice, and it might make a difference.
Something is bothering me here about the statement "user executes a FETCH and then calls a stored procedure that opens and processes the results from another cursor". I know that's not exactly what you're doing, if I remember correctly the next item is a temporary table, but who knows what the client/ADO driver stuff is doing under the covers.
Something else that's bothering me is the temporary table, to tell you the truth. You haven't made it global, it's local to the user. Could it be behaving differently on the ADO connection? What happens if you make it a global temporary table (just for testing), instead?
These are probably random ideas, I'm shooting them out in case they spark ideas from somebody else. Meanwhile, I'm going to go take a look at your procedure closely, in case I see a way of doing the same thing *without* the temporary table, which might just make the problem go away.
>L<
-
Some additional information for you: this is really starting to bug me <g>, so I decided to try to repro your conditions.
1) Here is my sproc. It is simpler than yours, leaves out some fields, leaves out one condition at the beginning, and I made that change to compare @@FETCH_STATUS against 0 that I mentioned to you, but otherwise I think you'll agree it's modelled pretty closely on yours, even though I'm using different tables. Cursor, temporary table, and all:
ALTER
PROCEDURE dbo.proc_Charge(
@Start
datetime= '11/1/2005',@End
datetime='1/1/2006')
AS
BEGIN
SET
NOCOUNT ONDECLARE
@StartDte datetime, @Enddte datetimeSELECT
@StartDte = CAST (CONVERT (char(8), @Start, 112) AS datetime)SELECT
@EndDte = CAST (CONVERT (char(8), @End, 112) AS datetime)-- Query if there is any record between that time frame
IF
1=1 --EXISTS (SELECT DischrgSessID FROM tblWasteDischarge WHERE DischrgStartDte BETWEEN @StartDte--AND @EndDte)BEGIN
DECLARE
@Tb table(
TCustID
varchar(30),TCustBillCode
char(8),TNormal
int,TAfter
int)
DECLARE
@TCustID varchar(30), @TCustBillCode varchar(10), @Tnormal int, @TAfter intDECLARE
Dischrg_cursor CURSOR FORSELECT
c.Sales_No, c.Mapics_No,[Business Hr]
= COUNT (CASE WHEN d.Scheduled_ship IS NULL THEN 1 ELSE NULL END),[After Hr]
= COUNT (CASE WHEN d.Recorded_install IS NULL THEN 1 ELSE NULL END)FROM
SOrderHeader c INNER JOIN S2S_OrderDetail d ON c.Sales_No = d.Sales_NoWHERE
c.Requested_Ship BETWEEN @StartDte AND @EndDteGROUP
BY c.Sales_No, c.Mapics_NoOPEN
Dischrg_cursorFETCH
NEXT FROM Dischrg_cursor INTO @TCustID, @TCustBillCode,@TNormal
, @TAfterWHILE
(@@FETCH_STATUS <> -1) begin INSERT INTO @Tb (TCustID, TCustBillCode, TNormal, TAfter) VALUES (@TCustID, @TCustBillCode, @TNormal, @TAfter) FETCH NEXT FROM Dischrg_cursor INTO @TCustID, @TCustBillCode,@TNormal
, @TAfter endCLOSE
Dischrg_cursorDEALLOCATE
Dischrg_cursorSELECT
T.TCustID, T.TCustBillCode,T.TNormal, T.TAfterFROM
@Tb T INNER JOIN S2S_OrderHeader ts ON T.TCustID = ts.Sales_NoORDER
BY T.TCustIDEND
SET
NOCOUNT OFFEND
2) Here is my Excel macro. I did exactly what we talked about.
Sub test() Dim S2S_SERVER_CONNECTSTRING As String ' S2S_SERVER_CONNECTSTRING = you know what goes here...
Dim rst As New ADODB.Recordset Dim conn As New ADODB.Connection myvar = "11/1/2005" With conn .Provider = "SQLOLEDB" .ConnectionString = S2S_SERVER_CONNECTSTRING .Open If conn.State = ADODB.adStateOpen Then Set rst = conn.Execute("proc_Charge @Start='" & myvar & "' ") j = 1 While Not (rst.BOF() Or rst.EOF()) For i = 0 To rst.Fields.Count - 1 ActiveSheet.Cells(j, i + 1).Value = rst.Fields(i).Value Next j = j + 1 rst.MoveNext Wend rst.Close conn.Close End If End With Set conn = Nothing Set rst = Nothing End Sub3) It worked fine, filling the spreadsheet with data -- after I set the permissions so that the sproc was visible to the identity I used in my connection string. When I didn't do that, it failed on the same line you have been complaining about.
Please tell me that's not it?!?!?
>L<
-
after few more trial and error, the results were not encouranging.
here were my test runs i have tried.1) i left out my condition just like you did above. tried on QA and of course, worked great. ran it thru excel macro, bombed with same error message, "operation is not allowed when the object is closed." @ the yellow statement.
j = 1
While Not (rst.BOF() Or rst.EOF())
For i = 0 To rst.Fields.Count - 1
2) left the excel macro the same. this time i modified my sproc and took out the temp table. not using temp table to return the result. queried my result thru the first select statement...the long one. exec on QA and it worked. ran it thru the excel macro and it filled out my spreadsheet with the same result from QA. GREAT! Conclusion: the excel macro works and the sproc is visible to the identity i used in my conn string.
3) left the excel macro untouched. put back the temp table. instead of using local temp table, i modifed it to use global temp table as you suggested, (##). ran thru QA and it worked. ran it thru excel macro and received the same result as in (1). global temp table made no diff.
4) modified the conn string to use "uid" and "pwd" instead of "trusted_connection". just for fun. repeated test (1) thru (3) using this new conn string. only (2) ran successfully. (1) and (3) failed with that same error message.
at this point, i can't think of anything other than leaving out the temp table in my sproc. i haven't tried to shorten my sproc or use a shorter sproc with temp table in it. i guess i will try that next. hopefully, it will get me somewhere...
kc
-
Sorry, KC, I didn't mean to imply that you should leave out the condition as something that was significant to your issues. I did it because I didn't have the same dataset as you have and it got in the way of my reproducing your conditions.
The only way that your condition would be significant would be if you sometimes had dates that yielded no records. I gather that you have already checked for this possibility, by setting literal dates of known values as your arguments. So, again, I didn't mean you should try this, I was just trying to show that I did a scrupulous test by listing all the things I changed <s>.
Also, *after* I made the suggestion about the global temp table, I then did the repro. And, as (I hope) you saw, my repro *included* a temp table. So I really don't see how that could be the issue either.
Meanwhile, I *did* bomb out when my proc didn't have permissions, on the same line as you saw. (Although you say you put in user and password and saw the same results, that is exactly not what I am talking about when I say "gave permissions". I mean I changed the properties on the sproc.)
So... there could be some connection although we have not figured it out.
Here are my suggestions at this time:
1) I do think you can leave out the temporary table. In fact, when I started to do the repro, I wrote the sproc with the idea of seeing "how else could I write this without the cursor and the temporary table". I didn't get that far because the darned thing didn't bomb, but I'm pretty sure you don't need all that code to do what you're doing.
2) I am still wondering if there is some permission that your user identity needs to create that temporary table, which it would not need in a sproc that does NOT create a temporary table, that it doesn't have on the server instance. Have you checked this? (Note: I can't remember whether you are using Windows auth or integrated auth, but if the former, does your user identity need some rights it doesn't have on that server box or in that domain? If the latter, does the SQL Server user have every permission it needs?)
>L<
-
thank you for your diligent help, lisa. i really appreciated.
1) i can afford to leave out the temp table but couldn't understand why it won't work that way. it could be very useful in some way though.
2) when i am using trusted connection / window authentication / integrated security, i used my account. i have domain admin as well as local admin to that box. also, i give myself sysadmin privilege in the sql server. i also verified that i have permission to execute that sproc. when i use the uid and pwd (sql authetication method), that user is the dbo of that database. i also verified that this user can execute the sproc (exec permission).
one thing i don't understand is QA. i have used both identities in QA and both can execute sproc just fine. is QA using OLE DB provider when connect to DB? if yes, then i really need to find the solution.
-
>> is QA using OLE DB provider when connect to DB?
I don't know, somebody else can probably answer this in a second. I do know that QA in Enterprise Mgr (SQL Server 2000) used ODBC, and there were some significant issues with this, depending on the driver, but I don't see how that could enter into anything here.
I guess we *could* look into the connection string a bit further, and see if it matters. Little variations have mattered more, and more often, than I would like to remember <s>. (I don't do that much connecting from Excel, to be honest, but when you set up a linked server, boy, does it matter!)
So... You are doing (from your first posts) something like this:
Const SERVER As String = "MyPC"
svrConn = "Provider=SQLOLEDB;Server=" & SERVER & ";Database=Test;Integrated Security=SSPI"
Set conn = New ADODB.Connection
conn.Open svrConn
... I'm not, I'm doing this:
With conn
.Provider = "SQLOLEDB"
.ConnectionString = S2S_SERVER_CONNECTSTRING
.Open
... where the connection string consists of
"data source=XXX;user id=YYY;password=ZZZ;initial catalog=AAA"
... exactly like that. I don't understand how it could make any difference to state the provider separately, but notice I am using slightly difference syntax altogether than what you are using, beyond the provider.
XXX = your SERVER value, and AAA is your Database value, but the argument names are a bit different. I suppose we might have different SQLClients supporting the SQLOLEDB provider.
>L<
-
point taken. actually, i am using the exact same conn method as you did except my connection string is a little diff from yours. i did check those syntax for ado in msdn and they meant the same.
stConn = "Server=MyPc;Database=XXXX;uid=xxxx;pwd=xxxx"
With conn
.Provider = "SQLOLEDB"
.ConnectionString = stConn
.Open
i did discovered something. you had mentioned earlier that identity might not have permission to create the temp table. so i modified my sproc to use global temp table because global temp table is visible to all sessions and this table will drop when all other tasks have stopped referencing to it.
stConn = "Server=MyPc;Database=XXXX;uid=xxxx;pwd=xxxx"
With conn
.Provider = "SQLOLEDB"
.ConnectionString = stConn
.Open
If conn.State = ADODB.adStateOpen Then
Set rst = conn.Execute(proc_Charge @Start='" & CDate(RangeA.Value) & _
"'" & ", " & "@End='" & CDate(RangeB.Value) & "'")
j = 5
While Not (rst.BOF Or rst.EOF)
For i = 0 To rst.Fields.Count - 1
ActiveSheet.Cells(j, i + 1).Value = rst.Fields(i).Value
Next
j = j + 1
rst.MoveNext
Wend
rst.Close
conn.Close
End If
End With
use same credential and same excel macro. i put two break points. one at the beginning of sub and the other on the statement where it crashed (highlighted brown as above). i stepped thru the macro code and right before that statement, i stopped (j=5). i switched to QA and went to tempdb db to see if that global temp table exist...voila! it did exit.
So, the "set rst..." statement did execute fine because the temp table was created. i went further, i ran a simple select statement against that temp table created from excel macro and it returned the result (select * from ##Tb). switched back to excel, stepped in to the next statement, it crashed. rather interesting! -
Yes, it is interesting, except that I wrote a sproc based on yours with a temp table and it did *not* crash <sigh>.
I just did it again. Using a different server. The client box has an empty worksheet with just the test sub I sent you earlier. The client box's Excel version is 2003, patched up to date, references is to ADO 2.8 library.
The server in this case was SQL Server Developer edition, not patched. The server in the last case was SQL Server express, patched.
Both worked fine.
The only additional thing I can imagine testing is a server in a different domain or something.
I have no doubt that the temp table *does* exist. But you say this:
>>i went further, i ran a simple select statement against that temp table created from excel macro and it returned the result (select * from ##Tb)
... have you tried doing the same thing from another Excel-based client, rather than from the Query Analyzer?
>L<
-
Hold the phone.
>> is QA using OLE DB provider when connect to DB?
I don't know, somebody else can probably answer this in a second. I do know that QA in Enterprise Mgr (SQL Server 2000) used ODBC, and there were some significant issues with this, depending on the driver, but I don't see how that could enter into anything here.
KC, you've been saying "QA" over and over again, and I wasn't thinking, until I was working with somebody else today on a completely different problem...
Are you really using "Query Analyzer", as in "SQL Server 2000 Enterprise Manager"? If so, then please (as I said above) do realize that QA does not use OLE DB. It uses ODBC and there are significant differences between the two.
There might be something very slightly wrong with your syntax which ODBC is forgiving but OLE DB is not. I have no clue what.
And, just btw, although the docs say that different ways of voicing connect string syntax are equivalent; in my experience OLE DB can be *very* finicky about this too.
>L<
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply