Query containing table variable returning no rows at runtime

Question

I have a query containing table variables for an Ole Db source. The SSIS designer correctly recognized the column names from the query and when I hit the Preview button, it also shows the data correctly. But when I execute the task, no rows are returned. Am i missing something? Here's a sample query containing the table variable:

Code Snippet
declare @mytable table
(
        col1 int,
        col2 varchar(20)
)

insert @mytable values (1, 'one')
insert @mytable values (2, 'two')
insert @mytable values (3, 'three')

select * from @mytable

Regards,

Syed Mehroz Alam

Answer 1

I can at least confirm that I am experiencing the same behaviour.  Off the top of my head, I can't think of a reason for it unless the OLE DB provider is silently tanking.

 

I reworked it as a CTE and I saw 3 rows flow through so definitely an oddity. 

Code Snippet

WITH D AS
(
SELECT 1 AS col1, cast('one' AS varchar(20)) AS col2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three'
)
SELECT D.* FROM D

 

 

Answer 2

Actually, I just found a comment on one of Jamie's threads.  Issue a

Code Snippet

SET NOCOUNT ON

 

 

and it will work.  I'll need to commit that one to memory or understand the reasoning on it.

Answer 3

Great find, Charles. It works perfect now. Looks like the output of insert statements: i.e. x row(s) affected was the source of problem and a SET NOCOUNT ON prevented such interferences. Thanks very much for your help.


Cheers,
Syed Mehroz Alam
MyBlog | MyArticles