"Timeout Expired" on large table change

I have a table containing several hundred thousand rows. Once of the
fields in the table is named EODFeedDate. The field is nullable. I've
decided that I do NOT want that field to be nullable. I've unchecked the
"allow nulls" checkbox for the field in Management Studio. I then tried to
save my schema change. I'm getting the following error from Management
Studio:
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding."
I can't figure out what's causing the problem. It's worth nothing that:
- Of the current entries in this table, none of them have EODFeedDate
set to null so making the field non-nullable should cause a conflict.
- No one else is connected to the database. It's a private development
machine -- so it isn't a contention problem.
Any ideas?
David
> "Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding."
> I can't figure out what's causing the problem.
I did a google search on this. You have to write a query to workaround this
bug. I've never written query code to modify a table before; I've always
used the UI, so I had some learning to do.
I learned about the ALTER TABLE statement but my execution of that statement
failed. The error message stated that the column could not be made
non-nullable because there is an index on the table that made use of the
column in question. That's when I learned about the DROP INDEX statement. By
using a DROP INDEX in tandem with an ALTER TABLE, I was able to make the
field non-nullable. I then re-created the index using the UI.
I've never spent so much time trying to do something so simple.
David
|||You can have Management Studio generate the script by making the change,
then right clicking the window and selecting "Generate Change Script"
"The One We Call 'Dave'" <ghetto@.englewood.com> wrote in message
news:-MOdnXQku9doHYXZnZ2dnUVZ_t-dnZ2d@.giganews.com...
> I did a google search on this. You have to write a query to workaround
> this bug. I've never written query code to modify a table before; I've
> always used the UI, so I had some learning to do.
> I learned about the ALTER TABLE statement but my execution of that
> statement failed. The error message stated that the column could not be
> made non-nullable because there is an index on the table that made use of
> the column in question. That's when I learned about the DROP INDEX
> statement. By using a DROP INDEX in tandem with an ALTER TABLE, I was able
> to make the field non-nullable. I then re-created the index using the UI.
> I've never spent so much time trying to do something so simple.
> David
>
|||"Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
news:uIYUXTHSGHA.4456@.TK2MSFTNGP14.phx.gbl...
> You can have Management Studio generate the script by making the change,
> then right clicking the window and selecting "Generate Change Script"
Doh! I didn't realize that!! Thanks for the tip. I'll be sure to make use of
that in the future.

"Timeout Expired" on large table change

I have a table containing several hundred thousand rows. Once of the
fields in the table is named EODFeedDate. The field is nullable. I've
decided that I do NOT want that field to be nullable. I've unchecked the
"allow nulls" checkbox for the field in Management Studio. I then tried to
save my schema change. I'm getting the following error from Management
Studio:
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding."
I can't figure out what's causing the problem. It's worth nothing that:
- Of the current entries in this table, none of them have EODFeedDate
set to null so making the field non-nullable should cause a conflict.
- No one else is connected to the database. It's a private development
machine -- so it isn't a contention problem.
Any ideas?
David> "Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding."
> I can't figure out what's causing the problem.
I did a google search on this. You have to write a query to workaround this
bug. I've never written query code to modify a table before; I've always
used the UI, so I had some learning to do.
I learned about the ALTER TABLE statement but my execution of that statement
failed. The error message stated that the column could not be made
non-nullable because there is an index on the table that made use of the
column in question. That's when I learned about the DROP INDEX statement. By
using a DROP INDEX in tandem with an ALTER TABLE, I was able to make the
field non-nullable. I then re-created the index using the UI.
I've never spent so much time trying to do something so simple.
David|||You can have Management Studio generate the script by making the change,
then right clicking the window and selecting "Generate Change Script"
"The One We Call 'Dave'" <ghetto@.englewood.com> wrote in message
news:-MOdnXQku9doHYXZnZ2dnUVZ_t-dnZ2d@.giganews.com...
> I did a google search on this. You have to write a query to workaround
> this bug. I've never written query code to modify a table before; I've
> always used the UI, so I had some learning to do.
> I learned about the ALTER TABLE statement but my execution of that
> statement failed. The error message stated that the column could not be
> made non-nullable because there is an index on the table that made use of
> the column in question. That's when I learned about the DROP INDEX
> statement. By using a DROP INDEX in tandem with an ALTER TABLE, I was able
> to make the field non-nullable. I then re-created the index using the UI.
> I've never spent so much time trying to do something so simple.
> David
>|||"Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
news:uIYUXTHSGHA.4456@.TK2MSFTNGP14.phx.gbl...
> You can have Management Studio generate the script by making the change,
> then right clicking the window and selecting "Generate Change Script"
Doh! I didn't realize that!! Thanks for the tip. I'll be sure to make use of
that in the future.

"Timeout Expired" on large table change

I have a table containing several hundred thousand rows. Once of the
fields in the table is named EODFeedDate. The field is nullable. I've
decided that I do NOT want that field to be nullable. I've unchecked the
"allow nulls" checkbox for the field in Management Studio. I then tried to
save my schema change. I'm getting the following error from Management
Studio:
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding."
I can't figure out what's causing the problem. It's worth nothing that:
- Of the current entries in this table, none of them have EODFeedDate
set to null so making the field non-nullable should cause a conflict.
- No one else is connected to the database. It's a private development
machine -- so it isn't a contention problem.
Any ideas?
David> "Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding."
> I can't figure out what's causing the problem.
I did a google search on this. You have to write a query to workaround this
bug. I've never written query code to modify a table before; I've always
used the UI, so I had some learning to do.
I learned about the ALTER TABLE statement but my execution of that statement
failed. The error message stated that the column could not be made
non-nullable because there is an index on the table that made use of the
column in question. That's when I learned about the DROP INDEX statement. By
using a DROP INDEX in tandem with an ALTER TABLE, I was able to make the
field non-nullable. I then re-created the index using the UI.
I've never spent so much time trying to do something so simple. :(
David|||You can have Management Studio generate the script by making the change,
then right clicking the window and selecting "Generate Change Script"
"The One We Call 'Dave'" <ghetto@.englewood.com> wrote in message
news:-MOdnXQku9doHYXZnZ2dnUVZ_t-dnZ2d@.giganews.com...
>> "Timeout expired. The timeout period elapsed prior to completion of the
>> operation or the server is not responding."
>> I can't figure out what's causing the problem.
> I did a google search on this. You have to write a query to workaround
> this bug. I've never written query code to modify a table before; I've
> always used the UI, so I had some learning to do.
> I learned about the ALTER TABLE statement but my execution of that
> statement failed. The error message stated that the column could not be
> made non-nullable because there is an index on the table that made use of
> the column in question. That's when I learned about the DROP INDEX
> statement. By using a DROP INDEX in tandem with an ALTER TABLE, I was able
> to make the field non-nullable. I then re-created the index using the UI.
> I've never spent so much time trying to do something so simple. :(
> David
>|||"Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
news:uIYUXTHSGHA.4456@.TK2MSFTNGP14.phx.gbl...
> You can have Management Studio generate the script by making the change,
> then right clicking the window and selecting "Generate Change Script"
Doh! I didn't realize that!! Thanks for the tip. I'll be sure to make use of
that in the future.

"SUM" of a varchar column?

I have a table that contains a number of columns containing either a varchar
value, or null. There are multiple rows per grouping. I would like to colapse
the multiple rows into a single row, either by appending the strings to each
other, or simply selecting the first (or last) one.
If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work, for
obvious reasons. I thought MAX would work, but MAX returns only one value for
all of the columns (as opposed to one per column) and the rest are left as
null.
Any suggestions?
Here's an example...
The table contains this data, all items are varchar
32611317
3261Non-Client
32612
3261mmarkowitz
I'd like to turn this into...
3261 1317 Non-client 2 mmarkowitz
|||This looks like a PIVOT. Can you flatten this table out on the client? If
not, see http://www.aspfaq.com/2462
http://www.aspfaq.com/
(Reverse address to reply.)
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7838E3D4-CD8D-46EB-A75F-D069B30EE9AB@.microsoft.com...
> I have a table that contains a number of columns containing either a
varchar
> value, or null. There are multiple rows per grouping. I would like to
colapse
> the multiple rows into a single row, either by appending the strings to
each
> other, or simply selecting the first (or last) one.
> If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work,
for
> obvious reasons. I thought MAX would work, but MAX returns only one value
for
> all of the columns (as opposed to one per column) and the rest are left as
> null.
> Any suggestions?
|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:DD2FA7A5-A0F2-4BFF-85E9-42533F4E423C@.microsoft.com...
> Here's an example...
> The table contains this data, all items are varchar
>
> 3261 1317
> 3261 Non-Client
> 3261 2
> 3261 mmarkowitz
> I'd like to turn this into...
> 3261 1317 Non-client 2 mmarkowitz
I'm not sure if this will help, but you may want to take a look at the
GROUP BY WITH ROLLUP and WITH CUBE commands. It may work for what you are
after.
SELECT Col1, Max(Col2)
FROM tablename
GROUP BY Col1
WITH ROLLUP
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
FROM YourTable
GROUP BY col1
David Portas
SQL Server MVP
|||I don't think this will work, he wants to flatten one ofthe two columns in
his table out into multiple columns...
http://www.aspfaq.com/
(Reverse address to reply.)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103306604.369012.58920@.z14g2000cwz.googlegro ups.com...
> SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
> FROM YourTable
> GROUP BY col1
> --
> David Portas
> SQL Server MVP
> --
>
|||OK. Looks to me like 5 columns but I guess that's just presentational ;-)
Tough to pivot without an explicit attribute for the column.
David Portas
SQL Server MVP
|||"Aaron [SQL Server MVP]" wrote:

> This looks like a PIVOT.
Actually it is the RESULT of a pivot, which is why it is spread out
vertically like that.
But I did figure out a "trick". After reading the page you sent, I combined
their technique of ISNULL (instead of CASE) with MIN, and presto.
Thanks!
(anyone interested in the code?)

"SUM" of a varchar column?

I have a table that contains a number of columns containing either a varchar
value, or null. There are multiple rows per grouping. I would like to colapse
the multiple rows into a single row, either by appending the strings to each
other, or simply selecting the first (or last) one.
If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work, for
obvious reasons. I thought MAX would work, but MAX returns only one value for
all of the columns (as opposed to one per column) and the rest are left as
null.
Any suggestions?Here's an example...
The table contains this data, all items are varchar
3261 1317
3261 Non-Client
3261 2
3261 mmarkowitz
I'd like to turn this into...
3261 1317 Non-client 2 mmarkowitz|||This looks like a PIVOT. Can you flatten this table out on the client? If
not, see http://www.aspfaq.com/2462
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7838E3D4-CD8D-46EB-A75F-D069B30EE9AB@.microsoft.com...
> I have a table that contains a number of columns containing either a
varchar
> value, or null. There are multiple rows per grouping. I would like to
colapse
> the multiple rows into a single row, either by appending the strings to
each
> other, or simply selecting the first (or last) one.
> If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work,
for
> obvious reasons. I thought MAX would work, but MAX returns only one value
for
> all of the columns (as opposed to one per column) and the rest are left as
> null.
> Any suggestions?|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:DD2FA7A5-A0F2-4BFF-85E9-42533F4E423C@.microsoft.com...
> Here's an example...
> The table contains this data, all items are varchar
>
> 3261 1317
> 3261 Non-Client
> 3261 2
> 3261 mmarkowitz
> I'd like to turn this into...
> 3261 1317 Non-client 2 mmarkowitz
I'm not sure if this will help, but you may want to take a look at the
GROUP BY WITH ROLLUP and WITH CUBE commands. It may work for what you are
after.
SELECT Col1, Max(Col2)
FROM tablename
GROUP BY Col1
WITH ROLLUP
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
FROM YourTable
GROUP BY col1
--
David Portas
SQL Server MVP
--|||I don't think this will work, he wants to flatten one ofthe two columns in
his table out into multiple columns...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103306604.369012.58920@.z14g2000cwz.googlegroups.com...
> SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
> FROM YourTable
> GROUP BY col1
> --
> David Portas
> SQL Server MVP
> --
>|||OK. Looks to me like 5 columns but I guess that's just presentational ;-)
Tough to pivot without an explicit attribute for the column.
--
David Portas
SQL Server MVP
--|||"Aaron [SQL Server MVP]" wrote:
> This looks like a PIVOT.
Actually it is the RESULT of a pivot, which is why it is spread out
vertically like that.
But I did figure out a "trick". After reading the page you sent, I combined
their technique of ISNULL (instead of CASE) with MIN, and presto.
Thanks!
(anyone interested in the code?)

"select rows @start through @end" how to do it?

Thought I'd post the whole problem I'm trying to solve -- seems like it's
common enough that there must be a "normal" way to do this.
I want to say "select the top 2000 rows, starting at row 15000, from myTable
order by foo". I know I could use SELECT TOP 17000... to limit the number of
rows returned, but I'm still pulling a ton more data than I need.
So I thought I'd do it with a UDF, but I thought I'd see if there was a good
one already written for this.
Thanks!Jesse wrote:
> Thought I'd post the whole problem I'm trying to solve -- seems like
> it's common enough that there must be a "normal" way to do this.
> I want to say "select the top 2000 rows, starting at row 15000, from
> myTable order by foo". I know I could use SELECT TOP 17000... to
> limit the number of rows returned, but I'm still pulling a ton more
> data than I need.
> So I thought I'd do it with a UDF, but I thought I'd see if there was
> a good one already written for this.
> Thanks!
Select Top 2000
From dbo.MyTable
Where MyCol >= 15000
Order By Foo
Unless you have some column in the table that is numbered, there is no such
thing as "row 15000". Rows are not positional. In SQL 2005, you can assign
row numbers to the columns in the query using hte ROW_NUMBER() function.
David Gugick
Quest Software|||Thanks, David --
You're right, if the query isn't sorted. But if you specify an ORDER BY
clause, then there is a definite Nth row. (If the data changes, the Nth row
may change as well, but for my purposes, that's OK.)
In your example, if you can stop at the 2,000th row returned from an ordered
query (SELECT TOP 2000), it follows that you could skip over the first N of
them.
Jesse

> Select Top 2000
> From dbo.MyTable
> Where MyCol >= 15000
> Order By Foo
> Unless you have some column in the table that is numbered, there is no
> such thing as "row 15000". Rows are not positional. In SQL 2005, you can
> assign row numbers to the columns in the query using hte ROW_NUMBER()
> function.
>
> --
> David Gugick
> Quest Software
>

"Random IV"?

I just encrypted a bunch of rows in a table. I was a little surprised
that encrypting N'A' resulted in a 44-byte result. N'AAAA' resulted in
a 52-byte result. That indicates that 8-byte padding is being used.
So the actual encrypted text of N'A' is only 8 bytes in length. What
in the world are the other 36 bytes being used for? I noticed an
interesting 'prefix' for the encrypted data:
0x0074126478AFB34BA54BA6280E2335EB010000
003AE8D337EF2E5B55BABDE251B245C1FCCD
B21C770073B642
0x0074126478AFB34BA54BA6280E2335EB010000
0057252C84D4B6C9F3FFA40444BB7E5ADC50
9233F73548C7A8
0x0074126478AFB34BA54BA6280E2335EB010000
0077113F30B10CC2A8FD976628C98C06C5F7
0E47AEEF3BE86C
It appears the first 20 bytes of every encrypted string are exactly the
same, regardless of the row, column, or data being encrypted. Further
these 20 bytes appear to be generated when the OPEN ... KEY statement
is issued. In between OPEN ... KEY statements, these first 20
characters change, but for multiple encryptions after an OPEN ... KEY
they remain the same.
These first 20 - 36 bytes wouldn't happen to be the "Random IV" would
they? One would think that storing the IV with the data would be
potentially less secure than letting the user manage their own IV...
What do you think?The first 16 prefix bytes represent the key identifier (a GUID). They are
used to determine what key should be used for decryption. They're
particularly useful if you use multiple encryption keys per column. The
decryption builtins will automatically search for the proper key and use it
to decrypt the blob.
So, if you're doing something like
select decryptbykey(SSN) from customers
it will decrpyt all SSNs encrypted by keys that you currently have opened.
The IV is indeed prefixed to the data. IV's are not meant to be secret,
they're just used to randomize the encryption output, so that patterns in
the cleartext data are not disclosed.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
<coles.michael@.gmail.com> wrote in message
news:1151728397.102411.65150@.h44g2000cwa.googlegroups.com...
>I just encrypted a bunch of rows in a table. I was a little surprised
> that encrypting N'A' resulted in a 44-byte result. N'AAAA' resulted in
> a 52-byte result. That indicates that 8-byte padding is being used.
> So the actual encrypted text of N'A' is only 8 bytes in length. What
> in the world are the other 36 bytes being used for? I noticed an
> interesting 'prefix' for the encrypted data:
> 0x0074126478AFB34BA54BA6280E2335EB010000
003AE8D337EF2E5B55BABDE251B245C1FC
CDB21C770073B642
> 0x0074126478AFB34BA54BA6280E2335EB010000
0057252C84D4B6C9F3FFA40444BB7E5ADC
509233F73548C7A8
> 0x0074126478AFB34BA54BA6280E2335EB010000
0077113F30B10CC2A8FD976628C98C06C5
F70E47AEEF3BE86C
> It appears the first 20 bytes of every encrypted string are exactly the
> same, regardless of the row, column, or data being encrypted. Further
> these 20 bytes appear to be generated when the OPEN ... KEY statement
> is issued. In between OPEN ... KEY statements, these first 20
> characters change, but for multiple encryptions after an OPEN ... KEY
> they remain the same.
> These first 20 - 36 bytes wouldn't happen to be the "Random IV" would
> they? One would think that storing the IV with the data would be
> potentially less secure than letting the user manage their own IV...
> What do you think?
>|||The first 20 bytes are not part of the ciphertext, it is really some
metadata for internal use:
*The first 16 bytes is the key GUID used by the system to find the proper
key from the key ring (opened keys), if there is no key that matches the
ciphertext GUID, the operation fails immediately returning NULL.
*The next 4 bytes is an internal version number, this value is hardcoded
for now, but can be potentially used in the future to distinguish what
version of the product was used to generate any given ciphertext.
*Then we have the randomly generated IV (8 bytes in case of DES family,
16 in case of AES) followed by the rest of the ciphertext + padding (up to a
whole block of padding, again 8 or 16 bytes depending on the algorithm).
The real plaintext is not purely the plaintext given to the builtin. It
includes an 8 byte header that describes some attributes of the plaintext an
d
the presence of the authenticator parameter for the builtin.
Let’s take a look to a sample:
CREATE SYMMETRIC KEY key1 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY PASSWORD
= 'k3y_1 P@.zzw0rd'
CREATE SYMMETRIC KEY key2 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY PASSWORD
= 'k3y_1 P@.zzw0rd'
go
OPEN SYMMETRIC KEY key1 DECRYPTION BY PASSWORD = 'k3y_1 P@.zzw0rd'
OPEN SYMMETRIC KEY key2 DECRYPTION BY PASSWORD = 'k3y_1 P@.zzw0rd'
go
SELECT key_name, convert(varbinary(100), key_guid) as key_guid_bin FROM
sys.openkeys
go
CREATE TABLE key_demo( key_guid_bin varbinary(100) , ciphertext
varbinary(8000) )
go
-- 3 times the same value with each key
INSERT INTO key_demo VALUES( key_guid('key1'), EncryptByKey(
key_guid('key1'), 'AAAAAAAA' ))
INSERT INTO key_demo VALUES( key_guid('key1'), EncryptByKey(
key_guid('key1'), 'AAAAAAAA' ))
INSERT INTO key_demo VALUES( key_guid('key1'), EncryptByKey(
key_guid('key1'), 'AAAAAAAA' ))
INSERT INTO key_demo VALUES( key_guid('key2'), EncryptByKey(
key_guid('key2'), 'AAAAAAAA' ))
INSERT INTO key_demo VALUES( key_guid('key2'), EncryptByKey(
key_guid('key2'), 'AAAAAAAA' ))
INSERT INTO key_demo VALUES( key_guid('key2'), EncryptByKey(
key_guid('key2'), 'AAAAAAAA' ))
go
SELECT * FROM key_demo
go
Let’s take a closer look to these results. In my case, I got the following
GUID on my demo key:
0x008FBF67275CF643BF623BC0422F7F5C
And the first ciphertext:
0x008FBF67275CF643BF623BC0422F7F5C010000
000EC0844ACA2346DB0FE2BD35FF93BEA656
B2A45296CB8E000F7094C2F21D0285
Notice that the GUID is indeed the prefix of the ciphertext, then we have
“01000000”, which is the hardcoded version.
0EC0844ACA2346DB – IV
0FE2BD35FF93BEA6 – 8 byte header
56B2A45296CB8E00 – cipher text (8 bytes)
0F7094C2F21D0285 – padding (8 bytes)
Now, let’s take a quick look and compare the next row:
0x008FBF67275CF643BF623BC0422F7F5C – same GUID (same key was used to encry
pt
this plaintext)
01000000 – same version
E4BA5A5141FE5842 – IV (different, and from this point the rest of the
ciphertext will be different as well)
0E4EEB8440877430 - header – (the plaintext for both headers should be the
same as the same options were used)
33C361724D74742F
26E6829B957E877F
I hope this helped to clarify the usage of the first 20 bytes of data as
well as the other 8 bytes used for the internal use header.
-Raul Garcia
SDE/T
SQL Server Engine
________________________________________
This posting is provided "AS IS" with no warranties, and confers no rights.
"coles.michael@.gmail.com" wrote:

> I just encrypted a bunch of rows in a table. I was a little surprised
> that encrypting N'A' resulted in a 44-byte result. N'AAAA' resulted in
> a 52-byte result. That indicates that 8-byte padding is being used.
> So the actual encrypted text of N'A' is only 8 bytes in length. What
> in the world are the other 36 bytes being used for? I noticed an
> interesting 'prefix' for the encrypted data:
> 0x0074126478AFB34BA54BA6280E2335EB010000
003AE8D337EF2E5B55BABDE251B245C1FC
CDB21C770073B642
> 0x0074126478AFB34BA54BA6280E2335EB010000
0057252C84D4B6C9F3FFA40444BB7E5ADC
509233F73548C7A8
> 0x0074126478AFB34BA54BA6280E2335EB010000
0077113F30B10CC2A8FD976628C98C06C5
F70E47AEEF3BE86C
> It appears the first 20 bytes of every encrypted string are exactly the
> same, regardless of the row, column, or data being encrypted. Further
> these 20 bytes appear to be generated when the OPEN ... KEY statement
> is issued. In between OPEN ... KEY statements, these first 20
> characters change, but for multiple encryptions after an OPEN ... KEY
> they remain the same.
> These first 20 - 36 bytes wouldn't happen to be the "Random IV" would
> they? One would think that storing the IV with the data would be
> potentially less secure than letting the user manage their own IV...
> What do you think?
>|||Thank you both for the info. That is quite a bit of extra storage when
encrypting small strings, but apparently that's just the cost of doing
business. I have another question for you - is it possible to invoke your
own implementation of an algorithm from SS 2005? For instance, if I created
my own CryptoAPI CSP can the T-SQL encryption functions recognize and use
it?
"Raul Garcia [MS]" <Raul Garcia [MS]@.discussions.microsoft.com> wrot
e in
message news:F7E85812-8C39-4E46-B8C0-C9B883BA06EC@.microsoft.com...[vbcol=seagreen]
> The first 20 bytes are not part of the ciphertext, it is really some
> metadata for internal use:
> *The first 16 bytes is the key GUID used by the system to find the
> proper
> key from the key ring (opened keys), if there is no key that matches the
> ciphertext GUID, the operation fails immediately returning NULL.
> *The next 4 bytes is an internal version number, this value is hardcoded
> for now, but can be potentially used in the future to distinguish what
> version of the product was used to generate any given ciphertext.
> *Then we have the randomly generated IV (8 bytes in case of DES family,
> 16 in case of AES) followed by the rest of the ciphertext + padding (up to
> a
> whole block of padding, again 8 or 16 bytes depending on the algorithm).
> The real plaintext is not purely the plaintext given to the builtin. It
> includes an 8 byte header that describes some attributes of the plaintext
> and
> the presence of the authenticator parameter for the builtin.
> Let's take a look to a sample:
> CREATE SYMMETRIC KEY key1 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY
> PASSWORD
> = 'k3y_1 P@.zzw0rd'
> CREATE SYMMETRIC KEY key2 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY
> PASSWORD
> = 'k3y_1 P@.zzw0rd'
> go
> OPEN SYMMETRIC KEY key1 DECRYPTION BY PASSWORD = 'k3y_1 P@.zzw0rd'
> OPEN SYMMETRIC KEY key2 DECRYPTION BY PASSWORD = 'k3y_1 P@.zzw0rd'
> go
> SELECT key_name, convert(varbinary(100), key_guid) as key_guid_bin FROM
> sys.openkeys
> go
> CREATE TABLE key_demo( key_guid_bin varbinary(100) , ciphertext
> varbinary(8000) )
> go
> -- 3 times the same value with each key
> INSERT INTO key_demo VALUES( key_guid('key1'), EncryptByKey(
> key_guid('key1'), 'AAAAAAAA' ))
> INSERT INTO key_demo VALUES( key_guid('key1'), EncryptByKey(
> key_guid('key1'), 'AAAAAAAA' ))
> INSERT INTO key_demo VALUES( key_guid('key1'), EncryptByKey(
> key_guid('key1'), 'AAAAAAAA' ))
> INSERT INTO key_demo VALUES( key_guid('key2'), EncryptByKey(
> key_guid('key2'), 'AAAAAAAA' ))
> INSERT INTO key_demo VALUES( key_guid('key2'), EncryptByKey(
> key_guid('key2'), 'AAAAAAAA' ))
> INSERT INTO key_demo VALUES( key_guid('key2'), EncryptByKey(
> key_guid('key2'), 'AAAAAAAA' ))
> go
> SELECT * FROM key_demo
> go
> Let's take a closer look to these results. In my case, I got the following
> GUID on my demo key:
> 0x008FBF67275CF643BF623BC0422F7F5C
> And the first ciphertext:
> 0x008FBF67275CF643BF623BC0422F7F5C010000
000EC0844ACA2346DB0FE2BD35FF93BEA6
56B2A45296CB8E000F7094C2F21D0285
> Notice that the GUID is indeed the prefix of the ciphertext, then we have
> "01000000", which is the hardcoded version.
> 0EC0844ACA2346DB - IV
> 0FE2BD35FF93BEA6 - 8 byte header
> 56B2A45296CB8E00 - cipher text (8 bytes)
> 0F7094C2F21D0285 - padding (8 bytes)
> Now, let's take a quick look and compare the next row:
> 0x008FBF67275CF643BF623BC0422F7F5C - same GUID (same key was used to
> encrypt
> this plaintext)
> 01000000 - same version
> E4BA5A5141FE5842 - IV (different, and from this point the rest of the
> ciphertext will be different as well)
> 0E4EEB8440877430 - header - (the plaintext for both headers should be the
> same as the same options were used)
> 33C361724D74742F
> 26E6829B957E877F
> I hope this helped to clarify the usage of the first 20 bytes of data as
> well as the other 8 bytes used for the internal use header.
> -Raul Garcia
> SDE/T
> SQL Server Engine
> ________________________________________
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "coles.michael@.gmail.com" wrote:
>|||No, right now, you cannot specify the CSP to be used for encryption. We're
looking at adding this capability in future versions of SQL Server.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike C#" <xyz@.xyz.com> wrote in message
news:u43EzXQoGHA.964@.TK2MSFTNGP05.phx.gbl...
> Thank you both for the info. That is quite a bit of extra storage when
> encrypting small strings, but apparently that's just the cost of doing
> business. I have another question for you - is it possible to invoke your
> own implementation of an algorithm from SS 2005? For instance, if I
> created my own CryptoAPI CSP can the T-SQL encryption functions recognize
> and use it?
>
> "Raul Garcia [MS]" <Raul Garcia [MS]@.discussions.microsoft.com> wr
ote in
> message news:F7E85812-8C39-4E46-B8C0-C9B883BA06EC@.microsoft.com...
>

"Insufficient key column information for updating and refreshing"

"Insufficient key column information for updating and refreshing"
What does this message mean. All that I am trying to do is manually update
2 rows in one table with one join to another table with the updated values.
Thanks.
ArcherThis message means that SQL Server can determine which value you want to
update because is the relation is not that clear for him. That could be
caused if you want to update a joined table for common data which can be
changed in particular only one ROW. But the best thing would be if you could
send some DDL and the query to find your problem.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"bagman3rd" <bagman3rd@.discussions.microsoft.com> schrieb im Newsbeitrag
news:3EA65F99-EC00-4156-B1B1-1C1A0E52DAB9@.microsoft.com...
> "Insufficient key column information for updating and refreshing"
> What does this message mean. All that I am trying to do is manually
> update
> 2 rows in one table with one join to another table with the updated
> values.
> Thanks.
> Archer|||Hi,
See the KB :-
http://support.microsoft.com/defaul...kb;en-us;814893
Thanks
Hari
SQL Server MVP
"bagman3rd" <bagman3rd@.discussions.microsoft.com> wrote in message
news:3EA65F99-EC00-4156-B1B1-1C1A0E52DAB9@.microsoft.com...
> "Insufficient key column information for updating and refreshing"
> What does this message mean. All that I am trying to do is manually
> update
> 2 rows in one table with one join to another table with the updated
> values.
> Thanks.
> Archer|||It means that there is no primary key declared on the table
by which the row you want to update can be identified
uniquely. When you "view" a table, you can point to a
row by where it is on the screen, but for the update to
succeed, the row must be identified by the value of a
declared primary key, not where you see it on the screen.
Non-database programs, like Excel, keep track of data
by physical position (row number), but database systems
do not. What you see on the screen when you do a "manual
update" is not the actual data, but a result set, like a printout.
It's a dubious convenience of most database systems to
provide an interface whereby someone can change something
in a printout and have the change propogate back to the data.
An UPDATE statement is the best way to modify data,
and my guess is that you can't specify what you want to
update uniquely in a WHERE clause.
Steve Kass
Drew University
bagman3rd wrote:

>"Insufficient key column information for updating and refreshing"
>What does this message mean. All that I am trying to do is manually update
>2 rows in one table with one join to another table with the updated values.
>Thanks.
>Archer
>

"Cannot sort a row of size" how to resolve

I have a SQL Stored Procedure which select data from some tables. I worked on some rows but when I inserted one more row which had a large data- new row has inserted but I cannot select and order its record and SQLsrv raised this error
"Cannot sort a row of size 8154, which is greater than the allowable maximum of 8094"
any one, plz help me to Fix this.
Thanks alotThe row size extends a maximum page length - You will have to normailze the data further or reduce the attribute lengths - Can you post the table schema (DDL)?