Take a look at FullConvert - uses bulk copy but handles guid->UID very well. There is a trial version to DL which does 100 records so you can see if it does what you want. Enterprise version is what you want. If you do a lot of this stuff it is really worth it. Fevkalada !
>I don't know if this is the right place to ask this question, sorry if not.
>
>
>
>I want to upsize tables at customer site from VFP tables to SQL server tables. I create the database, tables etc and start to upload data using ADO or ODBC in between but it is very slow. So instead I wanted to use SqlBulkCopy. It works much better and fast but nothing is perfect it has some problems which I think is a bug.
>
>
>
>
>Problem:
>
>
> OleDbCommand sourceCommand = new OleDbCommand(@"select * from sourceTable", source);
>
> using (OleDbDataReader dr = sourceCommand.ExecuteReader())
> {
> using (SqlBulkCopy s = new SqlBulkCopy(destl))
> {
> s.BatchSize = 500;
> s.BulkCopyTimeout = 300;
> s.DestinationTableName = "myTable";
> s.NotifyAfter = 10000;
> s.SqlRowsCopied += new SqlRowsCopiedEventHandler(s_SqlRowsCopied);
> s.WriteToServer(dr);
> s.Close();
> }
> }
>
>
>
>With this code if source table have a GUID (in any format you want that a GUID could be expressed) then it fails with an invalid cast exception. Exception is not right however. The value is castable to a uniqueidentifier, it's just that the class use Convert.ChangeType which can't make a conversion. This is a bug IMHO.
>
>
>
>I thought I was clever anf to overcome it I used a datatable cheating:
>
>
>
using System;
>using System.Data;
>using System.Data.SqlClient;
>using System.Data.OleDb;
>
>class sbc
>{
> static void Main(string[] args)
> {
>
> OleDbConnection source = new OleDbConnection(@"provider=vfpoledb;Data source=c:\temp\guids\guidtest.dbc");
> SqlConnection dest = new SqlConnection(@"server=.\sqlexpress;trusted_connection=yes;database=test;");
>
> DataTable tbl = new DataTable();
>
> dest.Open();
>
>// Read with a false where to grab schema from SQL server - like set fmtonly on/off
> SqlCommand cmd = new SqlCommand("select * from GuidTest where 1=2", dest);
> SqlDataReader sRdr = cmd.ExecuteReader();
> tbl.Load(sRdr);
>
>// Load from source into existing schema
> source.Open();
> OleDbCommand sourceCommand = new OleDbCommand(@"select * from " + args[0], source);
> OleDbDataReader dr = sourceCommand.ExecuteReader();
> tbl.Load(dr);
> source.Close();
>
> Console.WriteLine("Beginning Copy ....");
>
> try
> {
> using (SqlBulkCopy s = new SqlBulkCopy(dest))
> {
> s.BatchSize = 500;
> s.BulkCopyTimeout = 300;
> s.DestinationTableName = "GuidTest";
> s.NotifyAfter = 10;
> s.SqlRowsCopied += new SqlRowsCopiedEventHandler(s_SqlRowsCopied);
> s.WriteToServer(tbl);
> s.Close();
> }
> Console.WriteLine("Copy complete");
> }
> catch (Exception e)
> {
> Console.WriteLine("{0} Exception caught", e);
> }
> finally
> {
> dest.Close();
> }
>
> }
>
> static void s_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
> {
> Console.WriteLine("-- Copied {0} rows.", e.RowsCopied);
> }
>}
>
>
>This works and the data gets copied to SQL server. However now the problem is I need to use a DataTable. It is very slow and I could live with that if it wouldn't give OutOfMemory exception on large files (well from SQL server point very small tables are sufficient to cause that expception - ie: a 300Mb dbf file can give that exception on a 2Gb RAM machine.).
>
>
>Is there a solution using 2.0?
>
>PS: Using linkedserver this really works fast and nicely but linkedservers are not enabled by default and customers think if MS didn't enebale it then I shouldn't even if temporarily:(
>
>Thanks in advance
>
>Cetin
Charles Hankey
Though a good deal is too strange to be believed, nothing is too strange to have happened.
- Thomas Hardy
Half the harm that is done in this world is due to people who want to feel important. They don't mean to do harm-- but the harm does not interest them. Or they do not see it, or they justify it because they are absorbed in the endless struggle to think well of themselves.
-- T. S. Eliot
Democracy is two wolves and a sheep voting on what to have for lunch.
Liberty is a well-armed sheep contesting the vote.
- Ben Franklin
Pardon him, Theodotus. He is a barbarian, and thinks that the customs of his tribe and island are the laws of nature.