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

    Question

  • 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

    Monday, December 31, 2012 7:03 AM

Answers

  • Sorry very silly mistake.

    I have declared 6 fields in the temp table and im just inserting 5 values in the logic. I forgot that i commented a  value.

    • Marked as answer by udhayan Monday, December 31, 2012 1:08 PM
    Monday, December 31, 2012 1:08 PM

All replies

  • Sorry very silly mistake.

    I have declared 6 fields in the temp table and im just inserting 5 values in the logic. I forgot that i commented a  value.

    • Marked as answer by udhayan Monday, December 31, 2012 1:08 PM
    Monday, December 31, 2012 1:08 PM
  • Hi Udhayan,

    Thanks for your post.

    Glad to hear that the issue is solved by yourself and thanks for you sharing. The Insert Error message that Column name or number of supplied values does not match table definition appears when the column names or the supplied values used in the INSERT statement do not match those of the table definition.

    Please refer to the following article for detail description.

    http://www.sql-server-performance.com/2007/supplied-value-not-match/

    Regards,

    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Wednesday, January 02, 2013 7:50 AM
    Moderator