Is it SQL 2008 or VB 2008 problem? I don't know

[Marc Robitaille]

Hello,

I have created an application with Visual Basic2005 Standard Edition 2 years
ago. The application imports CSV files in the database. The application is
connecting to the database that I made it with SQL Server 2005. This is the
connection string:

Data Source=XXX; Initial Catalog=YYY; Integrated Security=SSPI;
Pooling=true;

The application uses the TransactionScope class this way with no problem:

Using Scope As New
Transactions.TransactionScope(Transactions.TransactionScopeOption.RequiresNew)
...
Scope.Complete()

End Using

In the transaction, the application executes a stored procedure with 3
parameters. I use the SqlClient namespace to do the job. This is the stored
procedure that I use:

ALTER PROCEDURE [dbo].[I_TDE_Imports]
@FilePath VARCHAR(500),
@FileName VARCHAR(40),
@TableName VARCHAR(6)
AS
DECLARE @sql NVARCHAR(2000)

SET @sql = 'INSERT INTO ' + @TableName + ' SELECT * FROM
OPENROWSET(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt;
*.csv)};DefaultDir=' + @FilePath + ';'',''SELECT * FROM ' + @FileName +
''')'
EXECUTE sp_executesql @sql

This year, I convert my code to Visual Basic 2008 Express Edition. I have
also switched my database from 2005 to SQL Server 2008 Express Edition. That
is where the problems started. The application does not work anymore. The
error is:

The requested operation could not be performed because OLE DB provider
"MSDASQL" for linked server "(null)" does not support the required
transaction interface.

I looked on the web to find an answer to this problem. Someone told me to
add a ";" in my OPENROWSET after MSDASQL like this:

SET @sql = 'INSERT INTO ' + @TableName + ' SELECT * FROM
OPENROWSET(''MSDASQL;'', ''Driver={Microsoft Text Driver (*.txt;
*.csv)};DefaultDir=' + @FilePath + ';'',''SELECT * FROM ' + @FileName +
''')'

I have another error:

The OLE DB provider "MSDASQL;" has not been registered.

But when I remove the ";" and put my TransactionScope declaration in
comments in my code, there is no more error and the application does is job
OK!!!

What is going wrong? I need that Transaction if something goes wrong. Is
there a new way to create a TransactionScope with VB Express 2008 that I am
not aware of? Is there somebody that had the same problem? If yes, how did
you solve the problem?

Thank you all!

Marc R.

 

[Alex Clark]

Have you tried using a SqlTransaction instead?

Dim trn = mySqlCon.BeginTransaction

.... ' do work

trn.Commit
trn.Dispose