Re: Status Column in sysobjects table...SQL2000

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

Date: Mon, 21 Feb 2005 09:52:48 -0600

Nishant,

What error are you getting? Please post a sample proc that demonstrates the
problem. As I mentioned previously, the only 'sticky' options are
QUOTED_IDENTIFIER and ANSI_NULLS. I suspect there are other variables
involved if these options are the same and you're getting different results. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"NB" <NB@discussions.microsoft.com> wrote in message 
news:1B168A63-6F77-47DC-A320-067E1C35CF5B@microsoft.com...
> Hi Dan,
>
> I mean when the same piece of code i.e. the stored procedure is compiled
> using isql or query analyser , it behaves differently at run time with
> identica; parameters it throws error in once case and executes 
> successfully
> in the query analyser compiled manner.
>
> Also the status column of sysobjects is different in all three cases when
> compiled from isql,osql or query analyser.
>
> Please advise.
>
> Regards,
> Nishant
>
> "Dan Guzman" wrote:
>
>> 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
    ... The QUOTED_IDENTIFER setting may or may not prevent the proc from being ... > Quoted_Identifiers and/or ANSI_NULLS it would not even compile. ... >> Dan Guzman ... >> SQL Server MVP ...
    (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)