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 :: FORMATMESSAGE

clock February 21, 2017 08:25 by author Peter

In this post, I will show you how to using bigint with FORMATMESSAGE. SQL Server 2016 added the FORMATMESSAGE function.  According to Books On Line, FORMATMESSAGE constructs a message from an existing message in sys.messages or from a provided string. The functionality of FORMATMESSAGE resembles that of the RAISERROR statement. However, RAISERROR prints the message immediately, while FORMATMESSAGE returns the formatted message for further processing.

So let's take a look at this new function, run the following
SELECT FORMATMESSAGE('Signed int %i, %i', 50, -50)
SELECT FORMATMESSAGE('Unsigned int %u, %u', 50, -50);


Here is the output if you run that

--------------------------------------------
Signed int 50, -50
Unsigned int 50, 4294967246

Here is what the type specifications that you can use are

Type specification         Represents
d or i                          Signed integer
o                                Unsigned octal
s                                String
u                               Unsigned integer
x or X                         Unsigned hexadecimal

We used i to denote a signed integer, we also used u to denote a unsigned integer

Let's look at another example, this time we are using a variable. The variable will be an integer and we  are using i as the type specification

DECLARE @Val int = 1
SELECT FORMATMESSAGE('The value you supplied %i is incorrect!', @Val);


Here is the output
---------------------------------------
The value you supplied 1 is incorrect!

That worked without a problem. Now let's use a variable of the bigint data type, we are using the same type specification as before

DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %i is incorrect!', @Val);

Here is the output
---------------------------------------------------------------------------
Error: 50000, Severity: -1, State: 1. (Params:).


The error is printed in terse mode because there was error during formatting.
Tracing, ETW, notifications etc are skipped.

As you can see that did not work, so what can we do?
One thing we can do is converting the value to a varchar and then use s as the type specification
DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %s is incorrect!',
   CONVERT(VARCHAR(100),@Val));

You will again get this as output
---------------------------------------

So converting to varchar worked, but what if we want to use a bigint data type without converting to a varchar?

Another way is to use I64d as the type specification
DECLARE @Val bigint = 1<br />
SELECT FORMATMESSAGE('The value you supplied %I64d is incorrect!', @Val);


You will get this
---------------------------------------

The value you supplied 1 is incorrect!
So there you have it, if you want to use bigint with FORMATMESSGAE use I64d as the type specification, or convert to varchar and use s as the type specification.

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 :: How to Auto Increment ID Column Using nvarchar DataType In SQL Server?

clock February 14, 2017 07:53 by author Peter

This article is about Auto Increment ID Column using nvarchar datatype in SQL Server 2016 . SQL server provides identity property to get auto-increment record values where the datatype used is “int”. This article explains a way to use nvarchar datatype column to get auto-increment record values.
e.g. S00001, E00001, INV00001. (Here there are prefix like S, E, INV used to denote the record ids.)

This article provides a solution to use alphanumeric record ids incremented automatically.

Solution
Create an Employee table as below,

Here “ID” column is for storing record id as similar to Serial No of any entry made. “EmployeeNo” column is used to store the alphanumeric auto increment id. “Name” stores the name of the Employee. Create a Windows Form with a TextBox and a Button control


The above figure shows the use of a textbox to enter name in the ”employee” table where the id is auto-incremented.
Code
    Import the NameSpace

Create 2 methods OpenConnection() and CloseConnection() to handle the SQL Server Connection object.
        private void OpenConnection(SqlConnection con) { 
            con.ConnectionString = "Data Source=.; Initial Catalog=demodb; Integrated Security=True"; 
            if (con.State == ConnectionState.Closed) { 
                con.Open(); 
            } 
        } 
        private void CloseConnection(SqlConnection con) { 
            con.Close(); 
        } 


On the Insert button click the id is auto-incremented and the relevant data is inserted in the employee table. (id, employeeno and name)
        SqlConnection con = new SqlConnection(); 
        OpenConnection(con); 
        string id = AutoIncrementID(); 
        int idLimit = 7; 
        string eno = "E" + ZeroAppend("0000000" + id, idLimit); 
        string query = "insert into employee values ('" + id + "', '" + eno + "', '" + txtName.Text + "')"; 
        SqlCommand cmd = new SqlCommand(query, con); 
        int i = cmd.ExecuteNonQuery(); 
        if (i > 0) { 
            MessageBox.Show("Data Added", "Add", MessageBoxButtons.OK, MessageBoxIcon.Information); 
        } 
        CloseConnection(con); 

Here a method AutoIncrementID() is used to do the task of auto-incrementing based on the last entry inserted in the table.
        private string AutoIncrementID() { 
            SqlConnection con = new SqlConnection(); 
            OpenConnection(con); 
            SqlCommand cmd = new SqlCommand("SELECT ISNULL(MAX(ID),0) + 1 from employee", con); 
            SqlDataReader dr = cmd.ExecuteReader(); 
            string id = null; 
            if (dr.Read()) { 
                id = dr[0].ToString(); 
            } 
            CloseConnection(con); 
            return id; 
        } 
        //The function ZeroAppend is used to append “0” after the prefix to the code. 
        public static string ZeroAppend(string data, int idLimit) { 
            return data.Substring(data.Length - idLimit); 
        } 

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.

 



SQL Server 2016 Hosting - HostForLIFE.eu :: How to Implement Inner Join When Query Contains Outer Join?

clock February 7, 2017 08:50 by author Peter

This is something interesting; i.e., how to enforce the compiler to use the inner join instead of the outer join. Let's take an example. We have two tables State_Tab and City_Tab. An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them. The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN returns all rows from multiple tables where the join condition is met.

State_Tab

City_Tab

Now, we write two queries, using outer join, and compare the execution plan of both the queries.

Query 1

Query 2


We can see that both the queries use the left outer join but the second query implements the inner join, instead of outer join, because for "On condition", we define the 1=1, which will be always true, so the query optimizer converts the left outer join to cross join due to the "Where condition". This cross join will convert into the inner join.



HostForLIFE.eu Proudly Launches Umbraco 7.5.7 Hosting

clock January 27, 2017 07:48 by author Peter

HostForLIFE.eu, a leading Windows web hosting provider with innovative technology solutions and a dedicated professional services team, today announced the support for Umbraco 7.5.7 hosting plan due to high demand of Umbraco users in Europe. The company has managed to build a strong client base in a very short period of time. It is known for offering ultra-fast, fully-managed and secured services in the competitive market.

 

HostForLIFE.eu hosts its servers in top class data centers that is located in Amsterdam, (NL), London, (UK), Washington, D.C. (US), Paris, (France), Frankfurt, (Germany), Chennai, (India), Milan, (Italy), Toronto, (Canada) and São Paulo, (Brazil) to guarantee 99.9% network uptime. All data centers feature redundancies in network connectivity, power, HVAC, security and fire suppression. All hosting plans from HostForLIFE.eu include 24×7 support and 30 days money back guarantee. HostForLIFE Umbraco hosting plan starts from just as low as €3.49/month only and this plan has supported ASP.NET Core 1.1, ASP.NET MVC 5/6 and SQL Server 2012/2014/2016.

Umbraco is a fully-featured open source content management system with the flexibility to run anything from small campaign or brochure sites right through to complex applications for Fortune 500's and some of the largest media sites in the world. Umbraco is strongly supported by both an active and welcoming community of users around the world, and backed up by a rock-solid commercial organization providing professional support and tools. Umbraco can be used in its free, open-source format with the additional option of professional tools and support if required.

Umbraco release that exemplifies our mission to continue to make Umbraco a bit simpler every day. The other change is that there's now a "ValidatingRequest" event you can hook into. This event allows you to "massage" any of the requests to ImageProcessor to your own liking. So if you'd want to never allow any requests to change BackgroundColor, you can cancel that from the event. Similarly if you have a predefined set of crops that are allowed, you could make sure that no other crop sizes will be processed than those ones you have defined ahead of time.

Further information and the full range of features Umbraco 7.5.7 Hosting can be viewed here: http://hostforlife.eu/European-Umbraco-757-Hosting



European SQL 2016 Hosting - HostForLIFE.eu :: MSSQL Server Comes with JSON?

clock January 17, 2017 10:27 by author Scott

With over a thousand votes on the Microsoft Connect site, JSON support is the most requested feature for SQL Server 2016. This month, Microsoft announced that the upcoming release of SQL Server 2016 will fulfill that request. Sort of.

Microsoft will certainly be touting this feature as an additional reason to upgrade when the time comes. Natively supporting JSON helps bridge the gap between the desirable aspects of a NoSQL database and a relational database like SQL Server. The trouble is that with this current implementation, you're not really gaining anything over what's currently available. It's nothing like the native XML data type that became available starting with SQL Server 2005.

The announcement starts off with a big caveat: they will be providing native JSON support, but not a native JSON type. In fact, storage of JSON data will happen the same way it happens today, in a NVARCHAR typed column. They list 3 hollow reasons for this:

  • Backward compatibility
  • Cross feature compatibility
  • Non Microsoft controlled JSON parsers on the client (C#)

Backward compatibility is weak, if you're already storing JSON data you wouldn't have a hard time moving into the JSON type. Cross feature compatibility means that they're not interested in implementing JSON in other SQL Server components, so instead everything that already works with NVARCHAR (aka everything) will still work (aka nothing's changed). The client side JSON parser point is an odd one and it leads into my next gripe of this feature implementation.

On the client side, such as in a C# application, it's already common to serialize data back and forth in JSON using a JSON parsing library. The most common, though maybe not the fastest, is the Newtonsoft JSON.Net library which comes packaged with the default templates in ASP.NET projects etc. The fact that there are other options out there doesn't really matter in my opinion. Sure some behave differently, but in the end, a JSON object has a specific syntax and any proper parser should be able to serialize and deserialize the output of any other parser.

What's baffling are the features that are being supported in this native JSON support.

You can now use FOR JSON to export the results of a query as JSON. This means that you can make a normal T-SQL query and ask for the result back as a JSON formatted result.  OK, but I could have just serialized the result on the client side in literally one line of code. They use the example of returning the results directly from an OData request through a web service.

You can transform a JSON object to a relational table with OPENJSON. This means that within a T-SQL query, you can provide a JSON object as part of the query and each item in the JSON will be returned as a table row which can be used to query or insert records into a relational table. They use the example of loading a JSON document into rows of a database, but again, that can easily be done on the client site by deserializing the JSON Array first.

Finally they are providing some built in functions for processing JSON data in the database. ISJSON will check if a NVARCHAR column has JSON data and JSON_VALUE which provides some scalar selection ability similar to the dot notation of JavaScript, e.g. 

$.property1.property2.array1[5].property3.array2[15].property4

That's basically the least they could do with this implementation. And as for indexing of JSON data, you're left with some basic support on the level of full-text indexing of any other NVARCHAR column.

So in the end, this "native JSON support" is basically nothing more that a few convenience function on top of a normal NVARCHAR column. It takes a familiar eye about 2 minutes to uncover the sad truth about this feature and the response has shown that Microsoft isn't fooling anybody with this JSON implementation. That's not to say there aren't great features coming in SQL Server 2016 (stretch database seems pretty cool), but JSON support isn't one of them.



European Entity Framework Core 1.0 Hosting - HostForLIFE.eu :: How to Access SQL from Entity Framework Core

clock January 12, 2017 08:33 by author Scott

ORMs like Entity Framework have always been shrouded in a bit of controversy from SQL purists who aren't in control of the types of queries that these tools are building behind the scenes. While this is a perfectly valid stance to take, these same tools can often assist in getting things done much faster than tinkering with an endless chain of JOINs, sub-queries, etc.

But what if you want to know exactly what is going on behind the scenes? What SQL is being generated by Entity Framework? And is it efficient enough to suit your needs, or do you need to handle writing some yourself?

This post will cover a quick tip that will allow you to see the SQL behind the scenes and judge for yourself using Entity Framework Core.

How Did This Work Prior to Entity Framework Core?

Previously, you could use Reflection to create an ObjectQuery object and then call the ToTraceString()method to actually store the query results as seen below:

// Build a query using Entity Framework
var query = _context.Widgets.Where(w => w.IsReal && w.Id == 42); 
// Get the generated SQL
var sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString(); 

And that's really it. The result of the ToTraceString() call will return a string variable containing the entire SQL query being executed.

Options for Entity Framework Core

The previous approach no longer works within the Entity Framework Core (EF7) world, so we have to resort to one of three options, which may vary depending on your needs:

  • Using Built-in or Custom Logging. Logging the executing query using your logger of choice or the built-in Logger in .NET Core as mentioned in this tutorial.
  • Using a Profiler. Using an SQL Profiler like MiniProfiler to monitor the executing query.
  • Using Crazy Reflection Code. You can implement some custom reflection code similar to the older approach to perform the same basic concept.

Since both of the first two options are fairly well documented, we will be focusing on the crazy reflection approach.

Getting Behind the Scenes in Entity Framework Core

Using the following snippets of code, which rely on Reflection to resolve information about the compiler, parser, database, and fields being targeted, we can use those things to reconstruct what is actually going on behind the scenes.

NOTE: Since this relies on Reflection, it may be subject to breaking in the future, especially with any API changes.

public class IQueryableExtensions 
{
    private static readonly FieldInfo QueryCompilerField =
    typeof(EntityQueryProvider).GetTypeInfo().DeclaredFields.First
    (x => x.Name == "_queryCompiler");

    private static readonly PropertyInfo NodeTypeProviderField =
    QueryCompilerTypeInfo.DeclaredProperties.Single(x => x.Name == "NodeTypeProvider");

    private static readonly MethodInfo CreateQueryParserMethod =
    QueryCompilerTypeInfo.DeclaredMethods.First(x => x.Name == "CreateQueryParser");

    private static readonly FieldInfo DataBaseField =
    QueryCompilerTypeInfo.DeclaredFields.Single(x => x.Name == "_database");

    private static readonly FieldInfo QueryCompilationContextFactoryField =
    typeof(Database).GetTypeInfo().DeclaredFields.Single
    (x => x.Name == "_queryCompilationContextFactory");

    public static string ToSql<TEntity>
    (this IQueryable<TEntity> query) where TEntity : class
    {
        if (!(query is EntityQueryable<TEntity>)
        && !(query is InternalDbSet<TEntity>))
        {
            throw new ArgumentException("Invalid query");
        }

        var queryCompiler = (IQueryCompiler)QueryCompilerField.GetValue(query.Provider);
        var nodeTypeProvider =
        (INodeTypeProvider)NodeTypeProviderField.GetValue(queryCompiler);
        var parser = (IQueryParser)CreateQueryParserMethod.Invoke
        (queryCompiler, new object[] { nodeTypeProvider });
        var queryModel = parser.GetParsedQuery(query.Expression);
        var database = DataBaseField.GetValue(queryCompiler);
        var queryCompilationContextFactory =
        (IQueryCompilationContextFactory)QueryCompilationContextFactoryField.GetValue(database);
        var queryCompilationContext = queryCompilationContextFactory.Create(false);
        var modelVisitor =
        (RelationalQueryModelVisitor)queryCompilationContext.CreateQueryModelVisitor();
        modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
        var sql = modelVisitor.Queries.First().ToString();

        return sql;
    }
}

And as far as actual usage goes, you would simply call the ToSql() method to return your SQL query string:

// Build a query using Entity Framework
var query = _context.Widgets.Where(w => w.IsReal && w.Id == 42); 
// Get the generated SQL
var sql = query.ToSql(); 



SQL Server 2016 Hosting - HostForLIFE.eu :: How to Get The Header Information Of A Table in SQL Server?

clock January 10, 2017 10:14 by author Peter

Now, I will show you how to Get The Header Information Of A Table in SQL Server. If someone asks the question, "How do I get the header information of the table in SQL Server?" the first solution that arises in our mind will be "use any condition that never matches," like 1=0 or 2=0. However, in this case, we only get the header information of the table, and no result will return.

It is true that using the above solution, we will not get any data and the result will be the header information of our table.

But this is not the best solution because in this case, SQL Server reads all rows and compares the conditions each time. That increases the turnaround time and resource complexity.

For this, SQL Server provides the "SET FMTONLY ON" keyword. "SET FMTONLY ON" returns only metadata to the client. It can be used to test the format of the response without actually running the query. This method returns header information without performing any read operation.

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 :: Clearing Down A Database Full Of Constraints In SQL Server

clock January 3, 2017 08:32 by author Peter

Have you ever been in a situation where you've got to clear down some data within a database that's chock filled with constraints but don't desire to wipe out your precious relationships, indices and all that other jazz?

I found myself in a similar situation earlier this week, and required a clear-down script that might wipe out all of the data within a whole database, without being bothered by any existing constraints. Here it is.
    USE @YourTable;  
    EXEC sp_MSForEachTable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"  
    EXEC sp_MSForEachTable "DELETE FROM ?"  
    EXEC sp_MSForEachTable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"  
    GO  

The script itself takes advantage of an undocumented stored procedure within SQL Server called sp_MSForEachTable that will actually iterate through all of the tables within a given database.

Now that we know we are going to be looping through each of the tables within the specified database, let's see what is going to happen to each of the tables.


ALTER TABLE ? NOCHECK CONSTRAINT ALL
This will disable any constraint checking that is present on the table (so, operations like deleting a primary key or a related object won't trigger any errors).

DELETE FROM ?
This will delete every record within the table.

ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL
This re-enables the constraint checking, bringing your table back to its original state, sans data.

It is very important that you properly scope this query to the table that you are targeting to avoid any crazy data loss. While I don't think that you could just leave that out and execute on master, I wouldn't want to even risk testing that out (although feel free to try it out and let me know if it nukes everything).

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 :: Collation Error In SQL Sever

clock December 20, 2016 07:56 by author Peter

ERROR - "Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS_KS_WS within the up to operation."

Don’t panic if you get this error while joining your tables. there's an easy way to solve this. It happens because of the different collation settings on 2 columns we are joining.

The first step is to figure out what are the two collations that have caused the conflicts.
Let us assume that collation has not been set at the column level and instead at the db level. Then, we've to execute two straightforward statements as below.
Statements

  • Select DATABASEPROPERTYYEX('DB1',N'Collation')
  • Select DATABASEPROPERTYYEX('DB2',N'Collation') 

One more thing to make a note of here is that if you are on SharePoint, you will get an error as following.

Latin_General_CI_AS_KS_WS.
 
If you are on any other database and use the default settings, you may get this SQL_Latin_General_CP1_CI_AS.

Now, we have to do something similar to CAST, called Collate (FOR Collation).

Refer to the example below.
      select * from Demo1.dbo.Employee emp 
    join Demo2.dbo.Details dt 
    on (emp.email =dt.email COLLATE SQL_Latin_General_CP1_CI_AS) 

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.



Node.js Hosting - HostForLIFE.eu :: Node.js Proxy Configurations on Windows

clock December 13, 2016 10:12 by author Peter

In this article, let me show you how to setup Node.js proxy configurations on Windows. It is often required to work with Node.JS and NPM package installer behind the proxy. When trying to install npm packages behind the proxy, you may come across the error, given below:

To resolve exceptions, shown above, http_proxy and https_proxy configurations needs to be done for NPM. The step by step guidance is given below to perform the configuration on Windows machine.

  • Go to C:\User\{User} directory.
  • If .npmrc file exists then open it in notepad editor else create .npmrcfile.
  • Add the lines, given below to the file for your valid AD domain user. proxy=http://domain\\username:[email protected]:port
  • Add the lines, given below for Non AD user proxy=http://username:[email protected]:port
  • Save .npmrc file
  • Try to use npm install now. It should work without any error.

The sample contents of .npmrc file are shown below:



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 2012 Hosting, ASP.NET 4.5 Hosting, ASP.NET MVC 5 Hosting, SQL 2014 Hosting and SQL 2014 Hosting.

Tag cloud

Sign in