Coping with No Column Names in the OLEDB Data Source Editor
Sooner or later you'll create a DataFlow and come across this type of dialog box for the OLE DB source:
No source columns listed and noting to map - great
So, why does this problem occur, when all previous attempts have worked? Well the most likely case is that you are selecting from a temporary table as the last statement in the source stored procedure, and in this case there is no metadata returned to the designer. Just a simple proc like the one below will cause this problem:
CREATE PROC myproc AS
No source columns listed and noting to map - great
So, why does this problem occur, when all previous attempts have worked? Well the most likely case is that you are selecting from a temporary table as the last statement in the source stored procedure, and in this case there is no metadata returned to the designer. Just a simple proc like the one below will cause this problem:
CREATE PROC myproc AS
SELECT TOP 10 organisation_name INTO #MyOrganisations FROM dbo.Organisation
SELECT * FROM #MyOrganisations
So, what can we do? There are 2 main possibilities (there is also a third one that I'll post up some other time).
(1) One possibility is to change the SQL Command Text as follows and add the italicised text:
SET FMTONLY OFF
SET NOCOUNT ON
exec myproc
This forces the parser to actually run the query to return the metadata and reverse the SET FMTONLY ON that it normally uses. This works, and you'll see the column names as per normal. However there is a serious downside that seems to be missed when this solution is advised. My colleague John Gillies added an auditing step to the proc "myproc" to test this and found that the proc itself was executed 5 times when running the package!!! This could seriously slow things down if the initial stored proc itself is slow anyway.
(2) Use a Table Variable instead
CREATE PROC myproc AS
So, what can we do? There are 2 main possibilities (there is also a third one that I'll post up some other time).
(1) One possibility is to change the SQL Command Text as follows and add the italicised text:
SET FMTONLY OFF
SET NOCOUNT ON
exec myproc
This forces the parser to actually run the query to return the metadata and reverse the SET FMTONLY ON that it normally uses. This works, and you'll see the column names as per normal. However there is a serious downside that seems to be missed when this solution is advised. My colleague John Gillies added an auditing step to the proc "myproc" to test this and found that the proc itself was executed 5 times when running the package!!! This could seriously slow things down if the initial stored proc itself is slow anyway.
(2) Use a Table Variable instead
CREATE PROC myproc AS
declare @mytable table (organisation_name varchar(100))
INSERT INTO @mytable(organisation_name)
SELECT TOP 10 organisation_name FROM dbo.Organisation
SELECT TOP 10 organisation_name FROM dbo.Organisation
SELECT * FROM @mytable
You'll now need to set the SQL Command Text to be:
SET NOCOUNT ON
exec myproc
Otherwise you'll get the dreaded "A rowset based on the SQL command was not returned by the OLE DB provider" error.
Using this technique will make sure that the proc only runs once only! OK - you can't put nonclustered indexes on a table variable and all the other restrictions, so if really needed you can use option (1) but in most cases I'd reason that option (2) is the preferable solution.
You'll now need to set the SQL Command Text to be:
SET NOCOUNT ON
exec myproc
Otherwise you'll get the dreaded "A rowset based on the SQL command was not returned by the OLE DB provider" error.
Using this technique will make sure that the proc only runs once only! OK - you can't put nonclustered indexes on a table variable and all the other restrictions, so if really needed you can use option (1) but in most cases I'd reason that option (2) is the preferable solution.
On option 1 above, what happens if you set the DelayValidation property to true on that component? Do you still get 5 executions of the sproc?
Also, what was the auditing mechanism in the sproc?
Basically, I'm looking for a repro so we can get a solid "best practices" going forward from the guys at Microsoft.
Reply to this
I've tried using method #2 but unfortunately it still won't work without the "SET FMTONLY OFF" command which results in it running 5 times. My query however is more complex than just putting data into a variable table, there's an insert command into an audit table and a few other things. I DID however SELECT * (and tried individual records) at the bottom of the SProc. I'm not sure what's going on
Reply to this
Hi Shawn,
strange that this didn't work.
At first I thought it might be related to recompilation, so I forced the proc containing the table variable to recompile but it still worked fine for me - no SET FMTONLY OFF and one execution only. Can you email me your proc (aul.Ibison@ReplicationAnswers.Com">Paul.Ibison@ReplicationAnswers.Com) and I'll try to repro. For now you might want to take a look at the comments of Phil and myself which will greatly reduce the # executions of the proc.
Cheers,
Paul
Reply to this
A caution for method #2: In SQL 2000, at least, table variable performance takes a _huge_ nosedive when the rowcount exceeds ~200. (It was explained to me as roughly analogous to moving the table from the stack to the heap.) In my case, a query that would return 30k rows in five seconds on its own would take over two minutes to return the same number of rows when written as a table-valued UDF.
I haven't been brave enough to test this in SQL 2005, but I see nothing to suggest that it's changed.
Reply to this