Month: July 2009

SQLBits Goes West – Session Submissions

Just this Monday they have opened up the session submissions for the next installment of SQLBits. For those of you you don’t know, SQLBits is the largest SQL Server event that I know of in the UK for several year running now. I believe they are on number 5 with this one and it will be taking place Nov 19th – 21st. For those presenters that are interested in an excuse to take a trip to the UK, this is a great event. You can read more about it on Chris Webb’s blog post.. Chris Web’s Blog Now I just have to work on a way in order to spin the $1000.00 airfare and the hotel to my CEO as an investment and not a cost…Hmmmmm. Any ideas? Cheers, AJ Technorati Tags: sqlbits,sqlbits v,chris webb,events,microsoft,sql...

Read More

Using INFORMATION_SCHEMA to Get Column Information

A commonly asked question that I field is when working with CRM or another 3rd party database what is the most efficient way in order to find out column information on a specific table using only T-SQL. This practice is often important if you are writing your own stored procedures and functions to import data into the database. This is often done by writing a series of views that will be used from the import source that map the table structure that the data is going into. So you would naturally want to write some T-SQL that would just automatically create the INSERT statements from view source A into destination table B. *Disclaimer: Although you could do the same type of thing using SSIS it may not be an option in your environment or you may not consider that option as flexible. This is readily accomplished by using the INFORMATION_SCHEMA. In the example below I have a custom entity in my CRM instance called Employee. Now in CRM you always have a Base table and then an ExtensionBase table. The ExtensionBase table for an entity is normally the place where you are looking at because your looking for attributes. So the query below should return me decent information about the custom attributes that have been created for me in the table…   SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM...

Read More

SSIS: Reading and Writing to Variables in Script Task

A lot of people still consistently ask me about how to read and write to variables in the SSIS script task. In this post I will demonstrate for you the two ways in which you can go about this, one from native SSIS functionality and the other from code. In SQL Server 2008 both will generally work out equally as well. In previous versions, you may want to stick with the coding piece as sometimes the ‘automatic’ integration with the variable dispenser was a little hooky. So for our example, I am going to set up a simple SSIS project. First I create 2 variables: MyName & YourName. I have scoped these at the packages level as it has always been my instinct that unless there is a compelling reason to scope it otherwise.     Now that we have our variables set up, we are ready to begin making our project. Since this example is going to be pretty straightforward, we will just drop two script components onto the control flow design surface. The first will be the one using the native way of handling variables in the script component and the second will be using a little bit of custom code to do it programmatically. The setup is simply shown below.   Now opening up the script task you can click on the … button next to...

Read More

More SQL Injection Attacks

There is another wave of  SQL Injection attacks underway by an older botnet, Asprox. In the link below Trend Labs outlines the history of this piece of code http://blog.trendmicro.com/yamsia-yet-another-massive-sql-injection-attack/ Further, SSWUG.org is giving some tips on what to do in order to protect your pages against these types of attacks. Hopefully, your companies are doing their due diligence in order to protect their sites from these clever critters. Cheers, AJ Technorati Tags: botnet,Asprox,sql injection,hackers,bugs,sql server,injection...

Read More

SQL Server 2008 Maintenance Plan Errors When Changing Server Name

  I recently ran into a bug issue here recently when I had to update a server name on a database box running SQL Server 2008. I updated the server name and then set SQL Server to run under the new name as well and restarted the system. The strange thing was that I went to immediately run one of the maintenance plans to get backups of the database system….it failed. Hhhhmmmm,interesting. Why would it fail now? The error log said that the problem stemmed from the Local Server Connection being unrecognized. Normally, when you create a maintenance task within the instance there is a connection that is set up automatically set to the local instance like shown below. This is supposed to make it easier when setting up the jobs and moving it between servers. Since it is set to localhost then it wouldn’t have to be updated or anything. So why is this one different? After some thinking, I went and looked at the maintenance plan local server connection value. There is where I found the answer. The local server connection showed something similar to below. The localhost connection was actually a named connection. The thing is that if I create another job on the SQL Server 2008 instance it does not replace localhost with the server name. So this isn’t a bug with SQL Server 2008...

Read More