Deduping to multiple outputs

I know that you can dedupe with the Sort transformation, but that seems to drop the dupes completely. Is there a way to dedupe and have the uniques go to one output, while the dupes go to another?One way that I've accomplished this is to Multicast the source. Feed one dataset to an Aggregate transform selecting count(*) and the duplicated fields. The output of the Aggregate is then fed into a Conditional Split with one criteria being count(*) == 1 and the other being count(*) > 1. You can then Merge Join the two outputs with another instance of the original multicast and continue on with your flow.
Its a round about way of getting there, but it works. It's also not very performant because each instance of the Multicast requires a memcopy for each row.
Larry

Dedub query

I am trying to join two table using a primary key, my problem is that one table has multiple listing of that primary key, I only want to join to the primary key once. Can anyone show me how this can be done?

Table1

acct_no sale_am tran_cd

123 50 2

123 54 1

113 20 9

124 30 7

Table2

acct_no exp_am res_am

123 50 20

113 24 30

124 60 10

What I need:

acct_no sum(sale_am) sum(exp_am) sum(res_am)

123 104 50 20

113 20 24 30

124 30 60 10

Thanks

There are several possibilities. And each one could provide different resultsets.

Please let us know what is your expected output and we can better assist you.

|||

Assuming you want the latest tran_cd value, you can just use a derived table and the ROW_NUMBER() windowed function:

select *

from (select acct_no, sale_am, row_number() over (partition by acct_no order by tran_cd desc) as rowNbr

from table1) as table1
join table2
on table1.acct_no = table2.acct_no

and table1.rowNbr = 1

If this is something that you do often, especially something that needs a lot of performance, I might consider implementing a current_row_flag in your table to denote the row you want to usually use (especially if those rows don't change much)

|||

Code Snippet

createtable #Table1( acct_no int, sale_am money, tran_cd int)

insertinto #Table1

select 123, 50, 2

union allselect 123, 54, 1

union allselect 113, 20, 9

union allselect 124, 30, 7

createtable #Table2( acct_no int, exp_am money, res_am money)

insertinto #Table2

select 123, 50, 20

union allselect 113, 24, 30

union allselect 124, 60, 10

select t1.acct_no,sum(t1.sale_am)as sale_am,

sum(t2.exp_am)as exp_am,sum(t2.res_am)as res_am

from #Table1 t1

innerjoin #Table2 t2

on t1.acct_no = t2.acct_no

groupby t1.acct_no

sql

decryptbykey multiple session issue

Hi

I'm having some issues using the decryptbykey method via multiple connections. When I run the below test script simultaneously on two machines the sum function is always less then the known amount (ie 14945490 and 36382777). Does anyone know of any locking method or alternative way to sum an encrypted column?

Thanks in advance

Waz

open symmetric key HR01 decryption by password='yes'
DECLARE @.Bonus decimal
DECLARE @.Salary decimal
DECLARE @.Errors int
DECLARE @.Success int
DECLARE @.LoopCount int
SET @.Errors = 0
SET @.Success = 0
SET @.LoopCount = 0

WHILE (@.LoopCount < 40)
BEGIN

SELECT
@.Bonus = SUM(convert(float,convert(varchar(80),decryptbykey(Bonus)))),
@.Salary = SUM(convert(float,convert(varchar(80),decryptbykey(Salary))))
FROM ChallengeEmployee
WHERE ChallengeID = 5

IF(@.Bonus <> 14945490 OR @.Salary <> 36382777)
BEGIN
PRINT 'Bonus ' + CAST(@.Bonus AS varchar(80))
PRINT 'Salary ' + CAST(@.Salary AS varchar(80))
SET @.Errors = @.Errors + 1
END
ELSE
SET @.Success = @.Success + 1

SET @.LoopCount = @.LoopCount + 1

END

PRINT 'Finish'
PRINT 'Errors ' + CAST(@.Errors AS varchar(80))
PRINT 'Success ' + CAST(@.Success AS varchar(80))
close symmetric key HR01

Unfortunately binary values cannot be converted/casted back to real/float data types. This limitation was also present in SQL Server 2000 (and from the information I could find, even in earlier versions). For detailed information on allowed cast/convert operations see http://msdn2.microsoft.com/en-us/library/ms187928.aspx.

I would recommend using a different data type if possible. Another workaround would be to cast to an intermediate data type (i.e. to a string), but this will cause data loss, and I would personally recommend against it because of the data loss potential. This seems to be the case in your particular scenario.

We really appreciate your feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul

Thanks for the reply.

I don't think it's a cast issue. I am actually casting to a string in my function. The problem seems to be with running over multiple connections. Running the test script on a single machine works fine. If I run the test scripts below the actual and binary values always return the correct values. Only the Encrypted ones fail when running from two separate machines. (all works fine on one connection). Maybe it's how I'm handling the keys. Could a close statement on a connection effect another connections read?

Cheers

--########################################### Setup Data ###############################################

open symmetric key HR01 decryption by password='yes'
DECLARE @.LoopCount int
DECLARE @.Bonus decimal
DECLARE @.Salary decimal
DECLARE @.EmployeeID int

CREATE TABLE TestData
(
empId int,
BonusActual decimal,
SalaryActual decimal,
BonusBinary varbinary(64),
SalaryBinary varbinary(64),
BonusEncrypt varbinary(64),
SalaryEncrypt varbinary(64)
)

SET @.EmployeeID = 1000
SET @.Bonus = 20000
SET @.Salary = 60000
SET @.LoopCount = 0
WHILE (@.LoopCount < 1000)
BEGIN
INSERT INTO TestData (empId, BonusActual, SalaryActual, BonusEncrypt,SalaryEncrypt)
VALUES (
@.EmployeeID,
@.Bonus,
@.Salary,
EncryptByKey(Key_GUID('HR01'), (CAST(@.Bonus AS varchar(80)))),
EncryptByKey(Key_GUID('HR01'), (CAST(@.Salary AS varchar(80))))
)

SET @.EmployeeID = @.EmployeeID + 1
SET @.Bonus = @.Bonus + 100
SET @.Salary = @.Salary + 500
SET @.LoopCount = @.LoopCount + 1
END

UPDATE TestData SET BonusBinary = decryptbykey(BonusEncrypt), SalaryBinary = decryptbykey(SalaryEncrypt)

close symmetric key HR01

--########################################### Verify Sums ###############################################

open symmetric key HR01 decryption by password='yes'
-- All figures should equal below (and do)
-- Bonus = 69950000
-- Salary = 309750000
SELECT
SUM(BonusActual) as 'BonusActualSum',
SUM(CAST((CAST(BonusBinary AS varchar(80)))AS decimal)) as 'BonusBinarySum',
SUM(CAST((CAST((decryptbykey(BonusEncrypt)) AS varchar(80)))AS decimal)) as 'BonusEncryptSum',
SUM(SalaryActual) as 'SalaryActualSum',
SUM(CAST((CAST(SalaryBinary AS varchar(80)))AS decimal)) as 'SalaryBinarySum',
SUM(CAST((CAST((decryptbykey(SalaryEncrypt)) AS varchar(80)))AS decimal)) as 'SalaryEncryptSum'
FROM TestData

close symmetric key HR01

--########################################### Run Tests ###############################################

open symmetric key HR01 decryption by password='yes'

DECLARE @.BonusActualSum decimal
DECLARE @.SalaryActualSum decimal
DECLARE @.BonusBinarySum decimal
DECLARE @.SalaryBinarySum decimal
DECLARE @.BonusEncryptSum decimal
DECLARE @.SalaryEncryptSum decimal
DECLARE @.ActualErrors int
DECLARE @.BinaryErrors int
DECLARE @.EncryptErrors int
DECLARE @.Success int
DECLARE @.LoopCount int
SET @.ActualErrors = 0
SET @.BinaryErrors = 0
SET @.EncryptErrors = 0
SET @.Success = 0
SET @.LoopCount = 0

WHILE (@.LoopCount < 40)
BEGIN
SELECT
@.BonusActualSum = SUM(BonusActual),
@.BonusBinarySum = SUM(CAST((CAST(BonusBinary AS varchar(80)))AS decimal)),
@.BonusEncryptSum = SUM(CAST((CAST((decryptbykey(BonusEncrypt)) AS varchar(80)))AS decimal)),
@.SalaryActualSum = SUM(SalaryActual),
@.SalaryBinarySum = SUM(CAST((CAST(SalaryBinary AS varchar(80)))AS decimal)),
@.SalaryEncryptSum = SUM(CAST((CAST((decryptbykey(SalaryEncrypt)) AS varchar(80)))AS decimal))
FROM TestData

IF(@.BonusActualSum <> 69950000 OR @.SalaryActualSum <> 309750000) SET @.ActualErrors = @.ActualErrors + 1
IF(@.BonusBinarySum <> 69950000 OR @.SalaryBinarySum <> 309750000) SET @.BinaryErrors = @.BinaryErrors + 1
IF(@.BonusEncryptSum <> 69950000 OR @.SalaryEncryptSum <> 309750000) SET @.EncryptErrors = @.EncryptErrors + 1

IF(@.BonusActualSum <> 69950000 OR @.BonusBinarySum <> 69950000 OR
@.BonusEncryptSum <> 69950000 OR @.SalaryActualSum <> 309750000 OR
@.SalaryBinarySum <> 309750000 OR @.SalaryEncryptSum <> 309750000)
BEGIN
PRINT '@.BonusActualSum ' + CAST(@.BonusActualSum AS varchar(80))
PRINT '@.BonusBinarySum ' + CAST(@.BonusBinarySum AS varchar(80))
PRINT '@.BonusEncryptSum ' + CAST(@.BonusEncryptSum AS varchar(80))
PRINT '@.SalaryActualSum ' + CAST(@.SalaryActualSum AS varchar(80))
PRINT '@.SalaryBinarySum ' + CAST(@.SalaryBinarySum AS varchar(80))
PRINT '@.SalaryEncryptSum ' + CAST(@.SalaryEncryptSum AS varchar(80))
END ELSE
SET @.Success = @.Success + 1

SET @.LoopCount = @.LoopCount + 1
END

PRINT 'Finish'
PRINT 'ActualErrors ' + CAST(@.ActualErrors AS varchar(80))
PRINT 'BinaryErrors ' + CAST(@.BinaryErrors AS varchar(80))
PRINT 'EncryptErrors ' + CAST(@.EncryptErrors AS varchar(80))
PRINT 'Success ' + CAST(@.Success AS varchar(80))

close symmetric key HR01

Results from one machine:

Finish
ActualErrors 0
BinaryErrors 0
EncryptErrors 0
Success 40

Results from two sessions of SQL Server Management Studio run simultaneously (just showing 1 of the 37):

Warning: Null value is eliminated by an aggregate or other SET operation.
@.BonusActualSum 69950000
@.BonusBinarySum 69950000
@.BonusEncryptSum 69404000
@.SalaryActualSum 309750000
@.SalaryBinarySum 309750000
@.SalaryEncryptSum 309750000

Finish
ActualErrors 0
BinaryErrors 0
EncryptErrors 37
Success 3

|||The strange thing is I cannot get this to fail on my local SQL Express. It's just failing on our development servers and production boxes. It also takes around 3 times as long to run on the high spec'd boxes. Could be an install issue....|||

Sounds unlikely it is related to the installation, but let’s not completely discard the possibility yet.I am suspecting it may be either a problem on how the key is being used or even a concurrency bug with the key ring in addition to the way the query is being cached/optimized by the server that we haven’t seen in our tests.

Let’s try to minimize the variables and see if we can get to the root problem. Can you try the following changes on your query and run them on the system that you know you can repro the problem?

--########################################### Run Tests ###############################################

open symmetric key HR01 decryption by password='yes'

-- if possible, separate the OPEN SYMMETRIC KEY from the rest of the batch

go

-- Key HR01 should be opened w/status = 1

if( (SELECT count(*) FROM sys.openkeys) = 0 )

PRINT 'Failed!!! no keys found in the key-ring'

ELSE

SELECT * FROM sys.openkeys

go

DECLARE @.BonusActualSum decimal

DECLARE @.SalaryActualSum decimal

DECLARE @.BonusBinarySum decimal

DECLARE @.SalaryBinarySum decimal

DECLARE @.BonusEncryptSum decimal

DECLARE @.SalaryEncryptSum decimal

DECLARE @.ActualErrors int

DECLARE @.BinaryErrors int

DECLARE @.EncryptErrors int

DECLARE @.Success int

DECLARE @.LoopCount int

SET @.ActualErrors = 0

SET @.BinaryErrors = 0

SET @.EncryptErrors = 0

SET @.Success = 0

SET @.LoopCount = 0

-- RG: Let's just make sure the decryptbykey values we are getting back are not null and seem like valid decimal values (hex)

SELECT decryptbykey(BonusEncrypt) as decrypted_bonus, decryptbykey(SalaryEncrypt) as decrypted_salary FROM TestData

WHILE (@.LoopCount < 40)

BEGIN

SELECT

@.BonusActualSum = SUM(BonusActual),

@.BonusBinarySum = SUM(CAST((CAST(BonusBinary AS varchar(80)))AS decimal)),

@.BonusEncryptSum = SUM(CAST((CAST((decryptbykey(BonusEncrypt)) AS varchar(80)))AS decimal)),

@.SalaryActualSum = SUM(SalaryActual),

@.SalaryBinarySum = SUM(CAST((CAST(SalaryBinary AS varchar(80)))AS decimal)),

@.SalaryEncryptSum = SUM(CAST((CAST((decryptbykey(SalaryEncrypt)) AS varchar(80)))AS decimal))

FROM TestData

-- Let's make sure no decryption call returned null

if( @.BonusEncryptSum is null OR @.SalaryEncryptSum is null )

BEGIN

PRINT 'FAILED!!! Some values are null'

SELECT @.BonusEncryptSum, @.SalaryEncryptSum, @.LoopCount

END

IF(@.BonusActualSum <> 69950000 OR @.SalaryActualSum <> 309750000) SET @.ActualErrors = @.ActualErrors + 1

IF(@.BonusBinarySum <> 69950000 OR @.SalaryBinarySum <> 309750000) SET @.BinaryErrors = @.BinaryErrors + 1

IF(@.BonusEncryptSum <> 69950000 OR @.SalaryEncryptSum <> 309750000) SET @.EncryptErrors = @.EncryptErrors + 1

IF(@.BonusActualSum <> 69950000 OR @.BonusBinarySum <> 69950000 OR

@.BonusEncryptSum <> 69950000 OR @.SalaryActualSum <> 309750000 OR

@.SalaryBinarySum <> 309750000 OR @.SalaryEncryptSum <> 309750000)

BEGIN

PRINT '@.BonusActualSum ' + CAST(@.BonusActualSum AS varchar(80))

PRINT '@.BonusBinarySum ' + CAST(@.BonusBinarySum AS varchar(80))

PRINT '@.BonusEncryptSum ' + CAST(@.BonusEncryptSum AS varchar(80))

PRINT '@.SalaryActualSum ' + CAST(@.SalaryActualSum AS varchar(80))

PRINT '@.SalaryBinarySum ' + CAST(@.SalaryBinarySum AS varchar(80))

PRINT '@.SalaryEncryptSum ' + CAST(@.SalaryEncryptSum AS varchar(80))

END ELSE

SET @.Success = @.Success + 1

SET @.LoopCount = @.LoopCount + 1

END

PRINT 'Finish'

PRINT 'ActualErrors ' + CAST(@.ActualErrors AS varchar(80))

PRINT 'BinaryErrors ' + CAST(@.BinaryErrors AS varchar(80))

PRINT 'EncryptErrors ' + CAST(@.EncryptErrors AS varchar(80))

PRINT 'Success ' + CAST(@.Success AS varchar(80))

close symmetric key HR01

Hopefully we will be able to see if the key-ring and/or decryptbykey values are returning unexpected results. For your own safety, make sure to not post any of the decrypted values from the SELECT statement, just glance through them to see if there is anything that doesn’t seem to be a valid value. You can manually verify them be casting them to a decimal in an ad-hoc query, example:

declare @.x varbinary(100)

set @.x = 0x12000001CBA70800 -- varbinary value copied from decrypted column

print cast(@.x as decimal)

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul

Thanks again for the reply. I amended my scripts (and removed excess variables) and I can still reproduce the error i.e. quite a few of the values returned from your select statement are null. I also showed the issue to the SQL Server team and they have now passed it onto Microsoft as they could not figure it out. I've included the latest script with your changes. I'm almost convinced that it's a install issue as I can run the tests without fail against my local SQL Express.

Thanks

--########################################### Description ###############################################

--The problem seems to be with running the decryptbykey function on the Company Servers
--Running the test script under the 'Run Tests' heading on a single connection will work fine
--most of the time (sometimes 1 or 2 will fail which is a concern). But running with
--the query in two windows of SQL Management Studio simultanteously will error on almost all results.
--The actual and binary values always return the correct values. But the Encrypted values will fail to sum correctly.
--This only seems to be a problem on the servers (Win2000 Server SP4 4GB RAM, 4 CPU)
--ie On local installs of SQL Express (Win XP SP2) we have increased the loop to 4000 and
--we do not have any issues and it runs 3 times as quick.

--########################################### Setup Data ###############################################

IF NOT EXISTS(SELECT * FROM sys.symmetric_keys WHERE [name] = 'TestKey')
BEGIN
CREATE SYMMETRIC KEY TestKey WITH algorithm=DES encryption BY password = 'yes'
END

open symmetric key TestKey decryption by password='yes'

DECLARE @.LoopCount int
DECLARE @.Bonus decimal
DECLARE @.EmployeeID int
CREATE TABLE TestData
(
empId int,
BonusActual decimal,
BonusBinary varbinary(64),
BonusEncrypt varbinary(64),
)

SET @.EmployeeID = 1000
SET @.Bonus = 20000
SET @.LoopCount = 0

WHILE (@.LoopCount < 1000)
BEGIN
INSERT INTO TestData (empId, BonusActual, BonusEncrypt)
VALUES (@.EmployeeID, @.Bonus, EncryptByKey(Key_GUID('TestKey'), (CAST(@.Bonus AS varchar(80)))))

SET @.EmployeeID = @.EmployeeID + 1
SET @.Bonus = @.Bonus + 100
SET @.LoopCount = @.LoopCount + 1
END

UPDATE TestData SET BonusBinary = decryptbykey(BonusEncrypt)

close symmetric key TestKey

--########################################### Verify Sums ###############################################

open symmetric key TestKey decryption by password='yes'

-- All figures should equal below
-- Bonus = 69950000
SELECT
SUM(BonusActual) as 'BonusActualSum',
SUM(CAST((CAST(BonusBinary AS varchar(80)))AS decimal)) as 'BonusBinarySum',
SUM(CAST((CAST((decryptbykey(BonusEncrypt)) AS varchar(80)))AS decimal)) as 'BonusEncryptSum'
FROM TestData

close symmetric key TestKey

--########################################### Run Tests ###############################################

open symmetric key TestKey decryption by password='yes'

go

-- Key HR01 should be opened w/status = 1

if( (SELECT count(*) FROM sys.openkeys) = 0 )
PRINT 'Failed!!! no keys found in the key-ring'
ELSE
SELECT * FROM sys.openkeys

go

DECLARE @.BonusActualSum decimal
DECLARE @.BonusBinarySum decimal
DECLARE @.BonusEncryptSum decimal
DECLARE @.ActualErrors int
DECLARE @.BinaryErrors int
DECLARE @.EncryptErrors int
DECLARE @.Success int
DECLARE @.LoopCount int

SET @.ActualErrors = 0
SET @.BinaryErrors = 0
SET @.EncryptErrors = 0
SET @.Success = 0
SET @.LoopCount = 0

-- RG: Let's just make sure the decryptbykey values we are getting
--back are not null and seem like valid decimal values (hex)

SELECT decryptbykey(BonusEncrypt) as decrypted_bonus
FROM TestData

WHILE (@.LoopCount < 40)
BEGIN
SELECT
@.BonusActualSum = SUM(BonusActual),
@.BonusBinarySum = SUM(CAST((CAST(BonusBinary AS varchar(80)))AS decimal)),
@.BonusEncryptSum = SUM(CAST((CAST((decryptbykey(BonusEncrypt)) AS varchar(80)))AS decimal))
FROM TestData

-- Let's make sure no decryption call returned null
if(@.BonusEncryptSum is null)
BEGIN
PRINT 'FAILED!!! Some values are null'
SELECT @.BonusEncryptSum, @.LoopCount
END

IF(@.BonusActualSum <> 69950000) SET @.ActualErrors = @.ActualErrors + 1
IF(@.BonusBinarySum <> 69950000) SET @.BinaryErrors = @.BinaryErrors + 1
IF(@.BonusEncryptSum <> 69950000) SET @.EncryptErrors = @.EncryptErrors + 1

IF(@.BonusActualSum <> 69950000 OR
@.BonusBinarySum <> 69950000 OR
@.BonusEncryptSum <> 69950000)
BEGIN
PRINT '@.BonusActualSum ' + CAST(@.BonusActualSum AS varchar(80))
PRINT '@.BonusBinarySum ' + CAST(@.BonusBinarySum AS varchar(80))
PRINT '@.BonusEncryptSum ' + CAST(@.BonusEncryptSum AS varchar(80))
END
ELSE
SET @.Success = @.Success + 1
SET @.LoopCount = @.LoopCount + 1
SET @.BonusEncryptSum = null
END

PRINT 'Finish'
PRINT 'ActualErrors ' + CAST(@.ActualErrors AS varchar(80))
PRINT 'BinaryErrors ' + CAST(@.BinaryErrors AS varchar(80))
PRINT 'EncryptErrors ' + CAST(@.EncryptErrors AS varchar(80))
PRINT 'Success ' + CAST(@.Success AS varchar(80))

close symmetric key TestKey

|||

This is really strange. Just to make sure that it is decryptbykey usage the one causing problems, can you try this change in the script on the real servers:

SELECT BonusEncrypt , decryptbykey(BonusEncrypt) as decrypted_bonus
FROM TestData

If it is decryptbykey is the one failing, we should see a valid encrypted value (the encrypted value should start with the same sequence, the key GUID on all rows and be of the same length) in the first column and null in the second one. If the first column shows any null then it also fails during the encryptbykey function.

After this test, can you try to change the script to add encryption by a certificate to TestKey and use decryptByKeyAutoCert? The way the decryptByKeyAutoCert handles the key ring may help on this particular case as a workaround; as I have never seen this behavior before, I am not sure if it will really work but it may be worth to give it a try:

OPEN SYMMETRIC KEY TestKey DECRYPTION BY PASSWORD = 'yes'

ALTER SYMMETRIC KEY TestKey ADD ENCRYPTION BY CERTIFICATE TestCert

CLOSE SYMMETRIC KEY TestKey

go

And on the test script, remove the OPEN SYMMETRIC KEY statement and use decryptbykeyautocert( cert_id('TestCert'), N'yes', BonusEncrypt) instead of the regular decryptbykey function.

I will also need to investigate more on this one, but any additional information I can get will help. I will also appreciate if you can test an algorithm different than DES. For testing purposes only, can you also give it a try with RC4 and RC2 algorithms?

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

I ran the select with the extra column and all of the BonusEncrypt values were returned correctly. Beside them were quite a few nulls in the decrypted_bonus column.

The interesting thing was changing the encryption method to RC4 or RC2 fixed the problem. Using the certificate didn't seem to make a difference.

Thanks
Warren

|||

Based on your observations I am suspecting the problem may be related to how Windows 2000 CAPI works with DES keys (to be more specific, the parity bits). Can you please try the following two tests?

* First, try using TRIPLE_DES instead of DES, I would expect a similar behavior.

* Save some of the rows that return null for the decrypted value, close and reopen the symmetric key and then copy the encrypted value and run the following script:

declare @.x varbinary(1000)

set @.x = -- Copy the encrypted value here

select datalength(@.x), decryptbykey( @.x )

go

Run this select statement a few times (reopen the key), it should always fail the same way (return null), the first column (datalength) is only to see if the encrypted data length seems correct (should always be the same). I would also expect that the select statement above works consistently with the rows that returned as not-null.

In the meantime I will continue investigating on my own environment. Thanks a lot.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul

I tried the TRIPLE_DES and it works fine. Just to make sure I repeated the test several times with both encryption methods and DES always failed and TRIPLE_DES never fails.

Also I couldn't get the script you sent to fail. So it only seems to be when reading from a table. Just to make sure I ran this script simultaneously in two windows without any errors:

OPEN SYMMETRIC KEY TestKey decryption by password='yes'

DECLARE @.LoopCount int
DECLARE @.x varbinary(1000)
DECLARE @.y varbinary(1000)
DECLARE @.z varchar(20)
SET @.LoopCount = 0
WHILE (@.LoopCount < 100000)
BEGIN
SET @.x = 0x0070D1427D69FA4BBC7EE4C3A031DCFE01000000C9E733BEA6BD461A28B9F8522280F0644CB68BE940D5E8AE
SET @.LoopCount = @.LoopCount + 1
SET @.y = decryptbykey( @.x )
SET @.z = CAST(@.y AS varchar(20))
IF @.y IS NULL OR @.z <> '20400' PRINT CAST(datalength(@.x) AS varchar(20)) + ' ' + @.z
SET @.y = NULL
SET @.z = NULL
END

CLOSE SYMMETRIC KEY TestKey

|||

I am working on this case with the premier support group, we will continue via premier support.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

As I mentioned on my last post, the premier support engineers are working on this case with the customer, but they already figured out that the root of the problem is a bug in Windows 2000 CryptoAPI that only happens when using DES keys.

To prevent anyone else to hit the same problem, I decided to update this thread. We strongly suggest updating to Windows 2003 server products, or if you still need to continue using Windows 2000 use a different algorithm, we strongly recommend using TRIPLE_DES keys when using Windows 2000.

Thanks a lot, and kudos to the Premier Support team for their great work and help on this case!

-Raul Garcia
SDE/T
SQL Server Engine

Decoding Days Bitwise AND

Hello All

I'm working on a recurring multi-day appointment program. Basically the user can choose a meeting on multiple days of the week over a span of time. For example: Tuesday and Thursday from 10:00 to 10:30 from December 1st 2004 to February 27th 2005.

So I've decided the best way to handle this is to assign a value to each day of the week like so:
MON = 1
TUE = 2
WED = 4
THU = 8
FRI = 16
SAT = 32
SUN = 64

So if the user picks TUE and THU that would be 2 + 8 = 10. The value is unique and seems to work.

So the values would be:
@.begin_date = '12/01/2004'
@.begin_time = '10:00 AM'
@.end_date = '02/27/2005'
@.end_time = '10:30 AM'
@.recur_days = 10

Now I want to pass the values to stored procedure that will decode the recur_days variable and create entries in a table for each date. I'm struggling to figure out 2 things

1. How do I decode the 10 back into 2(TUE) + 8(THU) ( I think it has something to do with the bitwise AND "&" operator but I'm not sure how to use it.)

2. What is the best way to loop through the date range and create a record for each day?

Regards
RussI would probably create another table that decodes the possible values that you would come up with, for example

Create table decode (
TtlValue int,
PtValue int
)

Then have a row for each separate value like for 10
it would be

insert decode(TtlValue, PtValue)
values(10, 8)
insert decode(TtlValue, PtValue)
values(10, 2)

after you did that your proc could just "walk the table" looking for values
that equalled your sum value.

Example:
declare @.ttlvalue int,
@.x int

select @.ttlvalue = the value of your total sums

select @.x = min(PtValue)
from decode
where TtlValue = @.ttlvalue

while @.x is not null
BEGIN
do whatever you need in here then when you're finished, move to the next row

select @.x = min(PtValue)
from decode
where TtlValue = @.ttlvalue
and PtValue > @.x
END

hope that might help?

Nick|||Use of bitwise AND operator:

Declare @.TestDate int
Declare @.TestBitwise int
set @.TestDate = 2 --Tuesday
set @.TestBitwise = 10 --Tuesday and Thursday = 2 + 8

--Check for Tuesday:
if @.TestDate & @.TestBitwise = @.TestDate
select 'Yes, Tuesday'
else select 'No, not Tuesday'

set @.TestBitwise = 9 --Monday and Thursday = 1 + 8
--Check for Tuesday:
if @.TestDate & @.TestBitwise = @.TestDate
select 'Yes, Tuesday'
else select 'No, not Tuesday'

Declaring and using an UPDATE CURSOR with SQL SERVER

In databases like Oracle or Sybase, if you have multiple records returned
from a query and want to update them, you declare an update cursor.
While I have read the "Help" files available in Enterprise, I have not
figured out the syntax for declaring and using an UPDATE CURSOR.
The query I am running is like this:
UPDATE A
SET A.Field1 = (SELECT B.Field1
FROM B INNER JOIN A ON A.id = B.id)
The tables have a many to one relationship on id.
I do not know if when I declare the cursor, if I put the whole update
statement in it... I do not know if when I use an update cursor, I have to
fetch next... And beyond that, if I do get the syntax and put it into a
stored procedure, how do I execute the query from within Enterprise Manager
(I am not writing code to call the procedure, I just want to execute it
against the table. I would execute it by just creating a query, but the
cursor format is not recognized in the query pane.)
I am completely new to SQL Server but not to databases.
What I am looking for is just a simple example of declaring and using an
UPDATE CURSOR.
Any help you can provide would be appreciated. Thanks!Hi
you can use somewhat this
Update
Set A.Field1 = B.Field
From
Where A.id = B.i
This statement this convert to UPDATE CURSOR in Oracle
Hermilson Tinoco.|||The simple syntax for declaring a cursor which allows updates is doc'd in
SQL books online , search for "Declare cursor", but it looks like
declare mycur Cursor for <Select statement> for update
you can then open it, fetch rows, and update table set col = value where
current of mycur
Generally in SQL, we try to avoid cursors and use relational update
statements whenever possible, because cursors (generally) do not perform as
well.
hope this helps.
"Carol Berry" <carol@.123marbella.com> wrote in message
news:OAGSMlx6DHA.2404@.TK2MSFTNGP11.phx.gbl...
> In databases like Oracle or Sybase, if you have multiple records returned
> from a query and want to update them, you declare an update cursor.
> While I have read the "Help" files available in Enterprise, I have not
> figured out the syntax for declaring and using an UPDATE CURSOR.
> The query I am running is like this:
> UPDATE A
> SET A.Field1 => (SELECT B.Field1
> FROM B INNER JOIN A ON A.id = B.id)
> The tables have a many to one relationship on id.
> I do not know if when I declare the cursor, if I put the whole update
> statement in it... I do not know if when I use an update cursor, I have
to
> fetch next... And beyond that, if I do get the syntax and put it into a
> stored procedure, how do I execute the query from within Enterprise
Manager
> (I am not writing code to call the procedure, I just want to execute it
> against the table. I would execute it by just creating a query, but the
> cursor format is not recognized in the query pane.)
> I am completely new to SQL Server but not to databases.
> What I am looking for is just a simple example of declaring and using an
> UPDATE CURSOR.
> Any help you can provide would be appreciated. Thanks!
>

Declaring and using an UPDATE CURSOR with SQL SERVER

In databases like Oracle or Sybase, if you have multiple records returned
from a query and want to update them, you declare an update cursor.
While I have read the "Help" files available in Enterprise, I have not
figured out the syntax for declaring and using an UPDATE CURSOR.
The query I am running is like this:
UPDATE A
SET A.Field1 =
(SELECT B.Field1
FROM B INNER JOIN A ON A.id = B.id)
The tables have a many to one relationship on id.
I do not know if when I declare the cursor, if I put the whole update
statement in it... I do not know if when I use an update cursor, I have to
fetch next... And beyond that, if I do get the syntax and put it into a
stored procedure, how do I execute the query from within Enterprise Manager
(I am not writing code to call the procedure, I just want to execute it
against the table. I would execute it by just creating a query, but the
cursor format is not recognized in the query pane.)
I am completely new to SQL Server but not to databases.
What I am looking for is just a simple example of declaring and using an
UPDATE CURSOR.
Any help you can provide would be appreciated. Thanks!Hi.
you can use somewhat this:
Update A
Set A.Field1 = B.Field1
From B
Where A.id = B.id
This statement this convert to UPDATE CURSOR in Oracle.
Hermilson Tinoco.|||The simple syntax for declaring a cursor which allows updates is doc'd in
SQL books online , search for "Declare cursor", but it looks like
declare mycur Cursor for <Select statement> for update
you can then open it, fetch rows, and update table set col = value where
current of mycur
Generally in SQL, we try to avoid cursors and use relational update
statements whenever possible, because cursors (generally) do not perform as
well.
hope this helps.
"Carol Berry" <carol@.123marbella.com> wrote in message
news:OAGSMlx6DHA.2404@.TK2MSFTNGP11.phx.gbl...

quote:

---

> In databases like Oracle or Sybase, if you have multiple records returned
> from a query and want to update them, you declare an update cursor.
> While I have read the "Help" files available in Enterprise, I have not
> figured out the syntax for declaring and using an UPDATE CURSOR.
> The query I am running is like this:
> UPDATE A
> SET A.Field1 =
> (SELECT B.Field1
> FROM B INNER JOIN A ON A.id = B.id)
> The tables have a many to one relationship on id.
> I do not know if when I declare the cursor, if I put the whole update
> statement in it... I do not know if when I use an update cursor, I have

---

to

quote:

---

> fetch next... And beyond that, if I do get the syntax and put it into a
> stored procedure, how do I execute the query from within Enterprise

---

Manager

quote:

---

> (I am not writing code to call the procedure, I just want to execute it
> against the table. I would execute it by just creating a query, but the
> cursor format is not recognized in the query pane.)
> I am completely new to SQL Server but not to databases.
> What I am looking for is just a simple example of declaring and using an
> UPDATE CURSOR.
> Any help you can provide would be appreciated. Thanks!
>

---

|||Thank you all for your help. If I need more help, I will post a more
complete problem. I am new to "posting" issues, too.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

debugging symbols for the SQLCE dlls while developing native applications

Hi,

I am developing a native C++ application using SQLCE (.NET is not a current option). Am having a problem while using multiple accessors for multiple blobs in a table. After I read the data for the first blob & try to read the data for the second one, I get an error in one of the dlls (something about using heap data that was freed). Where can I get symbol data so that I can use something like Windbg to resolve my problem.

It appears debug symbols are not available, see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1312588&SiteID=1

Debug stored procedure that uses comma delimited list to insert multiple records

I need some help with a stored procedure to insert multiple rows into a join table from a checkboxlist on a form. The database structure has 3 tables - Products, Files, and ProductFiles(join). From a asp.net formview users are able to upload files to the server. The formview has a products checkboxlist where the user selects all products a file they are uploading applies too. I parse the selected values of the checkboxlist into a comma delimited list that is then passed with other parameters to the stored proc. If only one value is selected in the checkboxlist then the spproc executed correctly. Also, if i run sql profiler i can confirm that the that asp.net is passing the correct information to the sproc:

exec proc_Add_Product_Files @.FileName = N'This is just a test.doc', @.FileDescription = N'test', @.FileSize = 24064, @.LanguageID = NULL, @.DocumentCategoryID = 1, @.ComplianceID = NULL, @.SubmittedBy = N'Kevin McPhail', @.SubmittedDate = 'Jan 18 2006 12:00:00:000AM', @.ProductID = N'10,11,8'

Here is the stored proc it is based on an article posted in another newsgroup on handling lists in a stored proc. Obviously there was something in the article i did not understand correctly or the author left something out that most people probably already know (I am fairly new to stored procs)

CREATE PROCEDURE proc_Add_Product_Files_v2
/*
Declare variables for the stored procedure. ProductID is a varchar because it will receive a comma,delimited list of values from the webform and then insert a row
into productfiles for each product that the file being uploaded pertains to.
*/
@.FileName varchar(150),
@.FileDescription varchar(150),
@.FileSize int,
@.LanguageID int,
@.DocumentCategoryID int,
@.ComplianceID int,
@.SubmittedBy varchar(50),
@.SubmittedDate datetime,
@.ProductID varchar(150)

AS
BEGIN

DECLARE @.FileID INT

SET NOCOUNT ON

/*
Insert into the files table and retrieve the primary key of the new record using @.@.identity
*/
INSERT INTO Files (FileName, FileDescription, FileSize, LanguageID, DocumentCategoryID, ComplianceID, SubmittedBy, SubmittedDate)
Values
(@.FileName, @.FileDescription, @.FileSize, @.LanguageID, @.DocumentCategoryID, @.ComplianceID, @.SubmittedBy, @.SubmittedDate)

Select @.FileID=@.@.Identity

/*
Uses dynamic sql to insert the comma delimited list of productids into the productfiles table.
*/
DECLARE @.ProductFilesInsert varchar(2000)

SET @.ProductFilesInsert = 'INSERT INTO ProductFiles (FileID, ProductID) SELECT ' + CONVERT(varchar,@.FileID) + ', Product1ID FROM Products WHERE Product1ID IN (' + @.ProductID + ')'

exec(@.ProductFilesInsert)

End
GO

I created your stored procedure locally, and did a PRINT of @.ProductFilesInsert and all looks good to me. Setting @.FileID = 0 instead of selecting its value to be @.@.Identity, this is what @.ProductFilesInsert contains, and that is syntactically correct:

INSERT INTO ProductFiles (FileID, ProductID) SELECT 0, Product1ID FROM Products WHERE Product1ID IN (10,11,8)

Your stored procedure is named proc_Add_Product_Files_v2, yet you are executing proc_Add_Product_Files. Is the problem simply that your are executing an old version of your stored procedure?|||

Terri:

Thanks! Sometimes it is so obvious. I am a little embarrassed that i did not catch that. :)

Thanks again,

Kevin

|||

Kevin.McPhail wrote:

Thanks! Sometimes it is so obvious. I am a little embarrassed that i did not catch that. :)

It wasn't obvious to me. The only reason I noticed was that exec proc_Add_Product_Files failed failed for me because I didn't have the original in place :-) I can't tell you how many times I've been burned by the very same thing.

For what it's worth, I am not a big fan of dynamic SQL, especially when an alternate methodology is possible. You could use this approach instead:

INSERT INTO
ProductFiles
(
FileID,
ProductID
)
SELECT
@.FileID,
Product1ID
FROM
Products
INNER JOIN
dbo.Split(@.ProductID,',') AS A ON Products.Product1ID = A.Element

There are many variations of a "split" function. Here's one that Dinakar provided in this thread:http://forums.asp.net/989365/ShowPost.aspx:

CREATE FUNCTION [dbo].[Split] ( @.vcDelimitedString nVarChar(4000),
@.vcDelimiter nVarChar(100) )
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
delimiter points. Returns the individual items as a table data
type with the ElementID field as the array index and the Element
field as the data
PARAMETERS:
@.vcDelimitedString - The string to be split
@.vcDelimiter - String containing the delimiter where
delimited string should be split
RETURNS:
Table data type containing array of strings that were split with
the delimiters removed from the source string
USAGE:
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID
AUTHOR: Karen Gayda
DATE: 05/31/2001
MODIFICATION HISTORY:
WHO DATE DESCRIPTION
-- ---- ----------------
***************************************************************************/
RETURNS @.tblArray TABLE
(
ElementID smallint IDENTITY(1,1) not null primary key, --Array index
Element nVarChar(1200) null --Array element contents
)
AS
BEGIN
DECLARE
@.siIndex smallint,
@.siStart smallint,
@.siDelSize smallint
SET @.siDelSize = LEN(@.vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@.vcDelimitedString) > 0
BEGIN
SET @.siIndex = CHARINDEX(@.vcDelimiter, @.vcDelimitedString)
IF @.siIndex = 0
BEGIN
INSERT INTO @.tblArray (Element) VALUES(@.vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @.tblArray (Element) VALUES(SUBSTRING(@.vcDelimitedString, 1,@.siIndex - 1))
SET @.siStart = @.siIndex + @.siDelSize
SET @.vcDelimitedString = SUBSTRING(@.vcDelimitedString, @.siStart , LEN(@.vcDelimitedString) - @.siStart + 1)
END
END

RETURN
END|||

Thanks again Terri! I had been looking for a good understandable (not a sql guru) way to pass a delimited string or array to sql for inserts. I read through a couple articles i found that left my head spinning and decided to go with the old dynamic sql method since i at least understood what it did. Your example(and Dinakar and Karen's ) is exactly what i had been looking for.

Thanks,

Kevin