March 18, 2009 at 12:03 pm
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
March 18, 2009 at 2:03 pm
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
March 18, 2009 at 2:06 pm
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