Feeds:
Posts
Comments

The reason why it’s not updating is because the schema has not been included in the update. So if your objects are created under a certain schema, the schema needs to be included.

Also, the schema needs to be at the top of the list otherwise the rest of the objects will be not added into the project in TFS. To get it to the top of the list, you can group it by schema as shown below.

 

Advertisements

I have a business requirement where when a user inserts a new record via Excel on MDS, the Code will be automatically generated as a concatenated value (once published), that makes the record unique.

To do this in MDS, I have had to create a business rule on the entity to say that if the Code is equal to zero then Code equals a concatenated value. The example below uses a concatenated value that consist of position_id and cost_centre.

After the business rule is published, the user is now able to insert a new record, however, he/she must set the Code value to zero or some numbers because it cannot be empty. This can be annoying because after copy and paste the record into the Excel, the user needs to modify the Code to zero or some numbers. It will be better if he/she only needs to copy and paste and then delete the Code (so that the Code appears blank). The above business rule will not work as the Code cannot be blank.

To solve this, the Code must be an integer instead of varchar. On MDS, you can set the Code to be automatically generated. Go to Home > System Administration > Manage Models > Entities and then edit the Entity and tick the “Create code values automatically” tickbox and set the “Start with :” textbox to number 1, as shown below:

You would then need to truncate and reload data into the entity. If you add a member into the entity manually, you will see that the Code textbox is greyed out as shown below.

Once set up, all good to go! 🙂

 

 

 

 

 

I have an SSIS package that does SCD type 2, i.e. when there is an update, it inserts a new record and then expires the previous record. In testing it, I updated one record but when the package was run, the INSERT has failed with the error saying that the primary key constraint has been violated and cannot insert duplicate key in object. I checked my code and the checksum making sure that the incoming record and the existing record have different checksums. Indeed they both have different checksums. So this does not make any sense.

I talked about this with my colleague and he Googled it and found a solution (https://stackoverflow.com/questions/20153310/identity-not-generating-the-seed-and-increment-correctly)  that worked for my case. Thanks to the user that has shared the fix. 🙂

The solution is to reseed the table by running the following SQL:

DBCC CHECKIDENT(‘Id_Table’, RESEED, 0)    —- Reseed to any smallest number

DBCC CHECKIDENT(‘Id_Table’, RESEED)      —- Reseed without providing any seed value

Reseed the table to Zero and then just execute the DBCC command without any seed value and it will reseed the Identity value to next highest available Identity value. You do not need to truncate the table to reseed.

Even though the solution has fixed the problem, however, it keeps recurring.

I had another chat with my colleague at work. He said he has seen this before and provided me with the fix.

The fix is to insert the default records (where the UIDs are -1, -2, -3) first then load the records from the source into the destination table in the SSIS package. UID = -1 is unknown, -2 is no value and -3 is invalid.

LAST_VALUE does not always return with the same value. The workaround is to use FIRST_VALUE but order the records descendingly.

 

I want to show a “80 out of 100” below the “80%” in a single textbox within a tablix in SSRS. To do it, create two placeholders on the same textbox and then place a break (“<br>”) at the end of the expression in the first placeholder. Format the font of the expression in the second placeholder to a smaller font.

 

I have a dataset as shown below. When the OP_CODE is null and PRIORITY = 1, I need to shift the OP_CODE of the Priority = 2 to become 1 and 3 to become 2 and 4 to become 3.

The dataset also has the history of the changes made to the records.

At first I ranked the records by using the RANK function but it didn’t work at all. It set the ranking incorrectly as shown below. It didn’t work because it ranks every record instead of every distinct record in the partition.

I then used DENSE_RANK function as it ranks the records within the partition of a result set, without any gaps in the ranking. When there is a tie in the partition, it ranks it to the same number. In this example, there are 3 records with the Priority_Shifted of 30000. These 3 records should all be ranked to 4. The DENSE_RANK function has done it! 🙂

 

 

Apparently Microsoft has redesigned the UI of the SSRS Report Manager in particular the Data Sources bit.  In SSRS 2016, you have a dropdown list to select the credential type i.e. Windows user name and password or Database user name and  password. In the previous version, you need to tick a box that says “Use as Windows credentials when connecting to the data source”.

When I first set up my data source on SSRS 2016 report manager, I ticked the box that says “Log in using these credentials, but then try to impersonate the user viewing the report”. It was a habit that as soon as I put my credential in,  I would tick the box.

Previous version:

SSRS 2016 version:

 

%d bloggers like this: