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

 

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.