Authentication Popup When Trying to Access Files In Reports Folder

We are in the process of upgrading to a new server.  Our old one was a Go Daddy dedicated server, which we managed ourselves.  We are having Go Daddy migrate our web site to a newer, faster dedicated server.

As we went through the testing of the new server we found that pages that were in a directory called reports were popping up an authentication box in the browser whenever we would try to browse to them.

We tried checking the permissions on that folder and files and everything seemed fine.  We added permissions to try to help, but it did not fix it.

We tried moving the files to another existing folder and that worked, but we didn't want those files there.

We tried creating a new folder, moving the files there, deleting the old folder, and renaming the new one to reports.  We thought that perhaps the directory had gotten corrupted or something, but it didn't fix it.

We renamed the folder to report and that fixed it.  At first we thought the server didn't like the folder name reports.  They we got to thinking maybe it doesn't like the routing.  We are routing requests that come into server/reports/ReportName to server/reports/ReportName.aspx.

We tried just changing the routing, but that did not work.

We have now left it alone, but our newest theory is that maybe the new server has Reporting Services installed on it and it grabs requests to server/reports for itself.

Update 1-29-2013
I started getting the Windows Authentication popup again today.  This time what I had done was copy our website to another fold, create a new web site, and point the new site to the copy.  When I tried to bring up the home page I got the Window log in box.  I tried various different settings in IIS and also security settings on the folder with not luck.

I finally looked at the Authentication section of the website in IIS.  You get to this by clicking on the website in the left pain of IIS Manager, then clicking Authentication under the IIS section in the right pain.

I looked and saw that Anonymous Authentication was enabled, which seemed like the right value, so I left it alone for a while.  I went back to it later and right clicked on it.  Turns out there is an edit sub menu.  I clicked it and it gave me a screen to pick which user's identity to run under.  It was set to specific user and had a user name selected.  I'm guessing I could've picked another user or went somewhere else and given that user more rights.

Instead what I did was told it to use the application pool identity.  Once I did that I tried my website again and no more being asked for login credentials.  Not sure what the ramifications of this are, so if you find that it is insecure to do that, then maybe the picking a specific user or giving the default user sufficient rights is what you want to do.

Server Error 500.0 ASP.Net 4, Windows Server 2008 R2, IIS 7.5

I set up a new server to be a test environment for our production server.  When I installed it and tried to run the web site I got a .Net error that was telling me that my config file wanted version 4 of the .Net frame work.

Unrecognized attribute 'targetFramework'. Note that attribute names are case-sensitive.

I found that I needed to go to the application pool and set its >Net framework version to 4.0. It was set to 2.0

When the page loaded I got "HTTP Error 500.0 - Internal Server Error" in the browser.

I checked the event log and got "An application has reported as being unhealthy."

I found solutions online that said stop and restart the application pool.  That did not help.

I found solutions that talked about running a command from the .Net folder that would repair .Net.  I think the program they suggested running was aspnet_regiis.exe.  That did not help.

I looked and saw that Microsoft .Net Framework 4 Extended was missing in Programs and Features in Control Panel.  I downloaded and installed the full version of the .Net Framework.  I think I may have only installed the Client Profile.  Once I did the install it added Microsoft .Net Framework 4 Extended, but did not fix the problem.

After more research I uninstalled both Microsoft .Net Framework 4 Extended and also Microsoft .Net Framework 4 Client Profile and reinstalled the full version of the .Net Framework version 4.

After that i got a different error, but I think that has to do with the configuration of my code.  At least now it appears to be at least getting to .Net and .Net is trying to process the page.  I think the previous error was indicating that IIS was not able to pass off to .Net.

A friend of mine has the theory that I had installed .Net before IIS and that is why it was confused.  He said that IIS needs to be installed first for ASP.Net to get configured correctly.  Not sure if that is what happened here or not, but it does make sense what he is saying.

Stripping the Time Off of a DateTime Field in SQL Server

I find myself occasionally having to return just the date portion of a DateTime.  Every time I have to remember what I did the last time.

Here are a few solutions I have tried.  You can apply them to whatever your need is.

Let's say you have a field in your table called DateOfExecution and that date in one record is 2012-09-01 15:14:55.540

Method 1 - Convert to a NVarChar and Format It.
CONVERT(nvarchar(20),  DateOfExecution  , 101)

This method returns 09/01/2012.  This lets you just compare the date by itself, but you have to compare it as a string.

Method 2 - Cast It to a Float, Take Off the Decimal Portion With Floor, and Cast It Back to a DateTime
CAST( FLOOR( CAST(  DateOfExecution   AS FLOAT ) ) AS DATETIME )

This method returns 2012-09-01 00:00:00.000, leaving a time portion there, but the time portion is all zeros.  This can be useful if you want to get two dates into the same time and compare the entire DateTime.


Method 3 - Pull the Year, Month, and Day Out of the Date as Strings, Concatenate Them Together, and Cast Them Back to a DateTime
CAST(
(
STR( YEAR(  DateOfExecution   ) ) + '/' +
STR( MONTH(  DateOfExecution   ) ) + '/' +
STR( DAY(  DateOfExecution   ) )
)
AS DateTime
)

This method is more complicated and also returns 2012-09-01 00:00:00.000.

Method 4 - Pull the Year, Month, and Day Out of the Date as Strings, Trim the Individual Parts, and Concatenate Them Together

rtrim(ltrim(STR( YEAR(  DateOfExecution   ) ))) + '/' +
rtrim(ltrim(STR( MONTH(  DateOfExecution    ) ))) + '/' +
rtrim(ltrim(STR( DAY(  DateOfExecution   ) )))

This method returns a string that looks like this 2012/9/1.  You could apply some formatting and/or concatenate the values in a different order to make it look the way you want it to.

Sharing SQL Server (TSQL) Temp Tables Across Stored Procedures

I had a query that I created for a report.  Another report needed to display that report and use the data from it to display some other reports on the same page.

In order to pull in data from different sources and aggregate it at the end and to remove duplicates I dumped all the records I was accumulating into a temp table.  The problem I had was getting the information from that temple table to the other stored procedure that was creating the other reports.

At first I was using a local temp table using the #TableName syntax.  This was only available to the current stored procedure, so I had to change that to a global temp table using the ##TableName syntax.  After that the second stored procedure could use the temp table information.  Unfortunately, since it was global, even one who ran it was using the same information and were stepping all over each other.

I next decided that I would not drop the table at the top of the first store procedure, but create the records storing SPID in field in the temp table, so that I would only see my records and not some else's.  As I thought about it more I thought it might be best to use the UserId of the currently logged in user.  This would allow me to delete all of my records from previous runs without disturbing others' records.

From what I have seen the global temp table drops itself after some period of inactivity, so that is good too, but as a precaution I am considering also storing a datetime field on the records I am creating as well as the user name, so that I can delete records older that a certain number of minutes, since the report is run and looked at and the temp data is no longer needed.

So that I can dynamically create the temple table from a query I put in code to check for the temp table existing.

IF OBJECT_ID('tempdb..##NewSales') IS NOT NULL

If it does not exist then I do

INSERT INTO tempdb..##NewSales

SELECT {FieldList}

If it does exist then I do

SELECT  {FieldList} INTO tempdb..##NewSales

Putting Javascript in an XSLT Stylesheet

There was some code from a previous developer that I had to edit. He had used XML and an XSLT stylesheet to display a form to the user. I needed to add some javascript to hide and show a div when an existing button was clicked. There was an <xsl:template tag and I had to put the script tags and the javascript in there.

<xsl:template match="/*">

    <script language="JavaScript" xmlns:msxsl="urn:schemas-microsoft-com:xslt" >

      function toggle(ID)

      {

      var element = document.getElementById(ID);

      if(element.style.display == "block")

        {

          element.style.display = "none";

        }

      else

        {

          element.style.display = "block";

        }

      }

    </script>

Redisplay Dynamic Controls Every Time ASP.Net

I had some controls that I was dynamically creating.  When I would click my button to postback I was finding that the code had no idea about those controls, even though I could see them in the page I just posted.  The controls were also still there when the code returned to the web page.

It turns out that you have to recreate the dynamic controls every time.  I avoided this answer, since recreating the control I thought would reinitialize the values and I would lose what the user has chosen on the page.  It turns out the viewstate figured it out and just needed to the control to be created each time for it to have a place for it to save its values.

It looks like Page_Load and Page_Init both work for this purpose.

mm is not MM

Even though when you call a ToString() on a DateTime the day and year format characters are dd and yyyy, respectively, that does not mean that the month part is mm.  mm is already used to format for minute.  I spent a while today trying to figure out why I was getting a number that was beyond the range of 1-12 for the mm portion of my date when using ToString(mmddyyyy).  You have to use MM for your month portion.