Wednesday, December 5, 2012

Sorting For Non Programmers With Report Builder

I have one primary user of the system I work on.  There are several user, but this one drives it and has a lot of need for various different reports.  It takes a long time to gather the requirements for each one, write a program to get it up on our web site, test, and deploy each one.

I have been looking for a way that he can gather and aggregate the data himself.  He can play with the data and turn it this way and that way with out getting a developer involved, other than training and supporting the product.

Another developer mentioned that he had heard about SQL Reporting Services (SSRS).  I looked into it and it still seemed like there would have to have a developer involved, since the tools are so complicated.

As I looked into SSRS I came across Report Builder.  It appeared as though each iteration of this product got a lot better.  I downloaded version 3.

It has a wizard for creating the report.  All of that is pretty straight forward, except two things.  I couldn't find a way to do sorting of the report through the wizard and the adding fields to the report seemed a little odd, but they said it is like a pivot table in Excel.  I don't know anything about pivot tables, but my user uses Excel a lot.

I searched and searched online for how to change the sorting and nothing was steering me into something simple for the user.  The best thing I found in my exploration was just adding an Order By clause to the SQL it generates.  That didn't seem great for a user, but I was almost willing to deal with the training for that.

Then I went through the program right clicking on things.  I found that I could pop up the properties window for the table that was the report and in there was a property called Sort Expression.  I had to right click on the grid and choose to select the grid and then I had to go to the View menu and select the properties check box.  Then that brought up the properties box.

It appears to be done in the report itself and not the SQL, because it doesn't appear to change the underlying SQL.

Saturday, November 10, 2012

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.

Wednesday, October 24, 2012

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.

Friday, September 21, 2012

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

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
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.

Monday, September 3, 2012

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

Friday, July 27, 2012

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( == "block")
 = "none";
 = "block";

Thursday, July 26, 2012

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.

Thursday, June 21, 2012

Have You Ever Done Any Debugging?

A while ago I was contacted by a recruiter.  One of the screening questions was, "Have you ever done any debugging?"

Maybe there is some type of software development that I am not familiar with where you don't debug code.  In my definition of software development debugging is mostly the main job.  Not only are you debugging the code you write and the code others have written, but you also have to debug the users process and perceptions.

Even if you count out debugging the user, I can't imagine why someone would list debugging as a requirement for a software developer, since it is implied in the title.

I also find it somewhat ridiculous that one that is sent to find candidates for a programming job hasn't been trained enough to know that programmers debug by definition.  It makes the recruiter look less than competent.  It makes the recruiting company look bad.  It also makes the company that is looking for a programmer look like they don't have it together.  Quite likely the company looking to hire someone is perfectly fine, but the recruiter hasn't been trained properly to be able to find candidates.

What's In a Name

Over the last 15-20 years of my programming career my title has changed all over the place while doing essentially the same job.

In some jobs I have been titled a programmer.  In others an application developer.  Still others I was called a software developer.  One employer called me a software engineer.

The engineer title was a little much for what was going on.  Even the title architect seems a bit much.  Perhaps there are those that get degrees in software architecture or software engineering, but it kind of leave my paradigm of what engineers and architects are.

The name 'Request' does not exist in the current context

I receive this message today in Visual Studio when trying to get at the Request object of a web page and it stumped me for a bit, since it did not represent what the actually problem was.  "The name 'Request' does not exist in the current context"

I had this code in the Page_Load event:
     if (Request("myFlag")=="1") DoSomething();

Since I had seen similar code elsewhere and I was in a Page_Load I was pretty sure I had the Request object available to me.

I tried going at the base page.  I tried using "this".  None of them worked.

As I flipped things this way and that I had something pop up that made me realize I had wrapped the parm of the Request object in parenthesis instead of brackets.  Once I changed that it fixed the problem.
    if (Request["myFlag"]=="1") DoSomething();

The error message was misleading.  While a method named Request was not in the current context, the problem actually was that I was using an object like a method.  I will admit that I probably made this mistake because I bounce back and forth between VB and C#.  Perhaps developers that exclusively do C# and Java would not be tripped up by this.

Tuesday, April 17, 2012

How To Backup a Blog

For a long time I used a utility called Blogger Backup, which had been uploaded to CodePlex.  It never really got all the kinks worked out of it and had not been updated for a long time, but it was good enough.

I was feeling like posts were disappearing from my blog, but I couldn't put my finger on a specific event or post that was disappearing I started to suspect maybe a bug in Blogger Backup, but I had no proof and still have no proof to this day.  I started looking around for other options.  Every time I would search I just kept getting pointed back to Blogger Backup.

Finally I began to wonder if Google hadn't built in a backup utility into blogger or an export or something after all these years.  I did some searching and found that there was something in Basic Settings.  The post must have been old, because there was nothing there dealing with export.  I happened upon the Other category under Settings.  There was the option that they had described in the post which stated it should be under the Basic section of Settings.

Here's what you do.

  1. Log into blogger.
  2. Go to the listing of your blogs.
  3. Click on your blog.
  4. On the left click the Settings item.
  5. It will expand and in there click the Other item.
  6. You will be presented with several things in here.  The one at the top is Blog Tools.
  7. In there you will see Import Blog, Export Blog, and Delete Blog.
  8. Click Export Blog.
  9. You will be prompted to download an XML file.  Click download and then give it a location and a name.
I would like a way to download all of my blogs in one click, but for now you have to do one by one.  They could perhaps give us the option to download all and let us choose a naming pattern for the files it would generate.  The advantage of doing them one by one is that you can uniquely and specifically name each blog's file.

So if you need to backup your blog, which everyone really should be backing up, you can use the old Blogger Backup or you can use the Export Blog feature from with Blogger to export an XML file.

Thursday, March 29, 2012

Enums in Visual Basic 6

It appears that VB6 allows you to declare enumerations in the code anywhere just like a sub or function.  I declared an enum at the bottom of a bas file.  I declared a function somewhere about that in the code and passed it a parameter of the type of the enum.  The code failed that it did not know the type I was using and the intellisense did not see the enum either.

I moved the enum definition to the top of the bas file at the end of the variable declarions.  At that point the intellisense was happy and so was the compiler.

I did not test it far enough to find out if the error I got was just because the compiler got to my using the type first before it got to the possible error of defining the enum in the wrong place.

Detailed Instructions How To Write a Select Statement in SQL Server (Transact SQL(T-SQL))

Basic Select Statement
A Select statement returns a set of records filtered by the criteria provided.  If not criteria clauses are specified, then all records in the table is returned.

Example returning all records from a table called Employees.

Select * From Employees.

The Asterisk
The asterisk specifies that all fields in the table will be returned.

Specifying Fields
To return only specific fields omit the asterisk and list the field names separated by commas.

Example Example returning all records from a table call Employees, but only the FirstName and LastName fields.
Select FirstName, LastName  From Employees

The Where Clause
The Where clause specifies criteria for which records will be returned.  Only records that meet the Where criteria will be returned.

Example of a Select statement with a Where clause that specifies to only return records with the first name of Samuel.
Select *
From Employees
Where FirstName = ‘Samuel’

Note that strings are enclosed in single quotes, not double quotes as in some programming languages.

The Like Operator and Wildcards
The Like operator and wildcards are used to match parts of the data in a field.  One would use Like in the place of an equal sign.  Previously we used the equal sign to return all records with a FirstName field equal to the string ‘Samuel’.  If we wanted to find any records with a FirstName field that start with the string ‘Sam’ we would do the following.
Select *
From Employees
Where FirstName Like ‘Sam%’

The character % matches any string of any length.  It also matches zero length strings.
The _ matches a single character.

The Order By Clause
The Order By clause sorts the records that are returned by a Select statement.  One may specify more than one field to be ordered on separated by commas.  The first field will be the one that is sorted first and the second field will be sorted within the first.  The third will be sorted within the second within the first and so forth for as many fields as one has chosen to order on.

Example of an Order By clause sorting records by LastName and FirstName.
Select *
From Employees
Order By LastName, FirstName

One may use Asc or Desc after the field name, but before the comma to specify a descending or ascending sort on a specific field.  If neither is specified ascending(Asc) is the default.

The Group By Clause
The Group By Clause tells SQL Server how to group the data together when using aggregate functions.  Aggregate functions perform calculations on each value in a field in a database table and returns a single value.  A single value is returned for each grouping.  For example, if the Group By clause says to group on a person’s name that every record that has that person’s name on it will be rolled into one value.  Each person’s name will return one value for the field being aggregated and only one record representing all records with that person’s name.
The following sections will describe some of the aggregate functions that one can use with the Group By clause.

One may specify more than one field to be grouped on separated by commas.  The first field will be the one that is grouped first and the second field will be grouped within the first.  The third will be group within the second within the first and so forth for as many fields as one has chosen to group on.

Note that all fields in the Select to be returned from the query which are not aggregate functions must also be in the Group By clause.

Aggregate Functions
Sum is used to add all values together in a field in all records that meet the criteria of a Group By clause.

Example of a Select statement with a Sum aggregate function summing the field HourlyWage and a Group By clause which groups on the LastName field of the Employees table.
Select LastName, Sum(HourlyWage)
From Employees
Group By LastName

Ave Sums all values in a field in all records that meet the criteria of a Group By clause and divides them by the number of records in the grouping.

Example of a Select statement with a Avg aggregate function Averaging the field HourlyWage and a Group By clause which groups on the LastName field of the Employees table.
Select LastName, Avg(HourlyWage)
From Employees
Group By LastName

Returns the number of records in the grouping specified by the Group By clause.

Example of a Select statement with a Count aggregate function Counting the number of records in a table.  Note that no Group By is given, so all records are counted and in sub groupings.
Select Count(LastName) From Employees

Inner Join, Outer Join, Huh?
Inner Join
An Inner Join joins two tables together on a common field and only returns those records where the common fields match.

An Outer Join joins two tables together on a common field and returns those records that match and also records that do not match.  To determine which table’s fields are returned when there is no match is determined by the modifier Left or Right or Full.

Left Join.  Right Join.  Go Left.  Go Right.  What is Going On?
The Left, Right, and Full modifiers are only available with the Outer Join.

A Left Outer Join returns all records from the table on the left side of the equals sign.  The records that match the sort criteria will have the values from the fields from the other table as well.  When they do not match, the values for those fields in the right table will be null.

Example of a Select statement that joins two tables and all records from the table on the left side of the equals sign being returned.  All records will be returned from the Employees table and only the records from the Address table where the AddressId matched in the Employees table.
Select *
From Employees
Left Outer Join Address On Employees.AddressId = Address.AddressId

A Right Outer Join returns all records from the table on the right side of the equals sign.  The records that match the sort criteria will have fields from the other table as well.  When they do not match those fields will be null.

Example of a Select statement that joins two tables and all records from the table on the right side of the equals sign being returned.  All records will be returned from the Address table and only the records from the Employees table where the AddressId in the Address table matches the AddressId in  the Employees table.
Select *
From Employees
Right Outer Join Address On Employees.AddressId = Address.AddressId

A Full Outer Join returns all records from both tables.  The records that match the sort criteria will have fields from both tables.  When they do not match fields from the table that could not match will be null.

Example of a Select statement that joins two tables and all records from both tables being returned.  Nulls will be returned for records that do not match the join field.
Select *
From Employees
Full Outer Join Address On Employees.AddressId = Address.AddressId

The Having Clause
The Having clause is similar to the Where clause, but is for use with the Group By clause and aggregate functions.

Example of a Select statement that filters on the Count of employees that have the same last name.  It only returns last names that appear on more than one record.

SELECT LastName, Count(LastName)
FROM Employee
HAVING Count(LastName) > 1
ORDER BY LastName;

One can also create subqueries within SQL queries.

Example of a subquery that finds the date that an employee’s address was change last.
Select Employees.FirstName, Employee.LastName,
       (Select Max(Address.ChangeDate)
        From Address
        Where Address Employees.AddressId = Address.AddressId) As MostRecentAddressChange
From Employees

Aliasing Tables and Fields
One can change the name that they reference a table as in the query or the name of the field that is returned from the query by using aliases.

Example of using Aliases for tables and field names.
Select e.FirstName As FName, e.LastName As LName, a.ZipCode As Zip
From Employees As e
Full Outer Join Address As a On e.AddressId = a.AddressId

Notice that when I reference the tables in the query I only need to use their aliases, which has the potential to make it shorter and more readable.  In my case I just use a single letter.  Using a little longer Alias might be more readable, without having to type out the entire table name.

Monday, March 26, 2012

SQL Server Acronyms

I have titled this post SQL Server Acronyms, but I think several of these acronyms refer to SQL in general and not a specific implementation like Microsoft SQL Server.

I had a job interview recently and the interviewer quized me on some SQL Server acronyms.  I was not as prepared as I should've been.  I had heard several of them, but not committed them to memory.  I write the code and don't always remember the catch phrases.

I have created a list below.  Some the interviewer asked about and some I came across as I was researching the others.

  • SRS/SSRS - SQL Reporting Services/SQL Server Reporting Services
    • I have seen this referred to as SRS in some places and SSRS in others.
    • It is a Microsoft reporting engine.
    • It competes with Crystal Reports and other reporting technologies.
    • The reports are defined with RDL - Report Definition Language.
  • SSIS - SQL Server Integration Services
    • Performs data migration tasks.
  • SSB - SQL Server Service Broker
    • Provides message queuing.
  • DTS - Data Transformation Services
    • Use to load, extract, and transform data in and out of a database.
  • DML - Data Manipulation Language
    • Things like Update, Insert, and Delete.
  • DDL - Data Definition Language
    • Things like Alter, Create, and Drop
  • DCL - Data Control Language
    • Things like Grant and Revoke statements
  • TCL - Transactional Control Language
    • Things like Commit and Rollback
  • DQL - Data Query Language
    • Some resources I have been reading say DQL is Select statements and others say Select is part of DML.

Truncate Table SQL Server

I was asked in an interview not long ago what Truncate did in SQL Server.  I had remembered seeing a Truncate statement in the code of the project that I am currently working on, but couldn't remember what we used it for.  I came home and read up on it.

The statement is Truncate Table [table name].

It appears to be the equivalent to the DELETE statement without a where clause.  Basically it dumps all your records in the table.  This is handy for temp tables that you might be using and reusing for heavy lifting.

Truncate Table is faster than Delete.  It is likely that truncate doesn't pick each row, but just dumps them all.  Perhaps moving a record pointer to the top of the table and letting all the other memory be reclaimed.  It also doesn't appear to do all the logging that Delete does.  It you need logging you should stick with Delete and take the performance hit.

In many ways the end result of Truncate Table is the same as executing a Drop Table and then a Create Table with the exact same table layout.

There seem to be limitations on the Truncate command that it can't be used if the table has a foreign key constraint.  The Delete statement does not have that limitation.