Problem

When executing queries using OPENROWSET statement, the following error can appear:

Msg 15281, Level 16, State 1, Line 1

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.

Solution

This error appears because Ad Hoc Distributed Queries configuration parameter is disabled.

To enable Ad Hoc Distributed Queries follow the next steps:

First, make sure that you can see all SQL configuration settings when running sp_configure command by enabling the 'show advanced options' configuration parameter:

sp_configure 'show advanced options',1
reconfigure

After this, enable the 'Ad Hoc Distributed Queries' by running the following statements:

sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

Executing sp_configure command you can see that the parameter is enabled:

Full story »

Problem

When trying to restore a SSAS database cube from a backup file, the following error appears:

The following system error occurred:  The file exists. (Microsoft SQL Server 2005 Analysis Services)

Solution

I encountered this error in two different situations. In the first one, I was trying to restore a backup of an SSAS 2008 cube on an SSAS 2005 server.

Full story »

Problem

When running some transactions on a database the following error appears:

Msg 9002, Level 17, State 4, Line 2

The transaction log for database 'Database_Name' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Solution

After the transactions are committed and the data pages are written on the disk, SQL Server tries to clear the transaction log data that is not needed anymore. By doing, it tries to reuse the transaction log for future operations. There are several possible reasons why the transaction log cannot be reused: open transactions, replication, a transactional backup is necessary, etc

To find out which one applies to your case execute the following statement:

SELECT name, log_reuse_wait_desc FROM sys.databases

 Keep in mind that it is possible to have more than one reason preventing log reuse. The log_reuse_wait_desc column will show only one of the reasons and, after fixing it and query the sys.databases view again, you can see a different log_reuse_wait reason.

To better interpret the log_reuse_wait_desc column you can access the following link:

http://msdn.microsoft.com/en-us/library/ms345414.aspx

The above error appears because the transaction log is full (has reached the maximum file size).

Full story »

Problem

When trying to view the properties of a database (right click in SSMS on the database and choose Properties from the drop down menu) the following error is displayed:

Cannot show requested dialog. (SqlMgmt)

Property Owner is not available for Database ?[Database_Name]?. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

Solution

The problem seems to occur only on one database, for the other the Properties window is displayed without any problem. Also, the account that tries to view the database?s properties is sysasmin, so is not a security related issue which leads us to the conclusion that the problem is somehow database related.

Full story »

Considering the following tables (table_1 with IDENTITY column):

IF (object_id('table_1') IS NOT Null)
drop table table_1

CREATE TABLE table_1
   (
     Id bigint IDENTITY(1,1) NOT NULL, 
     Col1 nvarchar(4000) NOT NULL,
     Col2 nvarchar(4000) NOT NULL
   )

Id (identity)    Col1    Col2

-----------    ----    ----

IF (object_id('table_2') IS NOT Null)
drop table table_2

CREATE TABLE table_2
   (
     Col1 nvarchar(4000) NOT NULL,
     Col2 nvarchar(4000) NOT NULL
   )

Col1    Col2

----    ----

Insert some data into table_2:

INSERT INTO table_2 VALUES ('some text', 'some other text')
INSERT INTO table_2 VALUES ('some text', 'some other text')
INSERT INTO table_2 VALUES ('some text', 'some other text')

Full story »