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 Check Available Column in A Table

clock January 5, 2016 21:50 by author Rebecca

In this tutorial, we will share to you how to check available colum in a table. If you want to check if the column is already available in the table, you can use system views like sys.columns or INFORMATION_SCHEMA.COLUMNS.

Step 1

First, let us create the dataset:

USE TEMPDB;
CREATE TABLE TESTING(ID INT, NAME VARCHAR(100))

Step 2

Suppose you want to find out the existence of the column named NAME and print a message. You can do it by using any of the following methods:

IF EXISTS
(
SELECT * FROM SYS.COLUMNS
WHERE NAME='NAME' AND OBJECT_ID=OBJECT_ID('TESTING')
)
PRINT 'COLUMN EXISTS'
--
IF EXISTS
(
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME='NAME' AND TABLE_NAME='TESTING'
)
PRINT 'COLUMN EXISTS'

There is also a shorter way to do this. You can use COL_LENGTH system function:

IF (SELECT COL_LENGTH('TESTING','NAME')) IS NOT NULL
PRINT 'COLUMN EXISTS'

What it does is that it finds the length of the column. If it is null, the column does not exist in the table otherwise it exists.

Simple and fast, right?

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 Add Primary Key to Existing Table

clock December 8, 2015 23:28 by author Peter

I have an existing table referred to as Persion. in this table I actually have five columns:
persionId
Pname
PMid
Pdescription
Pamt

When I created this table, I set PersionId and Pname because the primary key. I now wish to include an extra column within the primary key - PMID. however can i write an ALTER statement to try and do this? (I already have 1000 records in the table). First, you can drop constraint and recreate it with the following code:
alter table Persion drop CONSTRAINT <constraint_name>
alter table Persion add primary key (persionId,Pname,PMID)


You can find the constraint name by using the code below:
select OBJECT_NAME(OBJECT_ID) AS NameofConstraint
FROM sys.objects
where OBJECT_NAME(parent_object_id)='Persion'
and type_desc LIKE '%CONSTRAINT'


I think something like this should work
-- drop current primary key constraint
ALTER TABLE dbo.persion
DROP CONSTRAINT PK_persionId;
GO

-- add new auto incremented field
ALTER TABLE dbo.persion
ADD pmid BIGINT IDENTITY;
GO

-- create new primary key constraint
ALTER TABLE dbo.persion
ADD CONSTRAINT PK_persionId PRIMARY KEY NONCLUSTERED (pmid, persionId);
GO

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 Check Last Accessed And Modified Table Data in SQL Server?

clock October 29, 2015 23:38 by author Peter

Now, I will guide you how to check last accessed and modified table data in SQL Data. And here is the script that I use:

/*************************************************/
SELECT OBJECT_NAME(OBJECT_ID) AS [Object_Name], last_user_update,last_user_scan  ,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'Access') AND OBJECT_ID=OBJECT_ID('test')
/*************************************************/

--Note: This will return null if sql server restarted and after that data is not accessed

         Demo:
/**** Create New Database ****/
Create Database Access
GO
use Access
GO
/**** Create Table ****/
Create Table test (id int , name varchar (100))
GO
/**** Insert New Record ****/
Insert into test values (1,'saurabh')
Insert into test values (2,'Sumit')
GO
/**** After insertion Check Stats ****/
SELECT OBJECT_NAME(OBJECT_ID) AS [Object_Name], last_user_update,last_user_scan  ,*
FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID( 'Access') AND OBJECT_ID=OBJECT_ID('test')

Object_Name   last_user_update                  last_user_scan
test               2015-10-29 08:39:54.100           NULL

Here last user scan is null because we are just inserted data, If we read data then we'll get read time also
So now we are going to read data and see if we get last user scan. Now, write the following code:

Select * from access.dbo.test
GO
/**** Checking Stats ****/
SELECT OBJECT_NAME(OBJECT_ID) AS [Object_Name], last_user_update,last_user_scan  ,*
FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID( 'Access') AND OBJECT_ID=OBJECT_ID('test')


So here we just updated table , didnt read. You can see the result on the following picture:

Similarly if we want to see how current indexes are performing on table we can see user scan , user seek or look ups.

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 Tutorial - HostForLIFE.eu :: How to Detect Empty Row in A Table

clock October 28, 2015 03:14 by author Rebecca

Maybe you were recently doing a clean up of your website database, then you create some tables on your database but never adding any new rows to it. In this tutorial, I will tell you how to detect empty row in a table on SQL Server database.

Here's a simple query to list all empty rows in tables in your SQL Server database using a Dynamic Management View called dm_db_partition_stats. This will return page and row-count information for every partition in the current database:

;WITH EmptyRows AS
(
   SELECT SUM(row_count) AS [TotalRows],
          OBJECT_NAME(OBJECT_ID) AS TableName
   FROM sys.dm_db_partition_stats
   WHERE index_id = 0 OR index_id = 1
   GROUP BY OBJECT_ID
)
SELECT * FROM EmptyRows
WHERE [TotalRows] = 0

And here's the output:

Easy right?

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 :: Script the SQL Server Agent Operators

clock October 22, 2015 23:56 by author Peter

As a part of the Disaster recovery procedures, I wished to script out each server object that we had created. This enclosed SQL Server jobs, logins, operators, coupled servers and proxies. i used to be able to script out everything but the SQL Server Agent operators:

USE msdb
set nocount on;

create table #tbl (
id int not null,
name sysname not null,
enabled tinyint not null,
email_address nvarchar(100) null,
last_email_date int not null,
last_email_time int not null,
pager_address nvarchar(100) null,
last_pager_date int not null,
last_pager_time int not null,
weekday_pager_start_time int not null,
weekday_pager_end_time int not null,
Saturday_pager_start_time int not null,
Saturday_pager_end_time int not null,
Sunday_pager_start_time int not null,
Sunday_pager_end_time int not null,
pager_days tinyint not null,
netsend_address nvarchar(100) null,
last_netsend_date int not null,
last_netsend_time int not null,
category_name sysname null);

insert into #tbl
  EXEC sp_help_operator;

select 'USE msdb;' + char(13) + char(10) + 'if exists (select * from dbo.sysoperators where name =' + quotename(name, char(39)) + ') ' + char(13) + char(10) +
'exec sp_add_operator ' +
'@name = ' + quotename(name, char(39)) + ', ' +
'@enabled = ' + cast (enabled as char(1)) + ', ' +
'@email_address = ' + quotename(email_address, char(39)) + ', ' +
case
when pager_address is not null then '@pager_address = ' + quotename(pager_address, char(39)) + ', '
else ''
end +
'@weekday_pager_start_time = ' + ltrim(str(weekday_pager_start_time)) + ', ' +
'@weekday_pager_end_time = ' + ltrim(str(weekday_pager_end_time)) + ', ' +
'@Saturday_pager_start_time = ' + ltrim(str(Saturday_pager_start_time)) + ', ' +
'@Saturday_pager_end_time = ' + ltrim(str(Saturday_pager_end_time)) + ', ' +
'@Sunday_pager_start_time = ' + ltrim(str(Sunday_pager_start_time)) + ', ' +
'@Sunday_pager_end_time = ' + ltrim(str(Sunday_pager_end_time)) + ', ' +
'@pager_days = ' + cast(pager_days as varchar(3)) +
case
when netsend_address is not null then ', @netsend_address = ' + quotename(netsend_address, char(39))
else ''
end +
case
when category_name != '[Uncategorized]' then ', @category_name = ' + category_name
else ''
end +
'; ' + char(13) + char(10) + 'go'
from #tbl order by id;

drop table #tbl;

I hope it works for you!

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 Trigger in SQL Server 2014?

clock October 20, 2015 09:29 by author Peter

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

There are three types of triggers. Basically triggers are classified in to two main type

  • Insert Of Trigger.
  • After Trigger.


This After Trigger run after an insert, update, or delete on table. They are not support view.
So we can say that after trigger also classified in to three types:-

  • AFTER INSERT trigger.
  • AFTER UPDATE trigger.
  • AFTER DELETE trigger.

Insert Trigger
Whenever a row is inserted in the Customers Table, the following trigger will be executed. The newly inserted record is available in the INSERTED table. The following Trigger is fetching the CustomerId of the inserted record and the fetched value is inserted in the CustomerLogs table. Now, write the following code:
CREATE TRIGGER [dbo].[Customer_INSERT]
ON [dbo].[Customers]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CustomerId INT
SELECT @CustomerId = INSERTED.CustomerId      
FROM INSERTED
INSERT INTO CustomerLogs
VALUES(@CustomerId, 'Inserted')
END


Update Trigger
In the below code is an example of an After Update Trigger. Now write the following code:
CREATE TRIGGER [dbo].[Customer_UPDATE]
ON [dbo].[Customers]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @CustomerId INT
DECLARE @Action VARCHAR(50)

SELECT @CustomerId = INSERTED.CustomerId      
FROM INSERTED

IF UPDATE(Name)
BEGIN
      SET @Action = 'Updated Name'
END

IF UPDATE(Country)
BEGIN
      SET @Action = 'Updated Country'
END

INSERT INTO CustomerLogs
VALUES(@CustomerId, @Action)
END

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 Find Error Records in A Table

clock September 28, 2015 17:10 by author Rebecca

SQL Server database files are organized in 8KB (8192 bytes) chunks, called pages. When we create the first row in a table, SQL Server allocates an 8KB page to store that row. Similarly every row in every table ends up being stored in a page.

Say one of the pages in your table is corrupt and while repairing the corrupt pages, you may eventually end up loosing some data. You may want to find out which records are on the page. To do so, use the following undocumented T-SQL %%physloc%% virtual column:

USE AdventureWorks2014
GO
SELECT *, %%physloc%% AS physloc
FROM Person.AddressType
ORDER BY physloc;

As you can see, the last column represents the record location. However the hexadecimal value is not in a human readable format. To read the physical record of each row in a human readable format, use the following query:

SELECT *
FROM Person.AddressType
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)


The sys.fun_PhysLocCracker function takes the %%physloc%% and represents a human readable format fileid, pageid i.e. 880 and record number on the page 880.

If you are interested in knowing what’s inside the sys.fn_PhysLocCracker function, use sp_helptext as follows:

EXEC sp_helptext 'sys.fn_PhysLocCracker'
which display the definition of sys.fn_PhysLocCracker
-------------------------------------------------------------------------------
-- Name: sys.fn_PhysLocCracker
--
-- Description:
--    Cracks the output of %%physloc%% virtual column
--
-- Notes:
-------------------------------------------------------------------------------
create function sys.fn_PhysLocCracker (@physical_locator binary (8))
returns @dumploc_table table
(
    [file_id]    int not null,
    [page_id]    int not null,
    [slot_id]    int not null
)
as
begin
    declare @page_id    binary (4)
    declare @file_id    binary (2)
    declare @slot_id    binary (2)
    -- Page ID is the first four bytes, then 2 bytes of page ID, then 2 bytes of slot
    --
    select @page_id = convert (binary (4), reverse (substring (@physical_locator, 1, 4)))
    select @file_id = convert (binary (2), reverse (substring (@physical_locator, 5, 2)))
    select @slot_id = convert (binary (2), reverse (substring (@physical_locator, 7, 2)))
   
    insert into @dumploc_table values (@file_id, @page_id, @slot_id)
    return
end

The undocumented sys.fn_PhysLocCracker works on SQL Server 2008 and above.

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 Check Database Size in SQL Server 2014?

clock September 22, 2015 11:26 by author Peter

Sometimes we'd like to understand how much space utilized by databases in SQL Server. There are multiple ways that to know the database size in SQL SERVER.
1. using Table Sys.master_files
2. using stored Proc sp_spaceused
3. using Manual option in SSMS

Using Table Sys.master_files
This is one option by that we will know database size. Below query uses 2 tables databases that contains database ID, Name etc and another table master_files which contains size columns holds size of database. By using Inner join(database ID) we are getting database size. each tables are present in master database.
SELECT     sys.databases.name, 
           CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Total disk space] 
FROM       sys.databases  
JOIN       sys.master_files 
ON         sys.databases.database_id=sys.master_files.database_id 
GROUP BY   sys.databases.name 
ORDER BY   sys.databases.name


See the following picture after executing above code which gives all the databases with their sizes.

Using stored Proc sp_spaceused 

This  is second choice to recognize database size. Here we are going to call stored procedure sp_spaceused which is present in master database. This one helps to know size of current database.
exec sp_spaceused  

After calling above stored procedure it shows  below Image2 which contains column called database_size surrounded by red mark.

Using Manual Option in SSMS
This is another option to know database size. To know size Go to Server Explorer -> Expand it -> Right click on Database -> Choose Properties -> In popup window choose General tab ->See Size property which is marked(red) in Image3.

Image 3: Manual option to get Database size
Hope it helps you to get database size in SQL Server!

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 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.



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