|
|
|
|
This is the blog of Jamie Thomson, a freelance SQL Server developer in London
-
SSIS Reporting Pack is a suite of reports that I maintain at http://ssisreportingpack.codeplex.com/ that provide visualisation over the SSIS Catalog in SQL Server 2012 and attempt to add value over the reports that ship in the box. Work on the reports has stalled (my last SSIS Reporting Pack blog post was on 4th September 2011) as I’ve had rather more important things going on my life of late however I have recently checked-in a fix that couldn’t really be delayed. I discovered a problem with the Execution report that was causing the report to effectively hang, it was caused by this bit of SQL hidden away in the report definition: [generated_executables] AS (
SELECT [new_executable].[execution_path],[new_executable].[parent_execution_path]
FROM (
SELECT [execution_path] = SUBSTRING([loop_iteration].[execution_path] ,1, [loop_iteration].length_exec_path - [loop_iteration].[char_index_close_square] + 1)
, [parent_execution_path] = SUBSTRING([loop_iteration].[execution_path] ,1, [loop_iteration].length_exec_path - [loop_iteration].[char_index_open_square])
FROM (
SELECT [execution_path]
, [char_index_open_square] = CHARINDEX('[',REVERSE([execution_path]),1)
, [char_index_close_square] = CHARINDEX(']',REVERSE([execution_path]),1)
, [length_exec_path] = LEN([execution_path])
FROM [exec_stats] es
WHERE execution_path LIKE '%\[%]%' ESCAPE '\'
)AS [loop_iteration]
) AS [new_executable]
GROUP BY [new_executable].[execution_path],[new_executable].[parent_execution_path]
)
It was there because SSIS does not currently treat a loop iteration as an executable yet I figured there was still value in being able to view it as such – this SQL essentially “invents” new executables for those loop iterations; its what enabled the following visualisation:
where each of the three iterations of a For Each Loop called “FEL Loop over top performing regions” appear in the report. Unfortunately, as I alluded, this could under certain circumstances (most likely when there were many loop iterations) cause the report to hang as it waited for the results to be constructed and returned.
The change that I have made eradicates this generation of “fake” executables and thus produces this visualisation instead:
Notice that the three “children” of the For Each Loop are no longer the three iterations but actually the task (“EPT Call Data Export Package”) contained within that For Each Loop. The problem here is of course that there is no longer a visual distinction between those three iterations; I have instead made the full execution path viewable via a tooltip:
If you preferred the “old” way of presenting this information and are happy to put up with the performance degradation then I have kept the old version of the report hanging around in the reporting pack as “execution loop with iterations”
however none of the other reports link to it so you will have to browse to it manually if you want to use it. Please let me know if you ARE using it – I would be very interested to hear about your experiences.
The last change to make you aware of in the execution report is that by default I no longer show OnPreValidate or OnPostValidate messages as I consider them to be superfluous and only serve to clutter up the results.
If you want to put them back, well, its open source so go right ahead!
The latest release of SSIS Reporting Pack that contains all of these changes is v0.4 and can be downloaded from http://ssisreportingpack.codeplex.com/releases/view/88178
Feedback on all of the above changes would be very much appreciated.
@Jamiet
|
-
This is a simple heads up for anyone doing SQL Server Integration Services (SSIS) development using SSIS 2012. Be aware that messages do not always appear in [catalog].[event_messages] in the order that they occur, observe… In the following query I am looking at a subset of messages in [catalog].[event_messages] and ordering them by [event_message_id]: SELECT [event_message_id],[event_name],[message_time],[message_source_name]
FROM [catalog].[event_messages] em
WHERE [event_message_id] BETWEEN 290972 AND 290982
ORDER BY [event_message_id] ASC
--ORDER BY [message_time] ASC
Take a look at the two rows that I have highlighted, note how the OnPostExecute event for “Utility GetTargetLoadDatesPerETLIfcName” appears after the OnPreExecute event for “FELC Loop over TargetLoadDates”, I happen to know that this is incorrect because “Utility GetTargetLoadDatesPerETLIfcName” is a package that gets executed by an Execute Package Task prior to the For Each Loop “FELC Loop over TargetLoadDates”:
If we order instead by [message_time] then we see something that makes more sense:
SELECT [event_message_id],[event_name],[message_time],[message_source_name]
FROM [catalog].[event_messages] em
WHERE [event_message_id] BETWEEN 290972 AND 290982
--ORDER BY [event_message_id] ASC
ORDER BY [message_time] ASC
We can see that the OnPostExecute for “Utility GetTargetLoadDatesPerETLIfcName” did indeed occur before the OnPreExecute event for “FELC Loop over TargetLoadDates”, they just did not get assigned an [event_message_id] in chronological order. We can speculate as to why that might be (I suspect the explanation is something to do with the two executables appearing in different packages) but the reason is not the important thing here, just be aware that you should be ordering by [message_time] rather than [event_message_id] if you want to get 100% accurate insights into your executions.
@Jamiet
|
-
I recently overheard a remark by Greg Low in which he said something akin to "the most interesting parts of a new SQL Server release are the myriad of small things that are in there that make a developer's life easier" (I'm paraphrasing because I can't remember the actual quote but it was something like that).
The new DATEFROMPARTS function is a classic example of that . It simply takes three integer parameters and builds a date out of them (if you have used DateSerial in Reporting Services then you'll understand).
Take the following code which generates the first and last day of some given years:
SELECT 2008 AS Yr INTO #Years UNION ALL SELECT 2009 UNION ALL SELECT 2010 UNION ALL SELECT 2011 UNION ALL SELECT 2012
SELECT [FirstDayOfYear] = CONVERT(DATE,CONVERT(CHAR(8),((y.[Yr] * 10000) + 101)))
, [LastDayOfYear] = CONVERT(DATE,CONVERT(CHAR(8),((y.[Yr] * 10000) + 1231)))
FROM #Years y
here are the results:
That code is pretty gnarly though with those CONVERTs in there and, worse, if the character string is constructed in a certain way then it could fail due to localisation, check this out:
SET LANGUAGE french;
SELECT dt,Month_Name=DATENAME(mm,dt)
FROM (
SELECT dt = CONVERT(DATETIME,CONVERT(CHAR(4),y.[Yr]) + N'-01-02')
FROM #Years y
)d;
SET LANGUAGE us_english;
SELECT dt,Month_Name=DATENAME(mm,dt)
FROM (
SELECT dt = CONVERT(DATETIME,CONVERT(CHAR(4),y.[Yr]) + N'-01-02')
FROM #Years y
)d;
Notice how the datetime has been converted differently based on the language setting. When French, the string "2012-01-02" gets interpreted as 1st February whereas when us_english the same string is interpreted as 2nd January.
Instead of all this CONVERTing nastiness we have DATEFROMPARTS:
SELECT [FirstDayOfYear] = DATEFROMPARTS(y.[Yr],1,1)
, [LasttDayOfYear] = DATEFROMPARTS(y.[Yr],12,31)
FROM #Years y
How much nicer is that? The bad news of course is that you have to upgrade to SQL Server 2012 or migrate to SQL Azure if you want to use it, as is the way of the world!
Don't forget that if you want to try this code out on SQL Azure right this second, for free, you can do so by connecting up to AdventureWorks On Azure. You don't even need to have SSMS handy - a browser that runs Silverlight will do just fine. Simply head to https://mhknbn2kdz.database.windows.net/ and use the following credentials:
- Database AdventureWorks2012
- User sqlfamily
- Password sqlf@m1ly
One caveat, SELECT INTO doesn't work on SQL Azure so you'll have to use this instead:
DECLARE @y TABLE (
[Yr] INT
);
INSERT @y([Yr])
SELECT 2008 AS Yr UNION ALL SELECT 2009 UNION ALL SELECT 2010 UNION ALL SELECT 2011 UNION ALL SELECT 2012;
SELECT [FirstDayOfYear] = DATEFROMPARTS(y.[Yr],1,1)
, [LastDayOfYear] = DATEFROMPARTS(y.[Yr],12,31)
FROM @y y;
SELECT [FirstDayOfYear] = CONVERT(DATE,CONVERT(CHAR(8),((y.[Yr] * 10000) + 101)))
, [LastDayOfYear] = CONVERT(DATE,CONVERT(CHAR(8),((y.[Yr] * 10000) + 1231)))
FROM @y y;
@Jamiet
|
-
SQL Relay (#sqlrelay) is fast approaching and I wanted to take this opportunity to tell you a little about it. SQL Relay is a 5-day tour around the UK that is taking in five Server Server user groups, each one comprising a full day of SQL Server related learnings. The dates and venues are: Click on the appropriate link to see the full agenda and to book your spot. SQL Relay features some of this country's most prominent SQL Server speakers including Chris Webb, Tony Rogerson, Andrew Fryer, Martin Bell, Allan Mitchell, Steve Shaw, Gordon Meyer, Satya Jayanty, Chris Testa O'Neill, Duncan Sutcliffe, Rob Carrol, me and SQL Server UK Product Manager Morris Novello so I really encourage you to go - you have my word it'll be an informative and, more importantly, enjoyable day out from your regular 9-to-5. I am presenting my session "A Lap Around the SSIS Catalog" at Edinburgh and Manchester so if you're going, I hope to see you there. @Jamiet
|
-
I have been using SQL Server Data Tools (SSDT) both at work and on some hobby projects for quite a few weeks now and of all the new features I have to say the one that I am appreciating the most is Publish Profile files. I have been searching around on MSDN for an article that explains Publish Profile files but it seems no such article exists so I’ll attempt to surmise here. Publish Profile files are, essentially, a collection of all the property key-value pairs that are needed to deploy a database model (i.e. a .dacpac) to some target database. Those properties include (but are not limited to): - database name
- connection string
- whether or not to publish SSDT projects on which the current project has a dependency
- Recreate the database from scratch (or not)
- Backup before deploy
- Drop unknown objects in target
- SQLCMD Variables
Those that have used the predecessor to SSDT, Visual Studio Database Projects, will be familiar with a lot of those properties however in that case the properties had to be specified on a case-by-case basis; typically in an msbuild script or similar. Often those scripts are not maintained by a developer – rather they are maintained by a DevOps team that are not familiar with the code being deployed and as a developer myself that’s not a situation that I’m at all comfortable with. On my most recent project where Visual Studio Database Projects were being used I faced the maddening situation where every new SQLCMD variable added to a project required an email to be sent to the DevOps team to ask them to update their scripts accordingly. As you can imagine human errors crept in (on our side more than the DevOps side) and we ended up deploying projects with the wrong SQLCMD values. Moreover, we had to deal with different DevOps folks and often they would store the values in different places; totally infuriating, believe me. Publish Profile files make this process much easier because we can define all those properties on a per-environment basis and keep them in a dedicated Publish Profile file. The obvious benefit then is that a Publish Profile file abstracts all of the environment-specific information into a single file so deploying an SSDT project now requires only two things; the build output (i.e. a .dacpac file) and a Publish Profile file: >sqlpackage.exe /sf:MyDB.dacpac /pr:DEV.publish.xml The implied benefit (and this is what I really like about them) is that Publish Profile files are a source code artefact that a developer maintains the same as they would any other source code artefact, all that the DevOps people need are the build output (i.e. a .dacpac file) and an appropriately named Publish Profile file. Developers are now wholly in charge of how their code gets deployed which keeps them happy and the DevOps people have less work to do – so they’re a happy bunch too! I’m not saying that the wrong values won’t ever be supplied but at least now we know exactly where to go to fix those errors. SSDT also provides a friendly UI for maintaining these Publish Profile files. Double-click on one and this UI appears: 
Its fairly self-explanatory to fill these things out. A connection string, a database name and values for each SQLCMD variable defined within the project and you’re pretty much there. (I have written previously about one other benefit of Publish Profile files - that they can be used to make SQLCMD variables mandatory. Read more at SQL Server Data Tools does support required variables.) One minor downside of Publish Profile files is that they get created in the root of your SSDT project (I have griped about this previously at Folders in SQL Server Data Tools) so the convention that I have been using is to create a folder called Publish in each SSDT project and move all Publish Profile files into there. 
Hopefully this has given you a small taster of what Publish Profile files are all about. I’ll probably share some msbuild scripts that we’re using to deploy these things in a later blog post. @Jamiet P.S. As an aside, I have requested that the SSIS/SSAS/SSRS teams adopt the Publish Profile file approach in future iterations of their products. If you think that that is something you would like to see happen then click through, vote and leave a comment.
|
-
A little over a month ago the first version of the SSDT Power Tools were made available bringing with them some much needed functionality – a replacement for Schema View called SQL Server Object Explorer. 
Along with the first release of the power tools came a promise that they would be updated on a regular cadence and, true to their word, an updated version (v1.1) is already available. 
If you have got v1.0 installed then you should automatically be notified about the update. However, an attempt to apply the update results in this little nasty: 
Here’s the full text of that error in case anyone happens to Bingle* for it: Digital signature mismatch The signature on the update version of 'SSDT Power Tools' does not match the signature on the installed version. Therefore, extension manager cannot install the update.
If you trust the update, try the installation again after you install the installed version. Now, according to this KB article this isn’t an issue with the extension, it is actually an issue with the Extension Manager in Visual Studio 2010 Service Pack 1 hence the recommended course of action is to uninstall and then reinstall from the gallery 
I did this and it worked just fine. Of course, the only missing information is what is new in this update and a cursory search hasn’t uncovered anything. I suggest keeping an eye on the SSDT blog to see if any such news transpires. @Jamiet *Bingle – to search using Google or Bing. I first saw “Bingle” in an email a few weeks ago from Glenn Berry (blog |twitter) and it rather amused me so I’m adopting it for the foreseeable future 
|
-
Ever heard of SQLPeople.net? It is a website set up by Andy Leonard to showcase members of the SQL Server community by asking them the following questions:
- Everyone’s an accidental DBA (or database professional) – what’s your story? How’d you become a SQLPerson?
- What’s your favorite part of your current gig?
- Complete this sentence: “If I could do anything else, I would…”
- Complete this sentence: “When I’m not working I enjoy…”
- Complete this sentence: “I think the coolest thing in technology today is…”
- Complete this sentence: “I look forward to the day when I can use technology to…”
- Share something different about yourself. (Remember, it’s a family blog!)
Prior to the recent SQLBits X conference I asked Andy if I could extend the idea by posing the same questions to some of the conference attendees under the auspices of SQLPeople and film their responses, he graciously agreed. I managed to capture thirteen such videos and you can view them all online right now at http://vimeo.com/user3251268/videos/search:sqlpeople/.
You can find all of these people online at @troublewithdata @mattmasson @john_welch @ctesta_oneill @workerthread @al_eardley @Varigence @davebally @gavinpayneuk @JimmyBoo @sqlsophist @Technitrain
|
-
Recently I have begun a new project in which I am using SQL Server Data Tools (SSDT) and SQL Server Integration Services (SSIS) 2012. Although I have been using SSDT & SSIS fairly extensively while SQL Server 2012 was in the beta phase I usually find that you don’t learn about the capabilities and quirks of new products until you use them on a real project, hence I am hoping I’m going to have a lot of experiences to share on my blog over the coming few weeks. In this first such blog post I want to talk about file and folder organisation in SSDT. The predecessor to SSDT is Visual Studio Database Projects. When one created a new Visual Studio Database Project a folder structure was provided with “Schema Objects” and “Scripts” in the root and a series of subfolders for each schema: 
Apparently a few customers were not too happy with the tool arbitrarily creating lots of folders in Solution Explorer and hence SSDT has gone in completely the opposite direction; now no folders are created and new objects will get created in the root – it is at your discretion where they get moved to: 
After using SSDT for a few weeks I can safely say that I preferred the older way because I never used Solution Explorer to navigate my schema objects anyway so it didn’t bother me how many folders it created. Having said that the thought of a single long list of files in Solution Explorer without any folders makes me shudder so on this project I have been manually creating folders in which to organise files and I have tried to mimic the old way as much as possible by creating two folders in the root, one for all schema objects and another for Pre/Post deployment scripts: 
This works fine until different developers start to build their own different subfolder structures; if you are OCD-inclined like me this is going to grate on you eventually and hence you are going to want to move stuff around so that you have consistent folder structures for each schema and (if you have multiple databases) each project. Moreover new files get created with a filename of the object name + “.sql” and often people like to have an extra identifier in the filename to indicate the object type: 
The overall point is this – files and folders in your solution are going to change. Some version control systems (VCSs) don’t take kindly to files being moved around or renamed because they recognise the renamed/moved file simply as a new file and when they do that you lose the revision history which, to my mind, is one of the key benefits of using a VCS in the first place. On this project we have been using Team Foundation Server (TFS) and while it pains me to say it (as I am no great fan of TFS’s version control system) it has proved invaluable when dealing with the SSDT problems that I outlined above because it is integrated right into the Visual Studio IDE. Thus the advice from this blog post is: If you are using SSDT consider using an Visual-Studio-integrated VCS that can easily handle file renames and file moves I suspect that fans of other VCSs will counter by saying that their VCS weapon of choice can handle renames/file moves quite satisfactorily and if that’s the case…great…let me know about them in the comments. This blog post is not an attempt to make people use one particular VCS, only to make people aware of this issue that might rise when using SSDT. More to come in the coming few weeks! @jamiet
|
-
Two weeks ago I made available a copy of [AdventureWorks2012] on SQL Azure and published credentials so that anyone from the SQL community could connect up and experience SQL Azure, probably for the first time. One of the (somewhat) popular requests thereafter was to make the venerable Northwind database available too so I am pleased to say that as of right now, Northwind is up there too. 
You will notice immediately that all of the Northwind tables (and the stored procedures and views too) have been moved into a schema called [Northwind] – this was so that they could be easily differentiated from the existing [AdventureWorks2012] objects. I used an SQL Server Data Tools (SSDT) project to publish the schema and data up to this SQL Azure database; if you are at all interested in poking around that SSDT project then I have made it available on Codeplex for your convenience under the MS-PL license – go and get it from https://northwindssdt.codeplex.com/. Using SSDT proved particularly useful as it alerted me to some aspects of Northwind that were not compatible with SQL Azure, namely that five of the tables did not have clustered indexes: 
The beauty of using SSDT is that I am alerted to these issues before I even attempt a connection to SQL Azure. Pretty cool, no? Fixing this situation was of course very easy, I simply changed the following primary keys from being nonclustered to clustered: - [PK_Region]
- [PK_CustomerDemographics]
- [PK_EmployeeTerritories]
- [PK_Territories]
- [PK_CustomerCustomerDemo]
If you want to connect up then here are the credentials that you will need: - Server mhknbn2kdz.database.windows.net
- Database AdventureWorks2012
- User sqlfamily
- Password sqlf@m1ly
You will need SQL Server Management Studio (SSMS) 2008R2 installed in order to connect or alternatively simply use this handy website: https://mhknbn2kdz.database.windows.net which provides a web interface to a SQL Azure server. Do remember that hosting this database is not free so if you find that you are making use of it please help to keep it available by visiting Paypal and donating any amount at all to adventureworksazure@hotmail.co.uk. To make this easy you can simply hit this link and the details will be completed for you – all you have to do is login and hit the “Send” button. If you are already a PayPal member then it should take you all of about 20 seconds! I hope this is useful to some of you folks out there. Don’t forget that we also have more data up there than in the conventional [AdventureWorks2012], read more at Big AdventureWorks2012. @Jamiet AdventureWorks on Azure - Provided by the SQL Server community, for the SQL Server community!
|
-
SQL Server Data Tools (SSDT) recently got released as part of SQL Server 2012 and depending on who you believe it can be described as either:
- a suite of tools for building SQL Server database solutions
or
- a suite of tools for building SQL Server database, Integration Services, Analysis Services & Reporting Services solutions
Certainly the SQL Server 2012 installer seems to think it is the latter because it describes SQL Server Data Tools as "the SQL server development environment, including the tool formerly named Business Intelligence Development Studio. Also installs the business intelligence tools and references to the web installers for database development tools" as you can see here:
Strange then that, seemingly, there is no consensus within Microsoft about what SSDT actually is. On yesterday's blog post First Release of SSDT Power Tools reader Simon Lampen asked the quite legitimate question:
I understand (rightly or wrongly) that SSDT is the replacement for BIDS for SQL 2012 and have just installed this. If this is the case can you please point me to how I can edit rdl and rdlc files from within Visual Studio 2010 and import MS Access reports.
To which came the following reply: SSDT doesn't include any BIDs (sic) components. Following up with the appropriate team (Analysis Services, Reporting Services, Integration Services) via their forum or msdn page would be the best way to answer you questions about these kinds of services.
That's from a Microsoft employee by the way. Simon is even more confused by this and replies with: I have done some more digging and am more confused than ever. This documentation (and many others) : msdn.microsoft.com/.../ms156280.aspx expressly states that SSDT is where report editing tools are to be found
And on it goes.... You can see where Simon's confusion stems from. He has official documentation stating that SSDT includes all the stuff for building SSIS/SSAS/SSRS solutions (this is confirmed in the installer, remember) yet someone from Microsoft tells him "SSDT doesn't include any BIDs components". I have been close to this for a long time (all the way through the CTPs) so I can kind of understand where the confusion stems from. To my understanding SSDT was originally the name of the database dev stuff but eventually that got expanded to include all of the dev tools - I guess not everyone in Microsoft got the memo. Does this sound familiar? Have we not been down this road before? The database dev tools have had upteen names over the years (do any of datadude, TSData, VSTS for DB Pros, DBPro, VS2010 Database Projects sound familiar) and I was hoping that the SSDT moniker would put all confusion to bed - evidently its as complicated now as it has ever been. Forgive me for whinging but putting meaningful, descriptive, accurate, well-defined and easily-communicated names onto a product doesn't seem like a difficult thing to do. I guess I'm mistaken! Onwards and upwards... @Jamiet Update: There is evidence that they (Microsoft) are seeking to rectify this situation. In a blog comment Janet Yeilding whom, I assume, is on the SSDT team says: We recognize that our messaging on what SSDT is has been confusing and
even contradictory and we're working on getting a cleaner story in the
future.
|
-
Last week I launched AdventureWorks on Azure, an initiative to make SQL Azure accessible to anyone, in my blog post AdventureWorks2012 now available for all on SQL Azure. Since then I think its fair to say that the reaction has been lukewarm with 31 insertions into the [dbo].[SqlFamily] table and only 8 donations via PayPal to support it; on the other hand those 8 donators have been incredibly generous and we nearly have enough in the bank to cover a full year’s worth of availability. It was always my intention to try and make this offering more appealing and to that end I have used an adapted version of Adam Machanic’s make_big_adventure.sql script to massively increase the amount of data in the database and give the community more scope to really push SQL Azure and see what it is capable of. There are now two new tables in the database: - [dbo].[bigProduct] with 25200 rows
- [dbo].[bigTransactionHistory] with 7827579 rows
The credentials to login and use AdventureWorks on Azure are as they were before: - Server mhknbn2kdz.database.windows.net
- Database AdventureWorks2012
- User sqlfamily
- Password sqlf@m1ly
Remember, if you want to support AdventureWorks on Azure simply click here to launch a pre-populated PayPal Send Money form - all you have to do is login, fill in an amount, and click Send. We need more donations to keep this up and running so if you think this is useful and worth supporting, please please donate. I mentioned that I had to adapt Adam’s script, the main reasons being: - Cross-database queries are not yet supported in SQL Azure so I had to create a local copy of [dbo].[spt_values] rather than reference that in [master]
- SELECT…INTO is not supported in SQL Azure
- The 1GB limit of SQLAzure web edition meant that there would not be enough space to store all the data generated by Adam’s script so I had to decrease the total number of rows.
The amended script is available on my SkyDrive at https://skydrive.live.com/redir.aspx?cid=550f681dad532637&resid=550F681DAD532637!16756&parid=550F681DAD532637!16755 @Jamiet AdventureWorks on Azure - Provided by the SQL Server community,
for the SQL Server community!
|
-
Three days ago I tweeted this:
Evidently I wasn't the only one that thought this was a good idea because as you can see from the screenshot that tweet has, so far, been retweeted more than fifty times. Clearly there is a desire to see the AdventureWorks databases made available for the community to noodle around on so I am pleased to announce that as of today you can do just that - [AdventureWorks2012] now resides on SQL Azure and is available for anyone, absolutely anyone, to connect to and use* for their own means.
*By use I mean "issue some SELECT statements". You don't have permission to issue INSERTs, UPDATEs, DELETEs or EXECUTEs I'm afraid - if you want to do that then you can get the bits and host it yourself.
This database is free for you to use but SQL Azure is of course not free so before I give you the credentials please lend me your ears eyes for a short while longer. AdventureWorks on Azure is being provided for the SQL Server community to use and so I am hoping that that same community will rally around to support this effort by making a voluntary donation to support the upkeep which, going on current pricing, is going to be $119.88 per year. If you would like to contribute to keep AdventureWorks on Azure up and running for that full year please donate via PayPal to adventureworksazure@hotmail.co.uk:
Any amount, no matter how small, will help. If those 50+ people that retweeted me beforehand all contributed $2 then that would just about be enough to keep this up for a year. If the community contributes more than we need then there are a number of additional things that could be done:
- Host additional databases (Northwind anyone??)
- Host in more datacentres (this first one is in Western Europe)
- Make a charitable donation
That last one, a charitable donation, is something I would really like to do. The SQL Community have proved before that they can make a significant contribution to charitable orgnisations through purchasing the SQL Server MVP Deep Dives book and I harbour hopes that AdventureWorks on Azure can continue in that vein. So please, if you think AdventureWorks on Azure is something that is worth supporting please make a contribution.
OK, with the prickly subject of begging for cash out of the way let me share the details that you need to connect to [AdventureWorks2012] on SQL Azure:
- Server mhknbn2kdz.database.windows.net
- Database AdventureWorks2012
- User sqlfamily
- Password sqlf@m1ly
That user sqlfamily has all the permissions required to enable you to query away to your heart's content. Here is the code that I used to set it up:
CREATE USER sqlfamily FOR LOGIN sqlfamily;
CREATE ROLE sqlfamilyrole;
EXEC sp_addrolemember 'sqlfamilyrole','sqlfamily';
GRANT VIEW DEFINITION ON Database::AdventureWorks2012 TO sqlfamilyrole;
GRANT VIEW DATABASE STATE ON Database::AdventureWorks2012 TO sqlfamilyrole;
GRANT SHOWPLAN TO sqlfamilyrole;
EXEC sp_addrolemember 'db_datareader','sqlfamilyrole';
You can connect to the database using SQL Server Management Studio (instructions to do that are provided at Walkthrough: Connecting to SQL Azure via the SSMS) or you can use the web interface at https://mhknbn2kdz.database.windows.net:
Lastly, just for a bit of fun I created a table up there called [dbo].[SqlFamily] into which you can leave a small calling card. Simply execute the following SQL statement (changing the values of course):
INSERT [dbo].[SqlFamily]([Name],[Message],[TwitterHandle],[BlogURI])
VALUES ('Your name here','Some Message','your twitter handle (optional)','Blog URI (optional)');
[Id] is an IDENTITY field and there is a default constraint on [DT] hence there is no need to supply a value for those.
Note that you only have INSERT permissions, not UPDATE or DELETE so make sure you get it right first time! Any offensive or distasteful remarks will of course be deleted :)
Thank you for reading this far and have fun using AdventureWorks on Azure. I hope it proves to be useful for some of you.
@jamiet
AdventureWorks on Azure - Provided by the SQL Server community, for the SQL Server community!
|
-
In September 2011 I published a blog post SSIS Reporting Pack v0.2 now available in which I made available the credentials of a sample database that one could use to test SSIS Reporting Pack. That database was sitting on a paid-for Azure subscription and hence was costing me about £5 a month - not a huge amount but when I later got a free Azure subscription through my MSDN Subscription in January 2012 it made sense to migrate the database onto that subscription. Since then I have been endeavouring to make that move but a few failed attempts combined with lack of time meant that I had not yet gotten round to it. That is until this morning when I heard about a new feature available in the Azure Management Portal that enables one to move a SQL Azure server from one subscription to another. Up to now I had been attempting to use a combination of SSIS packages and/or scripts to move the data but, as I alluded, I ran into a few roadblocks hence the ability to move a SQL Azure server was a godsend to me. I fired up the Azure Management Portal and a few clicks later my server had been successfully migrated, moreover the name of the server doesn't change and neither do any credentials so I have no need to go and update my original blog post either. Its easy to be cynical about SQL Azure (and I maintain a healthy scepticism myself) but that, my friends, is cool! You can read more about the ability to move SQL Azure servers between subscriptions from the official blog post Moving SQL Azure Servers Between Subscriptions. @Jamiet
|
-
This evening I have been attempting to migrate an existing on-premise database to SQL Azure using the wizard that is built-in to SQL Server Management Studio (SSMS). When I did so I received the following error: The following objects are not supported =
[MS_Description] = Extended Property Evidently databases containing extended properties can not be migrated using this particular wizard so I set about removing all of the extended properties – unfortunately there were over a thousand of them so I needed a better way than simply deleting each and every one of them manually. I found a couple of resources online that went some way toward this: Unfortunately neither provided a script that exactly suited my needs. Angelo’s covered extended properties on tables and columns however I had other objects that had extended properties on them. Adam’s looked more complete but when I ran it I got an error: Msg 468, Level 16, State 9, Line 78
Cannot resolve the collation conflict between "Latin1_General_100_CS_AS" and "Latin1_General_CI_AS" in the equal to operation. So, both great resources but I wasn’t able to use either on their own to get rid of all of my extended properties. Hence, I combined the excellent work that Angelo and Adam had provided in order to manufacture my own script which did successfully manage to generate calls to sp_dropextendedproperty for all of my extended properties. If you think you might be able to make use of such a script then feel free to download it from https://skydrive.live.com/redir.aspx?cid=550f681dad532637&resid=550F681DAD532637!16707&parid=550F681DAD532637!16706&authkey=!APxPIQCatzC7BQ8. This script will remove extended properties on tables, columns, check constraints, default constraints, views, sprocs, foreign keys, primary keys, table triggers, UDF parameters, sproc parameters, databases, schemas, database files and filegroups. If you have any object types with extended properties on them that are not in that list then consult Adam’s aforementioned article – it should prove very useful. I repeat here the message that I have placed at the top of the script: /*
This script will generate calls to sp_dropextendedproperty for every extended property that exists in your database.
Actually, a caveat: I don't promise that it will catch each and every extended property that exists, but I'm confident it will catch most of them! It is based on this:
http://blog.hongens.nl/2010/02/25/drop-all-extended-properties-in-a-mssql-database/
by Angelo Hongens. Also had lots of help from this:
http://www.sqlservercentral.com/articles/Metadata/72609/
by Adam Aspin Adam actually provides a script at that link to do something very similar but when I ran it I got an error:
Msg 468, Level 16, State 9, Line 78
Cannot resolve the collation conflict between "Latin1_General_100_CS_AS" and "Latin1_General_CI_AS" in the equal to operation. So I put together this version instead. Use at your own risk. Jamie Thomson
2012-03-25
*/
Hope this is useful to someone! @Jamiet
|
-
Over the last few years Analysis Services guru Chris Webb and I have been on something of a crusade to enable better access to data that is locked up in countless Excel workbooks that litter the hard drives of enterprise PCs. The most prominent manifestation of that crusade up to now has been a forum thread that Chris began on Microsoft Answers entitled Excel Web App API? Chris began that thread with:
I was wondering whether there was an API for the Excel Web App? Specifically, I was wondering if it was possible (or if it will be possible in the future) to expose data in a spreadsheet in the Excel Web App as an OData feed, in the way that it is possible with Excel Services?
Up to recently the last 10 words of that paragraph "in the way that it is possible with Excel Services" had completely washed over me however a comment on my recent blog post Thoughts on ExcelMashup.com (and a rant) by Josh Booker in which Josh said:
Excel Services is a service application built for sharepoint 2010 which exposes a REST API for excel documents.
We're looking forward to pros like you giving it a try now that Office365 makes sharepoint more easily accessible. Can't wait for your future blog about using REST API to load data from Excel on Offce 365 in SSIS.
made me think that perhaps the Excel Services REST API is something I should be looking into and indeed that is what I have been doing over the past few days. And you know what? I'm rather impressed with some of what Excel Services' REST API has to offer.
Unfortunately Excel Services' REST API also has one debilitating aspect that renders this blog post much less useful than it otherwise would be; namely that it is not publicly available from the Excel Web App on SkyDrive. Therefore all I can do in this blog post is show you screenshots of what the REST API provides in Sharepoint rather than linking you directly to those REST resources; that's a great shame because one of the benefits of a REST API is that it is easily and ubiquitously demonstrable from a web browser. Instead I am hosting a workbook on Sharepoint in Office 365 because that does include Excel Services' REST API but, again, all I can do is show you screenshots.
N.B. If anyone out there knows how to make Office-365-hosted spreadsheets publicly-accessible (i.e. without requiring a username/password) please do let me know (because knowing which forum on which to ask the question is an exercise in futility).
In order to demonstrate Excel Services' REST API I needed some decent data and for that I used the World Tourism Organization Statistics Database and Yearbook - United Nations World Tourism Organization dataset hosted on Azure Datamarket (its free, by the way); this dataset "provides comprehensive information on international tourism worldwide and offers a selection of the latest available statistics on international tourist arrivals, tourism receipts and expenditure" and you can explore the data for yourself here. If you want to play along at home by viewing the data as it exists in Excel then it can be viewed here. Let's dive in.
The root of Excel Services' REST API is the model resource which resides at:
- http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model
Note that this is true for every workbook hosted in a Sharepoint document library - each Excel workbook is a RESTful resource. (Update: Mark Stacey on Twitter tells me that "It's turned off by default in onpremise Sharepoint (1 tickbox to turn on though)". Thanks Mark!)
The data is provided as an ATOM feed but I have Firefox's feed reading ability turned on so you don't see the underlying XML goo. As you can see there are four top level resources, Ranges, Charts, Tables and PivotTables; exploring one of those resources is where things get interesting. Let's take a look at the Tables Resource:
- http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Tables
Our workbook contains only one table, called ‘Table1’ (to reiterate, you can explore this table yourself here). Viewing that table via the REST API is pretty easy, we simply append the name of the table onto our previous URI:
- http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Tables('Table1')
As you can see, that quite simply gives us a representation of the data in that table. What you cannot see from this screenshot is that this is pure HTML that is being served up; that is all well and good but actually we can do more interesting things. If we specify that the data should be returned not as HTML but as:
- http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Tables('Table1')?$format=image
then that data comes back as a pure image and can be used in any web page where you would ordinarily use images. This is the thing that I really like about Excel Services’ REST API – we can embed an image in any web page but instead of being a copy of the data, that image is actually live – if the underlying data in the workbook were to change then hitting refresh will show a new image. Pretty cool, no? The same is true of any Charts or Pivot Tables in your workbook - those can be embedded as images too and if the underlying data changes, boom, the image in your web page changes too.
There is a lot of data in the workbook so the image returned by that previous URI is too large to show here so instead let’s take a look at a different resource, this time a range:
- http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Ranges('Data!A1|C15')
That URI returns cells A1 to C15 from a worksheet called “Data”:
And if we ask for that as an image again:
- http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Ranges('Data!A1|C15')?$format=image
Were this image resource not behind a username/password then this would be a live image of the data in the workbook as opposed to one that I had to copy and upload elsewhere. Nonetheless I hope this little wrinkle doesn't detract from the inate value of what I am trying to articulate here; that an existing image in a web page can be changed on-the-fly simply by inserting some data into an Excel workbook. I for one think that that is very cool indeed!
I think that's enough in the way of demo for now as this shows what is possible using Excel Services' REST API. Of course, not all features work quite how I would like and here is a bulleted list of some of my more negative feedback:
- The URIs are pig-ugly. Are "_vti_bin" & "ExcelRest.aspx" really necessary as part of the URI? Would this not be better:
http://server/Documents/TourismExpenditureInMillionsOfUSD.xlsx/Model/Tables(‘Table1’)
That URI provides
the necessary addressability and is a lot easier to remember.
- Discoverability of these resources is not easy, we essentially have to
handcrank a URI ourselves. Take the example of embedding a chart
into a blog post - would it not be better if I could browse first
through the document library to an Excel workbook and THEN through the
workbook to the chart/range/table that I am interested in? Call it a
wizard if you like. That would be really cool and would, I am sure,
promote this feature and cut down on the copy-and-paste disease that the
REST API is meant to alleviate.
- The resources that I demonstrated can be returned as feeds as well as images or HTML simply by changing the format parameter to ?$format=atom however for some inexplicable reason they don't return OData and no-one on the Excel Services team can tell me why (believe me, I have asked).
- $format is an OData parameter however other useful parameters such as $top and $filter are not supported. It would be nice if they were.
- Although I haven't demonstrated it here Excel Services' REST API does provide a makeshift way of altering the data by changing the value of specific cells however what it does not allow you to do is add new data into the workbook. Google Docs allows this and was one of the motivating factors for Chris Webb's forum post that I linked to above.
- None of this works for Excel workbooks hosted on SkyDrive
This blog post is as long as it needs to be for a short introduction so I'll stop now. If you want to know more than I recommend checking out a few links:
Any thoughts? Let's hear them in the comments section below!
@Jamiet P.S. If you agree with me that having the same functionality in SQL Server Reporting Services (i.e. rendering a report as an image) would be useful then please vote for it here.
|
|
|
|
|
|