SQLBulkCopy Issues With ODBC

Jun 15, 2007

I am trying to use SQLBulkCopy to copy from an excel spreadsheet to a table in a SQL database and it is all working fine, but I have to now change the connection to the SQl database to an ODBC connection and it is now erroring.

This is the error I get - keyword not supported 'dsn'

Is it possible to use SQlBulkCopy when using an odbc connection to the SQL database?

View 2 Replies


ADVERTISEMENT

SQLBULKCOPy

May 23, 2007

I am using SQLBULKCOPY to copy Excel spreadsheet into SQL Express Database table.  The copying went okay, but the only problem I have is that few records in one of the columns is NULL. 
The column contains numeric and alphnumeric fields in the spreadsheet.  After copying, the numeric fields got copy with no complication, but all the alphanumeric fields all becam NULL.  Can someone help me with this problem?

View 4 Replies View Related

SQLBulkCopy In LAN

Jun 9, 2008

HI,
I have one doubt. Is it possible to transfer data from one SQL Server to other SQL Server over the LAN.( Both the SQL Server database are in different cities)

View 1 Replies View Related

SqlBulkCopy

May 19, 2005

Hello,
I was curious if anyone knew if using SqlBulkCopy in code required any special permissions on the database side.  I wasn't sure if more permissions than writing capabilities were needed.
Thanks.

View 1 Replies View Related

Question On Using SQLBulkCopy

Feb 4, 2007

I need to copy large amounts of data between SQL databases, and between SQL and Access.  I've been reading a lot of good explanations of SQLBulkCopy, but the only good examples I've found are written in C# and I work in VB.  I'm very new to ASP.NET 2.0 (about a month) and came from Classic ASP, not .NET 1.1.  I'm still getting my feet wet and I'm afraid it doesn't take much to confuse me.  Can someone point me to a good article or tutorial that includes a clear example written in VB?
Diane

View 1 Replies View Related

SQLBulkCopy Question

Nov 28, 2007

Hi,
I am trying to use sqlbulkcopy to insert the contents of a data table to a temporary table in the database. The content of data table comes from a csv file. I can add the rows to the data table but it seems they are added as strings and when I try to WriteToServer(datatable) it chokes becuase my table has some int fields. Not sure how to do this.private static TimeSpan DoBulkCopy(string filePath)
{Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
StreamReader sr = new StreamReader(filePath);
prepareTable();string fullFileStr = sr.ReadToEnd();
sr.Close();
sr.Dispose(); string[] lines = fullFileStr.Split('');
DataTable dt=new DataTable() ;string[] sArr =lines[0].Split(',');foreach(string s in sArr)
{dt.Columns.Add(new DataColumn());
}
DataRow row;string finalLine = "";foreach (string line in lines)
{
row = dt.NewRow();finalLine = line.Replace(Convert.ToString('
'), "");row.ItemArray = finalLine.Split(',');
dt.Rows.Add(row);
} SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["connectionString"].ToString());System.Data.SqlClient.SqlBulkCopy bc = new System.Data.SqlClient.SqlBulkCopy(cn, SqlBulkCopyOptions.TableLock, null);
bc.BatchSize = dt.Rows.Count;
cn.Open();bc.DestinationTableName = "tmpTable";
bc.WriteToServer(dt);   // <---------------   it dies here
cn.Close();
bc.Close(); TimeSpan ts = stopWatch.Elapsed;
stopWatch.Stop();return ts;
}private static void prepareTable()
{SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["connectionString"].ToString());
string sql = @"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmpTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[tmpTable]; CREATE TABLE [dbo].[tmpTable] ([Remote] [int],[KFP] [int]) ON [PRIMARY]";SqlCommand cmd = new SqlCommand(sql, cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
cmd.Dispose();
}

View 1 Replies View Related

Question About SqlBulkCopy

Feb 29, 2008

Hello.

I have a problem using SqlBulkCopy for updating tables. In fact, I can´t update any table. I use this function for insert big groups of records, but I would like to know how I can configure SqlBulkCopy for update rows. I would like to see if there is a record (primary key exists) then update it.

Is possible?

Greetings.

Tanen.

View 1 Replies View Related

SqlBulkCopy And Indexes

Apr 25, 2008

Does SqlBulkCopy have any adverse affects on it's target table indexes? I like the performance gain but am worried about creating unnecessary table scans if the indexes/stats are not updated properly after it completes...

View 1 Replies View Related

SqlBulkCopy Error Messages

Sep 6, 2006

I'm using SqlBulkCopy.  Does anyone know how I can output what row (its column names) are throwing a duplicate primary key message when I bulkCopy.WriteToServer(datatable1)?Thanks 

View 1 Replies View Related

SQLBulkCopy Vs. Batch Update

Apr 17, 2007

I have a collection of around 16000 records, and have been trying to find the best way to update the information in the DB. I have done alot of reading about both BulkCopy and Batch Update, but haven't come to any clear solutions as to which performs better. I am not doing any inserting, just getting a dataset from the DB, changing the values, them want to update the Db again. Thanks for any help. Mick 

View 8 Replies View Related

Timeout Using SqlBulkCopy Across Servers

Apr 27, 2007

I'm in the final throws of redesigning a web application, and one of the major improvements is adding in an admin page for the users so they can maintain the data and system structure without the need for us to get involved.
One of the areas I'd held back on was promotion of data from SQL server to SQL server - once .NET 2 framework came in and we moved over to VS2005, I'm now in a position to do this work. 
I have tested it and it works perfectly well for small tables, but as soon as I try it on a reasonably large table (860,000 rows) I get the following error in VS's output window:
"A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code
Additional information: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
The application data is stored on SQL2005 servers, and I am running the application locally.  I have tried running the copy from table to table on the same server (different databases) and i get the same result, which leads me to believe that it may be memory related.  Running the SQL in SQL Server Management Studio works in just 15 to 20 seconds, but using VS falls over in just under a minute.  My connection string has a timeout of 3600.
Here's my C# code:// Execute reader...
using (IDataReader vReader = vCmd.ExecuteReader())
{
// Create SqlBulkCopy...
SqlBulkCopy vBulkData = new SqlBulkCopy(aTargetConn);
// Set destination table name...
vBulkData.DestinationTableName = aTableName;
// Write data...
vBulkData.WriteToServer(vReader);
}

aSourceConn and aTargetConn are the appropriate SqlConnections, and aTableName is the table to be populated with data (previously backed up and emtied of contents).
Any help/advice suggestions gratefully received - if any more info needed please ask.
Thanks
Martin
 

View 6 Replies View Related

Importing CSV Files With SqlBulkCopy

May 2, 2007

I am trying to import a CSV file into an SQL Server table with the OleDbDataReader and SqlBulkCopy objects, like this:   using (OleDbConnection dconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mystuff\;Extended Properties="text;HDR=No;FMT=Delimited""))
{
using (OleDbCommand dcmd = new OleDbCommand("select * from mytable.csv", dconn))
{
try
{
dconn.Open();

using (OleDbDataReader dreader = dcmd.ExecuteReader())
{
try
{

using (SqlConnection dconn2 = new SqlConnection(@"data source=MyDBServer;initial catalog=MyDB;user id=mydbid;password=mydbpwd"))
{
using (SqlBulkCopy bc = new SqlBulkCopy(dconn2))
{
try
{
dconn2.Open();
bc.DestinationTableName = "dbo.mytable";
bc.WriteToServer(dreader);
}
finally
{
dconn2.Close();
}
}
}
}
finally
{
dreader.Close();
}

}
}
finally
{
dconn.Close();
}
}
}
A couple of the columns for the destination table use a bit datatype.  The CSV files uses the strings "1" and "0" to represent these.When I run this code, it throws this exception:Unhandled Exception: System.InvalidOperationException: The given value of type String from the data source cannot be converted to type bit of the specified target column. ---> System.FormatException: Failed to convert parameter value from a String to a Boolean. ---> System.FormatException: String was not recognized asa valid Boolean.   at System.Boolean.Parse(String value)   at System.String.System.IConvertible.ToBoolean(IFormatProvider provider)   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)   at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)   --- End of inner exception stack trace ---   at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)   at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaDatametadata)   --- End of inner exception stack trace ---   at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaDatametadata)   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)   at MyClass.Main()It appears not to accept "1" and "0" as valid strings to convert to booleans.  The System.Convert.ToBoolean method appears to work the same way. Is there any way to change this behavior?  I discovered if you change the "1" to "true" and "0" to "false" in the  CSV file it will accept them.  

View 3 Replies View Related

I Can't Make SqlBulkCopy Work

Feb 7, 2008

I have a scenario whereby I'd like to insert multiple rows into a table on a SQL server database as efficiently and easily as possible.
After some research, it looked like .NET 3.0's SqlBulkCopy class would do what I want. I've tried to set something up, but it's not working. It's not even throwing an error. The code executes but it simply hasn't done the insert by the end of it!
 My table structure is simple. The name of the table is LPSTUnavailableDate. It has just two columns, one of them an auto-populated ID field:


LPSTUnavailableDateId, INT, PRIMARY KEY, IDENTITY(1,1)

View 1 Replies View Related

Mysterious Error With SQLBulkCopy

Jun 5, 2008

I have encountered a very frustrating situation when trying to use SQLBulkCopy. I have two excel files that I am trying to import into two tables in an MSSQL Server 2005 Express DB. One excel file has 5,000 rows, while the other file has 500,000 rows.I was able to import the smaller file successfully using this vb.net code:  Protected Sub L26ExcelToSQL() 'Declare Variables
Dim sSQLTable As String = "Local26Members"
Dim sExcelFileName As String = "Full Local 26 List Formatted.xls"
Dim sWorkbook As String = "[Sheet1$]"

'Create connection strings
Dim sExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=D:hostingmemberwolsite1l26voterreg" & sExcelFileName & ";" & _ "Extended Properties=""Excel 8.0;HDR=YES"""



Dim sSqlConnectionString As String = ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString.ToString 'Execute a query to erase any previous data from our destination table
Dim sClearSQL = "DELETE FROM " & sSQLTable Dim SqlConn As SqlConnection = New SqlConnection(sSqlConnectionString) Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, SqlConn) SqlConn.Open() SqlCmd.ExecuteNonQuery() SqlConn.Close() 'Series of commands to bulk copy data from the excel file into our SQL table
Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString) Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT * FROM " & sWorkbook), OleDbConn) OleDbConn.Open() Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader() Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(sSqlConnectionString) bulkCopy.DestinationTableName = sSQLTable bulkCopy.WriteToServer(dr) OleDbConn.Close() End Sub
  However, when I tried to import the 500,000 row excel file, I got the following error: Server Error in '/L26' Application.

A
transport-level error has occurred when receiving results from the
server. (provider: TCP Provider, error: 0 - The specified network name
is no longer available.)



Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException:
A transport-level error has occurred when receiving results from the
server. (provider: TCP Provider, error: 0 - The specified network name
is no longer available.)

Source Error:




Line 438:Line 439: bulkCopy.DestinationTableName = sSQLTableLine 440: bulkCopy.WriteToServer(dr)Line 441:Line 442: OleDbConn.Close()







Source File: d:hostingmemberwolsite1L26DuesDefault2.aspx.vb    Line: 440


Stack Trace:




[SqlException (0x80131904): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) +556 System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) +164 System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) +34 System.Data.SqlClient.TdsParserStateObject.ReadBuffer() +44 System.Data.SqlClient.TdsParserStateObject.ReadByte() +17 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +79 System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() +1336 System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) +916 System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) +151 _Default.CSVToSQL() in d:hostingmemberwolsite1L26DuesDefault2.aspx.vb:440 _Default.ButtonTest3_Click(Object sender, EventArgs e) in d:hostingmemberwolsite1L26DuesDefault2.aspx.vb:905 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746









Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433 After I received this error message, I tried viewing my database through the MSSQL Control Panel utilized by my hosting provider (WebHost4Life). However, I was unable to connect to the database and received this error: ___________________Microsoft OLE DB Provider for SQL Server error '80040e14' Database 1496 cannot be autostarted during server shutdown or startup. /getDBinfo.asp, line 29

_____________________ Now here is the most frustrating/mysterious part. I figured that maybe the error message were a result of the large size of the second excel file, so just for testing purposes, I created a new table in my MSSQL database. The table just has two fields, both set to varchar(50). I then created a test excel file, that had one row with the word "test" in the first and second column. When I tried using the code above to import the test excel data into the test table, I got the same exact error as I did with the 500,000 row file!Please help, I'm really stumped and I am not sure when I am having so much trouble replicating the success I had the 5,000 row file. Any suggestions are much apprecaited. -Bryan  

View 4 Replies View Related

SqlBulkCopy And Context Connection = T

Oct 6, 2006

I have created an assembly which I load into SQL 2005. However, if I set my connection string = context connection = true... I will get an error saying something like this feature could not be used in this context... So I changed my function to insert each row.... Now the issue I have is the transfer takes 4X as long.... Before I made the change I was using the bulkcopy by specifying the actual connection string....but I also had to specify the password in the string...and since I wanted to get way from this specification...I attempted the context route. So...is there any other way of using the bulkcopy feature or something like it using the context connection?

Private Shared Function BulkDataTransfer2(ByVal _tblName As String, ByRef _dt As DataTable, ByRef emailLog As String) As Boolean

Dim success As Boolean = False

emailLog = emailLog & System.DateTime.Now.ToString & " - bulk transfer2 - " & _tblName & vbCrLf

Dim insertStr As String = "INSERT INTO " & _tblName & "("

Dim values As String = ") Values("

Dim drow As DataRow = Nothing

Dim dCol As DataColumn = Nothing

'add the column names

For Each dCol In _dt.Columns

insertStr = insertStr & dCol.ColumnName.ToString & ", "

values = values & "@" & dCol.ColumnName.ToString & ", "

Next

'remove the last comma & form the final string

insertStr = insertStr.Substring(0, insertStr.Length - 2)

values = values.Substring(0, values.Length - 2)

insertStr = insertStr & values & ")"



Dim connStr As String = "context connection = true"

Dim conn As New SqlConnection(connStr)

Dim cmd As SqlCommand = Nothing



Using conn

Try

conn.Open()

For Each drow In _dt.Rows

cmd = New SqlCommand(insertStr, conn)

For Each dCol In _dt.Columns

cmd.Parameters.AddWithValue("@" & dCol.ColumnName.ToString, drow.Item(dCol.ColumnName.ToString))

Next

SqlContext.Pipe.ExecuteAndSend(cmd)

Next

success = True

Catch ex As Exception

emailLog = emailLog & System.DateTime.Now.ToString & " " & ex.ToString & vbCrLf

success = False

Finally

Try

conn.Close()

conn.Dispose()

Catch ex As Exception

success = False

End Try

End Try

End Using

Return success

End Function



View 4 Replies View Related

JDBC Equivalent Of SqlBulkCopy

Apr 20, 2008



Hi,

Is there a JDBC equivalent of the SqlBulkCopy command?

Simply using batched INSERTs, it takes days to insert 1m rows into SQL Server. However using C# client that uses SqlBulkCopy I can load it in about 1 hour.

Thanks in advance,
G.

View 5 Replies View Related

SqlBulkCopy CodePage Translation Vs BCP

Apr 24, 2007

I have deveoped a replacement for some an old bcp based applications in the .Net Framework that uses the SqlBulkCopy class.

I have run into some difficulties with code page translation:

The original BCP client runs with OEM Codepage 437 , thus the data "ëÄÆòÖ" gets loaded as "d-¦=+". DB is SQL_Latin1_General_CP1_CI_AS, column is varchar.

I have been unable to perform any code page Encoding in .Net that yields the same result.

I want to emulate this behaviour in my database loader but as yet have been able to find a way....

Any Ideas???

Thanks,
Niall

View 7 Replies View Related

SqlBulkCopy And Field Collation

Nov 19, 2007

I have the following table:
create table tTest
(
x varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS
)

I need to populate this table using SqlBulkCopy, however some symbols are inserted with mistakes.

Here is an example of the code that I€™m using:
using (SqlBulkCopy copier = new SqlBulkCopy(ci.ConnectionString))
{
copier.DestinationTableName = "tTest";
DataTable tbl = new DataTable();
tbl.Columns.Add(new DataColumn("x"));

tbl.Rows.Add(new object[] { "u00fc" }); //letter ü

copier.WriteToServer(tbl);
}

At the same time DTS and the statement
insert tTest values(N'ü')

works correctly.

View 5 Replies View Related

SqlBulkCopy Issues With Duplicate Records

Sep 14, 2006

SqlBulkCopy does not seem to have much flexibility. If your table has columns that dont allow duplicates, you are apparently screwed.  Its a shame there is no switch or param setting you can issue the SqlBulkCopy class (if there is, please let me know!!)Example:Say I have a table "Cars" with fields "CarId" (identity) and "CarName" (varchar) and the CarName field has a unique constraint. Now, I have a DataTable that contains a bunch of CarNames to insert.  If there are duplicates on CarName, the entire insert fails.  This is nothing to do with the PK or identity field. The problem I have: I would much rather have an option to ignore or silently not insert that duplicate row, but continue to insert the rest of my data. Any known work arounds for this would be much appreciated.  Maybe I am missing something?

View 3 Replies View Related

SqlBulkCopy Login Failed Error

Mar 20, 2007

Hi There, I'm trying to use a sql bulk copy to transfer data from xml file to a table in one of my page.  In this page I'm doing 2 database related.  The first is a simple insert that will return a value and the second one is the sql bulk copy data transfer.  I'm using the same connection for both of them and the sql bulk copy always give me a "login failed" error while the insert is fine.  Do I need to set a specific setting for the sql server account so that it can use sql bulk copy? Thank you 

View 3 Replies View Related

Unexpected Existing Transaction With SqlBulkCopy

Oct 17, 2007

I have programmatically created a SqlConnection that begins a SqlTransaction.  During the first part of this SqlTransaction, the contents of a table are deleted.  The next part uses the SqlBulkCopy object to copy data from another database (in the form of a DataTable).  The delete goes through fine, but the SqlBulkCopy always generates a SqlException with the message "Unexpected existing transaction."  I cannot think of anything I am doing wrong. 
The code looks at an XML file for instructions on each transaction.  Each transaction is composed of tasks.  Each task will pull data from a different type of database (MVR.Command is a Factory Database object).  Please view the code below and tell me if you can spot what I am doing wrong:
 using (SqlConnection destinationConnection = new SqlConnection(MVR.ConnectionSource.GetConnectionString(destinationServiceName)))
{
destinationConnection.Open();

using (SqlTransaction transaction = destinationConnection.BeginTransaction(IsolationLevel.Snapshot, "Transport"))
{
transaction.Save("Beginning");

int totalTasks = 0;
int successfulTasks = 0;

foreach (XmlNode taskNode in transactionNode.SelectNodes("Tasks/Task"))
{
totalTasks += 1;

string sourceServiceName = taskNode.Attributes["sourceServiceName"].Value;
string destinationTablename = taskNode.Attributes["destinationTablename"].Value;
string query = taskNode.InnerText;

MVR.Command source = MVR.ConnectionSource.GetCommand(sourceServiceName);
source.CommandType = CommandType.Text;
source.CommandText = query;

DataTable sourceData = source.ExecuteDataTable();

try
{
// Prepare the destination table (delete everything)
int rowsDeleted = new SqlCommand("delete from " + destinationTablename, destinationConnection, transaction).ExecuteNonQuery();

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
{
bulkCopy.DestinationTableName = destinationTablename;

bulkCopy.NotifyAfter = 1000;
bulkCopy.WriteToServer(sourceData);
bulkCopy.Close();

successfulTasks += 1;

// Log success
}
}
catch (Exception ex)
{
// Log failure
}
}

transaction.Save("End");

// Based on the success of all tasks, either commit or rollback
if (successfulTasks == totalTasks)
{
transaction.Commit();
}
else
{
transaction.Rollback();
}
}

destinationConnection.Close();

 

View 2 Replies View Related

Text File / SqlBulkCopy Code

Apr 29, 2006

Does anybody have the code to import a text file into a a Sql Server table using the SqlBulkCopy object?

View 1 Replies View Related

SQLBulkCopy In A C# App Causes SQL Server 2005 To Lock Up

May 16, 2007

I have written an app that will allow you to send a query to Teradata, return the results into a Reader and then Bulk Copy that data into SQL Server 2005.



If the query results in a large dataset (ie 20,000,000 rows) is processed then while that data is being bulk copied into SQL Server, using the SQLBulkCopy class, then it prevents users on other computers from logging into SQL Server Management Studio.

Those that are already logged in are shut down also. Everything appears fine to the users but queries do not finish running.



Everything immediatly starts working as normal when either my program finishes or I shut down my program.



Is there any type of property to the SQLBulkCopy class or any other function that will prevent Management Studio from locking up?



Thanks

Robert

View 2 Replies View Related

SqlBulkCopy Computed Column Error.

Feb 27, 2008



Hi,
Iam using SqlBulkCopy to copy data of all the tables from one database to another database. SqlBulkCopy runs just fine but it throws exception for one of the tables which is having computed column.

snnipet of code is


For Cnt = 0 To oDS.Tables(0).Rows.Count - 1

oSqlCmd2 = New SqlCommand

oSqlCmd2.Connection = oConn

oSqlCmd2.CommandText = "select * from " & "" & oDS.Tables(0).Rows(Cnt).Item(0).ToString & " "

'Dim reader As SqlDataReader

oDS2 = New DataSet

oDA2 = New SqlDataAdapter

oDA2.SelectCommand = oSqlCmd2

'reader = oSqlCmd2.ExecuteReader

oDA2.Fill(oDS2, "test")

Dim bulkData As SqlBulkCopy = New SqlBulkCopy(oConn2)



'Get the data from the second database and fill the dataset

oDA2 = New SqlDataAdapter

bulkData.DestinationTableName = "" & oDS.Tables(0).Rows(Cnt).Item(0).ToString & " "

bulkData.BatchSize = 1000

bulkData.BulkCopyTimeout = 2000

bulkData.WriteToServer(oDS2.Tables(0))

oDS2.Dispose()

oSqlCmd2.Dispose()

Next
For one of the tables iam getting the following error
The column "Col4" cannot be modified because it is either a computed column or is the result of a UNION operator

Since iam fetching table names at runtime its not possible to use columnMappings.

So, how to come out of this situation.
TIA


View 6 Replies View Related

Multiple Table Insertion Using SQLBulkCopy

Oct 11, 2007



I want to insert data into Header and Details table simultaneously using SQLBulkCopy. Header table contains an Identity columns and Details table contains Foreign Key to this identity column in Header Table.I want to use DataTable as datasource in SQLBulkCopy.Can any body help on this

thanks


View 2 Replies View Related

SqlBulkCopy And Triggers Cause Subquery Returned More Than 1 Value Error

Feb 22, 2008

Hi,
I am using .NET sqlBulkCopy to insert several rows at once into my sql table.  Yesterday I created a simple trigger on this table to take the primary key of the inserted row and place into another table.  Fairly straightforward I thought, but I keep getting this error when inserting 2 or more rows (works fine with one row):
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <= , >, >= or when the subquery is used as an expression. The statement has been terminated."
To test this, I changed the sqlBulkCopy into individual INSERT statements in a for each loop and then it works perfectly, so I know that it's definitely the sqlBulkCopy causing the problem, not the trigger itself.  Here is my trigger code anyway:
alter trigger tg_InsertClaim on AuditItemfor insertas
declare @AuditItemID as intset @AuditItemID = (select i.AuditItemID from inserted i inner join dbo.AuditItem a ON i.AuditItemID = a.AuditItemID)
insert into Claim (AuditItemID) VALUES (@AuditItemID)
Thanks!

View 2 Replies View Related

Transport-level Error Has Occurred When Using SqlBulkCopy

Mar 9, 2008

When i try to use SqlBulkCopy in vb.net to import 499 records i get the following error returned?

A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)

This then results in SQL Server services stopping - and forcing me to do a reset?

does any one know what would cause this and also how to fix it?

View 2 Replies View Related

Strange SqlBulkCopy Exception With SQL Server 2005

Dec 10, 2006

Hello,

Wondering if anyone can help with a strange exception thrown while using the SqlBulkCopy class. I am using the class to transfer records from a DataTable in memory (approx 11,000 rows) into a SQL Server 2005 table.

Initially, the WriteToServer method was timing out a la KB913177 (http://support.microsoft.com/default.aspx/kb/913177), however I downloaded the hotfix, which eliminated this issue.

Now, I get a new exception thrown, as follows:

System.Data.SqlClient.SqlException: OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].Power_Avg'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)

I can't see anything wrong with the data I have. The column "Power_Avg" is of type "float". When forming the DataTable, I cast my data to float expcilitly in C#.

Other things to know:

I am using SQL Server Express (2005)
This same code works fine with SQL Server 2000 (MSDE)
My code makes all modifications inside a single transaction of type "Snapshot" (I have activated SNAPSHOT READ COMMITTED in the database)
I have not activated MARS in the connection

Any ideas / suggestions?

Thanks,

Nick

View 7 Replies View Related

SqlBulkCopy.WriteToServer Succeeds Or Fails Depending On The Size Of DataTable?

Mar 4, 2008

Hello,I am trying to bulk copy some data from a text file to SqlServer. In my case, the table in SqlServer is simple. It has two columns: Symbol <nchar(5), Primary Key> and Company <nvarchar(50)>. Each row in the text file is Symbol and Company separated by a "#". Below is the code of my bulk copy:        public static void StartImport(string sourceFile)        {            SqlBulkCopy bulkCopy = new SqlBulkCopy(connString_local, SqlBulkCopyOptions.TableLock);            bulkCopy.DestinationTableName = "dbo.NasdaqSymbols";            DataTable dt = CreateSymbolDataTable(sourceFile);            bulkCopy.WriteToServer(dt);        }        private static DataTable CreateSymbolDataTable(string filePath)        {            DataTable dt = new DataTable();            DataColumn dc;            DataRow dr;            dc = new DataColumn();            dc.DataType = Type.GetType("System.String");            dc.ColumnName = "Symbol";            dc.Unique = true;            dt.Columns.Add(dc);            dc = new DataColumn();            dc.DataType = Type.GetType("System.String");            dc.ColumnName = "Company";            dc.Unique = false;            dt.Columns.Add(dc);            StreamReader sr = new StreamReader(filePath);            string input;            while ((input = sr.ReadLine()) != null)            {                string[] s = input.Split(new string[] { "#" }, StringSplitOptions.None);                dr = dt.NewRow();                dr["Symbol"] = s[0].Trim();                dr["Company"] = s[1].Trim();                dt.Rows.Add(dr);            }            sr.Close();            return dt;        } The problem is, I got the following exception when I tried to call my StartImport method (thrown from SqlBulkCopy.WriteToServer): System.InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. It turned out that the problem seems not to be String to nvarchar; because when I use a source text file which contains only about a dozen of rows, it works! I have no idea why SqlBulkCopy.WriteToServer works fine on a small set of data. Or is there something I overlooked? Thank you for time and help.Gary 

View 2 Replies View Related

Using SSIS With SqlBulkCopy For ETL: How To Delete Processed Records From Staging Table

Jul 27, 2007

I'm looking at SSIS and SqlBulkCopy as a possible method to quickly insert and process large amounts of data, my current method uses the sp_xml_preparedocument and OPENXML to parse an xml document of the data I want to process and insert into the database, however I'm noticing the performance of SqlServer parsing the xml document isn't that good.

I found the C# SqlBulkCopy method (new in .NET 2.0) and I was thinking it would be faster to use that to load my data into a staging table and then use SSIS to extract the data from the staging table, process and transform it as necessary and finally load it into the final destination tables. I was able to create an Integration Services Project that selects the data from the staging table, does a bit of processing on one of the fields (by calling a stored procedure), and finally loads the processed data into the final table.

The problem with this is I need to clean out the rows that were extracted from the staging table and I'm not sure how I can accomplish this (and I can't just issue a "delete from staging_table" because there maybe new records in the staging table that were not processed), perhaps I can either delete each record as it is proccessed or somehow get the last proccessed identity id from the staging table and delete all records less than or equal to that id.

thanks in advance for any help you can provide, maybe there is an easy way to accomplish what I'm trying to do.

-Matt Palmerlee

View 3 Replies View Related

SqlBulkCopy Instance Via SQLEXPRESS-ADO.NET 2.0-Visual C# Express: Security Was Unhandled?

Aug 28, 2007

Hi all,

I copied the following set of C# code statement from a website to learn the SqlBulkCopy instance via SQLEXPRESS-ADO.NET 2.0-Visual C# Express:

/////////////////////----Main.cs---//////////////////////

sing System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace SqlBulkCopySample

{

public partial class frmMain : Form

{

public frmMain()

{

InitializeComponent();

}

private void btnStart_Click(object sender, EventArgs e)

{

String sourceConnectionString =

"Data Source=.SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";

String destinationConnectionString =

"Data Source=.SQLEXPRESS;Initial Catalog=SqlBulkCopySample;Integrated Security=True";



DataTable data = SelectDataFromSource(sourceConnectionString);

CopyDataToDestination(destinationConnectionString, data);

}

private DataTable SelectDataFromSource(String connectionString)

{

DataTable data = new DataTable();

using (SqlConnection connection = new SqlConnection(connectionString))

{

SqlCommand command = new SqlCommand("SelectOrders", connection);

command.CommandType = CommandType.StoredProcedure;

connection.Open();

SqlDataReader reader = command.ExecuteReader();

data.Load(reader);

}

return data;

}

private void CopyDataToDestination(String connectionString, DataTable table)

{

SqlBulkCopyColumnMapping mapping1 =

new SqlBulkCopyColumnMapping("OrderID", "ID");

SqlBulkCopyColumnMapping mapping2 =

new SqlBulkCopyColumnMapping("ShipName", "Name");

SqlBulkCopyColumnMapping mapping3 =

new SqlBulkCopyColumnMapping("ShipAddress", "Address");

SqlBulkCopyColumnMapping mapping4 =

new SqlBulkCopyColumnMapping("ShipCity", "City");

SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString);

bulkCopy.BatchSize = 100;

bulkCopy.BulkCopyTimeout = 5;

bulkCopy.ColumnMappings.Add(mapping1);

bulkCopy.ColumnMappings.Add(mapping2);

bulkCopy.ColumnMappings.Add(mapping3);

bulkCopy.ColumnMappings.Add(mapping4);

bulkCopy.DestinationTableName = "DataMySqlBC1";

bulkCopy.SqlRowsCopied +=

new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);

bulkCopy.NotifyAfter = 200;

bulkCopy.WriteToServer(table);

}

void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)

{

MessageBox.Show

(String.Format("{0} Rows have been copied.", e.RowsCopied.ToString()));

}

}

}

//////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the above project in my local PC (with Adiministrator previlidge/use) that is in our LAN/Network system and I got an error: SecurityException was unhandled - Request for permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'failed. First, I have a question: is this problem related to the remote connection to SQLEXPRESS? Second, I do not know how to solve this problem. Please help and answer my first question and tell me how to solve this problem.

Thanks in advance,
Scott Chang

View 1 Replies View Related

Import Excel Spreadsheet Data Into SQL Server Database Table Using SqlBulkCopy

Dec 13, 2007

 
Hi, I'm a Student, and since a few months ago I'm learning JAVA. I'm creating an application to call and compare times. For this I create in Excel a time table which is quite big and it would be a lot of typing work to input one by one the data in each cell in SQL Server, considering that I have to create 8 more tables. I was able to retreive the data from excel usin the JXL API of JAVA but it doesn't give all the funtions to perform math operations as JDBC. That's why I need to move the tables from Excel to SQL.
I found this site http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx which gives a code to do so, but I guess that some heathers are missing or maybe I don't know which compiler to use to run that code, I would like you help to identify which compiler use to run that code or if there is some vital piece of code missing.// Connection String to Excel Workbook
string excelConnectionString = @"Provider=Microsoft .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

// Create Connection to Excel Workbook
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand ("Select ID,Data FROM [Data$]", connection);

connection.Open();

// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=.; Initial Catalog=Test;Integrated Security=True";

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "ExcelData";
bulkCopy.WriteToServer(dr);
}
}
}
On the other hand in this forum I that someelse use that link but implements a totally different code which I'm not able to compile also http://forums.asp.net/p/1110412/2057095.aspx#2057095. It seems this code works as I was able to read, but I do not know which language is used.
Dim excelConnectionString As String = "Provider=Microsoft .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;"""
 
' Using
 
Dim connection As OleDbConnection = New OleDbConnection(excelConnectionString)
 
Try
 
Dim command As OleDbCommand = New OleDbCommand("Select ID,Data FROM [Data$]", connection)
connection.Open()
 
' Using
 
Dim dr As DbDataReader = command.ExecuteReader
 
Try
 
Dim sqlConnectionString As String = WebConfigurationManager.ConnectionStrings("CampaignEnterpriseConnectionString").ConnectionString
 
' Using
 
Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(sqlConnectionString)
 
Try
bulkCopy.DestinationTableName =
"ExcelData"
bulkCopy.WriteToServer(dr)
 
Finally
 
CType(bulkCopy, IDisposable).Dispose()
 
End Try
 
Finally
 
CType(dr, IDisposable).Dispose()
 
End Try
 
Finally
 
CType(connection, IDisposable).Dispose()
 
End Try
 
Catch ex As Exception
 
End Try
The Compilers I have are: Eclipse, Netbeans, MS Visual C++ Express Edition and MS Visual C# Express Edition. In MS Visual C++
Thanks for your help.
 Regads,
Robert.
 

View 4 Replies View Related

Integration Services :: Unable To See 64bit ODBC Driver From SSIS Odbc Connection?

Jun 1, 2015

I am using SSIS 2014 with the below .net framework version and installed in Windows server 2012 R2 . I have installed my client's odbc drivers (both 32 bit and 64 bit) in my production server and created ODBC system DSNs for 32 bit and 64 bit.

When i open SSIS 2014 and tried to create the odbc connection but i can able to see only the 32 bit system DSN connection ,i can't able to see my 64 bit odbc system dsn connection.

Microsoft Visual Studio 2012 Shell (Integrated)
Version 11.0.50727.1 RTMREL
Microsoft .NET Framework
Version 4.5.51650

SQL Server Integration Services   
Microsoft SQL Server Integration Services Designer
Version 12.0.1524.0

And i installed my client odbc drivers(32,64 bit) and created ODBC system DSNs in my local system and when i open ssis 2014 and i can able to see both the ODBC system DSNS(32,64) connections from SSIS ODBC connection.

I am using below version of .net framework in my local system which was installed in windows 7 and i have SSIS 2012 also installed in my system and i can able to see both ODBC connections using 2012 as well in my local system.

Microsoft Visual Studio 2012 Shell (Integrated)
Version 11.0.50727.1 RTMREL
Microsoft .NET Framework
Version 4.5.50938

SQL Server Integration Services   
Microsoft SQL Server Integration Services Designer
Version 12.0.1524.0

why i can not see the ODBC 64 bit system DSN connection from SSIS in my production server ?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved