Sql can't drop table

Hi friends; i have this SP insql server and work Good...

but some times sql can't drop table in top of my code. why?

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE   [dbo].[GetStationCountAndOnlineStationsListGrroupByProvince_Ranking]
 @FK_RegionID  bigint
AS
BEGIN

-- Some times can't drop table and i have to drop it manually
begin try
drop table tblinfo
end try
begin catch
end catch


select  tblstations.StationID,stationname,tblstations.WMOCode,tblStations.IKAOCode,StationTypeName,tblstations.IsAutoStation,Stations_OnlineList.logtime as 'OlineDataTime' ,Stations_LogTimeList.logtime as 'LogTime'
into tblinfo
from tblstations  inner join tblStationTypes   on   tblstations.FK_StationTypeID=tblStationTypes.StationTypeID inner join Stations_LogTimeList  on Stations_LogTimeList.StationID=tblstations.stationid inner join
Stations_OnlineList on  Stations_OnlineList.StationID=tblstations.stationid 
 where tblstations.FK_RegionID=@FK_RegionID   and  tblstations.Active=1 

 select StationID,stationname,WMOCode,IKAOCode,tblinfo.StationTypeName,OlineDataTime,LogTime,IsAutoStation, case   when OlineDataTime>DATEADD(hour,-2,getdate())  then  'true' else 'false'   end  as 'Parameter'
  ,case   when LogTime>DATEADD(hour,-5,getdate())  then  'true' else 'false'   end  as 'Report'
    from tblinfo
end
April 27th, 2015 12:47am

Is the problem you cannot drop the table because it is not there?  Do you receive an error message? 

If the problem is that your stored proc throws an error because the table you are trying to drop does not exist yet then this should fix it:

-- Some times can't drop table and i have to drop it manually
begin try
IF OBJECT_ID('dbo.tblinfo') IS NOT NULL 
drop table tblinfo
end try
begin catch
end catch

Free Windows Admin Tool Kit Click here and download it now
April 27th, 2015 12:53am

Whats the error message you get when drop is not working?

I cant see anything obvious in the code you posted so error message would help

April 27th, 2015 1:00am

Thank you mr LucasF,

but table is exist and SQL can't drop it.

Free Windows Admin Tool Kit Click here and download it now
April 27th, 2015 1:05am

As per MSDN,

Important note Important

DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch.

Otherwise an unexpected error may occur.

You may also check that the user has sufficient permission on the table to alter it

Requires ALTER permission on the schema to which the table belongs, CONTROL permission on the table, or membership in the db_ddladmin fixed database role.


April 27th, 2015 1:16am

What is the error message you get?
Free Windows Admin Tool Kit Click here and download it now
April 27th, 2015 1:21am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics