Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Inserting Encrypted Files Expand / Collapse
Author
Message
Posted Thursday, November 13, 2008 7:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 03, 2009 10:22 AM
Points: 26, Visits: 3,729
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

Post #602116
Posted Thursday, November 13, 2008 9:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 03, 2009 10:22 AM
Points: 26, Visits: 3,729
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])


Post #602219
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse