Solved

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

Posted on 2009-07-03
13
272 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:ogreholdings
  • 6
  • 3
  • 3
13 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24774531
what is the tool?
0
 

Author Comment

by:ogreholdings
ID: 24774557
The tool is BMC SQL-Programmer but I don't think that's the issue other than it can't handle the deletion.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24774573
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:ogreholdings
ID: 24774603
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24774650
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
 

Author Comment

by:ogreholdings
ID: 24775057
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
 

Author Comment

by:ogreholdings
ID: 24775092
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 24777055
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
 

Author Comment

by:ogreholdings
ID: 24780415
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
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 24780438
Then I'm guessing that it is probably in a table in the DB and is only used by the SQL-Programmer app.
0
 

Author Comment

by:ogreholdings
ID: 24780549
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 24784496
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL LINE CONTINUATION ISSUE 12 31
Help Required 2 29
VB.net and sql server 4 33
Update a text value in another table 10 35
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now