Working with Seconds in Excel
I recently had to use back end data from SQL Server to deliver several reports in excel. All of my SQL code gave me results in seconds; however, the users had distinct formatting requests. I would have to use a few excel tricks to get things just right in each report.
Using datediff to display results as HH:MM:SS
DateDiff is a great tool in SQL Server. But you have to extend DateDiff a bit to get things in a nicer format, such as HH:MM:SS. See what Rudy found on SQLServerCentral.com which saved the afternoon!
Long TRANSACTION Identifier is Long
If you’re like me, you like using descriptive variable, procedure and function names. Along with those, descriptive transaction names are great, so you know exactly what type of havoc you were throwing at SQL Server. But, if you’re too descriptive, you’ll get the following error: Msg 103, Level 15, State 2, Line 1 The identifier...
Rudy: An MCITP Today. Rudy: An MCM Tomorrow?
Rudy Rodarte is thinking about taking an MCM immersion class given by the good folks over at http://www.sqlskills.com.
Determine Tables Sizes in SQL Server
Everyone is worried about their database size. Even though storage is relatively cheap, if your database is growing linearly or exponentially, you’re going to run into issues down the road. One quick way to check things out is to look at your table sizes. I looked all over the web and found Mitch Sellers’ blog...
Use SQL Server DMVs to find a table’s IDENTITY Column
Use SQL Server DMVs to find a table's IDENTITY Column. The SQL Server DMVs sys.objects and sys.columns contain valuable information that can identify IDENTITY Columns.
Incorrect syntax near ‘GO’ when creating Dynamic SQL
Learn about line breaks when creating dynamic SQL.
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...
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...
