Feeds:
Posts
Comments

Posts Tagged ‘Error Messages’

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’.

ssrs_permissions_error_foldersettings_1 Step 2:

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

ssrs_permissions_error_foldersettings_2

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.

SSRS_Permissions_Error_FolderSettings_3.PNG

Site Settings

Step 1:

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

ssrs_permissions_error_sitesettings_1

Step 2:

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

ssrs_permissions_error_sitesettings_2

Step 3:

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

ssrs_permissions_error_sitesettings_3.

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.

ssrs_permissions_error_trustedsitesettings_1

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 »

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.

ssrs_encryptionkeys

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 »

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 »

Quick one today:

Sometimes when we are setting up replication on our lab machines, we run into this error:

TITLE: Configure Distribution Wizard
------------------------------

An error occurred configuring SQL Server Agent.

------------------------------
ADDITIONAL INFORMATION:

RegCreateKeyEx() returned error 5, 'Access is denied.' (Microsoft SQL Server, 
Error: 22002)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2000&EvtSrc=MSSQLServer&EvtID=22002&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

 

Resolution:

This is easy to fix.

When you are going through the “Configure Distributor” wizard, do not select the option to “Yes, configure Sql Server Agent to start automatically“. Choose “No, I will start the Sql Server Agent manually” and go through the steps.

Once completed, go to Sql Server Configuration Manager and set the agent to start automatically.

The account under which these wizard runs does not have permissions to make registry changes. So just use the easier workaround.
Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

As a follow up to one of the previous posts on XML DML, this post goes into the next steps of the error message, if you have a typed XML column.

When you perform UPDATE (replace value of) on a typed XML column, sometimes you might run into this error:

Msg 6325, Level 16, State 1, Line 1
 XQuery: Replacing the value of a node with an empty sequence is allowed only 
if '()' is used as the new value expression. The new value expression evaluated to an 
empty sequence but it is not '()'.

T-Sql that throws this error:

--
-- Replace 'World' with 'Universe' in <ParentElement>..<Child2>
--
UPDATE dbo.SampleXML
SET XML_Col.modify('
		replace value of (/Root/ParentElement[@Att1='Brand']/Child2/text())[1]
		with Universe
')
WHERE ID = 1
GO

Repro Steps

First look at the code to replicate the issue:

--
-- Create new sample table
--
CREATE TABLE dbo.SampleXML (
	  ID		INT NOT NULL IDENTITY(1,1)
	, XML_Col	XML
)
GO

--
--	Create XML value
--
DECLARE   @X XML = '
        <Root>
            <ParentElement Att1='Color' Att2='10'>
                <Child1>Marco</Child1>
                <Child2>Polo</Child2>
            </ParentElement>
            <ParentElement Att1='Brand' Att2='16'>
                <Child1>Hello</Child1>
                <Child2>World</Child2>
            </ParentElement>
        </Root>'
--
-- INSERT XML value into table column
--
INSERT INTO dbo.SampleXML (XML_Col) VALUES (@X)
GO

--
-- See the result
--
SELECT *
FROM dbo.SampleXML
GO

Sample_XML_for_UPDATE

 

T-Sql that throws this error:

--
-- Replace 'World' with 'Universe' in <ParentElement> / <Child2>
--
UPDATE dbo.SampleXML
SET XML_Col.modify('
	replace value of (/Root/ParentElement[@Att1='Brand']/Child2/text())[1]
	with Universe
')
WHERE ID = 1
GO

 

Side story for context : Update Attribute value

In this UPDATE statement, we are attempting to update/replace value present in a node’s text. So, we need to approach it with caution. If it were updating/replacing value of an attribute, it would be straight forward. See example below:

--
--  Updating attribute '@Att2' value
--
UPDATE dbo.SampleXML
SET XML_Col.modify('
	replace value of (/Root/ParentElement[@Att1='Brand']/@Att2)[1]
	with '32'
')
WHERE ID = 1
GO

Solution

Unlike the above attribute example, here we are attempting to update the text() portion of a node. So, XML standard expects:

  • Conditions (if…else) to make sure correct XML element is identified & updated;
  • And it has a default value to fall back to in case there are any errors.

So, with some minor changes, the new code looks like this:

Version 1:

--
-- Replace 'World' with 'Universe' in <ParentElement> / <Child2>
--
UPDATE dbo.SampleXML
SET XML_Col.modify('
		replace value of (/Root/ParentElement[@Att1='Brand']/Child2/text())[1]
		with (
				if (/Root/ParentElement[@Att1='Brand']/Child2)
					then 'Universe'
				else
					'World'
			 )
	')
WHERE ID = 1
GO

Here are are

  • Identifying the exact section to ‘replace’ (/Root/ParentElement[@Att1=”Brand”]/Child2).
    • Then, we replace existing value with new value (Universe)
  • If the IF condition fails, for any reason, we have a fail back
    • update existing value with something else (in this case, same value ‘World’)

In some rare occasions, it might be needed to write the else clause slightly differently:

Version 2:

--
-- Replace 'World' with 'Universe' in <ParentElement2> / <Child2>
--
UPDATE dbo.SampleXML
SET XML_Col.modify('
			replace value of (/Root/ParentElement[@Att1='Brand']/Child2/text())[1]
			with (
				if (/Root/ParentElement[@Att1='Brand']/Child2)
					then 'Universe'
				else
					data(/Root/ParentElement[@Att1='Brand']/Child2)
				)
 		')
WHERE ID = 1
GO

In Version 2, we are essentially saying the same thing as (version 1) above, but with more code. If (/Root/ParentElement[@Att1=”Brand”]/Child2) exists, then use “Universe”, else use the existing value.

Happy XML DML!!

 

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Every once in a while, this error pops up in production environments, usually after application software upgrade. Error message looks something like this:

Length of LOB data (65536) to be replicated exceeds configured maximum 131072.
The statement has been terminated.

Essentially, what happened is that the size of LOB data that is being replicated has exceeded the upper limit settings. So Sql Server is complaining that in encountered sizes larger than what is is configured to replicate. There is an easy fix.

--
--  Increase the maximum size of text to replicate
--
EXEC sys.sp_configure N'max text repl size (B)', N'150000'
GO

RECONFIGURE WITH OVERRIDE
GO

max text repl size (B) indicates the maximum size in bytes that could be replicated in a single statement like INSERT UPDATE, etc.

For example, in our situation, we have a table with XML column. If the size of the XML file goes beyond the default size of 65536  bytes, this error occurs when that record is replicated.

As it is, XML columns can accept XML files larger than 65536 bytes during INSERT, UPDATE, etc; But when this row (new INSERT or UPDATE) needs to be replicated to Subscriber, the replication infrastructure may not be able to handle it as the upper limit was configured to 65536 bytes. As we saw above, it is easy to change.

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today: Earlier last week, I was playing with one of our customer databases in our lab environment. As part of implementing table partitioning, some tables changes were necessary; Moving data from one set of tables to another. In the middle of running those scripts, this error occurred:

Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.SORT temporary run storage: 140751663071232' 
in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by 
deleting unneeded files, dropping objects in the filegroup, adding additional files 
to the filegroup, or setting autogrowth on for existing files in the filegroup.

Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'. 

All the heavy data movement, between tables and reindexing, has incurred a lot of growth in the transactional log file of the user database; And equivalent growth in the data/log files of tempdb. So, we took the following steps to rectify it: As the script took a few hours to execute and complete all the data movement. We did the following steps, every few minutes:

  • Issue CHECKPOINT on the tempdb

USE tempdb
GO
CHECKPOINT
GO

  • Take Transactional log backup on the user database

BACKUP LOG CustomerXS TO DISK = N'M:\MSSQL\Backup\MSSQLSERVER\XS and RT\XS_Movement.trn'
WITH COMPRESSION
GO

This allowed us to create more free space in the database files to accommodate space for new activity.

Hope this helps,
_Sqltimes

Read Full Post »