change formating

I have a txt file that I import into my SQL2003 server. One of the items
is formated as 10/10/2005 13:23. That is the way teh file is sent to me.
I would like to be able to have it changed to just the 10/10/2005 format.
Is there an easy way to auto do that?
I currently have a tmp Table that the data gets readinto first, then it
updates the main table for any changes of exsisting records so there is room
for me to have somethign go in and modify the data.Hi
You don't say how you are loading this file?
You could treat this as two fields and ignore the second field (time part).
If you are using BCP or BULK INSERT then you can specify a format file and if
you are using DTS or the import wizard you can also specify that it should
ignore this data.
If you are loading into a datatime datatype you will always be holding the
time portion, but this will be 00:00:00 if you don't specify the time. A
different alternative (and probably slower!) if you are using datatime would
to update the data post insert.
John
"Johnfli" wrote:
> I have a txt file that I import into my SQL2003 server. One of the items
> is formated as 10/10/2005 13:23. That is the way teh file is sent to me.
> I would like to be able to have it changed to just the 10/10/2005 format.
> Is there an easy way to auto do that?
> I currently have a tmp Table that the data gets readinto first, then it
> updates the main table for any changes of exsisting records so there is room
> for me to have somethign go in and modify the data.
>
>|||I have a DTS package that is doing a bulk insert after reading teh text
file. Teh text file is camma delimted so I do not know how I would be able
to seperate teh time from teh date.
Here is a sample of the date being imported:
"ON HAND",12/19/2005
13:21:50,"01670136754","GILBERT","AEMNL0512012","09635","7977524ANJA","4",4,118,51.72,0.44,"N",,0,,0,,
"ON HAND",12/19/2005
13:21:50,"01670136754","GILBERT","AEMNL0512012","09635","7977525ANJA","4",4,118,51.71,0.44,"N",,0,,0,,
"ON HAND",12/19/2005
13:21:50,"01670136754","GILBERT","AEMNL0512012","09620","7977524ANJA","2",2,50,22.41,0.22,"N",,0,,0,,
"ON HAND",12/19/2005
13:21:50,"01670136754","GILBERT","AEMNL0512012","09620","7977525ANJA","2",2,50,22.41,0.22,"N",,0,,0,,
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D5D46761-C4B7-4557-825A-774BC97B8425@.microsoft.com...
> Hi
> You don't say how you are loading this file?
> You could treat this as two fields and ignore the second field (time
> part).
> If you are using BCP or BULK INSERT then you can specify a format file and
> if
> you are using DTS or the import wizard you can also specify that it should
> ignore this data.
> If you are loading into a datatime datatype you will always be holding the
> time portion, but this will be 00:00:00 if you don't specify the time. A
> different alternative (and probably slower!) if you are using datatime
> would
> to update the data post insert.
> John
> "Johnfli" wrote:
>> I have a txt file that I import into my SQL2003 server. One of the
>> items
>> is formated as 10/10/2005 13:23. That is the way teh file is sent to
>> me.
>> I would like to be able to have it changed to just the 10/10/2005 format.
>> Is there an easy way to auto do that?
>> I currently have a tmp Table that the data gets readinto first, then it
>> updates the main table for any changes of exsisting records so there is
>> room
>> for me to have somethign go in and modify the data.
>>|||Hi
You can specify that the source file is comma delimited and if you use an
ActiveX transformation you can then you can specify something like:
DTSDestination("Col002") = LEFT(DTSSource("Col002"),8)
in the properties dialog (The properties button is on the general tab)
John
"Johnfli" wrote:
> I have a DTS package that is doing a bulk insert after reading teh text
> file. Teh text file is camma delimted so I do not know how I would be able
> to seperate teh time from teh date.
> Here is a sample of the date being imported:
> "ON HAND",12/19/2005
> 13:21:50,"01670136754","GILBERT","AEMNL0512012","09635","7977524ANJA","4",4,118,51.72,0.44,"N",,0,,0,,
> "ON HAND",12/19/2005
> 13:21:50,"01670136754","GILBERT","AEMNL0512012","09635","7977525ANJA","4",4,118,51.71,0.44,"N",,0,,0,,
> "ON HAND",12/19/2005
> 13:21:50,"01670136754","GILBERT","AEMNL0512012","09620","7977524ANJA","2",2,50,22.41,0.22,"N",,0,,0,,
> "ON HAND",12/19/2005
> 13:21:50,"01670136754","GILBERT","AEMNL0512012","09620","7977525ANJA","2",2,50,22.41,0.22,"N",,0,,0,,
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D5D46761-C4B7-4557-825A-774BC97B8425@.microsoft.com...
> > Hi
> >
> > You don't say how you are loading this file?
> >
> > You could treat this as two fields and ignore the second field (time
> > part).
> > If you are using BCP or BULK INSERT then you can specify a format file and
> > if
> > you are using DTS or the import wizard you can also specify that it should
> > ignore this data.
> >
> > If you are loading into a datatime datatype you will always be holding the
> > time portion, but this will be 00:00:00 if you don't specify the time. A
> > different alternative (and probably slower!) if you are using datatime
> > would
> > to update the data post insert.
> >
> > John
> >
> > "Johnfli" wrote:
> >
> >> I have a txt file that I import into my SQL2003 server. One of the
> >> items
> >> is formated as 10/10/2005 13:23. That is the way teh file is sent to
> >> me.
> >> I would like to be able to have it changed to just the 10/10/2005 format.
> >>
> >> Is there an easy way to auto do that?
> >> I currently have a tmp Table that the data gets readinto first, then it
> >> updates the main table for any changes of exsisting records so there is
> >> room
> >> for me to have somethign go in and modify the data.
> >>
> >>
> >>
>
>

Certificate Restore Not working.

I have been trying to use Encryption to encrypt a few key fields. So my first goal was to create all the items I would need to effectivly use encryption, to encrypt a few key fields.

First I created a sample sql script to create a certificate, create a table and insert some test data encrypted. And then made sure I could decrypt the data encrypted with the certificate.

Second step was to test the backing up of the certificate, droping the certificate to similate a restore of the database. And then a restore of the certificate, and then see if the it was still possible to decrypt the existing encrypted data with the restored certificate.

I receive no errors when restoreing the certificate, but it does not properly decrypt the exisiting data.

Can anyone help and point out my mistake?

--Start Sql--

--Create Sample Cert CREATE CERTIFICATE SampleCert1 ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C' WITH SUBJECT = 'Sample Cert for Demo', EXPIRY_DATE = '04/18/2017'; --create Sample table with encrypted version CREATE TABLE Table_1 (Id int NOT NULL IDENTITY (1, 1), SSN_Encrypted varbinary(300) NOT NULL) ON [PRIMARY] GO --insert row with encrypted version. Insert into Table_1 (SSN_Encrypted) values (EncryptByCert(Cert_ID('SampleCert1'),'000-00-0000')) --returns '000-00-0000' for 'SampleCert1' since it was able to decrypt select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted,N'728AC41753642403251BF8E7233EC0C') as varchar(12)) as 'SampleCert1' from Table_1 GO --backup Certificate BACKUP CERTIFICATE SampleCert1 TO FILE = 'D:\Backups\SampleCert.cer' WITH PRIVATE KEY ( FILE = 'D:\Backups\SampleCert.pvk' , DECRYPTION BY PASSWORD = N'728AC41753642403251BF8E7233EC0C' , ENCRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh' ); GO DROP CERTIFICATE SampleCert1; GO --Restore from backup. to simulate a restore from tape CREATE CERTIFICATE SampleCert1 FROM FILE = 'D:\Backups\SampleCert.cer' WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk', DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh'); --This should return the same ssn as was encrypted, if returns null the restore failed. select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted,N'728AC41753642403251BF8E7233EC0C') as varchar(12)) as 'SampleCert1' from Table_1 --cleanup Removes Certificate from the system DROP CERTIFICATE SampleCert1; --removes the temporary table. Drop table Table_1

I think I found out why your statement is not working as you expected.

In the following query, the certificate is re-created from the backup, but only a decryption password (used to open the PVK file) is specified, but no encryption password is present. This will create the CERTIFICATE in SQL Server with the private key protected by the DB master key.

CREATE CERTIFICATE SampleCert1

FROM FILE = 'D:\Backups\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk',

DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh');

Instead of

CREATE CERTIFICATE SampleCert1

FROM FILE = 'D:\Backups\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk',

DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh',

ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C');

You can verify if my assumption is correct by running the following statement:

SELECT name, pvt_key_encryption_type_desc FROM sys.certificates

If I am right, you can just change the protection mechanism for the existing CERTIFICATE:

ALTER CERTIFICATE SampleCert1

WITH PRIVATE KEY ( ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C' );

Let us know if this information helped.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Well today is not my lucky day I am afraid. I can't verify yet.

Last night we rolled out a policy to enforce passwords complexity, and length requirements.

So I am geting an error just trying to run the same script, even though I am using NT Authentication, and have updated my password to conform to the new policy.

Msg 15118, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.

Does Create Certificate use a different security creditial then the loged in Users?

I even tried using SA with the following set. with the same results.

Code Snippet

Alter Login [sa] with CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Any more idea's where I need to look to figure this out?

|||

Disabling the password policy checks is not available for cryptographic objects (certificates, keys, etc.), the only part of the policy enforced in such objects is the password complexity for newly created or modified objects/passphrases.

ALTER CERTIFICATE SampleCert1

WITH PRIVATE KEY ( ENCRYPTION BY PASSWORD = '<<new password that complies with password policy>>' );

--returns '000-00-0000' for 'SampleCert1' since it was able to decrypt

select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted, <<new password that complies with password policy>>') as varchar(12)) as 'SampleCert1'

from Table_1

Or if you prefer to use the DBMK to protect your certificate, you can change your query to the following:

select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted) as varchar(12)) as 'SampleCert1'

from Table_1

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thank you for all your help.

Knowing what password I had to fix made it short work geting it working.

Owen.