Archive

Archive for the ‘Uncategorized’ Category

2020 blogging in review (Thank you & Happy New Year 2021 !!!)

December 31, 2020 Leave a comment

 

Happy New Year 2021… from SQL with Manoj !!!

As WordPress.com Stats helper monkeys have stopped preparing annual report from last few years for the blogs hosted on their platform. So I started preparing my own Annual Report every end of the year to thank my readers for their support, feedback & motivation, and also to check & share the progress of this blog.
 

In year 2019 & 2020 I could not dedicated much time here, so there were very few blogs posted by me. In mid 2019 something strange happened and my blog hits started declining day by day. Usually I used to get daily ~3.5k hits and within few months hits were reduced to just half. As the daily hits were under ~1.5k so I checked the SEO section in WordPress-admin, and I was surprised to discover that my blog & meta info was missing in Google webmaster.

I re-entered the meta info and since last 1 year the blog hits are stable at ~1k hits per day which is very low from what I was getting ~2 years back. Thus, you can see a drastic decline of hits in 2020 year below.

 
–> Here are some Crunchy numbers from 2020

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 281,285 times by 213,447 unique visitors in 2020. If it were an exhibit at the Louvre Museum, it would take about 40 days for that many people to see it.

There were 22 pictures uploaded, taking up a total of ~1 MB. That’s about ~2 pictures every month.

 

–> All-time posts, views, and visitors

SQL with Manoj all time views


 

–> Posting Patterns

In 2020, there were 10 new posts, growing the total archive of this blog to 561 posts.

LONGEST STREAK: 4 post in Oct 2020

 

–> Attractions in 2020

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

0. Blog Home Page (49,287 views)

1. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE (12,870 views)

2. Windows could not start SQL Server, error 17051, SQL Server Eval has expired (8,498 views)

3. Reading JSON string with Nested array of elements (8,016 views)

4. SQL Server 2016 RTM full and final version available (7,714 views)

5. Windows could not start SQL Server (moved Master DB) (6,988 views)

 

–> How did they find me?

The top referring sites and search engines in 2020 were:

SQL with Manoj 2020 Search Engines and other referrers

 

–> Where did they come from?

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

SQL with Manoj 2020 top Countries visitors

 

–> Followers: 442

WordPress.com: 180
Email: 262
Facebook Page: 1,480

 

–> Alexa Rank (lower the better)

Global Rank: 835,163 (as of 31st DEC 2020)
Previous rank: 221,534 (back in 2019)

 

–> YouTube Channel:

SQLwithManoj on YouTube
– Total Subscribers: 17,1700
– Total Videos: 70

 

–> 2021 New Year Resolution

– Write at least 1 blog post every week
– Write on new features in SQL Server 2019
– I’ve also started writing on Microsoft Big Data Platform, related to Azure Data Lake and Databricks (Spark/Scala), so I will continue to explore more on this area.
– Post at least 1 video every week on my YouTube channel

 

That’s all for 2020, see you in year 2021, all the best !!!
 

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


SSDT – OLE DB or ODBC error: Login failed for user ‘DOMAIN\ComputerName $’ 28000

March 23, 2017 1 comment

 
My colleague was working on a Tabular Model project and while working with the Tables so that they can be deployed to the Cube on Analysis Server, he was getting a weird error. The authentication was failing and in error message it was showing the computer name instead of his actual user name, suffixed with a $ sign.

Failed to save modifications to the server.
Error returned: ‘OLE DB or ODBC error: Login failed for user ‘DOMAIN\ComputerName$‘.; 28000.
A connection could not be made to the data source with the name of ‘CubeConnectionName’.

 

By looking at the above error it seemed that somewhere the authentication is being overridden by some setting. So after some research I found that in Data Source section there is Impersonation setting which can be set to a specific user, service account, current user or an unattended account.
 

–> Here is the resolution:

1. On Tabular Model Explorer go to the Project and expand Data Sources.

2. Here right click on the connection and click on Edit Data Source, or just double click on it.

3. Click on the Impersonation… button and check what option is selected.

… I found that the selection was defaulted to Service Account, and as my SSAS (Analysis Services) was running under the DOMAIN\ComputerName account which gets sets while installing SSAS. And as it does not have sufficient privileges or access rights, it fails to connect to the Database Server. Thus you need to check impersonation and provide an account which has access to the Database Server.

4. Change the selection from “Service Account” to “Specific Windows user name and password”, and apply the user name and password.

5. Click OK and Save the settings.

6. Now go again to the Table Properties and click on Refresh Preview button and click OK.


 


Categories: Uncategorized

SQL Error – The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE

June 22, 2016 9 comments

 
Today after setting up a new Database environment I ran into a small issue. While running a Stored Procedure I was getting following error:
 

Sql Severity : 16
Sql Message ID : 10314

Message
Executed as user: XYZdomain\XYZaccount. An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: System.IO.FileLoadException: Could not load file or assembly ‘XYZAssemblyName, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A) System.IO.FileLoadException: at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection) at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) at System.Reflection.Assembly.Load(String assemblyString) [SQLSTATE 42000] (Error 10314). The step failed.

 

I checked online and found that after restoring the Databases on DEV from PROD environment, the Stored Procedure that I was executing on DEV was CLR enabled thus it has the external_access or unsafe permission set from the database. And the login that was use to create the database on PROD was not same as in the instance of DEV DB. Thus it was not allowing to execute the CLR SP and resulting into error due to safety concerns.
 

Thus you can either use the Windows login or the SQL Server login which is common on both the environments (PROD & DEV, in my case), so:

1. First Enable Trustworthy database property

2. And change the database owner to ‘sa’

ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON;
GO

USE [DatabaseName]
GO

EXEC sp_changedbowner 'sa'
GO

 

This issue could also occur when you are Creating a new Assembly that has the external_access or unsafe permission set in the same database.

Server: Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly ‘XYZAssemblyName’ failed because assembly ‘XYZAssemblyName’ is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.


A very Happy SQL-licious & SQL-s-tastic Valentines day to all of you !!!

February 14, 2016 2 comments

Wish you all a very happy Valentines day !

Here is a love Query for you all:

SELECT NCHAR(10084)

Execute it and share it with your valentine 😂
 

… and now a special SQL Joke on this day!

--// Valentines special SQL query: for Guys

Select * from [Earth] 
where [Girl] = 'Single' 
and [status] = 'Available';

-- (0 row(s) affected)




--// Valentines special SQL query: for Girls

Select * from [Earth] 
where [Boy]  = 'Single' 
and [status] = 'Available';

-- System.outOfMemory.exception 
-- Too many rows to handle

 

For more jokes on SQL check here.
 

Subscribe my YouTube channel for more updates on SQL.


Categories: Uncategorized Tags:

Start a Blog | Why, What, How, Where

August 5, 2014 Leave a comment

I started Blogging almost 5 years back (early 2009). That time I didn’t thought to blog publicly, but just created a blog site in Google’s Blogger.com to create and track a library of technical articles that I can access anywhere. Suddenly I started getting hits on my posts and people started asking questions and suggestions. Then I thought to give my blog a professional look and add some quality content.

I was quiet impressed with WordPress.com so moved my blog to this new hosting site and SQLwithManoj.wordpress.com came into life.

Last week I thought to port my blog to a dedicated Domain name, so I upgraded my account in WordPress only rather than going with other domain registering sites. Now the blog’s new address is SQLwithManoj.com, which is maintained by WordPress only.

Lot of people ask me about Blogging, like: how to start blogging, what platform they should use, what content they should put, domain name, space, themes, etc. Here through this blog post I’m trying to answer some of the FAQs.
 

–> Main points to consider:

1. What do I blog:
– Think on a topic or subject you are passionate about, where you have deep/sound knowledge and can write about it very frequently, like Technology (All, or any Particular like SQL, .net, Java, etc.), Photography, Travel, Fashion, Politics, etc.
– Some people also blog to promote their own business/products/companies, etc.
– Make sure the content you put is genuine and will help people in reality.

2. How do I setup my blog:
– You can start with any of the free Blog Hosting sites, like WordPress, Google’s Blogger, Tumblr, etc.
– Or can go with Paid options for these site or other Hosts like GoDaddy, where you will get your own custom URL (Domain name) with more control.

3. How frequent should I blog: Depends upon the Topic, Time and your Passion.
– If it is current affairs and/or trending stuff, then daily, but you should have ample time.
– If automobiles/movies or similar then you should be the first and best (or amongst them) to blog them whenever they are launched.
– If its Technology then it could be daily/weekly/monthly, but at least one/two blog(s) per month to keep your readers connected with you.

4. Some Do’s & Don’ts:
– Do not copy other people’s blog posts.
– You can write about same topics what others have already posted, but make sure to give your post a different title.
– Its Ok to take tips from other Blogs, but the content should be genuinely written by you and should make a difference when compared by others.
– Use Widgets to provide more information and ease of access to your users to navigate on your blog site.
– Use Themes to beautify your blog, and can tweak CSS to give it a different look.
– Don’t forget to put a Disclaimer on the blog so that you do not come under any issues.
– Associate your blog URL or site map with Search Engines, like Bing, Google, etc. for SEO, so that you get max traffic/hits.
– Use inbuilt or third-party Stat counter, Comments, PingBacks, Rating systems/widgets to know the influence & popularity of your blog.
– Popularize your blog with Social media like a Facebook Page, LinkedIn, Twitter, etc.
 

Let me know if you have any questions or need clarifications on these points.

Happy Blogging !!!