Having trouble converting to datetime in SQL import

Our database gets updated each week with text files spat out by a mainframe. Previously, the database was in Access; we copied and pasted the text files into Excel, ran macros on them to convert the data, then pasted the results directly into Access and this worked fine for us.

Now that we've moved the tables to SQL Server 2000, we're having problems with the data. We wanted to set up DTS packages for each file to just put them directly into SQL Server. This works for pretty much everything except for the dates.

The way the files are set up, they're comma delimited files with quotes around the text and nothing around the dates. The dates don't have any delimiters; they're just listed like 13012006. Every time we try to import these files into SQL, it gripes about the datatypes; we're trying to put the dates into datetime fields but SQL thinks they're strings. Eek! If we put date delimiters (like 13/01/2006) SQL pulls them in fine, but apparently the mainframe lacks the ability to put these delimiters in by itself and still run everything else OK. The person who writes the extracts has to do it in a language called 'Focus' which I've never heard of and don't know anything about, and he says what I'm asking for can't be done. OK...so now what?

I've tried and tried to convert these strings into dates using both CAST() and CONVERT() and just can't manage to do it. I know I'm missing something really obvious here; does anyone have any tips or advice? Thanks in advance.It's a bit of a pain to find in the DTS Designer, but within the Data Pump task, if you individually define the column mappings, there is an option to select DateTime strings.

One way to do this is to:
1. Select your source and destination connections
2. Add the Data Pump task
3. Specify your source table/query
4. Specify the destination query
5. Click on the 'Transformations' tab (usually the column mappings will auto-populate)
6. Click on the column mapping where the source is the date formatted as a string.
7. Delete this column mapping (click on delete or hit the delete key)
8. Select the source column and the destination column and click on 'New'
9. Choose the third item from the pop-up list (DateTime string)
10. You will then have to define the properties of the source and destination formats.

I've worked with this before and it worked pretty well.

Alternatively, you could proceed through item 8 and then (in item 9) choose ActiveX script. This will allow you to define more custom formats and insert additional error checking and/or handling.

Regards,

hmscott|||It's a bit of a pain to find in the DTS Designer, but within the Data Pump task, if you individually define the column mappings, there is an option to select DateTime strings.

One way to do this is to:
1. Select your source and destination connections
2. Add the Data Pump task
3. Specify your source table/query
4. Specify the destination query
5. Click on the 'Transformations' tab (usually the column mappings will auto-populate)
6. Click on the column mapping where the source is the date formatted as a string.
7. Delete this column mapping (click on delete or hit the delete key)
8. Select the source column and the destination column and click on 'New'
9. Choose the third item from the pop-up list (DateTime string)
10. You will then have to define the properties of the source and destination formats.

I've worked with this before and it worked pretty well.

Alternatively, you could proceed through item 8 and then (in item 9) choose ActiveX script. This will allow you to define more custom formats and insert additional error checking and/or handling.

Regards,

hmscott

Thanks for the advice; I'll try your first suggestion, since I can't write ActiveX script. I can usually manage SQL and I can almost always figure it out on my own, but this one stumped me. I'm not a certified DBA or anything, I'm still learning. :)|||It's a bit of a pain to find in the DTS Designer, but within the Data Pump task, if you individually define the column mappings, there is an option to select DateTime strings.

One way to do this is to:
1. Select your source and destination connections
2. Add the Data Pump task
3. Specify your source table/query
4. Specify the destination query
5. Click on the 'Transformations' tab (usually the column mappings will auto-populate)
6. Click on the column mapping where the source is the date formatted as a string.
7. Delete this column mapping (click on delete or hit the delete key)
8. Select the source column and the destination column and click on 'New'
9. Choose the third item from the pop-up list (DateTime string)
10. You will then have to define the properties of the source and destination formats.

I've worked with this before and it worked pretty well.

Alternatively, you could proceed through item 8 and then (in item 9) choose ActiveX script. This will allow you to define more custom formats and insert additional error checking and/or handling.

Regards,

hmscott

We tried using datetime strings and it just refused to work; we had to write ActiveX scripts n the end using DateSerial in VBScript, which was tough for us because neither one of us really knows VBScript, I'm a VB noob and my colleague knows VBA but not VBScript; I know they're basically the same but he doesn't. :) But we did eventually get it to work! So yay!

Have an .mdf file but can't seem to attach it.

We had a server that was having all kinds of problems. We finally got it
booted and I copied the .MDF file off the hard drive. I'm trying to create a
new database on a different machine using this .MDF file and it appears to
not work. I had shut down SQL on the machine before I copied the file but I
didn't detach it ( didn't have the time and I was trying to explain it to
somebody else. ) So now I have this .MDF file that I can't attach. I'm doing
the following command:
use master
go
CREATE DATABASE BugTracking on (Filename='D:\Program Files\Microsoft SQL
Server\MSSQL\Data\BugTracking.mdf') for attach
go
and I'm getting the following error:
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'BugTracking'. CREATE DATABASE is aborted.
Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 23, object ID 1, index ID
1. Run DBCC CHECKTABLE on sysindexes.
I've also tried to a direct attach deleting the log file and it won't do it.
Anybody have any thoughts?
TIA - Jeff.
Can you try sp_attach_single_file_db and see if it works?
See:
http://msdn2.microsoft.com/en-us/library/ms174385(SQL.90).aspx

Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Mufasa" <jb@.nowhere.com> wrote in message
news:eH$ODo1sHHA.840@.TK2MSFTNGP03.phx.gbl...
> We had a server that was having all kinds of problems. We finally got it
> booted and I copied the .MDF file off the hard drive. I'm trying to create
> a new database on a different machine using this .MDF file and it appears
> to not work. I had shut down SQL on the machine before I copied the file
> but I didn't detach it ( didn't have the time and I was trying to explain
> it to somebody else. ) So now I have this .MDF file that I can't attach.
> I'm doing the following command:
>
> use master
> go
> CREATE DATABASE BugTracking on (Filename='D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\BugTracking.mdf') for attach
> go
>
> and I'm getting the following error:
>
> Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'BugTracking'. CREATE DATABASE is aborted.
> Msg 602, Level 21, State 50, Line 1
> Could not find row in sysindexes for database ID 23, object ID 1, index ID
> 1. Run DBCC CHECKTABLE on sysindexes.
>
> I've also tried to a direct attach deleting the log file and it won't do
> it.
> Anybody have any thoughts?
> TIA - Jeff.
>
|||Sorry everybody - I finally figured out what was wrong - I was trying to
attach the file from a 2005 db into 2000. Once I switched to another server,
it worked fine.
J.
"Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
news:E95832E9-C1D4-4BC3-B010-6F7079F2AEAF@.microsoft.com...
> Can you try sp_attach_single_file_db and see if it works?
> See:
> http://msdn2.microsoft.com/en-us/library/ms174385(SQL.90).aspx
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Mufasa" <jb@.nowhere.com> wrote in message
> news:eH$ODo1sHHA.840@.TK2MSFTNGP03.phx.gbl...
>

Have an .mdf file but can't seem to attach it.

We had a server that was having all kinds of problems. We finally got it
booted and I copied the .MDF file off the hard drive. I'm trying to create a
new database on a different machine using this .MDF file and it appears to
not work. I had shut down SQL on the machine before I copied the file but I
didn't detach it ( didn't have the time and I was trying to explain it to
somebody else. ) So now I have this .MDF file that I can't attach. I'm doing
the following command:
use master
go
CREATE DATABASE BugTracking on (Filename='D:\Program Files\Microsoft SQL
Server\MSSQL\Data\BugTracking.mdf') for attach
go
and I'm getting the following error:
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'BugTracking'. CREATE DATABASE is aborted.
Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 23, object ID 1, index ID
1. Run DBCC CHECKTABLE on sysindexes.
I've also tried to a direct attach deleting the log file and it won't do it.
Anybody have any thoughts?
TIA - Jeff.Can you try sp_attach_single_file_db and see if it works?
See:
http://msdn2.microsoft.com/en-us/library/ms174385(SQL.90).aspx
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Mufasa" <jb@.nowhere.com> wrote in message
news:eH$ODo1sHHA.840@.TK2MSFTNGP03.phx.gbl...
> We had a server that was having all kinds of problems. We finally got it
> booted and I copied the .MDF file off the hard drive. I'm trying to create
> a new database on a different machine using this .MDF file and it appears
> to not work. I had shut down SQL on the machine before I copied the file
> but I didn't detach it ( didn't have the time and I was trying to explain
> it to somebody else. ) So now I have this .MDF file that I can't attach.
> I'm doing the following command:
>
> use master
> go
> CREATE DATABASE BugTracking on (Filename='D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\BugTracking.mdf') for attach
> go
>
> and I'm getting the following error:
>
> Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'BugTracking'. CREATE DATABASE is aborted.
> Msg 602, Level 21, State 50, Line 1
> Could not find row in sysindexes for database ID 23, object ID 1, index ID
> 1. Run DBCC CHECKTABLE on sysindexes.
>
> I've also tried to a direct attach deleting the log file and it won't do
> it.
> Anybody have any thoughts?
> TIA - Jeff.
>|||Sorry everybody - I finally figured out what was wrong - I was trying to
attach the file from a 2005 db into 2000. Once I switched to another server,
it worked fine.
J.
"Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
news:E95832E9-C1D4-4BC3-B010-6F7079F2AEAF@.microsoft.com...
> Can you try sp_attach_single_file_db and see if it works?
> See:
> http://msdn2.microsoft.com/en-us/library/ms174385(SQL.90).aspx
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Mufasa" <jb@.nowhere.com> wrote in message
> news:eH$ODo1sHHA.840@.TK2MSFTNGP03.phx.gbl...
>

Have an .mdf file but can't seem to attach it.

We had a server that was having all kinds of problems. We finally got it
booted and I copied the .MDF file off the hard drive. I'm trying to create a
new database on a different machine using this .MDF file and it appears to
not work. I had shut down SQL on the machine before I copied the file but I
didn't detach it ( didn't have the time and I was trying to explain it to
somebody else. ) So now I have this .MDF file that I can't attach. I'm doing
the following command:
use master
go
CREATE DATABASE BugTracking on (Filename='D:\Program Files\Microsoft SQL
Server\MSSQL\Data\BugTracking.mdf') for attach
go
and I'm getting the following error:
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'BugTracking'. CREATE DATABASE is aborted.
Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 23, object ID 1, index ID
1. Run DBCC CHECKTABLE on sysindexes.
I've also tried to a direct attach deleting the log file and it won't do it.
Anybody have any thoughts?
TIA - Jeff.Can you try sp_attach_single_file_db and see if it works?
See:
http://msdn2.microsoft.com/en-us/library/ms174385(SQL.90).aspx
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Mufasa" <jb@.nowhere.com> wrote in message
news:eH$ODo1sHHA.840@.TK2MSFTNGP03.phx.gbl...
> We had a server that was having all kinds of problems. We finally got it
> booted and I copied the .MDF file off the hard drive. I'm trying to create
> a new database on a different machine using this .MDF file and it appears
> to not work. I had shut down SQL on the machine before I copied the file
> but I didn't detach it ( didn't have the time and I was trying to explain
> it to somebody else. ) So now I have this .MDF file that I can't attach.
> I'm doing the following command:
>
> use master
> go
> CREATE DATABASE BugTracking on (Filename='D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\BugTracking.mdf') for attach
> go
>
> and I'm getting the following error:
>
> Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'BugTracking'. CREATE DATABASE is aborted.
> Msg 602, Level 21, State 50, Line 1
> Could not find row in sysindexes for database ID 23, object ID 1, index ID
> 1. Run DBCC CHECKTABLE on sysindexes.
>
> I've also tried to a direct attach deleting the log file and it won't do
> it.
> Anybody have any thoughts?
> TIA - Jeff.
>|||Sorry everybody - I finally figured out what was wrong - I was trying to
attach the file from a 2005 db into 2000. Once I switched to another server,
it worked fine.
J.
"Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
news:E95832E9-C1D4-4BC3-B010-6F7079F2AEAF@.microsoft.com...
> Can you try sp_attach_single_file_db and see if it works?
> See:
> http://msdn2.microsoft.com/en-us/library/ms174385(SQL.90).aspx
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Mufasa" <jb@.nowhere.com> wrote in message
> news:eH$ODo1sHHA.840@.TK2MSFTNGP03.phx.gbl...
>> We had a server that was having all kinds of problems. We finally got it
>> booted and I copied the .MDF file off the hard drive. I'm trying to
>> create a new database on a different machine using this .MDF file and it
>> appears to not work. I had shut down SQL on the machine before I copied
>> the file but I didn't detach it ( didn't have the time and I was trying
>> to explain it to somebody else. ) So now I have this .MDF file that I
>> can't attach. I'm doing the following command:
>>
>> use master
>> go
>> CREATE DATABASE BugTracking on (Filename='D:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\BugTracking.mdf') for attach
>> go
>>
>> and I'm getting the following error:
>>
>> Msg 1813, Level 16, State 2, Line 1
>> Could not open new database 'BugTracking'. CREATE DATABASE is aborted.
>> Msg 602, Level 21, State 50, Line 1
>> Could not find row in sysindexes for database ID 23, object ID 1, index
>> ID 1. Run DBCC CHECKTABLE on sysindexes.
>>
>> I've also tried to a direct attach deleting the log file and it won't do
>> it.
>> Anybody have any thoughts?
>> TIA - Jeff.
>>
>