It's my second post, and it's not about SQL. If there's one thing I love more than SQL (excluding beer, chocolate, vacations etc), it's music. I'm a bit of a closet musician (hack?) and I'm really excited by a new application from Microsoft Research. It's called Songsmith and it's a fantastic tool for those who want to create songs.
Just sing a melody and Songsmith will create a chord progression that fits with your tune and then generate an arrangement. That's impressive. I remember a time when it was a hard enough to do a reasonable job of pitch-to-midi conversion. As far as I know it's almost unique in what it does.
You can see from the screenshot that it's easy to play with the song by adjusting the 'Happy' and 'Jazzy' sliders. If you want to be more involved you can go right ahead and tweak the chords yourself. There are also around 30 styles to choose from. This is gonna be fun.
I won't bore you with a review. Music is for playing and listening. You should be trying this app, not reading my blog.
Listen to me now, hear me later, it doesn't matter. Do yourself a favour and download the demo now!
Microsoft Research: http://research.microsoft.com/en-us/
Songsmith: http://research.microsoft.com/en-us/um/redmond/projects/songsmith/index.html
First post, so I thought we'd ease into things with something simple.
I recently happened across someone who had a problem with creating a report in SQL Server Reporting Services. The report utilised datasets from several data sources. So far no issue. However, tables that existed in different database instances that needed to be combined into a single dataset.
That is, each database has a table with a list of contacts to be retrieved as follows:
select FirstName,
LastName
from SalesDB.Customer.Contact
The original report looked a bit like this:
As you can probably see already, this isn't exactly useful. The report should show a single contact list. For the record, if you're not certain, SSRS can't do this for you. Each data source is treated as a distinct entity and you can't make a single set of data out of them. So, we need to turn to SQL Server to provide a singe datasource that contains the dataset.
Lucky enough we had an instance of SQL Server to play with and eventually use for production purposes. So, my first instinct was to create a linked server for each of the databases and then a view which combines them. Unfortunately, we ran into permissions issues and that's when I started to scratch my head. What next?
Well, there's an ugly-but-functional way of taking care of situations like this - OPENROWSET. The idea is to create a simple view to query each of the servers and combine the results. The beauty of this approach is that the permissions needed are only for creating a view.
OPENROWSET requires coding the credentials for the datasources inside the query. You'll recall that we were already connecting from SSRS, so we can just use the same information inside the view. In this case we were able to use a trusted connection. This is how the view looks:
create view GetTheData as
select a.*
from openrowset('SQLNCLI', 'Server=ServerA;
Trusted_Connection=yes;',
'select FirstName,
LastName
from SalesDB.Customer.Contact'
) as tabA
union all -- gives us the full datasets
select tabB.*
from openrowset('SQLNCLI',
'Server=ServerB;
Trusted_Connection=yes;',
'select FirstName,
LastName
from SalesDB.Customer.Contact '
) as tabB
union all
select tabC.*
select openrowset('SQLNCLI',
'Server=Server;
Trusted_Connection=yes;',
'select FirstName,
LastName
from SalesDB.Customer.Contact '
) as tabC
From here we can ditch the 3 datasources in SSRS, set up a single datasource and reference the view GetTheData. Note that this a very simple example, connecting via SQL Native Client OLE DB provider. OPENROWSET is capable of far more than connecting to SQL Server, it's great for many other OLE DB data sources.
Now, the important part for me here wasn't that I had anything technically difficult to do. My issue was that I was temporarily blind and automatically thought that linked servers would be the answer. When that didn't work I was stumped for a moment because I'd momentarily forgotten about OPENROWSET (!!) If you're like me, it's easy to have a favourite way of doing everything, to the detriment of keeping an open mind.
You can find more information on OPENROWSET in BOL: http://msdn.microsoft.com/en-us/library/ms190312.aspx
