John Paul Cook

32-bit ODBC on Windows Server 2008 R2

2010-03-16T23:16:43Z

Heterogeneous data access requires having the right drivers. If you have to use 32-bit ODBC drivers, you won’t find then when you start the Microsoft ODBC Administrator because it is 64-bit. The 32-bit ODBC Administrator is found here: C:\Windows\SysWOW64\odbcad32.exe You might want to make a shortcut for it to make it easy to find. You’ll need to use it when make 32-bit ODBC data connections. Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!...(read more)

Oracle Linked Servers on Windows Server 2008 R2

2010-03-15T17:05:19Z

Oracle hasn’t yet released versions of its client software for Windows Server 2008 R2. If you need to create an Oracle linked server, that’s a problem. You’ll see this installation block when attempting to install the Oracle client software for Windows Server 2008: It’s very simple to fix. Check the first checkbox to make the installer ignore the version check. Click Next and ignore the warning you’ll see. The installation should complete successfully. Windows does offer various strategies for mitigating...(read more)

Using Windows Authentication from a non-domain joined machine

2010-02-26T23:28:00Z

Sometimes it is necessary to use Windows domain credentials to authenticate with a SQL Server from a non-domain joined machine. Here’s a trick from my coworker Martin Kastenbaum to pass Windows domain credentials from a non-domain joined machine. You can pass your Windows credentials to SQL Server Management Studio by making a simple modification to the shortcut you use to launch it. It's probably a wise idea to make a copy of your SSMS shortcut and make all changes to the copy, not the original....(read more)

ODBC Multiplatform Survey - Microsoft needs your input

2010-02-24T01:21:00Z

This is from Chris Lee, Senior Program Manager for SQL Connectivity at Microsoft: The Microsoft SQL Server Connectivity team has received feedback indicating interest in ODBC support for non-Windows platforms and is now conducting a more detailed survey of this area. The survey takes less than 10 minutes to complete and you can take part by clicking on the following link https://www.surveymonkey.com/s/SQL_Server_multi-platform_ODBC_survey . The is open until March 10th. Your input will help shape...(read more)

Don’t take the SSMS login for granted!

2010-02-13T02:24:26Z

My boss couldn’t login using SSMS after he told me to drop a database no longer in use. It was easy to fix. Think about it – he could login before the database was dropped, but not after. It was his default database. When your default database does not exist or otherwise isn’t valid, your login fails. I reset his default database to a valid database and he could login. But he wanted to know what he could have done in case I wasn’t around. I thought of sqlcmd, but knew there had to be other workarounds....(read more)

SQL Heartbeat – my favorite free tool

2010-02-12T02:16:00Z

There are many great scripts available for monitoring SQL Server. But admit it, don’t you miss animation and color? I’ve been using SQL Heartbeat for about two weeks and I really appreciate the animation and color. These features make the output highly effective and easy to grasp. Processes grow larger as they use more resources. Lines appear between processes when locking occurs. I find the tool so useful I leave it up all day on my secondary monitor. There have been several times when the only...(read more)

SQL Search - free tool for searching your SQL Server

2010-02-11T16:52:00Z

I downloaded Red Gate's new free utility SQL Search http://www.red-gate.com/products/SQL_Search/index.htm earlier this week. A coworker just asked me if there is an easy way to search all stored procedures for a specific string. SQL Search was the answer! It exceeded my expectations. SQL Search integrates with SSMS and is completely intuitive. Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!...(read more)

Helping Haiti: Geeks Building Applications at CrisisCamp

2010-01-19T18:24:00Z

Stop what you're doing and go to http://crisiscommons.org/ and see how you can use your geekiness to help the people in Haiti. So far there are 8 cities in the United States holding CrisisCamps for Haiti this Saturday, January 23. There's a lot going on and more to be done. I'm sure there's a place for you. If you can't travel to one of the CrisisCamps, cash is always helpful. Low on cash? Have a garage sale. Have any gold coins you received as a gift or bought as investment? Gold prices are relatively...(read more)

Vote on Connect: Graphical UI for SQL Server Agent

2010-01-14T03:36:48Z

When you only have a few SQL Server Agent jobs, it’s easy to keep track of and make sense of them. I’m using a graphical free tool called SQLjobvis (screen capture below), available at http://www.sqlsoft.co.uk/sqljobvis.php . It provides a visual view of completed SQL Server Agent job steps. See the screen capture below. Wouldn’t it be great if Microsoft offered a graphical UI for SQL Server Agent? I’d like Microsoft to provide viewing and graphical editing of SQL Agent job schedules. Please vote...(read more)

Getting off to a good start

2010-01-01T22:20:45Z

There are several things I do to get each New Year’s Day off to a good start. All of my machines have been to Windows Update today and are updated and defragmented. My DBA toolkit on a stick is completely new for 2010. When I went to http://portableapps.com and downloaded the latest full suite , it was a pleasant surprise to find out that the portable version of Open Office finally works on 64-bit systems. These are my recommendations for portable applications you might want to add: 7-Zip portable...(read more)

Virtual PC and Virtual Server hotfixes and updates

2009-11-29T18:31:00Z

Many of you use Virtual PC and Virtual Server, but not many people are aware of some new hotfixes and security updates released in 2009. Before getting to those, the latest versions of those two products are Virtual PC 2007 Service Pack 1 and Virtual Server 2005 R2 Service Pack 1. You first need to be at that level. Here is a hotfix rollup for Virtual PC 2007 SP1: http://support.microsoft.com/kb/958162 released February 20, 2009 This is a security update for Virtual PC 2004, Virtual PC 2007, Virtual...(read more)

White elephant gift exchange for DBAs

2009-11-26T02:30:00Z

In the holiday season, white elephant gift exchanges are somewhat popular in the workplace. This is a really great definition from Wikipedia : "the term white elephant refers to a gift whose maintenance cost exceeds its usefulness." Now that definitely describes some databases I've seen! We can do a white elephant gift exchange of worst schemas. Here's my white elephant constructed of snippets from real production databases: create table [table] ( [column] varchar ( 1 ) --think Hungarian notation...(read more)

Windows 7 Problem Steps Recorder

2009-11-21T21:16:00Z

The Windows 7 Problem Steps Recorder is yet another reason to upgrade to Windows 7. It records what the user actually did, not what the user claims to have done. I used it to record the attached session of running an invalid T-SQL query in SSMS. When the recording was made, there was a step I added that’s not completely obvious from the recorded output. Notice that the query I executed was highlighted. I did a Control-C to copy the query into the past buffer. The Control-C event was recorded as User...(read more)

SQL Risk and Health Assessment Program (SQLRAP)

2009-11-19T17:47:00Z

The SQL Server Riadk and Health Assessment Program, otherwise known as SQLRAP, isn't well known. It hasn't been blogged about much. Today I'm analyzing SQLRAP output and developing a plan to implement changes it suggests. You can read more about the program here: http://www.microsoft.com/downloads/details.aspx?familyid=8D54DB4D-3232-4CF8-8BA5-E80557835421&displaylang=en The SQLRAP service is when you pay for a premier support engineer to come to your site and run the SQLRAP tools to collect metrics...(read more)

Script to determine cardinality for index analysis

2009-11-19T13:32:00Z

When reviewing a client's database, I suspected there might be indexes on low cardinality (i.e., low "uniqueness") columns. Typing COUNT ( DISTINCT colName )) became tedious quickly. Time for a script. Oops, wait, in deference to proponents of test driven development, time for a test case and then time for a script. Here's the test case: CREATE TABLE dbo . CardinalTest ( a bigint NULL, b binary ( 50 ) NULL, c bit NULL, d char ( 10 ) NULL, e date NULL, f datetime NULL, g datetime2 ( 7 ) NULL, h datetimeoffset...(read more)