Archive for October, 2010

CSS Issue: 100% Height on a Div Tag

Here at Topline, I personally tend to spend most of my time writing C# code and T-SQL. In the last year or two, however, in a game of tag that I didn’t realize I was playing, I have become “it” when it comes to UI design. As a result, I have spent a lot of evenings learning CSS, and, contrary to what I supposed at the outset, I have grown to enjoy quite a bit. That being said, I am still learning and from time to time I encounter new and earth-shattering principles off CSS design that I have somehow missed before. This is especially true of using CSS for positioning and not just to change individual tag appearance.


In any case, such an epiphany happened to me this week. To give some background, while trying to add a different skin to a previously designed website for my brother-in-law, I was converting it from the dreaded table layout to use CSS Positioning. I was attempting to do a fairly standard layout which would consist of the following:

  • A Header
  • A Body
  • A Footer


A very simplified version of my HTML would look like this:

<html>
    <head>Head stuff Here</head>
    <body>
        <div id="dvContainer">
            <div id="dvHeader">Header Here</div>
            <div id="dvBody">
                <div id="dvMenu">Menu Here</div>
                <div id="dvContent">Content Here</div>
            </div>
            <div id="dvFooter">Footer Here</div>
        </div>
    </body>
</html>

Well, that all seems straightforward enough. I defined an external CSS file, added a reference to it on the page and started changing the CSS so that the entire site would look like a million bucks …. and frankly, it did. Then my whole world came crashing down on me. I had been working on the site at home using my strange little monitor. One morning when I came in to work, I decided to pull it up on my humongous monitor there just to see how it would look and I was horrified to see the result. At home, on all of the pages the content was long enough to push the footer past the bottom of my screen. At work however, the content ended up only going halfway down the page, which made the footer seem to float around in the middle of the screen as if it were trying to attack the header in some sort of ill-conceived CSS Coup d’etat.


It seemed that the problem should be easy enough to fix though, so that night I did a little research about getting the footer to stay at the bottom of the page, which is related but the subject of another post. When I implemented the fix though which, among other things, involved setting the “dvContainer” min-height to 100% and the “dvBody” style min-height to 100% in my stylesheet (along with a couple of other styles to the header and footer which I won’t go into here), the hight simply would not change.


No matter what I did, I could not get “dvContainer” to actually extend to 100% of height of the screen. Finally, I realized that the crux of my problem had to do with the way that percentage settings work in CSS. In essence, when you set something to be a percentage height, you are telling the browser that it should be XX% of whatever the tag happens to be inside of. So, if I had the following markup:

<div id="container" style="height: 250px;">
    <div id="coolStuff" style="height: 100%">Cool Stuff Here</div>
</div>

You might think that the “coolStuff” div tag would be 100% of the screen, but in actuality, the “coolStuff” div tag would only be 250 pixels tall because it is only going to be 100% of the height of the box that it is inside of. For the most part, I think that the majority of people doing CSS Development already knew this (although if you didn’t I hope that you will revere me from now on). What some people might not know … and I certainly didn’t, is that the body and the HTML tag are included in this. In other words, “body”s are people containers too.


This might sound a little circular, but the body tag is only going to be as tall as it needs to be to hold whatever is inside of it and setting whatever is inside of it to a height of 100% will only make it as tall as as it needs to be to fit inside the body which, as you recall, is only big enough to fit whatever was inside of the body before its height was set to 100% to begin with. Seriously, “Who’s on first?”


Let me try again, if you only have enough content to extend halfway down your page, that is how tall the body tag will be. Setting the height of the “dvContainer” tag to 100% didn’t do anything because it was only going to be as tall as the body and html tags which had already been set just high enough to hold all of the content on my page. The solution was to set the min-height of both the “body” and “html” tags to 100% too. After I did that viola … my container and my body tags also extended to the bottom of the page and it looks great again.


This leaves us with two “Morals of the Story”

  1. The “body” and “html” tags are actually containers too and should be treated as such. If you want anything else on the page to cover 100% of the viewable area you need to start by setting the styles correctly there.
  2. Don’t spend your spare time redesigning websites for your brother-in-law.

Tags: , , , ,


Random Error Message: “Specified cast is not valid” During an Excel Import

I’ve been working on a data import where I was using an Excel worksheet as the source and a destination of SQL Server. I was getting an error message “Specified cast is not valid” while the import was running. I had read about setting the “IMEX=1″ value in the connection string to have it treat all the columns of Excel data as strings, but it turns out there’s a bit more to it than that. This article by Joe Morrison is the best discussion of the whole issue and a workaround.

In my case, I have some freedom to tell the customer what format the data for this recurring import should be in, so I am going to try Access and see if that works better (I expect it will). It will give the customer a lot of the Excel/spreadsheet data editing capabilities in more of a real database format. (And yes, I understand that Access is not an enterprise level database system, but it does have it’s place and perfect for my needs.)

Tags: , ,


Random Error Message: Error in “presence.htc”

After upgrading a customer from MS CRM 3.0 to version 4.0, I discovered an error that kept popping up in the “do you want to report this to Microsoft box”.  The actual XML to be sent to MS included:

<ScriptErrorDetails>
    <Message>Object required</Message>
    <Line>196</Line>
    <URL>/_static/_grid/presence.htc</URL>
    <PageURL>OrgName/_root/homepage.aspx?etc=2</PageURL>
    <Function>prepareEnablePresence()</Function>
    <CallStack>
        <Function>prepareEnablePresence()</Function>
    </CallStack>
</ScriptErrorDetails>

I isolated that the problem (at least in my case) only occurs when I have Windows Live messenger loaded.

After stepping through the code, I discovered that the error is indeed on lines 196 and 197 in presence.htc, which read:

var oImgSpan = oControl.firstChild;
if (oImgSpan.tagName == "IMG")

The problem is that oImgSpan.firstChild is null.  The workaround is to have line 197 check for the existence of the null, like this:

if ( (oImgSpan != null) && (oImgSpan.tagName == "IMG") )

My disclaimer on this is that while this will stop the error from happening, it isn’t necessarily solving the root problem.  I haven’t had time to look at exactly what the code is doing.  Since it is in presence.htc and it only happens when Windows Live Messenger is running, I believe “presence.htc” is basically a behavior that is supposed to display an icon that indicates whether someone is online or not.  Where and how, I’m not sure.  Since the line in question is followed by logic to assign the src property of the image <SPAN>, it may or may not show things correctly.  If I have time, I’ll investigate further.  Right now, since my customer doesn’t allow their users to use Messenger, they won’t see the error.  I’ve advised them of the potential issue – they said leave it as is.  My work here is done.

Tags: , ,


Random Error Message: “Sys.WebForms.PageRequestManager .getInstance()._destroyTree(b)”

As part of our continuing series of posts on random error messages that are cryptic and don’t seem to explain what on earth is causing the problem, I thought that I would add this one. The offending line was as follows:

{ 
    ... 
    Sys.WebForms.PageRequestManager.getInstance()._destroyTree(b);
    ...
}

During the weekend, I was conducting some Visual Studio 2010 experiments just for fun and noticed that all of a sudden I was having my code fail at this line. Do not ask me why I was doing VS2010 experiments on my time off, I just was. What can I say … I really like VS2010. My experiment involved converting a project that we currently work on using Visual Studio 2008 over to Visual Studio 2010 just to see what would happen so we are prepared in the event that the client approves such a move. In any case, the issue was happening during an AJAX postback on a page.

If I didn’t have debugging enabled, it appeared that the postback itself (and the UpdateProgress indicator) was just going on forever. While debugging, however, Visual Studio listed the line above as where the code was failing. I use the term “line” loosely since that snippet of code is actually ensconced in an actual line of Javascript code that seems to extend on for thousands of characters. The actual error that I received was equally nondescript and so common that I won’t even reprint it here so as not to draw other Google surfers here under false pretenses.

So, I did a Google search for that particular code block and got only one result, although, if you are reading this, then now there are perhaps two results when searching for it. The one post I found, however, didn’t solve my problem. Without going into all the boring details, in the end, the reason for the failure had to do with a Telerik script manager that was on the page. In fact, if found my answer on the Telerik website (VS 2010 Bug – Notice that the line of code is the same except for the one 1 letter variable name in the “_destroyTree” call)

Now, don’t interpret this as an indictment of the Telerik controls. I use them at Topline Strategies and I love them. If you are interested, in fact, visit their website at http://www.telerik.com. But when I converted the project, either by default (or because I selected it – I don’t remember which and I’m not going to repeat the whole process to find out), it was set to target the 4.0 framework. Apparently, the version of the Telerik controls that we were using was an older one and didn’t have support for the 4.0 Framework.

This was easy enough to fix using 1 of 2 methods.

  1. Download and install the latest build of the Telerik controls in your project. This is probably the best option, but our subscription with Telerik had expired, so I was limited to the last version of the controls that was released during the subscription period. This required me to go with the second option
  2. Go into the project’s properties and change it to target the 3.5 framework (or any other previous framework that you would like). This wasn’t a bad option either since nothing that we are currently doing in the project MUST have the new functionality available in the 4.0 framework.

So, if you find this error in your code and it is driving you crazy, I hope that this little post saves you some time.

Tags: , , ,


Comparing Schema Information

During a recent purge/archive project I needed to compare two tables in different SQL Server databases to see if the schemas were the same. Basically, I was moving data from a production database to an archive database and if new fields were added or changed to any of the production tables, I needed to know and sync those changes to the related archive tables. Below are two stored procedures that I created initially in the master database to simplify the process and to allow the stored procedures to be executed from any database.

The first stored procedure (sp_GetSchema) simply retrieves the column names and other related data for the specified database table using the INFORMATION_SCHEMA.COLUMNS view (Phil Haack has a great article about Using INFORMATION_SCHEMA Views to bulletproof Sql Change Scripts). It uses dynamic SQL to construct the query because the database name needs to be included. The schema information from the production and archive tables are then compared using the second stored procedure (sp_CompareSchema).

CREATE PROCEDURE sp_GetSchema
(
    @database	NVARCHAR(128)
    ,@table		NVARCHAR(128)
)
AS
SET NOCOUNT ON

DECLARE @sql    NVARCHAR(MAX)

SET @sql = '
    SELECT  column_name
            ,CASE   WHEN is_nullable = ''YES''
                    THEN ''NULL''
                    ELSE ''NOT NULL''
                    END AS is_nullable
            ,CASE   WHEN CHARINDEX(data_type, ''decimal, numeric'') > 0
                        THEN UPPER(data_type)
                            + ''(''
                            + CONVERT(NVARCHAR, numeric_precision)
                            + '', ''
                            + CONVERT(NVARCHAR, numeric_scale)
                            + '')''
                    WHEN CHARINDEX(data_type, ''varchar, nvarchar,
                            varbinary'') > 0
                            AND character_maximum_length = -1
                        THEN UPPER(data_type) + ''(MAX)''
                    WHEN CHARINDEX(data_type, ''char, nchar, varchar,
                            nvarchar, varbinary'') > 0
                        THEN UPPER(data_type)
                            + ''(''
                            + CONVERT(NVARCHAR, character_maximum_length)
                            + '')''
                        ELSE UPPER(data_type)
                    END AS data_type
            ,ordinal_position
            ,table_name
            ,''' + @database + ''' as dbname
    FROM    ' + @database + '.INFORMATION_SCHEMA.COLUMNS
    WHERE  table_name = ''' + @table + ''' '

EXEC( @sql )

SET NOCOUNT OFF

Here’s an example of executing the sp_GetSchema stored procedure and the output generated.

EXEC sp_GetSchema @database='Action', @table='Order'
column_name  is_nullable data_type ordinal_position table_name dbname
------------ ----------- --------- ---------------- ---------- ------
OrderId      NOT NULL    INT       1                Order      Action
CustomerId   NOT NULL    INT       2                Order      Action
OrderDate    NOT NULL    DATETIME  3                Order      Action
RequiredDate NULL        DATETIME  4                Order      Action
ShippedDate  NULL        DATETIME  5                Order      Action
Freight      NULL        MONEY     6                Order      Action
Version      NOT NULL    TIMESTAMP 7                Order      Action

A few things to note about the sp_CompareSchema stored procedure. It uses temporary tables to house the schema information (retrieved via sp_GetSchema) for the tables to compare because you cannot insert the result from a stored procedure into a table variable and it compares the column names, data type and nullability. I am using the characters “T”, “A” and “D” to indicate a different in type, a column to be added and a column to be deleted respectively in the schema diff table. Technically, you could move the sp_GetSchema logic into the sp_CompareSchema stored procedure, but I am using it for other purposes so I kept it separate.

CREATE PROCEDURE sp_CompareSchema
(
    @database1  NVARCHAR(128)
    ,@database2 NVARCHAR(128)
    ,@table1    NVARCHAR(128)
    ,@table2    NVARCHAR(128)
)
AS

    SET NOCOUNT ON

    DECLARE @ErrorCode      INT
    DECLARE @ErrorMsg       NVARCHAR(500)
    DECLARE @alterScript    NVARCHAR(MAX)

    SET @alterScript = ''

    -----------------------------
    -- CREATE TEMP TABLES
    -----------------------------
    IF( OBJECT_ID('#SCHEMA_T1') IS NOT NULL ) DROP TABLE #SCHEMA_T1
    IF( OBJECT_ID('#SCHEMA_T2') IS NOT NULL ) DROP TABLE #SCHEMA_T2

    -- Schema for table1
    CREATE TABLE #SCHEMA_T1
    (
        column_name     NVARCHAR(128)
        ,is_nullable    NVARCHAR(64)
        ,data_type      NVARCHAR(32)
        ,position       INT
        ,table_name     NVARCHAR(128)
        ,database_name  NVARCHAR(128)
    )
    -- Schema for table2
    CREATE TABLE #SCHEMA_T2
    (
        column_name     NVARCHAR(128)
        ,is_nullable    NVARCHAR(64)
        ,data_type      NVARCHAR(32)
        ,position       INT
        ,table_name     NVARCHAR(128)
        ,database_name  NVARCHAR(128)
    )
    -- Schema difference table
    DECLARE @SCHEMA_DIFF TABLE
    (
        id              INT IDENTITY(1, 1)
        ,database_name  NVARCHAR(128)
        ,column_name    NVARCHAR(128)
        ,is_nullable    NVARCHAR(64)
        ,data_type      NVARCHAR(32)
        ,diff_type      NVARCHAR(5)
    )

    -----------------------------
    -- POPULATE TEMP TABLES
    -----------------------------
    -- Get the fields for database1.table1 (cannot insert the result from a sproc into a table variable)
    INSERT INTO #SCHEMA_T1 (column_name, is_nullable, data_type, position, table_name, database_name)
        EXEC sp_GetSchema @database=@database1, @table=@table1

    -- Get the fields for database2.table2
    INSERT INTO #SCHEMA_T2 (column_name, is_nullable, data_type, position, table_name, database_name)
        EXEC sp_GetSchema @database=@database2, @table=@table2

    -----------------------------
    -- COMPARE THE FIELDS
    -----------------------------
    -- T - indicates a difference in Data Types...ALTER column in Target
    -- A - indicates a missing Column in the Target...ADD column to Target
    -- D - indicates a missing Column in the Source...DROP column from Target
    INSERT INTO @SCHEMA_DIFF
        SELECT  s.database_name, s.column_name, s.is_nullable, s.data_type,
                CASE WHEN (s.data_type != t.data_type) THEN 'T'
                     WHEN (s.column_name IS NOT NULL AND t.column_name IS NULL) THEN 'A' ELSE '' END AS diff_type
        FROM    #SCHEMA_T1 s
                FULL OUTER JOIN #SCHEMA_T2 t on s.column_name = t.column_name
        WHERE   s.data_type != t.data_type
        OR      (s.column_name IS NOT NULL AND t.column_name IS NULL)
        UNION ALL
        SELECT  t.database_name, t.column_name, t.is_nullable, t.data_type,
                CASE WHEN (s.column_name IS NULL AND t.column_name IS NOT NULL) THEN 'D' ELSE '' END AS diff_type
        FROM    #SCHEMA_T1 s
                FULL OUTER JOIN #SCHEMA_T2 t on s.column_name = t.column_name
        WHERE   s.column_name IS NULL AND t.column_name IS NOT NULL

    SELECT * FROM @SCHEMA_DIFF

    DROP TABLE #SCHEMA_T1
    DROP TABLE #SCHEMA_T2

    SET NOCOUNT OFF

Here’s an example of executing the sp_CompareSchema stored procedure and the output generated.

Exec sp_CompareSchema
    @database1  = 'AdventureWorks'
    ,@database2 = 'AdventureWorksLT'
    ,@table1    = 'Product'
    ,@table2    = 'Product'
id  database_name     column_name             is_nullable  data_type       diff_type
------------------------------------------------------------------------------------
1   AdventureWorks    MakeFlag                NOT NULL     BIT             A
2   AdventureWorks    FinishedGoodsFlag       NOT NULL     BIT             A
3   AdventureWorks    SafetyStockLevel        NOT NULL     SMALLINT        A
4   AdventureWorks    ReorderPoint            NOT NULL     SMALLINT        A
5   AdventureWorks    SizeUnitMeasureCode     NULL         NCHAR(3)        A
6   AdventureWorks    WeightUnitMeasureCode   NULL         NCHAR(3)        A
7   AdventureWorks    DaysToManufacture       NOT NULL     INT             A
8   AdventureWorks    ProductLine             NULL         NCHAR(2)        A
9   AdventureWorks    Class                   NULL         NCHAR(2)        A
10  AdventureWorks    Style                   NULL         NCHAR(2)        A
11  AdventureWorks    ProductSubcategoryID    NULL         INT             A
12  AdventureWorksLT  ProductCategoryID       NULL         INT             D
13  AdventureWorksLT  ThumbNailPhoto          NULL         VARBINARY(MAX)  D
14  AdventureWorksLT  ThumbnailPhotoFileName  NULL         NVARCHAR(50)    D

The data from the @SCHEMA_DIFF table could be used to build an alter script that would be applied against the archive table to sync the schema changes.

Tags: , , ,


Common Table Expression T-SQL

Common table expressions, a feature introduced by Microsoft SQL Server 2005, are a very powerful feature that may be used in lieu of temporary tables. They may be thought of as a temporary result set that is defined within the execution scope of a query. Using a common table expression you are able to create a reference to itself, which exposes a recursive ability that is very helpful for identifying a hierarchy amongst table elements. 

For our example we will use NorthWind’s employee table. This table is designed in such a way that the ReportsTo column is a foreign key field which refers to the primary key field EmployeeID. 

 

 

 WITH Managers AS
(
--Anchor Member
SELECT EmployeeID, FirstName, LastName, ReportsTo 
FROM Employees
WHERE ReportsTo is null
UNION ALL
--Recursive Member
SELECT e.employeeID,e.FirstName, e.LastName, e.ReportsTo
FROM Employees e
INNER JOIN Managers m 
ON e.ReportsTo = m.employeeID
)
SELECT * FROM Managers 

Semantics 

1. Split the Common Table Expression into anchor and recursive members. 

                1(a). The anchor is the root level node of the result set. In our example Andrew Fuller has no report to property. Thus, we declare our anchor value with ReportsTo = NULL 


SELECT EmployeeID, FirstName, LastName, ReportsTo 
FROM Employees
WHERE ReportsTo is null

 

                 1(b). The recursive member must be joined to the anchor using UNION ALL 


UNION ALL
--Recursive Member
SELECT e.employeeID,e.FirstName, e.LastName, e.ReportsTo
FROM Employees e
INNER JOIN Managers m 
ON e.ReportsTo = m.employeeID

Any rows returned are inserted into the CTE, which are also then available (seen) on the next recursion. Recursion occurs because of the query referencing the CTE itself based on the Employee in the Managers CTE as input. The recursive query is repeated until it returns an empty result set. 

2. The final step is to query the results of the Common Table Expression.


SELECT * FROM Managers

 

A drawback of this feature is that a Common Table Expression must be called immediately after definition. Any subsequent query referencing the Common Table Expression will fail.

Tags: , , , , ,


ACID Rules

A few days ago, I had to look for some information regarding the ACID rules and I didn’t really find anything relevant that helped me understand the topic very well. I even checked many SQL Books because I thought I would be able to find good information in them, but even though they were supposed to be “good books” , I still found nothing good . So I decided to put together all the information I found and a little bit about the topic.

First of all we need to understand what the letters A.C.I.D. stand for and what the idea behind it is. The A.C.I.D. letters stand for atomicity, consistency, isolation and durability. The meaning of those words will be discussed with more detail in order to have a better understanding and give us a better idea of the topic as a whole.

Atomicity

When we deal with data, we want to be sure that if one thing is happening, another thing will happen as well. In other words atomicity means that something has to happen or nothing will happen at all.

To illustrate the principle of Atomicity, imagine writing an Insert statement. If you have not written any yet, you can do it by typing something like this:

 INSERT INTO table_name
 VALUES (value1, value2, value3,...)

The statement would insert the values in the database. But let’s also imagine that the Server has a problem and at that precise moment something unexpected happened? Would we lose the information? Would the system crash cause the data to be damaged or lost? In order to follow the principle of atomicity, the database engine would never insert that information into the table. Therefore the information that the table had, before you ran the insert, would remain intact and none of the changes would have taken place. That serves the Atomicity principle.

Another option to prevent those problems and to follow the Atomicity principle correctly, the database engines have some features such as Transaction Management, Locks and Concurrency.

Consistency

This may be the most important principle of the ACID model. As we know, the data has to be consistent. Every time we run a transaction on the data base, the data may change. If that happens, the data has to transform from one consistent state of data into another consistent state of data. If something happens that breaks down the principle, the transaction has to be rolled back. How Data Base engines roll back transactions is another topic that we may explore some other day however I think it’s fair enough to mention that we can also control how the Data base server rolls back the transactions.

Let’s remember that Rollback basically goes back to the last committed transaction known by the data base. It restores that information on the rows affected by the update that failed. This behavior provides consistency and atomicity to the data.

Isolated

To better explain this term, let us imagine that we have a busy data base server receiving multiple transactions on the same table. Those transactions could be affecting one or multiple records. The ability to keep those transactions separated one from another and the data consistent is what we call Isolated. In other words, this term requires that multiple transactions occur at the same time and not impact each other’s execution. This is something that we have to take into consideration especially from the Data Base Administration point of view.

To avoid that from happening, SQL server uses a locking mechanism. This mechanism arbitrates when a process is allowed to modify data as well as insuring the read are consistent. Locks occur at three different levels and there are three different types. They can be applied in a row, page or table level.

Durability

This is the last topic of the ACID model but not less important than the others. When we talk about durability we are not necessarily saying that the information will last forever but the concept means that we have to ensure that the data will remain safe and it won’t be lost even if the server crashes. In order to prevent loss of data, we have to keep backups and transaction logs. They will facilitate the data restoration.

There is no doubt that all the other terms ensure the durability of the data, however we haven’t thought about external problems and situations that the Data base can’t control, such as server crashes or unexpected shut downs. In order to prevent those problems, the data bases provide many different tools to provide durability. The most common way to do this is by doing backups.

There are many different solutions we can currently find. It really doesn’t matter what you implement, the important thing is that you implement some kind of backup.

Tags: , , , , , , , , , ,