Querying an Oracle linked server fails
The other day, I needed to pull some data from an Oracle server on our network. The oracle server was set up as a linked server on my SQL Server 2005 machine. For some reason, the query didn’t work. Now, when working with an oracle linked server, you have to include the linked server name,...
Great Circles in SQL Server 2005 and SQL Server 2000
Recently, I had a request to calculate the distance between 2 GPS coordinates. Now, the system I am working with is SQL Server 2005. SQL Server 2008 gives us the geography data type, which is great and has a lot of powerful features. But, if you’re using SQL Server 2000 or 2005, you won’t have...
Automatic Index Rebuilding
While working on a large project, I found that the queries were running slower and slower as the days went on. We were working with current and historical data and were only joining about 3-5 tables at a time. Each join was bringing back between only about 50K rows or so, and the results were...
Unshare Folders in Windows 7
I was formatting the several of my machines the other day and I was moving files back and forth. Sure, for MP3s or videos, I use an external USB hard drive. But there are smaller files, such as the Adobe Flash installer or Opera, which are pretty small. For those smaller files, I use a...
Kill all Connections to a Database in SQL Server
One of the databases I work with runs a ton of updates overnight and in the early morning. Now, from time to time I run into a problem where a user has a lock on a table. This usually stems from, as far as I can tell, an MS Access query that is left open...
Importing Data into SQL Server – Using Excel Shortcuts
I’ll be the first to say, the switch from DTS to SSIS was painful. DTS would go through a lot of implicit conversions so you didn’t have to worry or think about them. But SSIS was not as merciful. From different fields lengths to different encodings, SSIS wants you to be specific. Sometimes, it is...
Importing Data into SQL Server – Using the Import Export Wizard
Today begins the series on importing data into SQL Server 2005/2008 using the various built in tools. With SQL Server, there are several ways of getting your data from one place to another. Today, we will focus on using the wizard from SQL Server Management Studio. Before we begin, I am using a simple database...
Storing Computed Columns in SQL Server
From time to time you will run into a case where you need to store computed columns in your SQL Server tables. Examples of computed columns include taxes on a sale or business days between several dates. Setting up computed columns in SQL Server 2008 is fairly simple. You simply give the column a name...
Could not install the custom actions
Learn how to fix the error "Could not install the custom actions" message
View All Permissions for Objects in SQL Server 2008
There are times when you need to audit user permissions to ensure that things aren’t getting inserted, over-written, or deleted. I looked around and found this script which uses several sys objects, which gives us most of the information we need. select dp.NAME AS principal_name ,dp.type_desc AS principal_type_desc ,o.NAME AS object_name ,o.type_desc as Object_type ,p.permission_name...
Using ActiveX to set query dynamically in SQL Server
Learn how to use ActiveX to dynamically change a query.
