Invalid object name in subquery

Hi
I have this problem.
In Query Analyzer when I write
dbo.spParseArray '12-13-14','-'
everytihng goes OK
but when i write
SELECT * FROM tblOrder WHERE ID_ORDER IN (dbo.spParseArray ('12-13-14','-'))
it returns me an error
Server: message 208, level 16, state 1, row 1
Invalid object name 'dbo.spParseArray'
What's the matter?Hi
Stored Procedures cannot be used as in line functions.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"xxx" wrote:

> Hi
> I have this problem.
> In Query Analyzer when I write
> dbo.spParseArray '12-13-14','-'
> everytihng goes OK
> but when i write
> SELECT * FROM tblOrder WHERE ID_ORDER IN (dbo.spParseArray ('12-13-14','-'
))
> it returns me an error
> Server: message 208, level 16, state 1, row 1
> Invalid object name 'dbo.spParseArray'
> What's the matter?
>
>|||Is dbo.spParseArray a Stored Proc or a UDF? If it's a Stored Proc, you canno
t
use the result set of a Stored Proc in a Select that way, you have to
"insert" the result set into a Table of some kind, (Real tbale, Temp Table,
Table Variable) and then use that table object in your Select.
IOr, rewrite the Stored Proc as a Table-Valued User Defined Function, then
you can write your select as either:
SELECT * FROM tblOrder
WHERE ID_ORDER IN
(Select OrderID
From dbo.spParseArray ('12-13-14','-'))
Or:
SELECT * FROM tblOrder O
Join dbo.spParseArray ('12-13-14','-') A
On A.OrderID = O.ID_Order
"xxx" wrote:

> Hi
> I have this problem.
> In Query Analyzer when I write
> dbo.spParseArray '12-13-14','-'
> everytihng goes OK
> but when i write
> SELECT * FROM tblOrder WHERE ID_ORDER IN (dbo.spParseArray ('12-13-14','-'
))
> it returns me an error
> Server: message 208, level 16, state 1, row 1
> Invalid object name 'dbo.spParseArray'
> What's the matter?
>
>

Invalid object name in stored procedure

-- In SQL Server 2000
--When run from Query Analyzer I correctly get identification of line
numbers having duplicate values of SKU_NameUsedBySCS:
-- LineNumber1 LineNumber2
-- 2 5
-- but when I try to create a stored procedure having this same code I get:
-- Invalid object name '#x'.
IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
begin
create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
insert into PermTable VALUES (11, 'name23')
insert into PermTable VALUES (11, 'name81')
insert into PermTable VALUES (11, 'name27')
insert into PermTable VALUES (11, 'name88')
insert into PermTable VALUES (11, 'name81')
end
declare @.SCS_ID int
set @.SCS_ID =11
set nocount on
select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x from
PermTable WHERE SCS_ID =@.SCS_ID
go
select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
drop table #x
gohello steve, did you check previous error messages? is you database Case
Sensitive? If it is, then the select into statement must have the names of
your fields in lower case.
hope this helps.
"SteveInSC" wrote:

> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go
>
>|||Hi
And where do you create the temporary table #x?
John
"SteveInSC" wrote:

> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go
>
>|||At the time the proc is compiled the #x table does not exist as it's
created at runtime. So when you try to compile the code to get an
execution plan the query optimiser cannot create a plan involving #x
because it doesn't yet exist.
Try creating the temp table explicitly in the proc and then inserting
into it with a normal INSERT statement (rather than SELECT ... INTO).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
SteveInSC wrote:

>-- In SQL Server 2000
>--When run from Query Analyzer I correctly get identification of line
>numbers having duplicate values of SKU_NameUsedBySCS:
>-- LineNumber1 LineNumber2
>-- 2 5
>-- but when I try to create a stored procedure having this same code I get:
>-- Invalid object name '#x'.
>
>IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
>declare @.SCS_ID int
>set @.SCS_ID =11
>set nocount on
>select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x from
>PermTable WHERE SCS_ID =@.SCS_ID
>go
>select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
>from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
>drop table #x
>go
>
>
>|||Hi Steve
If you have simply created the stored procedure by wrapping the SQL below in
a create procedure call then you have a GO in the middle of the declaration.
This will terminate the declaration.
Query Analyser will then try to run the later commands as immediate
commands. As the table is created by the SELECT ... INTO inside the
definition it will not find it for the later SELECT statement.
Try removing the GO statement in the middle of the declaration if there is
one.
As a separate point I would recommend using a Table variable (you know the
structure you want) as the scope is much better defined.
I hope this helps
Alasdair Russell
"SteveInSC" wrote:

> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go
>
>|||Comment out the first "GO" and you will be all set.
Try this:
create procedure sp_abc as
IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
begin
create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
insert into PermTable VALUES (11, 'name23')
insert into PermTable VALUES (11, 'name81')
insert into PermTable VALUES (11, 'name27')
insert into PermTable VALUES (11, 'name88')
insert into PermTable VALUES (11, 'name81')
end
declare @.SCS_ID int
set @.SCS_ID =11
set nocount on
select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x
from
PermTable WHERE SCS_ID =@.SCS_ID
-- ############## MySQLServer ############ --go
select top 40 min(Sequence) as LineNumber1, max(Sequence) as
LineNumber2
from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
drop table #x
go
SteveInSC wrote:
> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go|||You forgot to remove the "GO" before the "SELECT TOP 40".
Razvan

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

Interpreting the execution plan results

On Query Analyzer..
I can figure out a little what the information on the execution plan means
but I’m not sure
Where can I find a good description about each item that is show in the
execution plan?
e.g estimated subtree cost etc..
thks
Kenny M. (KennyM@.discussions.microsoft.com) writes:
> On Query Analyzer..
> I can figure out a little what the information on the execution plan means
> but I'm not sure
> Where can I find a good description about each item that is show in the
> execution plan?
>
> e.g estimated subtree cost etc..
SQL Server Books Online. Optimizing Database Performance->Query Tuning->
Analyzing a Query->Logical and Physical Operators.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx