Solved

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

Posted on 2009-07-03
13
265 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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

706 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

21 Experts available now in Live!

Get 1:1 Help Now