SQL Clone
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-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4065 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 Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93133 Visits: 38955
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-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 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 Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93133 Visits: 38955
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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4065 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-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 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 Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93133 Visits: 38955
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
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50527 Visits: 21152
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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4065 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