Technical Articles, training, Database Consulting, Blogging

Category Archives: Notes

When WITH CHECKSUM option is used while taking backup, the backup process will verify each page for checksums and torn page. In case bad page checksum found, the backup will stop. Using backup checksums may affect workload and backup throughput.

If you want to continue the backup, you have to write CONTINUE_AFTER_ERROR in backup WITH clause.
In case backup completes successfully it means no bad checksums.

OPENDATASOURCE
OpenDataSouce function helps you to get ad hoc connection information as part of a four-part object name as an one time alternative of linked server. You don’t have to specify or create the linked server to query other data sources (i.e. MS Excel, MS Access, MSSQL Older version to newer version etc.) if you are querying it infrequently.

You can use OPENDATASOURCE for the OLEDB data sources those are accessed infrequently, for several time use linked server as it provides more functionality.

You can get more information about the arguments of OpenDataSource function on MSDN site.

To use the OPENDATASOURCE you have to enable the ad hoc distributed queries. You required to have execute permission to use OPENDATASOURCE fucntion.

Common ErrorsError 1#
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider “SQLNCLI11” has not been registered.Solution: You will get above error if you have mentioned SQLNCLI11 while running OPENDATASOURCE query on SQL Server 2008 or lower version, it will work fine on SQL Server 2011. You can check list of registered provider by browsing Server Objects -> Linked Servers -> Provider in SSMSError 2#
Msg 15281, Level 16, State 1, Line 2
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.

SP_HELPTEXT system stored procedure is mostly used to check the object definition like definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure.

OBJECT_DEFINITION – is BUILT IN function returns the source text of the specified object. It returns the definition of check constraint, default constraint, stored procedure, function, rule and views.

Solution:
Above error occurs when the user with the minimum permission (i.e. SQLAgentReaderRole and SQLAgentUserRole) or the user is configured as job owner and trying to run the job which is running under the proxy account security context.

You can execute below script to grant permission to the user and fix the error.

Post navigation

Translator

Subscribe to this blog

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 1,029 other followers

Search @ SQLDBPOOL

Search for:

About Authors

Jugal Shah is the author & founder of this site. He has 14 plus years of experience as Cloud Solution Architect, Database Administrator and Developer in the Microsoft SQL Server, MySQL, PostgreSQL, MariaDB, Redshift and Aurora.

Email jugal.shah@sqldbpool.com

Server Server MVP (2010, 2011, 2012 and 2013)

Dhvani Shah is the author of this site. She has around 8 plus years of experience with the SQL Server and AWS cloud services. She has excellent skills in SQL Server database administration and development. She is also expert in RDS SQL Server, SSIS, SSRS and SSAS.

Blog Readers

1,842,265 Readers

Disclaimer

This is a personal blog. The opinions expressed here represent my own thoughts and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet/ BOL/MySQL. My employer do not endorse any tools, applications, books, or concepts mentioned on the blog. I have written my personal experience on this blog. Please don't direct implement or execute any query on production before have detailed study.