Tonight I'm presenting my Database Projects session at the Queensland SQL Server User Group.
It will cover pretty much everything the last session did but this time I'm adding some bits on Post-Deployment scripts and how to use them to deploy data and database diagrams.
I'll post a full blog on how database diagram deployment works in a few days.
Just presented the same session I presented at my user group last night to some of the people I work with.
4 times as many as I had for my group, and I didn't have to supply pizza.
I think I made some converts to the DB project cause.
This month I presented a session called “A Database Project! What’s that?”
It was a quick tour of what a database project was, and how to use it. It focused on the version that comes with Visual Studio 2010, but points out the differences to VS 2008 where necessary.
It ended up being a very cosy session as just 3 group members showed up, although it was very timely for one member as his work place is just now introducing the concept of Database Projects.
The consensus was that DB Projects rock, but I already knew that :)
Just a quick blog to let folks know that Microsoft have recently released 64 bit MS Office drivers at http://www.microsoft.com/downloads/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en
These are the Microsoft Office 2010 drivers and can be used for accessing Excel and Access files from within your .Net or SSIS script tasks when they are running in 64 bit mode.
Note that the instructions from the link above refer to accessing Excel 2010. If you need to access an earlier version of Excel try using "Excel 12.0" in your extended properties.
I have implemented these drivers on my SSIS server, and can now successfully run my SSIS packages in 64 bit mode and they happily access my Excel files.
I haven't done any detailed benchmarks but initial comparisons between the same package running in 32 bit mode and 64 bit mode show some very good performance improvements.
Just a quick note to say why I haven't blogged anything for a while, about me or the Canberra SQL User Group.
I am currently unemployed, by choice. I have left the ACT Government and will be starting with Wardy IT as of Monday April 19th. I expect that will be a lot of fun working with Peter and the gang.
So my Wife Jennifer and I are having a bit of a break before the new job starts, as I expect I won't get any more leave for a while. We've just been camping with friends for 5 days (which is why there was no UG meeting this month) and are about to leave for Bali for 7 days.
Good luck to Peter and Anthony with the ALM Conference next week. Wish I could be there but Bali calls.
Many thanks to Geoff Orr for his presentation on SQL MDX. I got a lot out of it and now realise it's not such a black art after all.
It was good to see that my giveaway book prize of Microsoft SQL Server 2008 MDX Step by Step, donated by O'Reilly Publications, was one of Geoff's recommended reference manuals. As I had just given away my only copy of this book I have since purchased an electronic copy of the book from O'Reilly and it looks fantastic on my Kindle DX, which is now available in Australia. Now to just find the time to read it.
Congratulations to Ben who won the book. He wasn't even planning to come to the meeting but was working with Geoff, our presenter, so tagged along when I picked Geoff up.
Other congratulations go to Thambap who won the copy of Windows 7 Ultimate Edition 64 bit that was donated by Microsoft User Group Support Services.
Thanks to both our sponsors, O'Reilly for the books, and Microsoft UGSS for the software and the pizza.
Presenting for us this month is Geoff Orr with a presentation called 'MDX for SQL Dudes'.
Geoff describes his presentation as follows;
If your brain is wired for Transact-SQL and you're looking for a quick and dirty introduction to the weird and wonderful world of Multi-Dimensional eXpressions then this is the session for you. The syntax looks similar to SQL but does not work the same way. Cubes have far more metadata & MDX takes advantage of that. The session commences with an explanation of the basic concepts and principles underpinning the MDX language, contrasts it with TSQL and then proceeds to describe and demonstrate key syntax, MDX queries and some MDX scripting. Key Topics: Dimensions, Measure Groups and Members, How to do a join, How to add a condition, Where vs Filters. Perspectives, Accessing Properties, A few must know functions.
Some info about Geoff;
Geoff Orr is an MCAD with over 20 years experience in IT in Australia and the UK. Geoff works in Database, Development and Business Intelligence. He has worked on systems as diverse as government grants, logistics, finance and even rubbish trucks. Geoff believes that data has its own story and loves databases. Geoff has previously worked for Microsoft in Premier Support for SQL Server.
This month we have 2 give away prizes for 2 lucky attendees that fill out an evaluation form;
Please register your attendance for this meeting at www.sqlserver.org.au or www.usergroupsupportservices.com
I have been trying out Microsoft's newest version of Visual Studio, the Visual Studio 2010 Ultimate Release Candidate. The new environment is quite nice although there doesn't seem to be a lot of difference with database projects so far. One nice extra is that you can now have windows tear off and be placed outside of the IDE. As I have multiple monitors I like to have the Solution Explorer open in the main IDE, and the Schema View window open on my second monitor. I find this greatly helps with quickly navigating around my project.
Anyway, back to the blog post.
When I am creating a new database schema, I like to have my project automatically load any required default data to my lookup tables. These tables are the ones where the data very rarely changes and are primarily referenced by Foreign keys. Examples of this table type could be a Status table, an Address type or even a Public Holiday date table that you'd like pre loaded.
Where I work we have tried different ways to action this but all the solutions have depended on a post deployment script executing either a bulk copy or multiple insert statements as follows;
-- Non SQL 2008 version
IF NOT EXISTS (SELECT 1 FROM [dbo].[TableName])
BEGIN
INSERT [dbo].[TableName] ([Column1], [Column2])
VALUES (Value1, Value2);
INSERT [dbo].[TableName] ([Column1], [Column2])
VALUES (Value3, Value4);
INSERT [dbo].[TableName] ([Column1], [Column2])
VALUES (Value5, Value6);
END;
-- SQL 2008 version
IF NOT EXISTS (SELECT 1 FROM [dbo].[TableName])
BEGIN
INSERT [dbo].[TableName] ([Column1], [Column2])
VALUES (Value1, Value2)
, (Value3, Value4)
, (Value5, Value6);
END;
-- Bulk Copy version
IF NOT EXISTS (SELECT 1 FROM [dbo].[TableName])
BEGIN
BULK INSERT [dbo].[TableName] FROM '$(DataFilePath)TableName.bcp WITH ...
END;
-- Create the table
CREATE TABLE [dbo].[AddressType]
(
[Id] INT IDENTITY(1, 1)
CONSTRAINT [PK_AddressType] PRIMARY KEY CLUSTERED
NOT NULL
, [Code] NVARCHAR(20) CONSTRAINT [UQ_AddressType_Code] UNIQUE ([Code])
NOT NULL
, [Description] NVARCHAR(150) NULL
);
Then I create a post deployment script LoadAddressType.sql in the database project with the following code in it;
MERGE [dbo].[AddressType] AS Target
USING
(
SELECT N'Billing', N'Billing Address Type'
UNION ALL
SELECT N'Branch', N'Branch Address Type'
UNION ALL
SELECT N'Head Office', N'Head Office Address Type'
UNION ALL
SELECT N'Postal', N'Postal Address Type'
UNION ALL
SELECT N'Residential', N'Residential Address Type'
) AS Source ([Code], [Description])
ON (TARGET.[Code] = SOURCE.[Code])
WHEN NOT MATCHED BY SOURCE
THEN DELETE
WHEN NOT MATCHED BY TARGET
THEN INSERT ([Code], [Description])
VALUES (SOURCE.[Code], SOURCE.[Description]) ;
This creates a virtual Source table inside the USING block which, at project deployment is compared to the target table on the common [Code] field. If the target table has a row which is not in the virtual table then it is deleted from the target table and if the target table does not have a row which is in the virtual table, then the virtual row gets inserted to the target table. The first time this script is executed will insert 5 rows to the target AddressType table.
If I were then to update the LoadAddressType.sql script as follows, deleting 'Branch' and modifying 'Head Office' to 'Central Office';
MERGE [dbo].[AddressType] AS Target
USING
(
SELECT N'Billing', N'Billing Address Type'
UNION ALL
SELECT N'Central Office', N'Central Office Address Type'
UNION ALL
SELECT N'Postal', N'Postal Address Type'
UNION ALL
SELECT N'Residential', N'Residential Address Type'
) AS Source ([Code], [Description])
ON (TARGET.[Code] = SOURCE.[Code])
WHEN NOT MATCHED BY SOURCE
THEN DELETE
WHEN NOT MATCHED BY TARGET
THEN INSERT ([Code], [Description])
VALUES (SOURCE.[Code], SOURCE.[Description]) ;
Then on the next deployment of the project, the 2 rows with a [Code] value of 'Branch' and 'Head Office' would be deleted from the target table as they are no longer in the virtual table and a new row with a [Code] of 'Central Office' would be inserted. The other 3 rows would remain unchanged.
If you wanted to extend the functionality so that the Description field gets updated if changed, then you could add the following code block to the merge which also compares the [Description] field;
MERGE [dbo].[AddressType] AS Target
USING
(
SELECT N'Billing', N'Billing Address Type'
UNION ALL
SELECT N'Central Office', N'Central Office Address Type'
UNION ALL
SELECT N'Postal', N'Postal Address Type'
UNION ALL
SELECT N'Residential', N'Residential Address Type'
) AS Source ([Code], [Description])
ON (TARGET.[Code] = SOURCE.[Code])
WHEN MATCHED AND TARGET.[Description] <> SOURCE.[Description]
THEN UPDATE
SET TARGET.[Description] = SOURCE.[Description]
WHEN NOT MATCHED BY SOURCE
THEN DELETE
WHEN NOT MATCHED BY TARGET
THEN INSERT ([Code], [Description])
VALUES (SOURCE.[Code], SOURCE.[Description]) ;
The way I have the virtual table coded relies on implicit data conversion for it's success, as well as being easier to read for blog entries but it really should be coded with the proper data type casting as follows;
MERGE [dbo].[AddressType] AS Target
USING
(
SELECT CAST('Billing' AS NVARCHAR(20)), CAST('Billing Address Type' AS NVARCHAR(150))
UNION ALL
SELECT CAST('Central Office' AS NVARCHAR(20)), CAST('Central Office Address Type' AS NVARCHAR(150))
UNION ALL
SELECT CAST('Postal' AS NVARCHAR(20)), CAST('Postal Address Type' AS NVARCHAR(150))
UNION ALL
SELECT CAST('Residential' AS NVARCHAR(20)), CAST('Residential Address Type' AS NVARCHAR(150))
) AS Source ([Code], [Description])
ON (TARGET.[Code] = SOURCE.[Code])
WHEN NOT MATCHED BY SOURCE
THEN DELETE
WHEN NOT MATCHED BY TARGET
THEN INSERT ([Code], [Description])
VALUES (SOURCE.[Code], SOURCE.[Description]) ;
Note that you could also copy the Merge query into an SSMS query and run it directly to get the same results.
One problem you may run into if you delete a value from your virtual source table is that come deployment, if that value is already referenced by a foreign key in another table, then unless you have the ON DELETE CASCADE option set for the foreign key the Merge statement will fail, causing your deployment to fail.
This method works well for us for small amounts of default data, but is still not suitable for large scale Bulk Insert data loads as it would just take too long to set up the virtual table, not not mention it would probably not be very efficient. For these it is probably best to empty the target table before executing the bulk insert.
------ Build started: Project: BillingDB, Configuration: Debug Any CPU ------ BillingDB -> D:\VS 2010 tests\Billing\Database\sql\BillingDB.dbschema ------ Deploy started: Project: BillingDB, Configuration: Debug Any CPU ------ D:\VS 2010 tests\Billing\Database\sql\Billing_BillingDB.sqldeployment(0,0): Error SQL00256: The deployment property IgnoreFileAndLogFilePath could not be used to configure deployment. BillingDB.dbschema(0,0): Error TSD01234: The deployment configuration file could not be loaded. Deployment cannot continue Done executing task "SqlDeployTask" -- FAILED. Done building target "DspDeploy" in project "BillingDB.dbproj" -- FAILED. Done executing task "CallTarget" -- FAILED. Done building target "DBDeploy" in project "BillingDB.dbproj" -- FAILED. Done building project "BillingDB.dbproj" -- FAILED. Build FAILED. ========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ========== ========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========After some investigation it seems that the VS 2008 database deployment property IgnoreFileAndLogFilePath is no longer available in VS 2010, and that the upgrade process for database projects does not delete or modify this setting. Selecting Edit for the sqldeployment options, making a change, then saving the change, also does not remove the property from the file.
Just had a small issue with my SQL Database project.
In my Script.PostDeployment.sql file I put a standard SQL comment at the end of a line just to remind me of the required data load order;
:r LoadLookupTable.sql :r LoadTestData.sql -- Must load LookupTable firstWhen building the project this caused the following error;
C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v9.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets(80,5)Error MSB4018: The "SqlSetupDeployTask" task failed unexpectedly.System.ArgumentException: Illegal characters in path. at System.IO.Path.CheckInvalidPathChars(String path) at System.IO.Path.IsPathRooted(String path) at Microsoft.Data.Schema.Sql.Build.SqlDeploymentScriptModifier.GenerateMergedSqlCmdFiles(ContributorConfigurationSetup setup, ContributorConfigurationFile configFile) at Microsoft.Data.Schema.Sql.Build.SqlDeploymentScriptModifier.OnEstablishDeploymentConfiguration(ContributorConfigurationSetup setup) at Microsoft.Data.Schema.Build.DeploymentProjectBuilder.VerifyConfiguration() at Microsoft.Data.Schema.Tasks.DBSetupDeployTask.BuildDeploymentProject(ErrorManager errors, ExtensionManager em) at Microsoft.Data.Schema.Tasks.DBSetupDeployTask.Execute() at Microsoft.Build.BuildEngine.TaskEngine.ExecuteInstantiatedTask(EngineProxy engineProxy, ItemBucket bucket, TaskExecutionMode howToExecuteTask, ITask task, Boolean& taskResult) Done executing task "SqlSetupDeployTask" -- FAILED.By moving the comment to its own line like the following it then built correctly;
:r LoadLookupTable.sql -- Must load LookupTable first :r LoadTestData.sqlI know it's because it's being interpreted in SQLCMD mode but you'd think the parser would be smart enough to ignore it.
A first chance exception of type 'antlr.NoViableAltForCharException' occurred in Microsoft.Data.Schema.ScriptDom.Sql.dll A first chance exception of type 'antlr.MismatchedTokenException' occurred in Microsoft.Data.Schema.ScriptDom.Sql.dll A first chance exception of type 'System.NullReferenceException' occurred in Microsoft.Data.Schema.Sql.dllI was getting a bit out of my depth here so started a thread on the Microsoft Visual Studio Team System Database Edition forum asking for help.
