Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Proc with temp table issue.


Stored Proc with temp table issue.

Author
Message
BlackGarlic
BlackGarlic
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 316
Hi everyone.

I understand that this topic was covered several times on this forum, but I can't seem to find ones matching my issues.... and am hoping to find some solution Sad.

I am trying to use stored proc created for report as a data source and move result into other database table.


Store proc first generate #temp table, then creates #temp noncluster index, then select statement to call data from #temp table (with multiple joins).

store proc sample:

> create table #temp
> index #temp table
> select statement from #temp (multiple joins to other table)



Store proc was developed for report and optimized for data read. However, when I try to use this stored proc as data source, I get 'Invalid Object name #Temp Table' error.



I did some research, but only thing I see is mostly calling data stored in temp table and using temp table for the source. My case is bit different... temp table is used, but it goes through another multiple joins and returns result.

Any suggestions?



Thank you all for reading this!
KoldCoffee
KoldCoffee
SSC Eights!
SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)

Group: General Forum Members
Points: 821 Visits: 1905
Hi BlackGarlic,
First, my advice is that you post this to the T-SQL forum because this one on SqlServerCentral is for Integration services (SSIS) which is not as frequently visited as T-SQL, not to mention its for a different topic.

Some suggestions (by no means am I an expert). But, first, if possible, I would copy/paste the select statement from sproc into another query editor window and execute the create #temp table first. Then I would, piecemeal, execute the other portion of that query up to and including it's first join to the #temp table.

This should reveal the problem. Perhaps the join to the #temp table has a typo. This is valid initial approach that you may have already taken.

I would do it and post the results of that investigation on the T-SQL forum.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24157 Visits: 37926
Where are you trying to use the stored procedure as a data source and how are you specifying it?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
BlackGarlic
BlackGarlic
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 316
When I run stored proc on ssms, it runs fine. Problem occurs when I try to execute the store proc in SSIS package.


Stored Proc is being used in Data Flow Task > OLE DB Source. I selected SQL query as source.


Here is the store proc code sample:


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter procedure dbo.usp_GetBill_Info

@StartDate datetime,
@EndDate datetime,

AS

BEGIN
SET NOCOUNT ON;


create table #ID
(
   ID nvarchar(100),
   TID nvarchar(100)
)


insert into #EID (ID, TEID)

select a1.ID, a2.TID

FROM CustID AS a1
inner JOIN TransactionHeader AS a2


where PV1.DischargeDtTm between @StartDate AND @EndDate2

create NONCLUSTERED INDEX #idx_ID on #TID(ID) include (TID)

-- Start Report

select
   #ID.ID
   ,ID.TID
   ,Account_Status = V1.AccountStatus
,Quantity=f.[Quantity]
,Amount = cast(fin.[Amount] as decimal(38, 2))
   ,Service_Date = fin.[ServiceDate]
   ,fin.BatchDate

from #ID
INNER JOIN Account as v1
on #ID.ID = v1.ID
INNER JOIN financeData AS fin
on #ID.TID = fin.TID

drop table #EID

end
go
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24157 Visits: 37926
My SSIS is rusty, how are you invoking the procedure in SSIS?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
KoldCoffee
KoldCoffee
SSC Eights!
SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)

Group: General Forum Members
Points: 821 Visits: 1905

create NONCLUSTERED INDEX #idx_ID on #TID(ID) include (TID)


hmmm. the create index is for a temp table I don't see referenced anywhere else (I see #ID and #EID)

Can you remove the create index statement from the sproc and try rerunning the bids package.
BlackGarlic
BlackGarlic
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 316
index portion of code is wrong. It's actually

create NONCLUSTERED INDEX #idx_ID on #ID(ID) include (TID).

But I will run it again without index portion.

I an using following sql code in OLE DB Source:

exec dbo.usp_GetBill_Info @StartDate =?, @EndDate=?
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24157 Visits: 37926
BlackGarlic (5/26/2013)
index portion of code is wrong. It's actually

create NONCLUSTERED INDEX #idx_ID on #ID(ID) include (TID).

But I will run it again without index portion.

I an using following sql code in OLE DB Source:

exec dbo.usp_GetBill_Info @StartDate =?, @EndDate=?




And you have defined the parameters and the appropriate variables in the OLE DB component?

I am trying to do something similar using SSIS on my system at home, just not with your proc.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8300 Visits: 19447
You seem to create a temp table called #ID, then INSERT and SELECT from #EID, which is not defined. Have you missed out a section from your code?

Can you possibly post the full text of the error message?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
KoldCoffee
KoldCoffee
SSC Eights!
SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)

Group: General Forum Members
Points: 821 Visits: 1905
I'm not saying you can't execute a sproc as a sql statement but I've never done it. Why don't you just extract the sql out of the sproc for the time being, minus the index and add back pieces one at a time. You can re-parameterize at the very end.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search