I have a character 36 column in a data table that contains a guid value imported via a data adapter from another database and am trying to use bulk copy class to write table out to server, but I get an error when bcp tries to convert the char column to a uniqueidentifier data type.

What can I do to make this work? The bcp class does not have any options on casting columns and what not it is all seamless. Do I need to query the incoming column as any specific data type? Bad news is the source database is foxpro.

Anyone?

Perhaps you could provide us with the exact error you are getting. Also, what class exactly are you using? System.???

-Peter
compiledthoughts.com

Here is the code that executes. I get an error on the WriteToServer()call. The error is: "The given value of type Byte[] from the data source cannot be converted to type uniqueidentifier of the specified target column."

The source column is either a Character(36 ) type or a binary type ( I have tried both and get same error ). The column holds a GUID value generated in Foxpro and I want to move that value to a uniqueidentifier column in bulk with other columns.


using (SqlBulkCopy bcp = new SqlBulkCopy(sqlConn))
                    {
                        bcp.DestinationTableName = TableName;
                        bcp.BulkCopyTimeout = 120;
                        bcp.ColumnMappings.Clear();

                        // get alist of fields to ensure column mapping
                        var query =
                            from fields in dtFields.AsEnumerable()
                            where PcsPublics.JustStem(fields.Field<String>("cobjectn am").Trim()) == TableName
                            select new
                            {
                                FieldName = PcsPublics.JustExt(fields.Field<String>("cobjectna m"))
                            };


                        //create a var named fieldDefs that is a collection ( query result ) stored in query
                        foreach (var fieldDefs in query)
                            bcp.ColumnMappings.Add(fieldDefs.FieldName.Trim(), fieldDefs.FieldName.Trim());

                        log.WriteLine("Importing data into Sql table " + TableName);
                        log.WriteLine("---");
                        log.WriteLine("");
                        log.Flush();
                        // label3.Text = "Importing data into Sql table " + TableName + "...";
                        // label3.Refresh();
                        try
                        {
                            bcp.WriteToServer(dsPcs.Tables[TableName]);

                            dsPcs.Tables.Remove(TableName);
                            dsPcs.AcceptChanges();
                        }
                        catch (InvalidOperationException oex)
                        {
                            log.WriteLine("Bulk Copy error: " + oex.Message);
                        }

I think that SQL Server expects incoming values for guids to be strings. You'll probably need to convert the character byte to a string. It appears the .NET constructors for System.Guid can accept a byte array, but it's 16 bytes, versus 32 chars.

-Peter
compiledthoughts.com

Thanks.

I have tried bringing the original data in as a character 36 in the data table and still get the error, so I'll try manipulating the data table in the dataset before I call the Bulk Copy method.