:::: MENU ::::

Posts Categorized / DDL

  • Dec 03 / 2013
  • 0
Constraints and Keys, Data Modeling and Database Design, dbDigger, DDL, SQL Server Error messages

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

Today i was working to create foreign keys on few tables. Some of ADD CONSTRAINT statements got failed by generating the following error message.

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “%”. The conflict occurred in database “%”, table “%”, column ‘%’.

If we look closely at the descriptive message it points to the same foreign key constraint that we are just trying to create. Reason is that while creating the constraint SQL Server is trying to validate the existing data based on new constraint. There are some records with no reference in their base primary key table. Such records are cause of error here.

SOLUTION

we have couple of options. If you want to make sure that no such orphaned records be there in subject table you may just detect and delete them. Then ADD CONSTRAINT statement will work without any error. However deleting the data will hardly be a suitable option. Alternate is to use the ADD CONSTRAINT statement with NOCHECK option. It will make sure that existing data is not validated for constraint at time of creation.

WITH CHECK | WITH NOCHECK

Here is some informative text on these options from BOL.

Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.
If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.

QUERY OPTIMIZER BEHAVIOR

The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.

  • Aug 21 / 2013
  • 0
dbDigger, DDL, DDL Triggers, SQL Server Error messages, SQL Server Integration Services (SSIS), Triggers

Error generated in SSIS task

While importing few tables from server instance to another i got following error message.

http://4.bp.blogspot.com/-SQO5uAIPeh8/UhTljUKxEWI/AAAAAAAADR4/Xm_GZfJcKfQ/s1600/Problem+with+the+query+Result+Set+property+not+set+correctly,+parameters+not+set+correctly+or+connection+not+established+correctly

The user does not have permission to perform this action. Possible failure reasons: Problem with the query. “Result Set” property not set correctly, parameters not set correctly or connection not established correctly.

Error message was not looking fit for my scenario. In the mean while i got another report about errors being generated while creating tables on same server. The error message was pointing to failure of a DDL trigger meant for logging purpose. The trigger was being invoked on table creation and got failed due to permission issues while inserting logs in trigger log table. So i disabled the trigger and executed the task again. This time task was completed successfully.

So if you fall in above mentioned scenario then along with checking the reasons provided in error message also look for DDL trigger context.

  • Feb 29 / 2012
  • 0
Data Modeling and Database Design, Data Types, dbDigger, DDL, SQL Server Error messages

Table creation failed due to row size limit

Following error messages may be generated while trying to create index on table having row size greater than 8060 bytes. In case where index is also tried to create following error would be generated
Index ‘%’ row length exceeds the maximum permissible length of ‘8060’ bytes.

And while trying to create table with out index and size greater than 8060 bytes, following error would be generated
Creating or altering table ‘%’ failed because the minimum row size would be 16011, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

SQL Server extent size is 8060 bytes and is comprised of 8 pages. Individual table row under 8060 bytes size is requirement of table creation. However data types like image or text would not be considered as participant in max size. If row size is expected to cross this limit then error message would be generated and creation would be failed.

Work Around

Some work around may be applied to solve the issue.

  • Try to cut down the number of columns by normalization
  • Try to optimize the data types
  • Study feasibility to change columns like char(8000) columns with text data type

Following are some scenarios where error messages related to row max size may be generated

-- Row size error message without index   
 IF EXISTS (SELECT name FROM sys.objects WHERE name = 'tblMaxRowDemo')  
 DROP TABLE tblMaxRowDemo  
 GO  
 CREATE TABLE tblMaxRowDemo  
 (ID INT IDENTITY(1,1),  
 col1 CHAR(6000),col2 CHAR(6000))  
 GO  
 -- Row size error message with index   
 IF EXISTS (SELECT name FROM sys.objects WHERE name = 'tblMaxRowDemo')  
 DROP TABLE tblMaxRowDemo  
 GO  
 CREATE TABLE tblMaxRowDemo  
 (ID INT PRIMARY KEY IDENTITY(1,1),  
 col1 CHAR(6000),col2 CHAR(6000))  
 GO  
  • Jul 02 / 2008
  • 0
dbDigger, DDL, Triggers

DDL triggers

DDL triggers were introduced in SQL Server 2005. They execute T-SQL code when the structure of the table changes. The syntax is similar to that of DML triggers. DDL triggers are AFTER triggers that fire in response to DDL language events; they do not fire in response to system-stored procedures that perform DDL-like operations. They are fully transactional, and can be rolled back. You can run either Transact-SQL or CLR code in a DDL trigger. DDL triggers also support the EXECUTE AS clause similar to other modules.

SQL Server provides the information about the trigger event as untyped XML. It is available through a new, XML-emitting built-in function called EVENTDATA(). You can use XQuery expressions to parse the EVENTDATA() XML in order to discover event attributes like schema name, target object name, user name, as well as the entire Transact-SQL DDL statement that caused the trigger to fire in the first place. For examples, see EVENTDATA (Transact-SQL) in SQL Server Books Online.

Database-level DDL triggers fire on DDL language events at the database level and below. Examples are CREATE_TABLE, ALTER_USER, and so on. Server-level DDL triggers fire on DDL language events at the server level, for example CREATE_DATABASE, ALTER_LOGIN, etc. As an administrative convenience, you can use event groups like DDL_TABLE_EVENTS as shorthand to refer to all CREATE_TABLE, ALTER_TABLE, and DROP_TABLE events. The various DDL event groups and event types, and their associated XML EVENTDATA(), are documented in SQL Server Books Online.

Unlike DML trigger names, which are schema-scoped, DDL trigger names are database scoped or server-scoped.

Use this new catalog view to discover trigger metadata for DML triggers and database-level DDL triggers:

SELECT * FROM sys.triggers ;
GO

If the parent_class_desc column has a value of ‘DATABASE’ then it is a DDL trigger and the name is scoped by the database itself. The body of a Transact-SQL trigger is found in the sys.sql_modules catalog view, and you can JOIN it to sys.triggers on the object_id column. The metadata about a CLR trigger is found in the sys.assembly_modules catalog view, and again, you can JOIN to sys.triggers on the object_id column.

Use this catalog view to discover metadata for server-scoped DDL triggers:

SELECT * FROM sys.server_triggers ;
GO

The body of a Transact-SQL server-level trigger is found in the sys.server_sql_modules catalog view, and you can JOIN it to sys.server_triggers on the object_id column. The metadata about a CLR server-level trigger is found in the sys.server_assembly_modules catalog view, and again, you can JOIN to sys.server_triggers on the object_id column.

You can use DDL triggers to capture and audit DDL activity in a database. Create an audit table with an untyped XML column. Create an EXECUTE AS SELF DDL trigger for the DDL events or event groups you are interested in. The body of the DDL trigger can simply INSERT the EVENTDATA() XML into the audit table.

Another interesting use of DDL triggers is to fire on the CREATE_USER event and then add code to automate permissions management. For example, suppose you want all database users to get a GRANT EXECUTE on procedures P1, P2, and P3. The DDL trigger can extract the user name from the EVENTDATA() XML, dynamically formulate a statement like ‘GRANT EXECUTE ON P1 TO someuser’, and then EXEC() it.

Consult us to explore the Databases. Contact us