|
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?
|
|
|
|
|
Oracled 8i DB hosted on Windows 2000 server is used by PowerBuilder application. this application tries to get data from oracle 9i on sun solaris using DBlink. on this scenario am getting "tns timed out". i tried removing the hot fix KB901214 from the windows server. but still facing the issue. can anyone help?
thanks in advance
|
|
|
|
|
Can anyone tell me how to insert a clob into oracle. I need to upload a document and insert it into a clob column
Thanks
|
|
|
|
|
Hello guys!
In my database I need to be able to update my data. Funny huh? 
Well I thought if it was possible to create a stored procedure, that only updates the data supplied by the program. So any data not supplied, would simply stay untouched.
An example. We have a table "Persons". The columns are: "PersonId", "FirstName", "LastName", "Age".
Now little Lisa has a birthday, and we need to change her age. So instead of supplying the SP with all the data of Lisa. I would like to be able to only supply the new value of "Age", and of course the "PersonId" for identification. All other values should remain the same.
But some day little Lisa gets married, and we need to change her last name. At that time, I would like to supply the same SP with only the new value "LastName", and all the other data should remain the same.
Is this possible? Or is it just not the way to do things?
Best regards
Soeren
|
|
|
|
|
Why not just create two stored procedures? say ChangeAge and ChangeLastName
You could create some dynamic SQL in a stored procedure and execute a varchar, but you are getting into the territory of opening yourself up to security risks (like SQL Injection Attacks). It is better, if you can, to use a solution that does not involve dynamic SQL. If you do have to use it then you need to implement extra safe guards to prevent an attack.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Hi
I also think Colin's idea is good.
well-done Colin.
regards,
chandana
|
|
|
|
|
Hi,
I am using MS SQL SERVER and trying to insert data into a table in which i have a column of varchar type and size is set to 7000, but my problem arises when i have data of length greater than 7000. It gives me an error thereby not inserting records into the database.
Can someone please suggest on how to go about this problem? Can i break data and store it in multiple rows?
Thanks.
|
|
|
|
|
smadan wrote: I am using MS SQL SERVER and trying to insert data into a table in which i have a column of varchar type and size is set to 7000, but my problem arises when i have data of length greater than 7000. It gives me an error thereby not inserting records into the database.
Of course, SQL Server has been told to expect a maximum of 7000 characters.
smadan wrote: Can someone please suggest on how to go about this problem?
You could use a text column type, it allows up to 2Gb of characters.
smadan wrote: Can i break data and store it in multiple rows?
Yes, but I'd recommend you use the text column type. If you are using SQL Server 2005 you can also use varchar(max)
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Colin Angus Mackay wrote: Of course, SQL Server has been told to expect a maximum of 7000 characters.
I think it is 8000
|
|
|
|
|
Dinuj Nath wrote: Colin Angus Mackay wrote:
Of course, SQL Server has been told to expect a maximum of 7000 characters.
I think it is 8000
No. The original posted said explicitly he'd set it to a varchar(7000) - Therefore the maximum SQL Server will expect is 7000 characters. The maximum that SQL Server 2000 can be told to expect is varchar(8000) but that was not what was specified at part of the original problem.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Sorry. I should read before posting.
|
|
|
|
|
Hi,
Thanks for ur reply.
I tried using the text field , but i still get an error that reads "The size given to the column 'Result' exceeds the maximum allowed for any data type (8000)."
Pls suggest how to proceed.
Thanks.
|
|
|
|
|
If u use text u shouldn't get the same error again since text takes as much data as we place
One thing to avoid this is just fire a validation at client side if the data exceeds more than maximum length
"Every morning I check Forbes 40 richest Indians list , if i'm not there I go to work."
|
|
|
|
|
Thanks for ur reply. I used the text field in my stored procedure and it worked.
I was going wrong in the way i was using this datatype.
-- modified at 6:43 Wednesday 8th March, 2006
|
|
|
|
|
Hi!
I have some questions about datasets in VS2005 and SQL Server 2005. I have added a dataset to my project and added all interesting tables in my DB using the 'data source configuration wizard', all works like a charm. But now to the interesting bit, i have changed the design of some of the tables using 'Microsoft SQL Server Mangament studio express'(for example changed one column from varchar(max) to varchar(100)) and now i want the design changes to appear in VS to.
Only way I have mangaged to do just that is to remove the table from the dataset and add it again but then i lose all functionality I have added to the TableAdapter. Is it possible just to update the dataset without losing any added functionality?
//Jesper Miller
-- modified at 5:16 Monday 6th March, 2006
|
|
|
|
|
hello every body,
I am tryin to develop import export utility like that is provided with SQL Server 2000,it would be useful when there will only MSDE is installed.
I cant use SQLDMO`s Back up and Restore functionality as it is for local Instances. I tryin to use link server way but struggling use with all databases.
I am not able to import data from database to given database.
What r other possible ways of doing the above?
regards
tak
|
|
|
|
|
hi,
tusha wrote: develop import export
if you are familier with .Net
Why dont you develop a .Net Windows application that will recieve excel files to import or export excel files from or to the database,or Develop an app to that will display a table or results from a query in a listview and export it to Excel or let the user input the data so the App will save it in the table.
and if you are not may be we can help.
|
|
|
|
|
Hi experts..im new to sql and i wrote this stored procedure for number to character conversion..
intially i have taken one numeric parameter and i have converted into varchar to display in words..
But i want to do one more modifications to this code..if i entered 500 then it shuld be treated
as 500 dollors and should be multplied with 45 and the result should be = 500 * 45.
But im returning varchar value..how to multiply with '45'. im getting data type conversion error..
Here im sending my code upto ten thousand only.. and return statement..
pls solve the problem..and thanks in advance..
@p_Number numeric(18,2) --> Parameter...
begin
declare @Num varchar(20)
declare @Dec varchar(3)
declare @Return varchar(2000)
set @Return = convert (varchar(2000),@p_Number)
set @Dec = substring(convert(varchar(20),@p_Number),len(convert(varchar(20),@p_Number))-2,3)
set @Num = substring(convert(varchar(20),@p_Number),1,len(convert(varchar(20),@p_Number))-3)
declare @Hundred Char(8)
declare @HundredAnd Char(12)
declare @Thousand Char(9)
declare @Lakh Char(5)
declare @Lakhs Char(6)
declare @Crore Char(6)
declare @Crores Char(7)
set @Hundred = 'Hundred '
set @Thousand = 'Thousand '
set @Lakh = 'Lakh '
set @Lakhs = 'Lakhs '
set @Crore = 'Crore '
set @Crores = 'Crores '
set @HundredAnd = 'Hundred and '
if len(@Num) = 1 -- One
begin
set @Return = testuser.fn_GetTextValue_arif(@Num)
end
else
if len(@Num) = 2 -- Ten
begin
set @Return = testuser.fn_GetTextValue_arif(@Num)
end
else
if len(@Num) = 3 -- Hundred
begin
set @Return = testuser.fn_GetTextValue_arif(substring(@Num,1,1)) + @Hundred
if substring(@Num,2,2) <> '00'
set @Return = @Return + 'And '
set @Return = @Return + testuser.fn_GetTextValue_arif(substring(@Num,2,2))
if substring(@Num,2,2) = '00'
set @Return = testuser.fn_GetTextValue_arif(substring(@Num,1,1)) + @Hundred
end
else
if len(@Num) = 4 -- thousand
begin
set @Return = testuser.fn_GetTextValue_arif(substring(@Num,1,1)) + @Thousand
if substring(@Num,2,1) <> '0'
set @Return = @Return + testuser.fn_GetTextValue_arif(substring(@Num,2,1)) + @Hundred
if substring(@num,3,2) <> '00'
set @Return = @Return + 'And '
set @Return = @Return + testuser.fn_GetTextValue_arif(substring(@Num,3,2))
if substring(@num,2,1) = '0' and substring(@num,3,2) = '00'
set @Return = testuser.fn_GetTextValue_arif(substring(@Num,1,1)) + @Thousand
end
else
if len(@Num) = 5 -- Ten Thousand
begin
set @Return = testuser.fn_GetTextValue_arif(substring(@Num,1,2)) + @Thousand
if substring(@Num,3,1) <> '0'
set @Return = @Return + testuser.fn_GetTextValue_arif(substring(@Num,3,1)) + @Hundred
if substring(@num,4,2) <> '00'
set @Return = @Return + 'And '
set @Return = @Return + testuser.fn_GetTextValue_arif(substring(@Num,4,2))
if substring(@num,3,1) = '0' and substring(@num,4,2) = '00'
set @Return = testuser.fn_GetTextValue_arif(substring(@Num,1,2)) + @Thousand
end
if @Dec <> '.00'
set @Return = @Return + 'And ' + testuser.fn_GetTextValue_arif(substring(@Dec,2,2)) + 'Paise '
select '$' + @Return + 'Only' as 'MoneyInWords' ---> Retrun value...
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
