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 :: Calculate Total Rows in SQL Server 2014

clock June 24, 2016 22:15 by author Anthony

In this tutorial, I will write an article about How to Calculate Total Rows Inserted per Second in SQL Server 2014. Ever expected to compute the quantity of columns embedded consistently, for each table in every database on a server? Alternately, have you ever expected to approve that all methods have quit keeping in touch with tables? These sorts of inquiries come up routinely for me. To help with this, I've composed the following script, which inspects metadata qualities utilizing sys.partitions. This system isn't as precise as running SELECT COUNT(*) FROM, however its much quicker.

Remember, since it’s just looking at row counts, its very little help on tables that have a considerable measure of update/delete  action. Yet it does what I need it to do, and I utilize it pretty frequently, so I thought I'd experience case any other individual can advantage from it as well.

/* Declare Parameters */

DECLARE @newBaseline BIT = 1 -- change to 0 when you don't want to replace the baseline, i.e. after initial run
  , @delay CHAR(8) = '00:00:30'; -- change as needed 
IF @newBaseline = 1
BEGIN
    IF OBJECT_ID('tempdb..#baseline') IS NOT NULL
        DROP TABLE #baseline; 
    CREATE TABLE #baseline
    (
         database_name  SYSNAME
       , table_name     SYSNAME
       , table_rows     BIGINT
       , captureTime    DATETIME NULL
    );
END 

IF OBJECT_ID('tempdb..#current') IS NOT NULL
    DROP TABLE #current;
 CREATE TABLE #current
(
     database_name  SYSNAME
   , table_name     SYSNAME
   , table_rows     BIGINT
   , captureTime    DATETIME NULL
); 
IF @newBaseline = 1
BEGIN
    EXECUTE sp_MSforeachdb 'USE ?;
        INSERT INTO #baseline
        SELECT DB_NAME()
            , o.name As [tableName]
            , SUM(p.[rows]) As [rowCnt]
            , GETDATE() As [captureTime]
        FROM sys.indexes As i
        JOIN sys.partitions As p
            ON i.[object_id] = p.[object_id]
           AND i.index_id  = p.index_id
        JOIN sys.objects As o
            ON i.[object_id] = o.[object_id]
        WHERE i.[type] = 1
        GROUP BY o.name;' 
    WAITFOR DELAY @delay;
END
 EXECUTE sp_MSforeachdb 'USE ?;
INSERT INTO #current
SELECT DB_NAME()
    , o.name As [tableName]
    , SUM(p.[rows]) As [rowCnt]
    , GETDATE() As [captureTime]
FROM sys.indexes As i
JOIN sys.partitions As p
    ON i.[object_id] = p.[object_id]
   AND i.index_id  = p.index_id
JOIN sys.objects As o
    ON i.[object_id] = o.[object_id]
WHERE i.[type] = 1
GROUP BY o.name;' 
SELECT  c.*
      , c.table_rows - b.table_rows AS 'new_rows'
      , DATEDIFF(second, b.captureTime, c.captureTime) AS 'time_diff'
      , (c.table_rows - b.table_rows) / DATEDIFF(second, b.captureTime, c.captureTime) AS 'rows_per_sec'
FROM #baseline AS b
JOIN #current AS c
    ON b.table_name = c.table_name
   AND b.database_name = c.database_name
ORDER BY new_rows DESC;

 

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. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.



SQL Server Hosting - HostForLIFE.eu :: Repair SQL Database From Suspect Mode

clock June 14, 2016 20:11 by author Anthony

Sometimes we have to face a critical situation when SQL Server database going to Suspect Mode. In that moment no work can be done on database. Database may go into suspect mode because the primary file group is damaged and the database cannot be recovered during the startup of the SQL Server
Reason for database to go into suspect mode:

Free ASP.NET Hosting - Europe

  • Data files or log files are corrupt.
  • Database server was shut down improperly
  • Lack of Disk Space
  • SQL cannot complete a rollback or roll forward operation

    
How to recover database from suspect mode:

  • Change the status of your database. Suppose database name is “BluechipDB”?

EXEC sp_resetstatus '';
Example:
EXEC sp_resetstatus 'BlueChipDB'

  • Set the database in “Emergency” mode

ALTER DATABASE  SET EMERGENCY;
Example:
ALTER DATABASE BlueChipDB SET EMERGENCY

  • Check the database for any inconsistency

DBCC CHECKDB('');

Example:
DBCC checkdb('BlueChipDB')

If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you need not execute the following query.

ALTER DATABASE  SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Example:
ALTER DATABASE BlueChipDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  • For safety, take the backup of the database.
  • Run the following query as next step.Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is once the database is repaired all the actions performed by these queries can’t be undone.
  • There is no way to go back to the previous state of the database.
  • So as a precautionary step you should take backup of your database in step 5 mentioned above.

DBCC CHECKDB ('', REPAIR_ALLOW_DATA_LOSS);

Example:
DBCC CheckDB ('BlueChipDB', REPAIR_ALLOW_DATA_LOSS)

  • Finally, bring the database in MULTI USER mode

ALTER DATABASE  SET MULTI_USER;
ALTER DATABASE [BlueChipDB]  SET MULTI_USER

  • Refresh your database server and verify the connectivity of your database. Now users should be able to connect to the database properly. If any data loss, you can restore database – backup taken in step 5.

 

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.



SQL Server Hosting - HostForLIFE.eu :: SQL Server Inline Queries

clock June 7, 2016 21:45 by author Anthony

In this tutorial, I will explain about inline queries. Most of the developers are very familiar with inline queries. Inline queries are basically known as sub queries or Inner Select statements which are always used inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another inline query.  They can be used anywhere in SQL scripts to encounter the conditional expressions. The SQL statement containing an Inline query is also known an outer query or outer select.

Free ASP.NET Hosting - Europe

How can Inline Queries helpful?

Most of the T-SQL statements which include inline queries can be alternatively formulated as join to encounter the conditional expressions. Other questions can be posed only with inline queries. In T-SQL, there is usually no performance difference between a statement that includes an inline query and a semantically equivalent version that does not. However, in some cases where existence must be checked, a join yields better performance.

Components of an Inline Query

An inline query has the same features as a normal SQL query. They only persist in the SQL statements and could have the following components –

  • A regular SELECT query including regular select list components from the main table.
  • A regular FROM clause including one or more table, function or view names.
  • An optional WHERE clause to encounter the conditional expressions.
  • An optional GROUP BY clause if your query contains aggregation functions.
  • An optional HAVING clause if your query contains aggregation functions.

Points to be remember

The SELECT query of an Inline query is always enclosed in parentheses. It cannot include a COMPUTE or FOR BROWSE clause, and may only include an ORDER BY clause when a TOP clause is also specified. There are three basic types of inline queries-
Operate on lists introduced with IN, or those that a comparison operator modified by ANY or ALL.
Are introduced with an unmodified comparison operator and must return a single value.
Are existence tests introduced with EXISTS.

To understand their functionality, we will create a table variable to work as the base table for the inline statements as given below-

---- declare OrderMaster table variable body
DECLARE @OrderMaster TABLE
(
OrdId INT IDENTITY(100,1),
OrdDate VARCHAR(30),
CustName VARCHAR(20)
)
---- declare OrderDetails table variable body
DECLARE @OrderDetails TABLE
(
OrdId INT,
ItemId VARCHAR(30),
ItemName VARCHAR(20),
SellingPrice Decimal(12,4)
)
---- Insert Values
INSERT INTO @OrderMaster (OrdDate,CustName)
Values (Getdate(),'Ryan Arjun'),(Getdate(),'Bill Trade'),
(Getdate(),'Rosy White')

---- Pull Order Data
SELECT * FROM @OrderMaster

----Fill Order Details
Insert Into @OrderDetails (OrdId, ItemId, ItemName, SellingPrice)
Values (100, 201,'Apple',135.78),(100, 202,'Mango',235.78)
,(101, 203,'Banana',124.50),(101, 204,'Orange',321.15)
,(102, 205,'Banana',124.50),(102, 204,'Orange',321.15),(102, 201,'Apple',135.78)

--- Pull Order Details
select * from @OrderDetails

Now, we have the tables and want to pull the order and customer wise total sales.

Pull Single Value

There are many ways to do this but we are using inline query to accomplish this task. To pull the single value, we are using inline query within the main SQL statement as given below-

---- Use inline query in the select statement
select OrdId, CustName,
---- Inline query for single value
[Price] =(Select 
[Price]=sum(SellingPrice)
from @OrderDetails
where OrdId=  Om.OrdId
)
from @OrderMaster OM


Pull Multiple Values with Group By
If we want to pull more than one column then inline query should work as join with the main query as given below:

---- Inline Query as Join
SELECT Om.OrdId, Om.CustName,
OD.[Item-Qty], OD.Price
FROM @OrderMaster OM
JOIN
(
SELECT  OrdId,
[Item-Qty]=COUNT(ItemId),
[Price]=SUM(SellingPrice)
FROM @OrderDetails
GROUP BY ORDID
)OD
ON OM.ORDID=OD.ORDID


Pull Value with Where Clause and Group By
If we want to pull more than one column based on some conditional expression then inline query should work as join with the main query as given below:

---- Inline Query as Join
SELECT Om.OrdId, Om.CustName,
OD.[Item-Qty], OD.Price
FROM @OrderMaster OM
JOIN
(SELECT  OrdId,
[Item-Qty]=COUNT(ItemId),
[Price]=SUM(SellingPrice)
FROM @OrderDetails
---- Conditional Expression
WHERE ItemName='Apple'
GROUP BY ORDID
)OD
ON OM.ORDID=OD.ORDID

Conclusion

It’s very beneficial concept in SQL and we can use them inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another inline query.  They are easily applicable in the function and stored procedure. These features are important in some Transact-SQL statements; the inline-query can be evaluated as if it were an independent query. Conceptually, the inline-query results are substituted into the outer query.

 

HostForLIFE.eu SQL Server 2016 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 :: Error While Saving Table in SQL Server

clock May 31, 2016 20:46 by author Anthony

That happens because sometimes it is necessary to drop and recreate a table in order to change something. This can take a while, since all data must be copied to a temp table and then re-inserted in the new table. Since SQL Server by default doesn't trust you, you need to say "OK, I know what I'm doing, now let me do my work." This problem occurs when the Prevent saving changes that require the table re-creation option is enabled, and you make one or more of the following changes to the table:

Free ASP.NET Hosting - Europe

  • You change the Allow Nulls setting for a column.
  • You reorder columns in the table.
  • You change the column data type.
  • You add a new column.

When you change a table so that you alter the metadata structure of the table, and then you save the table, the table must be re-created based on these changes. This may result in the loss of metadata and in a direct loss of data during the re-creation of the table. If you enable the Prevent saving changes that require the table re-creation option in the Designer section of the SQL Server Management Studio (SSMS) Options window, you receive the error message that is mentioned in the “Symptoms” section.

To change the Prevent saving changes that require the table re-creation option, follow these steps:

  • Open SQL Server Management Studio (SSMS).
  • On the Tools menu, click Options.
  • In the navigation pane of the Options window, click Designers.
  • Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.

Note If you disable this option, you are not warned when you save the table that the changes that you made have changed the metadata structure of the table. In this case, data loss may occur when you save the table.

Risk of turning off the “Prevent saving changes that require table re-creation” option

Although turning off this option can help you avoid re-creating a table, it can also lead to changes being lost. For example, suppose that you enable the Change Tracking feature in SQL Server to track changes to the table. When you perform an operation that causes the table to be re-created, you receive the error message that is mentioned in the “Symptoms” section. However, if you turn off this option, the existing change tracking information is deleted when the table is re-created. Therefore, we recommend that you do not work around this problem by turning off the option.

To determine whether the Change Tracking feature is enabled for a table, follow these steps:

  • In SQL Server Management Studio, locate the table in Object Explorer.
  • Right-click the table, and then click Properties.
  • In the Table Properties dialog box, click Change Tracking.

If the value of the Change Tracking item is True, this option is enabled for the table. If the value is False, this option is disabled.

When the Change Tracking feature is enabled, use Transact-SQL statements to change the metadata structure of the table.

Steps to reproduce the problem

  • In SQL Server Management Studio, create a table that contains a primary key in the Table Designer tool.
  • Right-click the database that contains this table, and then click Properties.
  • In the Database Properties dialog box, click Change Tracking.
  • Set the value of the Change Tracking item to True, and then click OK.
  • Right-click the table, and then click Properties.
  • In the Table Properties dialog box, click Change Tracking.
  • Set the value of the Change Tracking item to True, and then click OK.
  • On the Tools menu, click Options.
  • In the Options dialog box, click Designers.
  • Click to select the Prevent saving changes that require table re-creation check box, and then click OK.
  • In the Table Designer tool, change the Allow Nulls setting on an existing column.
  • Try to save the change to the 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 Hosting - HostForLIFE.eu :: SQL Server Event Alerts

clock May 24, 2016 18:34 by author Anthony

In this article, we will discuss about SQL Server Alert and conditional responses. SQL Server event alerts that respond by notifying operators can lead to too much noise in your email inbox. There is a built-in option to specify a number of minutes to delay between responses. For many, that setting alone is sufficient to keep the spam down to a minimum. But events may still occur during that "delay between responses" window. And SQL Server will fire alerts for them. You may not always want the response suppressed.

Free ASP.NET Hosting - Europe

SQL Server provides another way to more selectively respond to alerts: by executing a SQL Agent job, and using tokens within the job steps. To use tokens, you'll need to enable their use. Open SQL Server Agent Properties, select the Alert System page, and check the "Replace tokens for all job responses to alerts" checkbox as seen here:

Now let's look at a typical alert. The one pictured below raises alerts for errors with a severity level of 20. On the Response page, we see the alert is configured to respond by executing a job. (From this dialog you can choose an existing job, or click to create a new one.) The Notify Operators option is left unchecked.

Within the "DBA-Catch Severity 20 Alert" SQL Agent job, T-SQL job steps that use tokens can be created. Here is an example:

DECLARE @Descr VARCHAR(MAX) = '$(ESCAPE_SQUOTE(A-MSG))';

 

IF @Descr LIKE '%CLIENT: 127.0.0.1%'

BEGIN

    --Do nothing.

    PRINT 'Alert originating from pen test server.'

END

ELSE

BEGIN

    DECLARE @To NVARCHAR(MAX) = '[email protected]';

    DECLARE @Subj NVARCHAR(255);

    DECLARE @EmailBody NVARCHAR(MAX);

    DECLARE @Date VARCHAR(MAX) = '$(ESCAPE_SQUOTE(DATE))';

    DECLARE @Time VARCHAR(MAX) = '$(ESCAPE_SQUOTE(TIME))';

 

    SET @Subj = 'SQL Server Alert System: ''20-Fatal Error in Current Process''' +

        'occurred on ' + @@SERVERNAME

    SET @Date = SUBSTRING(@Date, 5, 2) + '/' + RIGHT(@Date, 2) + '/' + LEFT(@Date, 4);

    SET @Time = LEFT(@Time, 2) + ':' + SUBSTRING(@Time, 3, 2) + ':' + RIGHT(@Time, 2);

    SET @EmailBody = 'DATE/TIME: ' + @Date + ' ' + @Time + '

 

DESCRIPTION: ' + @Descr + '

COMMENT: (None)

JOB RUN: (None)'

    EXEC msdb..sp_send_dbmail

        @recipients = @To,

        @subject = @Subj,

        @body = @EmailBody

END


There are three tokens within the T-SQL (highlighted in yellow above): A-MSG, DATE, and TIME. SQL server replaces these three tokens as follows:

 

  • A-MSG: Message text. If the job is run by an alert, the message text value automatically replaces this token in the job step.

 

  • DATE: Current date (in YYYYMMDD format).

 

  • TIME: Current time (in HHMMSS format).

 

Note the conditional logic, checking the error message. If it originates from a server with a particular IP address, no action is taken. Otherwise, an email is sent with the relevant error information. (This is a real-world example involving recurring penetration tests. The pen test server conducted port scans on the SQL host and attempted numerous connections as [sa]. I got an onslaught of unwanted email.)

Tokens are a powerful tool that give you a lot of flexibility. This very simple example just scratches the surface of what is possible. Use your imagination. Get creative. Happy alerting!

 

 



SQL 2016 Hosting - HostForLIFE.eu :: How to Use Concat Function in SQL 2016?

clock May 3, 2016 00:52 by author Anthony

In this tutorial, I will explain how to use concat function in SQL 2016. CONCAT function is also known as T-SQL Function which was introduced in SQL Server 2012. This function allows us to concatenate two or more parameters values together and these parameters values should be separated by comma. Before release of Concat function, we used to use the “+” operator to combine or concatenate two or more string values. The most important feature of Concat function is that it also takes care of data type conversion and beautifully handles NULL on its own. In case of Concat function, we don't need to care about the null values in the parameters which are going to be used.

Syntax

CONCAT ( string_value1, string_value2 [, string_valueN ] )

Arguments

string_value: A string value to concatenate to the other values.

Return Types: String, the length and type of which depend on the input.

 

Interesting features

1. CONCAT takes a variable number of string arguments and concatenates them into a single string.

2. It requires at least two input values; otherwise, an error is raised.

3. All arguments are implicitly converted to string types and then concatenated.

4. Best part of this; Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned.

5. The implicit conversion to strings follows the existing rules for data type conversions. 

6. If none of the input arguments is of a supported large object (LOB) type, then the return type is truncated to 8000 in length, regardless of the return type. This truncation preserves space and supports efficiency in plan generation.

Examples:

Concatenate Space Characters with input variables

 

To understand the features of Concat function, we are showing the examples with Concat function and without the concat functions as given below:

---- Declare local variables

Declare @inpFirstName Varchar(20) ='John'

Declare @inpLastName Varchar(20) ='Carter'

 

---- Concatenating variables without Concat Function

Select @inpFirstName+' '[email protected] as FullName

FullName

John Carter

---- Concatenating variables with Concat Function

Select CONCAT(@inpFirstName,' ',@inpLastName) as FullName

FullName

John Carter

 

In the above example, we are using the two variables to concatenating together. You can see that, we are getting the same outputs. 

 

Concatenate Space Characters with with NULL values

Now, we are going to make some interesting changes in the above example by setting the null value in the second variable as given below:

---- Declare local variables

Declare @inpFirstName Varchar(20) ='John'

---- Set Null Value here

Declare @inpLastName Varchar(20) =NULL

 

---- Concatenating variables without Concat Function

Select @inpFirstName+' '[email protected] as FullName

FullName

NULL

---- Concatenating variables with Concat Function

Select CONCAT(@inpFirstName,' ',@inpLastName) as FullName

FullName

John

You can easily see that full name is showing NULL values in case of without Concat function but by using Concat function, full name is showing because of Null values are implicitly converted to an empty string.

Concatenate number and string together

If you want to concatenate string with numeric value then you should need to convert the numeric value into the string as given below:

---- Declare local variables

Declare @inpFirstName Varchar(20) ='John'

Declare @inpContactNumber BIGINT =1234567890

 

---- Concating variables without Concat Function

Select @inpFirstName+':'+CAST(@inpContactNumber as VarcharasNameWithContact

 

NameWithContact

John :1234567890

---- Concatenating variables with Concat Function

Select CONCAT(@inpFirstName,':',CAST(@inpContactNumber as Varchar))as NameWithContact

 

NameWithContact

John:1234567890

If you want to concatenate two numeric values then there is no need to change their data types because of implicit conversion to strings follows the existing rules for data type conversions.

Conclusion

The CONCAT function appends one string to the end of another string and does not require ISNULL for converting NULL into an empty string. All arguments are implicitly converted to string types and then concatenated.

 

 

 

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

 

 



SQL Server Hosting - HostForLIFE.eu :: Rename SQL Server Database

clock April 28, 2016 21:34 by author Anthony

Database Administrators usually use the sp_renamedb system stored procedure to quickly rename a SQL Server Database. However, the drawback of using sp_renamedb is that it doesn't rename the Logical and Physical names of the underlying database files. It's a best practice to make sure the Logical Name and Physical File Name of the database is also renamed to reflect the actual name of the database to avoid any confusion with backup, restore or detach/attach operations.

Let's first create a new database named CoreDB using the T-SQL below:

USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'CoreDB')
DROP DATABASE CoreDB
GO
USE master
GO
CREATE DATABASE [CoreDB]
ON PRIMARY
(
NAME = N'CoreDB',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\CoreDB.mdf' ,
SIZE = 2048KB ,
FILEGROWTH = 1024KB
)
LOG ON
(
NAME = N'CoreDB_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\CoreDB_log.ldf' ,
SIZE = 1024KB ,
FILEGROWTH = 10%
)
GO

Rename CoreDB Database Using sp_renamedb System Stored Procedure

Now let's rename the CoreDB database to ProductsDB by executing the below T-SQL code.

USE master
GO
ALTER DATABASE CoreDB
SET SINGLE_USER

WITH ROLLBACK IMMEDIATE
GO
EXEC master..sp_renamedb 'CoreDB','ProductsDB'
GO
ALTER DATABASE ProductsDB
SET MULTI_USER
GO

Once the above T-SQL has executed successfully the database name will change however the Logical Name and File Name will not change. You can verify this by executing the T-SQL below:

USE master
GO
/* Identify Database File Names */
SELECT

name AS [Logical Name],
physical_name AS [DB File Path],
type_desc AS [File Type],
state_desc AS [State]
FROM sys.master_files
WHERE database_id = DB_ID(N'ProductsDB')
GO

Your output should look something like this from the above query.

You can see in the above snippet that the Logical Name and File Name in the DB File Path column for ProductsDB are still reflecting the old name of CoreDB. This is not a good practice to follow in a Production Environment. Below you will see the steps which a DBA can follow to rename the database and its respective files.

Steps to Rename a SQL Server Database

DBAs should follow the below steps which will not only rename the database, but at the same time will also rename the Logical Name and File Name of the database.

This first set of commands put the database in single user mode and also modifies the logical names.


/* Set Database as a Single User */
ALTER DATABASE CoreDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
/* Change Logical File Name */
ALTER DATABASE [CoreDB] MODIFY FILE (NAME=N'CoreDB', NEWNAME=N'ProductsDB')
GO
ALTER DATABASE [CoreDB] MODIFY FILE (NAME=N'CoreDB_log', NEWNAME=N'ProductsDB_log')
GO

This is the output from the above code.


Now we need to detach the database, so we can rename the physical files.  If the database files are open you will not be able to rename the files.

/* Detach Current Database */
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'CoreDB'
GO

Once the CoreDB database is detached successfully then the next step will be to rename the Physical Files. This can be done either manually or by using the xp_cmdshell system stored procedure. You can enable xp_cmdshell feature using the sp_configure system stored procedure.

USE master
GO
sp_configure 'show advanced options'
GO
/* 0 = Disabled , 1 = Enabled */
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO

Once xp_cmdshell is enabled you can use the below script to rename the physical files of the database.

/* Rename Physical Files */
USE [master]
GO
EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\
MSSQL\DATA\CoreDB.mdf", "ProductsDB.mdf"'
GO
EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\
MSSQL\DATA\CoreDB_log.ldf", "ProductsDB_log.ldf"'
GO

Once the above step has successfully executed then the next step will be to attach the database, this can be done by executing the T-SQL below:

/* Attach Renamed ProductsDB Database Online */
USE [master]
GO
CREATE DATABASE ProductsDB ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB_log.ldf' )
FOR ATTACH
GO

Once the above step has successfully executed then the final step will be to allow multi user access for the user database by executing the below T-SQL:

/* Set Database to Multi User*/
ALTER DATABASE ProductsDB SET MULTI_USER
GO

You can verify the Logical and File Names for the ProductsDB database by executing the T-SQL below:

USE master
GO
/* Identify Database File Names */
SELECT
name AS [Logical Name],
physical_name AS [DB File Path],
type_desc AS [File Type],
state_desc AS [State]
FROM sys.master_files
WHERE database_id = DB_ID(N'ProductsDB')

 

HostForLIFE.eu SQL Server 2012 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. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.



SQL Server 2012 Hosting - HostForLIFE.eu :: SQL Server 2012 Database Fixing

clock April 26, 2016 00:35 by author Anthony

In this article, we will see about fixed database roles in SQL Server. As the name suggests, fixed database roles cannot be removed or modified and performs specific administrative tasks as it has pre-defined set of permissions. You need to be very careful while assigning these roles and assign these roles only when there is a serious requirements. You can execute the sp_helpdbfixedrole system procedure to get the list of fixed database roles.

EXEC sp_helpdbfixedrole
GO

  • db_owner
  • db_accessadmin
  • db_securityadmin
  • db_ddladmin
  • db_backupoperator
  • db_datareader
  • db_datawriter
  • db_denydatareader
  • db_denydatawriter

You can navigate to Database Roles from Object Explorer –> Expand Databases Node –> Select Database –> Navigate to Security –> Roles –> Database Roles


db_owner

All the members of db_owner fixed database roles can perform all the maintenance activities and setting configurations. This role should not be given to regular users and should be assigned very carefully as it can perform almost all the operations in a database.

db_accessadmin

As the name suggests, all the members of db_accessadmin can handle access related issues. This role controls security, grants access, revokes access for logins to enter database. This role is rarely used as these operations are performed by DBAs as he has relevant fixed server role.

db_securityadmin

Members of db_securityadmin role manages all the permissions and security related activities. As database administrators usually manages security, permissions, role membership etc. so this role is hardly assigned and used. You should not assign this role to regular users.

db_ddladmin

If any member is assigned with db_ddladmin role then he can perform all DDL operations and can execute, create, drop and alter any objects. Normally this role is assigned to developers to perform the related operations in application. This role is usually not assigned to regular users as he can misuse the DDL operations.

db_backupoperator

db_backupoperator role can perform the database backup operations. This role is rarely used as backup activity is a role of database administrator and he has much higher permissions rather than using this specific fixed database role.

db_datareader

If any user is requesting for SELECT permission on database tables then you can tag him with db_datareader fixed database role as this role allows a member to perform SELECT operations on database tables and views and tagged member will not be able to modify any object. This role is mostly assigned to developers and regular users who need table access on production database.

db_datawriter

All the members of db_datawriter fixed database role can perform INSERT, UPDATE, DELETE operations on all tables and views in respective database. This role is basically assigned to developers to perform operations on QA servers. Testing applications sometimes require this role.

db_denydatareader

As the name suggests this role doesn’t allow to read data from tables in a database. So user will not be able to perform SELECT operations on a table or views. I haven’t used this role any time and also never seen members assigned with this role.

db_denydatawriter

This role is basically opposing db_datawriter role which means members with this role will not be able to perform INSERT, UPDATE or DELETE operations on tables and views. Again as a DBA I have not used this role yet and never seen anyone using this role.

 


HostForLIFE.eu SQL Server 2012 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. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

 



SQL 2016 Hosting - HostForLIFE.eu :: How to Make Time Converter in SQL Server 2016?

clock April 22, 2016 00:06 by author Anthony

In this tutorial, I will explain about how to make time converter. The time converter converts the most common time units, second, hour, minute, day, week, month, year and century. Second is the SI base unit of time and officially defined as the duration of 9 192 631 770 periods of the radiation corresponding to the transition between the two hyperfine levels of the ground state of the caesium 133 atom.

We have an integer number which represents seconds (secs) part of the time. We need to convert this integer number into year, month, days, hour, minute and second parts. Let me explain with the help of the examples to make it clear.

Examples:

100 Seconds – 0 year(s) 0 month(s) 0 day(s) 0 hour(s) 1 minute(s) 40 second(s)

3600 Seconds – 0 year(s) 0 month(s) 0 day(s) 1 hour(s) 0 minute(s) 0 second(s)

86400 Seconds – 0 year(s) 0 month(s) 1 day(s) 0 hour(s) 0 minute(s) 0 second(s)

2678400 Seconds – 0 year(s) 1 month(s) 0 day(s) 0 hour(s) 0 minute(s) 0 second(s)

35110011 Seconds – 1 year(s) 1 month(s) 10 day(s) 8 hour(s) 46 minute(s) 51 second(s)

etc.


Convert number into year, month, day, hour, minute and second

Below is the code to convert the input integer number into year, month, day, hour, minute and second as above:

DECLARE @VARDT DATETIME = DATEADD(SECOND, 35110011, 0)
SELECT CAST(DATEPART(YEAR, @VARDT) - 1900 AS VARCHAR(10)) + ' year(s) ' + CAST(DATEPART(MONTH, @VARDT) - 1 AS VARCHAR(2)) + ' month(s) '
+ CAST(DATEPART(DD, @VARDT) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
+ CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'


Or, we can use the below code.


DECLARE @VARDT DATETIME = DATEADD(SECOND, 35110011, 0)
SELECT CAST(DATEDIFF(YEAR, 0, @VARDT) AS VARCHAR(10)) + ' year(s) ' + CAST(DATEPART(MONTH, @VARDT) - 1 AS VARCHAR(2)) + ' month(s) '
 + CAST(DATEPART(DD, @VARDT) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
 + CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

I have added the given integer value in second part of the default datetime value (‘1900-01-01 00:00:00.000’) of SQL Server to generate a datetime value. Once we have a datetime data type, we can easily apply the DATEPART function to extract the required parts from that. In this scenario SQL Server automatically takes care of the date time conversions. Finally, I have subtracted the respective date time parts from the generated datetime data type value from their respective parts in the final SELECT statement wherever required.

In second logic, I have just used a DATEDIFF function for the first step computation which can take care of all the conversions specially in below scenarios smoothly. For example, we don’t need to worry about to year to month, month to day and day to hour conversion. We just need to use DATEDIFF function for the first part.

Convert number into month, day, hour, minute and second


SELECT CAST((DATEDIFF(MONTH, 0, @VARDT)) AS VARCHAR(10)) + ' month(s) '
+ CAST(DATEPART(DD, @VARDT) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
+ CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'


Output:

13 month(s) 10 day(s) 8 hour(s) 46 minute(s) 51 second(s)

Convert number into day, hour, minute and second


SELECT CAST((DATEDIFF(DD, 0, @VARDT)) AS VARCHAR(10)) + ' day(s) '
 + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
 + CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'


Output:

406 day(s) 8 hour(s) 46 minute(s) 51 second(s)

Convert number into hour, minute and second


SELECT CAST(DATEDIFF(HOUR, 0, @VARDT) AS VARCHAR(20)) + ' hour(s) '
 + CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'


Output:

9752 hour(s) 46 minute(s) 51 second(s)

Convert number into minute and second


SELECT CAST(DATEDIFF(MINUTE, 0, @VARDT) AS VARCHAR(25)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

 

 

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

 



SQL Server 2014 Hosting - HostForLIFE.eu :: How to Make Stored Procedure in SQL Server 2014?

clock April 14, 2016 21:10 by author Anthony

In this tutorial I will show you Stored Procedure usage with step by step. First of All, you have good knowledge about SQL Server  after that, you will understand as well. Basically, Stored Procedure which is used in SQL Server, SQL Server is the product of Microsoft. Stored Procedure is mostly used with Insert, Update and Delete Data in Your Tables. If you make a table and insert update and delete record a records you have used insert update and delete query simply  again and again but Stored Procedure different. Stored Procedure you make a one time simply after that you don’t use long insert update and delete query. Stored Procedure is very helpful to make a Web Application or Desktop Application with the help of ASP.NET.

How to Make a Stored Procedure

First of All, you make a table, for example, I have made a Table namely Employee and I have inserted five columns ID_Number, Name_in_Full, Designation and Salary in my table you are see below with query and screenshot.
Make a Table in SQL Server
create table employee
(
ID_Number int primary key identity(1,1),
Name_in_Full varchar(50),
Employee_Number varchar(50),
Designation varchar(50),
Salary int
)


Create-a-Table-in-SQL-Server

I have made a table  with you can see table query and display screen shot, after that I have inserted data in a table.
Insert into employee values
(‘Asif Ali’, ‘5050’, ‘Web Developer’, 250000),
(‘Aamir Ali’, ‘5051’, ‘Businessman’, 250000),
(‘Asadullah’, ‘5052’, ‘Network Engineer’, 150000),
(‘Sameer Ali’, ‘5053’, ‘Accountant’, 450000)

I have made the table and insert a record in the table.


Create Stored Procedure in SQL Server

 

I have made a Stored Procedure namely CreateProc and I have used same column name with I have already used in a table below you can see complete query and screenshot. 

create procedure createProc
(@Name_in_Full varchar(50),
@Employee_Number varchar(50),
@Designation varchar(50),
@Salary int)
as
begin
insert into employee values(@Name_in_Full, @Employee_Number, @Designation, @Salary)
end


After that I have made complete Stored Procedure after that I have inserted a data in Table via Stored Procedure, complete query and screen shot as below.

execute createProc ‘Sadam’, ‘50504’, ‘Chemical Engineer’, 25000

Afer Run above query my data is inserted in my table with the help of SP


Alter Stored Procedure in SQL Server

After that I have changed with column data type I have used with Alter Query and change my column DataType, the complete query, and screen shot as below.

alter procedure createProc(@Name_in_Full varchar(50),@Employee_Number nvarchar(50),
@Designation varchar(50),@Salary int)
as
begin
insert into employee values(@Name_in_Full, @Employee_Number, @Designation, @Salary)
end

Alter Stored Procedure in SQL Server

After that, I have made UpdateProcedure which is used with an update a record with your table with the help of SP, the query and screenshot as below.

Update Stored Procedure in SQL Server

create procedure updateProc
(@ID_Number int,
@Name_in_Full varchar(50),
@Employee_Number nvarchar(50),
@Designation varchar(50),
@Salary int)
as
begin
update employee set [email protected]_in_Full,
[email protected]_Number,
[email protected], [email protected] where [email protected]_Number
end

After that, I have update a Record with Table with the help of SP, the complete query, and screenshot as below.

exec updateProc 5, ‘Sadam Hussain’, ‘50504’, ‘Chemical Engineer’, 250000

 

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu revolutionized hosting with Plesk Control Panel, a Web-based interface that provides customers with 24x7 access to their server and site configuration tools. Plesk completes requests in seconds. It is included free with each hosting account. Renowned for its comprehensive functionality - beyond other hosting control panels - and ease of use, Plesk Control Panel is available only to HostForLIFE's customers. They
offer a highly redundant, carrier-class architecture, designed around the needs of shared hosting customers.



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