querying from Excel and using multiple statements separated by semicolon

I am trying to feed this query to a DB2 database via excel.  I get an error message back from the db that is complaining about syntax of a semicolon.  
When I run the exact same text as a query in a native db query application it runs just fine, no issues.  

Any thoughts on why sending this via Excel is a problem?  
Any way to get around it aside from running each one as a separate query?
 

 drop alias qtemp.zz1 ;  drop alias qtemp.zz2 ;  drop alias qtemp.zz3 ;  drop alias qtemp.zz4 ;  drop alias qtemp.zz5 ;
LVL 11
RayData AnalystAsked:
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.

Dave FordSoftware Developer / Database AdministratorCommented:
What is the exact error-message that you receive?
0
RayData AnalystAuthor Commented:
Dave,
It is attached but it won't tell you anything.  I get the exact same error even running ONE of those (such as:  drop alias qtemp.zz1 ; ) if I include the semicolon.  

The bottom line appears to be that passing a semicolon to a query engine via VBA is problematic.
semicolon-error.PNG
0
[ fanpages ]IT Services ConsultantCommented:
Hi Ray,

(I am assuming you are using an ADODB connection & executing the SQL statement on this connection)

I think you have probably reached this conclusion already...

"Any thoughts on why sending this via Excel is a problem?"

As far as I am aware, you cannot run serial commands/statements.  Each has to be in a separate execution on the database object.

"Any way to get around it aside from running each one as a separate query?"

Could you write a stored procedure (in your database) that drops all five tables, & execute that as a single statement from MS-Excel?

Alternatively, a stored procedure that accepts variable parameters (in this case, the name of five tables), & assumes each parameter is a table name, then drops each in turn.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RayData AnalystAuthor Commented:
Looks like I solved this one with some internal help at my office.  Turns out that there are two catches to doing this, but it can be done via Excel and an ADO query connection.

1. you must start your group of statements with "BEGIN " and complete the batch with " END".

That will let the query actually run fine if not doing a select (this is referred to as a “dynamic compound statement”)
(such as:  "BEGIN   drop alias qtemp.zz1 ;  drop alias qtemp.zz2 ;  drop alias qtemp.zz3 ;  drop alias qtemp.zz4 ;  drop alias qtemp.zz5 ;   END  "
If doing a select, when it comes time to deliver your results you get an error about "select into".  At this point you realize that it treats your query like a stored procedure and therefore must have something to "select into" as it doesn't know what to do with the result set.  For me this led to step 2.
2. Part 2 is to select your results into a temp table, then have excel execute a query to retrieve everything from the temp table.

Easy as pie  HaHaHaHaHa

This all started because I was running a small query to populate a list in excel from 1 to 100 entries, those entries would cause my formulas to build from 4 to 300 separate sql statements ending up in a query selecting from that mess.  So now I can run all those statements in one query sent to the DB server via excel, drop the results in to a temp table, then query the results out of that temp table.  Works beautifully.
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
[ fanpages ]IT Services ConsultantCommented:
Thanks Ray.

You may wish to look at the difference between "atomic" & "non-atomic" Compound Dynamic SQL Statements, if you are going with the "BEGIN...END" transaction-based route:

[ http://www.mcpressonline.com/sql/techtip-compound-dynamic-sql-statements.html ]

PS. You did not mention Part 2 until now, otherwise I would have suggested the approach you mentioned.

Good luck in any respect.
0
RayData AnalystAuthor Commented:
Fanpages, I didn't know about part 2 'til after I conquered part 1    :-)
0
RayData AnalystAuthor Commented:
Solved it with some intra-company help.
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
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.