Bulk Insert Via SQLCommand?

  • Thread starter Thread starter Jason
  • Start date Start date
J

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!
 
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.
 
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
Click to expand...
FROM '\\MyComputer\c$\TestClass.txt'
 
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?
 
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.
 
Hm, thanks. I tried that, but it breaks on the .ExecuteNonQuery() line and
won't proceed to give me any more detailed info.
 
Yes, I'm using Windows authentication in both.

Sahil Malik said:
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?
Click to expand...
 
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 = ''~'')')"
 
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
 
Back
Top