I have SQl Server 2000. I wrote following stored procedure for one of my queries. I am .net programmer do not know much about sql. All of a sudden from last week , our tempdb database is blown up and i have to restart sql server each time to get it working. i searched online for solutions , one of the links said that sometimes using temporary tables can do that. Here is my stored procedure , i just want to know , if my stored procedure is the potential cause of this error and if yes, i need help of all the experts out there to solve this issue.
CREATE PROCEDURE [dbo].[sp_GetLocations]
-- Add the parameters for the stored procedure here
(
@loc_required int
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @gaps table (id int identity(1, 1),Lct varchar(10), locid int)
insert into @gaps(Lct, locid)
select location, id from location where filled =0 order by id
declare @templocation table(from_cell varchar(10),to_cell varchar(10),no_of_free_cells int)
insert into @templocation
select g1.Lct as 'from cell', g2.Lct as 'to cell' , [number of free cells] from
(select min(locid) as 'from_cell' , max (locid) 'to_cell' , count(Lct) as 'number of free cells' from @gaps
group by left(lct, 1), locid-id ) x
inner join @gaps g1 on x.from_cell = g1.locid
inner join @gaps g2 on x.to_cell = g2.locid
declare @finallocation table(from_cell varchar(10),to_cell varchar(10),no_of_free_cells int)
insert into @finallocation
select * from @templocation where no_of_free_cells >= @loc_required order by no_of_free_cells,from_cell
select top 1 * from @finallocation
END
GO
And my application gives me following error:-
Exception type: System.Data.SqlClient.SqlException
ExceptionMessage: Warning: Fatal error 9001 occurred at Feb 24 2010 9:27PMက催՛ꁄ ꀸ ꁸ 灴Ʋꁄ ꀸ . Note the error and time, and contact your system administrator.
Source: .Net SqlClient Data Provider
StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Location.findlocations(Int32 loc_required) in D:\Inetpub\wwwroot\AssemblyParts\App_Code\Location.vb:line 23
at RotorAssemby.gvRotor_RowCommand(Object sender, GridViewCommandEventArgs e) in D:\Inetpub\wwwroot\AssemblyParts\RotorAssembly.aspx.vb:line 263
i ran dbcc checkdb (tempdb) with all_error_msgs. But it didnot return me any msgs. Thanks in advance
