Hi All,

Not sure if this is the right forum to post this!
VB 2005 Prof, SQL server 2005, Windows application

Scenario: In a winform application, using SQL server authentication method
(not windows authentication), we have a text/binary file containing multiple
SQL statements (insert,update,delete commands) for multiple tables.
From the Winform , we need to create a process wherein we process this file
and update the database.
In case of a failure to process any sql statement in the middle of this file
(for whatsoever may be the reason , possibility is remote but should not be
excluded), then all the transaction need to be rolled back - meaning the
database state should be same as before we started the processing of this
file.

Query: What is the best method to carryout this functionality without using
DTS. Ofcourse, this process should be executed from a winform and from a
client machine.

Thanks.

In the input file, start a TRANSACTION before the current SQL Statements,
test each statement for success/failure, and either ROLLBACK abort further
processing, or at the end, COMMIT as appropriate.

However, you are opening your server to major security risks.

What is the nature of the input file?
Is it being created by the user?
Created by the application?
Or is it static?
Could it be transformed into a stored procedure?

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf


"AnikSol" <anisol (AT) community (DOT) nospam> wrote

Arnie,

Thanks.

Maybe I wan't clear in asking my query.

What I wanted to know was which method would be more appropriate -
- using BCP (since the file only contains valid SQL commands) - what happens
in case if there is a failure after processing of few records?
- use transaction scope (but somehow I am unable to do multiple commands in
the scope)
- use Adon.net transaction ( transaction.begin , commit, etc) (this is the
way you have recommended , but with a caution)

The file is in fact generated by an application so the chances of having an
improper sql command string is quite remote. It is definitely not static.
There would be one file to be "imported " daily.

thanks.


"Arnie Rowland" <arnie (AT) 1568 (DOT) com> wrote

Hello Anik,

According to your situation, I think you may try to consider to use the
ADO.NET transcation so that you could control the transaction in your
application.

Sincerely,

Wei Lu

Microsoft Online Community Support

==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.

==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Hi ,

How is everything going? Please feel free to let me know if you need any
assistance.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Wei,

Thanks. We have implemented the ado.net transactions.

regards

"Wei Lu [MSFT]" <weilu (AT) online (DOT) microsoft.com> wrote