SQL Server Setup Failed with Error 0x80070002

SQLServerF1

SQL Server Installation most often or not completes smoothly without any problems, but there are many occasions too where it fails and it is not easy to understand the cause and fixing the problem. Below is one such error which causes the SQL Server Setup from not getting installed.

When you run the SQL Server setup install an MS-Dos window may popup and disappears and nothing else happens. Even if you wait for 10 or 15 minutes no further setup wizard would be launched. Generally when the setup has problems at the very initial stage, we will not even be able to find the SQL Server setup logs, but we will find couple of logs and one of it may have some error messages about the cause of the error. You can find the log file by typing the command %temp%/SqlSetup.log from Run window. You may notice an error similar to below.

.Net security policy file does not exist
Attempting to create .Net security policy file
Error: InitializeSqlSetupCodeGroupCore(32bit) failed
Error: InitializeSqlSetupCodeGroup failed: 0x80070002
Setup closed with exit code: 0x80070002

Above error occurs mostly due to problems with the .net framework which may be corrupted. So, to resolve this issue we will have to try fixing the .net framework problems.

Article explains the process about on how to manually remove and then reinstall the.NET Framework. Writing down the steps here as well, so try to follow the steps to fix the issue.

– Click Start, click Run, type installer in the Open box, and then click OK.
– On the View menu in Windows Installer, click Details, right-click the Name column header, and then click Comment.
– Right-click the cached Windows Installer file that has the Microsoft .NET Framework 2.0 RTL x86 enu comment, and then click Repair.
– After the repair process has completed, quit Windows Installer.

Next run the below below steps

– Open a command prompt by choosing Rus-as-Administrator”
– Change the directory to C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
– Type below command.
caspol.exe -machine -reset

After following the above steps the issue must have been resolved.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Setup or Installation Failure on Windows 7 or 8.1

SQL Server installation or setup may fail with various reasons. Below is one of the weird error (Attempted to perform an unauthorized operation.) one may receive while installing SQL Server 2008 R2 or SQL Server 2012 on a computer with Operating System as Windows 7 or Windows 8.1.

Slp: Sco: Attempting to write hklm registry key SOFTWARE\Wow6432Node\Microsoft\MSSQLServer to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20140114_235617\Registry_SOFTWARE_Wow6432Node_Microsoft_MSSQLServer.reg_
Slp: Attempted to perform an unauthorized operation.
Slp: Watson bucket for exception based failure has been created
SSIS: Failed to set registry permission on key ‘SOFTWARE\Microsoft\Microsoft SQL Server\100’ to SID ‘S-1-5-21-1526145828-320026549-172912389-1029’. Exception message: ‘Attempted to perform an unauthorized operation.’
Slp: Sco: Attempting to open registry subkey SOFTWARE\Microsoft\Microsoft SQL Server\100\SSIS
SSIS: Setting permision on registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\SSIS.
Slp: Sco: Attempting to replace account with sid in security descriptor D:(A;OICI;KR;;;S-1-5-21-1526145828-320026549-172912389-1029)
Slp: ReplaceAccountWithSidInSddl — SDDL to be processed: D:(A;OICI;KR;;;S-1-5-21-1526145828-320026549-172912389-1029)
Slp: ReplaceAccountWithSidInSddl — SDDL to be returned: D:(A;OICI;KR;;;S-1-5-21-1526145828-320026549-172912389-1029)
Slp: Sco: Attempting to set security descriptor D:(A;OICI;KR;;;S-1-5-21-1526145828-320026549-172912389-1029)
Slp: Sco: Attempting to normalize security descriptor D:(A;OICI;KR;;;S-1-5-21-1526145828-320026549-172912389-1029)
Slp: Sco: Attempting to replace account with sid in security descriptor D:(A;OICI;KR;;;S-1-5-21-1526145828-320026549-172912389-1029)
Slp: ReplaceAccountWithSidInSddl — SDDL to be processed: D:(A;OICI;KR;;;S-1-5-21-1526145828-320026549-172912389-1029)
Slp: ReplaceAccountWithSidInSddl — SDDL to be returned: D:(A;OICI;KR;;;S-1-5-21-1526145828-320026549-172912389-1029)
Slp: Sco: Attempting to normalize security descriptor D:(A;OICI;KR;;;S-1-5-21-1526145828-320026549-172912389-1029)
Slp: Sco: Attempting to replace account with sid in security descriptor D:(A;OICI;KR;;;S-1-5-21-1526145828-320026549-172912389-1029)
Slp: ReplaceAccountWithSidInSddl — SDDL to be processed: D:(A;OICI;KR;;;S-1-5-21-1526145828-320026549-172912389-1029)
Slp: ReplaceAccountWithSidInSddl — SDDL to be returned: D:(A;OICI;KR;;;S-1-5-21-1526145828-320026549-172912389-1029)
Slp: Prompting user if they want to retry this action due to the following failure:
Slp: —————————————-
Slp: The following is an exception stack listing the exceptions in outermost to innermost order
Slp: Inner exceptions are being indented
Slp:
Slp: Exception type: Microsoft.SqlServer.Configuration.Sco.ScoException
Slp: Message:
Slp: Attempted to perform an unauthorized operation.
Slp: Data:
Slp: WatsonData = SSIS
Slp: DisableRetry = true
Slp: Inner exception type: System.UnauthorizedAccessException
Slp: Message:
Slp: Attempted to perform an unauthorized operation.
Slp: Stack:

Slp: at System.Security.AccessControl.Win32.GetSecurityInfo(ResourceType resourceType, String name, SafeHandle handle, AccessControlSections accessControlSections, RawSecurityDescriptor& resultSd)
Slp: at System.Security.AccessControl.NativeObjectSecurity.CreateInternal(ResourceType resourceType, Boolean isContainer, String name, SafeHandle handle, AccessControlSections includeSections, Boolean createByName, ExceptionFromErrorCode exceptionFromErrorCode, Object exceptionContext)
Slp: at Microsoft.SqlServer.Configuration.Sco.SqlRegistrySecurity..ctor(ResourceType resourceType, SafeRegistryHandle handle, AccessControlSections includeSections)
Slp: at Microsoft.SqlServer.Configuration.Sco.SqlRegistrySecurity.Create(InternalRegistryKey key)
Slp: at Microsoft.SqlServer.Configuration.Sco.InternalRegistryKey.SetSecurityDescriptor(String sddl, Boolean overwrite)

This error can occur due to various reasons, we can try below steps which should mostly resolve this error.

– Run the SQL Server setup by choosing “Run-as-Administrator”

– If the SQL Server setup or Installation media is on a DVD or network share, then copy the entire media on to C:\temp folder and then start the installation by choosing Run-as-Administrator”

– Make sure the Setup account used has local administrator permissions. Also grant local admin permission to SQL Server service account.

– When you get this error, do not abort, instead open registry editor and Grant full permissions to below registry keys to the setup account and SQL Service account and then click on retry.
SOFTWARE\Microsoft\Microsoft SQL Server\100
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server

After above steps are followed, mostly the issue must have been resolved.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Agent Fails with Error The remote procedure call failed

SQLServerF1

SQL Server Agent is one of the important feature which allows DBAs to Create and Schedule jobs to perform regular maintenance or run application related jobs regularly. There are many other options provided by SQL Server Agent which include Creating Alerts and Operators, and sending database mails from Agent jobs or when the jobs fail, etc. SQL Server Agent service gets installed along with the SQL Server installation. A separate windows service gets added for SQL Server Agent and it is dependent on SQL Server Services. When the SQL Server Agent Service starts, a windows process is created for the SQL Server Agent and handles job execution, etc.

Although SQL Server Agent functions smoothly, sometimes it can fail due to various reasons. One of the error which DBAs may observed is the below mentioned error when you try to enable the SQL Server Agent service which is in disabled state. Most often or no this happens due to installation of any visual studio components which affects the files related to SQL Server.

WMI Provider Error
The remote procedure call failed.[0x800706be]

The above error can occur due to various reasons including some known issues. Below are some of the options which you can try to fix this error and then start the SQL Server Agent Service.

– Always use SQL Server Configuration Manager to make any changes to the SQL Server or SQL Agent services.

– Launch SQL Server Configuration Manager by “Run-as-Administrator”

– If the SQL Server Agent Service is in disabled state, then enable the service and then try to start the service. If you get the same error when you try to change it from disabled to enabled then try changing it from services.msc and after that come back to SQL Server configuration manager and then attempt to start the SQL Server Agent service.

– Apply latest Service Pack available for the SQL Server instance. Example, if SQL Server is of version SQL Server 2008 R2 SP1, then install SP2 which should fix any problems.

– If already the SQL Server is on latest version or latest service pack, then perform repair of the SQL Server instance using the SQL Server media.

– Try to start the SQL Server Agent service from command line using below command. Make sure command prompt is launched using “Run-as-Administrator”

SQLAGENT90 -C -V > C:\SQLAGENT.OUT

– If there are multiple instances of SQL Server or related components installed on the same server, then use the configuration manager of the latest version. Example: If there are SQL Server 2008 R2 and SQL Server 2012 components installed on the server, then use the SQL Server 2012 configuration manager.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Agent SSIS Job Fails with TIMEOUT EXPIRED Error

SQLServerF1

SQL Server Integration Services (SSIS) packages can be created and used to perform various tasks related to ETL. Once the package is built, it can be reused or scheduled in a SQL Server Agent job so that it runs regularly to perform the task regularly based on the defined schedule.

SQL Server Agent allows running SSIS packages using the Integration Services Package as the job step type. Most often or not the SSIS packages runs fine through SQL Server Agent jobs, however sometimes it may fail due to various error. One of the common error SSIS package job may fail is due to TIMEOUT. Below is the error that you may see in the job history.

Code: 0xC0047062 Source: DTSTask_DTSDataPumpTask_1 ADO NET Source
Description: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper) End Error

This error can occur due to command timeout. SSIS package internally converts the task into SQL queries and sends the queries to run against the SQL Server instance. By default the timeout value will be 30 seconds. I a query does not complete by 30 seconds, then it will fail with TIMEOUT error.

To resolve this issue we will need to identify the queries which are taking more than 30 seconds. One way to find this is using SQL Server Profiler or Extended Events. Once the offending query is identified, we need to find options to tune this query.

If the package is related to vendor application or if it is acceptable for the query to take more time to complete, then you can consider increasing the timeout value from 30 seconds to higher value so that the query can take upto the number of seconds specified.

In this case for the above mentioned error, it was using ADO.NET connection, so in ADO.NET source task, click on the Properties and set the CommandTimeout from 30 to higher value or to zero (0) which indictes that the query can take any longer until it completes of fails.

What is SQL Server Integration Services?
Microsoft Integration Services is a platform for building enterprise-level data integration and data transformations solutions. You use Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data. The packages can work alone or in concert with other packages to address complex business needs. Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Unable to Restore System Databases Backup Due to Version Mismatch

SQLServerF1

You may receive an error as mentioned below while trying to restore a system database Master, Model or MSDB using an old backup file.

System.Data.SqlClient.SqlError: The backup of the system database on the device D:\msdb\msdb_backup.bak
cannot be restored because it was created by a different version of the server (10.50.2550) than this server (10.50.6000). (Microsoft.SqlServer.Smo)

This error occurs because, it is not allowed to restore a system databases using backup file of the same system database whose backup was performed while it was on a different build.

Example: If you have SQL Server 2012 SP2 instance and if you try to restore an System database while was of version SP1, then it will not allow you to restore as it requires the backup of same build.

In the above error, the MSDB backup was performed while the SQL Server instance was on build 10.50.2550 and they were trying to restore it on the SQL Server instance which was on build 10.50.6000. This kind of scenario happens when there are no regular backups being performed on the SQL Server instance and patches were applied to the SQL instance and some bad day the database got corrupted and now you cannot restore the backup anymore which was performed long back. Another situation when this issue happens could be that you performed backup on another SQL Server instance and using it to restore on another SQL Server instance.

The workarounds to this problem include, bring the SQL Server instance on to same build as that of the backup file, so that you could perform the system database restore. Other option is to restore the system databases on another test server which has SQL Server on same build and then extract the required data and create the same on which ever server required. You may also try to restore the database an user database and then extract the required data from it.

This problem is only specific to System databases, but not for User databases. We can perform user database backup on SQL Server 2014 SP1 and use it to restore on SQL Server 2014 RTM build.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Error Message When You Open SQL Server Configuration Manager

SQLServerF1

SQL Server Configuration manager is the important and preferred tool to make any changes to the SQL Server Services configurations related to changing SQL Service account password, Changes to Network Protocols, Enabling SSL, Changing SQL Server startup parameters, etc. This gets installed when any SQL Server components are installed on the server.

On servers with multiple SQL Server instances, same or different versions sometimes uses same files for configuration manager, thus sometimes causes problems or failures in opening the SQL Server Configuration manager.

Below is one of the common error which occurs on servers where there are multiple SQL Server instances of different versions or architectures are installed or uninstalled later, after which there are errors while opening SQL Server Configuration Manager.

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid namespace [0x8004100e]

This error occurs mostly because the WMI provider is either removed when you uninstall an instance of SQL Server or may have got corupted during installation of SQL Server or other related components.

To resolve this problem follow the below steps.

– Open a command prompt by choosing “Run-as-Administrator”
– Change the directory to C:\Program Files \Microsoft SQL Server\number\Shared folder
– Type the below command and change the number in the command to respective SQL Server version. Refer below for the correct number to use for the latest SQL Server version installed.
mofcomp “%programfiles(x86)%\Microsoft SQL Server\number\Shared\sqlmgmproviderxpsp2up.mof”
– Run the command by pressing Enter, which should complete without any errors.
– Close and open the SQL Server Confutation Manager by choosing “Run-as-Administrator”

The value of number depends on the version of SQL Server Version

Microsoft SQL Server 2012 110
Microsoft SQL Server 2008 R2 100
Microsoft SQL Server 2008 100
Microsoft SQL Server 2005 90

After following the above steps the issue will be resolved mostly.

What is SQL Server Configuration Manager?
SQL Server Configuration Manager is a tool to manage the services associated with SQL Server, to configure the network protocols used by SQL Server, and to manage the network connectivity configuration from SQL Server client computers. SQL Server Configuration Manager is a Microsoft Management Console snap-in that is available from the Start menu, or can be added to any other Microsoft Management Console display. Microsoft Management Console (mmc.exe) uses the SQLServerManager10.msc(10 is related to SQL 2008 or R2 and will be different for other versions) file in the Windows System32 folder to open SQL Server Configuration Manager.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Difference Between Select Count(*) and Count(1) in SQL Server

SQLServerF1

One of the most common question or confusion many DBAs or Developers have is about the difference between the below two commands in SQL Server

Select Count(*) from TableName
Select Count(1) from TableName

It is very common perception that the Count(1) perform better compared to Count(*), however it is not the case. If you test by looking at the execution plan, you will see same action being performed by both the commands and same number of rows being scanned. The time taken may be slightly different interms of CPU usage for count(*) , but is almost same as count(1). The confusion is generally because in older version of some RDBMS products like Oracle has difference in performance for select count(*) and count(1), but recent releases does not have any difference.

Below is sample execution plan which is same for both the commands on a test table with 10,000 rows.

Select Count(*) vs Select Count(1)

Select Count(*) vs Select Count(1)

Below is the IO statistics for both the operations which scans same number of records.

(1 row(s) affected)
Table ‘PCP_PhysicalDisk(_Total)’. Scan count 1, logical reads 9252, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table ‘PCP_PhysicalDisk(_Total)’. Scan count 1, logical reads 9252, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Other confusions people assume is that Select Count(*) will pull all the data of the table into memory which is not correct, you need to understand the difference between count(*) and select * which are different. Another assumption is in select (1) the 1 refers to the first column of the table which is incorrect too.

Another comparison which people make is comparing count(*), Count(1) and count(Column). Count(Column) will return all non-null rows, where are other two count(*) and count(1) returns all rows weather they are null or not null.

Some combinations of Count function in SQL Server and their description.

COUNT(*) returns the number of items in a group. This includes NULL values and duplicates.
COUNT(1) returns the number of items in a group. This includes NULL values and duplicates.
COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.
COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.

For return values greater than 2^31-1, COUNT produces an error. Use COUNT_BIG instead.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Difference Between Native SQL Server Backups and Third-Party Backup Tools

One of the most important responsibility of SQL Server DBAs include planning and implementing proper Backup and Recovery. Although most of the times, the backups may not seem to be required, but its importance is known when there is an disaster or corruption with the database and we have to recovery as much of data as possible. Without database backups this may not be possible.

There are various methods or tools for performing SQL Server backups with each one having its own advantages and Dis-advantages. SQL Server has built-in mechanism to facilitate automating regular database backups through maintenance plans or custom jobs created on each SQL Server instance. Other methods include installing third party backup tools on central server and manage backups and restores of all SQL Servers from central repository.

Often when planning a proper backup and recovery strategy various options are considered and their advantages and disadvantages are taken into consideration and then finally the best method for their organization will be chosen.

Below are some of differences or advantages and disadvantages with SQL Server Native Backups and 3rd party backup/recovery tools.

– SQL Server Native backup and restore solution is free, meaning it comes built-in with SQL Server. But DBAs need to manually create maintenance plans or jobs on all SQL Server instances to perform backups. 3rd party backup tools are expensive and the licensing will be most often or not is for each server or instance which makes it very expensive for small organizations.

– Performing Backups through maintenance plans or jobs are considered more stable, where as the backups and restores performed by 3rd party tools cannot be fully trusted because they use their own way of performing backups and compressing the backups. 3rd party backups need to be tested very frequently to make sure that they can be recovered to avoid any surprises during disaster.

– Support for failures with the backups or restores from Microsoft is often limited to Native SQL Server backups. DBAs have to rely on the 3rd party support in case of failures with the tools.

– One of the very important advantage of 3rd party backups over native backups is managing the backups and restores from central server. For native solution, DBAs have to implement backup jobs on each SQL instance and then work on each instance when restore is required and has additional tasks like copying the backup files to the new server where restore has to be performed, etc.

– If a new SQL Server version is released, then the backup tool may not work with the new SQL Server versions, so we have to wait for the vendor to release new version of backup tool to work on newer versions of SQL Server. Native SQL backups does not have this issue as we can just create maintenance plan jobs on new SQL Server instance.

– There can be bugs with the 3rd party backup solutions so need to work with vendor and fix the problems.

– Additional management is required for installing and configuring 3rd party backup tools and applying patches to the 3rd party backup tools.

– Need to learn about the 3rd party backup tools on how they work and what options are available and will require either DBAs or SysAdmins to learn and update themselves about the backup tools.

Considering all the above aspects, one has to choose which solution works best for their environment. Most of the medium and large enterprise organizations use 3rd party backup solutions, however there are many who develop custom solution for their own environment using native SQL backup solution.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 
1 2 3 8