Author: admin

Fix xmlexception rshostingservice.exe ssrs 2017

If your SSRS does not start and worst unable to completely start with the following exception thrown at event viewer

Application: RSHostingService.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: System.Xml.XmlException
at System.Xml.XmlTextReaderImpl.Throw(System.Exception)
at System.Xml.XmlTextReaderImpl.ParseRootLevelWhitespace()
at System.Xml.XmlTextReaderImpl.ParseDocumentContent()
at System.Xml.XmlReader.MoveToContent()
at System.Xml.Linq.XElement.Load(System.Xml.XmlReader, System.Xml.Linq.LoadOptions)
at System.Xml.Linq.XElement.Load(System.String, System.Xml.Linq.LoadOptions)
at Microsoft.BIServer.Configuration.ConfigReader..ctor(System.String)
at Microsoft.BIServer.BIService.ServiceConfig..ctor(System.String)
at Microsoft.BIServer.BIService.BIService.SetNewConfigFilePathIfExists(System.String)
at Microsoft.BIServer.BIService.BIService..ctor()
at Microsoft.BIServer.BIService.Program.Main()

 

Also tried to repair the installation and the process ALWAYS fails.

My solution here was to delete the SSRS folder at the default installation folder of

C:\Program Files\Microsoft SQL Server Reporting Services

After that reinstall the ssrs.

Register the old reportserver database in the report server configuration.

Happy Coding!

My stored procedure parameter is DateTime2 but I keep on getting SqlDateTime overflow. Microsoft.ApplicationBlocks.Data.SqlHelper

If you keep on getting this even if your stored procedure parameter is DateTime2. The error is:

Error – SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

The solution is to pass an SqlParameter object with the DateTime2 SqlDBType specified. Before I used to pass parameters of System.Object type and let the CLR and SQL Server figure out the proper type between DateTime and DateTime2.

Please look at the code for before and after. Also focus on the DocumentDate, StartDate and EndDate parameters.

BEFORE – used parameters as System.Object

object retVal = SqlHelper.ExecuteScalar(
                    Transaction,
                    _commandInsert,
                    data.ItemID,
                    data.SequenceNumber,
                    data.ReferenceNumber,
                    data.DocumentDate,
                    data.StartDate,
                    data.EndDate,
                    data.Spoilage,
                    data.Remarks,
                    data.NotedByUserID,
                    data.QualityCheckUserID,
                    data.ReturnedByUserID,
                    data.CreatedBy,
                    data.UpdatedBy,
                    data.EstimatedQuantity,
                    data.ActualQuantity,
                    data.RawMaterial,
                    data.DirectLabor,
                    data.MachineOverhead,
                    data.ApproveMaterialsByUserID,
                    (byte)data.Status,
                    data.WIPItemID,
                    data.WIPReportID);

AFTER and the CORRECT way – passed parameters as SqlParameter, also specifying explicitly SqlDBType DateTime2

SqlParameter paramDocumentDate = new SqlParameter("@DocumentDate", SqlDbType.DateTime2); paramDocumentDate.Direction = ParameterDirection.Input;
            SqlParameter paramStartDate = new SqlParameter("@StartDate", SqlDbType.DateTime2); paramStartDate.Direction = ParameterDirection.Input;
            SqlParameter paramEndDate = new SqlParameter("@EndDate", SqlDbType.DateTime2); paramEndDate.Direction = ParameterDirection.Input;
 
            paramDocumentDate.Value = data.DocumentDate;
            paramStartDate.Value = data.StartDate;
            paramEndDate.Value = data.EndDate;
 
            if(WithTransaction)
            {
                SqlHelper.ExecuteNonQuery
                (
                    Transaction,
                    CommandType.StoredProcedure,
                    _commandInsert,
                    paramID,
                    new SqlParameter("@ItemID", data.ItemID),
                    new SqlParameter("@SequenceNumber", data.SequenceNumber),
                    new SqlParameter("@ReferenceNumber", data.ReferenceNumber),
                    paramDocumentDate,
                    paramStartDate,
                    paramEndDate,
                    new SqlParameter("@Spoilage", data.Spoilage),
                    new SqlParameter("@Remarks", data.Remarks),
                    new SqlParameter("@NotedByUserID", data.NotedByUserID),
                    new SqlParameter("@QualityCheckUserID", data.QualityCheckUserID),
                    new SqlParameter("@ReturnedByUserID", data.ReturnedByUserID),
                    new SqlParameter("@CreatedBy", data.CreatedBy),
                    new SqlParameter("@UpdatedBy", data.UpdatedBy),
                    new SqlParameter("@EstimatedWeight", data.EstimatedWeight),
                    new SqlParameter("@ActualWeight", data.ActualWeight),
                    new SqlParameter("@ActualQuantity", data.ActualQuantity),
                    new SqlParameter("@RawMaterial", data.RawMaterial),
                    new SqlParameter("@DirectLabor", data.DirectLabor),
                    new SqlParameter("@ManufacturingOverhead", data.ManufacturingOverhead),
                    new SqlParameter("@ApproveMaterialsByUserID", data.ApproveMaterialsByUserID),
                    new SqlParameter("@Status", (byte)data.Status),
                    new SqlParameter("@WIPItemID", data.WIPItemID),
                    new SqlParameter("@WIPReportID", data.WIPReportID),
                    paramRV
                );
            }

HAPPY CODING!!!

Passed another Microsoft Exam! 70-761 Querying with Transact-SQL.

This exam was way too convenient compared to the other microsoft exam I took at a test center in Manila. This time it was an online proctored exam.

When I initiated the exam the proctor (from pearson) called me via VOIP and he sounded Indian. There was some SOP and everything he asked was easy to do.

The exam comprises of 48 questions and I got a score of 888 over 1000. The passing score is 700.

This is the tally per sql topic.

My company paid for the exam. I am happy I passed it.

I read the microsoft exam tutorial book by Itzik Ben Gan. I read it twice and I practiced on hand when in mood. I bought it as an ebook at microsoftpressstore.com.

Solid State Drive Upgrade

I wouldn’t need to spend my own savings for an upgrade that would be implemented on a company’s laptop but since I regularly spend at least 8 hours a day(even weekends) with it, I had to make a bold move.

Visual Studio 2013 is a pain on both loading forms with heavy number of controls and compiling.

I upgraded from a 960GB 5400 rpm stock harddrive to SKS NX200 SATA 480GBSSD. These are the numbers from crystaldiskmark.

HDD

SSD

Money spent is worth it.

SSRS Reportviewer – The underlying connection was closed: A connection that was expected to be kept alive was closed by the server.

Our client is complaining about a report viewer issue happening only to a single computer. When the c# winforms application tries to open a form class that happens to have a reportviewer object in it, it hangs the whole application and eventually throws an exception.

There is nothing unusual with the SSRS server since it is hosted on a on-premise SQL Server and I happen to know that it is not heavily used.

The c# is throwing an  exception of
The underlying connection was closed: A connection that was expected to be kept alive was closed by the server.

with an inner exception of

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.

The stack trace for both respectively,

20180104 04:10:34.062
Computer Name: YOG
CFS Version: 2.0.4.9
The underlying connection was closed: A connection that was expected to be kept alive was closed by the server.
   at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)
   at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request)
   at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.GetWebResponse(WebRequest request)
   at Microsoft.Reporting.WinForms.ServerReportSoapProxy.GetWebResponse(WebRequest request)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
   at Microsoft.SqlServer.ReportingServices2005.Execution.ReportExecutionService.LoadReport(String Report, String HistoryID)
   at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.LoadReport(String Report, String HistoryID)
   at Microsoft.Reporting.WinForms.ServerReport.GetExecutionInfo()
   at Microsoft.Reporting.WinForms.ServerReport.SetParameters(IEnumerable`1 parameters)
   at Accounting.Reports.UI.FormReportPreview.LoadReport()
   at Accounting.UI.FormBase.PreLoadReportComponents()
————————————–
————————————–
20180104 04:10:34.062
Computer Name: YOG
CFS Version: 2.0.4.9
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   at System.Net.PooledStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   at System.Net.Connection.SyncRead(HttpWebRequest request, Boolean userRetrievedStream, Boolean probeRead)
————————————–

 

Hours of research and trial and error led me to this fix which you only need to set this field upon runtime.

System.Net.ServicePointManager.Expect100Continue = false;

A better code is to add additional settings at your application config. This is better because it turned out to be a surgical strike and I never needed to commit unnecessary code to our codebase. The code was applied only to a specific computer.

Just put the code inside the configuration node like this

<configuration>
  <system.net>
    <settings>
      <servicePointManager expect100Continue="false"/>
    </settings>
  </system.net>
</configuration>

Happy Coding!

Two extended dual monitors for business laptops

I have just ported our Accounting Suite project from Visual Studio 2008 R2 to Visual 2013. The latter supports “New Window” for opened .cs files.

Also current GUIs of SQL Server allows you to move your query tabs to a different screen.

More screens will maximize the benefits of this features. I have only one screen output connection.
Yesterday I just bought a VGA to USB 2.0 connection adapter.

Tested it right away with my extra monitor and it worked. The next day I bought a new monitor.

Cons : The screen’s resolution cannot be configured. It is stuck to 800×600.

Play safe. Neutral literal date format for multi language property of SQL Server Login

Lesson learned. My usual date time literal:

YYYY-MM-DD HH:MM:SS.nnn

happens to be a bad practice since it is interpreted differently by the language property of the current login.

This sample code shows how it interprets a date with poor format by different languages.

DECLARE @datePoorFormat AS VARCHAR(23);
SET @datePoorFormat = ‘2017-02-12 23:12:12.123’;

DECLARE @DateTable AS TABLE (temp1 DATETIME)

SET LANGUAGE us_english;
INSERT INTO @DateTable(temp1) VALUES (@datePoorFormat);

SET LANGUAGE british
INSERT INTO @DateTable(temp1) VALUES (@datePoorFormat);

SELECT temp1 FROM @DateTable

The result of the query is

2017-02-12 23:12:12.123 — Feb 12
2017-12-02 23:12:12.123 — Dec 02

Please use the neutral formats. For DATETIME type these are:

‘YYYYMMDDHH:MM:SS.nnn’
‘YYYY-MM-DDTHH:MM:SS.nnn’
‘YYYYMMDD’
I like the second one because hyphens emphasizes date parts better in your code.
BTW language config is set by default at your current login. The picture below shows where you can set it via GUI.

Microsoft exam passed at first attempt. 70-483 Programming in C#

I made sure internet connection is not a problem. I had to set the venue at a test center in Manila. I only had 2 hours of sleep and I was inside the bus on or before 3AM. 9AM is the exam time and I was already at the vicinity 2 hours before. I looked for a place where I can eat, sit and empty my mind.

The test comprises of 50 questions with a passing score of 700. My score is 774. If you are weak at application debugging and applying security then you need to get high scores at program flow, create and use types and etc. That is the same case as mine.

When taking the exam push yourself but take your time. Read thoroughly.

Here is how I prepared.

  1. Read MCSD Certification toolkit Exam 70-483 Programming in C# – WROX (at least 3x)
  2. Read Programming in C# 70-483 Microsoft Publication (at least 2x)
  3. Measure-up official practice exam – downloadable with 30 day free online test. Make sure you are getting 90% something percentile consistently.
  4. MSDN
  5. When in the mood practice on hand.

Backyard farming, tomatoes, eggplants, squash and sweet potatoes.

This is my stress reliever. I really plan a self sustaining environment and now is a good time to practice that.

In red containers are tomatoes, I might replace them with another seed type from RAMGO because the leaves and stems at the bottom easily rot. The plants to the left of the tomatoes are sweet potatoes and I apologize for the small picture.

 

These pictures are the close-up of my best tomatoes. Not happy with the seed type.

 

 

 

Eggplant starting from seeds is the left picture. The right picture is what you’ll expect from the seeds in 1 and 1/2 months. Still not producing but I really like the seed type, it is still RAMGO brand.

 

Squash. Flower of the squash has been harvested and it has been served through pinakbet(Filipino viand). We are still waiting for the squash itself.

 

🙂