List of Bugs Fixed in SQL Server 2014 SP1 – Part 6

SQLServerF1

With every release of SQL Server product or subsequent service packs or Cumulative updates, new bugs are encountered or discovered. Below are some of the bugs which were fixed with the release of Service Pack 1 (SP 1) for Microsoft SQL Server 2014.

SQL Server Connect BUG Number 714689
Evaluation results are written to output xml file multiple times when you implement Enterprise Management Framework against multiple servers and an error occurs against one of the servers in the list.
SQL Server Connect BUG Number 735543
Database goes into a restore mode when its backup is restored as a different database on the same instance.
SQL Server Connect BUG Number 736509
You cannot debug a stored procedure that calls sp_executesql in SQL Server Management Studio (SSMS). When F11 is pressed, you receive an ‘Object reference not set to an instance of object’ error message.
SQL Server Connect BUG Number 740181
SSMS does not fully manage Full-Text in SQL Server Express.
SQL Server Connect BUG Number 745566
SQL Server SMO ignores default constraint in SQL Server 2012 and SQL Server 2014.

SQL Server Connect BUG Number 764197
SSMS handles Numbered Stored procedures inconsistently.
SQL Server Connect BUG Number 769121
“Column ‘‘ does not belong to table summary. (System.Data)” error message when replicating tables have the same names but are located in different schemas.
SQL Server Connect BUG Number 773710
After reverting to a database snapshot that contains full-text indexes, you cannot create any ft_catalogs until you either restart SQL Server, detach and then attach the database, or take the database offline and then set the database online.
SQL Server Connect BUG Number 774317
SSMS occasionally crashes on close, which then causes it to automatically restart
SQL Server Connect BUG Number 785064
“Value of ‘null’ is not valid for ‘stream’ ” error message when you work with customer pipeline components in Business Intelligence Development Studio (BIDS).

SQL Server Connect BUG Number 785151
When you execute queries with Show Actual Query Plan turned on, the result of Null is returned, and you receive the following error message:
Error: Msg 50000, Level 16, State 10, Procedure test, Line 34 String or binary data would be truncated.
SQL Server Connect BUG Number 791929
“A system assertion check has failed” error message when you insert data into a partitioned view that has triggers.
SQL Server Connect BUG Number 797967
Create script duplicates the statements when scripting column level permissions in SSMS.
SQL Server Connect BUG Number 799430
SSMS may crash when you try to refresh the SSMS window icon on the task bar.
SQL Server Connect BUG Number 804901
Deploying new versions of large projects encounters a time-out during deployment into SSIS Catalog database (SSISDB). Additionally, you receive the following error messages:
Failed to deploy project. For more information, query the operation_messages view for the operation identifier ’219′. (Microsoft SQL Server, Error: 27203) Failed to deploy the project. Fix the problems and try again later.:Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.
SQL Server Connect BUG Number 805659
“Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries” error message when you parse or execute stored procedure.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2014

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

 

Unable to Open Logshipping BackJob Properties for Database with AlwaysON AG Created

Recently while working on a production deployment, faced an issue with SQL Server LogShipping. As, it was planned maintenance, we had to find and fix the issue immediately. Writing this blog, so that others can get an idea on what can be done when faced with this type of issue.

Coming to the issue, we had three SQL Server 2012 servers Server01, Server02 and Server03. AlwaysON Availability Group had been setup between Server01 and Server02. Logshipping is setup between Server 01 and Server 03.

We had a planned maintenance where the two servers Server 01 & Server02, hardware was planned to be replaced, so we planned in a way that we will take down one server at a time and perform the maintenance, while the other server will be serving production traffic.

So, we failed-over the AlwaysoN AG with listener name as “Prod_RPT_LSTNR” from Server01 to Server02 which was successful. We disabled Logshipping backup job on Server01, disabled copy and restore jobs. on Server03. Configured logshipping from Server02 to Server03, which completed successfully, new backup, copy and restore jobs started working fine. Now Server01 was taken down for maintenance.

Later, logshipping started failing with errors related to backup path being incorrect. We checked and found that drive where we pointed backup path was removed and we were told that the drive cannot be added back, so we had to change the backup path in logshipping.

We tried to change the path and tried to click on the Logshipping backup job button in logshipping configuration tab, but it was returning below error


TITLE: Microsoft SQL Server Management Studio
------------------------------
SQL Server Management Studio could not save the configuration of 'Server01' as a Primary.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An entry for primary server Server01, primary database ReportServerTempDB does not exist on this secondary. Register the primary first. (Microsoft SQL Server, Error: 32023)
--------------------------------------------------------

TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot show requested dialog.

– After some troubleshooting and research, we found that, when we configured logshipping from Server02 to Server03, we connected to the SQL instance Server02 from SSMS via AlwaysON AG listener name which was Prod_RPT_LSTNR, but later when we tried to change the backup settings we connected to SQL instance Server02 SSMS using server name which was Server02. Now, we connected back again to SQL instance Server02 via SSMS using Listener name, then it allowed us to click on logshipping backup job button and we were able to open the job properties with out any errors.

Running below command on Secondary server, will provide us with the details of logshipping primary database like primary_server, primary_database, backup_source_directory, backup_destination_directory, file_retention_period, etc.

sp_help_log_shipping_secondary_primary
@primary_server = 'SQLInstanceName',
@primary_database = 'DatabaseName'

Running below command on Primary server will provide details of primary database settings

sp_help_log_shipping_primary_database
@database = 'DatabaseName'

Now, important thing to understand here is that, when we configure logshipping in environments where we have multiple ways of connecting a SQL instance, example via AlwaysON Listener name, IP Address, Alias, etc, we need to connect using the same method later as well, if we want to make any changes to the logshipping configuration. This happens because, the SQL Server instance name which we used to connect will be stored in MSDB logshipping metadata and will look for same server name later, so if later we try to connect using another method by specifying different instance name like IP Address/listener/alias, it will mis-match with the metadata and will return errors.

Hope this was helpful.

Thanks,
SQLServerF1 Team

 

Starting SQL Server using -T902 Trace Flag to skip the Script Upgrade

Whenever we install a patch(Service Pack or Cumulative Update) to SQL Server or if we upgrade SQL Server from one version to another version(Ex: SQL Server 2008 to SQL Server 2012), at the end of the up-gradation process some scripts are applied to Master and MSDB databases. Sometimes, there are possibilities that the script upgrade may fail due to various reasons like below.

Script level upgrade for database ‘master’ failed after Applying SQL 2008 R2 SP2 due to Error “The database principal owns a schema in the database, and cannot be dropped”

Script Upgrade Failure to Apply msdb110_upgrade Sript

At that time we would have to start SQL Server instance using -T902 to skip SQL Server from attempting to apply the scripts, so that we can take corrective actions to fix the problem causing failure of the scripts and finally remove Trace Flag -T902 and start SQL Server normally which will then successfully apply the upgrade scripts.

Below are the steps to perform to start SQL Server to skip the script upgrade using Trace Flag -T902

1. Start -> All Programs -> Microsoft SQL Server 2008 R2 or the highest version installed -> Configuration Tools -> SQL Server Configuration Manager

2. In SQL Server Configuration Manager, click SQL Server Services.

3. In the right pane, right-click SQL Server (), and then click Properties.

4. On the Startup Parameters tab, in the Specify a startup parameter box, type the parameter (in this case the trace flag -T902), and then click Add. You will now see the parameters similar to below

SQL Server Istance Startup Parameters

SQL Server Istance Startup Parameters


5. Click OK.
6. Restart the Database Engine.
7. Connect to SQL Server instance from SQL Server Management Studio and take corrective actions to resolve the errors causing script upgrade to fail.
8. Now finally remove the -T902 trace flag from SQL Server Configuration manager
9. Restart SQL Server Instance
10. Verify Errorlog to make sure script upgrade finished successfully

 

How to add a new instance to a 2 node SQL Server Cluster under the same networkname

I have got this question in one of the forums where a novice user was trying perform SQL Server installation. He had a question where he installed a SQL Server instance as cluster aware with a network name, ex: SQLServerClus. Now he was trying to add another SQL Server instance as a cluster aware and wanted to use the same network name SQLServerClus.

This is not possible. We cannot use the same network name for two SQL instances. We need to use a different network name for the second instance.

A failover cluster instance contains:
– A combination of one or more disks in a Microsoft Cluster Service (MSCS) cluster group, also known as a resource group. Each resource group can contain at most one instance of SQL Server.
– A network name for the failover cluster instance.
– One or more IP addresses assigned to the failover cluster instance.
– One instance of SQL Server that includes SQL Server, SQL Server Agent, the Full-text Search (FTS) service, and Replication. You can install a failover cluster with SQL Server only, Analysis Services only, or SQL Server and Analysis Services

http://msdn.microsoft.com/en-in/library/ms179410(v=sql.105).aspx

 

Error while Editing the backup maintenance plan in SQL Server 2005

Recently while I was trying to edit a maintenance plan, when trying to edit the backup task using Management Studio resulted in an error. Below is the error message


TITLE: Microsoft SQL Server Management Studio
Cannot show the editor for this task.
Value of ’27/6/2014 12:00:00 AM’ is not valid for ‘Value’. ‘Value’ should be between ‘MinDate’ and ‘MaxDate’.

After some research, found that this has impacted other users as well and a connect bug Link has been filed for the same.

There was no fix released for this, but there is a work around available which resolved the issue and then I was able to edit the backup task in the maintenance plan.

Below is the work around

1. Modify Maintenance Plan.
2. Click On the backup task.
3. Click F4 button on the keyboard and you will find the properties window to the right corner of the SSMS.
4. Change the value in “expiredate” by adding 10 years or Clear out the value in “expiredate”

Backup Task Maintenance Plan Properties

Backup Task Maintenance Plan Properties


5. Save the maintenance plan.
6. Reopen the maintenance plan and now you should be able to edit the backup task.

Happy Reading,
SQLServerF1 Team