Re: VB 2005 Insert records into multiple tables

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@xxxxxxxx> wrote in message
news:eldpIC3%23GHA.4740@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxx> wrote in message
news:uobiTGy%23GHA.4356@xxxxxxxxxxxxxxxxxxxxxxx
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.








.

Relevant Pages

  • Re: ntext getting truncated
    ... Tibor Karaszi, SQL Server MVP ... I applied the query as follows: ... select counthowmany, datalengthntextlength ...
    (microsoft.public.sqlserver.programming)
  • Re: upsizing to sql 2005
    ... which include MS SQL Server linked table. ... Query Name: Arcadia - ARC ... are still unable to upsize one of these queries, ...
    (microsoft.public.access.queries)
  • Re: VS.NET 2003, ADO.NET 1.1 and Access 2000: Getting a Concurrency violation
    ... I am using an Access update query, with parameters, for the ... Then my app goes against a SQL ... OleDbDataAdapter that I use to retrieve the original table from the ... So your named parameters for SQL Server suddenly become anonymous ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: upsizing to sql 2005
    ... A query in Access-Jet will be started and under the supervision of Jet, ... Jet syntax parsing and evaluation. ... so, in this case, into MS SQL Server. ...
    (microsoft.public.access.queries)
  • Re: Asynchronous Stored Procedure Never Returns - Help?
    ... If you have the Sql Server 2000 or 2005 docs they are thorough and can be ... for Transaction SQL Reference from the drop-down or select a keyword from ... your query in Query Analyzer or Sql Server Management Studio, ...
    (microsoft.public.dotnet.languages.csharp)
Loading