pwdencrypt and Case sensitive

Hi there
I know how to use pwdencrypt for my password and know how to use collation,
but how can I merge it together to make my password case sensitive and
pwdencrypt working, because collation only not support varbinary and my
pwdencrypt is saved on varbinary data type.
Any one has idea how to to this?
Thanks
Tony
I believe pwdencrypt behavior depends on the instance default collation. In
any case, you shouldn't use the undocumented pwdencrypt function in your
code. This may change or be removed in future SQL Server service packs or
versions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tony Dong" <tony.dong@.envoytrading.com> wrote in message
news:Oy07MZsRFHA.996@.TK2MSFTNGP09.phx.gbl...
> Hi there
> I know how to use pwdencrypt for my password and know how to use
> collation, but how can I merge it together to make my password case
> sensitive and pwdencrypt working, because collation only not support
> varbinary and my pwdencrypt is saved on varbinary data type.
> Any one has idea how to to this?
> Thanks
> Tony
>
|||> I know how to use pwdencrypt for my password
If you knew that then you wouldn't be using it. ;-) Don't.
..NET provides classees for generating password hashes. They are more secure
than pwdencrypt and they are also properly documented and supported whereas
pwdencrypt is likely to break or disappear in future releases.
David Portas
SQL Server MVP
|||I am Tony's coworker and using .NET to encrypt the password is not an
attractive option because it means that we cannot operate the database
without .NET. It makes the business tier and database tier tightly
coupled or to use some of my old C++ jargon "It breaks encapsulation!"
If we were using Yukon, then .NET would be the way to go.
Cheers
Ted
*** Sent via Developersdex http://www.codecomments.com ***

pwdencrypt and Case sensitive

Hi there
I know how to use pwdencrypt for my password and know how to use collation,
but how can I merge it together to make my password case sensitive and
pwdencrypt working, because collation only not support varbinary and my
pwdencrypt is saved on varbinary data type.
Any one has idea how to to this?
Thanks
TonyI believe pwdencrypt behavior depends on the instance default collation. In
any case, you shouldn't use the undocumented pwdencrypt function in your
code. This may change or be removed in future SQL Server service packs or
versions.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tony Dong" <tony.dong@.envoytrading.com> wrote in message
news:Oy07MZsRFHA.996@.TK2MSFTNGP09.phx.gbl...
> Hi there
> I know how to use pwdencrypt for my password and know how to use
> collation, but how can I merge it together to make my password case
> sensitive and pwdencrypt working, because collation only not support
> varbinary and my pwdencrypt is saved on varbinary data type.
> Any one has idea how to to this?
> Thanks
> Tony
>|||> I know how to use pwdencrypt for my password
If you knew that then you wouldn't be using it. ;-) Don't.
.NET provides classees for generating password hashes. They are more secure
than pwdencrypt and they are also properly documented and supported whereas
pwdencrypt is likely to break or disappear in future releases.
--
David Portas
SQL Server MVP
--

pwdencrypt and Case sensitive

Hi there
I know how to use pwdencrypt for my password and know how to use collation,
but how can I merge it together to make my password case sensitive and
pwdencrypt working, because collation only not support varbinary and my
pwdencrypt is saved on varbinary data type.
Any one has idea how to to this?
Thanks
TonyI believe pwdencrypt behavior depends on the instance default collation. In
any case, you shouldn't use the undocumented pwdencrypt function in your
code. This may change or be removed in future SQL Server service packs or
versions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tony Dong" <tony.dong@.envoytrading.com> wrote in message
news:Oy07MZsRFHA.996@.TK2MSFTNGP09.phx.gbl...
> Hi there
> I know how to use pwdencrypt for my password and know how to use
> collation, but how can I merge it together to make my password case
> sensitive and pwdencrypt working, because collation only not support
> varbinary and my pwdencrypt is saved on varbinary data type.
> Any one has idea how to to this?
> Thanks
> Tony
>|||> I know how to use pwdencrypt for my password
If you knew that then you wouldn't be using it. ;-) Don't.
.NET provides classees for generating password hashes. They are more secure
than pwdencrypt and they are also properly documented and supported whereas
pwdencrypt is likely to break or disappear in future releases.
David Portas
SQL Server MVP
--|||I am Tony's coworker and using .NET to encrypt the password is not an
attractive option because it means that we cannot operate the database
without .NET. It makes the business tier and database tier tightly
coupled or to use some of my old C++ jargon "It breaks encapsulation!"
If we were using Yukon, then .NET would be the way to go.
Cheers
Ted
*** Sent via Developersdex http://www.codecomments.com ***

Puzzling "NOT EXISTS" behavior

CASE CLOSED: removing the FROM clause noted below handles the problem ...
I've used "NOT EXISTS" in inserts before and thought I understood how they work, butI'm puzzled why I get three rows from the following sub-query, which I want to use to prevent errors from duplicate keys. Since it gives me three rows, it will actually try to insert three duplicate records and cause a primary-key fault, which is a twin to the very thing I'm trying to avoid. (of course, there are three records already in the table, none having the key of 20050810)
The sub-query is acting like it's using "WHERE DemoDate <> 20050810" instead of a "NOT EXISTS".
Attempts to use this statement causes a termination with no records inserted.
DemoDate is the primary key and an INT field in SQL Server. Four other int columns for this table have default values.
<code>
INSERT INTO DemoStats (DemoDate)
SELECT 20050810 AS Expr1
FROM DemoStats <-- remove this line so the effective table has only one row, when the NOT EXISTS is TRUE
WHERE (NOT EXISTS
(SELECT *
FROM DemoStats
WHERE DemoDate = 20050810))
</code>I read that as saying...
For every row in DemoStats that != 20050810 please insert 20050810
So surely you'd get loads inserts and therefore duplicate key bangs?
Why not simply
<code>
If not exists(xxxxx) insert <yyyy>
</code>
granted it introduces a condition but it's a lot easier to understand your intentions, plus it works ;)

|||I don't think that will work, since this is a web-based app, so someone else could easily insert the record between checking for exists and doing the insert, thus potentially causing the error anyway. This needs to be handled within a single sql statement.
I found the following (and several other examples that maybe suggest that EXISTS should work a little more like I'm thinking). In this case I wondering if "dual" is a reserved word with a special function, which I haven't seen before.
<code>

If you wanted to insert a single record, you could use the following statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);

The use of thedual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.

</code>|||

Edited:
If you really want to use your approach you could do simply like this :
INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
WHERE not exists (select * from clients
where clients.client_id = 10345);
without dual.

|||My theory (I'd love to have someone confirm or deny this): I think it's a bug in SQL Server or maybe even SQL specs. As I understand it, EXISTS is supposed to be a single-valued boolean: TRUE ifone or more rows satisfy the sub-query, and FALSE ifno rows satsify the sub-query. I think the bug depends on the fact that the INSERT, the SELECT, and the sub-query all deal with the same table. I have other UPDATES and INSERTS that use NOT EXISTS, and they're working correctly, but they don't have the same table in the INSERT, SELECT, and sub-query. If I'm wrong I need to go back and re-examine them, but need some confidence of knowing what is right.
When I add DISTINCT below it adds just one record (doesn't try to add three identical-key records), so I'm out of trouble, but I don't know why it otherwise tries to insert three records and triggers an error and termination.
<code>
INSERT INTO DemoStats (DemoDate)
SELECT DISTINCT 20050810 AS Expr1
FROM DemoStats
WHERE (NOT EXISTS
(SELECT *
FROM DemoStats
WHERE DemoDate = 20050810))
</code>
|||

BBradshaw wrote:

I don't think that will work, since this is a web-based app, so someone else could easily insert the record between checking for exists and doing the insert,

That's what isolation levels are for or you'll probably get away with just sticking it a transaction.

Push or Pull for Replication-Based failover

I am trying to devise a setup where I can use Replication to maintain a
failover server in case of a critical failure on the primary DB server. I
plan on using transactional replication and immeadiate updating for minimal
latency, but I have a couple of questions about the right setup:
1. Push or pull? Push would allow for 2 phase commit, which seems
desirable. On the other hand, when system 1 fails, don't you need to "break
the link" between the subscriber and publisher (i.e. run sp_dropsubscription)
in order to ensure proper operation?
2. We have some identity columns. I am planning on marking them as NOT FOR
REPLICATION and then in a failover scenario, run DBCC CHECKIDENT('my
table',RESEED,<looked up max from table>) to reseed the identity on the
secondary. Is this an advisable strategy?
I have been looking at other solutions in this area which involve modifying
some of the generated procs and I would like to avoid that so that we can use
as much out of the box as possible (trying not to pretend I can engineer
better than MS ).
Thanks,
Tom
Push or pull doesn't really relate to 2PC - the setting really controls who
initiates the distributor->subscriber traffic. If the subscriber fails, the
2PC won't matter because it is in the direction subscriber->distributor. The
other way round and you'll have to remove the subscription.
For the identities, the easiest solution is to set up immediateupdating with
queued updating failover and have a large seed set up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Could you clarify a bit? does subscriber->distributor imply pull whereas
distributor ->subscriber implies push? I am trying to understand when I
would have to remove the subscription.
Thanks,
Tom
"Paul Ibison" wrote:

> Push or pull doesn't really relate to 2PC - the setting really controls who
> initiates the distributor->subscriber traffic. If the subscriber fails, the
> 2PC won't matter because it is in the direction subscriber->distributor. The
> other way round and you'll have to remove the subscription.
> For the identities, the easiest solution is to set up immediateupdating with
> queued updating failover and have a large seed set up.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||The directions I indicated refer to data flow, eg subscriber->distributor in
your case is 2PC, whereas distributor ->subscriber is the PUSH/PULL running
of the distribution agent. I think you're mixing PUSH/PULL with the flow of
data, while the 2 are not really related in that way - it's more which
computer is going to do the synchronization work.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)