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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics