August 7, 2014 at 2:20 pm
Hello,
I am in the process of migrating a SQL 2005 box to SQL 2012. I have a bunch of SSIS packages (200+) that run off a master package. I am getting a strange error message in one of the procs that is being executed. I have tried debugging a few things but nothing has worked. I have included the error message and the proc below. I have changed the column names to generic names, but I think it self explanatory. I did not write this proc, but I believe the first select is some kind of placeholder. This is very strange indeed.
Thanks in advance.
ERROR MESSAGE:
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: "The metadata could not be determined because the statement:
'select
cast (null as varchar(50)) as Val1
,cast (null as varchar(50)) as Val2
in procedure 'usp_GenericName' is not compatible with the statement:
'select
p.Val1
,s.Val2
,s.Val3
,p.Val4
,s.Val5'
in procedure 'usp_GenericName'.".
Stored Procedure usp_GenericName
set nocount on;
declare @DataDate datetime
--set @DataDate ='2011-03-23 00:00:00.000'
--this select never executes ... it just lets SSIS know what output to expect
if 1=0
select
cast (null as varchar(50)) as val1
,cast (null as varchar(50)) as val2
,cast (null as varchar(50)) as val3
,cast (null as varchar(50)) as val4
,cast (null as varchar(50)) as val5
,cast (null as money) as val6
,cast (null as money) as val7
,cast (null as money) as val8
,cast (null as money) as val9
,cast (null as varchar(20)) as val10
,cast (null as varchar(50)) as val11
,cast (null as varchar(50)) as val12
,cast (null as varchar(20)) as val13
,cast (null as datetime) as val14
,cast (null as datetime) as val15
,cast (null as varchar(50)) as val16
,cast (null as varchar(50)) as val17
,cast (null as varchar(50)) as val18
select
p.val1
,s.val2
,s.val3
,p.val4 as val4
,s.val5
,p.val6
,p.val7 as val7
,p.val8
,p.val9as val9
,cast(round(p.val10,4) as varchar) val10
,p.val11
--,u.val12
--,uc.[val13] as val13
,l.val14
,cast(round(l.val15,4) as varchar) val15
,l.val16
,l.val17
,l.val18
,l.val19
,l.val20
fromView1 p
joinView2 l on p.val1=l.val1 and p.val1=l.val1
join View3 r on p.val1=r.val1
join View4 i on r.val1=i.val1
join TBL1 m on m.val1=r.val1
join TBL2 sa on sa.val1=m.val1
join TBL3 la on la.val1=sa.val1
join TBL4 u on u.val1=la.val1
where1=1
andp.date1=@DataDate
The are no problems, only solutions. --John Lennon
August 7, 2014 at 2:22 pm
After your SET NOCOUNT ON;, include:
SET FMTONLY OFF;
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
August 8, 2014 at 12:00 am
This behaviour (selecting from temp tables) changed in SSIS 2012. Rather than declaring dummy metadata in your proc, you should use the WITH RESULT SETS option when you run the proc to define the structure of the returned data.
Here's a link that should get you going.
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
August 8, 2014 at 10:23 am
Phil Parkin (8/8/2014)
This behaviour (selecting from temp tables) changed in SSIS 2012. Rather than declaring dummy metadata in your proc, you should use the WITH RESULT SETS option when you run the proc to define the structure of the returned data.Here's a link that should get you going.
Thanks for that Phil, I was unaware before.
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
August 11, 2014 at 7:46 am
Thanks Phil - So what is the standard? Craigs code seemed to work.
The are no problems, only solutions. --John Lennon
August 11, 2014 at 7:57 am
SQLTougherGuy (8/11/2014)
Thanks Phil - So what is the standard? Craigs code seemed to work.
Mine should execute slightly faster because there's no need for SSIS to go meta-data sniffing through the proc. I also think it's more robust, though perhaps others would disagree.
But if you have a working solution that you're happy with ...
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
August 11, 2014 at 10:58 am
SQLTougherGuy (8/11/2014)
Thanks Phil - So what is the standard? Craigs code seemed to work.
I'd recommend going with the new style in 2012+. I haven't had the chance to move up off 08 R2, so the FMTONLY is the standard for 08 and 05, but it's ALWAYS been considered a hack to get the thing working.
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
August 11, 2014 at 1:53 pm
Thanks guys! I appreciate the feedback.
The are no problems, only solutions. --John Lennon
August 11, 2014 at 1:58 pm
Evil Kraig F (8/11/2014)
SQLTougherGuy (8/11/2014)
Thanks Phil - So what is the standard? Craigs code seemed to work.I'd recommend going with the new style in 2012+. I haven't had the chance to move up off 08 R2, so the FMTONLY is the standard for 08 and 05, but it's ALWAYS been considered a hack to get the thing working.
Another vote for going with the 2012 method.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply