SSIS – String or binary data would be truncated. Invalid object name ‘sys.trusted_assemblies’.

I have SQL Server Management studio 14.0 as well as version 11.0 installed. When I was trying to setup SSISDB under Integration Services Catalogs using SSMS version 14.0, I  encountered below exception:

TITLE: Microsoft SQL Server Management Studio
——————————

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Management.IntegrationServices)

——————————
ADDITIONAL INFORMATION:

String or binary data would be truncated.
Invalid object name ‘sys.trusted_assemblies’. (Microsoft SQL Server, Error: 8152)

 

I wasn’t really sure what should  be done to resolve this as the password I provided was proper and also checked the first checkbox “Enable CLR Integration”.

I just tried the same operation from SSMS version 11.0 and it worked. Still not sure what the issue was but I could carry on with work.

Hope this helps someone facing the same issue.

 

 

 

Advertisements

SQL – Get Start and End Date of the Current Financial Year

Here is the formula I use to calculate start and end date of the current financial year. I hope it helps.

DECLARE @StartDate DateTime

DECLARE @EndDate DateTime
SET @StartDate = cast(’01-July-‘ + cast(CASE WHEN datepart(mm, CAST(GETDATE() AS datetime)) IN (7, 8, 9, 10, 11, 12) THEN DATEPART(yy, CAST(GETDATE() AS datetime)) ELSE DATEPART(yy, CAST(GETDATE()                          AS datetime)) – 1 END AS varchar) AS datetime)

SET @EndDate = EOMONTH(DATEADD(MONTH, 11, @StartDate))

SSRS – Header is cut off while Printing the Exported Excel file

Problem – When a report is viewed in SSRS or exported to PDF and printed, the header text format looks fine. The same report, when it is exported to excel, it formats well in Excel but while printing it, it is truncating the header column.

Here is how Excel file looks like.

Excel1

Here is the print preview in Excel, which is chopping off the column header.

Excel2

Solution –

If you have “CanGrow” property on the column set to True, set it to False and manually increase the height of the header column. This resolved my issue and here is how it looks like in print preview now.

Excel3

 

SSRS – Repeat column headers on each page

Recently I was trying to show column headers when the table is growing and going to multi-page. Tried various options and then found this easy to show which I would like to share it here.

  1. Click on the column header, it will show Row Groups and Column Groups on the screen. Right click Column groups and click on “Advanced Mode”.

AdvanceMode

2. Click on Row Groups, click on “Static” and press F4 to go to the properties.

RowGroup

3. Click on Properties and set “KeepWithGroup” to After and “RepeastOnNewPage” to “True”

Rowproperties

That’s it, all good to go!

MS Dynamics CRM (2013/2015/2016/D365)- Filtering a lookup(Customer) field

In the scenario, when customer field is being used in the form and later need to make changes to show either Account/ Contact in the lookup, you can write below script to filter. Please find detailed information about addPreSearch and addCustomFiler here

Place below line of code under the onLoad script.

 

Xrm.Page.getControl(“parentcustomerid”).addPreSearch(addFilter);

 

function addFilter() {

fetchQuery = “<filter type=’and’><condition attribute=’accountid’ operator=’null’ /></filter>”;

Xrm.Page.getControl(“parentcustomerid”).addCustomFilter(fetchQuery, ‘account’);

}

 

Similarly, to hide contacts, use the script below

function addFilter() {

fetchQuery = “<filter type=’and’><condition attribute=’contactid’ operator=’null’ /></filter>”;

Xrm.Page.getControl(“parentcustomerid”).addCustomFilter(fetchQuery, ‘contact’);

}

I hope this helps.

 

 

SSRS – Remove numbers or Text from a Field

I am very new to SSRS and there was a need to remove numbers from the value coming from DB. I found this quick way to write an expression to remove numbers or Text from a field in SSRS. Hope it would be useful to a newbie 🙂

System.Text.RegularExpressions.Regex.Replace(FieldValue,RegExPattern, ValueToReplace)

=System.Text.RegularExpressions.Regex.Replace(IIF(IsNothing(Fields!FieldName.Value), “”, Fields!FieldName.Value),  “[0-9]”, “”)

In a similar way to remove text from the string and to show only the numbers, here is the formula –

=System.Text.RegularExpressions.Regex.Replace(IIF(IsNothing(Fields!FieldName.Value), “”, Fields!FieldName.Value), “[^0-9]”, “”)