Wednesday, December 30, 2015

Using a Linked Server with Azure

I just spent the better part of an afternoon trying to figure out how to get a linked server to work between a SQL Server 2014 Database and an SQL Azure Database. After a lot of googling and experiments I was finally successful.

Here is what I learned:

  1. Don't use the GUI on the Linked Database node SSMS-- it will lead you places you don't need to go.
  2. The only Microsoft doc I could find with working example code is at, at the very end.
  3. Make sure you are connected to the master database when you run the sp_addlinkedserver, sp_addlinkedsrvlogin, and sp_dropserver stored procedures.
  4. When you run sp_addlinkedserver:
    1. Use 'sqlncli' as the provider.
    2. Use both the server parameter (What you are calling it on the client) AND the datasrc parameter (the actual name of the source database).
    3. srvproduct must be an empty string ('').
  5. You can't use windows logins (SQL Authentication only)

Here is some example code which I hope leads you in the right direction.


Sunday, December 20, 2015

Command Line SQL Server Interpreter

I've turned this into a project on GitHub, See this post for more info

I frequently have to debug data issues and reports on a SQL server database. In a lot of cases I just need to look up a few fields from a record or do a quick foreign key lookup. A SQL query tool or SSMS is overkill for the job because it takes to long to load.  SQLCMD requires a SSMS installation and I don't always have it installed where I'm working. So I wrote this lightweight powershell. script for these situations

Friday, December 11, 2015

Global Temp Tables are a Security Risk

I was working on a project to use bcp and xp_cmdshell to export data. Along the way, stumbled on a great idea to create reusable code and a stored procedure to export the results of any query to a delimited file -- I'd format the data into a global temp table  and export it with  bcp.  I even started working on a stored procedure that would accept parameters for the temp table name, delimiter, and export file name. Great. It's good, and it's fast, its reusable, and, I realized as I was working on it, it's a huge security problem.

The problem is the global temp table.  Here's what Microsoft says about global temp tables in SQL 2008 (the version I'm currently using) :

Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server. [Link]

So what this means is that once I populate my temp table anybody can look at the data.  Not good. I realize that if it's not sensitive data, it's not a big deal. If I use it wisely there shouldn't be a problem. But, once I put it in a stored procedure someone else may want to use it, and they may not realize that this is insecure. I bet this is how security vulnerabilities happen.

Before you flame me, I realize that there are security concerns about the use of xp_cmdshell but those can be mitigated by proper control of the sysadmin role.

More on BCP, if you are interested.

Read this entire thread if you are interested in learning more about xp_cmdshell security issues.

Sunday, July 19, 2015

Pondering Project Management

Questions to ask beyond the iron triangle.

  1. Is quality negotiable?
  2. Is risk negotiable?
  3. Are people resources the equivalent of  other resources?

Friday, July 3, 2015

Technology is not the answer

When I was a teenager, back in the 80's, I decided that I wanted to be a professional photographer.  My plan was to to sell stock photography. I already had a good understanding of the technology behind photography.  (We are talking about film photography, not digital photography.) The next step of the plan was to buy a good camera. I saved up for two years to buy a Nikon FEII camera, a sizable investment at the time. Now that I had good technology,  I could get started. I had just one problem, I didn't take very good pictures. In my teenage mind, I was sure that the technology would solve my problem.  What would have solved my problem was learning how to compose a good photo, something that can be done with any camera, not just an expensive one.  In fact the best photographers can use any camera to take a good picture.

Businesses frequently make the same mistake when adopting technology, they assume that the best technology will create the best outcome. The reality, however, is that a good processes create good outcomes, technology just improves the process. So what can you do to improve the processes before you implement the technology?
  1. Document your processes. (Flow charts and swim lanes work well.)
  2. Review that process. (A lot of problems may become obvious by looking at spaghetti on the charts from step 1.)
  3. Review the process with the people who do the process, they will have insights that you don't.
  4. Create a list of specific change to implement. 
  5. Measure the the improvements. 
  6. Return to step one.

So where does technology fit in, somewhere between step 4 and 5. Before you implement technology  make sure you do some cost justifications. Saving  $500 per month may not justify $100,000 worth of software. 

Sunday, June 7, 2015

I'm doing a little blog 'remodeling' today

Please bear with the frequent design changes and layout issues.

I hope to provide you with a better reading experience when I'm finished.

Thanks for your patience.

Saturday, May 16, 2015

29 Restaurant Tips I've learned from "Restaurant Impossible"

Image Courtesy
While I claim I don't like reality shows, I do have a soft spot for business rescue shows. The one I have watched the most is Restaurant Impossible on Food Network. If you don't watch the show,  the show is about a gruff but lovable chef, Robert Irvine, who goes into restaurants  where he  revamps the food, management, service, and decor 'in Two Days with $10,000'

This list of tips are based on my experience watching the show. If you are going to be on the show, this is what's coming.

Warning: Watching  reality TV in no way makes me an expert on the restaurant business. Beyond watching reality TV, my only other practical experience with the restaurant business is as a customer. If someone who has actually been successful in this business gives you advice, take it first.

 29 tips for running a successfully restaurant from that I  gleaned  by watching Restaurant Impossible

  1. Don't be stubborn. I know you think that its a virtue, its not. Its probably the primary reason you are (or need to) be on the show.
  2. This is a business, the goal is to make a profit. Don't do things what keep you from making a profit.
  3. A restaurant is not a good 'hands off investment' . You have to be there  to make sure its running properly.
  4. Just because someone likes your cooking or you like to cook doesn't mean you you should open a restaurant. You need to really learn the entire business first.
  5. Owners and managers should have well defined roles.
  6. Managers and owners shouldn't contradict each other, that why you have 'well defined' roles. If there is an issue it should be resolved in a meeting outside of operating hours.
  7. You should have regular meeting among staff and managers to make sure there is good communication.
  8. Have a procedure manual for your employees. Make them learn it, then hold them accountable.
  9. Treat your partners with respect.
  10. Treat your employees with respect.
  11. Expect respect from your employees.
  12. Good actions should have good consequences, bad actions should have bad consequences.
  13. Your employees are employees not family.
  14. If your some of employes are also family  communicate roles, expectations and consequences, before they start working. Make sure they the want to be held to these requirements before they start working. It's much easier to hire someone, rather than fix a relationship.
  15. Family is much more important than a restaurant.
  16. Have a good accounting system and use it. If you are not comfortable doing this, hire someone who is.
  17. Have a ticketing system for orders.
  18. Pay your taxes. The government doesn't care about your business.
  19. Pay your employees because you  want them to care about your business.
  20. Rule of Thumb: Food cost should be between 20 and 30% of the menu price.
  21. Don't overprice your food.
  22. Don't oversize your portions.
  23. Your customers are polite, they are not going to tell you the truth about your food, service, or decor. Use an anonymous method and/or check social media to see what they really think.
  24. Salt, pepper, and spices are your friend, use them appropriately.
  25. Fresh, not frozen or canned. 
  26. Microwave ovens-- Just say No!
  27. Clean your kitchen daily and follow the health department rules. Its bad form to get your customers sick.
  28. Clean the front of the restaurant daily, nobody wants to eat in filth
  29. Make sure you dishes and silverware are clean.

Sunday, May 10, 2015

Its been a while

Wow, I haven't posted in more than a year. I've been honing one of my most 'important' skills -- procrastination. Its not that I haven't done anything--over the last year:

  • Failed blog on Go programming
  • I did add a post to linked in that's worth reading The Once Social Network to Rule Them All
  • Some fun and exciting medical issues
  • Finally getting on the same page about finances with my wife (This is a huge win for my family) 
  • A major personal medical issue.

In any case, I'm going to try and do better this time and be a bit more consistent about posting

 Thanks for hanging in there