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.

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:

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

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.

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.

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:

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

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.

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:

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:

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:

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.

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.

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.

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
)

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.

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

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 Name_in_Full=@Name_in_Full,
Employee_Number=@Employee_Number,
Designation=@Designation, Salary=@Salary where ID_Number=@ID_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.

In this article I will make tutorial about how to make read, write and delete cookies in ASP.NET WebPages 3.1. Cookies are a small text file containing information about a user; for example, a cookie may contain information on the last video the user watched or what items they have in their shopping cart. Many e-commerce websites like Amazon save cookies to your computer that can either collect your data or store the items in the shopping cart.

In Microsoft WebMatrix create a new website based of the Personal Site template name the website Cookies. Next in the root folder create a page called Settings.cshtml.

Writing a Cookie

Writing, reading and deleting a cookie are all fairly simple, as will be displayed in this tutorial. We will give the user an option to choose between two site layouts, and once they select which one they prefer a cookie will be saved to their computer. This will be read by the main master page and it will load the correct style sheet.

Writing Cookie Syntax

Response.Cookies["Cookie Name"].Value = "Value";
Response.Cookies["Cookie Name"].Expires = DateTime.Now.Add (Either, days, months, years, minutes etc);
If you do not specify when the cookie expires then it will automatically expire at the end of the session (when the user closes their browser).

In the Settings page insert this code:

C# Code:

@{

if (IsPost){   

    var layout = Request["layout"];

    if (layout == "Dark"){
         Response.Cookies["Theme"].Value = "Dark";
         Response.Cookies["Theme"].Expires = DateTime.Now.AddYears(1);    
    }
  }

}

Here we have var layout which requests the HTML control layout. After the user is given an option, if they choose Dark a cookie with the name Theme and value Dark will be saved to their browser, and it expires in one year.

HTML Code:

<form method="post">

<fieldset>
<legend><h4>Choose Layout</h4></legend>
    <label>Select Layout</label>

    <select name="layout">
    <option value="Dark">Dark</option>
    <option value="Default">Default</option>
    </select>

    <input type="submit" />
</fieldset>
</form>
Leave the option default for now because we will come back to it. Now run the page and select Dark as your theme; the page will refresh. Then in your chosen browser go to your stored cookies and you should see that a new cookie has been made called Theme (this should be under a folder called localhost by default) with the value Dark. For chrome users simply copy and paste this in the address bar of your browser: chrome://chrome/settings/cookies.

Reading Cookies

The domain which inserts the cookie can read the cookie. So for example, if thecodingguys.net writes a cookie called "Theme" Google.com cannot read that cookie. This will apply to sub-domains as well; m.thecodingguys.net cannot read a cookie which was created by www.thecodingguys.net.
Now the cookie should be inserted in your browser. Before we can read the cookie we need to make a new layout, so we will make a new style sheet. In the content folder copy the Site.css file and paste it in that folder, and re-name it to Site_Two.css. Next open the Site_Two.css find the body style and change the background colour to a colour of your choice (make sure it is different than the default).

Navigate to the layouts folder and open the _SIteLayout.cshtml file. Find the linked style sheet (below title tag) and replace it with:

C# Code:

@if (Request.Cookies["Theme"] != null){//MAKE SURE COOKIE EXIST

    if (Request.Cookies["Theme"].Value == "Dark"){//READ COOKIE VALUE
         <link href="~/Content/Site_Two.css" rel="stylesheet" />
    }
}else{
  <link href="~/Content/Site.css" rel="stylesheet" />   //IF COOKIES DOES NOT EXIST LOAD DEFAULT STYLESHEET
}
The first If Statement requests the cookie by name, not the cookie value but just the cookie named Theme. We need to make sure it is not null (meaning it exists), and then if it exists read the value. If the value is Dark, and it will load the second style sheet. Now if the cookie is not there it will load the default style sheet.

Deleting Cookies

Return to the Settings.cshtml file and add this to the If Statement:

C# Code:

else if (layout == "Default"){
         Response.Cookies["Theme"].Expires = DateTime.Now.AddYears(-1);
    }
Deleting a cookie is simple: on submit if the user selects the Default option the cookie is deleted. There is no direct command to delete the cookie, all we do is set the expiry date to a previous date. In this case it is set to the year before meaning it has expired and the browser will delete it.

Summary

Reading, writing and deleting cookies is quite simple. Cookies are very useful when you want to have full control overs users and provide extra functionality. Remember that cookies are client sided so users can disable them if they want to. Also remember that there are some laws (EU Law) regarding cookies, so make sure you inform users you are saving cookies.

HostForLIFE.eu ASP.NET WebPages 3.1 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.

In this tutorial, I will explain how to fix unable to communicate with the runtime for ‘R’ script. Please check the requirements of ‘R’ runtime, Fatal error: cannot create ‘R_TempDir’. R is the most popular programming language for advanced analytics today. You can use it for statistical analysis, uncover patterns, identify trends or even build predictive models. R has a massive collection of free packages on CRAN created by an ever-growing worldwide developer community.

In the latest SQL Server 2016 CTP3 release, new native in-database support for open source R and multi-threaded RevoScaleR functions are now available via R Services. SQL Server R Services provides a platform for using the powerful R language and package portfolio with a simple Transact-SQL interface. To overcome open source R’s performance and scale limitations, Enterprise Edition includes RevoScaleR features.

Data scientists, BI developers and applications can make parameterized calls to the R runtime from SQL code or stored procedures to get R computed result sets or data visualizations. In SQL Server 2016 CTP3, support for ad-hoc execution of R scripts via a new system stored procedure, sp_execute_external_script, was added. It will currently support pushing data from a single SELECT statement with multiple input parameters to R and return a single data frame as output.

• Locate launcher config file (this is used by the MSSQL Launch pad service)
  
  • Its normally located in the Binn folder instance the which is available in the root directory in my case D: \ Program Files \ Microsoft SQL Server \ MSSQL 13.MSSQLSERVER \ MSSQL \ Binn
• View the contents of rlauncher and check the working directory of the which is where we are having issues creating temp dir.
  
  • R does not like spaces in the working directory
    

  

• Create a folder in C:\ called temp “C:\temp”
• Change the working directory to c:\temp
  

• Grant permissions for the service MSSQLaunchpad service account in my case its NT Service\MSSQLaunchpad
  



Restart the SQL server services and SQL Launch pad services.
We can also fix this by using short paths by replacing the working directory with a short path. Example below cmd will give us the short path for the location cmd /c for %A in (“D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\ExtensibilityData”) do @echo %~sA


 

 

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.