Invalid date format error from ImportData method

[Muhanned]

I have a VB6 application that utilizes SQL-DMO to import a file into a table (ImportData Method is used). However, I receive the following error "Invalid date format @#" when I execute the program.

The issue is, I have a Server X with regional setting for date as dd/mm/yyyy and the Database Server is in the standard mm/dd/yyyy. The Server X produces an error output file of records that previously failed to insert but now I want to import the data. However, the file produced has a date/time format in the previously mentioned dd/mm/yyyy.

Therefore, does anybody know if via SQL-DMO you can set the session date format to DMY instead of MDY? And, will this resolve the date conflict issue?

 

[kmfna]

It may add an extra step, but is there any way that you can run a cleanup on the file to change the format prior to using the import utility? IE. Read in the file, clean up all of the date fields (and any other fields you may decide to change) and spit it back out to a new file that can trigger the batch import.

Good luck.

Kevin

 

[chiph]

Can you use a Convert statement in the SQL used to extract the data from the source database?

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[Muhanned]

Hi Kevin,

Yes thank you very much for your prompt response.

I am very much considering this option. I have been doing my own separate research on the internet on date formating via SQL-DMO objects such as the BulkCopy and ImportData objects that I am using and it seems there is no way to affect the date format. I was just hoping just in case some one out there may have attempted this.

Thanks again,
Muhanned

 

[Muhanned]

Hi Chip,

We are trying to minimize any modification to our application that I designated as Server X (location of our application).

The application will give me the file as is and I have to attempt to import as is. Unfortunately, for us and our customer, we encountered "Murphy" way too many times in this project so, I prefer not to upset things at the source. So, I am trying to modify things downstream. I was just hoping that I may have not tried one more thing with SQL-DMO objects. Looks like it might be the long way out for this step.

Thanks again for your reply,
Muhanned

 

[kmfna]

Muhanned,

I wouldn't worry too much about it being a difficult transition, Unless the file is huge, the wait time for the field alteration and new file creation shouldn't be bad. The user would really never have to even know that was going on, you could have them browse for the file with the bad format and do everything else internally, and clean up by simply deleting the file after the import is done with noone the wiser.

Good luck,

Kevin

 

[Muhanned]

Okay, I created a reformat method that takes care of the input file. It was very easy.

However, does anyone know why the Rows Property of the SQL-DMO Table object does not refresh the property values. I was trying to get a before and after count with the number of rows imported. The Rows property never seems to get updated. Has anyone successfully tried to update this value after an ImportData Method?