httpRuntime – Increase file size limit from 4 MB to bigger size in ASP.NET

I wrote an article on how to upload image to SQL Server database BLOB fileld and retrieve it back. You can refer that article at:

http://www.sqlhub.com/2009/03/image-store-in-sql-server-2005-database.html

After trying to implement this functionality, one of my readers has observed that he can’t upload the file big file. He came to me for solution. Actually in ASP.NET you can upload file with maximum size of 4MB. That is the default settings. Than how could you upload bigger file than 4096 kb? Isn’t there any way around? Because this is somehow very common need.

Well, they is a will, there is way!!! You can set maximum file size with httpRuntime tag. You can set this at machine.config or web.config but I insist, not to touch machine.config file as it has web server wide effect, you should set it in web.config so that it affect one website or one subdirectory in which web.config reside.

You have to add one line under <system.web> element of your web.config. The line is

<httpRuntime maxRequestLength=”20480″ />

This will increase file limit from 4 MB to 20MB.

Enjoy!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Find user in all database with Sp_MSforEachDB in SQL Server 2005

I just assisted one person in one of the forum who wanted the list of all databases which are having user “Guest”. Since this is something needs to iterate between all available databases in SQL Server except “Master” and “TempDB”, I thought to do it by undocumented stored procedure available in Microsoft SQL Server 2005, Sp_MSforEachDB.

Let us see how I did it.

EXEC sp_MSforeachdb

‘if ”?” <> ”tempdb” and ”?” <> ”master”

begin

USE ? SELECT name,db_name() databasename FROM sys.sysusers

WHERE name = ”Guest” ORDER BY name

end

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Date calculation with CTE in SQL Server 2005

One of my close friends is working on one project for Construction Company recently as a free lancer, when I visited his home yesterday, he was calculating days in C#, since I believe to manipulate data more in SQL Server I helped him in this situation and we did calculation in SQL Server itself.

Here is small part of script the script with one small situation. In big construction company, they might have many contractors to work on site; they might have one table which stores information about contractor and their start and end date. Now, I want to calculate which contractor has worked how many days in particular month. Well, it seems pretty easy, isn’t it?

You can use date difference function and sum it up, you are done. Right? NO, it won’t give you proper picture, for example if one contractor has worked from 27-Jan-09 to 5-Feb-09 than date function will display 9 days in January month but what I want is 4 days in Jan and 5 days in FEB. Let’s have a look at how to solve this by CTE, there may other ways also, if any of my reader new that, kindly give it here so that other readers can find few other ways to do the same task.

–create table for demo

create table site

(

contractor varchar(20),

startDate datetime,

endDate datetime

)

–have some date for testing,

INSERT INTO Site

SELECT ‘Ritesh’,‘2009-02-01’,‘2009-02-07’ union all

SELECT ‘Ritesh’,‘2009-02-15’,‘2009-03-15’ union all

SELECT ‘Ritesh’,‘2009-01-01’,‘2009-01-14’ union all

SELECT ‘Rajan’,‘2009-02-25’,‘2009-03-05’ union all

SELECT ‘Rajan’,‘2009-03-11’,‘2009-04-01’

–cte as a powerful solution of this situation.

WITH CTE AS

(

select contractor,startDate, endDate, startdate totDate from site f

union all

select s.contractor,s.startDate, s.endDate, s.totDate+1 from cte s

where s.totdate+1<s.enddate

)

select contractor,year(totdate) as ‘year’,month(totdate) as ‘month’,count(*) as ‘TotalDays’ from cte

group by contractor,year(totdate),month(totdate)

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Split Value with System.Text.RegularExpressions.Regex.Split in C#

There are many ways to split the value in C# but the shortest yet efficient way is to use Split method of RegularExpression.Regex class. Let us see how it works. I will show you this example in Windows Application using C# but you can use same concept in ASP.NET with C#. If you want to use it in VB.NET than minor syntax change should be made in the function but the concept, class and method will remain same.

Let us one new Windows Application with C# and open code windows of FORM after creating one Label in windows FORM.

private void Form1_Load(object sender, EventArgs e)

{

label1.Text = “”;

string strTest = “1!800!200,19!1,0!2!!114”;

string[] strSplitedValue = returnSplittedArray(strTest, “!”);

foreach (string str in strSplitedValue)

{

if (str.Length > 0)

{

label1.Text += str;

label1.Text += “\n”;

}

}

}

public static string[] returnSplittedArray(string fullString,string separator)

{

string[] splitArray;

splitArray = System.Text.RegularExpressions.Regex.Split(fullString, System.Text.RegularExpressions.Regex.Escape(separator));

return splitArray;

}

That’s it, you are done, returnSplittedArray is very small and handy function for anyone.

Happy Programming!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

10% discount in Microsoft Exam and free second chance on failure (MCTS, MCITP, MCPD)

My friend, philosopher and guide Mr. Pinal Dave wrote an article about 10% discount in all Microsoft exams and get second chance on failure at http://blog.sqlauthority.com/2009/01/29/sqlauthority-news-microsoft-certification-exam-discount-code-free-second-chance-mcts-mcitp-mcpd/ on January 29, 2009. This offer is extended till April 30, 2009, you can use code described in that article. So, if you guys are planning to appear for Microsoft examination than hurry. What are you waiting for now???!!!! Go to nearest center and register as soon as possible.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

T-SQL Puzzle for Update in SQL Server 2005 second method

I wrote one article for showing power of simple UPDATE statement in critical situation. You can refer that article at

http://www.sqlhub.com/2009/04/t-sql-puzzle-for-update-in-sql-server.html

After reading that article, one of my blog’s regular readers Rashmika sent me one script to do the same task. Herewith, I am giving the script by given Rashmika. I have used simple UPDATE statement and she has used new feature introduced from SQL Server 2005 CTE. I always like to publish comment, concern, query or solutions given by my readers.

Note: If you have not read my previous article on this topic than kindly read it to understand more.

Create Table SQLPuzzle

(

ChildID Int,

ParentID Int,

SortOrder Int

)

–data

INSERT INTO SQLPuzzle VALUES (1,1,5)

INSERT INTO SQLPuzzle VALUES (2,1,10)

INSERT INTO SQLPuzzle VALUES (3,2,25)

INSERT INTO SQLPuzzle VALUES (4,3,10)

INSERT INTO SQLPuzzle VALUES (5,2,40)

GO

with cte as

(

SELECT ROW_NUMBER() OVER( ORDER BY Parentid ) AS ‘rownumber’,

ROW_NUMBER() OVER(PARTITION BY Parentid ORDER BY ChildID ) AS ‘Occurance’

FROM SQLPuzzle

)

Update SQLPuzzle Set SortOrder = cte.Occurance from

cte

inner join SQLPuzzle t on t.ChildID = cte.rownumber

This solution works perfectly fine. After running above query I thought to see performance difference between two script and here is the difference, my first solution is much faster but the script of Rashmika is really good example of CTE.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

error fix: The EXECUTE permission was denied on the object sp_send_dbmail, database ‘msdb’

Today I am in a mood to fix the error 😉

After reading my article at http://www.sqlhub.com/2009/04/email-from-insert-trigger-with.html one of my team members has tried to send an email from our production server and faced the error:

The EXECUTE permission was denied on the object ‘sp_send_dbmail’, database ‘msdb’

He comes to me for the solution as I am his handy reference than why he should even go to google J

Anyway, the main cause of this error is either your user does not exist in msdb database or it is not a member of DatabaseMailUserRole. How can we fix this?

If you have user exist in msdb database than simply run following script.

EXEC msdb.dbo.sp_addrolemember @rolename = ‘DatabaseMailUserRole’, @membername = ‘ritesh’

If your user doesn’t exist in msdb database than first add user to msdb and then try to execute above command like:

use msdb CREATE User[ritesh] FOR LOGIN [ritesh];

EXEC msdb.dbo.sp_addrolemember @rolename = ‘DatabaseMailUserRole’, @membername = ‘ritesh’

Hope this helps!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com