European Windows 2012 Hosting BLOG

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

SQL Server 2016 Hosting - HostForLIFE.eu :: SQL Server Important System Views and Tables

clock July 17, 2019 12:08 by author Peter

In this article I have listed few methods to know about the list of database, tables, views,etc.., It will be very useful when we trace the database objects in the query window. Even though it can be accessible in the sql server object explorer, but when we write the query it can be customized. That means it can filter the result set based on our requirement.

How to list out the available database in the SQL Server current connection?
Method 1
SP_DATABASES 

Method 2
SELECT name FROM SYS.DATABASES 

Method 3
SELECT name FROM SYS.MASTER_FILES 

Method 4
SELECT * FROM SYS.MASTER_FILES -- Type=0 for .mdf and type=1 for .ldf 
The sp_databases is a system stored procedure it can be listed the database with the size.
The sys.databases will list the databases, created date, modified date and database id along with the other information

The SYS.MASTER_FILES will query the database details like the database id, size, physical storage path and list both mdf and ldf.

How to list the user tables in the database?
The following method can be used to get the list of user tables in the SQL server.

Method 1
SELECT name FROM SYS.OBJECTS WHERE type='U' 

Method 2
SELECT NAME FROM SYSOBJECTS WHERE xtype='U' 

Method 3
SELECT name FROM SYS.TABLES 

Method 4
SELECT name FROM SYS.ALL_OBJECTS WHERE type='U' 

Method 5
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' 

Method 6
SP_TABLES 

How to list out the Stored Procedures in the database?
Method 1

SELECT name FROM SYS.OBJECTS WHERE type='P' 

Method 2
SELECT name FROM SYS.PROCEDURES 

Method 3

SELECT name FROM SYS.ALL_OBJECTS WHERE type='P'

Method 4
SELECT NAME FROM SYSOBJECTS WHERE xtype='P'

Method 5
SELECT Routine_name FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' 

The SYS.OBJECTS table has the common table that has the list for all the procedure, table, triggers, views,etc.., Here procedure can be filtered using the type='p'.

The Information_schema.routines is a view that has used in the SQL server 7.0 version. Now exclusive table available for the stored procedure.

How to list all Views in the database?
Method 1
SELECT name FROM SYS.OBJECTS WHERE type='V' 

Method 2
SELECT name FROM SYS.ALL_OBJECTS WHERE type='V' 

Method 3
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS 

Method 4
SELECT name FROM SYS.VIEWS

How to list out the Functions in the database?
Method 1

SELECT name FROM SYS.OBJECTS WHERE type='IF' -- inline function 

Method 2
SELECT name FROM SYS.OBJECTS WHERE type='TF' -- table valued function 

Method 3
SELECT name FROM SYS.OBJECTS WHERE type='FN' -- scalar function 

Method 4
SELECT name FROM SYS.ALL_OBJECTS WHERE type='IF' -- inline function 

Method 5
SELECT name FROM SYS.ALL_OBJECTS WHERE type='TF' -- table valued function 

Method 6
SELECT name FROM SYS.ALL_OBJECTS WHERE type='FN' -- scalar function

Method 7
SELECT Routine_name FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION'

Note: IF - Inlined Function, TF- Table valued function, FN- Scalar Function

How to get the Triggers in the database?
Method 1

SELECT * FROM SYS.TRIGGERS

Method 2
SELECT * FROM SYS.OBJECTS WHERE type='TR'

How to get the triggers in a table?
Method 1

SP_HELPTRIGGER Products

Method 2
SELECT * FROM SYS.TRIGGERS WHERE parent_id = object_id('products')

How to get the columns in a table?
Method 1

SP_HELP Products

Method 2
SP_COLUMNS Products

Method 3
SELECT * FROM SYS.COLUMNS WHERE object_id = object_id('Products')

Method 4
SELECT COLUMN_NAME,Ordinal_position,Data_Type,character_maximum_length 
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME='Products'

How to find the Columns in the table?
Method 1

SELECT O.name FROM SYS.OBJECTS O INNER JOIN SYS.COLUMNS C  
ON C.Object_ID =O.Object_ID  
WHERE C.name LIKE '%ShipName%'

Method 2

SELECT OBJECT_NAME(object_id) AS [Table Name]  
FROM SYS.COLUMNS  
WHERE name LIKE '%ShipName%'

Method 3
SELECT TABLE_NAME  
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE COLUMN_NAME LIKE '%ShipName%'

How to get the Total rows in the table?
Method 1

SELECT COUNT(@@ROWCOUNT) FROM Products

Method 2
SELECT COUNT (ProductID) FROM Products

Method 3
SELECT OBJECT_NAME(id) AS [Table Name],rowcnt  
FROM SYSINDEXES  
WHERE OBJECTPROPERTY(id,'isUserTable')=1 AND indid < 2  
ORDER BY rowcnt DESC

Method 4
SELECT rowcnt FROM sysindexes  
WHERE id = OBJECT_ID('Products') AND indid < 2

Method 5
SELECT OBJECT_NAME(OBJECT_ID) TableName,row_count  
FROM sys.dm_db_partition_stats  
WHERE object_id = object_id('Products') AND index_id < 2

How to get the Check Constraints in the database?
Method 1

SELECT * FROM SYS.OBJECTS WHERE type='C'

Method 2
SELECT * FROM sys.check_constraints

How to find the Indexes in the table?
Method 1

sp_helpindex Products

Method 2
SELECT * FROM sys.indexes  
WHERE object_id = object_id('products')

How to view the View schema definition?
Method 1

SELECT OBJECT_NAME(id) AS [View Name],text  
FROM SYSCOMMENTS  
WHERE id IN (SELECT object_id FROM SYS.VIEWS)

Method 2
SELECT * FROM sys.all_sql_modules  
WHERE object_id IN (SELECT object_id FROM SYS.VIEWS)

Method 3
SP_HELPTEXT ViewName

How to find the table used in the stored procedure?
Method 1

SELECT OBJECT_NAME(id) FROM SYSCOMMENTS S INNER JOIN SYS.OBJECTS O ON O.Object_Id = S.id 
WHERE S.text LIKE '%Products%' 
AND O.type='P'

I hope that the above methods will help you more when you work the query window to find the database objects. Please post your feedback and corrections about this article.



SQL Server 2016 Hosting - HostForLIFE.eu :: Aggregate Functions in SQL Server

clock July 3, 2019 11:58 by author Peter

What are Aggregate Functions in SQL Server? This article helps you to explore various Aggregate Functions in SQL Server.

What are Aggregate Functions?
I can give different definitions.

  • Aggregate functions are built in sql server functions.
  • Aggregate functions are applied to sets of records rather than to a single record.
  • Aggregate functions performs a computation on a set of values rather than on a single value.
  • Aggregate functions uses to summarize data.
  • Aggregate functions perform a calculation on a set of values and return a single value.

Getting Started
The information in multiple records are processed in a particular manner and then displayed in a single record answer.
Aggregate functions are often used in conjuction with GROUP BY clause.
Aggregate functions cannot be nested. The expression cannot be a subquery.

The list of built in Aggregate functions are:
AVG, CHECKSUM, CHECKSUM_AGG, COUNT, COUNT_BIG, GROUPING, MAX, MIN, SUM, STDEV, STDEVP, VAR, VARP.

AVG in SQL Server
AVG returns the average of the values in expression. The expression must contain numeric values. Null values are ignored.

The syntax: AVG ([ ALL | DISTINCT ] <expression>)
select orderid, avg(UnitPrice) UnitPrice from dbo.[Order Details] group by orderid; 

CHECKSUM in SQL Server
This is a basic hash algorithm usually used to detect changes or consistency in data.

"A digit representing the sum of the correct digits in a piece of stored or transmitted digital data, against which later comparisons can be made to detect errors in the data."

The syntax: CHECKSUM(<expression>, [ ... n] | *)

SELECT CHECKSUM(orderid, UnitPrice, quantity), orderid, UnitPrice, quantity FROM dbo.[Order Details] WHERE orderid = 10248 

CHECKSUM_AGG in SQL Server
The same as CHECKSUM, but the primary difference is that CHECKSUM is oriented around rows, whereas CHECKSUM_AGG is oriented around columns.

The syntax: CHECKSUM( [ALL | DISTINCT] <expression> )
SELECT CHECKSUM_AGG(CAST(UnitPrice AS int)) FROM dbo.[Order Details] 
update dbo.[Order Details] set UnitPrice = 15 
where orderid = 10248 and ProductID = 11 
SELECT CHECKSUM_AGG(CAST(UnitPrice AS int)) FROM dbo.[Order Details] 

COUNT in SQL Server

Returns the number of items in expression. The data type returned is of type int.

The syntax: COUNT( [ALL | DISTINCT] <expression> | * )
select COUNT(*), AVG(UnitPrice) from dbo.[Order Details] 

COUNT_BIG in SQL Server
Returns the number of items in a group. The data type returned is of type bigint.

The syntax: COUNT( [ALL | DISTINCT] <expression> | * )
select COUNT_BIG(*), AVG(UnitPrice) from dbo.[Order Details] 

GROUPING in SQL Server
MSDN : Is an aggregate function that causes an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP.

Function adds an extra column to the output of a SELECT statement.

The syntax: GROUPING(<column_name> )
select orderid, sum(UnitPrice) UnitPrice, GROUPING(orderid) 'orderid' 
from dbo.[Order Details] WHERE orderid = 10248 
GROUP BY orderid WITH cube 

MAX in SQL Server

Returns the maximum value from expression. Max ignores any NULL values.

The syntax: MAX( [ALL | DISTINCT] <expression> )
select MAX(QUANTITY) from dbo.[Order Details] 

MIN in SQL Server

Returns the smallest value from expression. Min ignores any NULL values.

The syntax: MIN( [ALL | DISTINCT] <expression> )
select MIN(QUANTITY) from dbo.[Order Details] 

SUM in SQL Server
Returns the total of all values in expression. Sum ignores any NULL values.

The syntax: SUM( [ALL | DISTINCT] <expression> )
select SUM(QUANTITY) from dbo.[Order Details] 

STDEV in SQL Server
Returns the standard deviation of all values in expression. Stdev ignores any NULL values.

The syntax: STDEV( <expression> )
select STDEV(QUANTITY) from dbo.[Order Details]

STDEVP in SQL Server
Returns the standard deviation for the population of all values in expression. Stdevp ignores any NULL values.

The syntax: STDEVP( <expression> )
select STDEVP(QUANTITY) from dbo.[Order Details] 

VAR in SQL Server
Returns the variance of all values in expression. Var ignores any NULL values.

The syntax: VAR( <expression> )
select VAR(QUANTITY) from dbo.[Order Details] 

VARP in SQL Server
Returns the variance for the population of all values in expression. Varp ignores any NULL values.
The syntax: VARP( <expression> )
select VARP(QUANTITY) from dbo.[Order Details] 

 



SQL Server 2012 Hosting - HostForLIFE.eu :: Passing Table to a Function Parameter in SQL Server 2012

clock June 25, 2019 12:01 by author Peter

In this article, I described how to pass a table to a function parameter in SQL Server. In this article, you create a table, student, then create a user-defined table type and pass the table type as a parameter to a function. So let's have a look at a practical example of how to pass a table as a function parameter in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

Here is how to implement passing a user-defined table type to a function.

1. Create a Student Table in SQL Server
Create a table named Student.
CREATE TABLE [dbo].[Student] 

    [StudentID] [int] NULL, 
    [StudentName] [varchar](30) NULL, 
    [StudentFees] [int] NULL 


2. Create a User-Defined Table Type in SQL Server
Now create a user-defined table type to be used as a table-valued parameter in the function.
CREATE TYPE dbo.StudentType AS TABLE 

   [StudentID] [int] , 
   [StudentName] [varchar](30) , 
   [StudentFees] [int]  


Now Press F8 to see the created type in the Object Explorer.
Database->Programmability->Types->User Define Table Types


3. Creating a Function in SQL Server

Now create the StudentDetailFunctionFunction. This function will accept a table-valued parameter.
READONLY keyword - This keyword is required to declare a table-valued parameter.

ALTER FUNCTION StudentDetailFunction( @StudentDetail dbo.StudentType READONLY ) 
RETURNS VARCHAR(50) 
AS 
BEGIN 
    DECLARE @Studentname VARCHAR(50) 
    SELECT  @Studentname= StudentName FROM @StudentDetail 
    RETURN @Studentname 
END 


4. Execute the SQL Server Function
Now you can declare a variable @StudentVariable which contains the value of the table columns.
DECLARE @StudentVariable AS StudentType 
INSERT INTO @StudentVariable(StudentName) VALUES('Peter') 
SELECT dbo.StudentDetailFunction(@StudentVariable) 



SQL Server 2016 Hosting - HostForLIFE.eu :: Sp_MSforeachtable Procedure in SQL Server

clock June 20, 2019 11:29 by author Peter
All versions of SQL Server have undocumented Stored Procedures or functions. This may be because those Stored Procedures or functions are used by Microsoft internally. This type of Stored Procedure or function (undocumented) can be any without any notification. The "sp_MSforeachtable" Stored Procedure comes with SQL Server, but it is not documented in MSDN. This Stored Procedure could be found in the Master database. The Stored Procedure "sp_MSforeachtable" allows us to easily process some code against each and every table in a single database. It means that it is used to process a single T-SQL command or number of various T-SQL commands against every table in the database.

sp_MSforeachtable Syntax

sp_MSforeachtable [ @command1 = ] 'command1' [ , [ @replacechar = ] replacechar ] [ , [ @command2 = ] command2 ] [ , [ @command3 = ] command3 ] [ , [ @whereand = ] where_and_Condition ] [ , [ @precommand = ] precommand] [ , [ @postcommand = ] postcommand]

Parameter
Parameter Description
@command1 It is the first command to be executed by this Stored Procedure and the data type is nvarchar(2000).
@replacechar It is a character in the command string that needs to be replaced with the table name being processed. The default value of this parameter is a "?".
@command2 @command2 and @command3 are two additional commands that can be run for each table. Here first Command1 is executing then command2 and then command3 will execute.
@command3
@whereand This parameter could be used to provide additional constraints to the command for helping to identify the rows in the sysobjects table that will be selected. Its data type is nvarchar(2000).
@precommand This command is to be run before processing any table. Its data type is nvarchar(2000).
@postcommand This command is to be run after the processing of all the tables. Its data type is nvarchar(2000).

Definition of sp_MSforeachtable procedure in SQL Server

    CREATE PROCEDURE sys.sp_MSforeachtable   
     @command1 NVARCHAR(2000),   
     @replacechar NCHAR(1) = N'?',   
     @command2 NVARCHAR(2000) = null,   
     @command3 NVARCHAR(2000) = null,   
     @whereand NVARCHAR(2000) = null,   
     @precommand NVARCHAR(2000) = null,   
     @postcommand NVARCHAR(2000) = null   
    AS   
    -- This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set   
     -- @precommand and @postcommand may be used to force a single result set via a temp table.  
     -- Preprocessor won't replace within quotes so have to use STR().  
     DECLARE @mscat NVARCHAR(12)   
     SELECT @mscat = LTRIM(STR(CONVERT(INT, 0x0002)))   
     IF (@precommand is not null)   
      EXEC(@precommand)   
     -- Create the SELECT  
       EXEC(N'DECLARE hCForEachTable cursor global for SELECT ''['' + REPLACE(schema_name(syso.schema_id), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(o.id), N'']'', N'']]'') + '']'' from dbo.sysobjects o join sys.all_objects syso on o.id =   
     syso.object_id '   
             + N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 '   
             + @whereand)   
     DECLARE @retval INT   
     SELECT @retval = @@error   
     IF (@retval = 0)   
      EXEC @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0   
     IF (@retval = 0 and @postcommand is not null)   
      EXEC(@postcommand)   
     RETURN @retval 


The following script helps us to list all the tables of the "TestDb" database.

Example script

Use Testdb  
exec sp_MSforeachtable 'print "?"' 
Another example. The following script helps us to determine the space used and allocated for every table in the database.

Example script
 Use Testdb 
    exec sp_MSforeachtable 'EXECUTE sp_spaceused [?];' 
Common uses of sp_MSforeachtable Stored
Procedure
This stored produce may be used for the following purposes.
  • To get the size of all the tables in the database
  • To rebuild all indexes of all the tables in the database
  • Disable all constraints and triggers of all the tables in the database
  • Delete all the data from all the tables in the database
  • To RESEED all tables to 0
  • To get the Number of Rows in all tables in a database
  • Update the statistics of all the tables in a database
  • Reclaim space from dropped variable-length columns in tables or indexed views of the database
These undocumented Stored Procedures can be used if we want to do the same operation on each table of any database. Please note that Microsoft may change the functionality and definition of this Stored Procedure at any time.

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.



European SQL 2017 Hosting :: Schema in SQL Server

clock May 29, 2019 06:31 by author Peter

This article explains schemas in SQL Server. A SQL schema in a database is a collection of logical structures of data. A schema in a database is a collection of logical structures of data. The schema is owned by a database user and is the same name as the database user. From SQL Server 2005, a schema is an independent entity (container of objects) different from the user who creates that object. In other words, schemas are very similar to separate namespaces or containers that are used to store database objects. Security permissions can be applied to schemas hence schemas are an important tool for separating and protecting database objects on the basis of user access rights. It improves flexibility for security-related administration of the database.

User schema separation
Before SQL Server 2005, database object owners and users were the same things and database objects (table, index, view and so on) were owned by the user. In other words database objects were directly linked to the user and the user could not delete them without removing the database object that were associated with the user. In SQL Server 2005, a schema separation is introduced, now the database object is no longer owned by a user, group or role. The schema can be owned by the user, group or role. The schema can have multiple owners. The schema ownership is transferrable. Database objects are created within the schema. Now the user can be dropped without the dropping of the database object owned by the user. But the schema cannot be deleted if it contains a database object.

The following are advantages of user schema separation:

The schema ownership is transferrable.
Database objects can be moved among the schemas.
A single schema can be shared among multiple users.
A user can be dropped without dropping the database objects associated with the user.
Provides more control of access and level of access.

Default schema
The default schema is the first schema searched when resolving object names. The user can be defined within the default schema. Using the "SCHEMA_NAME" function we can determine the default schema for the database.

The schema can be made the default for the user by defining DEFAULT_SCHEMA with CREATE USER or ALTER USER. If there is no default schema defined then SQL will assume "DBO" as the default schema. Note that there is no default schema associated with a user if the user is authenticated as a member of the group in the Windows operating system. In this case a new schema will be created and the name is the same as the user name.

Advantages of using Schema
Act as object protection tool: A schema can be a very effective object projection tool combined with the appropriate level of user permissions. A DBA can maintain control access to an object that would be very crucial.

Managing a logical group of database objects within a database: Schemas allow database objects to be organized into a logical group. This would be advantagous when multiple teams are working on the same database application and the design team wants to maintain integrity of the database tables.

Easy to maintain the database: A schema allows a logical grouping of the database objects, so the schema can help us in situations where the database object name is the same but falls in a different logical group.

Other Advantages
A single schema can be shared among multiple databases and database users.
A database user can be dropped without dropping database objects.
Manipulation of and access to the object is now very complex and more secure. The schema acts as an additional layer of security.
Database objects can be moved among schemas.
The ownership of schemas is transferable.

A schema is a very useful database concept and helps us to separate database users from the database object owners and also helps to create a logical grouping of database objects.



SQL Server 2016 Hosting - HostForLIFE.eu :: SQL Interview Questions And Answers

clock May 22, 2019 06:40 by author Peter

In this post we are going to share the interview questions or the information which you must know as a programmer or a developer, especially if you are a Dot Net developer. I hope you will like this article.

Background

I am a dot net developer. As a dot net developer, there are so many things that I must be aware of. I am sharing those in the form of articles, you can always read my other interview questions here in the following links.

So shall we now discuss about SQL interview questions.

SQL Interview Questions
Question: What are the types of Joins in SQL. Explain?

INNER JOIN: Returns all rows when there is at least one match in BOTH the tables.
LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table.
RIGHT JOIN: Returns all rows from the right table, and the matched rows from the left table.
FULL JOIN: Returns all rows when there is a match in ONE of the table.

Question: What is the default join in SQL? Give an example query?
The default join is INNER JOIN.

Example

SELECT column_name(s)  
FROM table1  
INNER JOIN table2  
ON table1.column_name=table2.column_name;  

Question: Describe all the joins with examples in SQL?
SQL LEFT JOIN
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.
    SQL LEFT JOIN Syntax  
    SELECT column_name(s)  
    FROM table1  
    LEFT JOIN table2  
    ON table1.column_name=table2.column_name;  


SQL RIGHT JOIN

The right join returns all the rows in the right table i.e. table 2 with the matching ones on the left table (table1).
    SELECT column_name(s)  
    FROM table1  
    RIGHT JOIN table2  
    ON table1.column_name=table2.column_name;  


SQL FULL OUTER
The full join returns all rows from the left table (table1) and from the right table (table2).
    SELECT column_name(s)  
    FROM table1  
    FULL OUTER JOIN table2  
    ON table1.column_name=table2.column_name;  


Question: What is Union and Union All ? Explain the differences?
SQL UNION
The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

Note: The UNION operator selects only distinct values by default.
    SELECT column_name(s) FROM table1  
    UNION  
    SELECT column_name(s) FROM table2;  
SQL UNION ALL
    SQL UNION ALL Syntax  
    SELECT column_name(s) FROM table1  
    UNION ALL  
    SELECT column_name(s) FROM table2;  

Allows duplicate values.

Question: Differentiate Clustered and Non clustered Index in SQL?
A clustered index is one in which the index’s order is arranged according to the physical order of rows in the table. Due to this reason there can only be one clustered index per table, usually this is the primary key.

A non clustered index is one in which the order of index is not in accordance with the physical order of rows in the table.

Create Index Syntax
CREATE INDEX [ CLUSTERED | NONCLUSTERED ] PIndex ON Persons (LastName,FirstName)

Question: Explain the difference between Stored Procedure and User Defined Function?

Stored Procedure

Stored procedures are reusable code in database which is compiled for first time and its execution plan saved. The compiled code is executed when every time it is called.

Function

Function is a database object in SQL Server. Basically it is a set of SQL statements that accepts only input parameters, perform actions and return the result. Function can return only a single value or a table. We can’t use functions  to Insert, Update, Delete records in the database table(s). It is compiled every time it is invoked.

Basic Difference
Function must return a value but in Stored Procedure it is optional (Procedure can return zero or n values).
Functions can have only input parameters for it whereas procedures can have input/output parameters.

Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advanced Differences

Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it,  whereas Function allows only SELECT statement in it. Procedures cannot be utilized in a SELECT statement, whereas function can be embedded in a SELECT statement. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section, whereas function can be.

The most important feature of stored procedures over function is retention and reusing the execution plan while in case of function it will be compiled every time.
Functions that return tables can be treated as another rowset. This can be used in JOINS with other tables.
Inline Function can be though of as views that take parameters and can be used in JOINS and other Rowset operations.
Exception can be handled by try-catch block in a procedure, whereas try-catch block cannot be used in a Function.
We can use transactions in stored procedure but not in functions.

Conclusion
Did I miss anything that you may think which is needed? Could you find this post as useful? I hope you liked this article. Please share me your valuable suggestions and feedback.

 



SQL Server 2016 Hosting - HostForLIFE.eu :: How To Check Current Installation Mode In SSAS

clock May 15, 2019 12:36 by author Peter

In this blog, we will learn how we can check which mode of SSAS is installed on our machine. SSAS is available with three modes of installation.

  • Tabular
  • Multidimensional
  • SharePoint

To check which mode of SSAS is installed on your machine, follow the below steps.

  • Open SSMS.
  • Right-click on SSAS Properties.

 

The Mode of Server Name is available in the property window. In our case, SSAS is installed with the Multidimensional mode, so it is showing “Multidimensional”.

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 2016 Hosting - HostForLIFE.eu ::Calling a Function From a Stored Procedure in SQL Server 2012

clock May 10, 2019 11:04 by author Peter

In this article, we will see how to call a function from a stored procedure in SQL Server 2012. Here, I have written a scalar function named MultiplyofTwoNumber that accepts two parameters and returns one parameter. Now I want to call this from a stored procedure. So let's take a look at a practical example of how to call a function from a stored procedure in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.  There are some simple things to do that are described here.

There are two types of functions in SQL Server; they are:

  •  System defined function
  •  User defined function

User defined functions are three types in SQL Server. They are scalar, inline table-valued and multiple-statement table-valued.

Creating a User-Defined Scalar Function in SQL Server

Now create a function named MultiplyofTwoNumber with the two parameters number1 and number2 returning one parameter named result. Both parameters have the same type, int. The function looks as in the following:
    Create FUNCTION [dbo].[MultiplyofTwoNumber]  
    (  
           @Number1 int,  
           @Number2 int  
    )  
    RETURNS int  
    AS  
    BEGIN  
           -- Declare the return variable here  
           DECLARE @Result int  
           SELECT @Result = @Number1 * @Number2;  
           -- Return the result of the function  
           RETURN @Result  
    END  


Creating a Stored Procedure in SQL Server
A function can be called in a select statement as well as in a stored procedure. Since a function call would return a value we need to store the return value in a variable. Now creating a stored procedure which calls a function named MultiplyofTwoNumber; see:
    Create PROCEDURE [dbo].[callingFunction]  
    (  
    @FirstNumber int,  
    @SecondNumber int  
    )  
    AS  
    begin  
    declare @setval int  
    select dbo.[MultiplyofTwoNumber](@FirstNumber, @SecondNumber)  
    end  

Now, we can execute the procedure with duplicate values to check how to call a function from a stored procedure; see:
    USE [registration]  
    GO  
    DECLARE  @return_value int  
    EXEC  @return_value = [dbo].[callingFunction]  
        @FirstNumber = 3,  
        @SecondNumber = 4  


Now press F5 to run the stored procedure.

A function can be called using a select statement:

    Select dbo.[MultiplyofTwoNumber](3, 4) as MultiplyOfNumbers  

Now press F5 to run the stored procedure.

Output

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.

 



European SQL 2017 Hosting :: How To Call A Web Service From SQL Server?

clock April 24, 2019 11:27 by author Peter

In this blog, I have shown the process of calling web services through a stored procedure in SQL Server database. Also, I have explained how to call a stored procedure with a SOAP Envelope.

Step 1
Create a stored procedure in your SQL Server.
  CREATE proc [dbo].[spHTTPRequest]    
        @URI varchar(2000) = 'http://localhost:55253/',         
        @methodName varchar(50) = 'Get',    
        @requestBody varchar(8000) = '',    
        @SoapAction varchar(255),    
        @UserName nvarchar(100), -- Domain\UserName or UserName    
        @Password nvarchar(100),    
        @responseText varchar(8000) output   
  as   
  SET NOCOUNT ON   
  IF    @methodName = ''   
  BEGIN   
        select FailPoint = 'Method Name must be set'   
        return   
  END   
  set   @responseText = 'FAILED'   
  DECLARE @objectID int   
  DECLARE @hResult int   
  DECLARE @source varchar(255), @desc varchar(255)    
  EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT   
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
                    source = @source,    
                    description = @desc,    
                    FailPoint = 'Create failed',    
                    MedthodName = @methodName    
        goto destroy    
        return   
  END   
  -- open the destination URI with Specified method    
  EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password   
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'Open failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  -- set request headers    
  EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8'   
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'SetRequestHeader failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  -- set soap action    
  EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapAction    
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'SetRequestHeader failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  declare @len int   
  set @len = len(@requestBody)    
  EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len    
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'SetRequestHeader failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  /*   
  -- if you have headers in a table called RequestHeader you can go through them with this   
  DECLARE @HeaderKey varchar(500), @HeaderValue varchar(500)   
  DECLARE RequestHeader CURSOR  
  LOCAL FAST_FORWARD   
  FOR  
        SELECT      HeaderKey, HeaderValue   
        FROM RequestHeaders   
        WHERE       Method = @methodName   
  OPEN RequestHeader   
  FETCH NEXT FROM RequestHeader   
  INTO @HeaderKey, @HeaderValue   
  WHILE @@FETCH_STATUS = 0   
  BEGIN  
        --select @HeaderKey, @HeaderValue, @methodName   
        EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, @HeaderKey, @HeaderValue   
        IF @hResult <> 0   
        BEGIN  
              EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT  
              SELECT      hResult = convert(varbinary(4), @hResult),   
                    source = @source,   
                    description = @desc,   
                    FailPoint = 'SetRequestHeader failed',   
                    MedthodName = @methodName   
              goto destroy   
              return  
        END  
        FETCH NEXT FROM RequestHeader   
        INTO @HeaderKey, @HeaderValue   
  END  
  CLOSE RequestHeader   
  DEALLOCATE RequestHeader   
  */    
  -- send the request    
  EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody    
  IF    @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'Send failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  declare @statusText varchar(1000), @status varchar(1000)    
  -- Get status text    
  exec sp_OAGetProperty @objectID, 'StatusText', @statusText out   
  exec sp_OAGetProperty @objectID, 'Status', @status out   
  select @status, @statusText, @methodName    
  -- Get response text    
  exec sp_OAGetProperty @objectID, 'responseText', @responseText out   
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'ResponseText failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  destroy:    
        exec sp_OADestroy @objectID    
  SET NOCOUNT OFF   
      
  GO   


The Stored Procedure takes the following parameters.
  @URI: the URI of the web service
  @MethodName: this would be ‘GET’ or ‘POST’
  @RequestBody: this is the SOAP xml that you want to send
  @SoapAction: this the operation that you want to call on your service
  @UserName: NT UserName if your web service requires authentication
  @Password: the password if using NT Authentication on the web service
  @ResponseText: this is an out parameter that contains the response from the web service


Step 2
Make the setting in SQL for it.
  Use master 
  sp_configure 'show advanced options', 1  
   
  GO  
  RECONFIGURE;  
  GO  
  sp_configure 'Ole Automation Procedures', 1  
  GO  
  RECONFIGURE;  
  GO  
  sp_configure 'show advanced options', 1  
  GO  
  RECONFIGURE; 


Step 3

Call the stored procedure (Here is a sample call to my service).
  declare @xmlOut varchar(8000) 
  Declare @RequestText as varchar(8000); 
  set @RequestText= 
  '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:tem="http://tempuri.org/"> 
     <soapenv:Header/> 
     <soapenv:Body> 
        <tem:CreateOrder> 
           <!--Optional:--> 
           <tem:OrderRequest> 
              <tem:OrderId>200</tem:OrderId> 
              <!--Optional:--> 
              <tem:OrderName>something</tem:OrderName> 
           </tem:OrderRequest> 
        </tem:CreateOrder> 
     </soapenv:Body> 
  </soapenv:Envelope>' 
  exec spHTTPRequest 
  'http://localhost/testwebservices/helloworldservice.asmx', 
  'POST', 
  @RequestText, 
  'http://tempuri.org/CreateOrderForMe',   -- this is your SOAPAction: 
  '', '', @xmlOut out 
  select @xmlOut  


Make sure your SOAP action is correct. Copy this action from your services. It will show up when your service is RUN.

 



SQL Server 2016 Hosting - HostForLIFE.eu :: Replication Of Max Text Length

clock April 16, 2019 09:34 by author Peter

Ever seen the below error? Until this week I hadn’t. So, I figured I’d take a little time and introduce it to those that had not.

Error Description: Length of LOB data (65754) to be replicated exceeds configured maximum 65536. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65536. A configured value of -1 indicates no limit


We ran into an issue with a customer this week. This error was flooding the error log. After a little digging, I found it had to do with transactional replication (also applies to Change Data Capture) they had set up which included LOB data.

Per MSDN,
The max text repl size option specifies the maximum size (in bytes) of text, ntext, varchar(max), nvarchar(max), varbinary(max), xml, and image data that can be added to a replicated column or captured column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement. The default value is 65536 bytes.

In the error above you can see it plainly states that the column’s LOB data nvarchar(max), in this case, was 65754 bytes which was over the max default size of 65536. Which ironically is 64k.   64*1024 = 65536 (if you didn’t know). Adjusting the max text repl size for this server solved our issue. Below you can see the ways to change this value. For us changing it to the max value of 2147483647 bytes which is 2 GB was the way to go. If you don’t know the max value you can also set it to -1 which means no limit, the limit will be based on datatype limits. Previously, the limit was 2GB.

Script
GO  
EXEC sp_configure 'show advanced options', 1;   
RECONFIGURE ;   
GO  
EXEC sp_configure 'max text repl size',2147483647;   
GO  
RECONFIGURE;   
GO 


Using GUI
At the Server Level right click and go to Properties.
Click on Advanced. Under Miscellaneous, change the Max Text Replication Size option to the desired value.

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.

 



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