|
i think Colin is the best one to answer SQL questions over CodeProject, but i can give it a try 
transactions can be seen for what you write on the database as an atomic statement. this means that when you begin a transaction, everything you write inside the transaction is actually buffered. when you commit the transaction, the database is physically altered by writing you datas. if you rollback the transaction, everything you did inside the transaction is lost, and the database state (as it was before your transaction) is not modified.
|
|
|
|
|
hi guys
i have question in SQL
select * from user where id='aaa' and pass='hello';
the problem is who can i make it case sensitive
like if in my table AAA exist it's different then aaa how can i select
the exacte word
Regards,
Charbel
|
|
|
|
|
hi,
First of all if your user table has an identity column PK so you can get the data by The PK.
Second here is a function that may come in handy
//For small a at the begining
Select * from user<br />
Where ASCII( id ) = 97
//for Big a at the begining
Select * from user<br />
Where ASCII (id ) = 65
ASCII Function:
Returns the ASCII code value of the leftmost character of a character expression.
|
|
|
|
|
Cast them to varbinary:
SELECT
*
FROM
user
WHERE
CAST(id AS VARBINARY(50)) = CAST('aaa' AS VARBINARY(50)) AND
CAST(pass AS VARBINARY(50)) = CAST('hello' AS VARBINARY(50))
|
|
|
|
|
Not an issue unless the database is set to be case sensitive. Other wise you can do this
select *
from user
where UPPER(id) = UPPER('aaa')
and UPPER(pass)= UPPER('hello');
|
|
|
|
|
Hi All,
I have the question about DataAdapter.Fill Method.
1. When I use Fill(DataSet), ADO.NET fills all data into DataSet with sql.
2. When I use Fill(DataSet, beginRecord, maxRecord, TableName), ADO.NET only fills maxRecord row into DataSet with the same Query.
But I wonder whether difference is in processing data of SQL Server when calling two methods:
1. When calling method 2, does SQL Server process more faster?
2. Or SQL Server process like method 1, it will send all data to ADO.NET and then ADO.NET filter neccessary rows?
Thanks,
Tran Thai Nam
|
|
|
|
|
Hi,
When I fetched 700000 record from the SQL db table from analyse it took somewhere aroung 36 second when i tried same thing using SQL Dataadapter (Methos 2)where my Select statement wes "Select * From Table" with maxRecord 700000 it took almost same time.........even in SQL Profiler it runs the same query............it seems like sqlDataAdapter send some hidden command to SQL server (Just like select Top N *..........
Regards,
Ritesh
|
|
|
|
|
I think you should try to use method 2 with query "select * from table" and maxRecord=10(just get 10 record) to see difference between two methods.
I want to know clearly time SQL Server process method 2 is or not difference method 1.
I don't talk about time SQL Server send this data to ADO.NET (because method 2 sure faster method 1).
Thanks,
Tran Thai Nam
|
|
|
|
|
 Hi my gurus,
I follow step by step to do merge replication function at http://www.codeproject.com/database/MergeReplication.asp[^] but after executting, in Publistion database automatic adding conflict_... tables and MSmerge_... tables, sysmerge... tables in Subcriber database. These destroyed my database structure, so they can't to use for my application program. Tell me the way to merge but don't create anything in the database except datas. Thanks !!! Or anyOne to know another way to synchronize data between different locations, pls tell me. Thanks a lot !!!
|
|
|
|
|
Those tables are needed by the merge process to keep track of merge progress and problems.
Your application should not care about those tables and should gracefully ignore them. Any application that can't tolereate tables that it has no concern with is badly designed.
Yor data structures should have remained untouched (i.e. no changes to your application tables). It is not unusual for system proccesses tocreate additional tables in a database to support that process, and as long as no existing structures are changed, that should be transparent to any applications using the database. Sounds like you made an unfounded design assumption when you designed your application...
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
The application is SAP B1, I'm not design. When I use Snapshot function, it's ok, but merge it can't. Do you know why ??? 
-- modified at 3:06 Thursday 9th March, 2006
|
|
|
|
|
Hi
I have a strange problem when trying to register SQL Server 2000 with other pc that same network with mine. i have problem related to SQL Server
registration problems.
Doing a SQL Server registration on localhost works.
i have create a new user at pc A with sql authentication. then at pc B i try new sql server registration there.
When trying to register the same SQL Server on an Enterprise Manager
installed on pc B the registration fails with message:
"Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection"
but when i do it the same way at my University lab , it work. when i do at my own house it fail
Any ideas out there what causes this? Thanks a lot!
chung
|
|
|
|
|
Are the "lab" pc's in a domain and the 'home' ones not?
if in a workgroup, does the accout you are using exist on both machines and have exactly the same password?
Integrated security expects the logon account to be either a trusted domain account, or if in workgroups to be identical on all machines (same username spelling, same password)
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
I install local at my home pc. i think is that problem lah,cause the the username and password is exactly same.
I will try reinstall my sql server and other pc also.
thank Rob Graham
chung
|
|
|
|
|
hi all,
I am using Sql Server 2000.
using 'SQL Query Analyser' I have try to convert DateTime ..But It is failed..
please kindly help me.
this is the code part I tried:-
declare @stdate VARCHAR(20)
set @stdate='12/05/05'
select convert(DATETIME,@stdate,103)
Error:-Syntax error converting datetime from character string.
I have received data like @stdate ,So I need to convert it to DateTime and insert for DateTime Column in SQL Server Table.
So How I avoid Error and do Conversion?
thanks in advance..
|
|
|
|
|
Hi,
why ur not sending stdate as '12/05/2005' from ur application??
anyway it's ur choice........i think this may work for u........(though this will assume ur current year>=2000)
declare @stdate VARCHAR(20)
set @stdate='12/05/05'
select convert(DATETIME,SUBSTRING(@stdate,1,6) + '20' + SUBSTRING(@stdate,7,2),103)
Regards,
Ritesh
|
|
|
|
|
Try:
Select cast(@stdate as varchar(20)) as datetime
instead of using convert
Jonathan
|
|
|
|
|
declare @stdate VARCHAR(20)
set @stdate='12/05/05'
select cast(@stdate as varchar )as datetime
"Every morning I check Forbes 40 richest Indians list , if i'm not there I go to work."
|
|
|
|
|
Hi All,
I'm having trouble putting my question into words, so if it sounds funny, i'm sorry. But here goes...
I've created a View in SQL that contains all the info i need from the different tables i have. The view contains Example:
TransactionDate, StaffID, StaffName, BrandID, BrandName, TotalCash, TotalQty
2006/03/01, 1, Jan, 2, Loreal, 250, 10
2006/03/01, 1, Jan, 3, Kerastase, 200, 8
2006/03/01, 2, Koos, 1, Revlon, 200, 11
2006/03/01, 2, Koos, 2, Loreal, 290, 12
2006/03/02, 1, Jan, 1, Revlon, 500, 25
2006/03/02, 1, Jan, 3, Kerastase, 345, 15
2006/03/03, 1, Jan, 3, Kerastase, 100, 6
2006/03/04, 2, Koos, 1, Revlon, 120, 3
With this examle jou see that i've calculated the totals on the TransactionDate for each Staffmember for each Brand.
I want to know if it is possible to write a procedure in SQL and send from C# a DateValue and BrandID to SQL to let the procedure know that it should only do the calculation between a specified Date and on every Brand. I need One total for each brand between the specified date: example 2006/03/01 - 2006/03/03.
I can do the calculation in C# but i need to speed up the processing time by using the database to do the calculation and not the application we wrote. Would Using a procedure in SQL actually speed up the Process or Would our application still run more or less the same time because it has to send a value to the database and wait for the calculation to complete and get the Value back from the Database?
I need to know rather urgantly Please. Thank you in advance.
-- modified at 5:29 Tuesday 7th March, 2006
|
|
|
|
|
you can pass the brand id and the two dates from yours c# application and in sql procedure use them as parameters and write like
select sum (column nmae) from table name where date<d1 and="" date="">d2 group by brand id
|
|
|
|
|
|
Hi All!!
I was browsing through that ASPNET database that .NET adds to your application, and in the membership table there are 2 password fields. The values seem to be both encrypted. One field is called Password, and the other is PasswordSalt. What is the difference between the 2? Are both required?
Thanks
Brendan
|
|
|
|
|
ma se wrote: What is the difference between the 2?
The password is a salted-hash. The PasswordSalt is the salt value used by the hashing algorithm (and I'll get to what that is in a moment). Basically the idea is that a dictionary could be built containing a set of common passwords and their corresponding hash values. This makes reversing the database very easy. A hash is a one-way encryption of some data, you cannot take the hashed value in the Password column and reverse it in to the original password (hence the need for a dictionary to be built).
Now, there is also the scenario that Alice and Mallory are users on a system. Unknown to each other they have chosen the same password. The hash values of that password will be the same. If Mallory happens to see the table in the database and sees that the hash value for his password and Alice's password are the same, he can conclude that they both have the same password and he can then malliciously gain access to Alice's account. This is bad.
So, to reduce the effectiveness of a dictionary attack and the consequence of happening to have the same password as some other user you can add a salt value to the password. This effectively adds a random element that makes the resulting hash values very different.
The salt can be known as it gives no real benefit to the attacker. The dictionary is useless because it would have to be regenerated by adding the salt value on to each plain text value and re-hashing it and then comparing the hash values. So, the effect is back to doing a brute force attack which is slow.
ma se wrote: Are both required?
Yes, both are required. When a user types a password, the known salt is added to it and it is then hashed. The hash value in the database is compared with the hash value generated by the user input+salt. If the salted hash values match then the user can be given access. If you take away the salt values then the user cannot be authenticated.
Does this help?
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
It does help in a way yes, but I'm going to need to do some more research on this topic.
How does one add the salt?
What is a good algorithym to use? I read that MD5 and SHA1 can be cracked, and that a newer SHA2 is being used. Is it really that better? Do you maybe know where I can find .NET code for hashing a password using SHA2?
Take care!!
|
|
|
|
|
ma se wrote: How does one add the salt?
I presume that in ASP.NET 2.0 there is code to do this for you in the new controls. However, you may like to read this thread[^] to see a similar answer I gave some time ago in response to an article I wrote.
ma se wrote: I read that MD5 and SHA1 can be cracked
Not so much that they can be cracked, as I remember, but that it was found that there were many collisions. i.e. Two different passwords producing the same hash value.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
