Invalid object name

I have a stored procedure that creates several temporary tables. When I call
this procedure from the Query Analyzer, it works just fine.
When I call the stored procedure through the DTS or from a query from the
reporting services, I get the error: Invalid object name '#NSLP'
#NSLP is the first temporary table. Any suggestions will be highly
appreciated.
Code for the stored procedure follows
CREATE PROCEDURE [dbo].[procGetSponsorApprovals]
@.iMonth AS integer
AS
SET NOCOUNT ON
CREATE TABLE dbo.#NSLP ( Sponsor varchar(10),
EligBkfstSevere Integer,
EligBkfst Integer,
EligLunch Integer,
EligSnack Integer
)
INSERT INTO #NSLP
SELECT
B.Sponsor,
Sum(CASE WHEN B.EligBkfstSeverePct > 40 THEN 1 ELSE 0 END) AS
EligBkfstSevere,
Sum(CASE WHEN EligBkfst = 'Regular' or EligBkfst = 'Prov1' or
EligBkfst = 'Prov2' or EligBkfst = 'Prov3' THEN 1 ELSE 0 END) AS EligBkfst,
Sum(CASE WHEN EligLunch = 'Regular' or EligLunch = 'Prov1' or
EligLunch = 'Prov2' or EligLunch = 'Prov3' THEN 1 ELSE 0 END) AS EligLunch,
Sum(CASE WHEN EligSnack = 'Regular' or EligSnack = 'Prov1' or
EligSnack = 'Prov2' or EligSnack = 'Prov3' THEN 1 ELSE 0 END) AS EligSnack
FROM tblSLPAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblSLPAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #DCCenter (Sponsor varchar(10), DCCenters integer)
INSERT INTO #DCCenter
SELECT
B.Sponsor,
Count(*) AS DCCenters
FROM tblDCAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblDCAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #ACCenter (Sponsor varchar(10), ACCenters integer)
INSERT INTO #ACCenter
SELECT
B.Sponsor,
Count(*) AS ACCenters
FROM tblACAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblACAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #SMCenter (Sponsor varchar(10), SMCenters integer)
INSERT INTO #SMCenter
SELECT
B.Sponsor,
Count(*) AS SMCenters
FROM tblSMAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblSMAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #SFCenter (Sponsor varchar(10), SFCenters integer)
INSERT INTO #SFCenter
SELECT
B.Sponsor,
Count(*) AS SFCenters
FROM tblSFAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblSFAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #SSFCenter (Sponsor varchar(10), SSFCenters integer)
INSERT INTO #SSFCenter
SELECT
B.Sponsor,
Count(*) AS SSFCenters
FROM tblSSFAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblSSFAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
SELECT
A.AgreementNo,
A.SponsorName,
CASE WHEN isnull(A.SLP,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SLP],
CASE WHEN isnull(A.DC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever DC],
CASE WHEN isnull(A.AC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever AC],
CASE WHEN isnull(A.SM,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SH],
CASE WHEN isnull(A.FH,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever FH],
CASE WHEN isnull(A.SF,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SF],
isnull((SELECT 'Y'
FROM dbo.fnAdminSLP('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo AND
(Status = 'Approved' OR Status = 'Suspended')),'_') AS [SLP Sponsor],
isnull((SELECT EligLunch
FROM #NSLP
WHERE Sponsor = A.AgreementNo AND
EligLunch > 0 ),0) AS NSLP,
isnull((SELECT EligBkfst
FROM #NSLP
WHERE Sponsor = A.AgreementNo AND
EligBkfst > 0 ),0) AS Brk,
isnull((SELECT EligBkfstSevere
FROM #NSLP
WHERE Sponsor = A.AgreementNo AND
EligBkfstSevere > 0 ),0) AS SevereBrk,
isnull((SELECT EligSnack
FROM #NSLP
WHERE Sponsor = A.AgreementNo AND
EligSnack > 0 ),0) AS ASSnk,
isnull((SELECT 'Y'
FROM dbo.fnAdminDC('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo AND
(Status = 'Approved' OR Status = 'Suspended')),'_')
AS [DC Sponsor],
isnull((SELECT DCCenters
FROM #DCCenter
WHERE Sponsor = A.AgreementNo AND
DCCenters > 0 ),0) AS [DC Centers],
isnull((SELECT 'Y'
FROM dbo.fnAdminAC('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [AC Sponsor],
isnull((SELECT ACCenters
FROM #ACCenter
WHERE Sponsor = A.AgreementNo AND
ACCenters > 0 ),0) AS [AC Centers],
isnull((SELECT 'Y'
FROM dbo.fnAdminFH('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [FH Sponsor],
isnull((SELECT 'Y'
FROM dbo.fnAdminSM('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [SM Sponsor],
isnull((SELECT SMCenters
FROM #SMCenter
WHERE Sponsor = A.AgreementNo AND
SMCenters > 0 ),0) AS [SM Centers],
isnull((SELECT 'Y'
FROM dbo.fnAdminSF('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [SF Sponsor],
isnull((SELECT SFCenters
FROM #SFCenter
WHERE Sponsor = A.AgreementNo AND
SFCenters > 0 ),0) AS [SF Centers],
isnull((SELECT 'Y'
FROM dbo.fnAdminSSF('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [SSF Sponsor],
isnull((SELECT SSFCenters
FROM #SSFCenter
WHERE Sponsor = A.AgreementNo AND
SSFCenters > 0 ),0) AS [SSF Centers],
isnull(Type, '') as Type
FROM tblAgreeData A
WHERE Sponsor <> 0 and AgreementNo NOT like 'OO%'
ORDER BY AgreementNo
GOTry to press the refresh button next to the data source. This will populate
all the fields for you. There are also some other replies to this question
just search for Invalid object in the newsgroup and you should see other
people making suggestions to this. Hope this helps. Let me know if this is
what you are looking for or if you have a different question.
Brendon Schwartz
http://spaces.msn.com/members/brendon
"Ron Sellers" wrote:
> I have a stored procedure that creates several temporary tables. When I call
> this procedure from the Query Analyzer, it works just fine.
> When I call the stored procedure through the DTS or from a query from the
> reporting services, I get the error: Invalid object name '#NSLP'
> #NSLP is the first temporary table. Any suggestions will be highly
> appreciated.
>
> Code for the stored procedure follows
> CREATE PROCEDURE [dbo].[procGetSponsorApprovals]
> @.iMonth AS integer
> AS
> SET NOCOUNT ON
> CREATE TABLE dbo.#NSLP ( Sponsor varchar(10),
> EligBkfstSevere Integer,
> EligBkfst Integer,
> EligLunch Integer,
> EligSnack Integer
> )
> INSERT INTO #NSLP
> SELECT
> B.Sponsor,
> Sum(CASE WHEN B.EligBkfstSeverePct > 40 THEN 1 ELSE 0 END) AS
> EligBkfstSevere,
> Sum(CASE WHEN EligBkfst = 'Regular' or EligBkfst = 'Prov1' or
> EligBkfst = 'Prov2' or EligBkfst = 'Prov3' THEN 1 ELSE 0 END) AS EligBkfst,
> Sum(CASE WHEN EligLunch = 'Regular' or EligLunch = 'Prov1' or
> EligLunch = 'Prov2' or EligLunch = 'Prov3' THEN 1 ELSE 0 END) AS EligLunch,
> Sum(CASE WHEN EligSnack = 'Regular' or EligSnack = 'Prov1' or
> EligSnack = 'Prov2' or EligSnack = 'Prov3' THEN 1 ELSE 0 END) AS EligSnack
> FROM tblSLPAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSLPAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #DCCenter (Sponsor varchar(10), DCCenters integer)
> INSERT INTO #DCCenter
> SELECT
> B.Sponsor,
> Count(*) AS DCCenters
> FROM tblDCAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblDCAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
>
> CREATE TABLE #ACCenter (Sponsor varchar(10), ACCenters integer)
> INSERT INTO #ACCenter
> SELECT
> B.Sponsor,
> Count(*) AS ACCenters
> FROM tblACAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblACAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #SMCenter (Sponsor varchar(10), SMCenters integer)
> INSERT INTO #SMCenter
> SELECT
> B.Sponsor,
> Count(*) AS SMCenters
> FROM tblSMAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSMAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #SFCenter (Sponsor varchar(10), SFCenters integer)
> INSERT INTO #SFCenter
> SELECT
> B.Sponsor,
> Count(*) AS SFCenters
> FROM tblSFAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSFAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
>
> CREATE TABLE #SSFCenter (Sponsor varchar(10), SSFCenters integer)
> INSERT INTO #SSFCenter
> SELECT
> B.Sponsor,
> Count(*) AS SSFCenters
> FROM tblSSFAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSSFAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> SELECT
> A.AgreementNo,
> A.SponsorName,
> CASE WHEN isnull(A.SLP,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SLP],
> CASE WHEN isnull(A.DC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever DC],
> CASE WHEN isnull(A.AC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever AC],
> CASE WHEN isnull(A.SM,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SH],
> CASE WHEN isnull(A.FH,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever FH],
> CASE WHEN isnull(A.SF,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SF],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSLP('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo AND
> (Status = 'Approved' OR Status => 'Suspended')),'_') AS [SLP Sponsor],
> isnull((SELECT EligLunch
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligLunch > 0 ),0) AS NSLP,
> isnull((SELECT EligBkfst
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligBkfst > 0 ),0) AS Brk,
> isnull((SELECT EligBkfstSevere
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligBkfstSevere > 0 ),0) AS SevereBrk,
> isnull((SELECT EligSnack
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligSnack > 0 ),0) AS ASSnk,
> isnull((SELECT 'Y'
> FROM dbo.fnAdminDC('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo AND
> (Status = 'Approved' OR Status = 'Suspended')),'_')
> AS [DC Sponsor],
> isnull((SELECT DCCenters
> FROM #DCCenter
> WHERE Sponsor = A.AgreementNo AND
> DCCenters > 0 ),0) AS [DC Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminAC('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [AC Sponsor],
> isnull((SELECT ACCenters
> FROM #ACCenter
> WHERE Sponsor = A.AgreementNo AND
> ACCenters > 0 ),0) AS [AC Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminFH('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [FH Sponsor],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSM('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SM Sponsor],
> isnull((SELECT SMCenters
> FROM #SMCenter
> WHERE Sponsor = A.AgreementNo AND
> SMCenters > 0 ),0) AS [SM Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSF('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SF Sponsor],
> isnull((SELECT SFCenters
> FROM #SFCenter
> WHERE Sponsor = A.AgreementNo AND
> SFCenters > 0 ),0) AS [SF Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSSF('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SSF Sponsor],
> isnull((SELECT SSFCenters
> FROM #SSFCenter
> WHERE Sponsor = A.AgreementNo AND
> SSFCenters > 0 ),0) AS [SSF Centers],
> isnull(Type, '') as Type
> FROM tblAgreeData A
> WHERE Sponsor <> 0 and AgreementNo NOT like 'OO%'
> ORDER BY AgreementNo
> GO
>|||Have you tried using table variables instead of temp tables? On the surface,
they provide the same functionality, but may be treated differently by RS.
"Ron Sellers" <RonSellers@.discussions.microsoft.com> wrote in message
news:73374365-09D9-41B8-8B35-FD7E7B382264@.microsoft.com...
>I have a stored procedure that creates several temporary tables. When I
>call
> this procedure from the Query Analyzer, it works just fine.
> When I call the stored procedure through the DTS or from a query from the
> reporting services, I get the error: Invalid object name '#NSLP'
> #NSLP is the first temporary table. Any suggestions will be highly
> appreciated.
>
> Code for the stored procedure follows
> CREATE PROCEDURE [dbo].[procGetSponsorApprovals]
> @.iMonth AS integer
> AS
> SET NOCOUNT ON
> CREATE TABLE dbo.#NSLP ( Sponsor varchar(10),
> EligBkfstSevere Integer,
> EligBkfst Integer,
> EligLunch Integer,
> EligSnack Integer
> )
> INSERT INTO #NSLP
> SELECT
> B.Sponsor,
> Sum(CASE WHEN B.EligBkfstSeverePct > 40 THEN 1 ELSE 0 END) AS
> EligBkfstSevere,
> Sum(CASE WHEN EligBkfst = 'Regular' or EligBkfst = 'Prov1' or
> EligBkfst = 'Prov2' or EligBkfst = 'Prov3' THEN 1 ELSE 0 END) AS
> EligBkfst,
> Sum(CASE WHEN EligLunch = 'Regular' or EligLunch = 'Prov1' or
> EligLunch = 'Prov2' or EligLunch = 'Prov3' THEN 1 ELSE 0 END) AS
> EligLunch,
> Sum(CASE WHEN EligSnack = 'Regular' or EligSnack = 'Prov1' or
> EligSnack = 'Prov2' or EligSnack = 'Prov3' THEN 1 ELSE 0 END) AS EligSnack
> FROM tblSLPAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSLPAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #DCCenter (Sponsor varchar(10), DCCenters integer)
> INSERT INTO #DCCenter
> SELECT
> B.Sponsor,
> Count(*) AS DCCenters
> FROM tblDCAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblDCAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
>
> CREATE TABLE #ACCenter (Sponsor varchar(10), ACCenters integer)
> INSERT INTO #ACCenter
> SELECT
> B.Sponsor,
> Count(*) AS ACCenters
> FROM tblACAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblACAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #SMCenter (Sponsor varchar(10), SMCenters integer)
> INSERT INTO #SMCenter
> SELECT
> B.Sponsor,
> Count(*) AS SMCenters
> FROM tblSMAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSMAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #SFCenter (Sponsor varchar(10), SFCenters integer)
> INSERT INTO #SFCenter
> SELECT
> B.Sponsor,
> Count(*) AS SFCenters
> FROM tblSFAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSFAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
>
> CREATE TABLE #SSFCenter (Sponsor varchar(10), SSFCenters integer)
> INSERT INTO #SSFCenter
> SELECT
> B.Sponsor,
> Count(*) AS SSFCenters
> FROM tblSSFAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSSFAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> SELECT
> A.AgreementNo,
> A.SponsorName,
> CASE WHEN isnull(A.SLP,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SLP],
> CASE WHEN isnull(A.DC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever DC],
> CASE WHEN isnull(A.AC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever AC],
> CASE WHEN isnull(A.SM,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SH],
> CASE WHEN isnull(A.FH,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever FH],
> CASE WHEN isnull(A.SF,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SF],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSLP('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo AND
> (Status = 'Approved' OR Status => 'Suspended')),'_') AS [SLP Sponsor],
> isnull((SELECT EligLunch
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligLunch > 0 ),0) AS NSLP,
> isnull((SELECT EligBkfst
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligBkfst > 0 ),0) AS Brk,
> isnull((SELECT EligBkfstSevere
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligBkfstSevere > 0 ),0) AS SevereBrk,
> isnull((SELECT EligSnack
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligSnack > 0 ),0) AS ASSnk,
> isnull((SELECT 'Y'
> FROM dbo.fnAdminDC('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo AND
> (Status = 'Approved' OR Status = 'Suspended')),'_')
> AS [DC Sponsor],
> isnull((SELECT DCCenters
> FROM #DCCenter
> WHERE Sponsor = A.AgreementNo AND
> DCCenters > 0 ),0) AS [DC Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminAC('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [AC Sponsor],
> isnull((SELECT ACCenters
> FROM #ACCenter
> WHERE Sponsor = A.AgreementNo AND
> ACCenters > 0 ),0) AS [AC Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminFH('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [FH Sponsor],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSM('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SM Sponsor],
> isnull((SELECT SMCenters
> FROM #SMCenter
> WHERE Sponsor = A.AgreementNo AND
> SMCenters > 0 ),0) AS [SM Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSF('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SF Sponsor],
> isnull((SELECT SFCenters
> FROM #SFCenter
> WHERE Sponsor = A.AgreementNo AND
> SFCenters > 0 ),0) AS [SF Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSSF('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SSF Sponsor],
> isnull((SELECT SSFCenters
> FROM #SSFCenter
> WHERE Sponsor = A.AgreementNo AND
> SSFCenters > 0 ),0) AS [SSF Centers],
> isnull(Type, '') as Type
> FROM tblAgreeData A
> WHERE Sponsor <> 0 and AgreementNo NOT like 'OO%'
> ORDER BY AgreementNo
> GO
>sql

Invalid Cursor State

Hi,

I have a stored procedure that calls 2 other stored procedures and combines the results into a temporary table. The results of the temporary table is then returned from the stored procedure.

When I execute the stored procedure in Query Analyzer, I get the exact data I want in the correct format - no errors.

When I execute that stored procedure in Omnivex SQL Link 3, I get an "Invalid Cursor State" error.

I did some digging on that error, and found that it could be related to print statements within the stored procedures. I removed all print statements from all 3 stored procedures and the error is still occuring.

Any suggestions?I suggest you post the code for the stored procedure and somebody will help you rewrite it without using a cursor. 99 times out of 100 they aren't necessary and only impede performance.|||Dude...I don't believe they are using a Cursor in the code...

What is Omnivex SQL Link 3 anyway?

EDIT: AHA

http://www.omnivex.com/index.asp

Is there code on the SQL Link side? Or does it act like ODBC...my Guess it's middleware and that's where you're problem resdies|||Good point.

I wonder if the stored proc might be returning spurious information before the final recordset. Maybe a simple SET NOCOUNT ON would solve the problem.|||That is brilliant! The SET NOCOUNT ON/OFF did the trick! Thank you so much!!!!|||"An expert is a person who has made all possible mistakes in a very narrow field." - Niels Bohr

Invalid Column Name does not cause error

Hi,
I have a SQL Server 2000 sproc that selects data into a temporary table.
If certain conditions are met, the sproc then uses a join on the temporary
table to update an other table.
In the join for this update, the join column on the temporary table is named
incorrectly.
This code is called as part of a sequence of calls to hundreds of sprocs.
The thing is the code has never raised an error, the successful execution of
the sproc is logged to another table. I would expect the code to raise
something like
Server: Msg 207, Level 16, State 3, Line ..
Invalid column name 'dddddddd'.
I am aware of deferred name resolution but I think that when SQL Server
compiles the procedure the error should be raised.
Of course I can edit the sproc but the client requires evidence of
malfunction. Any help appreciated.Could you please post the DDL of the offending procedure?
ML|||The table is created with a column rt_pol_num. The join is the non-existent
column num_pol_dcrt. I have called the sproc with the recompile option but
the error is not raised.
BEGIN
SELECT DISTINCT
rt_pol_num = DCAM.num_pol_dcrt
Blah, Blah …
INTO #tdcam_t
FROM dcam_t dcam
WHERE dcam.stge_rec_err_ind <> 'Y' AND
dcam.prces_act_cd IN('ISRT') AND
EXISTS
(
SELECT src_key_vlu_txt
FROM alternate_payee_t
WHERE alternate_payee_t.src_key_vlu_txt = 'DCAM' +
DCAM.num_pol_dcrt +
DCAM.num_cert_dcrt +
DCAM.ctlnum_reg_cntrl_number +
UPPER(REPLACE(CONVERT(CHAR(11), DCAM.dte_alt_mail_eff_dcam, 106),
' ', ''))
)
SELECT @.vErrNum = @.@.ERROR, @.vIsrtRecCount = @.@.ROWCOUNT, @.vStatrcTblNm =
'dcam_t'
IF @.vErrNum <> 0
BEGIN
SELECT @.vErrLocNm = 'cannot select from dcam_t into #tdcam_t',
@.vErrTblNm = 'dcam_t',
@.vStatDsc = 'procedure failed'
GOTO ON_ERROR
END
ELSE
SELECT @.vDropTmp = 'DROP TABLE #tdcam_t'
IF @.vIsrtRecCount > 0
BEGIN
IF @.pWriteTable <> 'N'
BEGIN
UPDATE dcam_t
SET stge_rec_err_ind = 'Y'
WHERE EXISTS
(
SELECT stge_rec_err_ind
FROM #tdcam_t TT
WHERE TT.num_pol_dcrt = dcam_t.num_pol_dcrt
)
SELECT @.vErrNum = @.@.ERROR, @.vUpdtRecCount = @.@.ROWCOUNT, @.vStatrcTblNm =
'dcam_t'
IF @.vErrNum <> 0
BEGIN
SELECT @.vErrLocNm = 'cannot update dcam_t',
@.vErrTblNm = 'dcam_t',
@.vStatDsc = 'procedure failed'
GOTO ON_ERROR
END|||Non-existing Tables are not schema validated. If the parser notifies a
table which exists the columns are checked for existence, but not if
the table isn=B4t present during compilation time (like in your case)
CREATE PROCEDURE Testproc
AS
BEGIN
CREATE TABLE #testtable
(
SomeColumn int
)
Select somenotexsitingcolumn from #testtable
END
--VS
CREATE TABLE testtable
(
SomeColumn int
)
CREATE PROCEDURE Testproc2
AS
BEGIN
Select somenotexsitingcolumn from testtable
END
HTH, Jens Suessmeyer.|||The OP also states there are no errors at execution.
ML|||Hi,
The error is not raised because during compilation the temporary table does
not exist and SQL Server defers examination of the statement until run-time.
@.vIsrtRecCount > 0
is always false and so the code is never entered. If
@.vIsrtRecCount > 0
is true, the error 'Invalid column name' occurs.
Thanks to all who replied.