How to process join date and anniversary date in reports if the requirement is to show the anniversary date fall in this year or next year?

I have used a case in my query to break down two scenarios.

1.       Check adding 1 year to the joined year equals to 1 year add to current year

2.       If equals then Anniversary Date is add one year to Joined Date

3.       Else calculate the no of years from current year to Joined date and add the number of years to Joined year to get the Anniversary Date.

 

SELECT a.jn_dte AS [Jonned Date],

CASE

WHEN DATEPART(year,DATEADD(year,1,a.jn_dte)) = DATEPART(year,dateadd(year,1,GETDATE()))

THEN DATEADD(year,1,a.jn_dte)

ELSE DATEADD(year,DATEPART(year,GETDATE()) - DATEPART(year,a.jn_dte),a.jn_dte)

END AS [Position Anniversary Date]

FROM company a WITH(NOLOCK)

Joined Date	Add 1year to join date	Check	Equals to current year + 1	Results	If No, check the year difference from current year	Equals	Year Difference	Add	Join year	Final result
1991-03-18	1992	=	2015	No	2014-1991	=	23	+	1991	2014-03-18
2014-08-18	2015	=	2015	Yes			1	+	2014	2015-08-05

SQL : Change the Uppercase to Lowercase But Uppercase after hyphen, apostophe or space

Below function change uppercase name to lower case with first letter capital if the name has space, hyphen(-) or apostophe (‘).

 

Ex : FLOWER TULIP  to Flower Tulip

     FLOWER-TULIP to Flower-Tulip

     TULIP O’TULIP to Tulip O’Tulip

     FLOWER BUD-TULIP O’TULIP  to Flower Bud-Tulip O’Tulip

 

CREATE FUNCTION CapToLower(@Name VARCHAR(8000))

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @Position INT

                  
SELECT @Name    = STUFF(LOWER(@Name),1,1,UPPER(LEFT(@Name,1))) COLLATE Latin1_General_Bin,@Position = PATINDEX('%[^A-Za-z][a-z]%',@Name COLLATE Latin1_General_Bin);

WHILE @Position > 0

SELECT @Name   = STUFF(@Name,@Position,2,UPPER(SUBSTRING(@Name,@Position,2))) COLLATE Latin1_General_Bin,@Position = PATINDEX('%[^A-Za-z][a-z]%',@Name COLLATE Latin1_General_Bin);

RETURN @Name;

END
GO

SQL : How to display Null when 0 (Data type is int)

There can be occasions you need to select out NULL values when an integer data type brings 0. Here is the quick fix,


Select CASE WHEN [empNum] = 0 THEN NULL ELSE CONVERT(VARCHAR(30),[empNum]) END AS empNum
From ...

SSRS report footer Page no. of total pages

Subscription Emails TO: Field is greyed out in SSRS 2008

When you try to subscribe SSRS report you might not be able to select other email address to send to because To field is grayed out,

How to make TO filed editable?

This need to be changed in the Report Server Config file. To change this navigate to the report server folder for SQL server. Under it will be the config file.

Set <SendEmailToUserAlias> false</SendEmailToUserAlias> 

True=The e-mail address of the user creating the subscription is used. This is the default value.To field is hidden
False=Any e-mail address can be specified.To field is visible.

How to receive emails without allowing to change TO field?
This way is more secure so no one can send the reports to outsiders or to their personal emails.

You need to set DefaultHostName and this works with SendEmailToUserAlias

Set <SendEmailToUserAlias> true</SendEmailToUserAlias> 

<DefaultHostName>domain.company.com</DefaultHostName>
<PermittedHosts>
     <HostName>domain.company.com</HostName>
</PermittedHosts>

SSRS : Subscription in Report Server

Another helpful feature provided by report server is Subscription. It can also be scheduled to receive reports automatically in the form of Email or File Share. In file share you can specify the format of the file (Pdf,Excel etc..) and schedule to save to ftp location or file path that you specify. If you want to schedule the report via email you can also send it as an attachment by using Render Format (PDF,Excel etc..)

After you deploy reports to report server right click report properties and it shows Subscription. It shows below screens which you can easily set the schedule.

However you also need to configure the RSReportServer.config file location in SQL server/reporting server.

There you may need to provide your SMTP server <SMTPServer>your.SMTP.servername.com</SMTPServer> port number   <SMTPServerPort></SMTPServerPort>

To use a remote SMTP service, this <SendUsing>2</SendUsing> value must be set to 2 in the RSReportServer.config file.

<RSEmailDPConfiguration>

<SMTPServer>SMTP.servername.com</SMTPServer>

<SMTPServerPort>25

</SMTPServerPort>

<SMTPAccountName>

</SMTPAccountName>

<SMTPConnectionTimeout>

</SMTPConnectionTimeout>

<SMTPServerPickupDirectory>

</SMTPServerPickupDirectory>

<SMTPUseSSL>

</SMTPUseSSL>

<SendUsing>2</SendUsing>

<SMTPAuthenticate>

</SMTPAuthenticate>

<From>noreply@From.com</From>

<EmbeddedRenderFormats>

<RenderingExtension>MHTML</RenderingExtension>

</EmbeddedRenderFormats>

<PrivilegedUserRenderFormats>

</PrivilegedUserRenderFormats>

<ExcludedRenderFormats>

<RenderingExtension>HTMLOWC</RenderingExtension>

<RenderingExtension>NULL</RenderingExtension>

<RenderingExtension>RGDI</RenderingExtension>

</ExcludedRenderFormats>

<SendEmailToUserAlias>True</SendEmailToUserAlias>

<DefaultHostName>

</DefaultHostName>

<PermittedHosts>

</PermittedHosts>

</RSEmailDPConfiguration>

  

                                                                             

SSRS - How to make the header appear on each page

No wonder you think easy but sometimes we forget there are more option we need to activate. Here it is...

 

It is not only to set the properties in tablix to make headers available on each page but few more steps for it.

 

Select the Advanced Mode and select Static row properties and set the KeepWithGroup to After and RepeatOnNewPage to True

How to deploy windows service

To install a Windows service

1. On the Start menu or Start Page, open the shortcut menu for Developer Command Prompt, and then choose Run As Administrator.

 

 

2.Navigate to the folder that contains your project's output. Navigate to D:\..\bin\Debug

3.Enter the following command:

installutil.exe program.exe

4.  Provide credentials and password. (domain\username)

5.  If the service installs successfully, installutil.exe will report "The transacted install has completed"

6. Go t to Computer Management and start the service “program”

7. Check the progress from Application log file under Windows Log (If you have written the log to Application)

To uninstall windows service

1.  On the Start menu or Start Page, open the shortcut menu for Developer Command Prompt, and then choose Run As Administrator.

2. Navigate to the folder that contains your project's output. For example, D:\..\bin\Debug

3. Enter the following command:

installutil.exe /u program.exe

4.  If the service uninstalls successfully, installutil.exe will report that your service was successfully removed. ("The uninstall has completed.")

 

A first chance exception of type 'System.NullReferenceException' occurred in program.exe

"A first chance exception of type 'System.NullReferenceException' occurred in program.exe"

The debugger will just exit out of the function only leaving this message and go through the rest of the program? How to break at the point this exception throws?

Go to Debug - > Exception (Ctrl + Altre+E) and click on Find. Select the Exception and tick the Thrown check box.

There is already an open DataReader associated with this Command which must be closed first....

msg:There is already an open DataReader associated with this Command which must be closed first

 

      <add name="ConnectionString"

      connectionString="Data Source=abc\ABCD;Initial Catalog=XYZZ;Integrated Security=True; MultipleActiveResultSets=true"
      providerName="System.Data.SqlClient" />

 

This happens when you have multiple DataReaders open sametime on the same connection. If you call SqlCommand.ExecuteReader but don't close the SqlDataReader returned before calling it again.(Ex: Within a while loop you call another method which opens a data reader).

Solution is to make MultipleActiveResultSets to true in App.Cofig connection string. This is applicable to SQL 2005 and above.

Create a workflow to email the count of an event registration in SharePoint2010

Using MS SharePoint Designer 2010;

 1.       Create a list with columns Title and Event Count – This is to store the no of count incrementing in each registration, I call this as Attendance Limits

 

2.       Create a list with a column Title – This is to store the name list of attendees, I call this as Breakfast

3.       Create a new workflow.
What I do check here is if someone get registered, then check if that user is in the List (Breakfast) title column. Else I set a variable list count to the List I created in point 1. Then push the attendee to in the List I created in Point 2. Then I do the calculation which increment the variable list count by 1 and store it for output variable. Delete the current item value in the List which store the counts and update it same time with new count value.

Then I set it to email to the event organizer. Click on the Email on workflow will allow you to edit the lay out of the Email. Here I take email the name of the user who registered and the latest total of attendees.

4. Publish the workflow, Once you publish the workflow you can see the aspx file has been created for the workflow.

5.Modify aspx file as you want to see it, change the layout wordings, font etc…Also if you want to redirect to a thank you page this can be done here.

6.Grant appropriate user permissions to Lists created otherwise Lists won’t get updated when they get registered and total counts may remain 0.

Can’t drop schema because it is being referenced by object…

 

Are you getting the below error when you try to drop schemas? Here is a trick to drop such schema.



 

Run the below query to find out what are the objects associates with the relevant schema and reassign objects to a different schema.

SELECT * FROM sys.objects

WHERE name = 'TEMP102913I'
and schema_id = SCHEMA_ID('sysadm');

SSRS : How to call sub report grand total on main report

How to call sub report grand total on main report?

Do you have main report with one or more sub report and need to bring the grand total of each sub report to main report within a group? Here is the trick to do this.

You can maintain the sub report as it is. However you need to define same datasets on the main report passing same parameters that you pass to sub reports. The create another tablix with grand total columns on the main report and call these datasets for grand total.

When you view the report, sub report will bring the data and show the sub total within the search group but grand totals for sub reports will be called from the dataset on the main report separately (not from sub report).

Remember to pass the same parameters that you pass to sub reports.

Restore a back up taken from SQL 2000 to SQL 2012?

When trying to restore a backup taken on SQL Server 2000 on a SQL Server 2012 RESTORE DATABASE is terminated?

“The database was backed up on a server running version <Old Version>. That version is incompatible with this server, which is running version <New Version>. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.”

The problem is that on a SQL Server 2012 instance you cannot restore a SQL Server 2000 backup. Practically on SQL Server 2012 you can restore backups taken on SQL Server 2005 or later version.

In such situation you can follow below steps.

- Restoring the SQL Server 2000 backup on SQL Server 2005 or 2008

- Set compatibility level to 90 (SQL Server 2005) or 100 (SQL Server 2008 / SQL Server 2008 R2)

- Backup the same database and restore it on the SQL Server 2012

SSRS: Use the Select All option in the drop down and how to query data to report according to the selections

I have written most of the bits and pieces which may need to make work with SSRS in my previous posts. Here is another common scenario in reports. You may need to select some options or alternately select all the options to view a report. Let’s see how to handle such scenario.

Below scenario, I allow user to select one or more divisions or option to select “”Select All”.

On the report heading when you view the report you also need to show which divisions you have selected, whether you have selected all divisions or one. (Selected Divisions : ...,..,..,)

You may need to handle it using the expression like below. The trick is use JOIN with “,” like below. Use the parameter label with Join which will separated by commas. So if you select only two divisions it will show on the report like

SELECTED SECTION : CS, ENG

How to show the SELECT ALL on the drop down. For that you need to allow multiple values on parameter properties.

Ok, Let’s see how the selected multiple values will be passed to Stored Procedure and bring report data. A trick to solve that problem is to use a function and pass the parameter values to this function which return and set multiple data ready to query eg; DIVISION IN (‘cs’,’eng’)

In the function below split the drop down multiple values and return to my stored procedure. So I can query the division data according to the drop down selections.

How to set a date time parameter to show first day of the current month and other date time parameter to current date (today)

Start date shows as first day of current month

 

Write an expression to Default Value using Date Serial like below.

 

End date show as today

You can write an expression using Date & Time Common functions

 

 

SSRS deployment error : Query execution failed for dataset…..(reErrorExecutionCommand)



Have you ever come across  Query execution failed for dataset…..(reErrorExecutionCommand) when you try to deploy a report in SSRS reporting server and when you run it? This error throws mainly because the dataset are not correctly set up during deployment. Best way to troubleshoot this is to edit in Report Builder and run the report. You will get the same error message but this design view help to troubleshoot problem easier. Go to dataset and check whether the connection type has been set to SQL Server and the connection string is correct. Test Connection button is helpful to test the connection. This error is common if you use more than one datasource in same report and setting up datasources correctly will resolve this error.