SPROC Problems

[Eric D.]

Hi,

I have a stored procedure that makes returns records from
a SELECT statement. This staments is dependant on
information from temp tables I create just before running
the SELECT statement. All these actions are in the same
sproc.

Here's the problem, when I run the sproc through
enterprise manager, the information comes out correctly.
When I run the sproc in Access, Access returrns a message
saying that "The stored procedure ran correctly, but did
not return any records."

I did a little more research on this ommitting the parts
of my sproc in an attempt to pinpoint what was causing the
problem. What I found was that once I try doing an INSERT
before any SELECT, no records are returned in Access.

Now is there something I'm missing, or can you not have an
INSERT and SELECT statement in the same sproc running in
Access?

Here's the code for the sproc:
===========================================
DECLARE @FiscalYear AS VARCHAR(4)
DECLARE @FiscalMonth AS VARCHAR(2)
DECLARE @CurrentYearStart AS DATETIME
DECLARE @CurrentYearEnd AS DATETIME
DECLARE @LastYearEnd AS DATETIME

SET @FiscalMonth = DATEPART(MONTH,GETDATE())
IF @FiscalMonth < 4
SET @FiscalYear = DATEPART(YEAR,GETDATE()) - 1
ELSE
SET @FiscalYear = DATEPART(YEAR,GETDATE())
SET @CurrentYearStart = (SELECT CAST(@FiscalYear + '0401'
AS DATETIME))
SET @CurrentYearEnd = (SELECT CAST(CAST((@FiscalYear + 1)
AS VARCHAR(4)) + '0331' AS DATETIME))
SET @LastYearEnd = (SELECT CAST(@FiscalYear + '0331' AS
DATETIME))

DECLARE @FiscalTempTable TABLE
(
ContractID INT,
FiscalStart SMALLDATETIME,
FiscalEnd SMALLDATETIME,
FiscalGrouping BIT
)

INSERT INTO @FiscalTempTable
SELECT ContractID,
(CASE WHEN...
FROM Contract

DECLARE @TotalsTempTable TABLE
(
ContractID INT,
TotalAmendments MONEY,
TotalPayments MONEY,
CurrentTotalAmendments MONEY,
CurrentTotalPayments MONEY
)

INSERT INTO @TotalsTempTable
SELECT C.ContractID,
(CASE WHEN F....
FROM [HQ\exd188].Contract C LEFT OUTER JOIN
@FiscalTempTable F ON F.ContractID = C.ContractID

SELECT F.FiscalGrouping, L.Line...
ORDER BY F.FiscalGrouping
=====================================

In SQL Query Analyser, this sproc returns 67 records.

What is wrong?

TIA,
Eric

 

[Lyle Fairfield]

When I run the sproc in Access

You definitely are not running the SPROC in Access. So we could guess that
you are using some technology involving Access to direct MS-SQL Server to
call the SPROC. But what? Are you connected with ODBC, ADP? Are you using
OLEDB and ADO from within VBA? Are you using an OLEDB connection? ... the
infamous "Access" connection?
How are you "running" the SPROC?

 

[Eric D.]

My apologies, I should have specified that. I'm running
the sproc staright through Access. I go into the sproc
listing and double click the sproc I want to run. That's
it. Nothing simpler than that.

Regards,
Eric

 

[Vadim Rapp]

Hello Eric:
You wrote in conference microsoft.public.access.adp.sqlserver on Mon, 16
Feb 2004 06:09:42 -0800:


ED> Here's the problem, when I run the sproc through
ED> enterprise manager, the information comes out correctly.
ED> When I run the sproc in Access, Access returrns a message
ED> saying that "The stored procedure ran correctly, but did
ED> not return any records."
....

ED> Now is there something I'm missing, or can you not have an
ED> INSERT and SELECT statement in the same sproc running in
ED> Access?


Put SET NOCOUNT ON in the beginning of your s.p.


Vadim