Monday, 31 December 2012

How to get size of the database in SQL / SQL Azure?

Some of the case database exceeds the db size and got error while trying to insert new row into the db. Most of the case this will happen after some years of using database. So we cannot identify the issue very quickly and may be our application need to shut down for some days. To monitoring this issue we can create a user interface (if we needed) which showing currently used size of the database.

Query for getting size of the database in SQL / SQL Azure
To get the table wise size we can use following query, which returns the name of the tables with size of the table.
select    
      sys.objects.name, sum(reserved_page_count) * 8.0 / 1024 as size_in_MB 
from    
      sys.dm_db_partition_stats, sys.objects 
where    
      sys.dm_db_partition_stats.object_id = sys.objects.object_id
group by sys.objects.name
To get the entire database used size we can use following query.
select
      sum(reserved_page_count) * 8.0 / 1024 as size_in_MB
from
      sys.dm_db_partition_stats
GO

Friday, 23 November 2012

Copy tables from one database to another in SQL Server


You can  use the Generate SQL Server Scripts Wizard to help guide the creation of SQL script's that can do the following:
  • copy the table schema
  • any constraints (identity, default values, etc)
  • data within the table
  • and many other options if needed
Good example workflow for SQL Server 2008 with screen shots shown here.

Right Click on Database >> Tasks >> Generate Scripts >>
This will pop up Generate SQL Server Scripts Wizards >> Click on Next >> Select Database >> This will bring up a screen that will suggest to Choose Script Option.
On Choose Script Option Screen of Script Wizard under section Table/View Options Look at Script Data row and Turn the Option to True.
The Next Screen will ask about object types. Select all the objects that are required in the generated script. Depending on the previous screen it will show few more screen requesting details about the objects required in script generation.
On the very last screen it will request output options. Select the desired output options of Script to file, Script to Clipboard or Script New Query Window.3
Clicking on Finish button will generate a review screen containing the required objects along with script generating data.
Clicking on Finish button one more time will generate the requested output.
Similarly, if you want to generate data for only one table, you can right click on the table and follow almost a similar wizard. I am sure this neat feature will help everybody who has been requesting for it for a long time.

Tuesday, 6 November 2012

How to implement SQL Bulk Copy in SQL Server

SqlBulkCopy for insert multiple rows of data into table in SQL Server
Sometimes we need to insert bulk amount of data into a table in SQL. We can insert bulk data to SQL in different methods. Here we are going to demonstrate how to insert large amount of data using SQL Bulk Copy method.
Example of SQL BulkCopy method
Here we are going to insert multiple rows of sample data into Employee table. First of all create a table in SQL named Employee. After that  create sample data for employee table and finally this multiple rows of employee data insert into table using SQL bulk copy method.
 
Create a Table in SQL 
CREATE TABLE Employee
(
ID INT,
EmpName varchar(200),
Department varchar(200),
DOB DateTime
);

SQLMulkCopy Example in SQL Server
SQLMulkCopy Example in SQL Server
 
C# Code to generate sample data and insertion using SQL Bulk Copy
private void loadDataBySQLBulkCopy()
        {
            DataTable dtTblEmployee = new DataTable();
            dtTblEmployee.Columns.Add("ID", typeof(Int32));
            dtTblEmployee.Columns.Add("EmpName", typeof(String));
            dtTblEmployee.Columns.Add("Department", typeof(String));
            dtTblEmployee.Columns.Add("DOB", typeof(DateTime));

            DataRow dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 1; //Bind Data to Columns
            dtrow["EmpName"] = "Wazeem";
            dtrow["Department"] = "Admin";
            dtrow["DOB"] = "1990-10-16 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 2; //Bind Data to Columns
            dtrow["EmpName"] = "Aslam";
            dtrow["Department"] = "HR";
            dtrow["DOB"] = "1987-05-16 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 3; //Bind Data to Columns
            dtrow["EmpName"] = "John";
            dtrow["Department"] = "Finance";
            dtrow["DOB"] = "1992-12-11 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 4; //Bind Data to Columns
            dtrow["EmpName"] = "Mishal";
            dtrow["Department"] = "Infra structure";
            dtrow["DOB"] = "1989-04-01 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            SqlBulkCopy bulkCopy = new SqlBulkCopy(
                "server=TEST;database=TEST;uid=test;password=test",
                SqlBulkCopyOptions.TableLock);
            bulkCopy.DestinationTableName = "dbo.Employee";
            bulkCopy.WriteToServer(dtTblEmployee);

        }

How many classes can a single .NET DLL contain?

There’s a common question in a programming interview that sounds like this:
“How many classes can a single .NET DLL contain?”
I did a little research on the web and I saw that lots of people think that the number is unlimited. Well, in my opinion, this is wrong!
Let me clarify this! Inside MSDN there’s a section on Overview on .NET Framework that contains a section on Metadata and Self-Describing Components. In this section you will find out how metadata is stored inside a Portable Executable and what is the structure of Metadata. When you are looking inside “Metadata and the PE File Structure” , you will find out that each element of your program is stored in a “Metadata table”(one for classes, one for members, a.s.o.). Each element is identified by a “Metadata token” and it’s definition is: ”Each row of each metadata table is uniquely identified in the MSIL portion of the PE file by a metadata token.Metadata tokens are conceptually similar to pointers, persisted in MSIL, that reference a particular metadata table.”(see MSDN). After this section, there is an explanation of these tokens. “A metadata token is a four-byte number. The top byte denotes the metadata table to which a particular token refers (method, type, and so on). The remaining three bytes specify the row in the metadata table that corresponds to the programming element being described.
Let me stop here. If a class is identified in the metadata table using a metadata token and the metadata token is afour-byte number, this means that the number of classes that an assembly can contain is smaller than the biggest number on 3 bytes (the first byte is a connection to the metadata table type). The biggest number on 3 bytes is 2^24– 1 or 16777215.
Therefore, the maximum number of classes (and classes members) that a .NET assembly can contain is 16777215. Am I right? (Wait for comments!)
That’s nice, right? :)
If I’ll have the time, I’ll try to write some code that will generate an assembly with more than 16777215 classes and see if I can use it… :)

Tuesday, 30 October 2012

is not a valid identifier.

I wrote this select but i recive a not valid identifier error message why??!?!?!?


set @sql = ' SELECT eVisionDalsid..'+@InvolvedTable+'.HeatEditedChemistryID AS tabid, eVisionDalsid..Heats.HeatID AS hid, eVisionDalsid..'+@InvolvedTable+'.HeatID AS Expr3,
eVisionDalsid..'+@InvolvedTable+'.'+@FieldValue+' AS fieldvalue, eVisionDalsid..Heats.HeatNumber AS Expr5, eVisionDalsid..HeatOrders.OrderNumber AS Expr6
FROM eVisionDalsid..'+@InvolvedTable+' INNER JOIN
eVisionDalsid..Heats ON eVisionDalsid..'+@InvolvedTable+'.HeatID = eVisionDalsid..Heats.HeatID LEFT OUTER JOIN
eVisionDalsid..HeatOrders ON eVisionDalsid..Heats.HeatID = eVisionDalsid..HeatOrders.HeatID
WHERE (eVisionDalsid..'+@InvolvedTable+'.HeatEditedChemistryID = @IdRegister)'

exec @sql


this is the error message:

Server: Msg 203, Level 16, State 2, Line 30
The name ' SELECT eVisionDalsid..HeatEditedChemistry.HeatEditedChemistryID AS tabid, eVisionDalsid..Heats.HeatID AS hid, eVisionDalsid..HeatEditedChemistry.HeatID AS Expr3,
eVisionDalsid..HeatEdit' is not a valid identifier.

exec (@sql)


Sunday, 23 September 2012

How to update top 100 rows from table in SQL


How to Update top 100 amount of data from a table in SQL

Below codes update top 100 rows. So it never meets the timeout exception and will complete execution faster than normal query
Query for update status of  top 100 data from the table My Table order by date in SQL 


WITH Q as
(
select top 100 * from My_Table order by DATE desc
)
UPDATE Q
SET Status = 0

How to delete large amount of rows from table in SQL

How to delete huge amount of data from a table in SQL

In some scenario we have to delete large amount of rows from sql table and it will going to timeout if the table has very large amount of rows (Some tables in the database has more than crore rows). In this scenario we need to delete some small amount of records and from the table and continue the process until all records in the table deleted.

Query for recursive deletion from the table in SQL 

Below codes delete 50000 rows recursively untill all records int the table deleted. So it never meets the timeout exception and will complete execution faster than normal query


WHILE exists (
SELECT * FROM myTable WHERE name like ‘%ab%’
 )
DELETE TOP (50000) scanned_cont_dtls WHERE name like ‘%ab%’;

Friday, 17 August 2012

How to complete remove deleted files from hard disk

Step:1   Press Win key + R
Step:2   Type CMD
Step:3   Type cipher/W:C\
Step:4   Press Enter

After  C then try D, E, F etc.

Sunday, 5 August 2012

How to Calculate Age in Sql Server OR Find age using SQL query

Calculate age of employee using SQL Server
To calculate an age from the SQL table is little bit tricky.  We are demonstrating how to calculate the age from the date of birth fields in the SQL table. While fill the application form it’s better to fill the Date Of Birth field instead of age field. Because age field can calculate from the Date Of Birth field easily and accurately.
Find the age from the Date Of Birth using SQL Server
Here we are having a table called Employee and having coloumns ID,Name and DOB. From this table we need to find the age of each employee using SQL script.






SELECT DOB AS DateOfBirth,

GETDATE() AS CurrentDate,

DATEDIFF(YEAR,DOB,GETDATE()) -

(CASE WHEN

DATEADD(YY,DATEDIFF(YEAR,DOB,GETDATE()),DOB) > GETDATE()

THEN 1

ELSE 0 END) AS Age

FROM Employee


Tuesday, 24 July 2012

How to pass table to stored procedures in SQL

What is Table – Valued Parameter in SQL Server 2008?

Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.

What we are going to do with Table – Valued Parameter?

We are going to demonstrate a very simple example for using Table – Valued parameter. In this sample project we will insert bulk amount of data into the table by passing a bulk data using datatable in C# to SQL stored procedure.

Create a Table for insert data using Table – Valued Parameter 

Here we are having a table named Officer and having three fields ID,Name and Salary. We are going to fill the table with bulk data.

CREATE TABLE Officer( ID INT PRIMARY KEY IDENTITY(1,1), NAME VARCHAR(50), SALARY DECIMAL(18, 0))
Stored Procedure for insert data by accepting Table Valued Parameter

Now we are going to create a Stored Procedure that accepting a table type as parameter and insert values in this type into the table.

CREATE PROCEDURE InsertOfficerDetails ( @OfficerData OfficerDetails readonly ) AS INSERT INTO Officer (Name, Salary) SELECT Name, Salary FROM @OfficerData;

Wednesday, 27 June 2012

A visual Git reference

A brief, visual reference for the most common commands in git.

The four commands between the working directory, the stage (also called the index), and the history (in the form of commits).

•git add files copies files (at their current state) to the stage.
•git commit saves a snapshot of the stage as a commit.
•git reset -- files unstages files; that is, it copies files from the latest commit to the stage. Use this command to "undo" a git add files. You can also git reset to unstage everything.
•git checkout -- files copies files from the stage to the working directory. Use this to throw away local changes.
Ref:-http://marklodato.github.com/visual-git-guide/index-en.html

Wednesday, 13 June 2012

Outlook configuration

For Outlook

1. In Outlook, go to the Tools menu and click on Email Accounts.
2. Select Add a new email account and then click Next.
3. Select POP3 and then click Next.
4. Enter your email information:

Your Name: your name
Email Address: matt@castleman.net (the email address the messages will be sent from)
Incoming Mail server (POP3): mail.castleman.net
Outgoing Mail server (SMTP): mail.castleman.net
User Name: matt@castleman.net
Password: the password for the email address

5. Click on More Settings and Select the Outgoing Server Tab. Check My outgoing server (SMTP) requires authentication. Select Use same settings as my incoming mail server.

6.Click Ok. Click Next. Click Finish.

If you cannot send email using mail.yourdomainname.com as your SMTP server,
it may be because your ISP is blocking port 25 on their network, used for sending outoing mail via the SMTP protocol. In this instance, we recommend using your ISPs outgoing mail server, which can be obtained from your ISP.

Wednesday, 9 May 2012

How to Enable Network Access in SQL Server Configuration Manager

To enable a network protocol

  1. On the Start menu, choose All Programs, point to Microsoft SQL Server and then click SQL Server Configuration Manager.
    Optionally, you can open Computer Manager by right-clicking My Computer and choosing Manage. In Computer Management, expand Services and Applications, expand SQL Server Configuration Manager.
  2. Expand SQL Server Network Configuration, and then click Protocols for InstanceName.
  3. In the list of protocols, right-click the protocol you want to enable, and then click Enable.
    The icon for the protocol will change to show that the protocol is enabled.
  4. To disable the protocol, follow the same steps, but choose Disable in step 3.

To configure a network protocol

  1. On the Start menu, right-click My Computer, and then choose Manage.
  2. In Computer Management, expand Services and Applications, expand SQL Server Configuration Manager, expand Server Network Configuration, expand Protocols for InstanceName, and then click the protocol you want to configure.
  3. Right-click the protocol you want to configure, and then choose Properties.
  4. In Properties, you can set the protocol-specific options.

Tuesday, 24 April 2012

How to create thumbnail image in ASP.Net/C#


How to reduce the size of image before uploaded to the server in ASP.Net/C#
In most of the ASP.Net application with uploaded image facility will face the performance issue while loading uploaded images to display for the user. We can limited the maximum size of the image that a user can upload but it may not be a good solution because a user has to facing very difficult situation for uploading a large image. He has to reduce the image from other tools and upload again.
The better approach to solve this issue, we are not going to giving any maximum size limit for uploading image. Instead we are internally reducing the size of the image that a user going to upload by checking whether it is exceeds affordable size.  Then there is no constraints are given to user so that he doesn’t care about the size and quality of the image.
Image Size 764 KB
Simple steps to reduce the size of image before upload in ASP.Net/C#
 Here we are going to demonstrate a very simple and best approach to reduce the size of the image before uploaded to the server. In some application we need to show thumbnail image with uploaded images. In that case there is no need to store very large image in the server. So before going to uploading to the server, we are going to create thumbnail image or reduce the size of image as we wish using C#/ASP.net. This is very simple sample of reducing image and can be easily understand by beginners also.
Step by Step process to create an application for reducing size of image before uploaded to the server in ASP.Net/C#
 Step 1 . Create a ASP.Net project and create a web page.
Step 2.  Drag a file uploader, button and Datalist(for display uploaded images)
ASPX Page
 <asp:Panel runat="server">
<asp:FileUpload ID="fileupload1" runat="server" />
<asp:Button ID="btnsave" runat="server" Text="Upload" OnClick="btnsave_Click" />
</div>
<div>
<asp:DataList ID="dtlist" runat="server" RepeatColumns="3" CellPadding="5">
<ItemTemplate>
<asp:Image ID="Image1" ImageUrl='<%# Bind("Name", "~/Images1/{0}") %>' runat="server" />
<br />
<asp:HyperLink ID="HyperLink1" Text='<%# Bind("Name") %>'
NavigateUrl='<%# Bind("Name", "~/Images1/{0}") %>'
runat="server" />
</ItemTemplate>
<ItemStyle BorderColor="Brown" BorderStyle="dotted" BorderWidth="3px"
HorizontalAlign="Center"
VerticalAlign="Bottom" />
</asp:DataList>
</asp:Panel>

Step 3 .  In page load call function ‘BindDataList’ to display uploaded images
Step 4.   On Save button click event, call function ‘GenerateThumbnails’ for reduce of image.
Code Behind Page 
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindDataList();
}
}
protected void BindDataList()
{
DirectoryInfo dir = new DirectoryInfo(MapPath("Images1"));
FileInfo[] files = dir.GetFiles();
ArrayList listItems = new ArrayList();
foreach (FileInfo info in files)
{
listItems.Add(info);
}
dtlist.DataSource = listItems;
dtlist.DataBind();
}
protected void btnsave_Click(object sender, EventArgs e)
{
string filename = Path.GetFileName(fileupload1.PostedFile.FileName);
string targetPath = Server.MapPath("Images1/" + filename);
Stream strm = fileupload1.PostedFile.InputStream;
var targetFile = targetPath;
//Based on scalefactor image size will vary
GenerateThumbnails(0.5, strm, targetFile);
BindDataList();
}
private void GenerateThumbnails(double scaleFactor, Stream sourcePath,
string targetPath)
{
using (var image = Image.FromStream(sourcePath))
{
// can given width of image as we want
var newWidth = (int)(image.Width * scaleFactor); 
// can given height of image as we want
var newHeight = (int)(image.Height * scaleFactor);
var thumbnailImg = new Bitmap(newWidth, newHeight);
var thumbGraph = Graphics.FromImage(thumbnailImg);
thumbGraph.CompositingQuality = CompositingQuality.HighQuality;
thumbGraph.SmoothingMode = SmoothingMode.HighQuality;
thumbGraph.InterpolationMode = InterpolationMode.HighQualityBicubic;
var imageRectangle = new Rectangle(0, 0, newWidth, newHeight);
thumbGraph.DrawImage(image, imageRectangle);
thumbnailImg.Save(targetPath, image.RawFormat);
}           
}
 
Image Size 36 KB
By using above application, we can reduce the size of images before uploaded to the server. If we check the size of the image that we uploaded, that will be less than the size of image that having in the local folder. We can implement this mechanism in most of the shopping site and photo gallery sites so that we can store large amount of images with small size and it will help the performance of the application. We can identify that even reducing the size of image there is no variation in quality or clarity of the image.  So we can easily reduce the resolution of the image using above code in ASP.Net/C# application.

Thursday, 12 April 2012

Property is locked for login Sa


If you are getting the error “Property is locked and not avaliable for login Sa” when You go to the properties of sa user in MS SQL management Studio . You can fix the problem by logging in MS SQL management studio , Right click on Server name , New Query and execute the command .

ALTER LOGIN [sa] WITH PASSWORD=N’newpassword’, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [sa] ENABLE

Monday, 2 April 2012

How to increase the asp.net Runtime execution timout value

We can increase the asp.net session time out value by adding the following entry in the web.config file 
<system.web>
  <httpRuntime executionTimeout="1300"/>
</system.web>

Time interval is in Seconds .The default is "00:01:50" (110 seconds).

How to increase the asp.net session timout value in IIS 7.0


We can increase the asp.net session timeout value by adding the following entry in the web.config file


<system.web>
<sessionState timeout=”540″/>
</system.web>

How to Register ASP.NET 2.0 with IIS


Go to the command prompt ,  go to the path C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727> and execute the command
ASPNET_REGIIS.EXE -i

Friday, 30 March 2012

Changing ASP execution time-- IIS

Some tiems you need to cheng the asp script execution time, especially in the case of asp uploads

steps to follow

1. Open IIS

2. Navigate to

IIS-->websites-->domainname.com-->properties( right click)-->Home directory-->configuration (in the bottom half of home directory)-->options (second tab) // here you can find the asp execition time (usually one) change it

 /*!!!! DONT RESTART IIS !!!*/

IIS Commands

Start IIS from command prompt

iisreset /start

Stop IIS from command prompt

iisreset /stop