May 26, 2013 at 3:38 am
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!
May 26, 2013 at 11:07 am
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.
May 26, 2013 at 11:22 am
Where are you trying to use the stored procedure as a data source and how are you specifying it?
May 26, 2013 at 12:33 pm
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
May 26, 2013 at 12:40 pm
My SSIS is rusty, how are you invoking the procedure in SSIS?
May 26, 2013 at 12:57 pm
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.
May 26, 2013 at 1:14 pm
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=?
May 26, 2013 at 1:35 pm
BlackGarlic (5/26/2013)
index portion of code is wrong. It's actuallycreate 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.
May 26, 2013 at 1:46 pm
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 26, 2013 at 9:41 pm
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.
May 26, 2013 at 11:53 pm
@Phil
Actual code has #ID. I made mistake while I was copying the code.
This is the error I am getting:
Exception from HRESULT: 0xC0202009
Error at Data Flow Task [OLE DB Source [18]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0"
Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".
Yes. I am in process of rewriting report query logic to avoid temp table. But I would still like know/learn how to utilize this kind of store proc because we have lot of report written with temp table...
Thanks!
May 27, 2013 at 12:26 am
Before you eliminate the #temp table, take a look at this link and see if it's ideas help solve the #temp table issue.
http://www.sql-server-performance.com/2013/temp-tables-ssis/
As far as the error message, I will tell you that SSIS is notorious for giving messages that lead you nowhere. So, they are of use to a marginal extent, as in CLUEs, but often misleading.
May 27, 2013 at 1:10 am
U can try out with ##temp tables that is (global temp table) use this only u can not find with #temp.
Moreover, u can create physical temp table in tempdb. that is same as #temp table (but u need to create and drop it manually)
May 28, 2013 at 1:29 pm
This is a fairly common issue with SSIS (at least in versions before SSIS 2012 - I haven't yet tested this on 2012). When using a stored proc as a data source, if that sproc uses temp tables at all, chances are you're going to have some issues using it in SSIS. To make a long story short, SSIS will try to validate the upstream data prior to execution, and if the temp table doesn't yet exist, that validation will often fail.
There are a few workarounds to this, none of which are very pretty. The easiest solution is usually to replace the temp table with a physical staging table (one that is persisted in the database), and just trunc/reload this staging table in the sproc.
If you can't change the sproc, you might consider executing the sproc in an Execute SQL Task and load those results into a staging table directly by doing an INSERT INTO <staging table> EXEC <sproc name>.
Hope this helps...
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
May 29, 2013 at 3:48 pm
I got this problem finally resolved. So the trick was in the beginning of the stored proc, I just had to declare all column values like following:
if 1=2
begin
select
CAST(NULL AS nvarchar(60)) Account,
CAST(NULL AS datetime) OrderDate,
What a pain!
Thank you all for your tips and suggestions!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply