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.