drop table

I upgraded my system from Sql2000 to sql2005.

And now i have a problem.

I have job wich runs every minute and perform the following

select * into tabler_1 from .....

begin tran

if exists(select * from sysobjects where xtype ='u' and name = 'tabler')

drop table tabler

exec sp_rename tabler_1, tabler

commit

And on client side i have IIS which runs asp pages which connect to database with

ado connction using ODBC and runs procedures wich performs select * from tabler

and there is a problem they some times failes with error tabler does not exists.

It worked fine in sql2000

all connections opened with default setting.

(transaction isolation level read commited)

What abou this here:

if not exists

(

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'tabler_1'

AND TABLE_TYPE = 'BASE_TABLE'

)

select * into tabler_1 from SomeTable

if exists

(

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'tabler'

AND TABLE_TYPE = 'BASE_TABLE'

)

BEGIN

DROP TABLE tabler

EXEC sp_rename tabler_1, tabler

END

Make sure that you check the existence of the table before doing a SELECT INTO.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

It is not a problem.

it make drop very nice.

the problem is on the client which runs store procedure like this

create procedure test_sp

as

select * from tabler

go

OR

create procedure test_sp_d

as

exec ('select * from tabler')

go

when i droping table i do it in transaction so schema is locked

the procedures have to wait for commit and then select

it worked fine sql2000

sql