none
Column name or number of supplied values does not match table definition

    Вопрос

  • Hi,

    Im using the below query to get the top long running query against all databases in my sql server. The quey works in a single database. It is alo working with Msforeachdb but when i try to insert the results in a temp table to get it as a single result set,Im getting this error:

    Column name or number of supplied values does not match table definition

    Someone please point me out what is wrong with the code:

    create table #temp (averageseconds decimal(8,3),totalseconds decimal(8,3),executioncount int,query varchar(8000),objectname varchar(2000),DBname varchar(1000))
    DECLARE @command varchar(3000)
    set @command='Use [?]
    insert into #temp 
    SELECT TOP 1
          qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
          --qs.total_elapsed_time / 1000000.0 AS total_seconds,
          qs.execution_count,
          SUBSTRING (qt.text,qs.statement_start_offset/2, 
          (CASE WHEN qs.statement_end_offset = -1 
          THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
          o.name AS object_name,
          DB_NAME(qt.dbid) AS database_name
    FROM 
          sys.dm_exec_query_stats qs
          CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
          LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
    WHERE 
          qt.dbid = DB_ID()
    ORDER BY 
          average_seconds DESC';
          
     EXEC sp_MSforeachdb @command
     select * from #temp
    drop table #temp

    I also tried changing the precision and scale values in the decimal datatype, tats also not helping. Please help me.

    Thanks

    31 декабря 2012 г. 7:03

Ответы

Все ответы