Bulk Insert Via SQLCommand?

[Jason]

Hi. I am trying to bulk insert a text file into SQL Server 2000. The command
works fine if run from the QueryAnalyzer, but when I run from within my code
it fails with a "System Error".

Bulk insert SQL (which works in QA) is:
BULK INSERT MyCatalog.dbo.Tempbcptest FROM '\\MyComputer\c$\TestClass.txt'
WITH (FIELDTERMINATOR = '~')

I am attempting to run from VB.NET with the following code:

SQLCmd = New System.Data.SqlClient.SqlCommand
SQLConn = New System.Data.SqlClient.SqlConnection
With SQLConn
.ConnectionString = "Initial Catalog=" & SQLCatalog
& "; Data Source=" & SQLServer & "; Integrated Security=SSPI"
.Open()
End With
sCmdString = "BULK INSERT " & SQLCatalog &
".dbo.Tempbcptest FROM '\\MyComputer\c$\TestClass.txt' WITH (FIELDTERMINATOR
= '~')"
With SQLCmd
.Connection = SQLConn
.CommandText = sCmdString
.ExecuteNonQuery()
End With

Why does this generate an error? Any code to help me accomplish this is
appreciated!

 

[Sahil Malik]

Jason,

Are you using different connection strings between Query Analyzer and your
ADO.NET application? Do they both have the same access rights? Is your
VB.NET a part of an ASP.NET app?

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[Jason]

I am not sure what you mean about a connection string in Query Analyzer; I
am connected to the same server and database as specified in my VB
connection string through the QA interface. Access rights should be the same
as I am a sysadmin on both the Windows network and the SQL Server. No, not
an ASP.NET app, just straight VB.NET.

 

[Elton Wang]

Did you run it in Web or Windows application?

Elton Wang
(e-mail address removed)

-----Original Message-----
Hi. I am trying to bulk insert a text file into SQL Server 2000. The command
works fine if run from the QueryAnalyzer, but when I run from within my code
it fails with a "System Error".

Bulk insert SQL (which works in QA) is:
BULK INSERT MyCatalog.dbo.Tempbcptest

FROM '\\MyComputer\c$\TestClass.txt'

 

[Jason]

Windows.

Did you run it in Web or Windows application?

Elton Wang
(e-mail address removed)
FROM '\\MyComputer\c$\TestClass.txt'

 

[Sahil Malik]

What I meant was ---> So you are using Windows Authentication in Query An.
Is that what you are using in your application's connection string?

 

[Elton Wang]

Use

Try
With SQLCmd
.Connection = SQLConn
.CommandText = sCmdString
.ExecuteNonQuery()
End With
Catch exc As SqlException
MessageBox.Show(exc.Message)
End Try

to find out more detail error message.

 

[Jason]

Hm, thanks. I tried that, but it breaks on the .ExecuteNonQuery() line and
won't proceed to give me any more detailed info.

 

[Jason]

Yes, I'm using Windows authentication in both.

What I meant was ---> So you are using Windows Authentication in Query An.
Is that what you are using in your application's connection string?

 

[Jason]

The trouble was, I had to pass the command string preceded by "Exec", and
thus also had to adjust the apostrophes. For some reason it takes twice as
long to run as it did from QA though.

This works:

sCmdString = "Exec('BULK INSERT " & DatabaseName & ".dbo.Tempbcptest FROM
''\\MyComputer\C$\TestClass.txt'' WITH (FIELDTERMINATOR = ''~'')')"

 

[Sahil Malik]

Sweet !! Something for us to learn over here LOL

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[Elton Wang]

Congratulation!

I just wondering when I execute a Bulk sql in one
application as follows

comm.CommandText = "BULK INSERT " +
System.Configuration.ConfigurationSettings.AppSettings
["workTable"] + " FROM '" + fileName + "' WITH
(DATAFILETYPE = 'char', FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')";

It inserts 100000+ line records without any trouble.

Elton Wang