Inserting Encrypted Files

  • 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),

    @CopyOfLicensevarbinary(max),

    @CopyOfW9varbinary(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

  • 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])

    SELECTISNULL(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])

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply