CONDITIONAL failed because the following SET optio

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CONDITIONAL failed because the following SET optio

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-09-23 : 15:10:16

I have a following script to generate some of my database table

USE [MyDBName]
GO
Alter DATABASE [MyDBName]
Set ALLOW_SNAPSHOT_ISOLATION ON;
Go
/****** Object: Table [dbo].[FILE_VERSION] Script Date: 08/26/2011 10:40:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FILE_VERSION](
[APS_SA_CREATE_DATETIME] [datetime] NULL,
[APS_SA_UPDATE_DATETIME] [datetime] NULL,
[VER_VERSION] [nvarchar](50) NULL
) ON [PRIMARY]
GO

Now when I ran it most of my ssql server 2008 server instance it works but one of my client machine is throwing following error

Msg 1934, Level 16, State 1, Procedure svrTrgNewDB, Line 11
CONDITIONAL failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Any Idea what might be wrong in it?

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-23 : 16:00:26

Is there a DDL trigger?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-09-23 : 16:41:02

No

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-09-23 : 17:16:22

Can you post the code for teh trigger that is causing the failure?

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-09-23 : 17:42:57

There isn't any trigger

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

Kristen
Test

22859 Posts

Posted - 2011-09-24 : 08:08:23

There isn't any trigger"


Msg 1934, Level 16, State 1, Procedure svrTrgNewDB, Line 11

is that not a trigger?

Can't remember if the error message says "Trigger" rather than "Procedure" if the error is in a trigger. Maybe its in a Procedure?

You can probably find columns that have ANSI_PADDING OFF using:



SELECT TOP 100
	is_ansi_padded, [Type]=LEFT(T.name, 20), [Table/Column]=O.name + '.' + C.name
FROM	sys.columns AS C
	JOIN sys.objects AS O
		 ON O.object_id = C.object_id
		AND O.type <> 'S'	-- Ignore SYSTEM_TABLE
	JOIN sys.types AS T
		 ON T.system_type_id = C.system_type_id
		AND T.name IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext')	-- CHAR types only
WHERE	is_ansi_padded = 0
ORDER BY O.name, C.name

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-09-24 : 12:35:44

No there is no trigger in my database.The difference we found is that new database server edition is SQL SERVER 2008 R@ while we were working with simple SQL SERVER 2008 edition where we haven't encounter it once.
Also your Query result nothing but when I run another query suggested in another post of mine at [url]http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/cb5fc0cd-b3dd-40bd-b23a-4270a0206844/?prof=required[/url] is as follows

SELECT * FROM sys.columns WHERE is_ansi_padded = 0
and type_name(system_type_id) LIKE '%var%'

give me two records.But i don't know which table or which column is throwing this padding problem

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

Kristen
Test

22859 Posts

Posted - 2011-09-24 : 12:54:34

"type_name(system_type_id) LIKE '%var%'"

Change my query from this:


AND T.name IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext')	-- CHAR types only


AND T.name LIKE '%var%'

Also run this to see what type of object svrTrgNewDB is


SELECT	O.type_desc, O.name
FROM 	sys.objects AS O
WHERE	O.name = 'svrTrgNewDB'

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-09-24 : 19:56:24

Both queries

SELECT TOP 100
is_ansi_padded, [Type]=LEFT(T.name, 20), [Table/Column]=O.name + '.' + C.name
FROM sys.columns AS C
JOIN sys.objects AS O
ON O.object_id = C.object_id
AND O.type <> 'S' -- Ignore SYSTEM_TABLE
JOIN sys.types AS T
ON T.system_type_id = C.system_type_id
AND T.name LIKE '%var%'
WHERE is_ansi_padded = 0
ORDER BY O.name, C.name

And

SELECT O.type_desc, O.name
FROM sys.objects AS O
WHERE O.name = 'svrTrgNewDB'

Resulted with no record

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-25 : 00:02:37

No. Database triggers aren't in sys.objects. But they ARE in sys.triggers.

SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE';

I don't think you got that error from the create table statement -- it's from the ALTER DATABASE statement. Which is failing inside of a database trigger. That trigger is probably calling a stored procedure, to do some sort of audit logging.

Which is stupid, becuase ALTER DATABASE commands are already logged.

Kristen
Test

22859 Posts

Posted - 2011-09-25 : 03:08:27

" But they ARE in sys.triggers"

Didn't think of that doh!

Kristen
Test

22859 Posts

Posted - 2011-09-25 : 03:14:13

"SELECT * FROM sys.columns WHERE is_ansi_padded = 0
and type_name(system_type_id) LIKE '%var%'

give me two records.But i don't know which table or which column is throwing this padding problem"

They are probably in the System tables then, and not part of your application. Remove this line from my query if you want to check:


		AND O.type <> 'S'	-- Ignore SYSTEM_TABLE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources