Coder's Revolution

Do you want a revolution?

Category Filtering: 'SQL'

SQL Server DTS: I Love It When A Plan Comes Together... Then Falls Apart Again.

SQL
At work, we are building some content management utilities to keep track of our training materials and document all of our content in a database. To get a head start, our marketing team started a large Excel spreadsheet to list, categorize, label, and tag our hundreds and hundreds of resources. We decided to attempt to automatically import some of the content directly from Excel to keep them from having to hand-enter it again. Seeing as how we are wandering around in the cold, dark, stone ages of SQL Server 2000 I thought I would throw the .XLS file at a DTS package and see what happened.
Comments are currently closed

SQL Server Performance: How Are My Indexes Being Using?

Performance, SQL
You may have a full-time DBA where you work, but a lot of us share the CF developer and DB developer hats. Your SQL Server's performance can be an easy thing to overlook if your database is very small, or your website gets very little traffic. The easy (but not always correct) answer to most performance problems is "add an index". Some indexes are an obvious help, but how do you tell if the less obvious ones are really being used? It is possible to have too many indexes. In addition to bloating the size of your database, they take time to update which can actually slow your application DOWN on inserts and updates.
Comments are currently closed

SQL Server Gotcha: Implicit Unicode Conversion

ColdFusion, Performance, SQL
A recent thread on CF-Talk brought up this very good topic. It deals with the performance hit you can get when SQL Server implicitly converts your data to nchar and nvarchar for you when you have enabled Unicode support in your data sources. Unicode text cannot be stored in normal char or varchar fields. It must use nchar or nvarchar. These data types use two bytes per character, which means you can only store half as much text in them (limit 4,400 instead of 8,800). The problem is two fold:
  1. SQL Server cannot directly compare a varchar and nvarchar value so it must convert one.
  2. String manipulation or conversion on an indexed column will render the index useless
Comments are currently closed

SQL Server: How Many Work/Week Days In Date Range

General, SQL
I had the need to calculate how many week days existed in an arbitrary range of dates today. I Googled for a while but didn't find anything I liked and I really didn't want to iterate over the entire range and count. For what it's worth, this is what I hacked out.
Comments are currently closed

MSSQL's openquery Saved Me

AS400, Performance, SQL
Sorry I've been quiet for the past few days. My Flex adventures took a quick detour through the massive land of our AS400 and DB2. Now that I had a prototype of my pretty line, bar, and pie charts I needed some real data. My job was to write a process to fetch our sales data from the AS400 server here at work nightly and populate some SQL Server tables with it. Easy Peasy, I thought. I didn't know what I was in for.
Comments are currently closed

SQL Date Parsing Error

SQL
I am writing some SQL to pull data from our AS400 here at work. I was getting the error "Arithmetic overflow error converting expression to data type datetime." from the following code where date_column contained a date formatted like 20080930: year(date_column) At first, I thought there was some bad data in the column. It turns out, the datatype was int as opposed to varchar like I had assumed. This fixed it: year(cast(date_column as varchar(8)))
Comments are currently closed

How to axe your transaction log

Security, SQL
If you are using MS SQL Server and ever want to just obliterate your transaction log, you can use the following SQL (where your database name is "foo"): BACKUP LOG foo WITH TRUNCATE_ONLY DBCC SHRINKFILE(foo_log,2) Don't ever do this to a database you care about like, say, production. I wanted this because I am screwing around creating rainbow tables of SHA-1 hashes. The Cartesian product of joining a table to itself on 1=1 is very handy for producing all possible combinations of a set of characters. Inserting a few million records can put a lot of crap in your transaction log though.
Comments are currently closed

ColdFusion SQL Color Coder

ColdFusion, SQL
A while back while building a monitoring tool for running processes on SQL Server 2005 I encountered the desire to color code SQL in the same manner of MS Query Analyzer (Or Management Studio) for HTML output. I hit up the CF-Talk list and Google for an existing ColdFusion implementation but got crickets. Not being one to give up, I created my own.
Comments are currently closed

Installed SQL Server 2005 Express

ColdFusion, SQL
I installed SQL Server 2005 Express today on my home PC. For the most part it was painless, but there were a couple snags. First, it took me a couple tries to find the right download page. A bunch of the links on Microsoft's site kept redirecting me to the 2008 page. Still other pages wouldn't even mention 2005.
Comments are currently closed

Disabling MySQL's Backslash Escaping Per Data Source

ColdFusion, Security, SQL
It has been mentioned in several places that MySQL 5.0.1 has a NO_BACKSLASH_ESCAPES mode it can be run in to prevent backslashes from being an escape character. Thanks to Azadi Saryev for pointing it out on my blog and Mark Krugers as well. Jake Munson even posted instructions for applying the setting to your SQL server at startup. For the record, you can also use this setting on a specific data source.
Comments are currently closed