|
In stored procedure i used a query
"select Password from tblUser where username='User1' and password='Password'
the reselt is 'Password'
But at the backend the value of Password is 'password'
(that is small letters.
Since password validation needs case sensitive validation how am i supposed to used string comparator. Else is there any other option
ItsMe
|
|
|
|
|
If you are using SQL 2005, you can use HashBytes function to calculate a hashing value of password then compare two hashing values.
If you use SQL 2000, you can use BINARY_CHECKSUM function instead of.
|
|
|
|
|
I am using SQL 2000. i this function is quite complicated one. I just wanted to return true by comparing the below string
string1='User1'
string2='User1'
string3='user1' (here the character 'u' is small)
true when string1 and string2 are compared
false when string 3 is compared with either string1 or string2
ItsMe
|
|
|
|
|
select Password <br />
from tblUser <br />
where username='User1' and cast(password as binary)=cast('Password' as binary)
Run this little test in the SQL Query Analyzer:
declare @cTest1 varchar(10)
declare @cTest2 varchar(10)
select @cTest1 = 'test'
select @cTest2 = 'Test'
if (@cTest1 = @cTest2)
print 'They are equal as varchar'
else
print 'They are NOT equal as varchar'
if (cast(@cTest1 as binary) = cast(@cTest2 as binary))
print 'They are equal as binary'
else
print 'They are NOT equal as binary'
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Your biggest problem is that you are storing plaintext passwords in a dababase table. Any hacker who compromises a single user login will have access to every username and password in the system...
|
|
|
|
|
Hi,
How to cast the following value into datetime.
select CAST('17/01/2006 14:52:48' as DATETIME) --> gives out-of-range datetime value.
|
|
|
|
|
You are nearly there try this
<br />
select Convert(DATETIME, '17/01/2006 14:52:48')<br />
There are options that you can use as well; as described below.
example convertimg German date for today without time
<br />
select Convert(DATETIME, CAST(Day(getdate())as char(2)) + '.' + CAST(Month(getdate()) as char(2)) + '.' + CAST(Year(getdate())as char(4)) , 104)<br />
Without century (yy) (1) With century (yyyy) Standard Input/Output (3)
0 or 100 (1, 2) Default mon dd yyyy hh:miAM (or PM)
1 101 U.S. mm/dd/yyyy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 (1) - dd mon yy
7 107 (1) - Mon dd, yy
8 108 - hh:mm:ss -
9 or 109 (1, 2) Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
13 or 113 (1, 2) Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
20 or 120 (2) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
21 or 121 (2) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
126 (4) ISO8601 yyyy-mm-ddThh:mm:ss.mmm (no spaces)
127(6) ISO8601 with time zone Z. yyyy-mm-ddThh:mm:ss.mmmZ (no spaces)
130 (1, 2) Hijri (5) dd mon yyyy hh:mi:ss:mmmAM
131 (2) Hijri (5) dd/mm/yy hh:mi:ss:mmmAM
-- modified at 7:05 Tuesday 8th August, 2006
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
For consistent date-time conversion behaviour, always use the ISO format: yyyyMMdd hh:mm:ss. All other formats are subject to the user's current locale setting.
Best practice when passing dates from an application into a query is to use parameterised queries with parameter objects. For example, in ADO.NET with SQL Server, use @name placeholders to mark the parameters, and SqlParameter objects to set the values of the parameters.
|
|
|
|
|
i need to select random row from table in my database to dir the contents of that row every time i dir different row how?!!!!!!!!!!!!!!!!
ma_refay
|
|
|
|
|
You can use newID() if your database is sqlserver for this purpose. It will give you random row from the table.
Best Regards,
Apurva Kaushal
|
|
|
|
|
NEWID() on its own won't give you a random row. It will only give you a new GUID.
|
|
|
|
|
But if we want to get the random row from a particular table then can't we get that with the help of newID().
Best Regards,
Apurva Kaushal
|
|
|
|
|
SELECT TOP 1 * FROM Table1 ORDER BY newid()
|
|
|
|
|
Make as stored procedure:
CREATE PROCEDURE GetRandomRow AS
declare @nRand int
declare @cSQL varchar(200)
select @nRand = 1 + (rand() * (select count(*) from MyTestTable))
select @cSQL = 'SELECT TOP 1 * FROM (SELECT TOP ' + cast(@nRand as varchar)
+ ' * FROM MyTestTable ORDER BY IDColumn ASC) as tbl ORDER BY IDColumn DESC'
exec(@cSQL)
You could send in the table name and ORDER BY field as parameters.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Your website is getting so many hits and visitors are running so many queries Temp DB logs starts growing exponentially thus your sites starts slowing down and even crashing. What would be the most effecient way to quickly clear temp DB without restarting the servers. I wrote this script which worked but it took a while, but it helped. Would you use another method is this script the best its gonna get?
DBCC SHRINKFILE(‘YourDataBase_log’)
BACKUP LOG YourDataBase WITH TRUNCATE_ONLY
After that is done if the log files still have not shrunken then I do this:
SET NOCOUNT ON
CREATE TABLE dummy
(a VARCHAR(8000))
WHILE (0 < 1)
BEGIN
INSERT INTO dummy SELECT REPLICATE(‘a’, 8000)
DELETE FROM dummy
END
-- modified at 0:45 Tuesday 8th August, 2006
Greg
Coding makes the world go round!!!
|
|
|
|
|
Put tempdb on a seperate diskdrive if possible and this will also improve performance.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
I have been reading this article, but can't seem to get my application to do what I need.
Here is the situation :
Data parsed from text file, dumped into table. User selects options for the data he needs. Data dumped into datagrid via dataset. I want the user to be able to edit info in the datagrid and save the changes to the dataset, which will now be dumped into a table to be used with Word.
I can't seem to find a way to save the changes to the dataset.
Any help very well appreciated.
Jude
|
|
|
|
|
|
Thanx for the reply...it was human error..I was calling the function to update the database before the changes were made in the datagrid
Jude
|
|
|
|
|
Hi All,
Can anyone tell me the order in which sql Query executes
Example:
select C.*,RenewalDate=(select * From CustomerApprovals where CustNo=1 Order By ApprovalDate Desc)
from Customers C where C.CustNo=1
|
|
|
|
|
All at once - SQL is a set based language not a procedural language, although for certain things you can act in a procedural manner (e.g. using cursors) but this is slow because it is not using the strengths of the language.
Basically a SELECT statement is telling SQL Server what you want, and then SQL Server figures out itself how to achive the result you want.
If it makes it any easier for you to think about you can think of the subquery executing first. However, the reality is that many parts of the query will execute in parallel.
Aside from that I cannot see what you are tying to do with your statement because it is invalid.
|
|
|
|
|
Look at the execution plan to see the order in which it will be executed.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
Hi
I want to create a report that lists the customers and all the items that they have not bought from us for a certain period. I have 3 relevant tables, CustomerMaster, InventoryMaster, SalesDetail.
SalesDetail holds all the sale info, for example
CustomerCode : 1AFR204
StockCode : FRA202
QtyInvoiced : 2
NetSalesValue: 1295
Year : 2006
Month : 7
The master tables holds all customers and inventory.
I want the report to basicaly look like this:
Ultra liquors Newton Park
Not listed:
Boland Cabernet
Spier Sauvignon Blanc
To be able to do this I need a recordset that consists of the stockcode and customercode of items not sold.
Any ideas how to do this? I have been playing with not in subqueries. Its easy to display items not sold or customers that did not buy, seperately, but to combine them is a different animal.
Thanks
Izak
|
|
|
|
|
SELECT CM.CustomerCode, IM.StockCode
FROM CustomerMaster CM, InventoryMaster IM
WHERE CM.CustomerCode NOT IN(SELECT SD.CustomerCode
FROM SalesDetail SD
WHERE SD.StockCode = IM.StockCode
AND SD.Year = 2006
AND (SD.Month >= 6 and SD.Month <= 7))
ORDER BY CustomerCode
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Thanks!
If I understand it correctly, the customermaster and the inventorymaster creates all possible combinations except for the combinations in the NOT IN clause?
So...
If customermaster has 200 records and inventorymaster has 100 records and the salesdetal has 750 distinct records. 200 * 100 = 20000 - 750 = 19250, the result will have 19250 possible sales combinations that did not happen.
Am my logic correct?
Thanks again Eric, this has been very helpfull! 
Izak
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
