A while back I started keeping a list of all the things I learned to make working with SSIS less painful and I thought I’d share. These are small things: tips, tricks and hints. Just stuff I wish I had known when I first started working with SSIS.
Continue Reading…
Tag Archive - SQL Server
Tips, Tricks and Hints for Using SSIS from A-Z
SSIS Output to Multiple Files
Let’s say a user asks you to create a flat file output from a database query. Piece of cake. Open SSMS, write the query, run it and copy the results (with headers using SSMS 2008) into a text file. Send the user the file and and you’re done with time left over for a siesta.
But oh wait—they want to be able to open it in an older version of Excel, which will only display ~ 65k rows per sheet. This file has 190k rows. So they ask you to write the output to multiple files, limiting the total rows per file to 60k.
Now what?
You could simply open the raw flat file, go to line 60,001 and cut-paste lines 60,001–120,000 into another file and repeat as necessary. That’s ok for a one-off task but not a good strategy if you are going to be doing this several times. It can be cumbersome and error prone to manually create several files this way.
No—the better approach is to make SSIS do the heavy lifting. I had to do this recently and thought I’d share my solution. Continue Reading…
SQL Server 2005 How to Change Default Backup Location
In SQL Server 2005, you can easily change the default location for log and data files. Open SSMS and connect to the instance you want to configure. Right-click on the instance and choose properties. Then select the Database Settings page and change the Database default locations.
Clickity-click-done. (I know, being a DBA can be tough sometimes. That’s why they pay us the big bucks Sparky.) Continue Reading…
The DBA’s Desk
It is Friday and thankfully I’m knee-deep in SQL Server work today (instead of, say, sitting in meetings). It seemed like the perfect opportunity to open the kimono a bit and show you what the DBA’s desk looks like.
This was only partially staged. I got rid of the dust bunnies.
This shot showcases the non-computer tools required by the proficient DBA. Each item was carefully selected and is ABSOLUTELY ESSENTIAL to accomplishing the DBA’s job. I’ll explain. Continue Reading…
Resources for Learning More About SQL Server Internals
A while back, I spent about a month working through several SQL Server performance issues after upgrading three systems from SQL Server 2000 to SQL Server 2005. During that time I would realize that my knowledge of how SQL actually manages data at a low level was sketchy. In the process of resolving said sketchiness, I came across a few resources that proved helpful. Continue Reading…
Thinkering for the MCTS 70-448
It’s been a long time since I’ve been in a classroom. I don’t remember exactly how I used to study in school. Getting back into study mode has been a bit of a challenge. But preparing for the MCTS 70-448 has yielded some interesting fruit. Continue Reading…
How to Convert Taxware Tax Exempt Certificates to Vertex Tax Exempt Certificates
As part of a project at work last year I had to convert ADP Taxware tax exempt certificates to Vertex tax exempt certificates. I searched the web and didn’t find any information on how to go about doing this. I have to believe that somebody has tackled this situation before and applied a much more elegant solution. But sometimes you just need to get something done regardless of how “pretty” the solution is. Continue Reading…
Stored Procedure Naming Convention
Never use the sp_ prefix for a user-stored procedure. One reason is that it can make it hard to tell which sprocs are delivered and which are user-defined. Another is that if you prefix your sprocs with sp_, SQL searches for the prefix in the master db each time the procedure is called, before looking in the local database. Use usp_ instead.
From Microsoft eLearning Course 3595: Establishing Database Conventions and Standards for Microsoft® SQL Server™ 2005.
The Difference Between the DATETIME and SMALLDATETIME Data Types
DATETIME requires 8 bytes of storage and stores dates from 1/1/1753 to 12/31/9999. It is accurate to about 3 milliseconds.
SMALLDATETIME requires only 4 bytes of storage. It can stores dates from 1/1/1900 to 6/6/2079 and is accurate to 1 minute.
If you’re not in the mood to wade through BOL, you can find a quick overview with a fair amount of detail on SQL Server datatypes here.