Re: Status Column in sysobjects table...SQL2000

From: NB (NB_at_discussions.microsoft.com)
Date: 02/18/05 

Date: Fri, 18 Feb 2005 15:43:07 -0800

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: VS.Net - Opening SQLS2k Stored Procedures in the IDE
    ... Yes I am using SQLS2k. ... I certainly can use Query Analyser, and it is local on this machine. ... > securing SQL Server objects. ... >>The stored procedure is opened in a text editor that you can use to ...
    (microsoft.public.dotnet.general)
  • Re: OLE DB CAccessor problem
    ... > Now, I have tested my stored procedure using the Query Analyser, and I ... I don't have any experience of the consumer templates - I access the ... I tried using your syntax with my setup, ...
    (microsoft.public.data.oledb)
  • Re: Executing a Stored Procedure via Query Analysier is faster then a
    ... "David Hekimian" wrote in message ... > I have a complicated Stored Procedure that when run as a Scheduled Job ... > Is there a fundamental difference in how Query Analyser executes a Stored ...
    (microsoft.public.sqlserver.server)
  • Re: Exporting query results to file using | (pipe) as a deliminator.
    ... And you can schedule a DTS package to run, ... > I can do this manually by going to options in query analyser and setting the ... > The question is how would I do this in a stored procedure or in sql code to ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure and column default
    ... Satya SKJ ... "Dan Guzman" wrote: ... > I don't believe you can specify a stored procedure as the default. ...
    (microsoft.public.sqlserver.server)