Re: Status Column in sysobjects table...SQL2000

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 02/12/05 

Date: Fri, 11 Feb 2005 21:33:02 -0600

The QUOTED_IDENTIFER setting may or may not prevent the proc from being
created but it is unlikely that the setting will affect results. On the
other hand, ANSI_NULLS will not affect the procedure create but you may get
different run-time behavior.

Can you elaborate on exactly what you mean when you say that the identical
stored procedures behave differently? 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"NB" <NB@discussions.microsoft.com> wrote in message 
news:95887047-E0CF-466B-95F0-548EFE5E32A8@microsoft.com...
> Thanks for your prompt reply dan but I think if there was an issue with 
> the
> Quoted_Identifiers and/or ANSI_NULLS it would not even compile. I did 
> check
> using the code you posted all procudures have both the properties set. I 
> am
> soory I should have also metioned that the databases compatibility level 
> for
> the database is set to 6.5 due to backward compatibility reasons.
>
> I am not sure if there is any way to get this undocumented information. I 
> am
> aware that Sybase usually includes such information in their manuals. I 
> will
> try searching SYBOOKS.
>
> Any suggestions are eagerly sought and highly appreciated.
>
> Thanks once again!!!
>
> Regards,
> Nishant
> "Dan Guzman" wrote:
>
>> These values are undocumented in SQL 2000 but you can use OBJECTPROPERTY 
>> to
>> determine the meaning.
>>
>> A likely cause of behavior differences in otherwise identical procs is 
>> the
>> ANSI_NULLS setting.  The ANSI_NULLS and QUOTED_IDENTIFIER settings at
>> creation time are remembered.  These override the connection settings at
>> execution time.  You can identify these settings using OBJECTPROPERTY:
>>
>> SELECT
>>     name,
>>     OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn') AS QuotedIdentOn,
>>     OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') AS AnsiNullsOn
>> FROM sysobjects
>> WHERE type = 'P' AND
>>     OBJECTPROPERTY(id, 'IsMSShipped') = 0
>>
>> -- 
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "NB" <NB@discussions.microsoft.com> wrote in message
>> news:B3C382E9-09B4-4AE1-87FB-66656A6126DD@microsoft.com...
>> > Hi,
>> >
>> > Can anyone please guide me as to what the different values in the 
>> > Status
>> > column of the Sysobjects table mean. I found the following values in my
>> > SQL
>> > 2000 server installation.
>> >
>> > 1610612736
>> > -536870912
>> > 16
>> > 1610612736
>> > 1610612993
>> > 1610612736
>> > 0
>> >
>> > The same procedure behave different if it is compiled and ends up with 
>> > a
>> > different status, even though there are no compilation errors.
>> >
>> > Regards,
>> > NB
>>
>>
>>

Relevant Pages

  • Re: Status Column in sysobjects table...SQL2000
    ... Please post a sample proc that demonstrates the ... >> Dan Guzman ... >> SQL Server MVP ... even though there are no compilation errors. ...
    (microsoft.public.sqlserver.programming)
  • Re: Database recovery with data file only
    ... Basically using that command breaks your business logic as there's no ... "Dan Guzman" wrote in message ... >> database Backup as well as ... >>> SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: how to scramble data in a table
    ... >> accomplish this with set-based processing using CASE expressions. ... >> Dan Guzman ... >> SQL Server MVP ... >>> Does anyone have a neat way to scramble data in one table? ...
    (microsoft.public.sqlserver.programming)
  • Re: transaction isolation level
    ... SET options will revert back to the original settings once the ... SQL Server MVP ... > Andrew J. Kelly SQL MVP ... >> will that count for every Select..From statement in the proc? ...
    (microsoft.public.sqlserver.programming)
  • Re: transaction isolation level
    ... SET options within a stored procedure only are in effect for the execution ... SQL Server MVP ... > Andrew J. Kelly SQL MVP ... >> will that count for every Select..From statement in the proc? ...
    (microsoft.public.sqlserver.programming)