locked
SQL Execution error: Column name or number of supplied values do not match table definition. RRS feed

  • Question

  • Hi All,

    I am getting below error while executing one SQL query to check blocking. Note I am having read only access on this SQLserver.

    Server: Msg 213, Level 16, State 7, Line 4
    Insert Error: Column name or number of supplied values does not match table definition.

    SQL Query:

    create table #Blocking_Check

    (

            SPID int,

            Status varchar(100),

            [Login] varchar(100),

            HostName varchar(100),

            BlkBy varchar(100),

            DBName varchar(100),

            Command varchar(100),

            CPUTIME varchar(100),

            DISKIO varchar(100),

            LASTBATCH varchar(100),

            PROGRAMNAME varchar(100),

            SPID1 varchar(100),

            REQUESTID varchar(100) 

    )

    insert into #Blocking_Check

    exec('sp_who2')

     

    Select case when BlkBy >= '1' then

    'Blocked By Some user'
    Else

    'No Blocking' end as BlockedStatus,* from #Blocking_Check

    drop table #Blocking_Check

    Tuesday, November 2, 2010 3:00 AM

Answers

  • Which release of SQL Server are you on?  On SQL Server 2000, sp_who2 returned one less column.  If you are on SQL 2000, try creating your temp table without the REQUESTID column.

    Tom

    • Proposed as answer by Naomi N Tuesday, November 2, 2010 10:07 PM
    • Marked as answer by KJian_ Tuesday, November 9, 2010 3:32 AM
    Tuesday, November 2, 2010 4:26 AM

All replies

  • Hi ALL,

    I am gettiing below error while executing my bleow mentioned script. Please not I am having read access on to this database:

    Error:

    Server: Msg 197, Level 15, State 1, Line 35
    EXECUTE cannot be used as a source when inserting into a table variable.

    SQL Script:

    declare @Blocking_Check table

    (

            SPID int,

            Status varchar(100),

            [Login] varchar(100),

            HostName varchar(100),

            BlkBy varchar(100),

            DBName varchar(100),

            Command varchar(100),

            CPUTIME varchar(100),

            DISKIO varchar(100),

            LASTBATCH varchar(100),

            PROGRAMNAME varchar(100),

            SPID1 varchar(100),

            REQUESTID varchar(100) 

    )

    insert into @Blocking_Check

    exec('sp_who2')

     

    Select case when BlkBy >= '1' then

    'Blocked By Some User'
    Else

    'No Blocking' end as BlockedStatus,* from @Blocking_Check

     

    • Merged by KJian_ Tuesday, November 9, 2010 3:33 AM
    Tuesday, November 2, 2010 2:49 AM
  • Which release of SQL Server are you on?  On SQL Server 2000, sp_who2 returned one less column.  If you are on SQL 2000, try creating your temp table without the REQUESTID column.

    Tom

    • Proposed as answer by Naomi N Tuesday, November 2, 2010 10:07 PM
    • Marked as answer by KJian_ Tuesday, November 9, 2010 3:32 AM
    Tuesday, November 2, 2010 4:26 AM
  • Are you using SQL 2000? If so you cannot do that, only from 2005 onwards

     


    Craig Bryden - Please mark correct answers
    • Proposed as answer by Tier 1 Support Tuesday, November 2, 2010 4:44 AM
    Tuesday, November 2, 2010 4:34 AM
  • Hi Jatinder,

    Tom is right, If you are using sql 2005 then your query will execute successfully,

    Please check how many columns return by executing sp_who2  and create your temporary table with same number of columns,

     

    Regards

    AtishRG


    AtishRG
    Tuesday, November 2, 2010 9:34 AM