European Windows 2012 Hosting BLOG

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

SQL Server 2014 Hosting - HostForLIFE.eu :: How to Wrap a Long Text in SQL Server Reporting Service

clock September 17, 2015 11:37 by author Rebecca

In this blog post “Report Viewer wrap text”, we are going to learn an important trick of text wrapping in SSRS reports for long texts (without space and any separator) exceeding the width of a report column’s defined width and being rendered in a Report Viewer control of .NET Application.

Impact of text wrapping in report project and on report server

Lets start this too with some demo and sample codes. Follow these steps to create a sample application and produce this on your machine:

1. Create a report server project in SQL Server Data Tools (SSDT)

2. Add a Datasource and a Dataset in the report.

3. In query type of Dataset, select “Text”.

4. Add below as query in “Query” box of the Dataset. You can also write your own query.

SELECT 'ThisTextDoesNotContainAnySpace' AS Comment
UNION ALL
SELECT 'ThisTextDoesNotContainAnySpace' AS Comment
UNION ALL
SELECT 'ThisTextDoesNotContainAnySpace' AS Comment

5. In above query, we have a text string without any space as a column named “Comment”. Now add a “Table” control from Report Item’s tollbox on the report body and select this column named “Comment” in the table to display. Remember you have fixed the column width less than the above string’s length to produce the mentioned issue.

6. Click on “preview” button to check the output locally. The text should be wrapped to the next row if it exceeds the width of the column. Below is the screen shot:

7. In above image you can see that this text could not be accommodated in one row in this column and has been wrapped in multiple rows. But the width of the column is constant as desired.Below i will show you the output of this report after deploying it on report server. I have deployed the same report on my local report server and executed from report server. Below is the screen shot:

Again you can see that we have the desired output i.e. text wrapping is taken care as per the column width.

Impact of text wrapping in ReportViewer Control

Now we have to check the same report in a report viewer control. To do so, follow these steps:

1. Create a ASP.NET Web Application in Visual Studio.

2. Make appropriate changes in web.config file. What i did on my machine is below:

<system.webServer>
<handlers>
<add name="ReportViewerWebControlHandler" preCondition="integratedMode" verb="*" path="Reserved.ReportViewerWebControl.axd" type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
</handlers>
</system.webServer>

3. Add an .aspx web form in the project and on this page register the report viewer control as below. Before that also add the reference of reportviewer assembly in project references.

<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>

4. Add a report viewer control on this page and a script manager too like below:

<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
<div>
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Width="1000">
</rsweb:ReportViewer>
</div>
</form>

5. Write a code to display your report from this report viewer control in .cs file of this web page like below. You can modify the below code as per your need. I did not have any parameter in my report to keep this demo simple and to focus in main issue.

if (!IsPostBack)
{
ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
 
Microsoft.Reporting.WebForms.ServerReport serverReport = ReportViewer1.ServerReport;
 
// Set the report server URL and report path
serverReport.ReportServerUrl = new Uri("http://mymachine/reportserver");
serverReport.ReportPath = "/TestReports/TextWrapping";
}

6. Build your application and open the web page in your browser. You will have an output as below:

In above figure, you can see that the output of the same report has been changed. The text wrapping is not working as it worked in above cases which was desired too. In this case width of the column is expanding to accommodate the text instead of wrapping the text in more than one row.

Enable Text Wrapping in SQL Server Reporting Server rendered in report viewer control on web page

As of now and what i know, there is no any staright forward method to achieve this task and to do this we have to folk it as below:

1. To render this report with text wrapping, follow these steps;
2. To avoid rework, go to your report project and click on the textbox which holds this data inside the table and press ctrl + X to cut it from the table.
3. Drag and drop a rectangle control in the table’s cell from the Report Item’s toolbox.
4. Press ctrl + V to put the textbox inside the rectangle.
5. Set the border of the rectangle as your textbox has.
6. Deploy the report and see the output in your browser:

HostForLIFE.eu SQL Server 2014 Hosting
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.



SQL Server 2014 Hosting - HostForLIFE.eu :: How to Get CONSTRAINT of Database or Table

clock June 22, 2015 05:51 by author Rebecca

In this post, I am going to explain how you can list all CONSTRAINT of database or table in SQl Server. When you are working on database sometimes you need to check or get the CONSTRAINT on database or table. Using below given query we can get the CONSTRAINT of table or database quickly.

Step 1

Query to list all fields of sys.objects:
SELECT * FROM sys.objects WHERE type_desc LIKE '%CONSTRAINT'
Using above query we get all fields of sys.objects.

Step 2

To get only Constraint name, tabe name and Constraint type I use the refined query mention below:
SELECT OBJECT_NAME(object_id) AS ConstraintName,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'

Step 3

To get the Constraint detail of a particular table use the below given query:
SELECT OBJECT_NAME(object_id) AS ConstraintName,
SCHEMA_NAME(schema_id) AS SchemaName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT' AND OBJECT_NAME(parent_object_id)='Student_Register'

The last, replace the Student_Register with your database table name.

HostForLIFE.eu SQL Server 2014 Hosting
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.



SQL Server 2014 Hosting - HostForLIFE.eu :: How to Use DELETE Statement to Remove Rows

clock June 12, 2015 08:31 by author Rebecca

Sometimes, you may want to delete rows because they are no longer needed, or they were incorrectly added in the first place. The DELETE statement is used to remove rows from a SQL Server table. A single DELETE statement can remove a single row, or number of rows. In this article, I will explore how to remove rows from a table using the DELETE statement.

Here is the basic syntax of the DELETE statement:

DELETE
[ TOP ( expression ) [ PERCENT ] ]
[ FROM ] <object>
[ <OUTPUT Clause> ]
[ WHERE <search_condition>]


( expression ) - is a number or an expression that equates to a number used to limit the number of rows deleted.

<object> - is the name of an object in a database from which you want to delete records

<OUTPUT Clause> - identifies the column values of the deleted rows to be returned from the DELETE statement

<search_condition>
- the condition used to identify the rows to be deleted

In order to demonstrate how to use the DELETE statement I will be creating a DemoDelete table. Here is the code I used to create and populate my DemoDelete table.

USE tempdb;
GO
CREATE TABLE DemoDelete (ID int,
DeleteDesc varchar(100));
GO
INSERT INTO DemoDelete VALUES
(1,'Thing One'),
(2,'Thing Two'),
(3, 'The Cat'),
(4, 'Sally'),
(5, 'The Brother'),
(6, 'The Mother'),
(7, 'The Fish');

Deleting a Single Row Using WHERE Constraint

In order to delete a single row from a table you need to identify that row with a WHERE constraint. Below is some code that deletes a single row from my DemoDelete table:

DELETE FROM DemoDelete
WHERE DeleteDesc = 'The Mother';

In this code I used the DeleteDesc column to constrain the records that I would be deleting. By specifying that the DeleteDesc column value had to be equal to the value "The Mother", only one record in my table got deleted, because only one row in my table had that value. Now if my table contained a number of rows that had a column value of "The Mother" then all the rows that contained that value would be deleted.

If you are unsure of the rows you are identifying to be deleted using the above example, and you want to make sure the rows you have targeted with the WHERE constraint are correct, then you can first run a SELECT statement. After you are confident that your SELECT statement is selecting the rows you want to delete you can then convert it to a DELETE statement.

Using the TOP Clause to Delete a Single Row

You can also use the TOP clause to delete a single row. Below is an example where I used the TOP clause to delete one row from my DemoDelete table:

DELETE TOP (1) FROM DemoDelete;

This statement deleted a random row from my DemoDelete table. It was random because SQL Server does not guarantee a sorted set will be returned where it can delete the top record of the ordered set. When I review the records left in my table I see I deleted the record that had an Id value of 1 and a DeleteDesc of "Thing One". Note if I change the TOP clause to another number like 3, then this statement would delete the number of rows equal to the value specified.

Deleting the TOP 1 Records from a Sorted Set

If you want to delete the first record from a sorted set you need to write your TSQL DELETE statement similar to the following code:

DELETE TOP (1) FROM DemoDelete
WHERE ID in
(SELECT TOP (1) ID FROM DemoDelete
ORDER BY ID DESC);

In the above code I create a subquery that returned a single ID value based on the descending sort order of ID column value in my DemoDelete table. I then used the WHERE constraint to only delete records that had that ID value. I also place a TOP (1) clause on my DELETE statement to only delete a single row should my DemoDelete table contain multiple records with the same ID value. If you are following along you can see the above code deleted the DemoDelete record that had an ID value of 7.

Since my DemoDelete table did not contain multiple records with the same ID value I could have also deleted the largest ID value row by running the following code:

DELETE FROM DemoDelete
WHERE ID in
(SELECT TOP (1) ID FROM DemoDelete
ORDER BY ID DESC);

When I run this code against my DemoDelete table it will delete ID value of 5.

Using Another Table to Identify the Rows to Delete and the OUTPUT Clause

There are times when you might what to delete the rows in a table based on values from another table. An example of where you might want to do this is to remove rows from your inventory table based on some sales data. To demo this first I will need to generate another table that contains key values for the rows I want to delete. Here is the code to create and populate my other table:

CREATE TABLE RecordsToDelete (
DeleteDesc varchar(100));
GO
INSERT INTO RecordsToDelete VALUES
('Thing Two'),
('Sally');


At this point after running all my different DELETE statements against my DemoDelete table there are only three rows left in my table. By selecting all the rows in my DemoDelete table I see that these three rows are left:

ID DeleteDesc
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
2 Thing Two
3 The Cat
4 Sally

In order to use the RecordsToDelete table to delete specific records in my DemoDelete table I need to run the code below:

DELETE FROM DemoDelete
OUTPUT DELETED.*
FROM DemoDelete INNER JOIN RecordsToDelete
on DemoDelete.DeleteDesc = RecordsToDelete.DeleteDesc;

This code joins the table DemoDelete and RecordsToDelete based on the DeleteDesc column. When the DeleteDesc matches between the two tables the matched rows within the DemoDelete table are deleted.

My delete statement above also contains the OUTPUT clause. The OUTPUT clause is used to return the column values of the deleted rows that are identified in the OUTPUT clause. In the code above I specified "DELETED.*". The "*" means to return all the columns values from the DELETED rows. When I ran this code the following rows were returned:

ID DeleteDesc
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
2 Thing Two
4 Sally

These returned rows could be used by your application for some purpose, like creating an audit trail.

Inserting OUTPUT Clause Data into a Table

There are times when you might retain the data created by the OUTPUT clause in a table instead of just returning the deleted row values to the application. To demonstrate running a DELETE statement that populates the row values being deleted into a table I will run the code below:

DECLARE @DeletedRows TABLE
(ID INT, DeleteDesc varchar(100));
DELETE FROM DemoDelete
OUTPUT DELETED.ID, DELETED.DeleteDesc
INTO @DeletedRows
WHERE DeleteDesc = 'The Cat';
SELECT * FROM @DeletedRows;


In this code sample I first created a table to contain my deleted rows. This table is a table variable name @DeletedRows. Next I ran my DELETE statement. This time my DELETE statement specified the deleted row output was to go into my table variable. That specification was made using the INTO clause of the DELETE statement.

The following output displayed the SELECT statement in the above code snippet:

ID DeleteDesc
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
3 The Cat

In both of my examples that used the OUTPUT clause of the DELETE statement I specified "DELETED.*" to denote outputting all the column values for the rows being deleted. I could have specified the actual column values I wanted to output. The code below is equivalent to the code above.

DECLARE @DeletedRows TABLE
(ID INT, DeleteDesc varchar(100));
DELETE FROM DemoDelete
OUTPUT DELETED.ID, DELETED.DeleteDesc
INTO @DeletedRows
WHERE DeleteDesc = 'The Cat';
SELECT * FROM @DeletedRows;

In this code you can see I specified "DELETED.ID, DELETED.DeleteDesc", instead of "DELETE.*". You can verify this code is equivalent by inserting the "The Cat" row back into the DemoDelete table and then running the code above.

As you can see there are multiple ways to delete rows from a SQL Server table. You can use the WHERE clause to identify specific criteria for the rows that need to be deleted. You can join a table to the table in which you are deleting rows to identify which rows to delete. You can even use the TOP clause to restrict the number of rows that will be deleted. The article should help you with developing your DELETE statement next time you have to remove some rows from a SQL Server table.

HostForLIFE.eu SQL Server 2014 Hosting
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.



SQL Server 2014 Hosting - HostForLIFE.eu :: Create Country Table and Populate With All Countries

clock June 9, 2015 08:29 by author Peter

In this article, I will tell you a SQL Script to Create Country table and populate with all countries. In this case, we either use a text box where user enters the country name or provide user with a drop down list of all countries. To create the dropdown, it is advised to store country names in a database table. Write the following code:

CREATE TABLE [dbo].[Country]( 
[ID] [int] IDENTITY(1,1) NOT NULL, 
[CountryName] [nvarchar](100) NOT NULL 
) ON [PRIMARY] 
 
GO 
SET IDENTITY_INSERT [dbo].[Country] ON 
 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Afghanistan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Albania') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Algeria') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'American Samoa') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Andorra') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Angola') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Anguilla') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Antarctica') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Antigua And Barbuda') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Argentina') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Armenia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Aruba') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Australia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Austria') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Azerbaijan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bahamas') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bahrain') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bangladesh') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Barbados') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Belarus') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Belgium') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Belize') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Benin') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bermuda') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bhutan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bolivia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bosnia And Herzegowina') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Botswana') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bouvet Island') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Brazil') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'British Indian Ocean Territory') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Brunei Darussalam') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bulgaria') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Burkina Faso') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Burundi') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cambodia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cameroon') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Canada') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cape Verde') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cayman Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Central African Republic') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Chad') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Chile') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'China') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Christmas Island') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cocos (Keeling) Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Colombia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Comoros') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Congo') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cook Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Costa Rica') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cote D''Ivoire') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Croatia (Local Name: Hrvatska)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cuba') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cyprus') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Czech Republic') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Denmark') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Djibouti') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Dominica') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Dominican Republic') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'East Timor') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Ecuador') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Egypt') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'El Salvador') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Equatorial Guinea') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Eritrea') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Estonia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Ethiopia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Falkland Islands (Malvinas)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Faroe Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Fiji') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Finland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'France') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'French Guiana') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'French Polynesia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'French Southern Territories') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Gabon') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Gambia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Georgia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Germany') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Ghana') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Gibraltar') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Greece') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Greenland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Grenada') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Guadeloupe') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Guam') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Guatemala') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Guinea') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Guinea-Bissau') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Guyana') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Haiti') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Heard And Mc Donald Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Holy See (Vatican City State)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Honduras') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Hong Kong') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Hungary') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Iceland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'India') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Indonesia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Iran (Islamic Republic Of)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Iraq') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Ireland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Israel') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Italy') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Jamaica') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Japan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Jordan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Kazakhstan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Kenya') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Kiribati') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Korea, Dem People''S Republic') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Korea, Republic Of') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Kuwait') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Kyrgyzstan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Lao People''S Dem Republic') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Latvia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Lebanon') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Lesotho') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Liberia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Libyan Arab Jamahiriya') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Liechtenstein') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Lithuania') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Luxembourg') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Macau') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Macedonia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Madagascar') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Malawi') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Malaysia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Maldives') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mali') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Malta') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Marshall Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Martinique') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mauritania') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mauritius') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mayotte') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mexico') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Micronesia, Federated States') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Moldova, Republic Of') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Monaco') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mongolia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Montserrat') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Morocco') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mozambique') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Myanmar') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Namibia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Nauru') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Nepal') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Netherlands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Netherlands Ant Illes') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'New Caledonia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'New Zealand') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Nicaragua') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Niger') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Nigeria') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Niue') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Norfolk Island') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Northern Mariana Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Norway') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Oman') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Pakistan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Palau') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Panama') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Papua New Guinea') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Paraguay') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Peru') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Philippines') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Pitcairn') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Poland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Portugal') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Puerto Rico') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Qatar') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Reunion') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Romania') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Russian Federation') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Rwanda') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Saint K Itts And Nevis') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Saint Lucia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Saint Vincent, The Grenadines') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Samoa') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'San Marino') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Sao Tome And Principe') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Saudi Arabia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Senegal') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Seychelles') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Sierra Leone') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Singapore') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Slovakia (Slovak Republic)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Slovenia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Solomon Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Somalia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'South Africa') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'South Georgia , S Sandwich Is.') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Spain') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Sri Lanka') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'St. Helena') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'St. Pierre And Miquelon') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Sudan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Suriname') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Svalbard, Jan Mayen Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Sw Aziland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Sweden') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Switzerland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Syrian Arab Republic') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Taiwan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Tajikistan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Tanzania, United Republic Of') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Thailand') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Togo') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Tokelau') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Tonga') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Trinidad And Tobago') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Tunisia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Turkey') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Turkmenistan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Turks And Caicos Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Tuvalu') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Uganda') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Ukraine') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'United Arab Emirates') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'United Kingdom') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'United States') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'United States Minor Is.') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Uruguay') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Uzbekistan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Vanuatu') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Venezuela') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Viet Nam') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Virgin Islands (British)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Virgin Islands (U.S.)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Wallis And Futuna Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Western Sahara') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Yemen') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Yugoslavia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Zaire') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Zambia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Zimbabwe') 
GO 
SET IDENTITY_INSERT [dbo].[Country] OFF 
GO 



SQL Server 2014 with Free ASP.NET Hosting - HostForLIFE.eu :: How to Use T-SQL to Find Weak Password

clock June 5, 2015 06:34 by author Rebecca

Recently one of our costumer emailed us below question:

Hi Hostforlife,
Need your urgent help. In recent past, we have been attacked by the hacker who was able to get in to our SQL Server via sysadmin account and made big damage to our data. To make sure it doesn’t happen in future, I have taken task to find out SQL Server password which are weak.
Do you have any suggestions for me?

This is one of the area which is always haunting all SQL DBAs. There are recommendations to use Windows Authentication to connect to SQL Server and that would save from all such problem. But it is not always feasible to use Windows Authentication. Now, if you decided to choose SQL Authentication, there is a setting which is “Enforce Password Policy” which would ensure that you are choosing a strong password.

If recommendations are not followed, you might end up in situation where SQL Logins have weak and basic passwords. SQL Server has provided a function PWDCOMPARE which can become very useful to find known password. Below are few example use of this out of box funtion:

SELECT NAME,
NAME 'password'
FROM   sys.sql_logins
WHERE  Pwdcompare(NAME, password_hash) = 1
UNION
SELECT NAME,
'<blank>' AS 'password'
FROM   sys.sql_logins
WHERE  Pwdcompare('', password_hash) = 1
UNION
SELECT NAME,
'password123' AS 'password'
FROM   sys.sql_logins
WHERE  Pwdcompare('password123', password_hash) = 1

In above query, we are trying to find:

  •     Password same as user name – first query
  •     Blank password – second query
  •     Password = password123 – third query

These are one of the most common password used in the industry. I am sure you can extend this further by modifying it and adding more weak passwords.

Here is the sample output for the above:

Hope this article would help you in finding weak passwords and make it more complex.

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.

 



SQL Server 2014 with Free ASP.NET Hosting - HostForLife.eu :: How to Return More Than One Table from Store Procedure in MSSQL Server?

clock June 4, 2015 08:12 by author Peter

In this post, I will tell you about how to return more than one table from store procedure in SQL Server 2014. Return a single table full of data from store procedure we are able to use a DataTable however to come back multiple tables from store procedure we've got to use DataSet. DataSet could be a bunch of DataTables. so the following code you'll use to return single table. SqlCommand

 

cmd = new SqlCommand("sp_Login", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = email;
cmd.Parameters.Add("@Password", SqlDbType.NVarChar).Value = password;
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
{
   con.Open();
}
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
return dt;


Now write the store procedure like the following code:
CREATE PROCEDURE sp_Test
    @Email nvarchar(255)
AS
BEGIN
    SET NOCOUNT ON;
    select * from <tblName> whhere email = @Email
END


And then, It’s time to found how to get multiple table value in one DataSet. Write the code below:
CREATE PROCEDURE sp_Test
AS
BEGIN   
    SET NOCOUNT ON;
    select * from Tbl1
    select * from Tbl2
    select * from Tbl3
    select * from Tbl4
END


And here is the C# code:
SqlCommand cmd = new SqlCommand("sp_test", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
{   
con.Open();
}
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
con.Close();
// Retrieving total stored tables from DataSet.             
DataTable dt1 = ds.Tables[0];
DataTable dt1 = ds.Tables[1];
DataTable dt1 = ds.Tables[2];
DataTable dt1 = ds.Tables[3];

I hope it works for you!

 

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting

Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.

 



SQL Server 2014 with Free ASP.NET Hosting - HostForLife.eu :: How to Track Page Splits in SQL Server

clock May 29, 2015 06:59 by author Rebecca

In this tutorial, I will show you how to use extended events to identify page splits in SQL Server. The easiest way to track if page splits are happening in SQL Server is to use the PerfMon Counters. For the records you can start from  “SQL Server: Access Methods -> Page Splits/sec”. This counter is quite handy to understand if this behavior happens in our SQL Server instance.

Step 1

Let us first create a database for our experiment. We will also create our Extended Event to track the PageSplits.

-- Create a dummy database
CREATE DATABASE PageSplits
GO

-- Get the DB ID, we will use it in next step
SELECT DB_ID('PageSplits')
GO

-- Create Session
CREATE EVENT SESSION [Catch_PageSplit] ON SERVER
ADD EVENT sqlserver.page_split(
WHERE ([package0].[equal_uint64]([database_id],(10))))  -- NOTE: Please make sure you change the DATABASE_ID
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF)
GO

-- Start the event session
ALTER EVENT SESSION [Catch_PageSplit] ON SERVER
STATE = start;
GO

Step 2

Before we get to the next step, let us start monitoring our Extended Events for any possible page splits.

USE PageSplits
GO
CREATE TABLE mytest (
myCompany CHAR(5) NOT NULL CONSTRAINT pk_mytest PRIMARY KEY CLUSTERED,
FillData VARCHAR(3000) NOT NULL
)
GO
INSERT mytest ( myCompany, FillData )
VALUES( '00001', REPLICATE( 'A', 3000 ) ),
( '00002', REPLICATE( 'B', 1000 ) ),
( '00003', REPLICATE( 'C', 3000 ) ),
( '00004', REPLICATE( 'A', 3000 ) ),
( '00005', REPLICATE( 'B', 1000 ) ),
( '00006', REPLICATE( 'C', 3000 ) ),
( '00007', REPLICATE( 'A', 3000 ) ),
( '00008', REPLICATE( 'B', 1000 ) ),
( '00009', REPLICATE( 'C', 3000 ) )
GO

Step 3

Next step is to create a table with some values. Later we will use this to create a page split scenario:

Step 4

Then, create the scenario of page split by updating a row with some extra data.

-- Update to introduce a page split
UPDATE mytest
SET FillData = REPLICATE( 'B', 3000)
WHERE myCompany = '00002'
GO

Don’t forget to look at the Live Data feed for entries. If the Page Split happen you will see something like:

As you can see, this Page Split has happened because of an Update. The two pages under question are also shown. We can also see the database ID under question that caused the page split.

Step 5

Now that we learnt something new here, let us clean up the database we just created using this code:
-- Clean up time
USE MASTER
GO
DROP DATABASE PageSplits
GO
DROP EVENT SESSION [Catch_PageSplit] ON SERVER
GO

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.



SQL Server 2014 with Free ASP.NET Hosting - HostForLIFE.eu :: Execute a Task at a Specific Time in SQL Server

clock May 28, 2015 07:52 by author Peter

Here during this example I'll show you the way to execute a task or call a store procedure at a specific time(like at midnight) using SQL Server or I will say the way to schedule a job at a specific time. Sometimes you have got to perform something daily, weekly or at a selected gap of your time. Repeat a similar task when by a person is little bit difficult. So, in this case SQL is here to solve your problem using SQL Job scheduler. This is often a task you fixed in your server, set timer on at particular time and write down the SQL query to perform. That is all you have got to do. Let’s see the way to schedule your Job at a selected time.

Open your Management Studio and check the SQL Server Agent. Begin the SQL Server Agent by following steps.

Next step, go to Job section in your SQL Server Agent and select New Job, shown on the below picture:

After choosing the New Job, a new window will be opened with all the properties of a New Job. Now it’s time to fill up each and every field according to your specification. In the General tabs write down any name according to your project and left the others as it is.

Now, in the “Steps”, click on the New button, and a new window will be opened as shown on the following picture:

Write a name for your step and your query to be executed and select on the OK button.

Now, in the Schedule tab follow the steps as the following image:

The only thing is left is to start your job. To start the job follow this steps.

After successfully starting of the job you will get a successful alert message shown on the following picture:

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.



SQL Server 2014 with Free ASP.NET Hosting - HostForLIFE.eu :: How to BackUp SQL Server Database at Once

clock May 26, 2015 06:21 by author Rebecca

Here I am going to share how can you take the database backup of all the SQL Server databases at once instead of taking it individually.

First, you need to create a folder in any drive to store the backup of database which you are going to take. For example, you have to create a folder named "dbBackup" in my drive "C:\"

Then, copy the below code & paste it in your SQL Server Query Executer page:

    DECLARE @DBName varchar(255)
    DECLARE @DATABASES_Fetch int
    DECLARE DATABASES_CURSOR CURSOR FOR
        select
            DATABASE_NAME   = db_name(s_mf.database_id)
        from
            sys.master_files s_mf
        where
           -- ONLINE
            s_mf.state = 0

           -- Only look at databases to which we have access
        and has_dbaccess(db_name(s_mf.database_id)) = 1

            -- Not master, tempdb or model
        and db_name(s_mf.database_id) not in ('Master','tempdb','model')
        group by s_mf.database_id
        order by 1

    OPEN DATABASES_CURSOR

    FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        declare @DBFileName varchar(256)   
        set @DBFileName = datename(dw, getdate()) + ' - ' +
                           replace(replace(@DBName,':','_'),'\','_')

        exec ('BACKUP DATABASE [' + @DBName + '] TO  DISK = N''c:\dbBackup\' +
            @DBFileName + '.bak' + ''' WITH NOFORMAT, INIT,  NAME = N''' +
            @DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 100')

        FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
    END

    CLOSE DATABASES_CURSOR
    DEALLOCATE DATABASES_CURSOR


Now execute the page and check your Folder "dbBackup" in Drive "C:/" . You will find all the databases backup in it.

Now, you are done! Simple, right?

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.



SQL Server 2014 with Free ASP.NET Hosting - HostForLIFE.eu :: How to Create a Large Table with Random Data in SQL Server

clock May 22, 2015 07:31 by author Rebecca

In this tutorial, I'm gonna tell you about inserting large amount of random data into sql server tables for performance testing. 

Just follow these step to create a large table with random data for performance testing:

Step 1

If Table exists drop the tables:

If (Exists (select *
            from information_schema.tables
            where table_name = 'tblProductSales'))
Begin
 Drop Table tblProductSales
End

If (Exists (select *
            from information_schema.tables
            where table_name = 'tblProducts'))
Begin
 Drop Table tblProducts
End

Step 2

Then you can recreate the tables:

Create Table tblProducts
(
 [Id] int identity primary key,
 [Name] nvarchar(50),
 [Description] nvarchar(250)
)

Create Table tblProductSales
(
 Id int primary key identity,
 ProductId int foreign key references tblProducts(Id),
 UnitPrice int,
 QuantitySold int
)

Step 3

Insert Sample data into tblProducts table

Declare @Id int
Set @Id = 1

While(@Id <= 300000)
Begin
 Insert into tblProducts values('Product - ' + CAST(@Id as nvarchar(20)),
 'Product - ' + CAST(@Id as nvarchar(20)) + ' Description')
 
 Print @Id
 Set @Id = @Id + 1
End

Step 4

Declare variables to hold a random ProductId, UnitPrice and QuantitySold

declare @RandomProductId int
declare @RandomUnitPrice int
declare @RandomQuantitySold int


Declare and set variables to generate a random ProductId between 1 and 100000

declare @UpperLimitForProductId int
declare @LowerLimitForProductId int


set @LowerLimitForProductId = 1
set @UpperLimitForProductId = 100000


Declare and set variables to generate a random UnitPrice between 1 and 100

declare @UpperLimitForUnitPrice int
declare @LowerLimitForUnitPrice int

set @LowerLimitForUnitPrice = 1
set @UpperLimitForUnitPrice = 100

Declare and set variables to generate a random QuantitySold between 1 and 10

declare @UpperLimitForQuantitySold int
declare @LowerLimitForQuantitySold int

set @LowerLimitForQuantitySold = 1
set @UpperLimitForQuantitySold = 10

Step 5

Now, you have to insert Sample data into tblProductSales table

Declare @Counter int
Set @Counter = 1


While(@Counter <= 450000)
Begin
 select @RandomProductId = Round(((@UpperLimitForProductId - @LowerLimitForProductId) * Rand() + @LowerLimitForProductId), 0)
 select @RandomUnitPrice = Round(((@UpperLimitForUnitPrice - @LowerLimitForUnitPrice) * Rand() + @LowerLimitForUnitPrice), 0)
 select @RandomQuantitySold = Round(((@UpperLimitForQuantitySold - @LowerLimitForQuantitySold) * Rand() + @LowerLimitForQuantitySold), 0)
 
 Insert into tblProductsales
 values(@RandomProductId, @RandomUnitPrice, @RandomQuantitySold)

 Print @Counter
 Set @Counter = @Counter + 1
End

Step 6

Finally, check the data in the tables using a simple SELECT query to make sure the data has been inserted as expected.
Select * from tblProducts
Select * from tblProductSales

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.



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