Executed as user: PROD\_tuition. 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’, see “Surface Area Configuration” in SQL Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.

Following code is causing failure

SET @Str = ‘INSERT INTO OPENROWSET(”SQLOLEDB”, ”Server=Tuitionaffordable;Trusted_Connection=yes;”,
”select sourceserver, tuitionsto, tuitionssubject, tuitionsbody from tools..tuitions”)
values(”’+ @tuitions_sourceserver + ”’, ”’ + @tuitions_tuitionsTo + ”’, ”’ + @tuitions_tuitionsSubject +”’, ”’ + @tuitions_tuitionsBody + ”’)’

The reason is as follows:

Executed as user: PROD\_tuition. 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’, see “Surface Area Configuration” in SQL Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.

Solution is as follows:

sp_configure ‘Ad Hoc Distributed Queries’ , ‘1’
RECONFIGURE

INTERVIEW PREPARATION IN 30 MINs – Read Following wires for a quick interview preparation.

  1. SQL Server DBA “Interview Questions And Answers”
  2. SQL Azure Interview Questions and Answers Part – 1
  3. SQL Azure Interview Questions and Answers Part – 2
  4. Powershell Interview Questions and Answers

Along with technical learning I would like to share some great articles for anyone interested in the betterment of his/her family life

Regards,
Online Powershell and SQL Classes @ http://tuitionaffordable.webstarts.com

Advertisements

2 comments

  1. Solution is as follows:
    EXECUTE SP_CONFIGURE ‘show advanced options’, 1
    RECONFIGURE WITH OVERRIDE
    GO

    EXECUTE SP_CONFIGURE ‘Ad Hoc Distributed Queries’, ‘1’
    RECONFIGURE WITH OVERRIDE
    GO

    EXECUTE SP_CONFIGURE ‘show advanced options’, 0
    RECONFIGURE WITH OVERRIDE
    GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s