August 28, 2009 at 8:34 am
I have a procedure for disabling and re-enabling default constraints on our database tables - this has been necessitated by the need to modify a function that's being used in some of the constraints.
Unfortunately, one of the tables happens to have a size that is greater than 8060 (the max. allowed by SQL Server 2000/2005). I want to suppress/ignore the sql warning about the exceeded row size. how can i achieve this?
Thank you.
August 31, 2009 at 6:56 am
[font="Verdana"]
Can you please come with more details so that we can understand the issue in full. Why there is need to disable and re-enable the constraints every time?
I think you need to work on table redesigning.
Mahesh[/font]
MH-09-AM-8694
August 31, 2009 at 7:53 am
There is a function fnOpsUsr that we are using to enforce default constraints. Now ae want to update the function. The function can not be modified without dropping and recreating it (poor design), hence the need to first drop all table default constraints and re-creating them.
I wrote a procedure to drop/recreate the constraints. It is when we recreate the constraints that the warnings are coming in. It seems there is a table in the db whose row size was already above 8060. However, the error only comes now, when we are modifying the schema.
I am giving the code for the function and the proc below.
function:
CREATE FUNCTION [dbo].[fnOpsUsr]()
RETURNS sysname
AS
/**********************************************************************************************
Description: Determines the current User by matching the current login against the
Login and Usr tables.
Usage: This function MUST be used in place of SELECT USER, SELECT CURRENT_USER,
SELECT USER_NAME() and SELECT SESSION_USER
This function allows clients to make use of Active Directory Groups for
managing logins on the SQL Server.
This function also makes it possible to make use of Application Roles.
Requirement: For SQL Logins, the login name must match the Ops user name otherwise
this function will fail. The database user name is irrelevant.
For Windows Logins, there must be a mapping between the Active Directory
account and the Ops User account in the OpsLogin table. The database
user name is irrelevant.
Returns: - CURRENT_USER if CURRENT_USER = 'dbo'
- A Ops user code if the login was successfully mapped to a Ops user
- NULL if none of the above.
***********************************************************************************************/
BEGIN
DECLARE @currentLogin sysname
DECLARE @currentUser sysname
SET @currentUser = CURRENT_USER
SET @currentLogin = SYSTEM_USER
IF @currentUser = 'dbo' -- SysAdmin's are always dbo. We sometimes rely on this.
RETURN (@currentUser)
ELSE
BEGIN
SET @currentUser = NULL
-- Try for a Windows Login to User mapping
SELECT @currentUser = OpsUsrCode FROM OpsLogin where WindowsLogin = @currentLogin
-- If not, try for a SQL Login to Our User mapping
IF (ISNULL (@currentUser, '') = '')
SELECT @currentUser = CONVERT (sysname, Code) FROM Usr where Code = @currentLogin
END
RETURN (@currentUser)
END
CREATE PROCEDURE dbo.OPS_Deactivate_Ref_Constraints
@ibackUpOrRestore tinyint,
@ifnName sysname
AS
BEGIN
SET NOCOUNT ON
DECLARE
@oname sysname,
@oparent_name sysname,
@uname sysname,
@cname sysname,
@otype char(2),
@mtext varchar(8000),
@sql nvarchar(4000)
IF @ibackUpOrRestore = 1
BEGIN
BEGIN TRAN
DECLARE get_constraint CURSOR READ_ONLY
FOR SELECT o.name as constraintName, OBJECT_NAME(o.parent_obj) As TableName,
u.name As SchemaName,
o.type As ConstrType,
c.Name As ColumnName,
m.text As ConstrDef
FROM sysobjects o
JOIN sysusers u ON (o.uid = u.uid)
JOIN syscolumns c ON (o.id = c.cdefault and ISNULL(ObjectProperty(c.cdefault, 'IsConstraint'), 0) = 1)
JOIN syscomments m ON (m.id = c.cdefault)
WHERE o.Type = 'D'
AND m.text LIKE '%'+@ifnName+'%'
OPEN get_constraint
FETCH NEXT FROM get_constraint
INTO @oname,
@oparent_name,
@uname,
@otype,
@cname,
@mtext
WHILE @@fetch_status = 0
BEGIN
IF NOT EXISTS (SELECT * FROM ConstraintsBackUp
WHERE functionName = @ifnName
AND ConstraintName = @oname
AND TableName = @oparent_name
AND SchemaName = @uname
AND ConstraintType = @otype
AND ColumnName = @cname
)
BEGIN
INSERT INTO ConstraintsBackUp (functionName, ConstraintName, TableName, SchemaName, ConstraintType, ColumnName, ConstrDef)
VALUES (@ifnName, @oname, @oparent_name, @uname, @otype, @cname, CONVERT(text, @mtext))
SET @sql = 'ALTER TABLE ' + @oparent_name + ' DROP CONSTRAINT ' + @oname
EXEC sp_executesql @sql
END
FETCH NEXT FROM get_constraint
INTO @oname,
@oparent_name,
@uname,
@otype,
@cname,
@mtext
END
COMMIT TRAN
CLOSE get_constraint
DEALLOCATE get_constraint
END--get_constraint cursor
ELSE--@ibackUpOrRestore = 0
BEGIN
BEGIN TRAN
DECLARE restore_constraint CURSOR READ_ONLY
FOR SELECT ConstraintName, TableName, SchemaName, ConstraintType, ColumnName, ConstrDef
FROM ConstraintsBackUp WHERE functionName = ISNULL(@ifnName, '')
OPEN restore_constraint
FETCH NEXT FROM restore_constraint
INTO @oname,
@oparent_name,
@uname,
@otype,
@cname,
@mtext
WHILE @@fetch_status = 0
BEGIN
IF NOT EXISTS (SELECT * FROM sysobjects o
JOIN sysusers u ON (o.uid = u.uid)
JOIN syscolumns c ON (o.id = c.cdefault and ISNULL(ObjectProperty(c.cdefault, 'IsConstraint'), 0) = 1)
JOIN syscomments m ON (m.id = c.cdefault)
WHERE ISNULL(o.name, '') = ISNULL(@oname, '')
AND ISNULL(OBJECT_NAME(o.parent_obj), '')
= ISNULL(@oparent_name, '')
AND ISNULL(u.name, '') = ISNULL(@uname, '')
AND ISNULL(o.type, '') = ISNULL(@otype, '')
AND ISNULL(c.Name, '') = ISNULL(@cname, '')
)
BEGIN
SET @sql = 'ALTER TABLE ' + @oparent_name + ' ADD CONSTRAINT ' + @oname +' DEFAULT '+@mtext+' FOR '+@cname--recreate
EXEC sp_executesql @sql
END
FETCH NEXT FROM restore_constraint
INTO @oname,
@oparent_name,
@uname,
@otype,
@cname,
@mtext
END
DELETE FROM ConstraintsBackUp--clear back up
WHERE functionName = ISNULL(@ifnName, '')
COMMIT TRAN
CLOSE restore_constraint
DEALLOCATE restore_constraint
END--restore_constraint cursor
END
August 31, 2009 at 8:07 am
There is a function fnOpsUsr that we are using to enforce default constraints. Now ae want to update the function. The function can not be modified without dropping and recreating it (poor design), hence the need to first drop all table default constraints and re-creating them.
I wrote a procedure to drop/recreate the constraints. It is when we recreate the constraints that the warnings are coming in. It seems there is a table in the db whose row size was already above 8060. However, the error only comes now, when we are modifying the schema.
I am giving the code for the function and the proc below.
function:
CREATE FUNCTION [dbo].[fnOpsUsr]()
RETURNS sysname
AS
/**********************************************************************************************
Description: Determines the current User by matching the current login against the
Login and Usr tables.
Usage: This function MUST be used in place of SELECT USER, SELECT CURRENT_USER,
SELECT USER_NAME() and SELECT SESSION_USER
This function allows clients to make use of Active Directory Groups for
managing logins on the SQL Server.
This function also makes it possible to make use of Application Roles.
Requirement: For SQL Logins, the login name must match the Ops user name otherwise
this function will fail. The database user name is irrelevant.
For Windows Logins, there must be a mapping between the Active Directory
account and the Ops User account in the OpsLogin table. The database
user name is irrelevant.
Returns: - CURRENT_USER if CURRENT_USER = 'dbo'
- A Ops user code if the login was successfully mapped to a Ops user
- NULL if none of the above.
***********************************************************************************************/
BEGIN
DECLARE @currentLogin sysname
DECLARE @currentUser sysname
SET @currentUser = CURRENT_USER
SET @currentLogin = SYSTEM_USER
IF @currentUser = 'dbo' -- SysAdmin's are always dbo. We sometimes rely on this.
RETURN (@currentUser)
ELSE
BEGIN
SET @currentUser = NULL
-- Try for a Windows Login to User mapping
SELECT @currentUser = OpsUsrCode FROM OpsLogin where WindowsLogin = @currentLogin
-- If not, try for a SQL Login to Our User mapping
IF (ISNULL (@currentUser, '') = '')
SELECT @currentUser = CONVERT (sysname, Code) FROM Usr where Code = @currentLogin
END
RETURN (@currentUser)
END
CREATE PROCEDURE dbo.OPS_Deactivate_Ref_Constraints
@ibackUpOrRestore tinyint,
@ifnName sysname
AS
BEGIN
SET NOCOUNT ON
DECLARE
@oname sysname,
@oparent_name sysname,
@uname sysname,
@cname sysname,
@otype char(2),
@mtext varchar(8000),
@sql nvarchar(4000)
IF @ibackUpOrRestore = 1
BEGIN
BEGIN TRAN
DECLARE get_constraint CURSOR READ_ONLY
FOR SELECT o.name as constraintName, OBJECT_NAME(o.parent_obj) As TableName,
u.name As SchemaName,
o.type As ConstrType,
c.Name As ColumnName,
m.text As ConstrDef
FROM sysobjects o
JOIN sysusers u ON (o.uid = u.uid)
JOIN syscolumns c ON (o.id = c.cdefault and ISNULL(ObjectProperty(c.cdefault, 'IsConstraint'), 0) = 1)
JOIN syscomments m ON (m.id = c.cdefault)
WHERE o.Type = 'D'
AND m.text LIKE '%'+@ifnName+'%'
OPEN get_constraint
FETCH NEXT FROM get_constraint
INTO @oname,
@oparent_name,
@uname,
@otype,
@cname,
@mtext
WHILE @@fetch_status = 0
BEGIN
IF NOT EXISTS (SELECT * FROM ConstraintsBackUp
WHERE functionName = @ifnName
AND ConstraintName = @oname
AND TableName = @oparent_name
AND SchemaName = @uname
AND ConstraintType = @otype
AND ColumnName = @cname
)
BEGIN
INSERT INTO ConstraintsBackUp (functionName, ConstraintName, TableName, SchemaName, ConstraintType, ColumnName, ConstrDef)
VALUES (@ifnName, @oname, @oparent_name, @uname, @otype, @cname, CONVERT(text, @mtext))
SET @sql = 'ALTER TABLE ' + @oparent_name + ' DROP CONSTRAINT ' + @oname
EXEC sp_executesql @sql
END
FETCH NEXT FROM get_constraint
INTO @oname,
@oparent_name,
@uname,
@otype,
@cname,
@mtext
END
COMMIT TRAN
CLOSE get_constraint
DEALLOCATE get_constraint
END--get_constraint cursor
ELSE--@ibackUpOrRestore = 0
BEGIN
BEGIN TRAN
DECLARE restore_constraint CURSOR READ_ONLY
FOR SELECT ConstraintName, TableName, SchemaName, ConstraintType, ColumnName, ConstrDef
FROM ConstraintsBackUp WHERE functionName = ISNULL(@ifnName, '')
OPEN restore_constraint
FETCH NEXT FROM restore_constraint
INTO @oname,
@oparent_name,
@uname,
@otype,
@cname,
@mtext
WHILE @@fetch_status = 0
BEGIN
IF NOT EXISTS (SELECT * FROM sysobjects o
JOIN sysusers u ON (o.uid = u.uid)
JOIN syscolumns c ON (o.id = c.cdefault and ISNULL(ObjectProperty(c.cdefault, 'IsConstraint'), 0) = 1)
JOIN syscomments m ON (m.id = c.cdefault)
WHERE ISNULL(o.name, '') = ISNULL(@oname, '')
AND ISNULL(OBJECT_NAME(o.parent_obj), '')
= ISNULL(@oparent_name, '')
AND ISNULL(u.name, '') = ISNULL(@uname, '')
AND ISNULL(o.type, '') = ISNULL(@otype, '')
AND ISNULL(c.Name, '') = ISNULL(@cname, '')
)
BEGIN
SET @sql = 'ALTER TABLE ' + @oparent_name + ' ADD CONSTRAINT ' + @oname +' DEFAULT '+@mtext+' FOR '+@cname--recreate
EXEC sp_executesql @sql
END
FETCH NEXT FROM restore_constraint
INTO @oname,
@oparent_name,
@uname,
@otype,
@cname,
@mtext
END
DELETE FROM ConstraintsBackUp--clear back up
WHERE functionName = ISNULL(@ifnName, '')
COMMIT TRAN
CLOSE restore_constraint
DEALLOCATE restore_constraint
END--restore_constraint cursor
END
September 1, 2009 at 5:48 am
You can check out SET ANSI_WARNINGS statement although i am not sure whether it will help.
"Keep Trying"
September 1, 2009 at 8:10 am
i had unfairly ruled it out. i will give it a try and see if ig works.
Thank you.
September 2, 2009 at 7:04 am
This did the trick. Thank you so much.
September 3, 2009 at 12:13 am
Glad i could help.
"Keep Trying"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply