SQL Server Performance

OLE DB Source and Stored Procedures

Discussion in 'SQL Server 2005 Integration Services' started by ddupuis, Jun 26, 2008.

  1. ddupuis New Member

    I want to take the results of msdb.dbo.sp_help_job on multiple servers and import it into a table my DBA Repository database. When I went to do this I created a Data Transform Task. Inside the task I created an OLE DB Source and set the data access mode to SQL Command. The command I put in was "EXEC msdb.dbo.sp_help_job". The problem here is that there is no way to define the columns when using a stored procedure. As a work around I tried the following:
    SET FMTONLY ON
    EXEC msdb.dbo.sp_help_job
    SET FMTONLY OFF
    EXEC msdb.dbo.sp_help_job
    This worked when executed on my 2000 servers but errors when I run it on my 2005 servers. This was true whether executing it from the pacakge or directly in Management Studio. As these are all system stored procedures there really isn't anything I can do about the error.
    So my question does anyone have a better idea of how I could accomplish this? My plan for now is to use an Execute SQL task to poplate a variable and then use a Script Component as a source in my Data Transform. I don't like it but it will work.
  2. techbabu303 New Member

    It works fine express edition I have in home pc. Are you sure you rights to execute it ?
    -Sat
  3. techbabu303 New Member

    Try without "EXEC" preceding the system stored procedure.
    -Sat
  4. satya Moderator

    No need to specify MSDB:
    USE msdb ;
    GO
    EXEC dbo.sp_help_job ;
    GO

Share This Page