ERROR EXECUTING PROCEDURE on SQLS2K

  • Hello,

    I know that this is a forum where we discuss anything about SQL Server 2005 but im having a problem that appears in a stored procedure in SQL Server 2000 that i think that you can help me.

    :sick:

    I've created the following procedures that will send me the output of:

    Instance Name / Nº of DBs / Total Space / Allocated Space and Free Space.

    Question:

    The first time i execute the stored procedure everything goes fine and the following output is shown:

    Instance Name Nº de BDs TotalSpace_MB UsedSpace_MB FreeSpace_MB

    -------------------- ----------- --------------- -------------- ------------

    MyInstanceName 19 286000 67300 218700

    My problem:

    The second time i execute the stored sprocedure an error is returned and i need to drop procedure and tables created to get the procedure to return results again.

    Error:

    Server: Msg 213, Level 16, State 4, Procedure sp_GetSpaceInfo2k, Line 59

    Insert Error: Column name or number of supplied values does not match table definition.

    I cant get what is wrong in the procedure.

    Can you help me?

    Thanks and regards,

    JMSM 😉

    /* ---------------------*/

    /* MyProc sp_GetSpcInf */

    /*----------------------*/

    use MyDBName

    go

    create procedure sp_GetSpcInf

    as

    Create TABLE #db_file_information(

    fileid integer

    , theFileGroup integer

    , Total_Extents integer

    , Used_Extents integer

    , db varchar(30)

    , file_Path_name varchar(300))

    -- Get the size of the datafiles

    insert into #db_file_information

    ( fileid

    , theFileGroup

    , Total_Extents

    , Used_Extents

    , db

    , file_Path_name )

    exec sp_MSForEachDB 'Use ?; DBCC showfilestats'

    -- add two columns to the temp table

    alter table #db_file_information add PercentFree as

    ((Total_Extents-Used_Extents)*100/(Total_extents))

    alter table #db_file_information add TotalSpace_MB as

    ((Total_Extents*64)/1024)

    alter table #db_file_information add UsedSpace_MB as

    ((Used_Extents*64)/1024)

    alter table #db_file_information add FreeSpace_MB as

    ((Total_Extents*64)/1024-(Used_Extents*64)/1024)

    if exists (select * from sysobjects where name = 'myauxtbl' and xtype in (n'u'))

    drop table [dbo].[myauxtbl]

    create table MyAuxTbl

    (fileid integer

    , theFileGroup integer

    , Total_Extents integer

    , Used_Extents integer

    , db varchar(30)

    , file_Path_name varchar(300)

    , PercentFree int

    , TotalSpace_MB bigint

    , USedSpace_MB bigint

    , FreeSpace_MB bigint

    );

    insert into MyAuxTbl select * from #db_file_information;

    -- select * from MyAuxTbl;

    -- select @@servername as 'Instance Name', sum(((Total_Extents*64)/1024)) as TotalSpace_MB, sum(((Used_Extents*64)/1024)) as UsedSpace_MB, sum(((Total_Extents*64)/1024-(Used_Extents*64)/1024)) FreeSpace_MB from MyAuxTbl;

    -- select @@servername as 'Instance Name', (select count(*) from master..sysdatabases) as 'NumDBs', sum(((Total_Extents*64)/1024)) as TotalSpace_MB, sum(((Used_Extents*64)/1024)) as UsedSpace_MB, sum(((Total_Extents*64)/1024-(Used_Extents*64)/1024)) FreeSpace_MB from #db_file_information;

    select @@servername as 'Instance Name', (select count(*) from master..sysdatabases) as 'NumDBs', sum(((Total_Extents*64)/1024)) as TotalSpace_MB, sum(((Used_Extents*64)/1024)) as UsedSpace_MB, sum(((Total_Extents*64)/1024-(Used_Extents*64)/1024)) FreeSpace_MB from MyAuxTbl;

    drop table #db_file_information

    -- exec MyDBName..sp_GetSpcInf

  • JMSM (3/18/2009)


    I know that this is a forum where we discuss anything about SQL Server 2005 but im having a problem that appears in a stored procedure in SQL Server 2000 that i think that you can help me.

    Please post SQL 2000 questions in the SQL 2000 forums in the future.

    Your problem is probably this line

    insert into MyAuxTbl select * from #db_file_information;

    Specify the columns for the insert and the select.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi

    The problem is that the temp table already exists at second execution and the engine and now it first determines if the INSERT fits to the available columns.

    There are two possible solutions:

    Either drop the temp table after the select at the end of the procedure,

    or specify the destination columns at the INSERT statement.

    Greets

    Flo

Viewing 3 posts - 1 through 2 (of 2 total)

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