Limitations of using Stretch Database in SQL Server 2016

SQLServerF1

New feature which was introduced with SQL Server 2016 is Stretch Database which migrates our historical data transparently and securely to the Microsoft Azure cloud. Stretch Database provides some benefits to the users, but also has its own limitations which make it less likely to be used as of now, unless Microsoft comes up with significant improvements. Some of the benefits to decide on using SQL Server 2016 Stretch Database feature are, Provides cost-effective availability for cold data(historical data which is not accessed much, but still available to support user queries from Azure SQL database). Using this feature does not require any changes to the applications, this feature takes care of it internally and transparently. Moving cold or not frequently used data to Azure SQL database will reduce the maintenance efforts on the production data like less times required for backups, indexing statistics updates, etc.

Although this is great feature and very helpful for many organizations, but at this time of SQL Server 2016 RTM release, there are lot of restrictions which make it less likely to be used in many environments, because stretch database cannot be used with many widely used features. Some of the limitations are mentioned below.
– One of the difficult restriction which stops the usage of stretch database is that uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints in the Azure table that contains the migrated data.
– We can’t UPDATE or DELETE rows that have been migrated to Azure SQL database cloud, or on the rows that meet eligible criteria for migration, in a Stretch-enabled table or in a view that includes Stretch-enabled tables.
– We are not allowed to INSERT rows into a Stretch-enabled table over a linked server.
– We cannot create an index for a view that includes Stretch-enabled tables.
– Filters on SQL Server indexes are not propagated to the remote table.
– Some of the Table properties limitations for stretch database include, Tables that have more than 1,023 columns or more than 998 indexes, FileTabless or tables that contain FILESTREAMM data, Tables that are replicated, or that are actively using Change Tracking or Change Data Capture, Memory-optimized tables, etc.

– Data types that are not support in a table to be part of stretch database include, text, ntextt and image
timestampp, sql_variantt, XML, CLR data types including geometry, geographyy, hierarchyidd, and CLR user-defined types
– Column types that are not supported with stretch database include COLUMN_SETt, Computed columns, Constraints,
Default constraints and check constraints, Foreign key constraints that reference the table. In a parent-child relationship (for example, Order and Order_Detail), you can enable Stretch for the child table (Order_Detail) but not for the parent table (Order).
– Indexes which are not supported on stretch database include
Full text indexes, XML indexes, Spatial indexes, Indexed views that reference the table

Local database. The on-premises SQL Server 2016 Release Candidate (RC3) database.
Remote endpoint. The location in Microsoft Azure that contains the database’s remote data.
Local data. Data in a database with Stretch Database enabled that will not be moved to Azure based on the Stretch Database configuration of the tables in the database.
Eligible data. Data in a database with Stretch Database enabled that has not yet been moved, but will be moved to Azure based on the Stretch Database configuration of the tables in the database.
Remote data. Data in a database with Stretch Database enabled that has already been moved to Azure.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

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

 

SAP Error Messages and Error Codes from SAP Error 29-604 to 29-613

SQLServerF1

SAP Error: 29-604
Desc: No default defined for indicator
SAP Internal Error: 9721
SAP Error: 29-605
Desc: Enter usage again
SAP Internal Error: 9722
SAP Error: 29-606
Desc: Enter reference usage
SAP Internal Error: 9723
SAP Error: 29-607
Desc: Set at least one default
SAP Internal Error: 9724

SAP Error: 29-608
Desc: Enter default for (required entry)
SAP Internal Error: 9725
SAP Error: 29-609
Desc: Item field control key not supported
SAP Internal Error: 9726
SAP Error: 29-610
Desc: Enter item field control key
SAP Internal Error: 9727

SAP Error: 29-611
Desc: Maintain material entry
SAP Internal Error: 9728
SAP Error: 29-612
Desc: Enter “+” or “-”
SAP Internal Error: 9729
SAP Error: 29-613
Desc: Select one only (var-size, text, document, class, PM str. or phant. mat.)
SAP Internal Error: 9730

Above are list of SAP Error codes and Error Messages and their Description including SAP Internal state and exact error code from SAP Error 29-604 to 29-613 received while performing certain operation against SAP and related products.

What are SAP Error Messages and Error Codes?

There are many different types of error messages received while using SAP Suites which include different types of errors received while performing different operations against any SAP products like Customer Relationship Management (CRM), Enterprise Resource Planning (ERP), Product Lifecycle Management (PLM), Supply Chain Management (SCM), Supplier Relationship Management (SRM). Each SAP Error or Warning Message mentioned above contains the Warning or Error Message Statement, a short explanation of the probable causes of the Error message, and a recommended action where required. The received error will include Error code, Error Message or Warning Message and Internal Error.

Hope this was helpful.

Thanks,
SQLServerF1 Team
Information about SAP Error Messages or Warning Messages and SAP Error Codes on Windows and Linux Operating Systems.

 

Oracle Database Errors or Warnings from Error ORA-33911 to ORA-34061

SQLServerF1

ORA-33911: (MAKEDCL29) You cannot define a string in analytic workspace string because it has not been upgraded to version string.
Cause: User attempted to define an object that requires a certain compatibility setting in an AW that has not been upgraded to that compatibility level.
Action: Make sure that the database is running in the appropriate compatibility mode, and upgrade the AW.
ORA-33918: (MAKEDCL33) You cannot define a surrogate of dimension workspace object because it is a string.
Cause: Not all kinds of dimensions can have surrogates. The user attempted to define a surrogate of a prohibited kind of dimension.
Action: Do not attempt to define a surrogate on this dimension.
ORA-33920: (MAKEDCL34) The string SURROGATE must have one of the following data types: ID, NTEXT, TEXT, NUMBER, DATETIME, TIMESTAMP, or INTEGER.
Cause: The user attempted to define a surrogate without specifying a valid data type.
Action: Specify the data type (ID, NTEXT, TEXT, NUMBER, or INTEGER) in the definition of the surrogate.
ORA-33922: (MAKEDCL35) You cannot define a surrogate of dimension workspace object because it is a time dimension.
Cause: The user attempted to define a surrogate on a dimension that has type DAY, WEEK, MONTH, or YEAR.
Action: Do not attempt to define a surrogate on this dimension.

ORA-33998: (MODCOMP12) You cannot use both workspace object and workspace object as model dimensions, because workspace object is a surrogate of workspace object.
Cause: The user attempted to include both a dimension and its surrogate in the DIMENSION statement of a model.
Action: Use either the dimension or the surrogate in the DIMENSION statement of the model, but not both.
ORA-34000: (MODCOMP13) You cannot use both workspace object and workspace object as model dimensions, because they are both surrogates of dimension workspace object.
Cause: The user attempted to include two dimension surrogates of the same dimension in the DIMENSION statement of a model.
Action: Use either of the two surrogates in the DIMENSION statement of the model, but not both.
ORA-34001: (MODCOMP14) Concat leaf dimension workspace object already is used in a DIMENSION statement, either explicitly or as a leaf of another concat dimension.
Cause: Either two concat dimensions which share a common leaf dimension, or a concat and one of its leaves where both specified in the DIMENSION statement(s).
Action: Do not specify overlapping concat dimensions, or any leaves of specified concat dimensions.

ORA-34019: (MSCGADD03) workspace object is not a LIST PARTITION TEMPLATE.
Cause: User attempted to MAINTAINT ADD or DELETE a list value from a RANGE or CONCAT partition template.
Action: Partitioning in a RANGE or CONCAT partition template cannot be changed using the MAINTAIN command.
ORA-34021: (MSCGADD04) You must specify a partition when maintaining PARTITION TEMPLATE workspace object.
Cause: User attempted to MAINTAINT ADD or DELETE a list value from a LIST partition template, but didn’t specify which partition to add to or delete from.
Action: Specify the partition: maintain (template) add to partition (partitionname) (values) or maintain (template) delete from partition (partitionname) (values)
ORA-34059: (MSEXECUT12) You cannot delete non session-only dimension values from unique concat dimension workspace object.
Cause: The user tried to apply MAINTAIN DELETE to a non-SESSION value.
Action: Only use MAINTAIN DELETE to remove SESSION values from a concat dimension.
ORA-34061: (MSEXECUT11) Session-only values cannot be added to non-unique concat dimension workspace object, or any of its base dimensions.
Cause: Only UNIQUE concat dimensions can have custom member values.
Action: Use the CHGDFN command to change the concat dimension to UNIQUE and retry.

Above are list of Oracle Database Errors or Warnings from Error ORA-33911 to ORA-34061 received while performing certain operation against Oracle Database or related products.

What are atOracle Database Error Messages?

Oracle Error Messages may be returned while using products which are part of Oracle Database. Each Oracle Dabase Error or Warning Message mentioned above contains the Warning or Error Message Statement, a short explanation of the probable causes of the Error message, and a recommended action.

Hope this was helpful.

Thanks,
SQLServerF1 Team
Information about Oracle Database Error Messages or Warning Messages on Windows and Linux Operating Systems.

 

Informix Error Messages and Error Codes From Error 9606 to 9920

SQLServerF1

Informix Error Code -9906 Cannot modify column data type to collection type.
You cannot use an ALTER statement to change a column type to a collection type.
Informix Error Code -9907 Not allowed to modify collection type column.
You cannot use an ALTER statement to change a column type from a collection type to another type.
Informix Error Code -9908 Columns of a row type cannot contain fields of type TEXT, BYTE, SERIAL or SERIAL8.
You created a table that has a column with a named row of type TEXT, BYTE, SERIAL, or SERIAL8. The following example should fail:
create row type rtype (col1 byte); create table tab1 (c1 rtype);
Re-create the table with a different type of named row.

Informix Error Code -9910 Byte, Text, Serial or Serial8 data types in collection type not allowed.
The fields of a row within a collection and the elements of a collection cannot be of type TEXT, BYTE, SERIAL, or SERIAL8.
Informix Error Code -9911 Defaults on collection type column not allowed.
Default values are not supported in the definition a column of a collection type. Remove the default value clause.

Informix Error Code -9912 Constraints on collection type column not allowed.
Constraint expressions are not supported in the definition of a column of a collection type. Remove the constraint clause.
Informix Error Code -9919 Cast already exists in database.
Casts must be unique with regard to their source and destination types, regardless of cast class. Use the DROP CAST statement to remove an existing cast.
Informix Error Code -9920 Cannot create cast between the same type or between two built-in types.
No cast is needed between identical types. Casts between built-in types are handled by internal system casts and cannot be overloaded.

Above are list of Informix Error Messages and Errors Codes or Status Codes or Exit Codes along with Error and Warning messages received while performing certain operation against Informix applications or related products.

What are Informix Error Messages?
All Informix messages returned by the Informix server are assigned an error code.

In general, each Informix error contains the following information:
• The Informix Error Code or Informix Error status
• The message text. This text is usually returned with the Error code. Some messages employ word substitution, where the word substituted represents the system-replacement of a term more specific to the occurrence.
• An explanation of how the error may have occurred.

Hope this was helpful.

Thanks,
SQLServerF1 Team
Information about Informix Error Codes or Status Codes or Exit Codes and Error Messages on Windows, Linux Operating Systems.

 

Informix Error Messages and Error Codes From Error 9143 to 9504

SQLServerF1

Informix Error Code -9143 Character, Text, and Byte data cannot be printed with using formats.
The USING function can only be applied to numeric data. Revise the report specification.
Informix Error Code -9500 A subscript is out of bounds.
This statement takes a substring of a character variable. The substring values (that is, two numbers in square brackets) are incorrect. The first is less than zero or greater than the length of the column, or the second is less than the first. Correct the form specification and recompile.

Informix Error Code -9503 The table tablename could not be opened.
Check that the table name is correctly spelled. If so, the table tablename might not exist in the database (it might have been dropped or renamed); or if it exists, your account might not have SELECT access to it. Alternatively, if you are using the INFORMIX-SE database server, you might have exceeded the limit on open files, which can limit the number of tables that you can name in a SELECT statement. In this case, you might have to revise the SELECT section of the report to use more SELECT statements, each of which names fewer tables. Products after Version 4.0 do not issue this message.

Informix Error Code -9503 PRINT FILE filename could not be opened.
The file that is shown could not be opened for output. Look for operating-system error messages that might give more information. Possible causes include a full disk or lack of the correct permissions for the file or directory.
Informix Error Code -9504 The argument number is out of bounds for the ascii function. Arguments must be between 0 and 255, inclusive.
The input to the ASCII function must be a number between 0 and 255. Examine the use of this function in the report specification. If the improper value number came from a command-line parameter, you might consider testing it with an IF statement.

Above are list of Informix Error Messages and Errors Codes or Status Codes or Exit Codes along with Error and Warning messages received while performing certain operation against Informix applications or related products.

What are Informix Error Messages?
All Informix messages returned by the Informix server are assigned an error code.

In general, each Informix error contains the following information:
• The Informix Error Code or Informix Error status
• The message text. This text is usually returned with the Error code. Some messages employ word substitution, where the word substituted represents the system-replacement of a term more specific to the occurrence.
• An explanation of how the error may have occurred.

Hope this was helpful.

Thanks,
SQLServerF1 Team
Information about Informix Error Codes or Status Codes or Exit Codes and Error Messages on Windows, Linux Operating Systems.

 

SQL Server Errors or Failures from Error: 7644 to Error: 7663

SQLServerF1

 

Error: 7644, Severity: 16, Full-text crawl manager has not been initialized. Any crawl started before the crawl manager was fully initialized will need to be restarted. Please restart SQL Server and retry the command. You should also check the error log to fix any failures that mig
Error: 7645, Severity: 16, Null or empty full-text predicate.
Error: 7646, Severity: 16, Fulltext predicate references columns from two different tables or indexed views ‘%.*ls’ and ‘%.*ls’ which is not allowed.
Error: 7647, Severity: 10, Warning: Configuration of full-text catalog at ‘%ls’ could not be saved during detach database.

Error: 7648, Severity: 10, Warning: Failed to attach full-text catalog ‘%ls’.
Error: 7649, Severity: 10, Warning: Failed to dismount full-text catalog at ‘%ls’.
Error: 7650, Severity: 10, Warning: Failed to drop full-text catalog at ‘%ls’.
Error: 7651, Severity: 10, Warning: The ongoing population is necessary to ensure an up-to-date index. If needed, stop change tracking first, and then deactivate the full-text index population.
Error: 7652, Severity: 16, A full-text index for table or indexed view ‘%.*ls’ has already been created.
Error: 7653, Severity: 16, ‘%ls’ is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a

Error: 7654, Severity: 16, Unable to obtain the population status of the table or indexed view ‘%.*ls’.
Error: 7655, Severity: 16, TYPE COLUMN option must be specified with column of image or varbinary(max) type.
Error: 7656, Severity: 16, Full-text index for table or indexed view ‘%.*ls’ cannot be populated because the database is in single-user access mode.
Error: 7657, Severity: 10, Warning: Table or indexed view ‘%.*ls’ has full-text indexed columns that are of type image, text, or ntext. Full-text change tracking cannot track WRITETEXT or UPDATETEXT operations performed on these columns.
Error: 7658, Severity: 16, Table or indexed view ‘%.*ls’ does not have a full-text index or user does not have permission to perform this action.
Error: 7659, Severity: 16, Cannot activate full-text search for table or indexed view ‘%.*ls’ because no columns have been enabled for full-text search.
Error: 7660, Severity: 16, Full-text search must be activated on table or indexed view ‘%.*ls’ before this operation can be performed.
Error: 7661, Severity: 10, Warning: Full-text change tracking is currently enabled for table or indexed view ‘%.*ls’.
Error: 7662, Severity: 10, Warning: Full-text auto propagation is currently enabled for table or indexed view ‘%.*ls’.
Error: 7663, Severity: 16, Option ‘WITH NO POPULATION’ should not be used when change tracking is enabled.

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 Errors or Failures Error: 268 to Error: 289

SQLServerF1

You may receive various errors in SQL Server while working. Below are some of the SQL Server Errors from Error: 268 to Error: 289

Error: 268, severity 16,
Cannot run SELECT INTO in this database. The database owner must run sp_dboption to enable this option.

Error: 270, severity 16,
Object ‘%.*ls’ cannot be modified.

Error: 271, severity 16,
The column “%.*ls” cannot be modified because it is either a computed column or is the result of a UNION operator.

Error: 272, severity 16,
Cannot update a timestamp column.

Error: 273, severity 16,
Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

Error: 275, severity 16,
Prefixes are not allowed in value or pivot columns of an UNPIVOT operator.

Error: 276, severity 16,
Pseudocolumns are not allowed as value or pivot columns of an UNPIVOT operator.

Error: 277, severity 16,
The column “%.*ls” is specified multiple times in the column list of the UNPIVOT operator.

Error: 278, severity 16,
The text, ntext, and image data types cannot be used in a GROUP BY clause.

Error: 279, severity 16,
The text, ntext, and image data types are invalid in this subquery or aggregate expression.

Error: 280, severity 16,
Only base table columns are allowed in the TEXTPTR function.

Error: 281, severity 16,
%d is not a valid style number when converting from %ls to a character string.

Error: 282, severity 10,
The ‘%.*ls’ procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

Error: 283, severity 16,
READTEXT cannot be used on inserted or deleted tables within an INSTEAD OF trigger.

Error: 284, severity 16,
Rules cannot be bound to text, ntext, or image data types.

Error: 285, severity 16,
The READTEXT, WRITETEXT, and UPDATETEXT statements cannot be used with views or functions.

Error: 286, severity 16,
The logical tables INSERTED and DELETED cannot be updated.

Error: 287, severity 16,
The %ls statement is not allowed within a trigger.

Error: 288, severity 16,
The PATINDEX function operates on char, nchar, varchar, nvarchar, text, and ntext data types only.

Error: 289, severity 16,
Cannot construct data type %ls, some of the arguments have values which are not valid.

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.