John Paul Cook

THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |

Home

Blogs

Downloads

Opml

Windows shell: commands are like environment variables on steroids. If you haven’t been using them, you need to start because they will make your life easier. First, a quick review of what you can do with a few helpful environment variables.

You can enter an environment variable such as %temp%, %appdata%, %systemroot%, or %programfiles% into the address bar in Windows Explorer and directly navigate to the corresponding location on your machine. It’s much faster than trying to click your way to your desired destination.

Figure 1. Using the %temp% environment variable in Windows Explorer.

Windows shell: commands provide similar direct navigation. There is some overlap between environment variables and shell: commands, but most shell: commands offer functionality not provided by Windows environment variables. Notice that you must put a colon between the word shell and the shell command.

Figure 2. Using the shell:SendTo command in Windows Explorer.

Where are all of these shell: commands listed? On your machine, assuming you have access to the registry. In corporate environments, you just might be out of luck. Even if you have administrative rights and can view your registry, you are still out of luck because of how the commands are stored in the registry at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\FolderDescriptions.

Figure 3. Shell: commands are under FolderDescriptions in the registry.

I exported the FolderDescriptions registry key, opened the file with a text editor, sorted it, and extracted the names of all of the shell: commands which I’ve listed below for you. Remember, the syntax you put in the address bar of Windows Explorer is of this format:

shell:nameOfShellCommand

You can also enter the shell: command into your search box as shown in Figure 4. It will start a new instance of Windows Explorer opened to the folder specified in the command.

Figure 4. Using a shell: command with the search box.

You may also enter a shell: command in a run dialog box.

Figure 5. Using a shell: command in a run dialog box.

List of shell: commands on my Windows 7 machine:

AddNewProgramsFolder
Administrative Tools
AppData
AppUpdatesFolder
Cache
CD Burning
ChangeRemoveProgramsFolder
Common Administrative Tools
Common AppData
Common Desktop
Common Documents
Common Programs
Common Start Menu
Common Startup
Common Templates
CommonDownloads
CommonMusic
CommonPictures
CommonRingtones
CommonVideo
ConflictFolder
ConnectionsFolder
Contacts
ControlPanelFolder
Cookies
CredentialManager
CryptoKeys
CSCFolder
Default Gadgets
Desktop
Device Metadata Store
DocumentsLibrary
Downloads
DpapiKeys
Favorites
Fonts
Gadgets
Games
GameTasks
History
HomeGroupFolder
ImplicitAppShortcuts
InternetFolder
Libraries
Links
Local AppData
LocalAppDataLow
LocalizedResourcesDir
MAPIFolder
MusicLibrary
My Music
My Pictures
My Video
MyComputerFolder
NetHood
NetworkPlacesFolder
OEM Links
Original Images
Personal
PhotoAlbums
PicturesLibrary
Playlists
PrintersFolder
PrintHood
Profile
ProgramFiles
ProgramFilesCommon
ProgramFilesCommonX64
ProgramFilesCommonX86
ProgramFilesX64
ProgramFilesX86
Programs
Public
PublicGameTasks
PublicLibraries
Quick Launch
Recent
RecordedTVLibrary
RecycleBinFolder
ResourceDir
Ringtones
SampleMusic
SamplePictures
SamplePlaylists
SampleVideos
SavedGames
Searches
SearchHomeFolder
SendTo
Start Menu
Startup
SyncCenterFolder
SyncResultsFolder
SyncSetupFolder
System
SystemCertificates
SystemX86
Templates
User Pinned
UserProfiles
UserProgramFiles
UserProgramFilesCommon
UsersFilesFolder
UsersLibrariesFolder
VideosLibrary
Virtual Machines
Windows

Posted Wednesday, February 13, 2013 11:11 PM by John Paul Cook | 1 Comments

Copy as path instead of a send to program

What do you do to capture the fully qualified name of a file from Windows Explorer? If you are using a newer version of Windows than XP, you can easily get the fully qualified file name without installing or reconfiguring anything. All you need to do as add the Shift key to the mix after selecting the file of interest.

Figure 1. Normal right-click menu option.

Figure 2. Shift plus right-click menu option. Notice the addition of Pin to Start Menu and Copy as path.

Here is what Copy as path put in my paste buffer:

"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf"

Considering that this particular path has embedded spaces, the double quotes surrounding the fully qualified file name are good to have. When there aren’t any spaces in the path or file name, the double quotes may be annoying. But the annoyance of double quotes is nothing compared to not having this functionality at all. Even on my own machine at home, I prefer Copy as path over other methods because I don’t want to install a send to clipboard program on my machine. The fewer programs that are installed, the better things are.

Posted Tuesday, February 12, 2013 6:59 PM by John Paul Cook | 2 Comments

UNION, UNION ALL and ORDER BY

At every client where I've worked, there have always been questions about UNION and UNION ALL. They may return the same results or they may not. It all depends on the data. If you don't have any duplicates, you'll get the same number of rows returned whether you use UNION or UNION ALL. If you have duplicates, UNION will return fewer rows than UNION ALL.

No matter who your database vendor is, a UNION statement follows the rules of set theory. Set theory tells us that a set only contains unique elements. In other words, there are no duplicates in a set. If you use a UNION statement, you will not see any duplicate values in your result set. To toss out the duplicates, the database engine has to do some type of sort or merge operation to identify the duplicates. The side effect of this is that your result set tends to be ordered. Notice I said tends. This is a big misconception about UNION statements. You may have always seen the results of UNION statements to be perfectly ordered. Just because you observe a query returning sorted results without an ORDER BY statement doesn't mean you can always count on sorted results. If you require the results to be ordered, you must use an ORDER BY clause. Without it, you're gambling.

I've provided some sample code that will work anywhere for you to play with and learn. Select the first three lines and execute them by themselves to see in what order the results appear. Then execute everything. Examine the output and remember what you learned for the next time when it is a real coding assignment.

select 7 -- Select only the first three lines
UNION -- and execute them by themselves.
select 2 -- See what row is returned first.
UNION
select 3
UNION
select 2

select 7
UNION ALL
select 2
UNION ALL
select 3
UNION ALL
select 2

Posted Friday, February 08, 2013 9:32 PM by John Paul Cook | 1 Comments

Why to use COALESCE instead of ISNULL

As of this writing, Books Online isn't completely clear about how ISNULL works. I submitted suggestion 778704 on Connect asking Microsoft to explicitly state that if the replacement_value is longer than the check_expression, it will be truncated to the length of the check_expression. A code sample that demonstrates the problem is shown below. To prevent this kind of truncation, use COALESCE instead of ISNULL as shown in the last step. Additionally, COALESCE is ANSI standard and ISNULL is not. Read Aaron's post if you want to know more about the performance implications of COALESCE vs. ISNULL. My primary point is to make people aware of how things actually work so that they can write more robust code. UPDATE: On February 12, 2013, Microsoft indicated that they would be clarifying the documentation on ISNULL.

DECLARE @msg NVARCHAR(4000);
DECLARE @str1 NCHAR(1);
DECLARE @str2 NCHAR(2);
DECLARE @str3 NCHAR(3);
DECLARE @str5 NCHAR(5);
DECLARE @str11 NCHAR(11);

/* notice that @str1, @str2, @str3, @str5, @str11 are never set, so they are NULL */
/* the objective is to display the value of @str1 */
/* is @str1 is actually NULL, then display "not defined" */

SET @msg = N'@str1 is ' + ISNULL (@str1, N'not defined');
SELECT N'str1 ISNULL', @msg;

SET @msg = N'@str2 is ' + ISNULL (@str2, N'not defined');
SELECT N'str2 ISNULL', @msg;

SET @msg = N'@str3 is ' + ISNULL (@str3, N'not defined');
SELECT N'str3 ISNULL', @msg;

SET @msg = N'@str5 is ' + ISNULL (@str5, N'not defined');
SELECT N'str5 ISNULL', @msg;

SET @msg = N'@str11 is ' + ISNULL (@str11, N'not defined');
SELECT N'str11 ISNULL', @msg;

SET @msg = N'@str1 is ' + COALESCE (@str1, N'not defined');
SELECT N'str1 COALESCE', @msg;

Posted Wednesday, February 06, 2013 6:53 PM by John Paul Cook | 4 Comments

Scope of SET TRANSACTION ISOLATION LEVEL

To help you better understand the scope of SET TRANSACTION ISOLATION LEVEL, I wrote some code for you to run on your own machine to experiment with and see for yourself. I think you'll remember it better by running the script below and carefully analyzing the output.

Whatever the TRANSACTION ISOLATION LEVEL is before executing a stored procedure is what it will be when the stored procedure starts executing. If it is changed inside the stored procedure, the change will persist within the stored procedure, but it will not persist outside the stored procedure.

If sp_executesql is executed within the stored procedure, the current TRANSACTION ISOLATION LEVEL in the stored procedure is carried through into the statement executed by sp_executesql. This is not a trivial point. There are some things in a stored procedure that don't carry through into sp_executesql. For example, a table valued function declared in a stored procedure isn't scoped into sp_executesql, but a temporary table declared in a stored procedure does scope into sp_executesql, which is why I used a temporary table instead of a table variable.

Documentation for sys.dm_exec_requests and transaction_isolation_level is found here. Quoting from said document, we see the following definitions of transaction_isolation_level:

      0 = Unspecified
   1 = ReadUncomitted
   2 = ReadCommitted
   3 = Repeatable
    4 = Serializable
   5 = Snapshot

I recommend setting SSMS to display the results as text instead of in a grid. Go to Query menu, then select Results To and from that menu, select Results to Text. Or more simply, do CTRL-T to accompish the same thing.

Have fun!

SET NOCOUNT ON

USE tempdb;
GO

CREATE PROCEDURE dbo.TIL
AS
DECLARE @transaction_isolation_level SMALLINT;
DECLARE @sqlString NVARCHAR(1000);

    CREATE TABLE #TILstate (
        context NVARCHAR(100)
       ,transaction_isolation_level SMALLINT
    );

    SELECT @transaction_isolation_level = transaction_isolation_level
    FROM sys.dm_exec_requests
    WHERE session_id = @@spid;

INSERT INTO #TILstate (context, transaction_isolation_level)
VALUES ('in stored proc, initial transaction_isolation_level before changing anything', @transaction_isolation_level);

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    SELECT @transaction_isolation_level = transaction_isolation_level
    FROM sys.dm_exec_requests
    WHERE session_id = @@spid;

INSERT INTO #TILstate (context, transaction_isolation_level)
VALUES ('in stored proc, after SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED', @transaction_isolation_level);

    SET @sqlString = 'INSERT INTO #TILstate (context, transaction_isolation_level)'
                   + ' SELECT ''in stored proc, transaction_isolation_level in sp_executesql'','
                   + ' transaction_isolation_level FROM sys.dm_exec_requests WHERE session_id = @@spid';

PRINT @sqlString;
PRINT '';

EXEC sp_executesql @sqlString;

SELECT * FROM #TILstate;

DROP TABLE #TILstate;

GO --need this statement to separate the CREATE PROCEDURE from what's next

/* If you execute the following statements a second time, you're likely to see slightly different results. */
/* You should be able to figure out why by carefully considering what condition changed. */

SELECT N'default transaction_isolation_level before executing anything' as context, transaction_isolation_level
FROM sys.dm_exec_requests
WHERE session_id = @@spid;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SELECT N'transaction_isolation_level after setting it and before executing stored procedure' as context, transaction_isolation_level
FROM sys.dm_exec_requests
WHERE session_id = @@spid;

exec dbo.TIL;

SELECT N'transaction_isolation_level after executing stored procedure' as context, transaction_isolation_level
FROM sys.dm_exec_requests
WHERE session_id = @@spid;

Posted Tuesday, February 05, 2013 10:31 PM by John Paul Cook | 0 Comments

Analyzing Complexity of Text

Storing text in a database is common. What isn’t common is needing to know the reading ease and grade level of the text, but I was presented such a requirement (actually it was more of a wish list item) this week. There are ways of solving this problem. In the conclusion to this post, I outline the steps for implementing T-SQL code to estimate reading complexity. I think the topic of reading ease and grade level ratings is potentially of greater general interest than you might at first think. For example, you could have data driven web pages accessed by the general public where choices entered by the users deliver custom content. Perhaps you need to deliver product specific operating or safety instructions.

For purposes of this post, I’ve assumed that you have a large body of unanalyzed text in VARCHAR and NVARCHAR columns. Text stored in Word documents stored in SQL Server FILESTREAM is out of scope for this post.

There are several well-known and relatively simple algorithms for estimating the grade level and reading ease of text. Microsoft Office has the Flesch-Kincaid algorithm that you can use to obtain an estimated grade level and the Flesch Reading Ease algorithm. To do readability analysis in Word, you’ll need to enable it. See http://blogs.office.com/b/microsoft-word/archive/2007/06/26/can-word-improve-your-writing.aspx and follow the easy instructions for doing this. Notice that the page shows an analysis of something written by Dr. Seuss which has a grade level of zero and a reading ease of 100. For comparison purposes, I analyzed the United States Internal Revenue Service instructions for completing a form 1040 income tax return. Notice that income tax instructions have a much lower ease of reading than Dr. Seuss, but somehow I think you already knew that.

Figure 1. United States income tax instructions analysis.

An analysis of the product information for acetaminophen (a.k.a. paracetamol, brand names Tylenol, Panadol, Acamol, Biogesic, Crocin) found at http://www.nlm.nih.gov/medlineplus/druginfo/meds/a681004.html shows an even lower reading ease than income tax instructions and a higher grade level.

Figure 2. Acetaminophen product information analysis.

Before you can write code to calculate reading difficulty, you need to pick an algorithm. The Flesch and Flesch-Kincaid algorithms require that you know the total syllables, total words, and total sentences in the body of text to be analyzed. The Simple Measure of Gobbledygook (SMOG), Gunning fog index, and Coleman-Liau index are similar. If you want to implement something simple using T-SQL, finding the number of syllables is too difficult. The Dale-Chall and Spache algorithms require that you use a list of words considered to be common so that you can find the percentage of complex words. Finding a copy of one of these word lists in a single column format is a bit of a challenge. I found the updated and expanded list of Dale-Chall words at http://lindacarlton.net/thoughts/2010/02/dalechall_list.php if you need to implement something possibly more accurate than the algorithm in the next paragraph.

The Automated Readability Index is sufficiently easy to code. The greatest difficulty you will likely encounter is in determining the number of sentences. Since computing readability isn’t an exact science, you could count the number of periods in a block of text to estimate the number of sentences. The accuracy could be improved by reducing the total by the number of ellipses (…) found in the text. As the linked document shows, the Automated Readability Index was developed for the United States Air Force in 1967. The document shows both a multiple regression version of the algorithm to estimate grade level as well as a simplified equation to compute the Automated Readability Index:

grade level = 0.50 * (number of words/number of sentences) + 4.71 (number of characters/number of words) – 21.43

Automated Readability Index = (number of words/number of sentences) + ( 9 * (number of characters/number of words) )

At this point, all that has been asked of me is to explain what it would take to analyze existing textual data for readability. I’ve presented algorithms that can be implemented. Do you remember how your advanced math textbooks would say the proof is obvious and is left as an exercise for the reader? I won’t provide the code for your stored procedure or function today – I leave the implementations details as an exercise for the reader.

This document has a Flesch Reading Ease of 44.7 and a Flesch-Kincaid Grade Level of 11.5. If reading something this complex gives you a headache, at least I provided a link to information about an analgesic!

Posted Saturday, January 05, 2013 1:32 PM by John Paul Cook | 0 Comments

SQL Server 2012 Sequences

SQL Server 2012 sequences provide Oracle style flexibility in doing parent child inserts in situations where the IDENTITY property would otherwise be used. When the IDENTITY property is used to populate a primary key in a parent table, some type of after the fact operation is necessary to find out what the just inserted primary key is. THis value is then used as a foreign key value in the inserts into the child tables. Many people prefer knowing the primary key in advance before the insert into the parent table is executed.

Look at the code sample below and decide for yourself if it is cleaner to know the parent table's primary key in advance.

The scope of the IDENTITY property is an individual table. The scope of a SEQUENCE is the schema. Sequences are found in SSMS under Programmibility.

Keep in mind that when using either SEQUENCE or IDENTITY inside a transaction that their values are never rolled back if a transaction fails or is rolled back. Rollbacks will cause gaps in the numbers generated by either IDENTITY or SEQUENCE. Please don't infer there is something wrong with gaps - it's just an observation. You can intentionally cause gaps if you want. Both IDENTITY and SEQUENCE have an INCREMENT property which will cause gaps whenever the INCREMENT is something other than 1 or -1.

Both in Oracle and SQL Server, SEQUENCE values can be cached. Instead of getting one sequence value at a time, a batch of sequence values can be obtained and loaded into a cache. Appropriate sizing of a sequence cache can improve performance. Depending on your specific circumstances, you may find that using sequences (even without sequence caching) instead of IDENTITY results in a measureable performance improvement.

Since this is our first blog post of 2013, consider having an open mind to trying something new in SQL Server 2012. Have you made any SQL Server New Year's resolutions, such as perhaps not running a script when your database is set to master? I almost did that today when testing the script!

P.S. Thanks to reader London DBA for a correction to step 3.

/* STEP 1 create objects */

create schema deleteMe;
go

declare @parentTablePK int;
declare @childTablePK int;

declare @parentTableI table (
parentTablePK int
, colA char(1)
);

create sequence deleteMe.oneByOne
start with 1
increment by 1;

create table deleteMe.parentTableI (
parentTablePK int not null primary key identity
, colA char(1)
);

create table deleteMe.parentTableS (
parentTablePK int not null primary key
, colA char(1)
);

create table deleteMe.childTableI (
childTablePK int not null primary key identity
, childTableFK int
, colB char(1)
, foreign key (childTableFK) references deleteMe.parentTableI(parentTablePK)
);

create table deleteMe.childTableS (
childTablePK int not null primary key
, childTableFK int
, colB char(1)
, foreign key (childTableFK) references deleteMe.parentTableS(parentTablePK)
);

/* STEP 2 parent child inserts using IDENTITY property */

insert into deleteMe.parentTableI (colA) values ('x');

select @parentTablePK = SCOPE_IDENTITY();

insert into deleteMe.childTableI (childTableFK, colB) values (@parentTablePK, 'y');

/* STEP 3 parent child inserts using IDENTITY property and return values */

delete from @parentTableI;

insert into deleteMe.parentTableI (colA)

output inserted.parentTablePK, inserted.colA into @parentTableI

values ('x');

insert into deleteMe.childTableI (childTableFK, colB)

select @parentTablePK, 'y' from @parentTableI;

/* STEP 4 parent child inserts using a SEQUENCE */

select @parentTablePK = next value for deleteMe.oneByOne;

set @childTablePK = next value for deleteMe.oneByOne;

insert into deleteMe.parentTableS (parentTablePK, colA) values (@parentTablePK, 'x');

insert into deleteMe.childTableS (childTablePK, childTableFK, colB) values (@childTablePK,@parentTablePK, 'y');

/* STEP 5 cleanup */

drop table deleteMe.childTableS;
drop table deleteMe.childTableI;
drop table deleteMe.parentTableS;
drop table deleteMe.parentTableI;

drop sequence deleteMe.oneByOne;

drop schema deleteMe;

Posted Tuesday, January 01, 2013 5:07 PM by John Paul Cook | 0 Comments

Attachment(s): sequences.txt

BI Beginner: Creating formulas in Excel

Most people haven’t created a formula in Excel using the Equation Editor. If you are writing specifications for what statistical analyses must be performed on a project, it is appropriate to begin with a professional looking formula. Excel’s Equation Editor isn’t difficult to use and you shouldn’t be afraid of it. It is just a collection of templates or frameworks that you nest one inside another. Once you have practiced a few times with the Equation Editor, you should be able to create a formula like the one shown in Figure 1 in less than 3 minutes. You can achieve proficiency with the Equation Editor after creating only a few equations.

Figure 1. Standard deviation of a sample’s frequency distribution.

To invoke the Equation Editor, first click INSERT in the menu bar and then click Equation, which is on the right in the Symbols menu.

Figure 2. Click Equation to start the Equation Editor.

After Excel switches to Equation Editor mode, I suggest right-clicking where it says Type equation here and selecting a larger font size. I chose a 24 point font size.

Figure 3. Increasing the equation’s font size.

The next several steps consist of choosing the appropriate formatting templates for your equation. The order in which you build an equation makes a difference on how easy it is to complete it. Build starting at the outside and go progressively inward.

Figure 4. The easy part of my equation.

The outermost part of my equation is a radical, so that is where I began. I selected the square root template as shown.

Figure 5. Choosing the square root template.

Notice that the cursor is set to the right of the equation. That’s not where it needs to be for the next step.

Figure 6. The cursor appears to the right of the highlighted formula.

Click the square box to set focus there and click on Fraction to see the list of fraction templates.

Figure 7. Click the square box and select a template to insert where the square box is.

We need the stacked fraction template for our formula.

Figure 8. Selecting a stacked fraction template.

Once again, the cursor appears to the right. You’ll need to change focus twice, once to the numerator and once to the denominator.

Figure 9. Square root template containing a stacked fraction template.

Set focus to the numerator and select the simple summation operator.

Figure 10. Ready to place a summation operator in the numerator.

After the summation operator template in place, click the square box to set focus to it.

Figure 11. The superscript template is found under Script.

Select the superscript template.

Figure 12. Selecting the Superscript template.

You’ll need to set focus to each of the Superscript’s square boxes, one at a time.

Figure 13. Initial appearance of a newly inserted Superscript template.

Set focus to the exponent square box and enter the exponent.

Figure 14. Focus set to the exponent before entering it.

After entering the exponent, set focus to the base and enter the parentheses.

Figure 15. Parentheses for the base.

We need x bar for the mean of the sample, which requires the use of the Accent template.

Figure 16. Putting a bar over a symbol is accomplished with an Accent template.

Make sure the cursor is in the correct location before selecting the Bar template.

Figure 17. Selecting the Bar template.

Once the Bar template is in place, set focus to the square box and enter the letter x to create x bar.

Figure 18. Using the Bar template.

Once the numerator is complete, it is time to complete the denominator.

Figure 19. Numerator finally complete.

Set focus to the denominator to enter another summation template.

Figure 20. Fraction template with focus set to the denominator.

I recommend entering the parentheses before inserting the summation operator.

Figure 21. Ready to insert summation template.

Figure 22. Summation template inserted.

Set focus to the square box to set focus and add the f.

Figure 23. Focus set to add the f.

Add the minus and the 1 to complete the equation.

Figure 24. All items entered into the Equation Editor.

Figure 25. Impressive looking end result.

Posted Monday, December 24, 2012 10:30 PM by John Paul Cook | 0 Comments

Scammers on the loose pretending to be Microsoft

UPDATE: The scammers called back today informing me that my computer had been sending “error messages for quite a long time”! They identified themselves as the “Technical Maintenance Department”. I was told that the count at the top of my Event Viewer is the number of infections on my computer. For $199 they would help me fix my computer. They directed me to browse to ms7.us, presumably to purchase something. I didn’t browse to that address, but I did a whois which indicated that the domain is registered to someone in India. Today, like yesterday, the people on the phone were very difficult to understand. Also like yesterday, the supervisor is very easy to understand when he curses. Perhaps he has practiced English curse words more than regular conversational words.

YESTERDAY: Minutes ago I received a phone call that the caller ID listed as “Out of area”, which I knew was a bad sign. It was difficult to understand the caller because of his very thick accent. He told me that he was from Microsoft and that my computer was throwing a large number of errors and he was calling to help me. He directed me to use Windows R to open a run dialog box, type eventvwr and then look at the Event Viewer. Within Event Viewer, he instructed me to open Custom Views and then open Administrative Events. He wanted to know the number of events. I told him that the count was 16,908, which he said was very bad. He routed me to his supervisor.

The supervisor asked me if I knew what this meant. I said yes, it meant that he wasn’t with Microsoft, and that he was a scammer. He asked me if the other guy indicated they were Microsoft. I said yes. He said that they were not Microsoft but instead a contractor to Microsoft, a certified and authorized company trying to help me. He said that if he was a scammer, he could hack my computer (after all, he said he knows my IP address), but he wouldn’t because he is a legitimate business. He did some cursing and hung up on me. His cursing was amazingly good, actually accent free as far as I could tell.

In case you don’t know what the Event Viewer is, it is a log of things that happen on your computer. It is not a listing of malware infections on your machine. It is completely normal for it to have tens of thousands of events. Don’t fall for someone calling you trying to scare you about how many events are in your event logs. Microsoft doesn’t call Windows users at home and have them open a run dialog box.

Posted Wednesday, December 19, 2012 7:58 PM by John Paul Cook | 3 Comments

Visual Studio 2012 and Oracle Development Environment

Creating a complete environment for developing .NET applications that target Oracle requires a little planning and understanding of how Oracle connectivity works. You need to be methodical and test along the way so that you aren’t trying to troubleshoot a multitude of interrelated problems at the end.

I’ve made several assumptions in writing this post:

You are using 64-bit Windows 7 because you are developer with a lot of ram. I think this post will help you even if you are running Windows 8 instead of 7 because the principles are the same.
You want a local copy of Oracle for testing things out because it makes initial testing much easier. That means you’re going to need the extra ram you have.
You want to access your Oracle database through Visual Studio 2012.

Oracle provides you with three options for creating a local database server:

64-bit server edition
32-bit server edition
Oracle XE, which is only offered as a 32-bit version

You might be able to configure Oracle XE to work on your 64-bit Windows 7 or 8 machine, but you are likely to waste a lot of time. You are trying to create a valid test environment. Your production application isn’t going to be running on Oracle XE. Nor is it likely to be running on a 32-bit version of Oracle server. Realistically, a production Oracle application is running on a 64-bit version of Oracle server. That is why this post focuses on getting 64-bit Oracle server working on Windows 7.

I didn’t provide an email address for notifications about security updates. If you do, you’ll have to configure additional settings not shown in this post. I would definitely provide an email address if this was a production instance.

Figure 1. Oracle 11g R2 64-bit server installation screen with no email address provided.

If you don’t provide an email address, you’ll see the following warning.

Figure 2. Warning about not providing an email address.

Take the default option to create and configure a database.

Figure 3. Take the default option.

You want a starter database (it is called HR) because having one makes it easier to test the overall installation and configuration process that is the subject of this post.

Figure 4. Select Desktop Class.

My machine has multiple disk drives, so I installed Oracle to the D drive instead of C. Be sure to remember your administrative password.

Figure 5. Oracle Enterprise Edition being installed to the D drive.

I overrode the default on the Windows Security Alert and limited communication to private networks.

Figure 6. Windows Firewall.

Be sure to click the Password Management button.

Figure 7. Click the Password Management button.

Your HR sample database has a user named HR. By default this HR user account is locked. You will save yourself grief by scrolling down and unlocking the HR account as shown below.

Figure 8. Be sure to unlock the HR account.

Continue on with the installation dialog boxes until the installation process is done. Reboot.

Do you need to reboot? Let’s don’t waste time arguing that point. I always reboot after installing Visual Studio, Oracle products, or SQL Server. As a developer you are going to reboot your machine sooner or later, maybe because you simply shut it down to conserve the battery or to be green. Before you begin testing and troubleshooting, your machine should be in its future state, not the just installed state. Reboot, okay?

Use SQL*Plus to test your database server configuration. You can either navigate to it or more easily just type sql in the search box and pick SQL Plus from the list.

Figure 9. Using search to find SQL*Plus.

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 13 11:40:54 2012

Enter user-name: hr
Enter password: hr

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

If you are unsuccessful in this basic connectivity test, then you need to resolve the problem before proceeding further. Obtain your Oracle error message an search for a solution. Posting to the Oracle Technology Forums is where I recommend starting. Stack Overflow is another place to post because I know that the ODP.NET team at Oracle posts there. The point is that you need to get help from people who understand Oracle.

Assuming that you remember the administrative password you entered during the server installation process, you can use the following syntax to reset an Oracle password, should the need arise. Notice that in SQL*Plus you terminate commands with a semicolon.

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 13 12:23:43 2012

Enter user-name: system
Enter password: passwordYouSpecifiedAtInstallTime

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user hr identified by newPassword;

User altered.

SQL>

With basic server connectivity verified, the next step in creating your local Oracle development and testing environment is to install ODP.NET, which is a 32-bit application. It does work with 64-bit Oracle server, but it does take an extra configuration step that is covered later.

Consistent with the keep it simple philosophy, only install the ODP.NET client components as shown below.

Figure 10. Installing ODP.NET client software.

If you see the following error dialog box, click the Ignore button. We want to keeps things simple.

Figure 11. Click the Ignore button.

When the installation of ODP.NET completes, reboot.

For your next test, you need to confirm basic ODP.NET client connectivity using a UDL file which bring up the Data Link Properties dialog box. Create a file called connectionTest.udl and put it on your desktop. Double click the UDL shortcut.

Figure 12. UDL file on desktop. Every developer needs a UDL file!

Select the Provider tab on the Data Link Properties dialog box. Select Oracle Provider for OLE DB and click the Next button.

Figure 13. Select the Oracle data provider.

On the Connection tab, enter ORCL for the data source and hr for the user name and password. Click the Test Connection button.

Figure 14. Testing client connectivity.

If all goes well, you should see the following message:

Figure 15. Confirmation of basic ODP.NET connectivity.

If you do not achieve success at this step, you must resolve the problem before attempting to connect through Visual Studio.

With all of this work done and successful tests completed, will you be able to connect to Oracle using Visual Studio? No. You will have an ORA-12154: TNS:could not resolve the connect identifier specified error message.

Figure 16. Visual Studio doesn’t know where to find ODP.NET.

Let’s take a look at the filesystem to understand what’s going on with the Oracle products that were installed.

Figure 17. ODP.NET is installed under the client_1 folder and the Oracle server is installed under the dbhome_1 folder.

SQL*Plus is resolving the location of the Oracle server by looking at the tnsnames.ora file under the dbhome_1 folder. On my machine, that specific location is the D:\app\John\product\11.2.0\dbhome_1\NETWORK\ADMIN folder.

Visual Studio is looking for a tnsnames.ora file under the client_1 folder. Specifically, on my machine, the location being searched is the D:\app\John\product\11.2.0\client_1\Network\Admin folder, which doesn’t have a tnsnames.ora file.

Keeping in mind the simplicity principle, here are two easy ways to configure your machine so that Visual Studio connects to your database using ODP.NET.

Copy the tnsnames.ora file under dbhome_1 to the appropriate location under client_1. The disadvantage is the potential confusion that will occur later on if the two different tnsnames.ora files get out of sync with each other.
Create the system environment variable TNS_ADMIN and point it to where the server’s tnsnames.ora file is.

To set a system environment variable, enter env in the search box and then select Edit the system environment variables.

Figure 18. Searching for the system properties dialog box.

Click the Environment Variables button.

Figure 19. Click the Environment Variables button on the Advanced tab of System Properties.

Notice that there isn’t a TNS_ADMIN environment variable.

Figure 20. Click New under System variables to create a new system environment variable.

On my machine, the server’s tnsnames.ora file is found in the D:\app\John\product\11.2.0\dbhome_1\NETWORK\ADMIN folder, which I pasted into the Variable value textbox. If Visual Studio was open before creating the environment variable, you’ll need to close it and reopen it so that it can read the variable.

Figure 21. Setting the value for TNS_ADMIN.

Once you have enabled Visual Studio to find tnsnames.ora, go to the Server Explorer and select Add Connection.

Figure 22. Add Connection in the Server Explorer.

You must select your Oracle instance from the dropdown list. Since the default installation options were chosen, this should be ORCL.

Figure 23. Selecting the ORCL instance as the data source.

Enter the user name and password and test the connection.

Figure 24. Connection parameters ready to be tested.

Everything should work at this point.

Figure 25. Life is good.

The Oracle database is now fully exposed in Server Explorer.

Figure 26. Oracle HR database exposed in Visual Studio 2012.

Posted Wednesday, December 12, 2012 4:29 PM by John Paul Cook | 16 Comments

Windows 8 SDK and Orca

The Windows 8 SDK has a new version of Orca for those of us who edit msi files. The download is for a small executable, sdksetup.exe which causes the following dialog box to appear. If you only want Orca and you don’t want to install the SDK, override the default and download all of the files to the location of your choice. In this example, the files are downloaded to D:\Media\Windows8\SDK

Figure 1. Downloading the Windows 8 SDK to D:\Media\Windows8\SDK instead of installing it.

Click the Download button to download the files.

Figure 2. Windows 8 SDK full download.

After the files are downloaded, you will find an Installers subfolder underneath the folder you specified. In this example, that would be D:\Media\Windows8\SDK\Installers

Figure 3. Windows 8 SDK download contents.

In the Installers subfolder you will find a Orca-x86_en-us.msi file that contains Orca. You don’t have to run the msi and install Orca. You can just extract it.

Open a command prompt (administrative prompt not required) and enter something similar to the following command to extract the Orca files (I found I had to use a full path for targetdir):

msiexec /a Orca-x86_en-us.msi /qb targetdir=D:\Media\Windows8\OrcaFiles

The extraction process creates an Orca subfolder containing the Orca.exe file. You can run Orca from that folder. It does need the other files in the directory to work. You can copy the entire folder to another location, even another computer and run Orca without installing it.

Figure 4. Orca 5.0.9200.16384

Posted Sunday, December 09, 2012 7:07 PM by John Paul Cook | 0 Comments

BI Beginner: Excel 2013 Power View Maps

If you know how to use Excel, you can be productive in minutes with the new features of Excel 2013. Don’t be intimidated. Follow these simple steps and produce something snazzy!

The Excel file used in this example comes from the following SQL Server query which was run against the AdventureWorks2012 database:

SELECT Purchasing.Vendor.Name, Person.Address.City, Person.StateProvince.Name AS State
FROM Purchasing.Vendor
INNER JOIN Person.BusinessEntityAddress
ON Purchasing.Vendor.BusinessEntityID = Person.BusinessEntityAddress.BusinessEntityID
INNER JOIN Person.Address
ON Person.BusinessEntityAddress.AddressID = Person.Address.AddressID
INNER JOIN Person.StateProvince
ON Person.Address.StateProvinceID = Person.StateProvince.StateProvinceID;

You can create your own data. Just create a simple, three column table like the one shown in the screen captures below. You only need a few lines of data. There’s no need to use the Excel file I used, but if you want to use it, it is attached at the bottom of this post and you can download it.

The reason I showed the query to create the example Excel file is because future posts will use those tables.

Click the INSERT tab and then click Power View.

Figure 1. Click Power View on the INSERT tab of Excel 2013.

If you haven’t previously enabled the Power View add-in, click Enable when you see the following prompt:

Figure 2. Click Enable to enable the Power View add-in.

Now you are ready to design your first Power View report.

Figure 3. Excel 2013 Power View Design view.

I added a title of Vendor Locations. This changed the active tab from DESIGN back to POWER VIEW and also reset the focus from the table to the title. After entering the title, reset the focus to the table by clicking it. Then go to the Power View Fields and uncheck the State and Vendor Name columns.

Figure 4. Power View after adding a title and unchecking State and Vendor Name from the Range.

You’re now ready to make your first map. Click the DESIGN tab and then click Map on the DESIGN toolbar. You’ll see a privacy warning yellow bar informing you that you need to have your data geocoded by Bing. Click the Enable Content button in the yellow bar to allow this.

Figure 5. Click Map to make a map showing where the vendors are located.

Use the handles to resize the map.

Figure 6. Map showing the cities where the vendors are located.

You can use the Filters pane to restrict the cities on the map. Click City to expand it and choose the cities you want to display. In this example, only cities that end with a Y are displayed. Notice that by moving the mouse over a dot on the map, the name of the city is displayed.

Figure 7. Map with a filter applied.

There’s much more to learn about Power View, but as you have seen here, you can easily get impressive results in only a few minutes.

Posted Saturday, November 24, 2012 8:54 PM by John Paul Cook | 1 Comments

Attachment(s): PowerViewData.zip

Genetic Considerations in User Interface Design

There are several different genetic factors that are highly relevant to good user interface design. Color blindness is probably the best known. But did you know about motion sickness and epilepsy? We’ve been discussing how genetic factors should be considered in user interface design in one of my classes at Vanderbilt University School of Nursing.

According to the National Library of Medicine, approximately 8% of males and 0.5% of females have red-green color discrimination problems with the most common genetic cause called deuteranomaly. I have had to redo reports for bosses who had trouble distinguishing the colors on charts and graphs. There are several free tools available for simulating how things appear to a color blind individual. I haven’t found what I consider the ideal simulator, but when working on a Windows platform, I do like Fujitsu’s free program ColorDoctor. Color Oracle is a good multiplatform tool.

Research has shown that Asians have an increased susceptibility to motion sickness. This is not a problem just for people playing video games. Some programs used for educational purposes have rapidly moving scenes. Although still relatively rare, there are 2-D and 3-D videos being used in corporate environments where the viewer “flies” through and above 3-D representations of data. The user experience can be similar to that of a flight simulator program. If an employee or student becomes ill or even just quite uncomfortable from using a program, it could be a protected disability under the Americans with Disability Act.

Of greatest concern is the genetic predisposition to an epileptic seizure induced by a flashing light. There is a free, downloadable tool for assessing web content and computer programs for the risk of inducing a seizure. It is call PEAT – Photosensitive Epilepsy Analysis Tool. By the way, as a nurse I should inform you that if you ever do encounter a person having a tonic-clonic seizure (proper name for what used to be called a grand mal seizure), absolutely do NOT put anything into the person’s mouth. Call for medical help. Do not restrain the person but do protect the person from injury from the uncontrolled movements. It will help the medical people if you can tell them how long the seizure lasted.

I do realize that in software development, considerations of genetic factors probably aren’t going to be a part of your project’s requirements. This post was made for informational purposes only so that you will have some awareness of the issues should they ever come up.

References in approximate APA format:

Klosterhalfen, S., Kellermann, S., Pan, F., Stockhorst, U., Hall, G., & Enck, P. (2005). Effects of ethnicity and gender on motion sickness susceptibility. Retrieved from http://www-users.york.ac.uk/~gh1/pdf/2005Klosterhalfen.pdf

Stern, R., Hu, S., Uijtdehaage, S., Muth, E., Xu, L., & Koch, K. (1996). Asian hypersusceptibility to motion sickness. Retrieved from http://www.ncbi.nlm.nih.gov/pubmed/8825456

National Library of Medicine. (19 November 2012). Color vision deficiency. Retrieved from http://ghr.nlm.nih.gov/condition/color-vision-deficiency

Lamaspina, P., Biondi, G., & Santillo, C. (1989). Colour blindness (cb) distribution in the male population of albanian and croatian communities of molise, italy (with a review of the published caucasoid cb gene frequencies).. Retrieved from http://www.ncbi.nlm.nih.gov/pubmed/2487056

Panayiotopoulos, C. (2005). Chapter 13 reflex seizures and reflex epilepsies. Retrieved from http://www.ncbi.nlm.nih.gov/books/NBK2596/

Seizure first aid. Retrieved from http://www.epilepsy.com/epilepsy/firstaid

Posted Wednesday, November 21, 2012 11:50 AM by John Paul Cook | 0 Comments

BI Beginner: Why to Use Excel 2013 Now

Most corporations and many individuals are slow to adopt new versions of Microsoft Office, particularly if the upgrade to the previous version was very recent. Excel 2013 is a special case and offers significant productivity enhancements. If you do business intelligence work or otherwise make your living with Excel, adding (notice I didn’t say upgrading to) Excel 2013 now makes a lot of sense.

The Power View feature in Excel 2013 (Office Professional version) is a completely sufficient reason to add Excel 2013. It has to be enabled, which is explained further down in this post. As the Office 2013 installation help documentation tells us, “Except for Microsoft Outlook, you can have two versions of Microsoft Office programs installed on the same computer.”

Here’s what I installed along with Excel 2013:

Figure 1. Custom installation adding Excel 2013 and Office 2013 tools to an existing full installation of Office 2010 Pro.

Notice that OCR, which was removed from Office 2010, is back.

One nice feature of Excel 2013 is that each file you open has its own window.

A really helpful new feature is the ability to compare Excel workbooks. You can find this feature under Office 2013 tools. There is a similar tool for comparing Access databases. You really didn’t think Database Compare 2013 was for SQL Server, did you?

Figure 2. Spreadsheet Compare 2013 compares versions of Excel workbooks.

Here is what Spreadsheet Compare 2013 looks like when it is first opened.

Figure 3. Spreadsheet Compare 2013 before loading files to compare.

Figure 4. Dialog box for specifying files to compare.

In this example, the Excel workbook old.xslx uses an unweighted grade calculation that incorrectly reports that a student is passing. Workbook new.xslx uses a weighted grade calculation that correctly reports that the student is failing. The difference is in the formula used. Notice that the comparison tool easily and clearly identifies not only the different data values but also the different formulas.

Figure 5. Differences between the two workbooks.

The ability to compare Excel workbooks may by itself justify the addition of at least some parts of Office 2013.

To enable PowerPivot and PowerView, open a spreadsheet, go to File and then select Options in Excel 2013. Select Add-Ins. On the Manage dropdown menu, select COM add-ins and then click the Go button.

Figure 6. Excel 2013 Options menu.

On the COM Add-Ins menu, check Microsoft Office PowerPivot for Excel 2013 and Power View, then click OK.

Figure 7. COM Add-Ins menu.

After adding PowerPivot, it appears as a new item in the menu bar.

Power View is an impressive new feature that will be discussed in upcoming posts.

Posted Tuesday, November 20, 2012 5:59 PM by John Paul Cook | 3 Comments

Social media and special characters

I’ve previously blogged about using Unicode with T-SQL to put superscripts, subscripts, and special characters into text strings. Unicode is also useful in formatting social media such as Facebook, Twitter, and that dinosaur otherwise known as email. When you can’t set properties of text such as italicizing the subject line of an email message or adding subscripts to a Facebook post, Unicode can make it possible. There are Unicode characters that are intrinsically italicized. Others are intrinsically superscripted or subscripted.

Here is an example of a subject line containing a subscript and italics:

Figure 1. Italicized Unicode characters found in the Mathematical Alphanumeric Symbols group were used in an email subject line.

To specify Unicode characters, you need a good tool. If you a Windows user, BabelMap is a great tool and it is legally free, even for commercial use. (The author does accepted donations if you want to contribute.) Download the zip file, extract it, and double-click the exe to run it. It is a portable app, so there’s no installation. It just works.

What is really helpful about BabelMap is that it has features to help you find the Unicode character you need. You can select a grouping of characters or search for a specific character. For example, to find a subscripted form of the number 2, search for SUBSCRIPT TWO. Searching for SUBSCRIPT 2 won’t work, you must use SUBSCRIPT TWO.

Figure 2. Searching for SUBSCRIPT TWO.

The Edit Buffer is what I use to build Unicode text strings that I paste into email subject lines or social media.

Figure 3. Superscripts, special characters, Greek symbols, subscripts, bold, and italics in Facebook.

Sometimes Unicode special characters cause rendering problems as the Twitter post shows:

Figure 4. Notice the whitespace appearing before the uppercase O. It is an artifact because the input string does not have any whitespace preceding the O.

Even Notepad can be enhanced with bold and italics.

Figure 5. Notepad with bold and italics.

Posted Tuesday, October 02, 2012 6:05 PM by John Paul Cook | 0 Comments

More Posts « Previous page - Next page »