SQL Server - Bulk statements execution

Hi,
I am using SQL Server 2008R2, and noticed that every time I sent a group of statements in once for execution, and there is an error in a statement positioned in the middle, SQL Server will stop the execution (which is right), but can't return back the error message to the client.
Example
globalStmt = stmt1
globalStmt += "; " + stmt2
globalStmt += "; " + stmt3
globalStmt += "; " + stmt4
globalStmt += "; " + stmt5
try
    mydatabaseObject.begintrans()
    mydatabaseObject.executeSQL(globalStmt)
    mydatabaseObject.commit()
catch (exception e)
   mydatabaseObject.rollback()
   msgbox(message())
endtry
Let's say stmt2 has a syntax error, the rest of stmts won't be executed (which is right), but I am not getting back an error when it is a group of statements sent for execution.
Is there any SQL Server Command that can force this to happen, as it does when it is a single statement?

Thanks for your inputs.
Omer-PitouAsked:
Who is Participating?
 
Omer-PitouAuthor Commented:
The solution is to have that a bulk  statements ran into a stored procedure
0
 
ste5anSenior DeveloperCommented:
First of all: Exception is generic and should never be catched in this place. Catch the concrete exception instead. SqlException should return the errors.

Your code sample is also too vague. What types are involved, especially what type has mydatabaseObject?
0
 
Omer-PitouAuthor Commented:
I am using dBase Plus.
The type of mydatabaseObject is a database object
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
ste5anSenior DeveloperCommented:
Then, please, change the topic. And I'm out.. :) The last time I've seen dBase was in the last millenium.
0
 
Omer-PitouAuthor Commented:
This isn't about dbase. I can track the same error using mysql.
0
 
Omer-PitouAuthor Commented:
The last version of dbase came out like 6 months ago. I don't which dbase you referring to.
0
 
ste5anSenior DeveloperCommented:
I wanted to express that, if it's an dBase issue, that

1) the topic is wrong.
2) I don't have a clue. Cause the last time I've worked with dBase was in the 90s.

Where does MySQL comes now from??

Can you please rephrase your question and topic? SQL Server, dBase, MySQL and an incomplete C#.NET code snippet..

btw, "a database object" is not really a type, when we are talking about C#.NET here.

p.s. have you read the link about SqlException?
0
 
Omer-PitouAuthor Commented:
I am a little bit confused, and surprised that you refer to c#.net, while I didn't mention that in one of my message.
Anyway I have a workaround for that: I will just submit my statements individually, and from this,  I can be able to track what statement is causing problems.

-> btw, "a database object" is not really a type, when we are talking about C#.NET here.
 mydatabaseObject  is an instance of the Class DATABASE of dBASE. So, it is an object, as It inherits attributes and methods of that class. Some of the methods are begintrans(), executeSQL(), commit(), etc... as indicated in my first message.

The reason why I mentioned MySQL is that I can sent a group of statements in once, and track which one causes problem in try statement.

Thanks.
0
 
ste5anSenior DeveloperCommented:
Your code looks like .net and with SQL Server this is the most common stack. Thus my mistake. So, please create a new question with topic dbase and delete my nonsense.

Your problem is only a dbase problem.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
This doesn't looks like SQL Server code. It's similar but isn't T-SQL.
Can you share with us the tools that you are using for coding?
0
 
ste5anSenior DeveloperCommented:
@Vitor:  A new thread, properly tagged with more details in the op, makes imho more sense. Too much noise from me.
0
 
Omer-PitouAuthor Commented:
Only the code included in stmt variables are SQL Server
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
And how do you pretend to run this?
0
 
Omer-PitouAuthor Commented:
From dBase, there are methods to run sql statements
executeSQL(stmt),
0
 
Omer-PitouAuthor Commented:
Stored procedure will help to track individual statement error while executing
0
 
Omer-PitouAuthor Commented:
Stored procedure in this case  will help to track individual statement error while executing
0
 
Omer-PitouAuthor Commented:
Stored procedure will help to track individual statement error while executing
0
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.

All Courses

From novice to tech pro — start learning today.