Sql Server : TRY_PARSE function (Replacement for isNumeric, isReallyNumeric or isInteger)

Quick one today:

Years ago, there was a popular comment that Sql Server did not wide variety of functions; Once of them is the much needed IsNumeric functionality. Though such a function existed, there was a case where it resulted in incorrect results. So there were several custom functions like IsReallyNumeric or IsInteger, etc (my personal DBA library also had such custom functions)

Now, there is a powerful function that does more than just IsNumeric capability. It is called TRY_PARSE. Let’s take an example:

--
-- Sample code for TRY_PARSE
--
SELECT	  TRY_PARSE('ABCDEF' AS INT)		AS [INT_Test1]
	, TRY_PARSE('123456' AS INT)		AS [INT_Test2]
	, TRY_PARSE('ABCD-01-01' AS DATETIME)	AS [DateTime_Test1]
	, TRY_PARSE('2016-01-01' AS DATETIME)	AS [DateTime_Test2]
GO

This function takes input in VARCHAR/NVARCHAR format and attempts to convert into the requested format. If the result is successful, the value is returned in requested data type. If not, NULL is returned. This makes it very useful in data processing logic flows; And makes it more useful function than the legacy (IsNumeric) functions.

Hope this helps,
_Sqltimes

Read Full Post »

SSRS : rsAccessDenied : The permissions granted to user are insufficient for performing this operation (rsAccessDenied)

Interesting one today:

Setting up a new SSRS machine on one of the virtual machines in our lab, we ran into this interesting error after walking through the initial steps of install & configuration. Turns out, this is a common error after fresh installs. Following is the error message:

The permissions granted to user are insufficient for performing this operation. 
(rsAccessDenied) Get Online Help

Following steps have helped resolve it.

Resolution:

SSRS service runs under the security of a user account; My guess is that this account needs permissions to access the content on the SSRS site & folders. So we need to carry out two steps to assign proper permissions.

1. Folder Permissions
2. Site Permissions
3. Trusted Site Settings

Folder Permissions

Step 1:

Open browser with Administrative Privileges and point it to Reports Manager URL. Go to ‘Folder Settings’.

 Step 2:

In the properties page, go to the Security tab and click on ‘New Role Assignment‘

Step 3:

In the new page, add the user account for ‘Group or User Name’ field. Then assign ‘Content Manager‘ permissions to the user account. And hit okay to save the settings.

Site Settings

Step 1:

Use the same browse (with administrative permissions), to go to Site Settings on the right.

Step 2:

In the settings page, go to Security tab and click on New Role Assignment.

Step 3:

In the new page, add the windows user to the ‘Group or user name’; Assign System Administrator role and hit OK.

.

Trusted Site Settings

Go to Internet Explorer, Tools -> Internet Options -> Security tab > Click on Trusted Sites. In the new window, enter the URL for Report Manager and close.

Now you can open browser and open Report Manager without any security warning.

Note:

Once the permissions are set, close the browser and re-open it without administrative permissions. Going forward, it would work without needing to open browser with elevated permissions.

For more information, please refer to this BoL article.

Hope this helps,
_Sqltimes

Read Full Post »

SSRS : The report server cannot decrypt the symmetric key that is used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content

Interesting one today:

Recently, on one of the lab machines, we had to rebuild SSRS machine on a new VM. After walking through the regular install and other configuration steps, when browser is opened and pointed to the SSRS URL, we get this error.

Error Message:

The report server cannot decrypt the symmetric key that is used to access sensitive
or encrypted data in a report server database. You must either restore a backup key
or delete all encrypted content.

This error message seems cryptic and familiar at the same time. After pacing back and forth for a few minutes near my desk, it dawned on me. This VM with pre-installed SSRS must have some encryption keys set up already, that need recreation or just deletion.

Steps:

1. Open Reporting Services Configuration Manager
2. Connect to the SSRS engine on the VM
3. Open Encryption Keys tab on the left
4. Click on ‘Delete’ button to remove the keys (from previous set up)
5. Now open browser with Administration privileges
   1. Open SSRS URL

Now the error message is resolved.

Note: Since this is a lab machine (and freshly minted VM), these aggressive steps are warranted. But in a production environment, you want to restore the encryption keys with the correct backup keys.

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : A read operation on a large object failed while sending data to the client (solved)

Interesting error today:

Recently on one of out performance test environment, we ran into an interesting error that looked new (unfamiliar) and a bit perplexing. Below is the error:

Error: 7886, Severity: 20, State: 1
A read operation on a large object failed while sending  data to the client. 
A common cause for this is if the  application is running in READ UNCOMMITTED isolation level.  
This connection will be terminated.

When this error occurred?

Upon further digging (and rereading the error message a few times), it became clear that were performing some long-running NOLOCK query, which was not uncommon in this environment. As it turns out, this is retrieving some large objects and sending to client applications. But in our situation, both of them are not the root of the problem.

What could be the problem?

Since these kind of operations are not uncommon in this environment, it could not have been these operations that could have caused it. May be they contributed, but they did not seem to be the root cause. We ran the same operations several times, sometimes they throw this error and other times it runs fine. So, the problem is some where else.

Solution

Luckily, someone already talked about this error in detail here. It turns out, this particular database, before every performance test, is being rest to ‘AutoGrowth’ with small increments (a.k.a. ~ 1 MB). Once we corrected out preparatory steps (for performance test) to configure the database size properly, this error got resolved.

What the root cause?

As of now, it is not clear as to why this issue occurs, but this is my working theory. When a large read operations (reading LOB and sending to client) occur with NOLOCK isolation, it reads dirty data. It might be possible that, someone else is attempting to change the this LOB at the same time. This by itself would not be a problem, since NOLOCK allows reading uncommitted data; But might be a contributor.

Adding to that, there is not enough free space left in the data file to accommodate the new changes coming in. If its a larger LOB, with size greater than the ‘AutoGrowth’ setting of 1 MB, it might have caused some problem where Sql Server has to take a breather to expand the file properly before allowing changes to occur properly. So the best option is to stop the NOLOCK read connection, which is the least important or light weight task. Doing this would cause least negative impact to the database integrity, while keeping the cost minimal (connection close).

Not sure if this theory is correct, but just a made up theory.

Hope this helps,
_Sqltimes

Read Full Post »