I've finally gotten round to investigating Change Data Capture or CDC in SQL Server 2008. For ages it's seemed like this big unknown, but while studying for the SQL Server 2008 Developer MCITP certification I decided it would be a good time to dive in.
Surprisingly, Books Online (BOL), which I've always struggled using, has not only a good explanation of CDC but a great tutorial on setting it up. Plus the kind Microsoft people even take you through the implementation of an SSIS based incremental load. This was a great find, as one of the ways in which CDC really shines is when used as part of an ETL solution. There are other very useful applications, such as auditing data changes (not to be confused with SQL Server 2008's Audit feature), but I'll focus on the ETL application here.
For more in-depth information about what Change Data Capture is and how it works see the BOL entry 'Basics of Change Data Capture'.
What I'd like to do over the next few posts is share a few scripts I've come up with to streamline the implementation of CDC. While the basics are very easy to implement, it's the SSIS package(s) that could get rather tedious to build without some generic scripts.
I'm keeping this blog as a repository for SQL tips and tricks I have learned over my time working with SQL Server.
Monday, 22 October 2012
Monday, 30 July 2012
Orphaned users
I'm sure many people have experienced the need to "reconnect" orphaned users after restoring a database from a different server. Not a difficult remedy, just run exec sp_change_users_login 'auto_fix','user_name' and you're good. If you run sp_change_users_login 'Report' first you can find all orphaned users for the given database.
Today I bumped into a situation where I ran the SP and received the error "An Invalid parameter or option was specified for 'sys.sp_change_users_users_login'". I was perplexed as I've run the sp many times before with no problems. I was starting to tear my hair out having searched the web with no joy until, and in hindsight, this is silly, I looked to see if the login existed on the server. And guess what, it didn't.
So, as is often the case, the Microsoft error message is, if not wrong, then at least misleading! I would have expected to see an error like 'User doesn't exist' or similar. Alas, in future, when this error appears, check whether the login exists.
See my other post with a query to resolve any and all orphaned users on a database.
Today I bumped into a situation where I ran the SP and received the error "An Invalid parameter or option was specified for 'sys.sp_change_users_users_login'". I was perplexed as I've run the sp many times before with no problems. I was starting to tear my hair out having searched the web with no joy until, and in hindsight, this is silly, I looked to see if the login existed on the server. And guess what, it didn't.
So, as is often the case, the Microsoft error message is, if not wrong, then at least misleading! I would have expected to see an error like 'User doesn't exist' or similar. Alas, in future, when this error appears, check whether the login exists.
See my other post with a query to resolve any and all orphaned users on a database.
Wednesday, 21 December 2011
My Trip to Delhi for MS Certification Training
I was made redundant/laid off in November. So decided to make something of it rather than wallow in sorrow (as it's the second time in as many years that this has happened). I had found a training centre in India almost a year ago, Koenig Solutions, and was angling to get my employer to split the cost of sending me out for training. Obviously, that was not to be. So when I got the sad news I decided to book a course for myself.
I was a little worried about the standards even though I had read good things about the company. You never know whether testimonials are genuine or not. But the price was good, even with the flights, and they provided everything: accommodation; full board; etc.
Next thing I know I've paid the deposit on the SQL Server 2008 DBA MCITP course, gotten a visa to India and booked my flights! No turning back. In the end everything was pretty much as promised. And the proof is in the pudding: I passed my certification and am that much more prepared for the dreaded job hunt in the new year.
Below are a few (understatement) pictures from my trip.
I was a little worried about the standards even though I had read good things about the company. You never know whether testimonials are genuine or not. But the price was good, even with the flights, and they provided everything: accommodation; full board; etc.
Next thing I know I've paid the deposit on the SQL Server 2008 DBA MCITP course, gotten a visa to India and booked my flights! No turning back. In the end everything was pretty much as promised. And the proof is in the pudding: I passed my certification and am that much more prepared for the dreaded job hunt in the new year.
Below are a few (understatement) pictures from my trip.
Sunday, 11 December 2011
Logon Triggers
I just learned of a nice feature, Logon Triggers. A nice example of what they can do is limit a specific user to a certain number, 3 for example, concurrent logons.
A nice little example of how to do this is as follows:
Let's say we have a login name of login_test.
The interesting feature of this type of trigger is that the server authenticates the login but BEFORE the user is connected to the server or database the trigger is fired and the appropriate action is taken.
See the full explanation of logon triggers at http://msdn.microsoft.com/en-us/library/bb326598.aspx
There's also a good explanation of how these triggers relate to trace events and event notifications.
See my other post detailing a good use for such a trigger.
The interesting feature of this type of trigger is that the server authenticates the login but BEFORE the user is connected to the server or database the trigger is fired and the appropriate action is taken.
See the full explanation of logon triggers at http://msdn.microsoft.com/en-us/library/bb326598.aspx
There's also a good explanation of how these triggers relate to trace events and event notifications.
See my other post detailing a good use for such a trigger.
Friday, 23 September 2011
Drop Table Loop
I often, for various reasons, need to import data from external sources. Most of the time I import into tables on a staging database before further manipulating the data.
What I then tend to find after a few months is a very large and cluttered database. So below is a simple little query that creates a temp table of objects that then get dropped via a while loop. Nothing fancy, just a small time saver.
What I then tend to find after a few months is a very large and cluttered database. So below is a simple little query that creates a temp table of objects that then get dropped via a while loop. Nothing fancy, just a small time saver.
Tuesday, 8 March 2011
Quick Cheat to Shrink Database Log Files
This is a quick way to reduce the size of a database log file. As a caveat to beginners, this should not be used on mission critical db systems. It is intended as a quick and dirty fix for non-essential systems.
For those not familiar with the Attach and Detach commands:
--You need to make sure the database is not being used by anyone else/any other process. That is why there is a set single_user statement.
--If your database has multiple files/filegroups, you need only specify the primary file in the create statement (if you haven't made any file modifications after detaching).
-- You may need to enable xp_cmdshell therefore I've included the sp_configure statements.
For those not familiar with the Attach and Detach commands:
--You need to make sure the database is not being used by anyone else/any other process. That is why there is a set single_user statement.
--If your database has multiple files/filegroups, you need only specify the primary file in the create statement (if you haven't made any file modifications after detaching).
-- You may need to enable xp_cmdshell therefore I've included the sp_configure statements.
Friday, 20 August 2010
Comparing Rows in a Table
As all SQL professionals will know sqlservercentral.com is an amazing resource. In an entry titled Linking to the Previous Row the author, David McKinney, describes a simple way to compare rows within a table using the two functions introduced in SQL Server 2005: Common Table Expressions (CTE) and Rownumber().
To be honest, until I read this article, I didn't see much of a use for CTEs other than recursive queries (for more on that see: Recursive Queries in SQL Server 2005). But this example provides a tidy method for comparing, for instance, changes over time.
And if you don't already know, the Rownumber() function allows you to include, as the name suggests, a column of row numbers in your result set based on a sort order that you determine. In addition, the PARTITION BY allows you to group the results.
I will definitely be employing this
To be honest, until I read this article, I didn't see much of a use for CTEs other than recursive queries (for more on that see: Recursive Queries in SQL Server 2005). But this example provides a tidy method for comparing, for instance, changes over time.
And if you don't already know, the Rownumber() function allows you to include, as the name suggests, a column of row numbers in your result set based on a sort order that you determine. In addition, the PARTITION BY allows you to group the results.
I will definitely be employing this
Wednesday, 4 August 2010
Parse a Comma Separated List
Here is a simple user-defined function to parse a comma separated (or any other character separated) list and return a table of the values in the list. The input variable must be a text data type (varchar). It returns the results as varchar so depending on your needs you may need to convert to int or datetime, etc.
Tuesday, 13 July 2010
A Quick Cheat to Create a Comma Separated List
The select statement below is an easy way of creating a comma separated list within a larger select query without the need for a cursor, user defined function or even a paramenter. It exploits the 'for xml' statement. By not defining any xml elements the column you select gets concatenated into a single line.
In my scenario I have keywords that can be members of multiple adgroups. Since my revenue figures are aggregated by keyword and not broken down by adgroup I need to display all adgroups in the same row as the keyword or I will get duplicate revenue figures.
I owe a lot to the colleague who showed this to me. It's saved me tremendous amounts of time and trouble over the past few years. I hope you find this as helpful as I did.
In my scenario I have keywords that can be members of multiple adgroups. Since my revenue figures are aggregated by keyword and not broken down by adgroup I need to display all adgroups in the same row as the keyword or I will get duplicate revenue figures.
I owe a lot to the colleague who showed this to me. It's saved me tremendous amounts of time and trouble over the past few years. I hope you find this as helpful as I did.
Monday, 5 July 2010
Find SSRS Subscription Jobs
A helpful query found on Patrick LeBlanc's blog:
The result set returns a list of SQL Server Reporting Services Reports matched to the jobs used to run the report subscriptions. A very helpful query in that SSRS report manager names the regular subscription and data driven subscription jobs based on a uniqueidentifier. This makes it difficult to identify which jobs run which subscriptions when viewing jobs through SQL Server Management Studio.
The result set returns a list of SQL Server Reporting Services Reports matched to the jobs used to run the report subscriptions. A very helpful query in that SSRS report manager names the regular subscription and data driven subscription jobs based on a uniqueidentifier. This makes it difficult to identify which jobs run which subscriptions when viewing jobs through SQL Server Management Studio.
Subscribe to:
Posts (Atom)
