European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

European Windows 2008 Hosting :: How to Solve Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

clock December 20, 2011 06:02 by author Scott

The Scenario:

Sometimes you may get a timeout issue looking something like this:

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()

The important part here is what is in the exception message:


System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

This may give you the impression that the server is down or something similar.

However, this is basically the SqlCommand.CommandTimeout property that has expired; the default timeout is 30 seconds.

See more at:

".NET Framework Class Library -> SqlCommand.CommandTimeout Property"

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

Now, why would it time out?

There are 2 common reasons.

Long running tasks or uncommitted transactions. Let's show this by example.

In the first example, we emulate that the command execution takes a very long time to execute and return, for example there could be millions of rows being updated or for some other reason the execution takes a long time.

In the code I just call the SQL Server method "waitfor delay" that will pause the execution in SQL Server for 30 seconds, I then change the SqlCommand.CommandTimeout  from 30 seconds to 10 seconds so that we do not have to sit all day and wait for the exception. The code should be pretty self explanatory, just create a console application in Visual Studio.

Note that we connect to the trusty Northwind, if we would set the CommandTimeout to 60 seconds, then after 30 seconds we would get our Shippers table data back.

        static void Main(string[] args)
        {
            string cString = @"Data source=<your server>;Integrated Security=SSPI;Initial Catalog=Northwind";
            using (SqlConnection sc = new SqlConnection(cString))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand("waitfor delay '00:00:30';select * from Shippers", sc);
                    cmd.CommandTimeout = 10;
                    Console.WriteLine("CommandTimeout: {0}", cmd.CommandTimeout);
                    sc.Open();
                    SqlDataReader r = cmd.ExecuteReader();
                    while (r.Read())
                        Console.WriteLine("{0} : {1}", r[0].ToString(), r[1].ToString());
                    sc.Close();
                }
                catch (SqlException se)
                {
                    Console.WriteLine(se);
                }
            }
        }

Run it, and after 10 seconds you will get the exception:

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   ...

For the second reason, that there might be an uncommitted transaction, again we will use Northwind running the following the code, almost the same as above (only difference is that there is no call to “waitfor delay” in the SQL):

        static void Main(string[] args)
        {
            string cString = @"Data source=<your server>;Integrated Security=SSPI;Initial Catalog=Northwind";
            using (SqlConnection sc = new SqlConnection(cString))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand("select * from Shippers", sc);
                    cmd.CommandTimeout = 10;
                    Console.WriteLine("CommandTimeout: {0}", cmd.CommandTimeout);
                    sc.Open();
                    SqlDataReader r = cmd.ExecuteReader();
                    while (r.Read())
                        Console.WriteLine("{0} : {1}", r[0].ToString(), r[1].ToString());
                    sc.Close();
                }
                catch (SqlException se)
                {
                    Console.WriteLine(se);
                }
            }
        }

Run this code and you should get the rows in the Shippers table returned.

Now, open Query Analyzer or Sql Server Management Studio and execute an uncommitted transaction on the Shippers table, like so:

use Northwind
go

begin tran
 update Shippers set CompanyName = 'aaaaa' where ShipperID = 1
--commit

(Note that the new value, in this case 'aaaaa' must be different compared to the existing one in order to see the problem)

Rerun the code above, and after 10 seconds you will, again, get the exception:

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   ...

Go back to Query Analyzer or Sql Server Management Studio and commit the transaction, rerun code, and you will once again get the Shippers table data returned.

So to summarize, if the command you are executing is a long running one, adjust the CommandTimeout accordingly. If there are uncommitted transactions, you need to find what and where they are and change your code or your stored procedures accordingly.This is outside the scope of this blog, but one way to check for uncommitted transactions is to from QA or SSMS run the following:

dbcc opentran ('Northwind')

This will show if there are any blocked spids in the Northwind database which could be an indication of uncommitted transactions and queries that are blocked as a result of this.

It may seem obvious that a command times out if the command timeout expires. The background for this post is that I had a case where occasionally my customers’ users could not log in to their system. There was no clear pattern to this, and what happened when they tried to log in was that they got the exception above.  In the end it turned out that they had a page in the application that allowed the user to change employee information.

The problem was that when they made the change, they opened a transaction, however, they did not commit it until the user pressed a Save button. The interval between starting the edit of the employee information and the saving of it could be anything, either they made the change and saved immediately, or they made the change and went to lunch without saving.

During this time, all the logins would fail since the login functionality basically did a select from the employee table with the users’ login and password, which subsequently failed since the table was locked by the uncommitted transaction.

Once this was figured out, and the changes were made, all was well in login land.



HostForLife.EU now supports Web Deploy 2.0 Hosting

clock December 15, 2011 10:24 by author Scott

HostForLIFE.EU has supported Web Deploy technique on all our hosting plans. With a web deploy technique, a customer can efficiently synchronize sites, applications or servers across your IIS 7.0 server farm by detecting differences between the source and destination content and transferring only those changes which need synchronization.

What is Web Deploy 2.0?

Web Deploy (Web Deployment Tool) simplifies the migration, management and deployment of IIS Web servers, Web applications and Web sites. Administrators can use command-line scripting with Web Deploy to synchronize IIS 6.0 and IIS 7.0 servers or to migrate an IIS 6.0 server to IIS 7.0. Web Deploy Tool also enables administrators and delegated users to use IIS Manager to deploy ASP.NET and PHP applications to an IIS 7.0 server. 


Web Deploy 2.0 Features             

·    Seamless integration with IIS 7.0 Manager and Visual Studio 2010 interface for creating packages and deploying them onto a machine, both locally and remotely

·    Seamless integration with the Web Platform Installer to install community web applications simply and easily.

·    Web application packaging

·    Web application deployment

·    Web server migration and synchronization

·    In addition to the IIS Manager and Visual Studio 10, tasks can be performed using the command-line or public APIs.



European WebMatrix Hosting :: How to Deploy WebMatrix Hosting

clock December 9, 2011 06:41 by author Scott

WebMatrix is a free tool from Microsoft that makes the task of creating, editing and publishing your website easy. It allows you to intelligently publish only the files that have changed locally, automatically configure your server for maximum compatibility, and keep your development environment in sync with the live published version of your site.

In order to build sites using WebMatrix, you'll first need to install it on your local computer or development machine.  
Click here for the download.

In order to publish sites to our servers using WebMatrix, you'll need to be sure your plan supports it. Currently all ASP.NET plans hosted on Windows 2008/IIS7 and ColdFusion 9 plans support WebMatrix.

Once you're ready to publish your site onto our live servers, you'll need to configure your Publish Settings.

1. First, be sure you're on the Site view in WebMatrix.



2. Go to the Publish drop down, under the Home tab, then select Settings.



3. From Publish Settings, select the Web Deploy protocol from the drop down (alternately, you can use FTP).

4. For Server, enter in your site's IP address.

5. For User Name, enter a valid Site User set up on your account.  If you recently ordered your site, this would be the username you set up during signup.

6. For Password, enter in the password associated with your Site Username.

7. For Site Name, enter in your domain name without the extension (e.g. .com, .net, .org, etc.), and the words "web site" after it.  For example, if your domain was "domain.com", you would enter "domain web site" in the Site Name field.  The site name is based on how your site's name is entered in IIS, and this is the naming convention we use to identify your site on the server.

8. For Destination URL, enter in the URL for the web site/application you're publishing.



9. To ensure your Web Deploy settings in WebMatrix is correct or working correctly, click Validate Connection.

WebMatrix is ready.

10. To create a Database connection, be sure your in the Databases view.

11. Under the Home tab, select New Connection.



To connect to a database, you'll need to:

1. Enter in your database name

2. The database type.

3. The server location. 

4. And then your database Login and Password.



Database Connection Strings

SQL Server Database
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

MySQL Database
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;



SQL 2008 Europe Hosting :: How to Send Automated Job Email Notifications in SQL Server with SMTP

clock December 7, 2011 09:53 by author Scott

When you have automated backup jobs running on your database server, sometimes you forget that they are even running. Then you forget to check to see if they are running successfully, and don’t realize until your database crashes and you can’t restore it since you don’t have a current backup.

That’s where email notifications come in, so you can see the job status every morning when you are sipping your coffee and pretending you are working.

SQL Server provides a built-in method of sending emails, but unfortunately it requires you to have Outlook and a profile installed on the server, which isn’t necessarily the ideal way to send an email. Thankfully there is another method, that involves installing a stored procedure on your server that will allow you to send email via SMTP.

You will want to edit one line in the stored procedure to put the IP address of your SMTP server:

EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields(“http://schemas.microsoft.com/cdo/configuration/smtpserver”).Value’, ’10.1.1.10′

Install the stored procedure into the master database, so it can be easily used from wherever needed.

Open up the SQL Server Agent \ Jobs list, and select the properties for the job you are trying to create a notification for:



Click on the Steps tab, and you should see a screen that looks like this:



Click the New button to create a new job step. We will use this step to send the email notification on success.

Step Name: Email Notification Success

Enter this SQL into the Command window as seen below. You will want to customize the email addresses and message subject to match your environment:

exec master.dbo.sp_SQLNotify ‘[email protected]’,'[email protected]’,'Backup Job Success’,'The Backup Job completed successfully’



Click OK and then click the New button again to create another step. This will be the failure notification step.

Step Name: Email Notification Failure

SQL:

exec master.dbo.sp_SQLNotify ‘[email protected]’,'[email protected]’,'Backup Job Failure,’The Backup Job failed’

Now the idea is to make the items follow a specific workflow. First click Edit on step 1, and set the properties as shown here:



What we are saying is that on success, go to the success step, and on failure, go to the failure step. Pretty simple stuff.

Now edit the second step, the one labled “Email Notification Success”, and set the properties as seen here:



We are saying that if the notification job is successful, then just quit the job without running step 3. If we don’t specify this, then we will end up getting two emails, one with success and one with failure.

Now edit the third step, the one labled “Email notification failure”, and set the properties as seen here:



Now your job steps should look like this:



You should now have email notifications in your inbox for either success or failure.



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in