January 27, 2009 at 6:23 am
Hi can someone please tell me what is wrong with this syntax: I am trying to insert unencryted data into a decrypted field
INSERT INTO ALLSTAFF_dec
(Co
, CoName
, Department
, Dept
, EmplID
, PayGroup
, Grade
, CAST(CONVERT(VARCHAR(50),DecryptByKey(TotalPack)) AS Float) AS TotalPack
, Age
, Initials
, CAST(CONVERT(VARCHAR(50),DecryptByKey(AnnualRate)) AS Float) AS AnnualRate
, [Action]
, Prefix
, CompRate
, AnnBenRt)
SELECT Co, CoName, Department, Dept, EmplID, PayGroup, Grade, TotalPack, Age, Initials, AnnualRate, [Action], Prefix, CompRate, AnnBenRt
FROM ALLSTAFF
WHERE (Period = 200811)
January 27, 2009 at 7:19 am
What is the error or result that you get? Is the Symmetric Key already open before executing this INSERT statement?
K. Brian Kelley
@kbriankelley
January 27, 2009 at 7:20 am
Does it give a syntax error? Does it give a runtime error? Does it give incorrect results? If an error, what error?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2009 at 1:20 am
Yes the symmetric key is open. The error I'm getting is:
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '('.
and when I remove the decryption code and run the query it runs but gives null values for the decrypted fields
January 28, 2009 at 1:26 am
I'm blind. You've got a syntax error in your insert statement. You've got the cast and convert and decrypt by key where you're supposed to be declaring the columns to populate. Here's how it should be:
INSERT INTO ALLSTAFF_dec
(Co
, CoName
, Department
, Dept
, EmplID
, PayGroup
, Grade
, TotalPack
, Age
, Initials
, AnnualRate
, [Action]
, Prefix
, CompRate
, AnnBenRt)
SELECT Co, CoName, Department, Dept, EmplID, PayGroup, Grade,
CAST(CONVERT(VARCHAR(50),DecryptByKey(TotalPack)) AS Float) AS TotalPack, Age, Initials,
CAST(CONVERT(VARCHAR(50),DecryptByKey(AnnualRate)) AS Float) AS AnnualRate, [Action], Prefix, CompRate, AnnBenRt
FROM ALLSTAFF
WHERE (Period = 200811)
K. Brian Kelley
@kbriankelley
January 28, 2009 at 1:50 am
I still get an error:
Msg 8116, Level 16, State 1, Line 4
Argument data type float is invalid for argument 1 of DecryptByKey function.
January 28, 2009 at 6:02 am
thatok (1/28/2009)
Argument data type float is invalid for argument 1 of DecryptByKey function.
Indeed it is. Decrypt takes a varbinary parameter, as all encrypted data is varbinary. If it's float, it can't be encrypted.
What's the definition of the ALLSTAFF table?
Hang on, I just noticed what you said in your first post.
I am trying to insert unencryted data into a decrypted field
Is the data in the ALLSTAFF table encrypted or not? If not, why are you trying to decrypt it at all?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2009 at 6:43 am
On a monthly basis I import data from another department and bring it to the data mart. So it is then required of me to make sure that certain fields in the table are encrypted. e.g Salaries, Identity numbers etc.
Now the tables on my side are already encrypted. The process I used last month was rather messy.
I decrypted the data into a new table then continued to add new data and then encrypt it again afterwards. The number of tables with fields that need to be encrypted is increasing so i'm trying to automate the process
January 28, 2009 at 7:23 am
thatok (1/28/2009)
Now the tables on my side are already encrypted. The process I used last month was rather messy.
How are they encrypted? To use DecryptByKey, the data must have been encrypted using EncryptByKey, with the same symmetric key, resulting in a varbinary value
What's the definition of the ALLSTAFF table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2009 at 7:47 am
Yes I used a symmetric key.
This is the allstaff table which is not encrypted.
CREATE TABLE ALLSTAFF(
Co float NULL,
CoName varchar(255) ,
Department varchar(255),
Dept varchar(255),
EmplID float NULL,
PayGroup varchar(255),
Grade varchar(255),
TotalPack Float NULL,
Age float NULL,
Initials varchar(255),
AnnualRate Float NULL,
Action varchar(255),
Prefix varchar(255),
CompRate float NULL,
AnnBenRt float NULL) ;
This is ALLSTAFF_dec which is encrypted
CREATE TABLE ALLSTAFF(
Co float NULL,
CoName varchar(255) ,
Department varchar(255),
Dept varchar(255),
EmplID float NULL,
PayGroup varchar(255),
Grade varchar(255),
TotalPack varbinary(128) NULL,
Age float NULL,
Initials varchar(255),
AnnualRate varbinary(128) NULL,
Action varchar(255),
Prefix varchar(255),
CompRate float NULL,
AnnBenRt float NULL) ;
So the ALLSTAFF_dec contains History data while ALLSTAFF is current, I would like to append data from ALLSTAFF to ALLSTAFF_dec .
January 28, 2009 at 7:53 am
If the ALLSTAFF is not encrypted and the ALLSTAFF_dec is encrypted, then you need to be using the EncryptByKey function when you insert from AllStaff to AllStaff_Dec. You're trying to encrypt data, not decrypt it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2009 at 4:32 am
Hi! Gail
Thanks a million. That's exactly what I needed.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply