Drop DB via SQSH

I want to drop a remote sql server DB using SQSH. I’ve tried two ways;

the first one is to set the DB offline given that the auto change is off.

sqsh -h -S databaseHost -D master -U USER -P PASS -C "ALTER DATABASE ${TARGETDB} set OFFLINE WITH ROLLBACK IMMEDIATE; DROP DATABASE ${TARGETDB}"

This results

Error: 615, Severity: 21, State: 1. Could not find database ID 9356, name '9356'

The second way

sqsh -h -S databaseHost -D master -U USER -P PASS -C  "ALTER DATABASE ${TARGETDB} set SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE ; DROP DATABASE ${TARGETDB}"

This works fine but if no one else is connecting/trying to connect to the DB, if so then it results

The database is in single-user mode, and a user is currently connected to it. 

The second way should work fine because I set it to single-user and drop it in the same connection, or it’s supposed to be the same connection, any idea how to solve this?

Multiple issues with the way you are trying to DROP .. the database.

1. The SQSH command prompt doesn’t seem to be able to drop an OFFLINE database.
2. When set to SINGLE_USER a database can be used by any user. You might have connections to the database between switching the database to SINGLE_USER mode and dropping the database. Or the SQSH command prompt has a handle on the database.

To overcome these issues you might want to perform the following:

1. Set database offline.
2. Set database online.
3. Set database into restricted user mode.
4. Drop database.

In summary this would look like this:

ALTER DATABASE <database> SET OFFLINE WITH ROLBACK IMMEDIATE
GO
ALTER DATABASE <database> SET ONLINE
GO
ALTER DATABASE <database> SET RESTRICTED_USER
GO
DROP DATABASE <database> 
GO

Now sqsh is a replacement for Sybase’s isql command prompt executable. As such there may be reasons why this will not work with SQL Server (as your tag is suggesting).