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

I am working with MS SQL Server 2005. Using a development tool, I have managed to create a batch object with single quotes in the object name. To wit: "Fix wo_srno item_srno_id's in wo's 1001-1007". Ugly name I know. In any case, I now find that I cannot drop this same batch object using that tool. I know it doesn't like the spaces and I suspect it also doesn't like the quote marks. I have tried using squarce brackets around the name to no avail. There has to be a simple way to delete this object, but I can't find it so I'm asking you fine people... Help!

Thanks, Kevin
ogreholdingsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the tool?
0
ogreholdingsAuthor Commented:
The tool is BMC SQL-Programmer but I don't think that's the issue other than it can't handle the deletion.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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"]

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

ogreholdingsAuthor Commented:
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"
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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?
0
ogreholdingsAuthor Commented:
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.
0
ogreholdingsAuthor Commented:
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.
0
Jim P.Commented:
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%'

Open in new window

0
ogreholdingsAuthor Commented:
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
0
Jim P.Commented:
Then I'm guessing that it is probably in a table in the DB and is only used by the SQL-Programmer app.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ogreholdingsAuthor Commented:
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.
0
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows 2000

From novice to tech pro — start learning today.