Temp Tables in Sproc the Culprit?
-
I am using ADO in an Excel module to populate a workbook with a recordset returned from a Sproc...
sSQL = "dbo.usp_MyGDProc"
Set oRs = oCx.Execute(sSQL)
Gives me run-time error 3704 (the object is closed). When I test with sSQL = "dbo.usp_Test", where usp_Test only contains "Select getdate()", it's fine. I have some temp tables in dbo.usp_MyGDProc and I think this is the problem. Is there another way to execute this Sproc from ADO? Do I need to re-write the Sproc? How about table variables? Will those fail also?
J. Moseley
[font="Courier New"]ZenDada[/font]
-
Can you post your procedure code?
Two things you might want to check,
1) Do you have a RETURN statement? If so, try removing it.
2) Change your temp table into a normal table and see if the procedure works. If so, then you'll need to work around the temp table. Maybe using a Function that returns a table, or try the Table variable.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
CREATE PROCEDURE dbo.usp_ManDial_PD_Status_DMM_Rank
@sitevarchar(1),
@EmpLevel int
as
declare @location nvarchar(25)
Set @location = (case @site
when 'a' then 'Austin'
when 'l' then 'Louisville'
Else Null end)
begin
SELECT
RANK=IDENTITY(INT),
Site,
TL_EmpNo,
MGR_Name,
EmpNo,
Coll_Name,
EmpLevel,
DMM_pts
INTO #RANK
FROM dbo.tbl_ManDial_PD_Status_Report_Aggregate
WHERE 1=2 -- always false - force table definition without insert
end
begin
INSERT INTO #RANK
SELECT
Site,
TL_EmpNo,
MGR_Name,
EmpNo,
Coll_Name,
EmpLevel,
DMM_pts
FROM dbo.tbl_ManDial_PD_Status_Report_Aggregate
WHERE Site = @location
AND EmpLevel = @EmpLevel
ORDER BY DMM_pts DESC
end
begin
SELECT
Site,
TL_EmpNo,
MGR_Name,
EmpNo,
Coll_Name,
DMM_pts,
RANK
FROM #RANK
ORDER BY SITE, MGR_NAME, RANK
end
DROP TABLE #RANK
J. Moseley
[font="Courier New"]ZenDada[/font]
-
had a similar problem with temp tables using openquery .Don't know if this will help , but the metadata retrieval phase in the openquery function errors if the sp has temp or table variables , the cheat was to set the SQL to "SET FMTONLY OFF EXEC <proc>" instead of "EXEC <proc>" , wonder if this will work for you
-
Perhaps you just need to put SET NOCOUNT ON at the beginning of the SP. This works for me:
USE Northwind
CREATE PROC p_Rank @EmpId int AS
SET NOCOUNT ON
SELECT Rank = IDENTITY(int),EmployeeID, CustomerId, OrderDate, Freight
INTO #Rank
FROM Orders
WHERE 1 = 2
INSERT #Rank
SELECT EmployeeId, CustomerId, OrderDate, Freight
FROM orders
WHERE EmployeeID = @EmpId
ORDER BY Freight DESC
SELECT Rank, EmployeeID, CustomerId, OrderDate, Freight
FROM #Rank
ORDER BY CustomerID, Rank
DROP TABLE #RankUsing this in Excel:
Sub test()
Dim aConn As New ADODB.Connection
Dim aCmd As New ADODB.Command
Dim aRst As ADODB.Recordset
Dim strServer As String
Dim strConn As String
Dim intEmpID As Integer
strServer = "SQL_SRV_4"
intEmpID = 1
strConn = "Provider=SQLOLEDB;Initial Catalog=Northwind;Data Source=" & _
strServer & ";integrated security = sspi"
aConn.Open strConn
aConn.CursorLocation = adUseClient
With aCmd
.ActiveConnection = aConn
.CommandType = adCmdStoredProc
.CommandText = "p_Rank"
.Parameters.Append .CreateParameter("@EmpID", adInteger, adParamInput, 4, intEmpID)
Set aRst = .Execute
End With
Set aCmd = Nothing
Set aConn = Nothing
Range(Cells(1, 1), Cells(aRst.RecordCount, aRst.Fields.Count)) = Application.Transpose(aRst.GetRows)
Set aRst = Nothing
End Sub--Jonathan
--Jonathan -
I am SO STUPID - it was NOCOUNT!
Thanks!
J. Moseley
[font="Courier New"]ZenDada[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply