Bulk Insert Via SQLCommand?

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
Wed Jan 05 13:20:44 CST 2005

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" <someone@microsoft.com> wrote in message
news:OPL$Tb18EHA.3820@TK2MSFTNGP11.phx.gbl...
> 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!
>
>

Jason
Wed Jan 05 13:25:40 CST 2005

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.

"Sahil Malik" <contactmethrumyblog@nospam.com> wrote in message
news:OKPcnu18EHA.3596@TK2MSFTNGP12.phx.gbl...
> 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" <someone@microsoft.com> wrote in message
> news:OPL$Tb18EHA.3820@TK2MSFTNGP11.phx.gbl...
>> 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!
>>
>>
>
>

Elton
Wed Jan 05 13:37:06 CST 2005

Did you run it in Web or Windows application?

Elton Wang
elton_wang@hotmail.com
>-----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'
>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!
>
>
>.
>

Jason
Wed Jan 05 13:41:52 CST 2005

Windows.

"Elton Wang" <elton@discussions.microsoft.com> wrote in message
news:04ff01c4f35d$f0aaf3e0$a401280a@phx.gbl...
> Did you run it in Web or Windows application?
>
> Elton Wang
> elton_wang@hotmail.com
>>-----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'
>>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
Wed Jan 05 13:51:50 CST 2005

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" <someone@microsoft.com> wrote in message
news:eNvQZx18EHA.3944@TK2MSFTNGP12.phx.gbl...
> 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.
>
> "Sahil Malik" <contactmethrumyblog@nospam.com> wrote in message
> news:OKPcnu18EHA.3596@TK2MSFTNGP12.phx.gbl...
> > 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" <someone@microsoft.com> wrote in message
> > news:OPL$Tb18EHA.3820@TK2MSFTNGP11.phx.gbl...
> >> 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!
> >>
> >>
> >
> >
>
>

Elton
Wed Jan 05 13:54:40 CST 2005

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.


>-----Original Message-----
>Windows.
>
>"Elton Wang" <elton@discussions.microsoft.com> wrote in
message
>news:04ff01c4f35d$f0aaf3e0$a401280a@phx.gbl...
>> Did you run it in Web or Windows application?
>>
>> Elton Wang
>> elton_wang@hotmail.com
>>>-----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'
>>>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!
>>>
>>>
>>>.
>>>
>
>
>.
>

Jason
Wed Jan 05 14:08:28 CST 2005

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

"Elton Wang" <elton@discussions.microsoft.com> wrote in message
news:146a01c4f360$6480d030$a501280a@phx.gbl...
> 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.
>
>
>>-----Original Message-----
>>Windows.
>>
>>"Elton Wang" <elton@discussions.microsoft.com> wrote in
> message
>>news:04ff01c4f35d$f0aaf3e0$a401280a@phx.gbl...
>>> Did you run it in Web or Windows application?
>>>
>>> Elton Wang
>>> elton_wang@hotmail.com
>>>>-----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'
>>>>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!
>>>>
>>>>
>>>>.
>>>>
>>
>>
>>.
>>

Jason
Wed Jan 05 14:08:44 CST 2005

Yes, I'm using Windows authentication in both.

"Sahil Malik" <contactmethrumyblog@nospam.com> wrote in message
news:eeZL$$18EHA.808@TK2MSFTNGP10.phx.gbl...
> 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" <someone@microsoft.com> wrote in message
> news:eNvQZx18EHA.3944@TK2MSFTNGP12.phx.gbl...
>> 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.
>>
>> "Sahil Malik" <contactmethrumyblog@nospam.com> wrote in message
>> news:OKPcnu18EHA.3596@TK2MSFTNGP12.phx.gbl...
>> > 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" <someone@microsoft.com> wrote in message
>> > news:OPL$Tb18EHA.3820@TK2MSFTNGP11.phx.gbl...
>> >> 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!
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Jason
Wed Jan 05 14:43:15 CST 2005

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 = ''~'')')"


"Jason" <someone@microsoft.com> wrote in message
news:OPL$Tb18EHA.3820@TK2MSFTNGP11.phx.gbl...
> 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
Wed Jan 05 15:10:56 CST 2005

Sweet !! Something for us to learn over here :) LOL

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

"Jason" <someone@microsoft.com> wrote in message
news:e5Rkvc28EHA.3596@TK2MSFTNGP12.phx.gbl...
> 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 = ''~'')')"
>
>
> "Jason" <someone@microsoft.com> wrote in message
> news:OPL$Tb18EHA.3820@TK2MSFTNGP11.phx.gbl...
> > 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!
> >
>
>

Elton
Thu Jan 06 13:03:52 CST 2005

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

>-----Original Message-----
>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
= ''~'')')"
>
>
>"Jason" <someone@microsoft.com> wrote in message
>news:OPL$Tb18EHA.3820@TK2MSFTNGP11.phx.gbl...
>> 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!
>>
>
>
>.
>