SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Inserting Encrypted Files


Inserting Encrypted Files

Author
Message
a-848662
a-848662
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 3729
I have a table in which I need to insert multiple fields, including two documents of any size that will be encrypted in sql server 2005. I have come to a solution that requires two temporary tables and the main table. My problem is is that when I combine the two temporary tables into the main table, the row are either duplicated or disjointed. I have the same fields in the temp tables as I do the main table, only difference is is that each temp table contains one document each. Is there a better way??? Thanks to those who respond. See the procedure below


CREATE PROCEDURE AddAffiliate
-- Add the parameters for the stored procedure here
@FullLegalName nvarchar(150),
@TypeOfEntity nvarchar(150),
@LicenseNumber nvarchar(50),
@StateLicensed nvarchar(50),
@Website nvarchar(200),
@TaxID nvarchar(50),
@Address1 nvarchar(50),
@Address2 nvarchar(50),
@City nvarchar(50),
@State nvarchar(25),
@ZipCode nvarchar(10),
@ContactPerson nvarchar(50),
@EMail nvarchar(150),
@PhoneNumber nvarchar(15),
@FaxNumber nvarchar(15),
@CopyOfLicense varbinary(max),
@CopyOfW9 varbinary(max),
@LoginID nvarchar(50),
@Password nvarchar(50)
AS
BEGIN

declare @idx int
declare @licLength int
declare @w9Length int
declare @blockSize int
declare @blockNum int
declare @block varbinary(7500)
declare @keyGuid uniqueidentifier

OPEN SYMMETRIC KEY AKey
DECRYPTION BY CERTIFICATE HappyCert WITH PASSWORD = '#####';

set @keyGuid = Key_GUID('AKey')
set @blockSize = 7600

BEGIN
CREATE TABLE #EncyptedLicenseData
(
FullLegalName nvarchar(150),
TypeOfEntity nvarchar(150),
LicenseNumber nvarchar(150),
StateLicensed nvarchar(150),
Website nvarchar(200),
TaxID nvarchar(150),
Address1 nvarchar(150),
Address2 nvarchar(150),
City nvarchar(150),
State nvarchar(25),
ZipCode nvarchar(10),
ContactPerson nvarchar(150),
EMail nvarchar(150),
PhoneNumber nvarchar(15),
FaxNumber nvarchar(15),
CopyOfLicense varbinary(max),
LicenseBlockNum int,
CopyOfW9 varbinary(max),
W9BlockNum int,
LoginID nvarchar(50),
Password nvarchar(50)
);
set @blockNum = 1
set @idx = 1
set @licLength = datalength(@CopyOfLicense)

WHILE @idx < @licLength
BEGIN
set @block = EncryptByKey(@keyGuid, SUBSTRING(@CopyOfLicense, @idx, @blockSize))
insert into #EncyptedLicenseData ([FullLegalName], [TypeOfEntity], [LicenseNumber], [StateLicensed],
[Website], [TaxID], [Address1], [Address2], [City], [State], [ZipCode],
[ContactPerson], [EMail], [PhoneNumber], [FaxNumber], [CopyOfLicense],
[LicenseBlockNum], [CopyOfW9], [W9BlockNum], [LoginID], [Password])
values( @FullLegalName, @TypeOfEntity, @LicenseNumber, @StateLicensed,
@Website, @TaxID, @Address1, @Address2, @City, @State, @ZipCode,
@ContactPerson, @EMail, @PhoneNumber, @FaxNumber,
@block, @blockNum,
NULL, NULL,
@LoginID, @Password);
set @idx = @idx + @blockSize
set @blockNum = @blockNum + 1
END
END

BEGIN
CREATE TABLE #EncryptedW9Data
(
FullLegalName nvarchar(150),
TypeOfEntity nvarchar(150),
LicenseNumber nvarchar(150),
StateLicensed nvarchar(150),
Website nvarchar(200),
TaxID nvarchar(150),
Address1 nvarchar(150),
Address2 nvarchar(150),
City nvarchar(150),
State nvarchar(25),
ZipCode nvarchar(10),
ContactPerson nvarchar(150),
EMail nvarchar(150),
PhoneNumber nvarchar(15),
FaxNumber nvarchar(15),
CopyOfLicense varbinary(max),
LicenseBlockNum int,
CopyOfW9 varbinary(max),
W9BlockNum int,
LoginID nvarchar(50),
Password nvarchar(50)
);
set @blockNum = 1
set @idx = 1
set @w9Length = datalength(@CopyOfW9)

WHILE @idx < @w9Length
BEGIN
set @block = EncryptByKey(@keyGuid, SUBSTRING(@CopyOfW9, @idx, @blockSize))
insert into #EncryptedW9Data ([FullLegalName], [TypeOfEntity], [LicenseNumber], [StateLicensed],
[Website], [TaxID], [Address1], [Address2], [City], [State], [ZipCode],
[ContactPerson], [EMail], [PhoneNumber], [FaxNumber], [CopyOfLicense],
[LicenseBlockNum], [CopyOfW9], [W9BlockNum], [LoginID], [Password])
values( @FullLegalName, @TypeOfEntity, @LicenseNumber, @StateLicensed,
@Website, @TaxID, @Address1, @Address2, @City, @State, @ZipCode,
@ContactPerson, @EMail, @PhoneNumber, @FaxNumber,
NULL, NULL,
@block, @blockNum,
@LoginID, @Password);
set @idx = @idx + @blockSize
set @blockNum = @blockNum + 1
END
END

BEGIN
SET NOCOUNT ON

INSERT INTO dbo.Affiliate([FullLegalName], [TypeOfEntity], [LicenseNumber], [StateLicensed],
[Website], [TaxID], [Address1], [Address2], [City], [State], [ZipCode],
[ContactPerson], [EMail], [PhoneNumber], [FaxNumber], [CopyOfLicense],
[LicenseBlockNum], [CopyOfW9], [W9BlockNum], [LoginID], [Password])
SELECT [#EncyptedLicenseData].[FullLegalName],
[#EncyptedLicenseData].[TypeOfEntity],
[#EncyptedLicenseData].[LicenseNumber],
[#EncyptedLicenseData].[StateLicensed],
[#EncyptedLicenseData].[Website],
[#EncyptedLicenseData].[TaxID],
[#EncyptedLicenseData].[Address1],
[#EncyptedLicenseData].[Address2],
[#EncyptedLicenseData].[City],
[#EncyptedLicenseData].[State],
[#EncyptedLicenseData].[ZipCode],
[#EncyptedLicenseData].[ContactPerson],
[#EncyptedLicenseData].[EMail],
[#EncyptedLicenseData].[PhoneNumber],
[#EncyptedLicenseData].[FaxNumber],
ISNULL([#EncyptedLicenseData].[CopyOfLicense],[#EncryptedW9Data].[CopyOfLicense]) ,
ISNULL([#EncyptedLicenseData].[LicenseBlockNum],[#EncryptedW9Data].[LicenseBlockNum]),
ISNULL([#EncyptedLicenseData].[CopyOfW9],[#EncryptedW9Data].[CopyOfW9]),
ISNULL([#EncyptedLicenseData].[W9BlockNum],[#EncryptedW9Data].[W9BlockNum]),
[#EncyptedLicenseData].[LoginID],
[#EncyptedLicenseData].[Password]
FROM [#EncyptedLicenseData]
INNER JOIN [#EncryptedW9Data] ON
[#EncyptedLicenseData].[FullLegalName] = [#EncryptedW9Data].[FullLegalName]
END

CLOSE SYMMETRIC KEY AKey;

END


a-848662
a-848662
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 3729
SOLVED!!!!



INSERT INTO dbo.Affiliate([FullLegalName], [TypeOfEntity], [LicenseNumber], [StateLicensed],
[Website], [TaxID], [Address1], [Address2], [City], [State], [ZipCode],
[ContactPerson], [EMail], [PhoneNumber], [FaxNumber], [CopyOfLicense],
[LicenseBlockNum], [CopyOfW9], [W9BlockNum], [LoginID], [Password])
SELECT ISNULL(ELD.[FullLegalName], EWD.[FullLegalName]),
ISNULL(ELD.[TypeOfEntity], EWD.[TypeOfEntity]),
ISNULL(ELD.[LicenseNumber], EWD.[LicenseNumber]),
ISNULL(ELD.[StateLicensed],EWD.[StateLicensed]),
ISNULL(ELD.[Website], EWD.[Website]),
ISNULL(ELD.[TaxID], EWD.[TaxID]),
ISNULL(ELD.[Address1], EWD.[Address1]),
ISNULL(ELD.[Address2], EWD.[Address2]),
ISNULL(ELD.[City], EWD.[City]),
ISNULL(ELD.[State], EWD.[State]),
ISNULL(ELD.[ZipCode],EWD.[ZipCode]),
ISNULL(ELD.[ContactPerson], EWD.[ContactPerson]),
ISNULL(ELD.[EMail], EWD.[EMail]),
ISNULL(ELD.[PhoneNumber], EWD.[PhoneNumber]),
ISNULL(ELD.[FaxNumber], EWD.[FaxNumber]),
ELD.[CopyOfLicense],
ELD.[LicenseBlockNum],
EWD.[CopyOfW9],
EWD.[W9BlockNum],
ISNULL(ELD.[LoginID], EWD.[LoginID]),
ISNULL(ELD.[Password], EWD.[Password])
FROM [#EncyptedLicenseData] ELD
FULL OUTER JOIN [#EncryptedW9Data] AS EWD ON
ELD.[FullLegalName] = EWD.[FullLegalName]
AND ISNULL(ELD.[LicenseBlockNum], EWD.[W9BlockNum]) = ISNULL(EWD.[W9BlockNum], ELD.[LicenseBlockNum])



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search