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!

Having an updated base every day

Hello,
I have two symmetrical databases on two SQL servers - one remote, the
other - local. The data in the remote server changes every minute. The data
in local SQL server is used for creating some reports and general testing.
Sometimes data from the local base can be irreversibly deleted.
I want to have some sort of procedure, that would do one-direction
synchronisation of the two databases. The data in the remote database should
overwrite the data in the local database, so that after the update the local
database is exact copy of the remote database. Of course, this kind of
result could be achieved by detaching the remote base and bringing over the
database files, or doing full DTS. However, detaching and transfering is not
a viable solution since the remote base is actively used and the size of
data files are considerable; And, doing full DTS would take years. There is
some need to do some comparison of record hashes etc., so that the size of
the data that have to be transfered is reduced to minimum.
Thanks,
Pavils
The first thing that comes to mind is BACKUP and RESTORE. After you restore
a database backup you will have an exact copy of the original (as of the
date and time the backup completed).
You mention that the size of the data files are "considerable." I am not
sure what that means, or if that is a limitation for you in your
environment.
You might be able to look into replication.
Another option: if you have datetime columns (such as DateUpdated or
DateInserted) stored on your tables you could create your own shipping
routines...simply find the "recent" data (whatever that means to you) and
insert it.
You could also use linked servers and INSERT INTO...SELECT...WHERE NOT
EXISTS... you will have to fill in the blanks, but they should be fairly
easy.
Keith
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:OsrHETCkEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have two symmetrical databases on two SQL servers - one remote, the
> other - local. The data in the remote server changes every minute. The
data
> in local SQL server is used for creating some reports and general testing.
> Sometimes data from the local base can be irreversibly deleted.
> I want to have some sort of procedure, that would do one-direction
> synchronisation of the two databases. The data in the remote database
should
> overwrite the data in the local database, so that after the update the
local
> database is exact copy of the remote database. Of course, this kind of
> result could be achieved by detaching the remote base and bringing over
the
> database files, or doing full DTS. However, detaching and transfering is
not
> a viable solution since the remote base is actively used and the size of
> data files are considerable; And, doing full DTS would take years. There
is
> some need to do some comparison of record hashes etc., so that the size of
> the data that have to be transfered is reduced to minimum.
> Thanks,
> Pavils
>
|||Hello Keith,

> You mention that the size of the data files are "considerable." I am not
> sure what that means, or if that is a limitation for you in your
> environment.
The idea is that I don't want to transfer every single bit of the whole
database.

> You might be able to look into replication.
Would replication allow to create a fully matching copy wit using as little
data xfer as possible?

> Another option: if you have datetime columns (such as DateUpdated or
> DateInserted) stored on your tables you could create your own shipping
> routines...simply find the "recent" data (whatever that means to you) and
> insert it.
That could be a good solution, but it requires reworking much of the current
application.

> You could also use linked servers and INSERT INTO...SELECT...WHERE NOT
> EXISTS... you will have to fill in the blanks, but they should be fairly
> easy.
Surely, I can do that, however I was hoping for some more automatic (or
wizard-style) solution.
-- Pavils.
|||"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:uUQ1L7CkEHA.3968@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Hello Keith,
not
> The idea is that I don't want to transfer every single bit of the whole
> database.
understood

>
> Would replication allow to create a fully matching copy wit using as
little
> data xfer as possible?
That is the point of replication
[vbcol=seagreen]
and
> That could be a good solution, but it requires reworking much of the
current
> application.
Probably so (if you don't have a way to identify "new" rows.
[vbcol=seagreen]
fairly
> Surely, I can do that, however I was hoping for some more automatic (or
> wizard-style) solution.
This is not much different than my previous idea...it simply requires you to
type up some Transact-SQL. Hey, no one said being a dba/developer/IT
Professional was easy.

> -- Pavils.
>
Keith

Having an updated base every day

Hello,
I have two symmetrical databases on two SQL servers - one remote, the
other - local. The data in the remote server changes every minute. The data
in local SQL server is used for creating some reports and general testing.
Sometimes data from the local base can be irreversibly deleted.
I want to have some sort of procedure, that would do one-direction
synchronisation of the two databases. The data in the remote database should
overwrite the data in the local database, so that after the update the local
database is exact copy of the remote database. Of course, this kind of
result could be achieved by detaching the remote base and bringing over the
database files, or doing full DTS. However, detaching and transfering is not
a viable solution since the remote base is actively used and the size of
data files are considerable; And, doing full DTS would take years. There is
some need to do some comparison of record hashes etc., so that the size of
the data that have to be transfered is reduced to minimum.
Thanks,
PavilsThe first thing that comes to mind is BACKUP and RESTORE. After you restore
a database backup you will have an exact copy of the original (as of the
date and time the backup completed).
You mention that the size of the data files are "considerable." I am not
sure what that means, or if that is a limitation for you in your
environment.
You might be able to look into replication.
Another option: if you have datetime columns (such as DateUpdated or
DateInserted) stored on your tables you could create your own shipping
routines...simply find the "recent" data (whatever that means to you) and
insert it.
You could also use linked servers and INSERT INTO...SELECT...WHERE NOT
EXISTS... you will have to fill in the blanks, but they should be fairly
easy.
--
Keith
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:OsrHETCkEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have two symmetrical databases on two SQL servers - one remote, the
> other - local. The data in the remote server changes every minute. The
data
> in local SQL server is used for creating some reports and general testing.
> Sometimes data from the local base can be irreversibly deleted.
> I want to have some sort of procedure, that would do one-direction
> synchronisation of the two databases. The data in the remote database
should
> overwrite the data in the local database, so that after the update the
local
> database is exact copy of the remote database. Of course, this kind of
> result could be achieved by detaching the remote base and bringing over
the
> database files, or doing full DTS. However, detaching and transfering is
not
> a viable solution since the remote base is actively used and the size of
> data files are considerable; And, doing full DTS would take years. There
is
> some need to do some comparison of record hashes etc., so that the size of
> the data that have to be transfered is reduced to minimum.
> Thanks,
> Pavils
>|||Hello Keith,
> You mention that the size of the data files are "considerable." I am not
> sure what that means, or if that is a limitation for you in your
> environment.
The idea is that I don't want to transfer every single bit of the whole
database.
> You might be able to look into replication.
Would replication allow to create a fully matching copy wit using as little
data xfer as possible?
> Another option: if you have datetime columns (such as DateUpdated or
> DateInserted) stored on your tables you could create your own shipping
> routines...simply find the "recent" data (whatever that means to you) and
> insert it.
That could be a good solution, but it requires reworking much of the current
application.
> You could also use linked servers and INSERT INTO...SELECT...WHERE NOT
> EXISTS... you will have to fill in the blanks, but they should be fairly
> easy.
Surely, I can do that, however I was hoping for some more automatic (or
wizard-style) solution.
-- Pavils.|||"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:uUQ1L7CkEHA.3968@.TK2MSFTNGP11.phx.gbl...
> Hello Keith,
> > You mention that the size of the data files are "considerable." I am
not
> > sure what that means, or if that is a limitation for you in your
> > environment.
> The idea is that I don't want to transfer every single bit of the whole
> database.
understood
> > You might be able to look into replication.
> Would replication allow to create a fully matching copy wit using as
little
> data xfer as possible?
That is the point of replication
> > Another option: if you have datetime columns (such as DateUpdated or
> > DateInserted) stored on your tables you could create your own shipping
> > routines...simply find the "recent" data (whatever that means to you)
and
> > insert it.
> That could be a good solution, but it requires reworking much of the
current
> application.
Probably so (if you don't have a way to identify "new" rows.
> > You could also use linked servers and INSERT INTO...SELECT...WHERE NOT
> > EXISTS... you will have to fill in the blanks, but they should be
fairly
> > easy.
> Surely, I can do that, however I was hoping for some more automatic (or
> wizard-style) solution.
This is not much different than my previous idea...it simply requires you to
type up some Transact-SQL. Hey, no one said being a dba/developer/IT
Professional was easy.
> -- Pavils.
>
--
Keith

Having an updated base every day

Hello,
I have two symmetrical databases on two SQL servers - one remote, the
other - local. The data in the remote server changes every minute. The data
in local SQL server is used for creating some reports and general testing.
Sometimes data from the local base can be irreversibly deleted.
I want to have some sort of procedure, that would do one-direction
synchronisation of the two databases. The data in the remote database should
overwrite the data in the local database, so that after the update the local
database is exact copy of the remote database. Of course, this kind of
result could be achieved by detaching the remote base and bringing over the
database files, or doing full DTS. However, detaching and transfering is not
a viable solution since the remote base is actively used and the size of
data files are considerable; And, doing full DTS would take years. There is
some need to do some comparison of record hashes etc., so that the size of
the data that have to be transfered is reduced to minimum.
Thanks,
PavilsThe first thing that comes to mind is BACKUP and RESTORE. After you restore
a database backup you will have an exact copy of the original (as of the
date and time the backup completed).
You mention that the size of the data files are "considerable." I am not
sure what that means, or if that is a limitation for you in your
environment.
You might be able to look into replication.
Another option: if you have datetime columns (such as DateUpdated or
DateInserted) stored on your tables you could create your own shipping
routines...simply find the "recent" data (whatever that means to you) and
insert it.
You could also use linked servers and INSERT INTO...SELECT...WHERE NOT
EXISTS... you will have to fill in the blanks, but they should be fairly
easy.
Keith
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:OsrHETCkEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have two symmetrical databases on two SQL servers - one remote, the
> other - local. The data in the remote server changes every minute. The
data
> in local SQL server is used for creating some reports and general testing.
> Sometimes data from the local base can be irreversibly deleted.
> I want to have some sort of procedure, that would do one-direction
> synchronisation of the two databases. The data in the remote database
should
> overwrite the data in the local database, so that after the update the
local
> database is exact copy of the remote database. Of course, this kind of
> result could be achieved by detaching the remote base and bringing over
the
> database files, or doing full DTS. However, detaching and transfering is
not
> a viable solution since the remote base is actively used and the size of
> data files are considerable; And, doing full DTS would take years. There
is
> some need to do some comparison of record hashes etc., so that the size of
> the data that have to be transfered is reduced to minimum.
> Thanks,
> Pavils
>|||Hello Keith,

> You mention that the size of the data files are "considerable." I am not
> sure what that means, or if that is a limitation for you in your
> environment.
The idea is that I don't want to transfer every single bit of the whole
database.

> You might be able to look into replication.
Would replication allow to create a fully matching copy wit using as little
data xfer as possible?

> Another option: if you have datetime columns (such as DateUpdated or
> DateInserted) stored on your tables you could create your own shipping
> routines...simply find the "recent" data (whatever that means to you) and
> insert it.
That could be a good solution, but it requires reworking much of the current
application.

> You could also use linked servers and INSERT INTO...SELECT...WHERE NOT
> EXISTS... you will have to fill in the blanks, but they should be fairly
> easy.
Surely, I can do that, however I was hoping for some more automatic (or
wizard-style) solution.
-- Pavils.|||"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:uUQ1L7CkEHA.3968@.TK2MSFTNGP11.phx.gbl...
> Hello Keith,
>
not[vbcol=seagreen]
> The idea is that I don't want to transfer every single bit of the whole
> database.
understood

>
> Would replication allow to create a fully matching copy wit using as
little
> data xfer as possible?
That is the point of replication

>
and[vbcol=seagreen]
> That could be a good solution, but it requires reworking much of the
current
> application.
Probably so (if you don't have a way to identify "new" rows.

>
fairly[vbcol=seagreen]
> Surely, I can do that, however I was hoping for some more automatic (or
> wizard-style) solution.
This is not much different than my previous idea...it simply requires you to
type up some Transact-SQL. Hey, no one said being a dba/developer/IT
Professional was easy.

> -- Pavils.
>
Keithsql

Hacked SQL Server

We just found out that we got hacked by the sql server
worm. We have updated the machine and removed the worm.
However we now have a new user in our sql database that
we did not have before. Also we can no longer access any
of the databases that we were able to before. Looks like
the hacker accessed the sa account and changed the
password. Whenever we try to do anything we get a message
saying we do not have access. Looks like the hacker
removed administrators from doing any changes to the
machine.
How do we recover the sa account password or make any
changes to anything?
Thanks in advance for any advice
David Anderson
andretti@.toyorders.comWas the SQL Server behind a firewall?
What service pack were you running on SQL?
Do you have backups of the master database prior to this event?
The only options are restoring master or using rebuildm to rebuilt the
master database.
But, unless you understand how the machine was comprimised, this may happen
again.
Our recommendation & the general recommendation of the Security community
would be to rebuild the machine from the OS with all the patches installed.
However, you will need to make a business decision and weigh out the pros
and cons of not be able to conclusively detect all the changes made to this
machine. Cert also has good recommendations;
http://www.cert.org/nav/recovering.html
Help: I Got Hacked. Now What Do I Do?
http://www.microsoft.com/technet/co...gmt/sm0504.mspx
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||On Tue, 15 Jun 2004 15:02:50 -0700, "David Anderson"
<andretti@.toyorders.com> wrote:

>We just found out that we got hacked by the sql server
>worm. We have updated the machine and removed the worm.
>However we now have a new user in our sql database that
>we did not have before. Also we can no longer access any
>of the databases that we were able to before. Looks like
>the hacker accessed the sa account and changed the
>password. Whenever we try to do anything we get a message
>saying we do not have access. Looks like the hacker
>removed administrators from doing any changes to the
>machine.
>How do we recover the sa account password or make any
>changes to anything?
You can restore the Master DB from backup or rebuild it to recover the
SA settings or reset them to defaults. Be aware there may have been
changes since your last valid backup that may affect your database.
Also, are you sure that you're not still compromised? Having SA
access, especially if the SP_EXEC stored procedure is left in place
means you could have much more compromised on your system than your
SQL server accounts.
Best is to nuke and rebuild. Only restore from known good backups.
Jeff