No column informatin was returned by the SQL command

  • Ok so I've got a rather large SSIS project I'm working with.

    One of the data sources is the following SQL query. SSIS complains that no column information was returned by the SQL command.

    I've added FMTONLY OFF to take care of temp tables, I've added NOCOUNT ON, Transaction Isolation is because this can be a lengthy read from a production system during the day, and I'd prefer not to prevent them from working on their stuff, I can always pick up an update later in the next run 1 hour later.

    I've also set my AlwaysUseDefaultCodePage to true.

    I even declared a table variable with all the columns I want to return and then selected out of it. How in the world can I still get the dreaded No column information was returned by the SQL command error?

    This code works perfectly in SSMS, so it's an SSIS issue, I just don't know what tricks left to try.

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    SET ANSI_WARNINGS OFF;

    SET FMTONLY OFF;

    SET NOCOUNT ON;

    DECLARE @lastPMGnote DATETIME

    SELECT

    @lastPMGNote = MAX(CSSExtractDate)

    FROM

    cssdw.lastCSSExtraction

    WHERE

    CSSExtractType = 'PMGNote'

    IF @lastPMGNote IS NULL

    SET @lastPMGNote = 0

    IF OBJECT_ID('tempdb..#pmg_notes') IS NOT NULL DROP TABLE #pmg_notes

    CREATE TABLE #pmg_notes(

    pmg INT NOT NULL,

    pmgnote VARCHAR(MAX))

    INSERT #pmg_notes(pmg,pmgnote)

    SELECT

    a.projectid,

    ProjectNote = createuserid + ' ' + CONVERT(VARCHAR(MAX),DATEADD(hh,pubdata.dbo.get_utc_offset(a.CreateTimestamp),a.CreateTimestamp)) + CHAR(10) + CHAR(13) + CAST(a.Text AS VARCHAR(MAX))

    from

    tbl_pmg_Projectnote a

    WHERE

    a.CreateTimestamp >= @lastPMGnote

    ORDER BY

    a.projectid,

    a.CreateTimestamp

    DECLARE @pmgnote VARCHAR(MAX) =''

    DECLARE @pmg INT =0

    UPDATE #pmg_notes

    SET

    @pmgnote = pmgnote = CASE WHEN @pmg != pmg THEN pmgnote

    ELSE @pmgnote + CHAR(10) + CHAR(13) + pmgnote END,

    @pmg = pmg

    IF OBJECT_ID('tempdb..#hold') IS NOT NULL DROP TABLE #hold

    DECLARE @hold TABLE(

    pmg INT NOT NULL,

    createtimestamp DATETIME,

    pmgnote VARCHAR(MAX))

    ;WITH cte_lastnotedate AS (

    SELECT

    projectid,

    createtimestamp = MAX(createtimestamp)

    from

    tbl_pmg_projectnote

    GROUP BY

    projectid

    )

    INSERT @hold

    ( pmg, createtimestamp, pmgnote )

    SELECT

    pmg,

    createtimestamp = CAST(MAX(b.createtimestamp) AS DATETIME),

    pmgnote = CAST(MAX(pmgnote) AS VARCHAR(MAX))

    FROM

    #pmg_notes a LEFT JOIN cte_lastnotedate b ON

    a.pmg = b.projectid

    GROUP BY

    pmg

    SELECT

    pmg,

    createtimestamp,

    pmgnote

    FROM @hold



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • I finally solved this...

    After all my attempts using an OLE DB Data Source,

    I changed the data source for the queries that gave me this trouble to ado.net data sources, and all the problems went away.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • I just wanted to say thanks for posting your solution for this. I had a fun couple of hours with trying to figure out what was happening with a new version of my query blowing up SSIS.

    I had changed out my temp table to instead be a table variable. Along with that I was passing in set fmtonly off based on another post. No dice with any of that.

    Everything started working when I switch over to an ADO.Net Source....I'm glad it's working but I don't understand why it was broken to begin with.

  • Yep, solved my problem. Thanks!

  • Solved my issue too but i would like to understand why?

    My query also worked direct on management studio but not through SSIS with OLEDB source \ destination

    CODE:

    --variables to hold each 'iteration'

    declare @query varchar(100)

    declare @dbname sysname

    declare @vlfs int

    --table variable used to 'loop' over databases

    declare @databases table (dbname sysname)

    insert into @databases

    --only choose online databases

    select name from sys.databases where state = 0 and name not in ('master' , 'model')

    --table variable to hold results

    declare @vlfcounts table

    (ServerInstance nvarchar (50),

    dbname sysname,

    vlfcount int)

    --table variable to capture DBCC loginfo output

    --changes in the output of DBCC loginfo from SQL2012 mean we have to determine the version

    declare @MajorVersion tinyint

    set @MajorVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))-1)

    if @MajorVersion < 11 -- pre-SQL2012

    begin

    declare @dbccloginfo table

    (

    fileid tinyint,

    file_size bigint,

    start_offset bigint,

    fseqno int,

    [status] tinyint,

    parity tinyint,

    create_lsn numeric(25,0)

    )

    while exists(select top 1 dbname from @databases)

    begin

    set @dbname = (select top 1 dbname from @databases)

    set @query = 'dbcc loginfo (' + '''' + @dbname + ''') '

    insert into @dbccloginfo

    exec (@query)

    set @vlfs = @@rowcount

    insert into @vlfcounts

    values(@@SERVERNAME, @dbname, @vlfs)

    delete from @databases where dbname = @dbname

    end --while

    end

    else

    begin

    declare @dbccloginfo2012 table

    (

    RecoveryUnitId int,

    fileid tinyint,

    file_size bigint,

    start_offset bigint,

    fseqno int,

    [status] tinyint,

    parity tinyint,

    create_lsn numeric(25,0)

    )

    while exists(select top 1 dbname from @databases)

    begin

    set @dbname = (select top 1 dbname from @databases)

    set @query = 'dbcc loginfo (' + '''' + @dbname + ''') '

    insert into @dbccloginfo2012

    exec (@query)

    set @vlfs = @@rowcount

    insert into @vlfcounts

    values(@@SERVERNAME, @dbname, @vlfs)

    delete from @databases where dbname = @dbname

    end --while

    end

    ----output the full list

    select serverinstance, dbname, vlfcount

    from @vlfcounts

    --drop table #vlfcounts

    --order by dbname

  • I had the same issue, and it was because I was trying to use a #tmp table in the Dataflow task.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply