Problem uploading large files in ASP.NET

If you try to upload large files you might get an exception like

HttpException: Maximum request lenght exceeded.

This problem occurs because the default value for the maxRequestLength parameter in the section 

of the machine.config or Web.config file is 4096 (4M).

So any file with a size bigger will fail.

However I think that the max size that you can write here is 2G 2097151

Some info can be found here: http://support.microsoft.com/default.aspx?scid=kb;EN-US;295626

So to change that for 512mb use something like:

<configuration>

    <system.web>

        <httpRuntime maxRequestLength="524288" /> 

    </system.web>

</configuration>

Crystal Reports in Windows Azure

If you ever wonder, if Crystal Reports can be used on the cloud in Windows Azure, well the answer is you can.

These two links provides some guidance on this matter:

http://scn.sap.com/people/coy.yonce/blog/2011/05/02/sap-crystal-reports-and-microsoft-sql-azure (this one is in English)

http://blogs.msdn.com/b/luispanzano/archive/2011/04/11/crystal-reports-en-windows-azure.aspx (this one is in Spanish but Bing and Google Translate do a nice work here ;) )

SQL SERVER Find and Kill User Session

I found this excellent post from Aasim adbullah

http://connectsql.blogspot.com/2010/12/sql-server-find-and-kill-specific-user.html

SELECT DISTINCT
        name AS database_name,
        session_id,
        host_name,
        login_time,
        login_name,
        reads,
        writes
FROM    sys.dm_exec_sessions
        LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
        INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE   resource_type <> 'DATABASE'
--AND name ='YourDatabaseNameHere'
ORDER BY name

Insert into select * for huge columns lists

It is very easy to insert rows from one table to the other using an insert into and select * statement but to use this statement you need sometimes to provide the columns list and that is boring :(

So I quick way to do that is use a T-SQL snipped like this:

DECLARE @columns varchar(max)

SELECT @columns = Coalesce(@columns + ', ', '') +  column_name
FROM   information_schema.columns
WHERE  table_schema = 'dbo'
AND    table_name = 'Account'
ORDER
    BY ordinal_position

PRINT @columns

See SQL Server Express from other computers

When I use SQLServer 2008 Express for my development tests, I always forget which things I have to do in order to make my SQL Server 2008 Express instance available to other machines over the networks.

So to not forget that again this are the things that you have to check to be able to acces sql server express from other machines:

 1. Go to the Start Menu\All Programs\Microsoft SQL Server 2008 R2\ and run SQL Server Configuration Manager

2. In the SQL Server Configuration Manager Window, expand the tree on the left and expand the SQL Server Network configuration element on the tree. Make sure that at least TCP/IP protocol is enabled. 

3. Now Click on the SQL Server Services element on the tree and make sure that the SQL Server Browser service is running. It is needed in order to make other computer able to see your server.

Migrating Access to SQL Server

If during a VB6 migration you decide o take a step forward and move your databases to MS SQL

don't worry. The SQL Server team has a tool just for that.

I just don't know why it is so hard to find it in google. But the appropiate link is:

http://blogs.msdn.com/b/ssma/p/access.aspx

Also a recent article in Teched goes into all the gory details: http://technet.microsoft.com/en-us/magazine/hh334645.aspx

Good Luck and don't hesitate to contact us for any doubts.

Easy way of Tracing open Data Reader

Finding DataReaders that are not being close in an .NET application can be something difficult.

Thats why I use this trick to detect which DataReaders where not close.

1. First add a new code file to your project.

2. Enter this code:

public static class SqlReaderTracer
	{
		static Dictionary<WeakReference,System.Diagnostics.StackTrace> traceInfo = new Dictionary<WeakReference,System.Diagnostics.StackTrace>();
		public static System.Data.SqlClient.SqlDataReader ExecuteReaderEx(this System.Data.SqlClient.SqlCommand command)
		{
			var stack = new System.Diagnostics.StackTrace(1, true);
			var reader = command.ExecuteReader();
			traceInfo.Add(new WeakReference(reader), stack);
			return reader;
		}
		public static void PrintTraceState()
		{
			Console.WriteLine("*******SQL Trace Results************");
			foreach (var reader in traceInfo.Keys)
			{
				if (reader.IsAlive)
				{
					var traceInfoFrame =  traceInfo[reader].GetFrame(0);
					var methodWhereItWasCreated =traceInfoFrame.GetMethod().DeclaringType.FullName + "." + traceInfoFrame.GetMethod();
					Console.Write("Reader alive created in " + methodWhereItWasCreated);
					if (traceInfoFrame.GetFileName() != null)
					{
						Console.Write(string.Format("at {0} ({1},{2})",traceInfoFrame.GetFileName(),traceInfoFrame.GetFileLineNumber(),traceInfoFrame.GetFileColumnNumber()));
					}
					Console.WriteLine();
						
				}
			}
		}

	}

And then to a Find /Replace All and change all the .ExecuteReader() for .ExecuteReaderEx(). 3. Ok Now. You are all set. To test it do something like this:

class Program
	{
		
		static void Main(string[] args)
		{
			var xxx = new System.Data.SqlClient.SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\MyProjects\Phonebook.mdf;Integrated Security=True");
			xxx.Open();
			var ccc = xxx.CreateCommand();
			ccc.CommandText = "SELECT * from Phonebook";
			var reader = ccc.ExecuteReaderEx();
			xxx.Close();
			SqlReaderTracer.PrintTraceState();
			return;
		}
	}

And you will see an output like:

Using MARS or There is already an open DataReader associated with this Command which must be closed first.

This is a very strange error that you can find sometimes when working with ADO.NET.

David McKean from MSFT says:

This occurs when you have multiple DataReaders open concurrently on the same connection,
ie you call SqlCommand.ExecuteReader but don't close the SqlDataReader returned by this
method before calling it again (either on the same command or another command on the same connection).

It requires a feature called MultipleActiveResultSets which is not available in all providers.

For example SQL2000 does not support it, it was implemented starting from SQL2005.

Also .NET 2.0 must be used.

For more information about enabling Multiple Active Result Sets see: http://msdn.microsoft.com/en-us/library/h32h3abf(v=vs.80).aspx

A good recommendation to make sure that the the readers are closed is to put them inside a using statement, in that case,
no matter if an exception happened they will be closed and disposed.

If you are using SQL Server 2000, MARS is not available so you can create two different connection objects.

Another good article about this issue is: http://blogs.msdn.com/b/spike/archive/2009/08/20/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-closed-first-explained.aspx

But in general to use it is just a change in the connection string:

<connectionStrings>
    <clear />
      <add name="VasquezDB" 
         connectionString="Data Source=rvasquez;Initial Catalog=VasquezDB;
                 Integrated Security=True;MultipleActiveResultSets=Yes" />
 </connectionStrings>

Good Luck

NOTE: a good link with more details about MARS is:

http://blog.typps.com/2011/04/mars-multiple-active-result-sets.html