Ethernet Over Power thermal problems

If my last post was a departure from my normal topics, this one is a detour through back roads. Don't worry, though, I am working on a lengthy post that will get me back on track with SQL topics.

In the meantime, let me explain something that happened to me last week. The very first symptom was that my desktop would not print to my network printer. The printer itself appeared to be working normally, and I could even pull up the printer's web interface from my desktop. But I could not print. As I tried to resolve the communication between my desktop and the printer, I found my overall network connectivity was becoming intermittently interrupted. The problem escalated until at one point I used the ipconfig utility to manually release my IP address and attempted to renew it. No renewal occurred; DHCP was not working for me.

I would have assumed the router itself was down or having trouble, except that the laptops (yes, my wife and I each have two -- each of us has one for work and one personal laptop) were working fine with their wireless connectivity directly to the router. The only device apparently affected was my desktop. Having concluded this, I ordered a replacement network adapter. My desktop has the new PCI Express ports and no standard PCI ports, and I discovered that network adapters that fit PCI Express ports are difficult to impossible to find in a regular store, even an electronics store. So I ordered it on the Internet, and I expect it to arrive on Monday.

In the meantime my IP phone from work started having trouble. And this made me start the whole process over of trying to find the problem. One device that I thought I had tested and eliminated from the equation was the ethernet-over-power (EoP) adapter that I use to bridge my office computer equipment with the router downstairs, which is just barely within the range of the wireless router signal. But when it became apparent that something more was wrong than just the network adapter in my PC, I took another look. I moved the EoP adapter from the wall plug where it normally sits and moved it downstairs. While carrying it, I noticed that the EoP adapter was not just warm; it was downright hot, especially  the metal ports that hold the RJ45 terminator.

It did not occur to me right then, but gradually I began to wonder if the problem was a thermal one. Our air conditioning has been on the fritz for three days, meaning that my office was warmer than usual. Perhaps the PoE adapter was getting too hot to work properly. To test this, I left the adapter unplugged all last night and plugged it in this morning. Voila. Everything is working again on the network, including my desktop and the IP work phone. Unless something more occurs (which I doubt), I am going to chalk this up to thermal issues. I will probably unplug the PoE adapter in the evening before going to bed and plug it back in when I get to my desk the next day.

I post this as a cautionary tale to those out there who use ethernet-over-power adapters. If your network connectivity becomes unstable, don't overlook the possibility that the EoP adapter is overheating. It might save you some time and expense. As for me, I will have to see about the Amazon.com return policy.

Reapplying the Notes Master Style in PowerPoint

This post is a departure from my usual topics because I have been working for several weeks on the handouts for the 2011 International Shelby Conference, coming up June 14th through 17th.

Our preferred format for handouts is in PowerPoint, and we print the handouts in Notes view so that we can have a place for notes at the bottom of each page and so that we can put a nice cover page and final bio page on each set of slides. In any case, we receive PowerPoint files in a variety of styles and settings. Many times the notes view does not match the preferred template setup, and even resetting the Notes Master does not immediately apply to the notes view of individual pages.

It can take several clicks per page to "reapply the master" to the notes view. For lengthy presentations, this can add up to a very monotonous and time-consuming task. When faced with a 60+ page presentation that needed the notes master re-applied to every single page, I decided to look for a better solution.

I found one. I found a VBA macro script that reapplies the notes master style to every page in the presentation in a single step, and I want to share it here for your benefit (and for my own next year when I am once again working on this task). I give credit to the PPTools web site for re-posting the macro script that saved the day for me.  I am also posting it here because it appears to be in the public domain. If I am incorrect about that and you are the holder of a copyright of the script, please let me know. The macro VBA script is:


Sub ApplyMasterToNotes()

' Modified version of code originally posted to
' msnews.microsoft.com public newsgroups by
' David Foster in May of 1999

Dim ctl As CommandBarControl
Dim oSl As Slide

' 700 is the control ID for Layout
Set ctl = CommandBars.FindControl(Id:=700)
ActiveWindow.ViewType = ppViewNotesPage

If (ctl Is Nothing) Then
MsgBox "command not available"
Exit Sub
End If

For Each oSl In ActivePresentation.Slides

' go to the current slide
ActiveWindow.View.GotoSlide (oSl.SlideIndex)
DoEvents

' Bring up the dialog
ctl.Execute
DoEvents

' send it the needed keystrokes
SendKeys "%r{enter}"
DoEvents

Next

End Sub


If you would like some help implementing this code, I once again recommend the PPTools web site, which has an article on how to use VBA code in PowerPoint.

ShelbyQUERY Text Limit

I apologize for originally posting this as an empty message. For some reason the message was lost when I clicked to publish it, and I just didn't have time to re-write the whole thing.

What I intended was to post the maximum number of characters that the ShelbyQUERY text editor can handle. I had to determine this maximum because a customer contacted me about an overflow error when she was using ShelbyQUERY, and it turned out she was exceeding the maximum character limit of the editor. Oddly, the editor can hold more text than the "maximum" technically allowed, and it can execute the query as well. But if you try to move the insertion cursor anywhere past the maximum limit, you will receive an overflow error. So it is just good to avoid hitting that limit if you can.

Through some experimentation, I discovered that the maximum number of characters is 32,766. However, carriage returns count as two characters. And spaces count as one each also.

You should not hit this limit unless you are doing some extremely complex or multi-layered query analysis. But if you start getting overflow errors in ShelbyQUERY, this is the first thing to check.

As a troubleshooting tip, I recommend copying the text of your query over into MS Word and using the Review > Word Count tool. Use the character count (with spaces) value and add double the number of lines. That is equal to the number of the "characters" in your query. This will give you an idea for how much text needs to be cut out of the query.

If you are in a real pinch to squeeze out every extraneous space, you can remove carriage returns and use a space instead. You can even remove the space character if there is a punctuation mark (such as a comma or a single quotation mark) to separate key words in the statement. The result will not be readable, but it may be enough to fit the limit of the ShelbyQUERY text editor.

A better "workaround" might be to just use MS SQL Server Management Studio. That tool has no arbitrary limit to the amount of text, and so you can execute extremely long query statements without any trouble at all.

The Semicolon and Common Table Expressions

In the last few months I have begun using Common Table Expressions. These are subqueries that could be used in the FROM clause of the primary query, but instead they have been moved into a WITH clause before the primary query statement, even above the SELECT clause.

Although I will explore Common Table Expressions in later blog posts, I just want to document something I discovered in relation to using them in ShelbyQUERY. Curiously, what I discovered is also related to the little-used semicolon punctuation mark.

The semicolon is the terminator symbol for a T-SQL command. This includes query statements and other functions supported in T-SQL. Therefore every query written in T-SQL could -- some might say should -- end in a semicolon to indicate the end of the statement. However, I will admit that none, or almost none, of the queries I write end with a semicolon. This is because the semicolon is optional in the great majority of situations. There is one situation where the semicolon is not optional, and that is for any T-SQL command that precedes a WITH clause to define common table expressions.

This is particularly important to note in ShelbyQUERY because experience has shown that ShelbyQUERY actually prefixes one or more T-SQL commands in front of the actual query that is written in the text editor. Although I do not yet know what these commands are exactly, I suspect they are there to enforce the table-level security restrictions of the Supervisor user security setup and also the General Ledger user account restrictions. Whatever the commands are, it has become clear that sometimes (though not always) the final command prior to the actual query does not terminate with a semicolon.

Of course in the vast majority of cases, the lack of a semicolon on the prefixed command is moot. However, if the query is to begin using WITH and common table expressions, it is definitely not moot. In such a case the lack of a semicolon on the prefixed command with trigger a syntax error near the word WITH.

The simplest solution is to add a semicolon before the word WITH, either on a line above it or just in front of it on the same line. The fact that T-SQL is not picky about spacing means that you can put it anywhere you like, as long as it is in front of the word WITH.

While I'm on the topic, I will also caution anyone out there who uses variables in your queries. If you put DECLARE and SET statements in front of a WITH clause, you also need to put the semicolon after the last statement prior to the word WITH. Of course, you could also put the semicolon after every complete statement. Putting the semicolon only in front of the word WITH is the minimum requirement, but it is certainly more rigorous to put one after every complete T-SQL statement.

Discovering the Compatibility Level of a Database

Even though a database is running in a particular version of SQL Server, it can have a compatibility level set to a prior version. If this is the case queries that rely on features implemented in the current version of SQL Server may fail to work on your database even though the SQL Server version meets the requirements.

To find out your database's compatibility level, run the following query:

select name, compatibility_level from sys.databases

The number corresponds to the version of SQL Server as shown below:

80 = SQL Server 2000 (or MSDE)
90 = SQL Server 2005
100 = SQL Server 2008

If the compatibility_level value is lower than the version of SQL Server you are using to host the database, you can change the compatibility level to match by using the SQL Server Management Studio software on the server. Right-click on the database name and choose Properties. Then choose the Options page and set the Compatibility Level drop-down option to the highest level available, which will be the same as the version of the SQL Server host.

Table Aliases - and an apology

First, I need to apologize for the lengthy interruption in my posting to this blog. For explanation I will just point to the Thanksgiving and Christmas holidays, the usual January crunch in the Shelby Systems offices when it is "all hands on deck" to answer Support questions, and also (perhaps primarily) the fact the fact that I hit a file-system permissions snag on the transactional replication process, and I have been unable to make more headway on that since my last post.

Anyway, while the transactional replication concept stews for awhile, I want to put out a quick post on using table aliases. This is a technique I have already used in a few earlier SQL query posts, in fact most of them, but I haven't given it an explanation of its own yet. Using a Support inquiry as my impetus, I will post that explanation now and revive this blog at the same time I address this question.

A table alias is nothing more than a "new name" for a database table. Table aliases are assigned in the FROM clause, and when they are used, all other clauses must reference the table by the alias instead of the original name. This applies to the SELECT clause as well. This may seem counter-intuitive because the SELECT clause appears before the FROM clause and thus "before" the alias has been assigned. But this is only in the visible order of operations. In the logical order of operations (the order that matters to the server), the FROM clause is interpreted first.

Table aliases are assigned simply by putting the alias name immediately after the table name in the FROM clause. An optional "AS" keyword can introduce the alias. If the alias contains a space character, brackets are required around the alias. Here is an example

SELECT Names.NameCounter, Names.FirstMiddle, Names.LastName
FROM Shelby.NANames AS NamesORDER BY Names.LastName, Names.FirstMiddle

Notice in this example that the alias of the NANames table is Names. The SELECT clause references the Names alias, not the original table name. And the ORDER BY clause also references the alias.

There are several advantages to using table aliases, including:

• The ability to reference the same table multiple times, as long as each as a different alias. (This is perhaps the biggest advantage.)
• The ability to give more meaningful names to tables.
• The ability to give shorter names to tables.
• The ability to type only once the fully qualified table name, which can include the database name, the schema name, and the table name, and instead use the alias alone for all other references to the table.

Because of these advantages, I use table aliases almost 100% of the time when I write queries. For examples in practice, view the query samples in these earlier blog posts:

Listing Husband and Wife on One Line
Calculating Age in T-SQL
Rollup and Grouping Functions
Pivoting Rows to Columns

Transactional Replication: A Journey into the Unknown Part 2

I worked some more on this transactional replication process this morning. I discovered that the snapshot creation problem was being caused by one or more "views" that included select * somewhere in them. I eliminated all my personal views and left only the "official" views created automatically by the Shelby v.5 system, and the snapshot creation process completed normally.

After this things went smoothly until I tried to initiate the first replication process, at which point I started getting errors saying that the "replication subsystem failed to load" and also that the process as "rejected" because the job was "suspended." It took more digging, but I discovered that all I needed to do was to restart the SQL Server Agent service. After I restarted it and tried again, the synchronization process "started successfully."

As I type this the first synchronization is still in progress. Its been running for several minutes, and I'm not sure when it will complete. I'm going to let it keep running and I'll post again once I have more to report.

For now, here are three things I learned about implementing Transactional Replication that was not included in the 1-2-3 steps of the SQL Server Management Studio wizard:

1. Make sure the SQL Server Browser service is running on the Principal/Distribution server(s).
2. Do not include any views as replication articles if they include select * anywhere in them.
3. After setting up the Subscription on the Subscriber server, restart the SQL Server Agent.

Knowing these three points would have helped me avoid several minutes of hunting down the causes of problems and then following several trouble-shooting steps. I'm sure there will be more to learn ahead.