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»»

There is a data source column with no name.Each data source column must have a name. Expand / Collapse
Author
Message
Posted Monday, June 16, 2008 1:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 21, 2012 12:06 PM
Points: 111, Visits: 179
Hi every body,
I am trying to get data in SSIS dataflow task from a strore procedure, but strangly i m getting this error

"Error at Data Flow Task [OLE DB Source [1]]: There is a data source column with no name. Each data source column must have a name.
Exception from HRESULT: 0xC0207016"

However i have checked the output of my store proecdure and every column has a name, there isnt any coulmn, with no name, but the SP is complex having some case satatement and column alias...but surely every column has name.
I wonder how to resolve this issue, i cant find any solution on web, i wonder wat is the problem and how to resolve this issue.
Any help in this connection is highly appreciated.
Post #517380
Posted Monday, June 16, 2008 3:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, January 6, 2011 6:44 AM
Points: 206, Visits: 363
Hi

Have you tried setting the NOCOUNT to ON in the beginning of your SP?
This could prevent extra result sets from interfering with SELECT statements

SET NOCOUNT ON
Post #517411
Posted Monday, June 16, 2008 3:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 21, 2012 12:06 PM
Points: 111, Visits: 179
yes SET NOCOUNT is ON in the beginnig of SP,
let me paste the code here.........

/****** Object: StoredProcedure [dbo].[usp_Inv_Lot_Ship_History_2201s] Script Date: 06/16/2008 02:40:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_Inv_Lot_Ship_History_2201s]
AS
/*
** This proc replaces the view Inv_Lot_Ship_History_2201s.
**
** Version History
** 1 04/20/2007 kchong Initial Version
** 2 05/27/2008 bsmet Add time restriction
**
*/
if (datepart(hh, getdate()) not between 12 and 24)
begin
select 'Error - Running outside allowed time window'
select 1/0
return
end

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

CREATE TABLE #PSXLATITEM (
FIELDNAME varchar(18),
FIELDVALUE varchar(4),
EFF_STATUS varchar(1),
XLATLONGNAME varchar(30)
)

INSERT INTO #PSXLATITEM
SELECT
FIELDNAME,
FIELDVALUE,
EFF_STATUS,
XLATLONGNAME
FROM SPOT_RAW..PSXLATITEM


CREATE INDEX IDX_PSXLATITEM ON #PSXLATITEM (FIELDNAME, FIELDVALUE, EFF_STATUS, XLATLONGNAME)


CREATE TABLE #PS_EN_BOM_COMPS (
INV_ITEM_ID varchar(18),
COMPONENT_ID varchar(18),
DATE_IN_EFFECT datetime,
DATE_OBSOLETE datetime)

INSERT INTO #PS_EN_BOM_COMPS
SELECT
INV_ITEM_ID,
COMPONENT_ID,
CONVERT(VARCHAR(10),DATE_IN_EFFECT,101),
CONVERT(VARCHAR(10),DATE_OBSOLETE,101)
FROM
SPOT_RAW..PS_EN_BOM_COMPS


CREATE INDEX IDX_PS_EN_BOM_COMPS ON #PS_EN_BOM_COMPS (INV_ITEM_ID, DATE_IN_EFFECT, DATE_OBSOLETE)


CREATE TABLE #PS_INV_ITEMS
(
SETID varchar(5),
INV_ITEM_ID varchar(18),
EFFDT datetime,
INV_ITEM_TYPE varchar(5)
)

INSERT INTO #PS_INV_ITEMS
SELECT
SETID,
INV_ITEM_ID,
EFFDT,
INV_ITEM_TYPE
FROM SPOT_RAW..PS_INV_ITEMS F
WHERE
F.SETID='SHARE' AND (F.INV_ITEM_TYPE='CHIP' OR F.INV_ITEM_TYPE='TRNKY')

CREATE INDEX IDX_PS_INV_ITEMS ON #PS_INV_ITEMS (INV_ITEM_ID)
CREATE INDEX IDX_PS_INV_ITEMS1 ON #PS_INV_ITEMS (SETID,INV_ITEM_ID,EFFDT)

SELECT
A.BUSINESS_UNIT,
A.DEMAND_SOURCE,
A.SOURCE_BUS_UNIT,
A.ORDER_NO,
A.ORDER_INT_LINE_NO,
A.SCHED_LINE_NO,
A.INV_ITEM_ID,
A.DEMAND_LINE_NO,
A.INV_LOT_ID,
A.DT_TIMESTAMP,
SH.SHIP_CUST_NAME1,
SH.CUST_NAME,
SH.PARENT_PROD_ID,
SH.PRODUCT_ID
INTO #A_SH
FROM SPOT_RAW..PS_DEMAND_PHYS_INV A with (nolock)
INNER JOIN
SPOT_RAW..PS_SHIP_INF_INV SH with (nolock)
ON A.BUSINESS_UNIT=SH.BUSINESS_UNIT
AND A.DEMAND_SOURCE=SH.DEMAND_SOURCE
AND A.SOURCE_BUS_UNIT=SH.SOURCE_BUS_UNIT
AND A.ORDER_NO=SH.ORDER_NO
AND A.ORDER_INT_LINE_NO=SH.ORDER_INT_LINE_NO
AND A.SCHED_LINE_NO=SH.SCHED_LINE_NO
AND A.INV_ITEM_ID=SH.INV_ITEM_ID
AND A.DEMAND_LINE_NO=SH.DEMAND_LINE_NO
AND SH.SHIPPED_FLAG='Y'
where A.DEMAND_SOURCE='OM'


CREATE INDEX IDX_A_SH ON #A_SH (INV_ITEM_ID, INV_LOT_ID)


DECLARE @DATE VARCHAR(10)
SET @DATE = CONVERT(VARCHAR(10),GETDATE(),101)


SELECT
A.INV_ITEM_ID AS "BRCM Item",
B.MODEL_NBR AS "Marketing Part",
RIGHT(A.INV_LOT_ID,9) AS "Lot ID",
LEFT(A.INV_LOT_ID,6) AS "Date Code",
SUBSTRING(A.INV_LOT_ID,3,4) AS "Datecode YYWW",
A.ORDER_NO AS "Order No",
A.ORDER_INT_LINE_NO AS "Order Line",
A.SHIP_CUST_NAME1 AS "ShipTo Name",
A.CUST_NAME AS "SoldTo Name",
"End Cust Name" =
ISNULL((SELECT max(CU.NAME1)
FROM
SPOT_RAW..PS_BRC_ORD_LN_CUST BOL,
SPOT_RAW..PS_SET_CNTRL_REC SI,
SPOT_RAW..PS_CUSTOMER CU
WHERE A.SOURCE_BUS_UNIT = BOL.BUSINESS_UNIT
AND A.ORDER_NO = BOL.ORDER_NO
AND A.ORDER_INT_LINE_NO = BOL.ORDER_INT_LINE_NO
AND BOL.BUSINESS_UNIT=SI.SETCNTRLVALUE
AND SI.REC_GROUP_ID='FS_23'
AND SI.RECNAME='INV_CUST_ADR_VW'
AND SI.SETID=CU.SETID
AND BOL.BRC_CUST_ID_LVL1=CU.CUST_ID),'N/A'),
"End-End Cust Name" =
ISNULL((SELECT max(CU.NAME1)
FROM
SPOT_RAW..PS_BRC_ORD_LN_CUST BOL,
SPOT_RAW..PS_SET_CNTRL_REC SI,
SPOT_RAW..PS_CUSTOMER CU
WHERE A.SOURCE_BUS_UNIT = BOL.BUSINESS_UNIT
AND A.ORDER_NO = BOL.ORDER_NO
AND A.ORDER_INT_LINE_NO = BOL.ORDER_INT_LINE_NO
AND BOL.BUSINESS_UNIT=SI.SETCNTRLVALUE
AND SI.REC_GROUP_ID='FS_23'
AND SI.RECNAME='INV_CUST_ADR_VW'
AND SI.SETID=CU.SETID
AND BOL.BRC_CUST_ID_LVL2=CU.CUST_ID),'N/A'),
"Customer Product ID" =
ISNULL((SELECT max(KIT.CUSTOMER_ITEM_NBR)
FROM
SPOT_RAW..PS_BRC_ORD_PRODKIT KIT
WHERE A.SOURCE_BUS_UNIT = KIT.BUSINESS_UNIT
AND A.ORDER_NO = KIT.ORDER_NO
AND A.ORDER_INT_LINE_NO = KIT.ORDER_INT_LINE_NO
AND A.PARENT_PROD_ID=KIT.PRODUCT_ID
AND A.PRODUCT_ID=KIT.PROD_COMPONENT_ID),OL.CUSTOMER_ITEM_NBR),
C.ITEM_FIELD_C4 as "P-Code",
"Die Item"=
ISNULL((SELECT max(BOM.COMPONENT_ID)
FROM #PS_EN_BOM_COMPS BOM
WHERE
A.INV_ITEM_ID=BOM.INV_ITEM_ID
AND CONVERT(VARCHAR(10),BOM.DATE_IN_EFFECT,101) <= @DATE
AND CONVERT(VARCHAR(10),BOM.DATE_OBSOLETE,101) > @DATE
AND 1=(SELECT COUNT(*) FROM #PS_EN_BOM_COMPS BOM2
WHERE A.INV_ITEM_ID=BOM2.INV_ITEM_ID
AND CONVERT(VARCHAR(10),BOM.DATE_IN_EFFECT,101) <= @DATE
AND CONVERT(VARCHAR(10),BOM.DATE_OBSOLETE,101) > @DATE )),
J.BRC_PART_DIE),
C.ITEM_FIELD_C10_C AS "Core Part",
D.BRC_FAB_ID AS "Fab Lot ID",
D.COUNTRY_ORIGIN AS "COO",
G.BRC_A_VEND AS "Assy Vendor ID",
H.VNDR_NAME_SHRT_USR AS "Assy Vendor",
G.BRC_F_VEND AS "Fab Vendor ID",
I.VNDR_NAME_SHRT_USR AS "Fab Vendor",
XPROCESS.XLATLONGNAME as "Fab Process",
XPHASE.XLATLONGNAME as "Item Phase",
XPKGTYP.XLATLONGNAME as "Package Type",
XPKGSIZ.XLATLONGNAME as "Package Size",
J.BRC_PKG_PIN_COUNT as "Pin Count",
F.INV_ITEM_TYPE as "Item Type",
A.DT_TIMESTAMP
FROM
#A_SH A
LEFT OUTER JOIN
SPOT_RAW..PS_BRC_LOT_CNTL D
ON A.INV_ITEM_ID=D.INV_ITEM_ID
AND A.INV_LOT_ID=D.INV_LOT_ID
INNER JOIN
#PS_INV_ITEMS F
ON A.INV_ITEM_ID=F.INV_ITEM_ID
INNER JOIN
SPOT_RAW..PS_PROD_ITEM B
ON A.INV_ITEM_ID=B.INV_ITEM_ID
AND B.SETID='SHARE'
INNER JOIN
SPOT_RAW..PS_BRC_ITEM_TBL J
ON B.SETID=J.SETID
AND B.INV_ITEM_ID=J.INV_ITEM_ID
INNER JOIN
SPOT_RAW..PS_MASTER_ITEM_TBL C
ON A.INV_ITEM_ID=C.INV_ITEM_ID AND C.SETID='SHARE'
LEFT OUTER JOIN
#PSXLATITEM AS XPHASE
ON C.ITEM_FIELD_C1_C=XPHASE.FIELDVALUE
AND XPHASE.FIELDNAME='ITEM_FIELD_C1_C'
AND XPHASE.EFF_STATUS='A'
INNER JOIN
SPOT_RAW..PS_BRC_DC_PREFX G
ON SUBSTRING(A.INV_LOT_ID,1,2)= G.BRC_DC_PREFIX
AND G.SETID='SHARE'
LEFT OUTER JOIN
SPOT_RAW..PS_VENDOR H
ON G.BRC_A_VEND_SETID=H.SETID
AND G.BRC_A_VEND=H.VENDOR_ID
LEFT OUTER JOIN
SPOT_RAW..PS_VENDOR I
ON G.BRC_F_VEND_SETID=I.SETID --
AND G.BRC_F_VEND=I.VENDOR_ID
LEFT OUTER JOIN
#PSXLATITEM AS XPKGTYP
ON J.BRC_PACKAGE=XPKGTYP.FIELDVALUE
AND XPKGTYP.FIELDNAME='BRC_PACKAGE'
AND XPKGTYP.EFF_STATUS='A'
LEFT OUTER JOIN
#PSXLATITEM AS XPROCESS
ON J.BRC_PROCESS=XPROCESS.FIELDVALUE
AND XPROCESS.FIELDNAME='BRC_PROCESS'
AND XPROCESS.EFF_STATUS='A'
LEFT OUTER JOIN
#PSXLATITEM AS XPKGSIZ
ON J.BRC_PKG_SIZE=XPKGSIZ.FIELDVALUE
AND XPKGSIZ.FIELDNAME='BRC_PKG_SIZE'
AND XPKGSIZ.EFF_STATUS='A'
INNER JOIN
SPOT_RAW..PS_ORD_LINE OL
ON A.SOURCE_BUS_UNIT=OL.BUSINESS_UNIT
AND A.ORDER_NO=OL.ORDER_NO
AND A.ORDER_INT_LINE_NO=OL.ORDER_INT_LINE_NO
WHERE
F.EFFDT=
(SELECT MAX(INV.EFFDT) FROM #PS_INV_ITEMS INV
WHERE F.SETID = INV.SETID
AND F.INV_ITEM_ID=INV.INV_ITEM_ID
AND CONVERT(VARCHAR(10),INV.EFFDT,101) <= @DATE) --SUBSTRING(CONVERT(CHAR,@DATE,121), 1, 10))

UNION ALL

SELECT
A.INV_ITEM_ID AS "BRCM Item",
B.MODEL_NBR AS "Marketing Part",
A.INV_LOT_ID AS "Lot ID",
LEFT(A.INV_LOT_ID,6) AS "Date Code",
'N/A' AS "Datecode YYWW",
A.ORDER_NO AS "Order No",
A.ORDER_INT_LINE_NO AS "Order Line",
A.SHIP_CUST_NAME1 AS "ShipTo Name",
A.CUST_NAME AS "SoldTo Name",
"End Cust Name" =
ISNULL((SELECT max(CU.NAME1)
FROM
SPOT_RAW..PS_BRC_ORD_LN_CUST BOL,
SPOT_RAW..PS_SET_CNTRL_REC SI,
SPOT_RAW..PS_CUSTOMER CU
WHERE A.SOURCE_BUS_UNIT = BOL.BUSINESS_UNIT
AND A.ORDER_NO = BOL.ORDER_NO
AND A.ORDER_INT_LINE_NO = BOL.ORDER_INT_LINE_NO
AND BOL.BUSINESS_UNIT=SI.SETCNTRLVALUE
AND SI.REC_GROUP_ID='FS_23'
AND SI.RECNAME='INV_CUST_ADR_VW'
AND SI.SETID=CU.SETID
AND BOL.BRC_CUST_ID_LVL1=CU.CUST_ID),'N/A'),
"End-End Cust Name" =
ISNULL((SELECT max(CU.NAME1)
FROM
SPOT_RAW..PS_BRC_ORD_LN_CUST BOL,
SPOT_RAW..PS_SET_CNTRL_REC SI,
SPOT_RAW..PS_CUSTOMER CU
WHERE A.SOURCE_BUS_UNIT = BOL.BUSINESS_UNIT
AND A.ORDER_NO = BOL.ORDER_NO
AND A.ORDER_INT_LINE_NO = BOL.ORDER_INT_LINE_NO
AND BOL.BUSINESS_UNIT=SI.SETCNTRLVALUE
AND SI.REC_GROUP_ID='FS_23'
AND SI.RECNAME='INV_CUST_ADR_VW'
AND SI.SETID=CU.SETID
AND BOL.BRC_CUST_ID_LVL2=CU.CUST_ID),'N/A'),
"Customer Product ID" =
ISNULL((SELECT max(KIT.CUSTOMER_ITEM_NBR)
FROM
SPOT_RAW..PS_BRC_ORD_PRODKIT KIT
WHERE A.SOURCE_BUS_UNIT = KIT.BUSINESS_UNIT
AND A.ORDER_NO = KIT.ORDER_NO
AND A.ORDER_INT_LINE_NO = KIT.ORDER_INT_LINE_NO
AND A.PARENT_PROD_ID=KIT.PRODUCT_ID
AND A.PRODUCT_ID=KIT.PROD_COMPONENT_ID),OL.CUSTOMER_ITEM_NBR),
C.ITEM_FIELD_C4 as "P-Code",
"Die Item"=
ISNULL((SELECT max(BOM.COMPONENT_ID)
FROM
#PS_EN_BOM_COMPS BOM
WHERE
A.INV_ITEM_ID=BOM.INV_ITEM_ID
AND CONVERT(VARCHAR(10),BOM.DATE_IN_EFFECT,101) <= @DATE
AND CONVERT(VARCHAR(10),BOM.DATE_OBSOLETE,101) > @DATE
AND 1=(SELECT COUNT(*) FROM
#PS_EN_BOM_COMPS BOM2
WHERE A.INV_ITEM_ID=BOM2.INV_ITEM_ID
AND CONVERT(VARCHAR(10),BOM.DATE_IN_EFFECT,101) <= @DATE
AND CONVERT(VARCHAR(10),BOM.DATE_OBSOLETE,101) > @DATE )),
J.BRC_PART_DIE),
C.ITEM_FIELD_C10_C AS "Core Part",
D.BRC_FAB_ID AS "Fab Lot ID",
D.COUNTRY_ORIGIN AS "COO",
'N/A' AS "Assy Vendor ID", 'N/A' AS "Assy Vendor",
'N/A' AS "Fab Vendor ID", 'N/A' AS "Fab Vendor",
XPROCESS.XLATLONGNAME as "Fab Process",
XPHASE.XLATLONGNAME as "Item Phase",
XPKGTYP.XLATLONGNAME as "Package Type",
XPKGSIZ.XLATLONGNAME as "Package Size",
J.BRC_PKG_PIN_COUNT as "Pin Count",
F.INV_ITEM_TYPE as "Item Type",
A.DT_TIMESTAMP
FROM
#A_SH A
LEFT OUTER JOIN
SPOT_RAW..PS_BRC_LOT_CNTL D
ON A.INV_ITEM_ID=D.INV_ITEM_ID
AND A.INV_LOT_ID=D.INV_LOT_ID
INNER JOIN
#PS_INV_ITEMS F
ON A.INV_ITEM_ID=F.INV_ITEM_ID
INNER JOIN
SPOT_RAW..PS_PROD_ITEM B
ON A.INV_ITEM_ID=B.INV_ITEM_ID
AND B.SETID='SHARE'
INNER JOIN
SPOT_RAW..PS_BRC_ITEM_TBL J
ON B.SETID=J.SETID
AND B.INV_ITEM_ID=J.INV_ITEM_ID
INNER JOIN
SPOT_RAW..PS_MASTER_ITEM_TBL C
ON A.INV_ITEM_ID=C.INV_ITEM_ID AND C.SETID='SHARE'
LEFT OUTER JOIN
#PSXLATITEM AS XPHASE
ON C.ITEM_FIELD_C1_C=XPHASE.FIELDVALUE
AND XPHASE.FIELDNAME='ITEM_FIELD_C1_C'
AND XPHASE.EFF_STATUS='A'
LEFT OUTER JOIN
#PSXLATITEM AS XPKGTYP
ON J.BRC_PACKAGE=XPKGTYP.FIELDVALUE
AND XPKGTYP.FIELDNAME='BRC_PACKAGE'
AND XPKGTYP.EFF_STATUS='A'
LEFT OUTER JOIN
#PSXLATITEM AS XPROCESS
ON J.BRC_PROCESS=XPROCESS.FIELDVALUE
AND XPROCESS.FIELDNAME='BRC_PROCESS'
AND XPROCESS.EFF_STATUS='A'
LEFT OUTER JOIN
#PSXLATITEM AS XPKGSIZ
ON J.BRC_PKG_SIZE=XPKGSIZ.FIELDVALUE
AND XPKGSIZ.FIELDNAME='BRC_PKG_SIZE'
AND XPKGSIZ.EFF_STATUS='A'
INNER JOIN
SPOT_RAW..PS_ORD_LINE OL
ON A.SOURCE_BUS_UNIT=OL.BUSINESS_UNIT
AND A.ORDER_NO=OL.ORDER_NO
AND A.ORDER_INT_LINE_NO=OL.ORDER_INT_LINE_NO
WHERE
F.EFFDT=
(SELECT MAX(INV.EFFDT) FROM #PS_INV_ITEMS INV
WHERE F.SETID = INV.SETID
AND F.INV_ITEM_ID=INV.INV_ITEM_ID
AND CONVERT(VARCHAR(10),INV.EFFDT,101) <= @DATE) --SUBSTRING(CONVERT(CHAR,@DATE,121), 1, 10))
AND NOT EXISTS
(SELECT * FROM SPOT_RAW..PS_BRC_DC_PREFX DC
WHERE SUBSTRING(A.INV_LOT_ID,1,2)= DC.BRC_DC_PREFIX
AND DC.SETID='SHARE')

DROP TABLE #PSXLATITEM
DROP TABLE #PS_EN_BOM_COMPS
DROP TABLE #PS_INV_ITEMS
DROP TABLE #A_SH
Post #517421
Posted Monday, June 16, 2008 6:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 7,121, Visits: 15,022
hmm...needle in the haystack time. The best I can offer up would be to start commenting sections out to isolate where the error might be. The second though would be to double-check your views if any are involved.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #517497
Posted Monday, June 16, 2008 1:41 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:02 AM
Points: 1,595, Visits: 6,612
The 'column has no name' error is caused by the statement 'select 1/0'. I suggest to use the RAISERROR statement for raising errors. I tested this using the following procedure:
create procedure up_test
as
begin
if (datepart(hh, getdate()) not between 12 and 24)
begin
-- select 'Error - Running outside allowed time window'
-- select 1/0
-- return
raiserror ('Error - Running outside allowed time window', 16, 0)
end
select 'A' as A into #temp
select A from #temp t
drop table #temp
end

Next problem you will face is that BIDS is not able to collect metadata of procedures returning data from temporary tables. If you call the procedure above with an OLE DB reader, the object will have no columns. Questions about this behaviour in SSRS projects comes up every now and then (for example, see the thread last week started by someone name 'scott'). In SSRS the workaround is to add the columns of the output of the stored procedure manually to a dataset. Unfortunately, in the above example I wasn't able to add the column using the Advanced Editor. If I hit the datatype property, the dialog disappears. Anybody know what the 'Add column' button is for?

Peter
Post #517849
Posted Tuesday, June 17, 2008 3:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 21, 2012 12:06 PM
Points: 111, Visits: 179
Thnx peter you are abs rite, column has no name, error was coz of select 1/0 statement, and yup now i m getting same problem that u have mentioned with BIDS when it invloves temporary tables it returns no cloumns.
I m working on this, if you get any scuess to get hold on this problem without changing SP plz reply me on this forum.

Thanks once again for ur feedback n help.
Post #518090
Posted Tuesday, June 17, 2008 12:56 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:02 AM
Points: 1,595, Visits: 6,612
After some googling I found two solutions:

1. use a dummy select statement as the first select statement in your procedure to provide the metadata:
ALTER procedure [dbo].[up_test]
as
begin
set nocount on
if 1=0
begin
select cast('A' as varchar(1)) as A
end
select 'A' as A into #temp
select * from #temp
drop table #temp
end

2. Use table variables instead of temporary tables
ALTER procedure [dbo].[up_test]
as
begin
set nocount on
declare @temp table (A varchar(1))
insert into @temp values ('A')
select * from @temp
end

Peter
Post #518503
Posted Thursday, January 27, 2011 6:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 19, 2011 5:23 AM
Points: 7, Visits: 42
I have the below query in a data flow and cannot get it to work no matter what I do

SELECT DISTINCT
B12.MODLN_USC_C + ';' +
LEFT(ENT_ENTITY_C, 1) +
SUBSTRING(ENT_ENTITY_C, 3, 1) +
SUBSTRING(ENT_ENTITY_C, 6, 1) +
SUBSTRING(ENT_ENTITY_C, 4, 1) +
SUBSTRING(ENT_ENTITY_C, 5, 1) + ';' +
b10Opt.USC_C + ';' +
LTRIM(RTRIM(ISNULL(B10Opt.USC_DESC_X, '')))
FROM
(select ent_entity_c, modln_usc_c, ent_opt_usc_c
from CDMStaging..mfslb12_entlst
where MKT_C = 'FF' AND ENT_STATUS_C IN ('O','B')) AS b12
INNER JOIN
(SELECT usc_c, usc_desc_x
FROM CDMStaging..mfslb10_elm
where mkt_c = 'FF') As B10Opt
on CHARINDEX(B10Opt.usc_c, B12.ENT_OPT_USC_C) > 0
ORDER BY 1

I have tried the dummy data methods but still no joy.
Post #1054517
Posted Thursday, January 27, 2011 1:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 13,334, Visits: 10,199
David Jahngir (1/27/2011)
I have the below query in a data flow and cannot get it to work no matter what I do

SELECT DISTINCT
B12.MODLN_USC_C + ';' +
LEFT(ENT_ENTITY_C, 1) +
SUBSTRING(ENT_ENTITY_C, 3, 1) +
SUBSTRING(ENT_ENTITY_C, 6, 1) +
SUBSTRING(ENT_ENTITY_C, 4, 1) +
SUBSTRING(ENT_ENTITY_C, 5, 1) + ';' +
b10Opt.USC_C + ';' +
LTRIM(RTRIM(ISNULL(B10Opt.USC_DESC_X, '')))
FROM
(select ent_entity_c, modln_usc_c, ent_opt_usc_c
from CDMStaging..mfslb12_entlst
where MKT_C = 'FF' AND ENT_STATUS_C IN ('O','B')) AS b12
INNER JOIN
(SELECT usc_c, usc_desc_x
FROM CDMStaging..mfslb10_elm
where mkt_c = 'FF') As B10Opt
on CHARINDEX(B10Opt.usc_c, B12.ENT_OPT_USC_C) > 0
ORDER BY 1

I have tried the dummy data methods but still no joy.


What are the errors that you get?

Also, is it possible to bring some formatting in your code, because it's a real pain to read.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1054908
Posted Friday, January 28, 2011 2:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 19, 2011 5:23 AM
Points: 7, Visits: 42
I get the "Error at Data Flow Task [OLE DB Source [1]]: There is a data source column with no name. Each data source column must have a name.
Exception from HRESULT: 0xC0207016" error
Post #1055165
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse