Wednesday, February 22, 2017

Power BI Desktop - Accessing SQL Server Database - Error: The user was not authorized

In this post, I am sharing a solution for a problem I faced while I started using Power BI. When you try to connect with SQL Server as Data Source, you may encounter this error message:

The user was not authorized

And it will not let you proceed.

There could be two possibilities for this problem you can try and fix it. First of all, of-course check if the user credentials are correct for login purpose. In my case, I am using a user name which letting me login to my SQL Server successfully, but when I try to connect through Power BI by same user name, it starts giving me the above error message.

Here is how I fixed it.

Solution 1:

  • Go to SQLSERVERNAME > Security > Logins > select you desired user name

  • Right click on selected user, select Properties, and go to Server Roles page.

  • Assign sysadmin role by setting the checkbox as true.

  • Click OK.

Now if you try login from Power BI desktop client, it should login successfully.

Just in cause, if it still not logged-in, you may try this second option. I don't know the real reason for this behavior but I found on internet while searching for my problem and find this workaround, so I am sharing it here if it may help someone facing similar problem.

Solution 2:

Your log-in screen may be similar to this:

Note that here Windows tab is selected and providing two options as Use my current credentials and Use alternate credentials. You might have tried both and it did not worked.

The trick is that, just use the second tab below Windows, named Database. Similar to this:

Put your user name and password in this tab, and it should work now.

I hope you find this post helpful. I would be happy to receive your thoughts about this, if you have encountered similar problem and fixed it. In case if you have tried some other option, please just mention in the comments below and I will add in this post so others people could get benefit.

Thursday, February 9, 2017

Cannot open backup device. Operating System error 5

I created small utility program for different routine tasks to run on different schedules, one of which is to take database backup to a local folder, and it was working fine. To have access it across network for selected users, and making it available for copy/move to other backup devices, we shared this folder on network. This was the moment I start getting this error in utility error log. In this post I will show the trick I used to solve this problem in our case. First let me share the script I used to take backups, here it is:

BACKUP DATABASE MyDatabaseName
TO DISK = 'D:\DBBackups\mydb-datetimestamp.Bak'  
 

After sharing the folder on network for specific users, it started generating the following error message:

    Msg 3201, Level 16, State 1, Line 1
    Cannot open backup device 'D:\DBBackups\mydb-datetimestamp.Bak'. Operating system error 5(Access is denied.).

    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.

Solution

  • Open Windows Services Screen. (Start > Administration > Services)
  • Find the Service in the list with name SQL Server (MSSQLSERVER), check for the Log On As column (if it doesn't already exist in the list, you can add it from the View menu > Add / Remove Columns...).

    The name of the account you see in Log on As column, is the account you need to give permissions to the directory where you want to place backups.

  • Right click on the folder. Go to > Properties > Share and Security tabs. And add permissions for required account.
  • Click Apply / OK, and let the permissions take effect.

Now try taking new backup, and it should work successfully.

Sunday, January 15, 2017

Crystal report document load hangs

I faced this problem after I made some changes in Crystal Report design. Somehow in Page Setup dialog box, the printer is changed in drop-down list, pointing to my local PC printer. And it was working fine at local PC but getting stuck when crystal report document is calling its Load() method on server.

Here is how I fixed it in my case:

  • Open report in Crystal Report Designer and Right Click
  • Select Design > Page Setup..., Page Setup Dialog box appears.

  • From the printer drop-down list, select Microsoft XPS Document Writer.

  • Click OK button.

Then I deployed the new report file on server, it stars working.

Monday, December 5, 2016

How can I select from list of values in SQL Server - Table Value Constructor (Transact-SQL)

Table Value Constructor provides a way to construct a table from row value expressions. It allows multiple rows of data to be specified in a single DML statement. One simpler version of select statement with single row values may be used like this:

select 1 as Id, 'ST0258' as ItemCode, 'LASER JET TONER HP 1320 (49A)' as ItemName

But what if we need to select multiple rows with value literals, we need Table Value Constructor. Lets see how we can write similar select statement but with multiple rows.

SELECT Id, ItemCode, ItemName
FROM
  (
    VALUES
  (1, 'ST0258', 'LASER JET TONER HP 1320 (49A)'),
  (2, 'ST0259', 'LASER JET TONER HP 1000 (15A)'),
  (3, 'ST0260', 'LASER JET TONER HP P1005 (35A)'),
  (4, 'ST0261', 'LASER JET TONER HP 540 (CM1312)'),
  (5, 'ST0262', 'LASER JET TONER HP P2035 (05A)')
  )
  AS MyTableName (Id, ItemCode, ItemName)

Its a really cool technique and helps me to save my time while working with small chunks of data or debugging scenarios.

You can also use subqueries to construct row values, but the limitation is that only single scalar values are allowed as a row value expression to construct a single cell value. A subquery that involves multiple columns is not allowed as a row value expression, i.e., you can not specify a subquery with more than one columns to construct multiple cells of a row. For example, the following code will generate syntax error because it contains a subquery constructing more than one columns.

SELECT Id, ItemCode, ItemName
FROM
  (
    VALUES
  (1, 'ST0258', 'LASER JET TONER HP 1320 (49A)'),
  (2, 'ST0259', 'LASER JET TONER HP 1000 (15A)'),
  (3, 'ST0260', 'LASER JET TONER HP P1005 (35A)'),
  (4, 'ST0261', 'LASER JET TONER HP 540 (CM1312)'),
  (5, 'ST0262', 'LASER JET TONER HP P2035 (05A)'),
  (select Id,Item_Code,Item_Name from dbo.items where Item_Code='ST0263')
  )
  AS MyTableName (Id, ItemCode, ItemName)

However, you can rewrite the statement by specifying each column separately in the subquery. The following example code will execute successfully.

SELECT Id, ItemCode, ItemName
FROM
  (
    VALUES
  (1, 'ST0258', 'LASER JET TONER HP 1320 (49A)'),
  (2, 'ST0259', 'LASER JET TONER HP 1000 (15A)'),
  (3, 'ST0260', 'LASER JET TONER HP P1005 (35A)'),
  (4, 'ST0261', 'LASER JET TONER HP 540 (CM1312)'),
  (5, 'ST0262', 'LASER JET TONER HP P2035 (05A)'),
  (
   (select Id from dbo.items where item_code='ST0263'),
   (select Item_Code from dbo.items where item_code='ST0263'),
   (select Item_Name from dbo.items where item_code='ST0263')
  )
  )
  AS MyTableName (Id, ItemCode, ItemName)

References:

Thursday, December 1, 2016

SQL Server Management Studio - Keyboard shortcut CTRL+K, CTRL+C not working

Keyboard shortcuts speed up development tasks in order to get more productive results. I encountered this problem that some keyboard shortcuts stopped working like CTRL+K, CTRL+C is not commenting the selected text, and it seemed unaffected. There could be two possible reasons which may cause this behavior:

  • First check if you have any add-ins installed? Perhaps this is the cause of making default shortcuts not working as expected because an add-in has taken over the control.
  • Second, check the keyboard scheme under Tools > Options > Environment > Keyboard. In my case, it has been switched to SQL Server 2000 you might try switching it back to Standard.