|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Bulk Insert Via SQLCommand?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,
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 Show quoteHide quote "Jason" <some***@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! > > 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. Show quoteHide quote "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" <some***@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! >> >> > > 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? Show quoteHide quote "Jason" <some***@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" <some***@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! > >> > >> > > > > > > Yes, I'm using Windows authentication in both.
Show quoteHide quote "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" <some***@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" <some***@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! >> >> >> >> >> > >> > >> >> > > Did you run it in Web or Windows application?
Elton Wang elton_w***@hotmail.com >-----Original Message----- Server 2000. The command >Hi. I am trying to bulk insert a text file into SQL >works fine if run from the QueryAnalyzer, but when I run from within my code >it fails with a "System Error". FROM '\\MyComputer\c$\TestClass.txt' > >Bulk insert SQL (which works in QA) is: >BULK INSERT MyCatalog.dbo.Tempbcptest >WITH (FIELDTERMINATOR = '~') System.Data.SqlClient.SqlCommand> >I am attempting to run from VB.NET with the following code: > > SQLCmd = New > SQLConn = New System.Data.SqlClient.SqlConnection> With SQLConn Catalog=" & SQLCatalog > .ConnectionString = "Initial Show quoteHide quote >& "; Data Source=" & SQLServer & "; Integrated accomplish this is 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 Show quoteHide quote >appreciated! > > >. > Windows.
Show quoteHide quote "Elton Wang" <el***@discussions.microsoft.com> wrote in message news:04ff01c4f35d$f0aaf3e0$a401280a@phx.gbl... > Did you run it in Web or Windows application? > > Elton Wang > elton_w***@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! >> >> >>. >> 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. Show quoteHide quote >-----Original Message----- >Windows. > >"Elton Wang" <el***@discussions.microsoft.com> wrote in message >news:04ff01c4f35d$f0aaf3e0$a401280a@phx.gbl... >> Did you run it in Web or Windows application? >> >> Elton Wang >> elton_w***@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! >>> >>> >>>. >>> > > >. > Hm, thanks. I tried that, but it breaks on the .ExecuteNonQuery() line and
won't proceed to give me any more detailed info. Show quoteHide quote "Elton Wang" <el***@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" <el***@discussions.microsoft.com> wrote in > message >>news:04ff01c4f35d$f0aaf3e0$a401280a@phx.gbl... >>> Did you run it in Web or Windows application? >>> >>> Elton Wang >>> elton_w***@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! >>>> >>>> >>>>. >>>> >> >> >>. >> 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 = ''~'')')" Show quoteHide quote "Jason" <some***@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! > Sweet !! Something for us to learn over here :) LOL
- Sahil Malik http://dotnetjunkies.com/weblog/sahilmalik Show quoteHide quote "Jason" <some***@microsoft.com> wrote in message '\\MyComputer\c$\TestClass.txt'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" <some***@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 Show quoteHide quote > > 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! > > > > 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----- preceded by "Exec", and >The trouble was, I had to pass the command string >thus also had to adjust the apostrophes. For some reason it takes twice as >long to run as it did from QA though. & ".dbo.Tempbcptest FROM > >This works: > >sCmdString = "Exec('BULK INSERT " & DatabaseName >''\\MyComputer\C$\TestClass.txt'' WITH (FIELDTERMINATOR Server 2000. The = ''~'')')" > > >"Jason" <some***@microsoft.com> wrote in message >news:OPL$Tb18EHA.3820@TK2MSFTNGP11.phx.gbl... >> Hi. I am trying to bulk insert a text file into SQL >> command works fine if run from the QueryAnalyzer, but when I run from >> within my code it fails with a "System Error". FROM '\\MyComputer\c$\TestClass.txt' >> >> Bulk insert SQL (which works in QA) is: >> BULK INSERT MyCatalog.dbo.Tempbcptest >> WITH (FIELDTERMINATOR = '~') System.Data.SqlClient.SqlCommand>> >> I am attempting to run from VB.NET with the following code: >> >> SQLCmd = New >> SQLConn = New System.Data.SqlClient.SqlConnection>> With SQLConn Catalog=" & SQLCatalog >> .ConnectionString = "Initial Show quoteHide quote >> & "; Data Source=" & SQLServer & "; Integrated accomplish this is 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 Show quoteHide quote >> appreciated! >> > > >. >  
Other interesting topics
GetChildRows - Finding Deleted ChildRows ?
DBnull, Date and DataAdapter Constraint violated but no exception thrown Best way to process millions of records Problem with Update Adapter Really need some help on this typed dataset and sort order on save Handling very huge data Thread was being aborted Errors How can I read a list of files from a folder into a DataTable? |
|||||||||||||||||||||||
