Database Partitioning - Partition switching

Hi,

I need replies immediately...

This is with regard to switching of partitions in a sliding window scenario.

I wrote a stored procedure which does the partition switching in a sliding window scenario. During one switching, if the stored procedure is half executed, if I re-execute the same procedure it throws errors like

'Filegroup is in a different partition switching failed'

So how to rollback all the transactions in the stored procedure that does switching of partitions? If it is not possible, then how to identify whether a file group is in its original partition before the stored procedure is part executed? How to bring it back to its original partition before executing the stored procedure again switching?

Can any one help me immediately...

Thanks

Sekharpc

Moving thread to appropriate forum.|||

hi sek,

you have to enclose your sp scripts in a begin transaction.... end transaction clause..

create sp swtichpartion

(

)

as

begin transaction

partition switch... code1

partition switch code 2.

partition switch code 3.

commit transaction

in this way all switch task must commit as a unit. a failure in one job

fails the entire operation.

for a more comprehensive solution you can also incorporate the 2005

error handling feature try.. catch block for more information

you can consult my blogs look for the topic

The SQL SERVER 2005 Try Catch Block look for sub topic

"try catch with transaction"

or BOL

regards,

joey