Wise man or Wise guy? You Decide

Just another WordPress.com weblog

Archive for the ‘Problems’ Category

Access Denied, Not Possible

Posted by sqlwiseguy on August 27, 2009

What’s going on here?

When trying to open Default Trace files on Windows 7 and SQL Server 2008 I got the “You don’t have permission to open this file” error.  My first and second thoughts were “How can this be, I’m an administrator!?”.  SO the first thing I tried was reading the files using fn_trace_gettable in SSMS.  This works, what is different between SSMS and Profiler?  Nothing that I can think of.

What’s Next?

First step, check permissions on the folder, yup, full control.  Second step, turn off inheritance.  Third step, bang head on desk!  So off to google/bing I went.  I found this post by the SQL Server PSS team, , which told me about the issue, but not a good way to solve it.  I can’t just grant rights to each file because the Default Trace rolls over and creates new files, I need rights all the time.  Then I found this post, which says the owner of the files needs to copy and paste them out to a another shared folder.  While not the most helpful idea for my situation I tried it, and it worked!  I’m still annoyed at this point because I don’t want to have to move the files to read them.

Finally, a solution

Once again it is Twitter to the rescue.  I tweeted my problem and got a pretty quick response from Brian Kelley (Twitter|Blog) asking about OS and if I ran Profiler as Administrator.  I answered Win 7 and Yes, I was mistaken on the Yes it turns out.  I thought because the account I was running under had administrative privileges that I was running as administrator.  This is not the case in Vista, Win 7, and Windows Server 2008 when UAC is on.  With UAC on you still need to run Profiler using elevated privileges by right-clicking and selecting “Run As Administrator”.  That is the answer.  I should also note that Ken Simmons (Twitter|Blog) also produced this answer.   Jonathan Kehayias (Twitter|Blog) also chimed in to let me know that you can set individual applications to always run as administrator by right-clicking, selecting Properties, Compatibility, and checking the Run as Administrator box.  All of this in about 20 minutes and this is after I spent at least an hour fighting with it and searching the internet for a solution!

Conclusion

I really need to get a better understanding of UAC, and believe it or not, Twitter works!

Posted in Problems, Profiler, SQL Server, Twitter | Leave a Comment »

Software Developers, Remember that People Need to Use Your Software

Posted by sqlwiseguy on May 7, 2009

As a hybrid DBA\Developer I had the message of the title reinforced through a negative experience with a web site this past week.  Please bear with me as I tell a fairly long story/rant to illustrate my point.

The web site I had an issue with is www.sunpass.com.  SunPass is Florida’s prepaid toll system and they provide a web site to manage your account.  There are 2 devices you can purchase, a portable transponder and a SunPass Mini Sticker transponder which applies to your windshield and is only good for 1 vehicle.  My wife and I purchased a SunPass Mini and I went to the web site and activated it and I set up my account so that it would auto-replenish using one of my credit cards when the balance dipped below a certain threshold. 

Well, last week we traded in the vehicle that had the Mini in it, so we bought a new Mini for our new vehicle.  This is when the trouble started.  I went to the SunPass web site to activate the new Mini.  I logged into my account and nowhere on my account home page was there a link to activate a new transponder or remove the old transponder.  I thought, “Well, the package says I can activate it online and I activated the first one online so I know it has to be here somewhere.”  So I double–checked and nope, not there.  I went back to the SunPass home page and, lo and behold, the “Existing Customers activate a New Transponder” link was on that page instead of my account home page.  Alright that’s not the most intuitive interface, but I only spent about 10 minutes trying to find it, but at least I found it.  So I clicked on the link and here is what happened:

SunPassError Wait a minute, I don’t have a commercial account, what’s going on?  So I lookup the customer agreement and here is the definition of a commercial account:

5.2 Commercial Accounts: Primarily for companies or businesses with corporate owned, leased or rented vehicles and/or trailers. A current Federal Employer Identification Number (FEIN) must be provided to open an account of this type. FEIN information must be kept current and may be requested by a SunPass® representative at any time. The minimum opening balance for a Commercial Account is dependent on the User’s estimated monthly usage, but must be a minimum of $50.00. User shall be notified of any changes to the required replenishment amount on their monthly Account Summary Statement. For Commercial accounts to qualify for tax exempt status, User must provide a valid tax exemption certificate at the time of account activation.

Nope, I didn’t provide any of this information when I setup my account so, why am I getting this message?  I found the contact us link and sent them a message asking why I was getting the message.  As expected I got a non-response response:

Please call customer service

The next day I got a letter from SunPass telling me that I had a negative balance and I needed to call them to pay up.  Now I’m not only confused, but I’m a getting a bit ticked off, I mean, I did setup my account to auto-replenish, why isn’t it happening?  Back to the web site to make sure auto-replenish is setup, yup, it is.  So I call customer service to deal with the issues.   Here are the answers I got:

  1. Can’t add transponder because I have a commercial account.
      The reality is that when they added the new Mini’s they could not treat them like regular transponders, why I don’t know, so if you open your account with a Mini your account is treated like a commercial account.  So you have to call to activate any new Mini’s on your account.  I asked “How was I supposed to know that?” and the answer is you have to call.  So I suggested that they fix the message to tell me the REAL reason the web site doesn’t do what asked.
  2. Can’t delete the old transponder.
      Again, because it is a Mini it is treated differently and they assume you will disable it by taking it off the windshield so you don’t need to ever disable the transponder.  I asked, “How was I supposed to know that?” and the answer is you have to call.
  3. Auto-replenish isn’t working.
      Apparently the system had a “glitch”, so while the web site is showing that auto-replenish is setup it really never was.  I asked, “How was I supposed to know that?” and the answer is you have to call.

So now to the point of the post. What could the developers/designers done differently to provide a better user experience.

  1. Put account related activities on the account home page.  So an add transponder link on my account home page in addition to the main home page would have made life much simpler and intuitive.
  2. Provide a message that conveys the real reason functionality is not available.  Tell me that you cannot add a Mini to an existing account, not that I have a Commercial account.  In my opinion, the real answer is to not provide the functionality at all.  If account type = Commercial do not show the Add transponder link.
  3. Catch errors and tell the user that their efforts were unsuccessful.  If something goes wrong in setting up auto-replenishment tell me!  If it happens in a batch at the end of the day, send me an email telling me it failed.  Don’t wait to send me a letter when I’m already in the hole.

Please keep the end user in mind when designing and developing your applications.  Don’t frustrate your customers, whether internal or external, with poor design.

Posted in Expectations, Lessons, Problems, Programming, Rants, Standards, Suggestions, Technology | 1 Comment »

The Deception of IsNumeric()

Posted by sqlwiseguy on April 14, 2009

Has anyone else ever used the IsNumeric() function to try to eliminate this error (or similar):

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

yet still get the error? Isn’t that what the function is for?  Here’s what is says in BOL:

ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0

But run this code (generates a list of ASCII Characters):

;WITH cteNumbers AS
   
(
   
SELECT TOP 255
        ROW_NUMBER
() OVER(ORDER BY NAME) AS n
   
FROM
       
sys.all_columns AS AC
   
)
   
SELECT
       
n AS ASCII_code,
       
CHAR(n) AS [character],
       
ISNUMERIC(CHAR(n)) AS is_numeric
   
FROM
       
cteNumbers
   
WHERE
       
n <=255 AND
       
ISNUMERIC(CHAR(n)) = 1

And you’ll see that the first 10 rows and last 8 rows returned are not what I would consider numeric.  Or how about this:

SELECT
   
ISNUMERIC('20,00')
GO
SELECT
   
CONVERT(DECIMAL(9,2), '20,00')

The first statement returns 1, while the second fails. 

Here is the last interesting behavior of IsNumeric() in relation to Cast/Convert provided by Frank Kalis on this SQLServerCentral thread:

SELECT ISNUMERIC(0X0e) AS E, ISNUMERIC(0X0fAS F

E           F          
----------- -----------
0           0

(1 row(s) affected)

WHILE

SELECT CAST(0X0e AS INT) AS E, CAST(0X0f AS INT) AS F

E           F          
----------- -----------
14          15

The moral of the story is that IsNumeric() <> CanBeConvertedToNumeric().

So what is the answer?  I don’t know.  You would need to customize the solution to meet your specific situation.  Jeff Moden suggests an IsAllDigits function in this thread on SQLServerCentral for instances where you want to eliminate rows with non-numeric characters.

Do you have anything that you recommend?

Posted in Bugs, Code, Code Example, Problems, SQL Server, SqlServerCentral | Leave a Comment »

Two Mistakes

Posted by sqlwiseguy on November 12, 2008

I was challenged by Andy Warren to write a post about two mistakes I have made in my career since he was sure I had made many. I’m sure I have made many, but only one SQL Server mistake sticks out in my mind although I compounded it while trying to fix it.My second mistake does not relate to SQL Server, but did have a negative impact on the business I was working for.

  1. In the first year of my career my colleagues and I had written an order entry system for the paper mill we worked at using SQL Server and classic asp. Being an agile shop, even before agile was really popular, we rolled it out and made upgrades/fixes on about a weekly basis. One evening I was tasked with rolling out an update to the database. We had our development/QA database up to date with the latest data and I had a dts package that I had developed to migrate the changes. Well, I started the package, and I realized I had made a mistake and stopped the package. THAT was my REAL mistake as the first step of the package DROPPED the existing tables and I stopped the package after tables had been dropped, but BEFORE they had been recreated. I had not put the package ina transaction (another mistake) so there was no rollback. Ouch! Unfortunately, I was young and not paranoid, like any good DBA would be, so I had NOT taken a full database backup before starting the process. I did have the previous night’s backup and all the transaction log backups from the day so I was able to restore the database and then re-do the upgrade. Of course it took me “forever” to do the restore because first I forgot to add WITH NORECOVERY to the first restore so I had to do the full restore twice! So what should have taken me an hour took 5 hours. What did I learn? Always, always, always take a full backup BEFORE doing any maintenance or upgrades on a database or server.
  2. My second memorable mistake (I am sure co-workers have others, I have blocked the rest) does not have anything to do with SQL Server, but was a pretty big one. Our mill ran on an AS400 based system that tracked, labeled, wrapped, and shipped the product. When a roll of paper (not like a toilet paper roll, but a large roll of 500+ lbs) was weighed, labeled, and wrapped the system compared the actual weight with an estimated weight to make sure the roll was the one that was expected. Humans would be a barcode on the end of the roll that was scanned as it was weighed and, believe it or not, occasionally they would put the wrong barcode tag on. Obviously, this process would take the scanned ID and read the DB2 database to get the estomated weight. Well, a younger co-worker was on call and had a problem that he could not solve and I was the only person he could get in touch with to help. I was not and am not an AS400 or DB2 expert, but I “knew” where to look for this problem. We checked for locks on the “rolls” table and, sure enough, a process had a lock on roll that it had not released. The precsribed treatment for this diagnosis is to “kill” the process that had not released the lock. This was done fairly regularly and all that happened was that user just needed to re-enter the program they were in, no big deal. Of course, I did not notice that the process I was killing was a system process, not a user process. So I killed the wrapper program and what was already the bottleneck in our productin line was SHUT DOWN! Needless to say the production folks were NOT happy and neither were the other IT guys who had to be called at 2 AM to first figure out what I had done, and then to fix it! I did learn how to determine what was a system process on an AS400 from that mistake.

I’ll pass along the challenge to a couple of SQLServerCentral friends, Grant Fritchey and R. Barry Young.

Posted in Problems, SQL Server | Leave a Comment »

What happened to my profile?

Posted by sqlwiseguy on October 1, 2008

When I arrived at work on Monday morning, I decided I should reboot my computer. I like to reboot regularly since that is what I always tell people to do when they call me with a computer problem. Well this time I made a mistake. When I logged in and attempted to work I found that my rights had been restricted and severely. I could no longer access my C drive and did not have RUN available. This was a little before 8:00. When the network admin came in, I put him to work on the problem. First he checked all the Group Policies that had been applied to my account and, of course, there was nothing out of the ordinary. Then he tried forcing a group policy refresh, and once again, no joy. Finally he logged into my PC and renamed my local ntuser.dat file and with a reboot I was logged back in, albeit without any of my personal settings as I had a new profile. It was about 11:00 then.

All this because I decided to reboot.

Posted in Problems, Windows | Leave a Comment »