SQl server 2000 stored proc to detach database gives Error "Only user processes can be killed. Cannot detach database as it is in use"

are you, by any chances, connected yourself to the database you are trying to detach?

no i am not . Also, this error pops up intermittently. On most occasions it works fine.

how often do you run that detach? regulary?
can you give a bit more insight in what/how you are doing?

This detach is sometimes run very frequently , once every 15 mins.  

The application needs an mdf file to be sent across to a web service.
My application creates a copy of an existing mdf file and attaches it to a new database, adds some data using some stored procedure into the attached database. Then the database is detached and the mdf with the data inside is sent across.   This detach is failing.  The sp code snippet is as follows:

 SELECT spid FROM MASTER..SYSPROCESSES  
  WHERE dbid=db_id('db_name')

loop through all those spids and then execute  
 SET @sql_KillProcess = 'kill ' + cast(@nSPID AS varchar(200))    
   EXEC (@sql_KillProcess)  

then finally outside the loop call EXEC sp_detach_db @vcDBName  

>The application needs an mdf file to be sent across to a web service.
I would not make it that way. I would issue a BACKUP DATABASE statement, and send the backup file, and restore the backup file instead.

I am not quite clear on what you mean. Do you mean that instead of detaching the database, take its backup and send that. But what should be done the next time? There will be multiple databases then that would be generated everytime.

I am not in a state to change the design right now. But I would be glad if there is a way to fix the bug by changing the stored procedure ?

Thanks