OLE DB source Editor and External columns
I have query like below that I am using as a OLE DB source Set NOCOUNT ON Select * Into #temp1 from A Select * Into #temp2 From B Select * from #temp1 a Join #temp2 b on a.episode_key = b.episode_key I can see the preview data , but when I click columns, there are no available external columns.. Howcan I fix this issue?
April 23rd, 2008 3:55am
The OLE DB Source doesn't deal very well with multiple statements. Can you combine it into a single query, or put it in a stored procedure?
Free Windows Admin Tool Kit Click here and download it now
April 23rd, 2008 4:17am
It may also help to add SET FMTONLY OFF as well.
April 23rd, 2008 5:32am
I've tried to put it in SP and Add SET FMTONLY OFF.. but still no luck. i did this way for thequery performancereasoin...is there any other ways that I can try to make this work? thanks
Free Windows Admin Tool Kit Click here and download it now
April 23rd, 2008 2:06pm
Hi,I have the same issue : I can see the preview result (3 columns) but there is not output recognized. I have an "OLE DB Source" with "SQL Server 2005" as source. The "Data Access Mode" is "SQL Command From Variable".The variable that have the query has EvaluateAsExpression=True. The query is dynamically generated at execution time, then I can't use an SP I think.A sample of generated query is IF EXISTS (SELECT*FROMSTO.sys.tablesWHEREsys.tables.name='foobar') EXEC ('SELECT CAST('ok' as VARCHAR(10)) as foo FROM foobar') ELSE EXEC ('select CAST(NULL as VARCHAR(10))as foo');This is a serious bug ! Does anyons have a suggestion to make this work ?a+, =)-=Clement=-
October 22nd, 2009 12:25pm
I think that you have to add the columns in thequery and/or add them in the OLE Source object
Questions
Are you using ... Source Connection --> Property --> RetainSameConnection = TRUE .... ??????
If you add the column names in your script dose it help?
Sincerely SH -- Please kindly dont forget to mark the post(s) that answered your question and/or vote for the post(s)
Free Windows Admin Tool Kit Click here and download it now
October 22nd, 2009 4:30pm
I'm unable to reproduce the behavior you descrie, Clement; I see a column list. I think there might be a bug in your statement, though: Either the apostrophes around the "ok" need to be doubled, or you should get rid of the EXEC statements. E.g.: IF EXISTS (SELECT*FROMSTO.sys.tablesWHEREsys.tables.name='foobar') SELECT CAST('ok' as VARCHAR(10)) as foo FROM foobar) ELSE (select CAST(NULL as VARCHAR(10))as foo)I've only tried this on 2008, so maybe that's the difference.This posting is provided "AS IS" with no warranties, and confers no rights.
November 3rd, 2009 11:24pm
Has no one been able to figure this out for those of us that can't use a SPROC? HELP!!!!
The same thing happens with both temp tables and table variables. This is a serious quirk/bug that needs a fix or reasonable workaround, because there are situations like mine where you can't just move the code into a SPROC (the code works in SSMS
2005 and SSIS 2005 can both parse and execute/preview the results, but no dice with the columns showing up). Here's my code (temp tables)-
SET FMTONLY OFF
--SET NOCOUNT ON
IF 1 = 0
BEGIN
SELECT CAST (NULL AS INT ) AS [Year],
CAST (NULL AS INT ) AS [Month],
CAST (NULL AS FLOAT ) AS [Transaction Amount],
CAST (NULL AS INT ) AS [Transaction Count],
CAST (NULL AS FLOAT ) AS [Check Amount],
CAST (NULL AS INT ) AS [Check Count],
CAST (NULL AS FLOAT ) AS [DirectPay Amount],
CAST (NULL AS INT ) AS [DirectPay Count],
CAST (NULL AS FLOAT ) AS [Total Amount],
CAST (NULL AS INT ) AS [Total Count]
END
DECLARE @FirstDayof11MonthsAgo as SmallDatetime
DECLARE @CurrentDay as SmallDatetime
DECLARE @FirstDayof11MonthsAgoString as Char(10)
DECLARE @CurrentDayString as Char(10)
SELECT @CurrentDay = GETDATE()
IF DATEPART(d, @CurrentDay) = 1
BEGIN
SELECT @CurrentDay = @CurrentDay - 1
END
SELECT @FirstDayof11MonthsAgo = DATEADD(m, - 12, @CurrentDay)
SELECT @FirstDayof11MonthsAgo = DATEADD(d, - DATEPART(d, @FirstDayof11MonthsAgo) +1,@FirstDayof11MonthsAgo)
SELECT @CurrentDayString = CAST(DATEPART(mm, @CurrentDay) AS varchar) +
'/' + CAST(DATEPART(d, @CurrentDay) AS varchar) +
'/' + CAST(DATEPART(yy, @CurrentDay) AS varchar)
SELECT @FirstDayof11MonthsAgoString = CAST(DATEPART(mm, @FirstDayof11MonthsAgo) AS varchar) +
'/' + CAST(DATEPART(d, @FirstDayof11MonthsAgo) AS varchar) +
'/' + CAST(DATEPART(yy, @FirstDayof11MonthsAgo) AS varchar)
SELECT DATEPART(yyyy, at.postingdt) AS [Year],
DATEPART(m, at.postingdt) AS [Month],
SUM(at.billingamount) AS [Transaction Amount],
COUNT(*) AS [Transaction Count]
INTO #Transactions
FROM accountTransaction AS at WITH (NOLOCK)
WHERE at.postingdt BETWEEN @FirstDayof11MonthsAgoString AND @CurrentDayString
AND at.transactionType <> 'Y'
GROUP BY DATEPART(yyyy, at.postingdt),
DATEPART(m, at.postingdt)
SELECT DATEPART(yyyy, pcl.createDt) AS [Year],
DATEPART(m, pcl.createDt) AS [Month],
SUM(pcl.Amount) AS [Check Amount],
COUNT(*) AS [Check Count]
INTO #Checks
FROM purchaseCardLog AS pcl WITH (NOLOCK)
INNER JOIN v_PaymentType AS pt WITH (NOLOCK) ON pt.PaymentTypeID = pcl.PaymentTypeID
INNER JOIN v_purchaseLogStatus AS pls WITH (NOLOCK) ON pls.purchaseLogStatusId = pcl.PurchaseLogStatusId
WHERE (pt.PaymentTypeName ='CHECK') AND (pls.status = 'Reconciled') AND (pcl.createDt BETWEEN @FirstDayof11MonthsAgoString AND @CurrentDayString)
GROUP BY DATEPART(yyyy, pcl.createDt),
DATEPART(m, pcl.createDt)
SELECT DATEPART(yyyy, pcl.createDt) AS [Year],
DATEPART(m, pcl.createDt) AS [Month],
SUM(pcl.Amount) AS [DirectPay Amount],
COUNT(*) AS [DirectPay Count]
INTO #DirectPays
FROM purchaseCardLog AS pcl WITH (NOLOCK)
INNER JOIN v_PaymentType AS pt WITH (NOLOCK) ON pt.PaymentTypeID = pcl.PaymentTypeID
INNER JOIN v_purchaseLogStatus AS pls WITH (NOLOCK) ON pls.purchaseLogStatusId = pcl.PurchaseLogStatusId
WHERE (pt.PaymentTypeName = 'SETTLE') AND (pls.status = 'Reconciled') AND (pcl.createDt BETWEEN @FirstDayof11MonthsAgoString AND @CurrentDayString)
GROUP BY DATEPART(yyyy, pcl.createDt),
DATEPART(m, pcl.createDt)
SELECT tr.[Year], tr.[Month],
ISNULL(tr.[Transaction Amount],0.00) AS [Transaction Amount], ISNULL(tr.[Transaction Count], 0) AS [Transaction Count],
ISNULL(ch.[Check Amount], 0.00) AS [Check Amount], ISNULL(ch.[Check Count], 0) AS [Check Count],
ISNULL(dp.[DirectPay Amount], 0.00) AS [DirectPay Amount], ISNULL(dp.[DirectPay Count], 0) AS [DirectPay Count],
ISNULL(tr.[Transaction Amount],0.00) + ISNULL(ch.[Check Amount], 0.00) + ISNULL(dp.[DirectPay Amount], 0.00) AS [Total Amount],
ISNULL(tr.[Transaction Count],0) + ISNULL(ch.[Check Count], 0) + ISNULL(dp.[DirectPay Count], 0) AS [Total Count]
FROM #Transactions AS tr
LEFT JOIN #Checks AS ch ON tr.[Year] = ch.[Year] AND tr.[Month] = ch.[Month]
LEFT JOIN #DirectPays AS dp ON tr.[Year] = dp.[Year] AND tr.[Month] = dp.[Month]
ORDER BY 1,2
DROP TABLE #Transactions
DROP TABLE #Checks
DROP TABLE #DirectPays
Free Windows Admin Tool Kit Click here and download it now
December 8th, 2010 9:30pm