August 5, 2015 at 11:02 am
newbieuser (8/5/2015)
Thank you so much.. I've generated the alter commands below to make the constraints trusted. Totally there are about 2000 alter commands, but just wondering if it is possible to put them all in one script and report if any error or is it best to run them manually?ALTER TABLE dbo.table1 WITH CHECK CHECK CONSTRAINT constraint_name1;
ALTER TABLE dbo.table1 WITH CHECK CHECK CONSTRAINT constraint_name2;
....
Please help...
Run them manually. It will be a pain, but I would rather be able to confirm the data issues when the script fails than try to figure it out later after 2000 alters fail.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 5, 2015 at 11:06 am
I tried the below script, it prints all the alter commands. But, when I do execute @sql instead of print, I get the below error..
declare @sql nvarchar(max)
SELECT 'ALTER TABLE ' + SCH.name + '.' + TBL.name +
' WITH CHECK CHECK CONSTRAINT ' +FK.name + ';'
FROM sys.check_constraints AS FK
INNER JOIN sys.objects AS TBL
ON FK.parent_object_id = TBL.object_id
INNER JOIN sys.schemas AS SCH
ON FK.schema_id = SCH.schema_id
WHERE FK.is_not_trusted = 1
ORDER BY SCH.name, TBL.name, FK.name;
execute @sql
(1912 row(s) affected)
Msg 2812, Level 16, State 62, Line 12
Could not find stored procedure ''.
Also, it doesn't appear to run the alter commands.. After executing the above script there are still about 2000 constraints untrusted.. Please help...
August 5, 2015 at 3:28 pm
newbieuser (8/5/2015)
I tried the below script, it prints all the alter commands. But, when I do execute @sql instead of print, I get the below error..
declare @sql nvarchar(max)
SELECT 'ALTER TABLE ' + SCH.name + '.' + TBL.name +
' WITH CHECK CHECK CONSTRAINT ' +FK.name + ';'
FROM sys.check_constraints AS FK
INNER JOIN sys.objects AS TBL
ON FK.parent_object_id = TBL.object_id
INNER JOIN sys.schemas AS SCH
ON FK.schema_id = SCH.schema_id
WHERE FK.is_not_trusted = 1
ORDER BY SCH.name, TBL.name, FK.name;
execute @sql
(1912 row(s) affected)
Msg 2812, Level 16, State 62, Line 12
Could not find stored procedure ''.
Also, it doesn't appear to run the alter commands.. After executing the above script there are still about 2000 constraints untrusted.. Please help...
EXECUTE (@sql);
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 5, 2015 at 9:18 pm
I tried EXECUTE (@sql) as below, command completes successfully. But then it executes the alter command for only one constraint at a time... Please help...
declare @sql nvarchar(max)
SELECT @sql = 'ALTER TABLE ' + SCH.name + '.' + TBL.name +
' WITH CHECK CHECK CONSTRAINT ' +CK.name + ';'
FROM sys.check_constraints AS CK
INNER JOIN sys.objects AS TBL
ON CK.parent_object_id = TBL.object_id
INNER JOIN sys.schemas AS SCH
ON CK.schema_id = SCH.schema_id
WHERE CK.is_not_trusted = 1
ORDER BY SCH.name, TBL.name, CK.name;
EXECUTE (@sql);
Thanks a lot
August 6, 2015 at 12:33 am
Yes, because that generates just one ALTER.
Easier way, remove the variable and just have the commands printed to screen, then copy, paste into new query window and run all
SELECT 'ALTER TABLE ' + SCH.name + '.' + TBL.name +
' WITH CHECK CHECK CONSTRAINT ' +CK.name + ';'
FROM sys.check_constraints AS CK
INNER JOIN sys.objects AS TBL
ON CK.parent_object_id = TBL.object_id
INNER JOIN sys.schemas AS SCH
ON CK.schema_id = SCH.schema_id
WHERE CK.is_not_trusted = 1
ORDER BY SCH.name, TBL.name, CK.name;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 6, 2015 at 3:55 am
SQLRNNR (8/4/2015)
newbieuser (8/4/2015)
Thanks but my understanding was that only when enabling/disabling constraints needs to be created 'with check check'. Do we need to use 'with check check' when dropping and re-creating constraints as well?Thanks much.
Yes.
The MSDN entry for ALTER TABLE states:
WITH CHECK | WITH NOCHECK
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.
To me, that says if you drop a constraint then recreate it, the recreated constraint will be treated as newly added and therefore WITH CHECK will apply.
Having said that, in the past I have had constraints unexpectedly becoming untrusted after following that guidance and omitting the explicit WITH CHECK when recreating a previously dropped constraint.
August 6, 2015 at 8:46 am
If you have an SSIS package with a bulk load task and option 'Check Constraints' turned off, then it will automatically alter your check constraints to disable them prior to loading the table. Your check constraints will then be left in untrusted state until they are altered either manually, or you can add a task after bulk load to alter them.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply