Simons SQL Blog

Don't forget if you are in the Cambridge area on Monday (6th July 2009) we are doing another usergroup meeting.

This one is going to be on SQL Server 2008 features.

We are holding this at the head quarters of Redgate software who are kindly sponsoring the event.

To register go to http://sqlserverfaq.com/events/183/SQL-Server-User-Group-Cambridge-SQL-Server-2008.aspx

I look forward to seeing you there.

Martin is continuing to post some great new posts on his blog. I thought the last one is a real nasty one.

http://sqlblogcasts.com/blogs/martinbell/archive/2009/06/29/After-disabling-tde-you-still-requires-certificates-to-restore-the-database.aspx

If you have enabled TDE on a database and encrypted some data then remove the TDE and backup, you may not be able to restore that backup if you don't have the certificate.

I am guessing that this is to do with the encryption when you turn TDE on being a background task, I expect the same is true in reverse, i.e. pages stay encrypted until a background process decrypts them.

I wonder if the database ever gets back to a totally unencrypted version

Many years ago I was working on a project that had some large batch processing in it. To do this processing we had some dedicated servers, one of which sat under the desk next to me.

Apart from the noise of the fan soudning like we had actually bought a jet fighter the most annoying thing about this processing was that it resulted caused the servers beep to continually sound. Something in the code caused it to fire.

Boy it was annoying.

We were never able to find out how to stop it, I think in the end we disconnected it. Well last week I found out and it was so annoyingly simple.

net stop beep

Yep thats it, thanks to the trainer that pointed this out last week. As you can imagine, having 12 training machines all beeping is not something you really want

To permanently stop it you have to disable the service using

sc config beep start= disabled

Whats really odd is that beep doesn't appear in the list of services

One feature introduced in SQL Server 2005 is reporting in management studio. You have a set of standard reports that allow you to see in a nice report format output from dmvs and the default system background trace. Whats neat is that you can write your own custom reports. For those that long after the taskpad view of a database from SQL 2000 Enterprise manager with the help from Jasper Smith I created this http://sqlblogcasts.com/blogs/simons/archive/2007/03/28/Update-to-the-taskpad-custom-report.aspx

The custom report feature is almost fantastic, there are 3 main features I think make them awkward to use.

1. You can select text from the report and copy it
2. You can't add additional parameters to allow additional filtering, i.e. filter audit changes for today.
3. You can't have hyperlinks.

The last one I raised a connect item for https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240407.

If we had this you could write reports that had links to KB articles, blog posts, or internal KB systems. Similar to what most monitoring systems provide.

If you think this is a good idea then please vote on the connect item. I've been asked by the tools team to see if it is something people want.

Some other connect items that you might be interested in supporting if you think they are worthwhile.

Use expressions for data sources - https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=238233

Be able to combine data from different data sources - https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240268

Be able to run reports against SQL 2000 database - https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

I incorrectly said in my last blog post http://sqlblogcasts.com/blogs/simons/archive/2009/06/23/Cambridge-SQL-Server-Usegroup---SQL-2008.aspx that the usergroup was on the 6th June. That would be going some, whilst SQL is good it doesn't yet do time travel.

The usergroup is on the 6th July.

I've just updated the agenda for the Cambridge usergroup meeting on the 6th July.[This is now the correct date SS 23/6/08]

If you plan to attend make sure you register http://sqlserverfaq.com/events/183/SQL-Server-User-Group-Cambridge-SQL-Server-2008.aspx  

We are holding the meeting at Redgates offices (http://www.red-gate.com/about/Map_colour.pdf which are very nice)

The topic for the evening is all things SQL Server 2008.

We will be trying live meeting again to view go to https://www.livemeeting.com/cc/usergroups/join?id=HWJRB6&role=attend&pw=R7R4N%217pq This will be open just before the meeting starts.

I look forward to seeing you there.

In the immortal words of Alan Rickman

"'you, redgate offices, 6:00 , you, 6:45.... bring a friend'

If you plan to attend make sure you register http://sqlserverfaq.com/events/183/SQL-Server-User-Group-Cambridge-SQL-Server-2008.aspx  

Simon

James Rowland-Jones asked me this evening how to export XML data from SSIS. I've done this before but always used the Script component and I wondered if there was another way.

I tried to think of a component that saves data to a file. Oddly there isn't a control flow task that does that. The nearest I came to was the "export column" transform, this is a hardly known transform. It was a very specific function that most people don't need. For each row of data going through it, it exports the contents of a column to a file with a filename specified in another column.

Thinking of this I generated an example query that returns a rowset with a filename column and our XML column (data).

select 'c:\test.xml' filename

    , (select name, (select name

                  from sys.columns c where c.object_id = o.object_id

                  for xml path('column'),type) columns

     from sys.objects o

     for xml path('object'), root('schema'), type) data

This can then be plugged into an OLEDB source, connected to an Export Column transform configure the data column, filename and whether to append or truncate and away you go. You can get the video here http://media.sqlknowhow.com/nuggets/Exporting%20XML%20data%20from%20SSIS.wmv  or watch it below.

If you came to the usergroup meeting and completed the survey today and said your postcode was WC1X 8TG can you please ocntact me so we can discuss what nugget/session you would be inetersted in doing, as you forgot to put your name on the survey.

You can use the contact line on the right hand side of this blog.

Cheers

Simon

The live meeting on performance tuning and profiler has started.

To attend go to https://www.livemeeting.com/cc/usergroups/join?id=JK8HND&role=attend&pw=Wbg%5CM4%3FfR

Hope to see you there

I had an email from one the people that attends the usergroup I help run in London. She was suprised that when she had a create table statement intellisense is able to understand that this table is being created and provide intellisense on that table. This is just like any other object in your script, i.e. if you declare a variable then intellisense can provide that variable in a list for you. I guess tables are more complex than variables, but if with other languages like C# the intellisense is able to parse objects methods and even comments to provide information about those objects and methods without even compiling your code.

For a demo of this feature have a look at this nugget http://media.sqlknowhow.com/nuggets/Intellisense%20and%20objects%20created%20in%20scripts.wmv