to a newer version of Wordpress, and It seems to have lost some functionality. Getting it fix asap.

No Comments | In: Uncategorized | | #

it’s small and very funny*…

Okay it’s not so small but it’s called by a web service which updates a table that fires a trigger that starts the job(not my design) which updates an activity table and also starts my SSIS packages spinning. But I also want the job to fire, should the web service fail to call it.

All in all it seems really messy. I need to make it cleaner.

Also, SQL server 2008 offers a beautiful job status API however  helpdesk has no access to my production server to use it to see what happened to a failed job at 3am. So I need it in a format that someone who has very little access to the database to see.

I once wrote an ASP web application that would take all job status and show a green, yellow, or red light on the page depending on the health of the job. I may have to do something like that again. Unless there’s something already out there that would save me the work.

So those are my two big questions for now. Input is welcome.

1 Comment | In: Uncategorized | | #

I’ve been working strictly with SSIS in SQL Server 2008 for a a little over month now. It’s an amazing tool.

One thing I encounter when generating horizontal tables from adata warehouse is the factthat I have to pivot tables. Last week, when trying to pivot data using the “Pivot Task” I got the error:

“Cannot convert the pivot key value_ to the data type of the pivot key column.” (The underscorereplacesthe name of the output column I had created. )

Thing is, I was trying to be fancy, In my OLE DB Source I had decoded mycolumn names from numbers to text.

This bit me in the end. I couldn’t figure out why I was getting an error. But here was the solution.

Pass the numbers in and name them under your PivotKeyValue.

Correct:

SELECT[1AnchorColumn], [3PivotValue], [2PivotKey]
FROM [AdventureWorksDW].[dbo].[Table]

output column 1 “Blankets” PivotKeyValue=125 Name=Blankets SourceColumn=LineageID from Input

output column2 “HomeOffice” PivotKeyValue=126 Name=HomeOffice SourceColumn=LineageID fromInput

output column3 “Kitchen” PivotKeyValue=300 Name=Kitchen SourceColumn=LineageID fromInput

Incorrect:


SELECT
[1AnchorColumn], [3PivotValue],[2PivotKey] =
CASE [2PivotKey]
WHEN 125THEN ‘Blankets’
WHEN 126 THEN ‘HomeOffice’
WHEN 300 THEN ‘Kitchen’
ELSE ‘Other’END
FROM [AdventureWorksDW].[dbo].[Table]

output column 1 “Blankets” PivotKeyValue=Blankets Name=Blankets SourceColumn=LineageID from Input

output column2 “HomeOffice” PivotKeyValue=HomeOffice Name=HomeOffice SourceColumn=LineageID fromInput

output column3 “Kitchen” PivotKeyValue=Kitchen Name=Kitchen SourceColumn=LineageID fromInput

The logic was that characters were coming from a field that was an integer. Thus the error:

“Cannot convert the pivot key value _ to the data type of the pivot key column”

Hope this helps you in your pivoting woes. I figured it myself! Brian Knight would be proud.

Toodles!

(I really will try to post more often!)

1 Comment | In: Uncategorized | | #

So far Ive found some serious lack of tools I need in SSIS. Don’t get me wrong, I LOVE SSIS. So we’ve had to either write in house custom components or purchase them from third parties.

The two big ones are SFTP Task and XML Destination.

Here is what I could find form Microsoft about the XML Destination:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=303815
I need to output some seriously complex XML… writing this component in house was the only option.

The other Component was a SFTP Task, now FTP is available, but I needed SFTP, my coworkers did a lot of research and came up with a nice plug-in from  cozyroc.com to handle this.

No Comments | In: Uncategorized | | #

I wanted to blog about whatever I had learned in the past week but really what came to mind was not answers, but more questions. Questions are good, they have a side effect of helping me learn. So here you go…

Migration:

What is the ideal way to push changes from your development server, to stage, and then production SQL servers with Microsoft products?

The way I am familiar with is using a third party tool, Apex Diff, to script data/schema differences add scripts to VSS, then having a custom script for any data cleanups… and push.

But Microsoft has built this all into Team system I think, I’ve never used it personally.  

Also, versioning… say I make changes to a table, “alter table add column blah blah”, what is the best way for VSS to pick up this change? I’ve always checked out the table creation script, manually added my alter script to the bottom with comments about the workorder where the new column was requested by the client, then checked in the script, hit ”f5″ to push just that portion to my database and called it a day.

But is there a way for VSS to automagically pick up these changes, and who made them… it would save me so much time to just hit “f5″ and be done.

The same goes for SSIS. How do I know what changes were made to SSIS packages before I got to them? I suppose I could compare the XML files… but I need more, I need to know why the change was made and who did it…

I guess what I’m saying is, if I only had Microsoft tools to work with, what are the best practices for pushing these changes to my databases.

All these questions are things I’ll most likely be researching and blogging about in the next few weeks.

This seems like a vital part of having a database, code change and migration process per Microsoft should be clear as a bell right?

3 Comments | In: Uncategorized | | #

Sometimes you need to check to see if a file exists before you put the path in your database. Per local rumor this is an undocumented sproc in SQL Server. Love it, use it. 1 means yeah 0 means no. Try it out!

xp_fileexist ‘C:\test.txt’  from SSMS.

No Comments | In: Uncategorized | | #

http://www.sqlsaturday.com/

I would LOVE to put together a SQL Server Users Group in Knoxville. Even more I would love to eventually put together a SQL Saturday. (link above) if your interested send me a message.

2 Comments | In: Uncategorized | | #

Database locking issues arose this morning for me. When two updates or selects happen to the same object and the same record, record/page locking occurs. In my defense I was running a script for someone else, also I didn’t realize that there were two recurring jobs that started moments into running my script. I had the DB in restricted user mode. I only knew of one job that was going to start this morning but I should have been done with my work way before it ran.

Here is a simple debug script you can run to see if you have locking on your database and what spids are causing it: 

SELECT status, SPID, CPU, Physical_IO, WaitTime, Blocked, HostName, Program_Name, nt_username,Login_Time, Last_Batch, Open_Tran, waittype, lastwaittype
FROM master..sysprocesses with (nolock)
where blocked >0 or open_tran > 0

Also there is the SQL Server 2000 sp_lock command MSDN article about it’s results is here.

After a phone call or two I managed to disable the jobs and kill off any spids that were keeping my script from finishing. I then reenabled/ran them safely when I was done. The angels sang, the publish was complete, and had passed testing.

Lesson learned? Be more attentive to what jobs are running on the server, ugh. *smacks forehead* Gah.

No Comments | In: Uncategorized | | #

It seems that the question of the hour is always what my opinion is on Normalization. Or maybe they are asking me if I’m normal… hmm, I think I’ll not go there.
What is normalization? Well, its the method in which you reduce redundancy in a database.

There are several industry standards on this, they have fancy schmancy names (not really). The standards I’m covering here were created by an Englishman named Edgar F. Codd.

His concepts go from the most basic form of Normalization 1st Normal Form(1NF), to the stricter form, 3rd normal form(3NF). (There are more however I’m not going to cover them here)

To get the gist of how this all works lets define our database:
Your database stores information on Mickey Mouse and his Balloon Race competition.
Mickey and his friends need three mouskatools to get Donald Duck through the race.
Our table displays which character used which tool.
Mickey and Donald used Stickers to fix the holes in the balloon.
Mickey used the baby elephant to weight down the balloon.
Pete used the rope swing to get unstuck from a tree.
Pluto tags along but doesn’t really do anything.

So you might be tempted to have a table that looks like this.

Lets talk about 1st Normal Form(1NF):
The rule of 1st normal form says that you shouldn’t have any data that repeats within one cell (above). Nor should you, in this scenario, define multiple columns that hold the same type of data(Tool1,Tool2). Basically we are removing redundancy from left to right (horozontally)

In order for us to follow this Form we need to break the above data up into multiple tables with primary keys.

Lets talk about 2nd Normal Form (2NF):
The rule of 2NF says that you must follow 1NF AND seperate out groups of data into their own tables.

3rd normal form (3NF):
The rule of 3NF says that you must follow 2NF, AND remove redundancy from top to bottom (vertically).

So there you go, a basic basic idea of 1-3 Normal Form.

1 Comment | In: Uncategorized | | #

I am in the market for a new laptop, one that will be my primary machine and can be left on 24/7 if need be.

I want to be able to connect it to my dual monitors, it will replace my desktop. My current laptop is a really nice little Sony VIAO and it’s less than 5 pounds, Ive had it for a few years and it’s been good to me. I can run Virtual Machine on it but over time it get’s extremely hot. 

I’ve been thinking about getting a Mac Book Pro with 4GB of RAM. With VMWare I can run windows. But I’m not sure how SQL Server performs. I wouldn’t be using it for a server, just as a development platform. I don’t think it supports dual monitors. I also need to be able to remote into work via Cisco VPN.

One thing I dont like about laptops is the preinstalled software that comes with the machine. I just want Windows please…

Advice is welcome.

7 Comments | In: Uncategorized | | #