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 11:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 10:03 AM
Points: 43, Visits: 295
@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".

@KoldCoffee

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!

Post #1456972
Posted Monday, May 27, 2013 12:26 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 8:10 AM
Points: 656, Visits: 1,533
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.
Post #1456976
Posted Monday, May 27, 2013 1:10 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:52 AM
Points: 1,608, Visits: 375
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)

Post #1456987
Posted Tuesday, May 28, 2013 1:29 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:46 PM
Points: 1,045, Visits: 2,725
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, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #1457496
Posted Wednesday, May 29, 2013 3:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 10:03 AM
Points: 43, Visits: 295
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!
Post #1457979
Posted Wednesday, May 29, 2013 4:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:48 PM
Points: 6,256, Visits: 7,441
I was about to suggest that then realized you'd found your answer as I read down.

There are two things you always do for any stored procedure that's more complex than a single statement. IE: If you can't use it as an iTVF, make this your default:

SET NOCOUNT ON;
SET FMTONLY OFF;

WHILE 0=1
BEGIN

-- SELECT STATEMENT with CONVERT NULL and column names to set the return metadata.

END

Here's why:

1) You don't want multiple results to return to SSIS, so you turn off recordcount. It doesn't foul things up as much as it used to but it's good practice anyway.

2) FMTONLY: Short version, it doesn't 'high speed' through the proc trying to figure out the metadata, it actually compiles. It avoids missing #tmp errors and the like.

3) SELECT with structure first: This is because procs don't carry metadata for the return set, so SSIS has to go in and make some assumptions. The assumption it makes is that it goes to the first SELECT statement, and grabs that metadata. Even if it's an impossible if tree, that is the only SELECT metadata it will take. By front loading it, you always avoid any confusion from further down in the process.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1457982
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse