May 29, 2013 at 4:01 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply