Deleting a MS SQL Batch Object with single quote in the name

The tool is BMC SQL-Programmer but I don't think that's the issue other than it can't handle the deletion.

let's see, you tried all of the below:

drop table "Fix wo_srno item_srno_id's in wo's 1001-1007"
 
drop table [Fix wo_srno item_srno_id's in wo's 1001-1007]
 
drop table ["Fix wo_srno item_srno_id's in wo's 1001-1007"]
                                              

1:
2:
3:
4:
5:

I assume you meant 'drop batch' and I previously tried all of those forms. Just to be sure, I just tired them again using both drop table and drop batch.

Interestingly, when I try drop table I get an error saying that table doesn't exist. However, when I try drop batch I get an error saying Incorrect syntax near "Fix wo_srno item_srno_id's in wo's 1001-1007"

hmmm...
actually DROP BATCH is not t-sql, unless I missed something.

so, it something specific to BMC application.
anything in the documentation of that software for the statements vs special characters?

what was the exact code to create the object?
is there some reference that shows how to "list" the existing batches?

Well, I got the 'drop batch' from having SQL Programmer script the drop action. The script to recreate a batch object as produced by SQL Programmer is:

drop batch dbo.Transfer_Select
GO

create batch dbo.Transfer_Select
select * from sysobjects

endbatch
GO

I just put in a random select to give you the idea., but otherwise... this is what SQL Programmer gives you for dropping and recreating a batch object.

Your remarks got me to checking and I found the following:

SQL Server batch object command syntax
SQL-Programmer has developed extensions to SQL Server syntax to create, execute, and drop batch objects.

Commands

CREATE BATCH [owner.] batch_name sql_statements ENDBATCH

DROP BATCH [owner.] batch_name

EXECUTE BATCH [owner.] batch_name

Keywords and parameters

owner

(optional)
 Specifies the owner of the batch object.

The default is the username of the creator, or in the case of a drop or execution, the username of the user requesting the drop or execution. Owner must be a valid user of the database where the batch object is created.
 
batch_name
 Specifies the name of the batch object to be created, dropped, or executed.
sql_statements
 The SQL statements or other batch object statements.
 
ENDBATCH
 The keyword to specify the end of the batch object.

Notes:

If the creator is the dbo of the database, the creator can specify any valid user as owner, and can execute or drop any batch object, regardless of the owner. To create a batch that can be executed by all users, owner must be publicbatch.
If the creator of the batch is not dbo of the database, the creator can only specify their own user name as owner. Only batch objects owned by the user or those created as publicbatch can be executed. Only batch objects owned by the user can be dropped.
batch_name is case-sensitive if server is. The rules for naming batch objects are as follows:
first character must be alphabetic
the underscore symbol ( _ ) is allowed
maximum length 30 characters
The ENDBATCH keyword is used by SQL-Programmer to distinguish between a GO command in a batch and the end of the CREATE BATCH transaction. The SQL-Programmer Script Generator will read the ENDBATCH keyword when scripting batch objects. For more information on generating scripts, see Script Interface.
The number of nested levels of batch objects is unlimited. For example, batch objects can nest CREATE statements that create, execute, or drop other batch objects.
There must be exactly one space between the COMMAND name, KEYWORD or parameter name, and the batch_name.

So, I'll have to do some digging to discover how and where they are kept. I may just drop and recreate the database as an expedient.

There should be no reason to do that.

I'm guessing that it could have been created in the app DB, the master or the msdb.

Run the following queries from the SQL Server Management Studio and see if you can find it.

If you can -- tell us what DB it is in and things like the id, xtype, etc. There are ways force a delete without starting from scratch.

select *
from master.dbo.sysobjects
where [name] like '%item_srno_id%'
--------------------------------------------
select *
from msdb.dbo.sysobjects
where [name] like '%item_srno_id%'
--------------------------------------------
select *
from [MyDBName].dbo.sysobjects
where [name] like '%item_srno_id%'

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

The batch object is contained in a db called 'test'. I ran all three querys with the last one agaist test to no avail. Nothing was returned.  I also tried searching for '%Fix%' and '%1001%' in case the name was truncated in some manner. SQL-Programmer help suggests that the names can only be 30 characters long. I didn't read that before I saved them. I guessed that if I did something the tool didn't like, it would reject it. Ah if only life were so simple.

I'm beginning to think this isn't worth the effort. It's a test database which I can recreate without a great deal of effort. It may be best to simply drop it and recreate it.

Kevin

Well, by hook and crook, I found them. They are stored in a table added by SQL-Programmer called 'SQL_Programmer_Objects'. For whatever reason I didn't see it in the list of tables although I have some types turned off in the display. Unfortunately, I found this only after I dropped the database and started recreating it. Thanks for your help. I will say your telling me that drop batch was not a sql command is what put me onto the answer.