M
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.
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.