BCP using SMO

We have a SQLServer 2000 stored procedure, which imports data from files using SQL-DMO BulkCopy object(sp_OACreate is used to create the objects). We also use Format files to aide the import.

Now, we are planning to convert this stored procedure to .NET application using Visual Studio 2005. Books Online says I have to use SMO Transfer object. But I could not find any information how to BulkCopy using this object.

Any guidance in this regard would be helpful.

Thanks

Baskar

Please refer to the SMO Transfer BOL topic: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9mref/html/T_Microsoft_SqlServer_Management_Smo_Transfer.htm|||Hi, SMO does not have support for BCP objects.

You have 2 options:

1. Use SQL-DMO BulkCopy
2. Use SqlBulkCopy: http://msdn2.microsoft.com/en-us/library/30c3y597(en-us,vs.80).aspx.|||

I tried to do BCP using SQL-DMO object. Our project requires FORMAT FILES to load data into work tables.

If I generate the format file according to SQL 2005 specification (with 9.0 as version number on top of format file), my C# application reports an error "Attempts to read unknown version of BCP Format file". If I change it to 8.0 it works fine.
I installed the backward compatibility utility for SQL-DMO (SQL2005_BC.msi), thinking that it might fix the problem. Nope again the same message.

Now with the SqlBulkCopy object of ADO.NET, can we import into a table from FILES? Can I use the Mappings collection to map columns in text file to Work Table?

Any help in this regard is greatly appreciated.

Thanks
Baskar

BCP using SMO

We have a SQLServer 2000 stored procedure, which imports data from files using SQL-DMO BulkCopy object(sp_OACreate is used to create the objects). We also use Format files to aide the import.

Now, we are planning to convert this stored procedure to .NET application using Visual Studio 2005. Books Online says I have to use SMO Transfer object. But I could not find any information how to BulkCopy using this object.

Any guidance in this regard would be helpful.

Thanks

Baskar

Please refer to the SMO Transfer BOL topic: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9mref/html/T_Microsoft_SqlServer_Management_Smo_Transfer.htm|||Hi, SMO does not have support for BCP objects.

You have 2 options:

1. Use SQL-DMO BulkCopy
2. Use SqlBulkCopy: http://msdn2.microsoft.com/en-us/library/30c3y597(en-us,vs.80).aspx.|||

I tried to do BCP using SQL-DMO object. Our project requires FORMAT FILES to load data into work tables.

If I generate the format file according to SQL 2005 specification (with 9.0 as version number on top of format file), my C# application reports an error "Attempts to read unknown version of BCP Format file". If I change it to 8.0 it works fine.
I installed the backward compatibility utility for SQL-DMO (SQL2005_BC.msi), thinking that it might fix the problem. Nope again the same message.

Now with the SqlBulkCopy object of ADO.NET, can we import into a table from FILES? Can I use the Mappings collection to map columns in text file to Work Table?

Any help in this regard is greatly appreciated.

Thanks
Baskar

BCP and Date Problems

Hi,

We have a job that, every night, imports mail logs into SQL Server for processing. We receive these mail logs from the mail group, so we have no say in how the log should be formatted, basically we are given what we are given.

Our problem started occurring on Oct-01, and has occured every day since then.

The log file may contain rows with dates such as follows (don't ask me why there isn't a consistant format)

1) 2003-10-01 00:18:5
2) 2003-9-01 00:8:6
3) 2003-9-1 00:9:6
4) 2003-9-1 0:09:6

5) 2003-10-1 00:18:6
6) 2003-10-01 0:19:6
7) 2003-10-01 00:8:6

Now, 1-4 import. 5-7 don't.

From what I can see.

If the month is a single digit month, i.e. September above, then it will import basically all formats (e.g. 1 digit for the hour, 1 digit for the minute or 1 digit for the day).

If the month is a double digit month, i.e. October above, then it will not import unless the date is perfectly formatted (e.g. 2 digits for the hour AND 2 digits for the minute AND 2 digits for day).

An excerpt from the format file we are using is listed below.

Why does SQL Server allow 'bad' dates with single digit months, but not double digit months? It's really confusing me. I know the obvious solution, tell the Mail guys to properly format their files, but are there any other solutions?

Thanks heaps. Hope I've asked this clearly.

Format File
----
8.0
11
1 SQLCHAR 0 0 "" 0 TBL_MAIL_LOG_ID ""
2 SQLCHAR 0 0 "" 0 LOG_FILE_ID ""
3 SQLCHAR 0 1 " " 3 DATE ""

The last line reads (broken into individual lines below)

3
SQLCHAR
0
1
" " <-- there is a space in there
3
DATE
""Howdy

Sadly it appears its a quirk in SQL - unless a '1' is an '01' it seems to get its knickers in a knot, especially at the start of a column of information.

Try importing the raw data into another table that uses CHAR instead of datetime for the dates, then add the '0' to any dates required, then transfer across to final table. Its time consuming, but in the long run more robust.

Cheers

SG