February 28, 2014

Sql: Order by Email Domain Name

I was working on getting a report in Sql Server and it was actually simple report of a list email addresses and some and other data. Apparently, while sorting the report by email address, I realised that I need to sort by the domain name of the email address to get a better understanding. So, how can you sort the result by domain name of the email address. Here is how.

Sql Script
  1. SELECT EmailAddress
  2. FROM tblUser
  3. ORDER BY SUBSTRING(EmailAddress,(CHARINDEX('@',EmailAddress)+1),1)

February 27, 2014

To continue on from the previous post, in this post I will show how to create a relationship between two entities in the model. In the last post, I had an Employee entity created. In this one, I will create a new entity Department, update the Employee entity to have an association with Department.

Here is how the model looks like currently.

 image

Now, the Department entity is added in the same way Employee was added previously. The model looks like below now.

image

Now, to add the association, right click on Employee entity and choose Add New > Association.

image

An Add Association prompt like below will appear with most of the options correctly set.

image

Notice that Multiplicity has been incorrectly set in this scenario. I will invert the relationships so 1 department can have multiple employees. So , this will look like below.

image

Notice that there is an option to “Add Foreign key to the Employee Entity”. I have selected it clicked OK. The model is now updated but the foreign key is named as “DepartmentDepartmentId” like below. This happened as I renamed the original primary key from “Id” to “DepartmentId”.

image

I have renamed it to DepartmentId by clicking on it and editing it through the properties. My model is now ready and I clicked Validate like but it failed with the messages below.

image

This is because the new table does not exist yet. So, I will right click and choose generate database from model which will correct this issue as shown in the previous post.

That’s it, the association is now created and the model is good to go.

I usually create a Sql Server database using Sql Server Management Studio and use asp.net or mvc application to create a database connected application. Using Entity Framework’s model first approach, I can also visually create the model and then create a database using it. in this post, I will show how to create a data model using entity framework and then create a database using it.

I will use an empty asp.net application and add an entity data model to it. To do so, add a new file and choose Data > ADO.NET Entity Data Model .

image

I have renamed the model to "EmployeeModel and clicked OK. In the next screen, I chose Empty Model and clicked Finish.

image

Clicking Finish creates the EmployeeModel.edmx and EmployeeModel.Context.tt files in the solution explorer.

image

The designer should be opened by default and I will write click and Add a New  Entity.

image

In the next prompt, fill in the Entity name, and choose a unique id property which I have set as EmployeeId.

image

Clicking OK will generate the entity as below.

image

Next, I will add a few properties to this entity like FirstName, LastName, EmailAddress, DateJoined, IsActive and Salary.

To add the properties, right click on the Entity and choose Add New > Scalar Property

image

After adding the properties, set the properties of individual fields. Like FirstName, LastName, EmailAddress will be string, isActive will be Boolean, DateJoined will be DateTime and Salary will be double. Other properties like Nullable and default value can also be set. I have set 0 for the default value for Salary. Notice that the string fields have additional properties like Fixed Length and Max Length. I have set 50 to be the Max Length for all string properties.

For now, let’s assume that the model is ready. In future posts, (I will add more to it.).  Right click on the designer and click on Validate.

image

Notice that Validation will fail with one warning – Entity type ‘Employee’ is not mapped.

image

This is because the model is not mapped to any database. To create the database, right click on the model and choose “Generate database from model”. A prompt will open to choose a connection or to create a new one. In this situation, I have created a new connection to an Employee database previously created using Sql Server Management Studio. The script is generated and when I click on Finish, the database will be generated.

Generated sql script
  1.  
  2. -- --------------------------------------------------
  3. -- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
  4. -- --------------------------------------------------
  5. -- Date Created: 02/27/2014 15:33:14
  6. -- Generated from EDMX file: C:\Users\...\Documents\Visual Studio 2012\Projects\FormsTestApp\EntityPrac\EmployeeModel.edmx
  7. -- --------------------------------------------------
  8.  
  9. SET QUOTED_IDENTIFIER OFF;
  10. GO
  11. USE [Employee];
  12. GO
  13. IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
  14. GO
  15.  
  16. -- --------------------------------------------------
  17. -- Dropping existing FOREIGN KEY constraints
  18. -- --------------------------------------------------
  19.  
  20.  
  21. -- --------------------------------------------------
  22. -- Dropping existing tables
  23. -- --------------------------------------------------
  24.  
  25.  
  26. -- --------------------------------------------------
  27. -- Creating all tables
  28. -- --------------------------------------------------
  29.  
  30. -- Creating table 'Employees'
  31. CREATE TABLE [dbo].[Employees] (
  32.     [EmployeeId] int IDENTITY(1,1) NOT NULL,
  33.     [FirstName] nvarchar(50)  NOT NULL,
  34.     [LastName] nvarchar(50)  NOT NULL,
  35.     [EmailAddress] nvarchar(50)  NOT NULL,
  36.     [DateJoined] datetime  NOT NULL,
  37.     [Salary] float  NOT NULL,
  38.     [IsActive] bit  NOT NULL
  39. );
  40. GO
  41.  
  42. -- --------------------------------------------------
  43. -- Creating all PRIMARY KEY constraints
  44. -- --------------------------------------------------
  45.  
  46. -- Creating primary key on [EmployeeId] in table 'Employees'
  47. ALTER TABLE [dbo].[Employees]
  48. ADD CONSTRAINT [PK_Employees]
  49.     PRIMARY KEY CLUSTERED ([EmployeeId] ASC);
  50. GO
  51.  
  52. -- --------------------------------------------------
  53. -- Creating all FOREIGN KEY constraints
  54. -- --------------------------------------------------
  55.  
  56. -- --------------------------------------------------
  57. -- Script has ended
  58. -- --------------------------------------------------

 

The generated sql file is also saved in the project. This will create the store schema definition language (SSDL) and the mapping specification language (MSL) that is based on the model. Note that the database tables are not really added. The generated script will need to be run from with Sql Server Management Studio to create the tables to the database.

February 25, 2014

I am trying to create a new database and ended up receiving an error like “Directory lookup for the file … failed with the operating system error 2”. The following screen shot tells the full story.

image

With this kind of error, it normally happens when a path is specified on where the database will be created and in my situation I have specified the path but have not created the appropriate folders. Sql Server Management Studio does not automatically create the folders where the database will be created and this needs to be created manually by the administrator.

In the previous post, I showed how to create a new Sql Server database from backup, however, at times the following error might occur -

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'NewDB' database. (Microsoft.SqlServer.SmoExtended)

The above error really states that the new database is not the same the database in the backup. To avoid this error, I will, set couple more options in the Restore database dialog.

Click on Options on the left and select “Overwrite the existing database (WITH REPLACE)”.

image

And, in the Files option, I will set the data file folder and log file folder to the folder location that I used to create the database. By default, it will have the paths specified for the original backed up database.

image

Click on OK and continue to create the database as specified in the previous post on how to create a database from backup.

Usually, we take a backup of a database at regular time intervals and to be on the safe side that if something goes wrong, we have a point in time to go back to. However, we can also create a new database from the backup and this might be useful for debugging purposes. In this post, I will show how to create a new database from a backup of different database. 

To create a database from a backup follow the following steps.

Create a new database with the new name.

image

Click OK and this will create the database. Once created, right click on Databases and choose Restore Database. In the Source section, select Device, click on the … icon

image1

Click on Add and select the location and select the backup file.

image

Click OK twice and then select the destination. Select the NewDB that was created earlier.

image

Click on OK and the new database should be setup correctly.

So, I have got an asp.net application and I can see the file structure of the application using Visual Studio or by navigating to the folder and viewing the directories but how can I view it on the server's end. This is not particularly useful and I don't recommend having this on the server but it might be useful for debugging specially when you don't have full control on deployment.

Since, I am getting the file structure of an asp.net application, I will use a web control for displaying the file structure. Because of the type of data I am expecting to get, a TreeView control is the perfect control to bind this data to. The TreeView control is instantiated like below.

<asp:TreeView ID="tview" runat="server"></asp:TreeView>

 

Now, I will need to get the list of all directories and files and bind to this TreeView. To start with, I will need to get the root application folder as discussed in my previous post – how to get root application path.

Next, I have written a method that will recursively go through all the directories and add the file names and directory names with the TreeView. And finally, from Page_Load event, I have added the nodes to the TreeView. Here is the code for how it works.

Create Nodes
  1. protected void Page_Load(object sender, EventArgs e)
  2. {
  3.     tview.Nodes.Clear();
  4.     tview.Nodes.Add(CreateNodes(new DirectoryInfo(RootDirectory())));
  5. }
  6.  
  7. //return root directory
  8. private string RootDirectory()
  9. {
  10.     return Server.MapPath("~");
  11. }
  12.  
  13. //recursively bind TreeView to directory structure
  14. private TreeNode CreateNodes(DirectoryInfo di)
  15. {
  16.     var dirNode = new TreeNode(di.Name);
  17.  
  18.     foreach (var dir in di.GetDirectories())
  19.         dirNode.ChildNodes.Add(CreateNodes(dir));
  20.     foreach (var file in di.GetFiles())
  21.         dirNode.ChildNodes.Add(new TreeNode(file.Name));
  22.     return dirNode;
  23. }

 

Once the page is run, the following screen shot is displayed for my application.

image

An asp.net application can be installed almost anywhere in windows server but is usually installed in C:\inetpub folder. So, how can I get the directory where the application is installed.

So, I have got a Windows Server setup in Windows Azure and I want to create more of these with the same settings. For example, in the initial Windows Server, I already have configured IIS and Sql Server and firewall and now I want create more servers with the same configuration. To achieve this in Windows Azure, I will need to basically create an image of the existing server and then use that image to create more servers. So, what is an image – an image is a template of the server that is currently running and is a virtual hard disk that can be later used to create more windows servers.

To create an image of the server, I will log on to the server and then run sysprep command with administrative privilege within command prompt. To run sysprep, change current folder to %windir%\system32\sysprep and run sysprep.exe .

The System Preparation Tool options will appear. Choose “Entire System Out of Box Experience” in “System Cleanup Action” and select “Generalize”. In the “Shutdown Options” select “Shutdown”. The screen shot for this is below.

image

Click OK. Sysprep shuts down the virtual machine. At this stage, I cannot connect to the virtual machine using remote desktop which is the right behavior. However, the machine appears to be running from the Windows Azure portal. This is a bug in Windows Azure portal and at this stage I cannot capture the image of the virtual machine.

So, I chose the shut down option for the virtual machine and then clicked on the Capture button in the portal.

image

While clicking Capture a popup will appear suggesting that the currently running virtual machine will be deleted and I need to accept that before continuing. This, to me, is a poor experience as I don’t necessarily want to kill the virtual machine and this works better in AWS. In next step, I got a prompt to name the image name to continue. This will create the image for the current virtual machine.

Cool, if you have any better ways of doing it, please comment on this post.

February 23, 2014

The Sql Server has been setup and from the Services panel and Sql Server Configuration panel, I can see that it’s up and running but I cannot connect to it from asp.net solution. At this stage, I would verify if Sql Server is indeed running by logging into the server using Sql Server Management Studio from within the server. In this situation, it will connect from within the server but not when connected from outside – that is, from other servers.

The above issue can happen remote connections are not allowed in the Sql Server. To enable remote connections, open Sql Server Management Studio and connect to the Sql Server. Right click on the server name and click on “Properties”. This will open a up dialog like this.

image

Click on Connections from the left hand panel which will open a dialog like this.

image

Select the “Allow remote connections to the server” option and and click OK.

Now connect to the Sql Server from a remote Sql Server Management Studio or an asp.net solution and it should work.

February 22, 2014

Has this ever happened to you that you have setup Sql Server (any version) and it seems to be up and running but I cannot access it from Sql Server Management Studio. To start with, how do you know it's up an running - I usually check if the  Sql Server related services are up and running from Administrative Tools > Services section.

Once confirmed I connect from SSMS in the same server to the Sql Server but it does not work. At this stage, we need to check if firewall settings allow SSMS to access the sql server. Open firewall settings from Administrative Tools > Windows Firewall and Advanced Security and a new inbound rule to allow the SSMS program to access the sql server like below.

image

In the programs section, browse to the location where SSMS is installed. This setting will allow SSMS to access Sql Server.

February 21, 2014

I have setup Sql Server Express a number of times in Windows Azure within a VM and every time I have had some trouble setting it up so I could connect to it. Today, when I tried to set the sql server up, I received the error
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
which I have received almost every time. As this does take up a lot of time to figure out the issue, this is how I have gone about it.

February 20, 2014

Few days back I was working with our IT team to do an audit on the Sql Servers and to get a list of how many is installed. Later, I realised that I can also do so using SQLCMD command with in the command prompt. Here is how you do it.

Open Command Prompt and type in SQLCMD -L. This will list the servers.

image

Alternatively and even better we can output the list to a text file by using the following command.

SQLCMD -L  > c:\testdata\sqlserver.txt

This will save the text file – sqlserver.txt in C:\testdata

Binding ListView to a CSV

How often do we work with csv files? I am sure even with databases, xml and web services, csv files are still used in a great number of applications. In this post, I will show how we can bind a csv to a web control like ListView and GridView.

The main challenge to bind data from csv is to get the data in a reasonable format like a DataTable. To do so, we can use OleDB to connect and query the csv and populate a DataTable and then bind the GridView and the ListView to the data table.

February 15, 2014

How many of the web developers have heard this before that the site runs fine in localhost and development environment but is slow in production? I am sure many of us have had this issue in the past and have worked it out after few trials and errors. The following are the few components to consider to solve the issue.

Scenario 1: Many users have issues with many pages

Server issue:

This is a big problem as it means the site is not usable. I would start by looking into the server usage - CPU and memory. If the site is receiving lots of requests then this could cause the CPU and memory usage it spike up. Check for the server specs - how much CPU and memory it has. One the web servers I worked for had 2 GB ram when my laptop had 4 GB. The server definitely needs to be upgraded with better specs.

This would ease the problem but may not solve the issue totally. If you are receiving way more requests than the server can handle, you need to think about multiple server load balancing. Windows Azure can help you do that - http://www.windowsazure.com/en-us/documentation/articles/load-balance-virtual-machines/ .

Database issue:

Also, if the site is database driven, check if that is causing the issue. There might be lots of database calls in individual pages or maybe the master page has few database calls which is slowing the site down. You might be logging each of the requests, etc to the database which is calling the database too many times. Monitor your database activity when a page is requested and compare.

Another way to check if database is slowing down the site is to create html pages with same content and to note the loading time differences between the 2 page.

If you conclude that it is a database related issue, consider caching your content where possible and only grab the data that is needed instead of a random set of data. For example,

  1. SELECT * FROM Customer -- bad
  2. SELECT TOP 10 * FROM Customer --good
  3. SELECT * FROM Customer WHERE criteria IN (1,2) -- good

In case, it is a database related issue but the application is not really doing any strange queries, it could be the database server that is overloaded. In many cases, the same database server is used to host multiple database instances and chances are another database instance is taking all the resources in the server. In this situation, you need to figure out our hosting situation.

Bandwidth issue:

The speed of a web page is dependent on the size of the data that is passed on. Check if the page got huge video files or maybe it has a large number of css, javascript and images files that is taking a lot of time to download. In Google Chrome, you can right click on a page, inspect an element and look into the network tab to check the file sizes and also the number of files served/ To avoid multiple css, javascript files you can use asp.net bundling and minification - http://www.asp.net/mvc/tutorials/mvc-4/bundling-and-minification - to reduce the number of files to 1 which will improve the download time. In addition, CDNs can also be used to used to serve certain files like jquery script or some other standard script. Using cdn, there is a risk that the cdn server might be down but the chances are not really so high.

To improve bandwidth issue, you can cache content on the client as necessary. For example, the scripts, css and images can be cached in the client browser but there is no benefit to first time users. We can cache content by using the cache control for static content in IIS7 or the system.webServer property in web.config file.

 

HTML structure:

If the HTML file served to the client is large, then HTML minification is another component look into. You should also reduce the spaces and new lines when possible from the controls. For example, if you have the following snippets in a ListView,

  1. <ItemTemplate>
  2.     <li>
  3.         <%#Eval("data") %>
  4.     </li>
  5. </ItemTemplate>


it will generate 3 new lines for every record returned. So, in case you generated 100 records - this will result in 300 extra lines of html. Instead of above, use,

  1. <ItemTemplate><li><%#Eval("data") %></li></ItemTemplate>


The images and css and javascripts should have the correct sources specified. With incorrect or empty sources an unnecessary request will be made to the server and waste bandwidth and server compute.

Also, put the css files at the top within the head section and scripts at the bottom of the page before the body end tag. Putting css at top allows the page to render progressively and putting scripts to the bottom avoid blocking other content downloads.


Static sub-domain for static resources:

Cookies are sent across the network for every requests browser makes. For example, in a request to www.example.com, the browser will send request to get each of the images, css, javascripts and any other static resources and any cookies saved from the domain will be sent each time. To improve the scenario, send the static resources using a sub domain like static.example.com and make sure there are no cookies associated with this sub domain.
 

JavaScript issue:

Sometimes, pages will run slow if we have too many javascripts. Nowadays, we use a number of javascripts by default, for example, scripts for analytics and tracking, jquery, mooTools, modernizr, social sharing, etc. We also have custom scripts for generating items like menu or enforcing compatibility and so on. Most of these will be harmless but when you have a number of these, it will slow down your site. For example, if you run functions from body onload or window.onload - these will impact the page's loading.

Large number of javascripts from domains also have a negative impact as the browser needs to looks multiple DNS. Each DNS lookup takes up 20-120 milli seconds, so 10 DNS lookups will cost approximately half a second.

Also, make sure the same JavaScript resource is not called multiple times. This can happen when a page is created with a master page and both master and child page is called the JavaScript. The page will still work and it's a waste of bandwidth and slow down the site a bit.

 

Avoid redirect:

Few years back, while working on a site, it was recommended to have all urls in lowercase and submitting from one domain for SEO reasons. In my situation, the site was configured to serve from both example.com and www.example.com and the same link was created in many different way like example.com/abc , example.com/Abc , www.example.com/abc . Even though these are referring to the same page, it's a bad practice to have multiple urls to the same page. The site was also hosted in IIS 6 and to overcome this I used isapi rewrite tool to redirect pages to one url format. Though it solved the url formatting issue, the extra redirects slowed down the pages.


Scenario 2: One user having issues with speed of the site

If you have more than one user, you can be more or less certain that the issue is at the client end. To start with, check where the user is located. For example, you might be hosting the site in US whereas the user is visiting it from Australia. The site will certainly be slower due to network latency. You can use the site - http://www.webpagetest.org/ - to get some idea on how the site performs in various geographical locations.

Secondly, it could also be the user has a proxy setup which blocks certain files, sites, port number etc. For example, you might be serving an image, javascript file from a different location which is blocked by the user's proxy. Alternatively, you might be serving the site using a non 80 port number which is blocked by the client's proxy. If that's the case, the user needs to communicate with their internal support team.

February 7, 2014

There are few ways we can get the size of a Sql Server database. There are built in stored procedures and we can also write our own and even use the sql server management studio to get the size. In this scenario, I am using the AdventureWorks database.

sp_spaceused

The stored procedure "sp_spaceused" returns the database size, index size, data size and unused space of the database. We can use it in the following way.

  1. USE [AdventureWorks2012]
  2. exec sp_spaceused;

 

image

sp_helpdb

The stored procedure "sp_helpdb" returns the size of the database, name, dbid, filename, data size, log size and few other metadata. It can be used like below.

  1. sp_helpdb 'AdventureWorks2012';

 

image

 

Custom Script

We can write a custom script to query from sysfiles to get the size of the database. The script and the result it produces is below.

  1. USE [AdventureWorks2012]
  2. GO
  3.  
  4. SELECT name, [size] * 8 / 1024 as [size MB]
  5. FROM sysfiles;


image

SSMS

Lastly, and most easily, we can get the size from the properties of the database. Right click on the database name in object explorer, click on properties and then choose Files from "Select a page".

image

February 6, 2014

incorrect syntax near ':' in SQL Server

I created a project using Sql Server Database template in Visual and generated a changed script from it. The script looked like below.

Sql
  1. :setvar DatabaseName "AdventureWorks2012"

However, when I ran the script, it did not validate and produced the error message: 

Error Message
  1. Msg 102, Level 15, State 1, Line 1
  2. Incorrect syntax near ':'.
Reference: Shahed Kazi at AspNetify.com