Encrypt By Key gives different results every call time

Every time I call Encrypt by key I get a different result, although I am encrypting the same clear text,using the same authenticator and using the same key.
I tried calling it twice inside one stored procedure and I got two different results.

Declare @.Temp varbinary(8000);
Declare @.Temp2 varbinary(8000);
set @.Temp = EncryptByKey(Key_GUID('MyKey'),@.cleartext,1,@.Authenticator);
set @.Temp2= EncryptByKey(Key_GUID('MyKey'),@.cleartext,1,@.Authenticator);
Select @.Temp,@.Temp2;

when executing this stored procedure, the value of @.Temp is differnent from the result of @.Temp2, and the same thing happens without using an authenticator.

Is there something going behind in the encryption mechanism of SQL server?

yes it's deliberate and important. You might have 2 rows with the same value, for example a salary. If these had the same resulting encrypted value it would make discovery of the non-encrypted value much easier. To address this SQL 2005's encryption methods use a salt with a random initialisation vector so you get a different encrypted value for the same input text.

thx,

Simon.

Encoding problem

Hi,

I'm struggling to understand why I'm getting the following error when I call "sp_xml_preparedocument"

"XML parsing error: Switch from current encoding to specified encoding not supported."

The header of the xml document contains "<?xml version="1.0" encoding="UTF-8"?>"

XML data is passed into my stored procedure via an "ntext" variable so I would assume that there would'nt be an encoding issue.

Does anyone have any pointers for me? BTW I'm using SQL Server 2000.

Many thanks in advance.

Ian.

PS I've just changed the parameter from "ntext" to "text" and it works fine. Don't understand even more now.

I think ntext uses UTF-16 (respectively the older UCS-2) not UTF-8.

Empty Recordset

New to Reporting Services.
I have written a stored procedure which I call from a report built with RS.
Certain parameters being passed to the procedure does not materialize a
record set, therefore, the report comes up blank, with no labels etc. What I
need to do is detect an empty record set, from within the report, and set
default values so the report does not appear blank. I have tried the
IsNothing function within each text boxes expressions, however, it does not
work. Has anyone run into this and if so, would you be so kind to provide a
solution?
Regards,
Fred
Regards,
FredHi,
maybe you can use the NoRows property of your table to display the desired
information?
"FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> New to Reporting Services.
> I have written a stored procedure which I call from a report built with
> RS.
> Certain parameters being passed to the procedure does not materialize a
> record set, therefore, the report comes up blank, with no labels etc. What
> I
> need to do is detect an empty record set, from within the report, and set
> default values so the report does not appear blank. I have tried the
> IsNothing function within each text boxes expressions, however, it does
> not
> work. Has anyone run into this and if so, would you be so kind to provide
> a
> solution?
> Regards,
> Fred
>
> Regards,
> Fred|||Hello Ralph,
Thank you for responding. I am not familiar with the NoRows property, do you
have an example? I tried searching the RS BOL but came up empty.
"Ralph Watermann" wrote:
> Hi,
> maybe you can use the NoRows property of your table to display the desired
> information?
> "FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> > New to Reporting Services.
> > I have written a stored procedure which I call from a report built with
> > RS.
> > Certain parameters being passed to the procedure does not materialize a
> > record set, therefore, the report comes up blank, with no labels etc. What
> > I
> > need to do is detect an empty record set, from within the report, and set
> > default values so the report does not appear blank. I have tried the
> > IsNothing function within each text boxes expressions, however, it does
> > not
> > work. Has anyone run into this and if so, would you be so kind to provide
> > a
> > solution?
> >
> > Regards,
> > Fred
> >
> >
> > Regards,
> > Fred
>
>|||Hi Fred,
You could modify your stored procedure to test whether or not you
retrieve records depending on the parameters you pass. If the query in your
stored procedure does not return records, then you can pass a single record
with the default values in a Select statement, that you want to show in the
report...
Cheers,
Daniel.
"FredG" wrote:
> Hello Ralph,
> Thank you for responding. I am not familiar with the NoRows property, do you
> have an example? I tried searching the RS BOL but came up empty.
> "Ralph Watermann" wrote:
> > Hi,
> >
> > maybe you can use the NoRows property of your table to display the desired
> > information?
> >
> > "FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
> > news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> > > New to Reporting Services.
> > > I have written a stored procedure which I call from a report built with
> > > RS.
> > > Certain parameters being passed to the procedure does not materialize a
> > > record set, therefore, the report comes up blank, with no labels etc. What
> > > I
> > > need to do is detect an empty record set, from within the report, and set
> > > default values so the report does not appear blank. I have tried the
> > > IsNothing function within each text boxes expressions, however, it does
> > > not
> > > work. Has anyone run into this and if so, would you be so kind to provide
> > > a
> > > solution?
> > >
> > > Regards,
> > > Fred
> > >
> > >
> > > Regards,
> > > Fred
> >
> >
> >|||"NoRows" is an element that is a part of the "Properties" for a table. You
can specify a value in the table's "Properties" (under "Data") for the case
when there is no row to display.
"daniel_xi" wrote:
> Hi Fred,
> You could modify your stored procedure to test whether or not you
> retrieve records depending on the parameters you pass. If the query in your
> stored procedure does not return records, then you can pass a single record
> with the default values in a Select statement, that you want to show in the
> report...
> Cheers,
> Daniel.
>
> "FredG" wrote:
> > Hello Ralph,
> > Thank you for responding. I am not familiar with the NoRows property, do you
> > have an example? I tried searching the RS BOL but came up empty.
> >
> > "Ralph Watermann" wrote:
> >
> > > Hi,
> > >
> > > maybe you can use the NoRows property of your table to display the desired
> > > information?
> > >
> > > "FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> > > > New to Reporting Services.
> > > > I have written a stored procedure which I call from a report built with
> > > > RS.
> > > > Certain parameters being passed to the procedure does not materialize a
> > > > record set, therefore, the report comes up blank, with no labels etc. What
> > > > I
> > > > need to do is detect an empty record set, from within the report, and set
> > > > default values so the report does not appear blank. I have tried the
> > > > IsNothing function within each text boxes expressions, however, it does
> > > > not
> > > > work. Has anyone run into this and if so, would you be so kind to provide
> > > > a
> > > > solution?
> > > >
> > > > Regards,
> > > > Fred
> > > >
> > > >
> > > > Regards,
> > > > Fred
> > >
> > >
> > >|||Hello Daniel,
That is exactly what I did to solve the problem. Thanks
"daniel_xi" wrote:
> Hi Fred,
> You could modify your stored procedure to test whether or not you
> retrieve records depending on the parameters you pass. If the query in your
> stored procedure does not return records, then you can pass a single record
> with the default values in a Select statement, that you want to show in the
> report...
> Cheers,
> Daniel.
>
> "FredG" wrote:
> > Hello Ralph,
> > Thank you for responding. I am not familiar with the NoRows property, do you
> > have an example? I tried searching the RS BOL but came up empty.
> >
> > "Ralph Watermann" wrote:
> >
> > > Hi,
> > >
> > > maybe you can use the NoRows property of your table to display the desired
> > > information?
> > >
> > > "FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> > > > New to Reporting Services.
> > > > I have written a stored procedure which I call from a report built with
> > > > RS.
> > > > Certain parameters being passed to the procedure does not materialize a
> > > > record set, therefore, the report comes up blank, with no labels etc. What
> > > > I
> > > > need to do is detect an empty record set, from within the report, and set
> > > > default values so the report does not appear blank. I have tried the
> > > > IsNothing function within each text boxes expressions, however, it does
> > > > not
> > > > work. Has anyone run into this and if so, would you be so kind to provide
> > > > a
> > > > solution?
> > > >
> > > > Regards,
> > > > Fred
> > > >
> > > >
> > > > Regards,
> > > > Fred
> > >
> > >
> > >

Empty contents of database

Hi All,
We have an SQL database that is filled by a Cisco call accounting program.
This program can only put data in a database which is less then 1.5GB in
size. Currently the database is 1.5GB and so the program can no longer keep
data.
What we wish to do is get rid of all records from this database, but leave
the table structure, etc.. in tact. We did try using just a blank database,
but the program does not work in that situation..
To that end, how can we empty the SQL database of records without upsetting
any of the other database properties.
ThanksHi
This sounds like you may be using MSDE which has a 2GB limit. You could
upgrade to a version of SQL Server that does not have this restriction!
If you do need to delete the data, make sure that you don't delete
static/configuration data that is required by the application to function
correctly, which could be why the empty database did not work (assuming you
did have tables in there!).
If you are deleting data from a table you will need to delete data from any
table that references it with a foreign key unless the FK has cascading
deletes, or alternatively you can disable foreign keys before deleting the
data and then re-instating them.
This may help you find which tables may be used most and therefore not
static data!
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
How to find foreign keys:
http://www.aspfaq.com/show.asp?id=2520
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
If you are deleting a large amount of data you may want to set the recovery
mode to simple during the excercise. You may want to TRUNCATE the table
rather than deleting rows. Truncation will reset identity values, you may
have to reset the identity values separately if you use delete.
Once you have a working template database, you can back it up and then you
will only need to restore the template database instead of deleting data the
next time it gets full.
John
"Luke Cassar" wrote:
> Hi All,
> We have an SQL database that is filled by a Cisco call accounting program.
> This program can only put data in a database which is less then 1.5GB in
> size. Currently the database is 1.5GB and so the program can no longer keep
> data.
> What we wish to do is get rid of all records from this database, but leave
> the table structure, etc.. in tact. We did try using just a blank database,
> but the program does not work in that situation..
> To that end, how can we empty the SQL database of records without upsetting
> any of the other database properties.
> Thanks

Empty contents of database

Hi All,
We have an SQL database that is filled by a Cisco call accounting program.
This program can only put data in a database which is less then 1.5GB in
size. Currently the database is 1.5GB and so the program can no longer keep
data.
What we wish to do is get rid of all records from this database, but leave
the table structure, etc.. in tact. We did try using just a blank database,
but the program does not work in that situation..
To that end, how can we empty the SQL database of records without upsetting
any of the other database properties.
Thanks
Hi
This sounds like you may be using MSDE which has a 2GB limit. You could
upgrade to a version of SQL Server that does not have this restriction!
If you do need to delete the data, make sure that you don't delete
static/configuration data that is required by the application to function
correctly, which could be why the empty database did not work (assuming you
did have tables in there!).
If you are deleting data from a table you will need to delete data from any
table that references it with a foreign key unless the FK has cascading
deletes, or alternatively you can disable foreign keys before deleting the
data and then re-instating them.
This may help you find which tables may be used most and therefore not
static data!
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
How to find foreign keys:
http://www.aspfaq.com/show.asp?id=2520
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
If you are deleting a large amount of data you may want to set the recovery
mode to simple during the excercise. You may want to TRUNCATE the table
rather than deleting rows. Truncation will reset identity values, you may
have to reset the identity values separately if you use delete.
Once you have a working template database, you can back it up and then you
will only need to restore the template database instead of deleting data the
next time it gets full.
John
"Luke Cassar" wrote:

> Hi All,
> We have an SQL database that is filled by a Cisco call accounting program.
> This program can only put data in a database which is less then 1.5GB in
> size. Currently the database is 1.5GB and so the program can no longer keep
> data.
> What we wish to do is get rid of all records from this database, but leave
> the table structure, etc.. in tact. We did try using just a blank database,
> but the program does not work in that situation..
> To that end, how can we empty the SQL database of records without upsetting
> any of the other database properties.
> Thanks

Empty contents of database

Hi All,
We have an SQL database that is filled by a cisco call accounting program.
This program can only put data in a database which is less then 1.5GB in
size. Currently the database is 1.5GB and so the program can no longer keep
data.
What we wish to do is get rid of all records from this database, but leave
the table structure, etc.. in tact. We did try using just a blank database,
but the program does not work in that situation..
To that end, how can we empty the SQL database of records without upsetting
any of the other database properties.
ThanksHi
This sounds like you may be using MSDE which has a 2GB limit. You could
upgrade to a version of SQL Server that does not have this restriction!
If you do need to delete the data, make sure that you don't delete
static/configuration data that is required by the application to function
correctly, which could be why the empty database did not work (assuming you
did have tables in there!).
If you are deleting data from a table you will need to delete data from any
table that references it with a foreign key unless the FK has cascading
deletes, or alternatively you can disable foreign keys before deleting the
data and then re-instating them.
This may help you find which tables may be used most and therefore not
static data!
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
How to find foreign keys:
http://www.aspfaq.com/show.asp?id=2520
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
If you are deleting a large amount of data you may want to set the recovery
mode to simple during the excercise. You may want to TRUNCATE the table
rather than deleting rows. Truncation will reset identity values, you may
have to reset the identity values separately if you use delete.
Once you have a working template database, you can back it up and then you
will only need to restore the template database instead of deleting data the
next time it gets full.
John
"Luke Cassar" wrote:

> Hi All,
> We have an SQL database that is filled by a cisco call accounting program.
> This program can only put data in a database which is less then 1.5GB in
> size. Currently the database is 1.5GB and so the program can no longer ke
ep
> data.
> What we wish to do is get rid of all records from this database, but leave
> the table structure, etc.. in tact. We did try using just a blank database
,
> but the program does not work in that situation..
> To that end, how can we empty the SQL database of records without upsettin
g
> any of the other database properties.
> Thanks