How to set QuotedIdentifier property off or on for existing table

Hi All,
How to set QuotedIdentifier property off or on for existing table. I tried
like
SET QuotedIdentifier off.
But the property is not chnaging.
Any idea?
Thanks in advance.
Supriya.Hi
This property is set when you create the table e.g
USE TEMPDB
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE TABLE tablquoteoff ( id int not null )
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE tablquoteon ( id int not null )
GO
SELECT
OBJECTPROPERTY(OBJECT_ID('tablquoteoff'),'IsQuotedIdentOn'),
OBJECTPROPERTY(OBJECT_ID('tablquoteon'),'IsQuotedIdentOn')
John
"Supriya Pagadala" wrote:
> Hi All,
> How to set QuotedIdentifier property off or on for existing table. I tried
> like
> SET QuotedIdentifier off.
> But the property is not chnaging.
> Any idea?
> Thanks in advance.
> Supriya.
>
>|||Thank for response.
I tried this.
SET QUOTED_IDENTIFIER OFF
GO
CREATE TABLE tablquoteoff ( id int not null )
But QUOTED_IDENTIFIER property of table is always true.
Not getting why it is not updating.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:03B62E37-AB8B-4D00-B9BE-4B7355112689@.microsoft.com...
> Hi
> This property is set when you create the table e.g
> USE TEMPDB
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> CREATE TABLE tablquoteoff ( id int not null )
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE tablquoteon ( id int not null )
> GO
> SELECT
> OBJECTPROPERTY(OBJECT_ID('tablquoteoff'),'IsQuotedIdentOn'),
> OBJECTPROPERTY(OBJECT_ID('tablquoteon'),'IsQuotedIdentOn')
>
> John
> "Supriya Pagadala" wrote:
>> Hi All,
>> How to set QuotedIdentifier property off or on for existing table. I
>> tried
>> like
>> SET QuotedIdentifier off.
>> But the property is not chnaging.
>> Any idea?
>> Thanks in advance.
>> Supriya.
>>|||Hello, Supriya
The IsQuotedIdentOn property is only meaningful for stored procedures,
triggers, views and UDF-s. For other types of objects (tables,
constraints, etc) it is always 1.
Razvan|||Hi
The script was to show that the value is taken when the table was created
and will not change with your connection setting. To change this setting I
believe you would need to create a new table and transfer the data.
John
"Supriya Pagadala" wrote:
> Thank for response.
> I tried this.
> SET QUOTED_IDENTIFIER OFF
> GO
> CREATE TABLE tablquoteoff ( id int not null )
> But QUOTED_IDENTIFIER property of table is always true.
> Not getting why it is not updating.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:03B62E37-AB8B-4D00-B9BE-4B7355112689@.microsoft.com...
> > Hi
> >
> > This property is set when you create the table e.g
> >
> > USE TEMPDB
> > GO
> >
> > SET QUOTED_IDENTIFIER OFF
> > GO
> >
> > CREATE TABLE tablquoteoff ( id int not null )
> > GO
> >
> > SET QUOTED_IDENTIFIER ON
> > GO
> >
> > CREATE TABLE tablquoteon ( id int not null )
> > GO
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('tablquoteoff'),'IsQuotedIdentOn'),
> > OBJECTPROPERTY(OBJECT_ID('tablquoteon'),'IsQuotedIdentOn')
> >
> >
> > John
> >
> > "Supriya Pagadala" wrote:
> >
> >> Hi All,
> >>
> >> How to set QuotedIdentifier property off or on for existing table. I
> >> tried
> >> like
> >>
> >> SET QuotedIdentifier off.
> >>
> >> But the property is not chnaging.
> >>
> >> Any idea?
> >>
> >> Thanks in advance.
> >>
> >> Supriya.
> >>
> >>
> >>
>
>

How to set ANSI_NULLS on on a existing table?

Hi,
I was trying to create an index on the indexed view, when I got this
error
Msg 1935 : Cannot create index.Object 'tablename' was created with the
following SET option off: ANSI_NULLS.
Is there anyway inside the database to set ANSI_NULLS on for the
existing table?
Thanks a lot for your help.
AJ> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
You'll need to recreate the table with SET ANSI_NULL ON.
Hope this helps.
Dan Guzman
SQL Server MVP
<aj70000@.hotmail.com> wrote in message
news:1144964972.095350.55480@.t31g2000cwb.googlegroups.com...
> Hi,
> I was trying to create an index on the indexed view, when I got this
> error
> Msg 1935 : Cannot create index.Object 'tablename' was created with the
> following SET option off: ANSI_NULLS.
> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
> Thanks a lot for your help.
> AJ
>|||Hi Dan,
Thanks for the reply. that is a bummer since I have atleast 12 tables
that i need to use for indexed view. and these tables contain financial
information.
AJ|||aj70...@.hotmail.com wrote:
> Hi Dan,
> Thanks for the reply. that is a bummer since I have atleast 12 tables
> that i need to use for indexed view. and these tables contain financial
> information.
> AJ
Were your tables created with ANSI_NULLS OFF by design or due to
ignorance? Unfortunately, ignorance is the usual reason. Take care
always to have ANSI_NULLS set to ON when creating tables or other
objects.
Note that Enterprise Manager defaults to ANSI_NULLS OFF when it's
installed. Those people who create tables with Enterprise Manager often
forget to change that setting first. Your procs and other objects may
suffer the same problem if you create them in EM. The smartest option
is to avoid EM altogether. Don't use EM to create objects.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

How to set ANSI_NULLS on on a existing table?

Hi,
I was trying to create an index on the indexed view, when I got this
error
Msg 1935 : Cannot create index.Object 'tablename' was created with the
following SET option off: ANSI_NULLS.
Is there anyway inside the database to set ANSI_NULLS on for the
existing table?
Thanks a lot for your help.
AJ> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
You'll need to recreate the table with SET ANSI_NULL ON.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<aj70000@.hotmail.com> wrote in message
news:1144964972.095350.55480@.t31g2000cwb.googlegroups.com...
> Hi,
> I was trying to create an index on the indexed view, when I got this
> error
> Msg 1935 : Cannot create index.Object 'tablename' was created with the
> following SET option off: ANSI_NULLS.
> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
> Thanks a lot for your help.
> AJ
>|||Hi Dan,
Thanks for the reply. that is a bummer since I have atleast 12 tables
that i need to use for indexed view. and these tables contain financial
information.
AJ|||aj70...@.hotmail.com wrote:
> Hi Dan,
> Thanks for the reply. that is a bummer since I have atleast 12 tables
> that i need to use for indexed view. and these tables contain financial
> information.
> AJ
Were your tables created with ANSI_NULLS OFF by design or due to
ignorance? Unfortunately, ignorance is the usual reason. Take care
always to have ANSI_NULLS set to ON when creating tables or other
objects.
Note that Enterprise Manager defaults to ANSI_NULLS OFF when it's
installed. Those people who create tables with Enterprise Manager often
forget to change that setting first. Your procs and other objects may
suffer the same problem if you create them in EM. The smartest option
is to avoid EM altogether. Don't use EM to create objects.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--