In exactly 1 week I’m at PASS Summit 14

firsttimers

In exactly 1 week from now I’m in Seattle for my first ever Pass Summit, and I for one cannot tell you how much I look forward to be there with all the amazing people from the SQL community. As it my first time there i’m planning to attend the different “firsttimer” events, as well as all the other events that’s part of this summit.

We’re 3 guys travelling together this time, 2 from Rehfeld and 1 from Mærsk (whom used to be at Rehfeld) – and instead of crashing each night at different hotel rooms we’ve rented an whole appartment via AirBNB.

It seemed as the smartest, and it’s actually cheaper than hotels, only downside, is that the houserules says “STRICTLY NO PARTIES” – gonna be a tough one to manage ;o)

I’m attending 2 full day pre conference workshops, monday and tuesday and I’m really excited to learn from the best – and even pick up some tricks for use in my own presentations at SQLSaturdays around europe.

I’m in the process of trying to plan my session schedule, but I assure you, it’s a tough task – trying to pick which one of the sessions that I can skip to go for another.

Well until sunday next week, have a nice week, catch me on twitter, maybe we’ll see each other in Seattle.

 

CU 4 for SQL Server 2014 ready

sql-server-2014It’s a good day today, the release of Cumulative Update 4 for SQL Server 2014 will fix a lot of small and larger bugs.

Including the painfull one that task would shift to upper left corner in SSIS when resized.

Cumulative update package 4 for SQL Server 2014 (including update for SSIS) is available at http://support.microsoft.com/kb/2999197/en-us

This CU release contains a list of bug fixes for SQL in general, SSRS, SSAS and SSIS.

For SSIS fixes:

FIX: Tasks shift to upper-left corner when you move or resize them while designing a maintenance plan in SSMS 2014

FIX: Error in SQL task with IsQueryStoredProcedure set to True when you use .NET Data Provider for Teradata in SSIS

FIX: SSIS 2012 or SSIS 2014 XML Source task cannot load data from URI

For SSAS Fixes:

FIX: MDX query returns no result when the cache is not cleared after you run a previous query in OLAP or tabular model

FIX: User obtains incorrect data when two users in the same security role run a client report in SSAS 2012 or SSAS 2014

SQL Performance Fixes:

FIX: A severe error occurs when you run a query that uses CTEs against the partitioned tables in SQL Server

SSRS Fixes:

FIX: Error when you print, print preview, or export the report to PDF in SSRS 2012 or SSRS 2014

For the full list look here

Salesforce and integration with SQL Server – part 2

SFSQL

As described int the previous post, I’ve been working on a project which integrates data from Salesforce (SF) to Navision (NAV) manipulate and create new data, and then we needed to push it back to SF again.

To get all this to work together , there is a need for a local database in your SQL server, and in this case we call it LOCALSF – this is where alle the storedprocedures from DBAmp resides. But it is also the place for the local tables which is used to store data from the SF cloud storage.

The previous post showed you how to generate the tables to hold SF data, but DBAmp is also capable of Insert, Update and Delete in SF cloud storage. To mange this there is a need for either a Insert, Update or Delete table – these can be generated almost automatically by calling the stored procedure SF_Generate

To do this we have to issue a statement that will grab the table structure from SF containing all the attributes of which we can insert, update or delete values, the procedure wil then render a table in the local database.

The statement if we would like to create an insert table for account is this

EXEC SF_Generate @operation = 'insert', 
                 @table_server = 'SFCLOUD', 
                 @load_tablename = 'Account_insert'

This creates a table with an ID, ERROR attributes as well as alle the attributes needed to insert a new Account in SF, you then need to populate the table with values, ID and ERROR must be left empty as DBAmp uses these to keep hold of which record is deployed and which are not.

After the population of the account_Insert table, we need to puch the data into SF, this is done by calling the following statement.

EXEC SF_BulkOps @operation = 'Insert',
                @table_server = 'SFCLOUD', 
                @table_name = 'Account_Insert'

What DBAmp then does is, that it will grab all rows from the insert table and insert them into the account object in SF, afterwards it writes a status in the ERROR attribute on the insert table, so it’s possible to see if any given record is succesfully transfered.

If you instead want to generate a table for Deletes you have to issue the following statement.

EXEC SF_Generate @operation = 'delete', 
                 @table_server = 'SFCLOUD', 
                 @load_tablename = 'Account_delete'

This statement will generate a table called ‘Account_delete’ which will contain an ID, and ERROR attribute and that’s it.

Then you could populate the IDs with SF ID’s that needed deletion from SF, then as before you would need to issue a statement for deleting these records.

EXEC SF_BulkOps @operation = 'Delete', 
                @table_server = 'SFCLOUD', 
                @table_name = 'Account_Delete'

Again DBAmp will connect to SF in the cloud and delete the approriate records and save a status in the ERROR attribute.

In the next posts, i’ll be getting into the stored procedure SF_BulkOps that we shortly looked at in this post.

Salesforce and integration with SQL Server

SFSQLIn a recent project i’ve been working on, we needed to transfer data from Salesforce (SF) to Navision (NAV) manipulate and create new data, and then we needed to push it back to SF again. To manage this we needed to persistate the data from SF in our on onpremise SQL database, there are a few tools that allows that – we used DBAmp for the purpose, its a framework developed in T-SQL using stored procedures. It allows you to replicate tables from the SF Cloud to you on-premise database.

In the following series of posts I’ll go through the various procedures and how it could be implemented in Integration Services

First step is to make a Linked Server on you SQL server to the SF database in the cloud (how to do this is not covered here) – when that is in place you should be able to make queries like this to you data in SF.

SELECT * FROM SFCLOUD...Account

In the above statement SFCLOUD is the name of the linked server, and remember to use 4 name convention

These are the main procedures in the product, and before we can push data to SF we need to have local copy of the table.

  1. SF_Replicate
  2. SF_Generate
  3. SF_ReplicateAll
  4. SF_BulkOps
  5. SF_Refresh

To do this we have to issue a statement that will grab the table structure from SF and then make a create statement on our local server, the statement if we would like to get the account table is this

EXEC SF_Replicate @linked_server = 'SFCLOUD',
                  @object_name = 'Account', 
                  @options = 'batchsize(50)'

What this will do is that the table will be created in the local database, and the data will be transfered as well, and data will be transfered in batches of 50 rows.

When the stored procedure is finished you should be able to select data from your local table, in your database, so if your database is called LOCALSF the you SQL statement would look like this.

USE LOCALSF
GO
SELECT * FROM dbo.Account

That should result in a dataset exactly as the one you would get from the first statement, what was executed on the Linked Server.

In the next posts, i’ll be getting into the stored procedure SF_Generate

Flush DNS on iPhone

IMG_0663.JPGDuring the move of this blog from one hosting partner to another, I had the need for testing the site from my mobile, in this case an IPhone.

But no matter what I did and how long I waited after the DNS record was changed, I still got the old site! Now if it was an laptop running windows I would just have opened up an command prompt and run the following command.

Ipconfig /flushdns

But as you know, there’s no command prompt on an iPhone, so how do we manage to flush the DNS.

I thought that maybe turning the wifi off and on would do the trick, but no still the old site.

There is an option that flushes the DNS and that is so simple I almost laughed when I found it!

Just enable “airplane” mode on your iPhone or ipad and disable it again

Badabing, the new site was shown, instead of the old one, I hope you can use this little trick even though it’s not about sql server og databases.

Have a nice weekend

Campus Days 2014 – Our abstract got selected

uIIot3-P_400x400So this year I submitted an abstract for CampusDays ’14 – the conference of the year, well actually one of my friends and I submitted together. So I’m thrilled to be speaking in 2 sessions together with Régis Baccaro ( Twitter | Blog ), we’ll be talking about Azure, Azure Storage, HDInsight, Azure Machine Learning and a bunch more.

The speakers selected this year, is in the absolut top, there’s MVP’s (did I mention that Régis is MVP) experts in various fields, so if you would like to know the latest about Microsoft Technology – do not hesitate – hurry up and register for a great time, together with like minded people.

Have a look at the tracks, and other information about Campus days in Copenhagen – Click on the logo

 

Campusdays er stedet hvor IT Pro og udviklere mødes channel9.msdn.com/Events/Microso… #CampusDays #MSDevDK #DKITPro

I’ll be speaking at #SQLSAT275 in Copenhagen

Just recieved an email from Régis Bacarro (Twitter | Blog) who is the organizer of SQLSaturdays in Denmark. He informed me that my session on Master Data Service Automation had been chosen for the SQL Saturday 275 event, 29th of March 2014. Needless to say that i’m thrilled to be given this opportunity, and it’ll be my debut as a SQLSAT speaker, but hey, there’s a first for everything. I look very much forward to catch up with all of the other speakers, and to further develop my SQL network.

 

So I hope to see you at the event, if you haven’t already signed up for a full day packed with SQL info, hurry up and get at seat.

Register Here

MDS 2012 – Recreate subscriptionviews

In Master Data Services 2012 (MDS) we have the possibility to create an subscription view of the data in our MDS lists. This gives users, applications access to Master Data (MD) through a view, and therefore we have full control over MD, as the user or application is unable to update the MD in the view.

One of the disadvantages is that whenever there is a change in the Meta Model for the MD which the subscriptionview is based – we have to recreate the subscriptionviews. A rather tedious task for the one that have to do it.

As you can see here it takes a good amount of time to delete and recreate the view.

There are a few ways to do it, one is to use a SSIS package to do it, another is to use a T-SQL script, this post will show the latter, but there is a post on the way for SSIS.

So to help with that I created a SQL Script that will do that for you in a matter of seconds, all views in the specified Model and Version will be deleted and recreated – at the moment the script can only handle the simplest form of views.

All you have to do is to rename the Modelname and VersionName to the one you’re using, and as always this is an unsupported way of using MDS, and you should not do it in your Production Environment, and only if you’re confindent with the MDS datamodel.

DECLARE @ModelName as Varchar(50) = 'FinanceMasterData'
DECLARE @versionName as Varchar(50) = 'MonthlyPostings'
DECLARE @ViewName as Varchar(50) 
DECLARE @ModelID as INT
DECLARE @ModelVersionID as INT
DECLARE @EntityID as INT
DECLARE @Rows INT = 1, @Id INT = 0

----Set Model ID
SELECT @ModelID = ID FROM MDM.tblModel WHERE Name = @ModelName

----Set ModelVersionID
SELECT @ModelVersionID = ID FROM mdm.tblModelVersion 
WHERE name = @versionName

--Delete temp table if already created
IF OBJECT_ID('tempdb..#TempSubscriptionViews') 
IS NOT NULL DROP TABLE #TempSubscriptionViews

--Fill temp table with SubscriptionID, EntityID (MDStableEntityID) 
--and SubscriptionViewNames for supscriptionviews
SELECT S.ID, E.ID as EntityID, E.name Into #TempSubscriptionViews 
FROM mdm.tblSubscriptionView S
INNER JOIN MDM.tblEntity E ON E.ID = S.Entity_ID
WHERE S.Model_ID = @ModelID and S.ModelVersion_ID = @ModelVersionID


--Delete all the Subscriptionviews 
DECLARE @DeleteID INT
DECLARE DeleteCursor CURSOR FAST_FORWARD
FOR

SELECT ID FROM #TempSubscriptionViews
OPEN DeleteCursor

FETCH NEXT FROM DeleteCursor
INTO @DeleteID
WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC mdm.udpSubscriptionViewDeleteByID @DeleteID,1
FETCH NEXT FROM DeleteCursor
INTO @DeleteID

END
CLOSE DeleteCursor
DEALLOCATE DeleteCursor

--Recreate all the Subscriptionviews
DECLARE CreateCursor CURSOR FAST_FORWARD
FOR

SELECT EntityID, Name FROM #TempSubscriptionViews
OPEN CreateCursor

FETCH NEXT FROM CreateCursor
INTO @EntityID, @Viewname
WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC MDM.udpSubscriptionViewSave     NULL,
                                         @EntityID,
                                         @ModelID,
                                         NULL,
                                         @ModelVersionID,
                                         NULL,
                                         1,
                                         NULL,
                                         @Viewname,
                                         NULL,
                                         NULL
FETCH NEXT FROM CreateCursor
INTO @EntityID, @Viewname

END
CLOSE CreateCursor
DEALLOCATE CreateCursor

MDS 2012 – Change Attribute order with SQL

If you need to change the order of attributes in an MDS entity, there is the correct way to do it, and then there is the easy way to do it ;o)

First the correct way:

Locate your Entity i MDS System Administration, select the Entity and Edit the Entity

Now select the attribute that needs another sorting, and use the arrow up/down to select the correct place.

 

 

The much easier method

This method involves a knowledge of the table structure in MDS, and it is advised not to do this in a production environment!

Open SQL Server Management Studio, select you MDS database.

Find your entity with this SQL :

SELECT ENTITY_ID, NAME FROM MDM.VIW_SYSTEM_SCHEMA_ENTITY
WHERE NAME LIKE '%_MONTHLYPOSTINGS'

Now you have the Entity_ID for the entity you need to change attribute order on.

Run this script:

SELECT 'UPDATE MDM.TBLATTRIBUTE 
SET SORTORDER = ' + CAST(A.SORTORDER AS VARCHAR(3)) + ' 
WHERE ENTITY_ID = ' + CAST(A.ENTITY_ID AS VARCHAR(4)) + ' 
AND NAME = ''' + A.NAME + '''' 
FROM MDM.TBLATTRIBUTE A WHERE A.ENTITY_ID = 156

This will generate a list of updates just like this list:

UPDATE MDM.TBLAttribute SET SortOrder = 1 WHERE Entity_ID = 156 and NAME = 'ID'
UPDATE MDM.TBLAttribute SET SortOrder = 2 WHERE Entity_ID = 156 and NAME = 'Version_ID'
UPDATE MDM.TBLAttribute SET SortOrder = 3 WHERE Entity_ID = 156 and NAME = 'Status_ID'
UPDATE MDM.TBLAttribute SET SortOrder = 4 WHERE Entity_ID = 156 and NAME = 'ValidationStatus_ID'
UPDATE MDM.TBLAttribute SET SortOrder = 5 WHERE Entity_ID = 156 and NAME = 'EnterDTM'
UPDATE MDM.TBLAttribute SET SortOrder = 6 WHERE Entity_ID = 156 and NAME = 'EnterUserID'
UPDATE MDM.TBLAttribute SET SortOrder = 7 WHERE Entity_ID = 156 and NAME = 'EnterVersionID'
UPDATE MDM.TBLAttribute SET SortOrder = 8 WHERE Entity_ID = 156 and NAME = 'LastChgDTM'
UPDATE MDM.TBLAttribute SET SortOrder = 9 WHERE Entity_ID = 156 and NAME = 'LastChgUserID'
UPDATE MDM.TBLAttribute SET SortOrder = 10 WHERE Entity_ID = 156 and NAME = 'LastChgVersionID'
UPDATE MDM.TBLAttribute SET SortOrder = 11 WHERE Entity_ID = 156 and NAME = 'LastChgTS'
UPDATE MDM.TBLAttribute SET SortOrder = 12 WHERE Entity_ID = 156 and NAME = 'Name'
UPDATE MDM.TBLAttribute SET SortOrder = 13 WHERE Entity_ID = 156 and NAME = 'Code'
UPDATE MDM.TBLAttribute SET SortOrder = 15 WHERE Entity_ID = 156 and NAME = 'MUID'
UPDATE MDM.TBLAttribute SET SortOrder = 16 WHERE Entity_ID = 156 and NAME = 'AsOf_ID'
UPDATE MDM.TBLAttribute SET SortOrder = 17 WHERE Entity_ID = 156 and NAME = 'ChangeTrackingMask'
UPDATE MDM.TBLAttribute SET SortOrder = 18 WHERE Entity_ID = 156 and NAME = 'Description'
UPDATE MDM.TBLAttribute SET SortOrder = 30 WHERE Entity_ID = 156 and NAME = 'Comment'
UPDATE MDM.TBLAttribute SET SortOrder = 14 WHERE Entity_ID = 156 and NAME = 'Item'
UPDATE MDM.TBLAttribute SET SortOrder = 31 WHERE Entity_ID = 156 and NAME = 'Approved'

Now just adjust the number in SortOrder, and execute the updates.

Remember to update any subscription views on the Entity

This is way easier than using the System Administration website.