Archive

Archive for December, 2016

2016 blogging in review

December 31, 2016 3 comments

 
Happy New Year 2017 from SQLwithManoj !!!
 

This time WordPress.com stats helper monkeys didn’t prepare annual report for any of their blogs for year 2016. So I prepared my own Annual Report.
 

–> Crunchy numbers
sqlwithmanoj2016-views

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 640,749 times by 396,212 unique visitors in 2016. If it were an exhibit at the Louvre Museum, it would take about 20 days for that many people to see it.

There were 117 pictures uploaded, taking up a total of 3.1 MB. That’s about 2-3 pictures per week.

The most popular post blogged in 2016 was SQL Server 2016 RTM full and final version available viewed 25,829 times.

 

–> Posting Patterns

In 2016, there were 52 new posts, growing the total archive of this blog to 416 posts.

LONGEST STREAK: 10 post each day, in March 2016

 

–> Attractions in 2016

These are the top 5 posts that got most views in 2016:

1. Passed 70-461 Exam : Querying Microsoft SQL Server 2012 (25,946 views)

2. SQL Server 2016 RTM full and final version available (25,829 views)

3. DB Basics – What are Candidate, Primary, Composite & Super Keys (17,189 views)

4. What is SQL, PL/SQL, T-SQL and difference between them (12,966 views)

5. SQL Basics – Difference between TRUNCATE, DELETE and DROP? (11,694 views)

 

–> How did they find you?

The top referring sites and search engines in 2016 were:

sqlwithmanoj2016-referrer
 

–> Where did they come from?

Out of 210 countries, top 5 visitors came from India, United States, United Kingdom, Canada and Australia:

sqlwithmanoj2016-countries
 

That’s all for 2016, see you in year 2017, all the best !!!
 

Connect me on Facebook, Twitter, LinkedIn, YouTube, Google, Email

Advertisement
Categories: SQLwithManoj Tags: , , ,

New built-in function TRANSLATE() in SQL Server 2017

December 29, 2016 2 comments

 
Microsoft looks very serious this time to move people from other databases to SQL Server. As with SQL Server 2016 & 2017 you can see lot of Built-in function added, which were present in other databases from long back, will ease database development in SQL Server.

One of this function is TRANSLATE() function, which can be used like a REPLACE() function, and would avoid using REPLACE() function multiple times in a query.
 

Syntax:

TRANSLATE ( inputString, characters, translations)

Note: characters and translations params should have same length.
 

–> Consider this example I’ve taken from MSDN:

SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');
GO

Output:

Input Output
2*[3+4]/{7-2} 2*(3+4)/(7-2)

 

–> If you had to do same with REPLACE() function then you would end up writing multiple & nested REPLACE() function, like:

SELECT 
	REPLACE(
		REPLACE(
			REPLACE(
				REPLACE('2*[3+4]/{7-2}', '[', '('), 
			']', ')'), 
		'{', '('), 
	'}', ')');
GO

 

After working with this new feature it reminds me of IIF vs CASE statement. The IIF() function also works as a shortcut of CASE statement and cuts lot of clutter and gives you clean code.

Hope you find this small utility very handy while developing complex queries, will post more scenarios if I came across going forward, thanks !!!

Categories: SQL Built-in Functions, SQL Server 2017 Tags: , ,

New built-in function TRIM() in SQL Server 2017

December 26, 2016 2 comments

 
If you are thinking the new TRIM() function in SQL Server 2017 is just a combination of LTRIM() & RTRIM() functions, then you are wrong :). It’s more than that and we will check it today !
 

– LTRIM() function is used to truncate all leading blanks, or white-spaces from the left side of the string.

– RTRIM() function is used to truncate all trailing blanks, or white-spaces from the right side of the string.
 

–> Now, with teh new TRIM() function you can do both, but more than that.

Usage #1: TRIM() function will truncate all leading & trailing blanks from a String:

SELECT 
	TRIM ('  Manoj Pandey     ') as col1,
	LTRIM(RTRIM('  Manoj Pandey     ')) as col2

trim-1

Usage #2: Plus it can be used to remove specific characters from both sides of a String, like below:

SELECT 
	TRIM ( 'm,y' FROM 'Manoj Pandey') as col1,
	TRIM ( 'ma,ey' FROM 'Manoj Pandey') as col2,
	TRIM ( 'm,a,e,y' FROM 'Manoj Pandey') as col3

trim-2

Thus with the above query you can see that you can trim characters too, by providing leading & trailing characters, but should be in same sequence as your string is.

Also for Col2 & Col3 we have provided Trimming Characters in 2 different ways, but got the same output.
 

–> Note: I just mentioned above that the leading & trailing characters should be in same sequence. If you provide in different sequence like below you won’t get desired results.

SELECT 'Manoj Pandey' as st,
	TRIM ( 'a,n' FROM 'Manoj Pandey') as Col1,
	TRIM ( 'm,e' FROM 'Manoj Pandey') as Col2,
	TRIM ( 'm,o,y,e' FROM 'Manoj Pandey') as Col3

trim-3

Like for Col3 you cannot get rid of middle characters (like ‘o’ and ‘n’) until and unless they become leading or trailing characters.

Categories: SQL Built-in Functions, SQL Server 2017 Tags: , ,

SQL Error – SQL Server blocked access to STATEMENT ‘OpenRowset/ OpenDatasource’ of component ‘Ad Hoc Distributed Queries’

December 25, 2016 Leave a comment

 
Today while executing a Stored Procedure which internally executes a remote query via Linked Server, I got following error:

Msg 50000, Level 16, State 127, Procedure spExecureRemoteQuery, Line 50

SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.

 

The above clearly means that the “Ad Hoc Distributed Queries” option is disabled on the database instance.

–> Run below Query to check if this property is disabled, if will show you 0 value under Config & Run value columns:

sp_configure 'show advanced options', 1
GO
EXEC sp_configure
GO

adhocdistributedqueries
 

–> To enable it run the below Query:

sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO
EXEC sp_configure
GO

adhocdistributedqueries-set

…the value under Config & Run value columns shows 1, means that now the Property is enabled and you can execute your Remote/Linked-Server queries.

Categories: SQL Errors Tags: ,

SQL Error – The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)

December 24, 2016 6 comments

 
I was trying to export a SQL table to Excel and I got below error:

TITLE: SQL Server Import and Export Wizard
——————————
The operation could not be completed.
——————————
The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)
——————————

 

sqltoexcel-error
 

After searching a bit I came to know that the above provider is not installed, and I need to install the Microsoft Access Database Engine setup, to facilitate the transfer of data between existing Microsoft Office files such as “Microsoft Office Access 2010” (*.mdb and *.accdb) files and “Microsoft Office Excel 2010” (*.xls, *.xlsx, and *.xlsb) files to other data sources such as “Microsoft SQL Server”.

–> Get the Microsoft Access Database Engine 2010 Redistributable, [link].

There will be two files:

1. AccessDatabaseEngine (for. 32-bit)

2. AccessDatabaseEngine_x64 i.e. 64-bit

If your SQL Server is 32 bit, then install just the first one.
But if its 64 bit, then install both, first 32 bit & then 64 bit.

Categories: Excel, SQL Errors Tags: