Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stored Proc with temp table issue. Expand / Collapse
Author
Message
Posted Sunday, May 26, 2013 3:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 19, 2014 11:32 AM
Points: 43, Visits: 289
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 .

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!
Post #1456847
Posted Sunday, May 26, 2013 11:07 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:20 AM
Points: 605, Visits: 1,412
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.



it helps to talk it out
Post #1456876
Posted Sunday, May 26, 2013 11:22 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 23,049, Visits: 31,575
Where are you trying to use the stored procedure as a data source and how are you specifying it?



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)
Post #1456878
Posted Sunday, May 26, 2013 12:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 19, 2014 11:32 AM
Points: 43, Visits: 289
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
Post #1456896
Posted Sunday, May 26, 2013 12:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 23,049, Visits: 31,575
My SSIS is rusty, how are you invoking the procedure in SSIS?



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)
Post #1456897
Posted Sunday, May 26, 2013 12:57 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:20 AM
Points: 605, Visits: 1,412

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.


it helps to talk it out
Post #1456902
Posted Sunday, May 26, 2013 1:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 19, 2014 11:32 AM
Points: 43, Visits: 289
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=?


Post #1456907
Posted Sunday, May 26, 2013 1:35 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 23,049, Visits: 31,575
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.



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)
Post #1456910
Posted Sunday, May 26, 2013 1:46 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 4,983, Visits: 11,675
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1456913
Posted Sunday, May 26, 2013 9:41 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:20 AM
Points: 605, Visits: 1,412
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.

it helps to talk it out
Post #1456959
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse