Bug with SET options for table-valued functions.

Hi,

I think I've come across a bug in SQL Server 2000 (SP3):

If I create a table-valued function with the QUOTED_IDENTIFIER and ANSI_NULLS options both set to ON e.g.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION dbo.tfn_Test()
RETURNS TABLE
AS
RETURN (SELECT 1 AS test)
GO
Then when Scripting this function out again (or using external tools to analyse it - SQL Compare for example), it is scripted as

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.tfn_Test()
RETURNS TABLE
AS
RETURN (SELECT 1 AS test)
GO

It appears to run OK with the original settings, but obviously this script is incorrect.

And if I run this script against my database :
SELECT Name,
Type,
OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn') IsQuotedIdentOn,
OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') IsAnsiNullsOn
FROM sysobjects
WHERE type IN ('FN','IF','TF')

Then I get NULLs back for the ObjectProperty statements when the type is TF. The other function types return 1 or 0 as expected.

This would appear to be a bug in the way that SQL Server reports the properties out via DMO and the system functions for Table-Valued functions. It is breaking our syncronisation process (using SQL compare).

How do I submit this as a bug to Microsoft? And is it likely to be fixed in an update or Service Pack soon?

Thanks

Alex Weatherall

Further to this :

I can't actually modify table valued functions on SQL Server 2000 in the new SQL 2005 Management Studio due to this error:

TITLE: Microsoft SQL Server Management Studio

Property QuotedIdentifierStatus is not available for UserDefinedFunction '[dbo].[fn_nums]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&
EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=QuotedIdentifierStatus&LinkId=20476

This is due to the same problem. This bug needs fixing asap, it means that I can't use the new management tools to manage SQL Server 2000, I need to use Query Analyzer to access the code for all table valued functions.

Please can someone get back to me asap.

Thanks

Alex Weatherall
TeleWare.com
|||

Solution:

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=0750596e-9915-42ea-9295-62d1fb31d0a4

|||

You're right - the properties don't apply to table-valued functions in SQL Server 2000. In SQL Server 2000, the behavior is as if QUOTED_IDENTIFIER and ANSI_NULLS were always on.

The defect where Management Studio tries to get these properties for tabled-value functions in SQL Server 2000 servers has been fixed for SP2.

Thanks,
Steve

Bug in SQL Server 2000 sp3?

Hi
I have a table defined as
create table Project
(
Project nvarchar(20) collate database_default not null constraint
DF_Project_Project default '',
/* some other irrelevant fields */
CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
)
If I insert a long numeric value into Project (3123456789), I get some
troubles:
insert Project (Project) values ('3123456789')
If i execute the statement
select Project from Project
in Query Analyzer, I get the expected result (the record shown in the result
grid)
If i execute the statement
select * from Project
I get the following error (and no record shown in the result grid)
Server: Msg 248, Level 16, State 1, Line 1
The conversion of the nvarchar value '3123456789' overflowed an int column.
Maximum integer value exceeded.
Why??
My SQL Server version is (according to Select @.@.Version)
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
IMO, using Select * should be functionally equal to a Select statement
listing all fields in the table, but it is not.
Best regards,
Benny Tordrup
Benny Tordrup wrote:
> Hi
> I have a table defined as
> create table Project
> (
> Project nvarchar(20) collate database_default not null constraint
> DF_Project_Project default '',
> /* some other irrelevant fields */
> CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
> )
> If I insert a long numeric value into Project (3123456789), I get some
> troubles:
> insert Project (Project) values ('3123456789')
>
> If i execute the statement
> select Project from Project
> in Query Analyzer, I get the expected result (the record shown in the
> result grid)
> If i execute the statement
> select * from Project
> I get the following error (and no record shown in the result grid)
> Server: Msg 248, Level 16, State 1, Line 1
> The conversion of the nvarchar value '3123456789' overflowed an int
> column. Maximum integer value exceeded.
>
> Why??
> My SQL Server version is (according to Select @.@.Version)
>
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> IMO, using Select * should be functionally equal to a Select statement
> listing all fields in the table, but it is not.
> Best regards,
> Benny Tordrup
I went ahead and recreated your table here and don't get any error. Not
sure what the problem is.
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
David G.
|||You can get the error you mention if you have a computed column in the table
that uses the project column in its expression.
Jacco Schalkwijk
SQL Server MVP
"Benny Tordrup" <nospam dot bt at fk-data dot dk> wrote in message
news:%23WshbbwlEHA.3520@.TK2MSFTNGP11.phx.gbl...
> Hi
> I have a table defined as
> create table Project
> (
> Project nvarchar(20) collate database_default not null constraint
> DF_Project_Project default '',
> /* some other irrelevant fields */
> CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
> )
> If I insert a long numeric value into Project (3123456789), I get some
> troubles:
> insert Project (Project) values ('3123456789')
>
> If i execute the statement
> select Project from Project
> in Query Analyzer, I get the expected result (the record shown in the
> result
> grid)
> If i execute the statement
> select * from Project
> I get the following error (and no record shown in the result grid)
> Server: Msg 248, Level 16, State 1, Line 1
> The conversion of the nvarchar value '3123456789' overflowed an int
> column.
> Maximum integer value exceeded.
>
> Why??
> My SQL Server version is (according to Select @.@.Version)
>
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> IMO, using Select * should be functionally equal to a Select statement
> listing all fields in the table, but it is not.
> Best regards,
> Benny Tordrup
>
|||Jacco,
It was exactly the problem.
I had a computed column defined as
Cast(Case when IsNumeric(Project)=0 then 0 else Project end as
decimal(20,0))
Changing the definition to
Cast(Case when IsNumeric(Project)=0 then '0' else Project end as
decimal(20,0))
solved my problem
Thanks
Benny
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > skrev
i en meddelelse news:eGgnRixlEHA.596@.tk2msftngp13.phx.gbl...
> You can get the error you mention if you have a computed column in the
table
> that uses the project column in its expression.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Benny Tordrup" <nospam dot bt at fk-data dot dk> wrote in message
> news:%23WshbbwlEHA.3520@.TK2MSFTNGP11.phx.gbl...
>

Bug in SQL Server 2000 sp3?

Hi
I have a table defined as
create table Project
(
Project nvarchar(20) collate database_default not null constraint
DF_Project_Project default '',
/* some other irrelevant fields */
CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
)
If I insert a long numeric value into Project (3123456789), I get some
troubles:
insert Project (Project) values ('3123456789')
If i execute the statement
select Project from Project
in Query Analyzer, I get the expected result (the record shown in the result
grid)
If i execute the statement
select * from Project
I get the following error (and no record shown in the result grid)
Server: Msg 248, Level 16, State 1, Line 1
The conversion of the nvarchar value '3123456789' overflowed an int column.
Maximum integer value exceeded.
Why''
My SQL Server version is (according to Select @.@.Version)
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
IMO, using Select * should be functionally equal to a Select statement
listing all fields in the table, but it is not.
Best regards,
Benny TordrupBenny Tordrup wrote:
> Hi
> I have a table defined as
> create table Project
> (
> Project nvarchar(20) collate database_default not null constraint
> DF_Project_Project default '',
> /* some other irrelevant fields */
> CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
> )
> If I insert a long numeric value into Project (3123456789), I get some
> troubles:
> insert Project (Project) values ('3123456789')
>
> If i execute the statement
> select Project from Project
> in Query Analyzer, I get the expected result (the record shown in the
> result grid)
> If i execute the statement
> select * from Project
> I get the following error (and no record shown in the result grid)
> Server: Msg 248, Level 16, State 1, Line 1
> The conversion of the nvarchar value '3123456789' overflowed an int
> column. Maximum integer value exceeded.
>
> Why''
> My SQL Server version is (according to Select @.@.Version)
>
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> IMO, using Select * should be functionally equal to a Select statement
> listing all fields in the table, but it is not.
> Best regards,
> Benny Tordrup
I went ahead and recreated your table here and don't get any error. Not
sure what the problem is.
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
--
David G.|||You can get the error you mention if you have a computed column in the table
that uses the project column in its expression.
--
Jacco Schalkwijk
SQL Server MVP
"Benny Tordrup" <nospam dot bt at fk-data dot dk> wrote in message
news:%23WshbbwlEHA.3520@.TK2MSFTNGP11.phx.gbl...
> Hi
> I have a table defined as
> create table Project
> (
> Project nvarchar(20) collate database_default not null constraint
> DF_Project_Project default '',
> /* some other irrelevant fields */
> CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
> )
> If I insert a long numeric value into Project (3123456789), I get some
> troubles:
> insert Project (Project) values ('3123456789')
>
> If i execute the statement
> select Project from Project
> in Query Analyzer, I get the expected result (the record shown in the
> result
> grid)
> If i execute the statement
> select * from Project
> I get the following error (and no record shown in the result grid)
> Server: Msg 248, Level 16, State 1, Line 1
> The conversion of the nvarchar value '3123456789' overflowed an int
> column.
> Maximum integer value exceeded.
>
> Why''
> My SQL Server version is (according to Select @.@.Version)
>
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> IMO, using Select * should be functionally equal to a Select statement
> listing all fields in the table, but it is not.
> Best regards,
> Benny Tordrup
>|||Jacco,
It was exactly the problem.
I had a computed column defined as
Cast(Case when IsNumeric(Project)=0 then 0 else Project end as
decimal(20,0))
Changing the definition to
Cast(Case when IsNumeric(Project)=0 then '0' else Project end as
decimal(20,0))
solved my problem
Thanks
Benny
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> skrev
i en meddelelse news:eGgnRixlEHA.596@.tk2msftngp13.phx.gbl...
> You can get the error you mention if you have a computed column in the
table
> that uses the project column in its expression.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Benny Tordrup" <nospam dot bt at fk-data dot dk> wrote in message
> news:%23WshbbwlEHA.3520@.TK2MSFTNGP11.phx.gbl...
> > Hi
> >
> > I have a table defined as
> >
> > create table Project
> > (
> > Project nvarchar(20) collate database_default not null constraint
> > DF_Project_Project default '',
> > /* some other irrelevant fields */
> >
> > CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
> > )
> >
> > If I insert a long numeric value into Project (3123456789), I get some
> > troubles:
> >
> > insert Project (Project) values ('3123456789')
> >
> >
> > If i execute the statement
> >
> > select Project from Project
> >
> > in Query Analyzer, I get the expected result (the record shown in the
> > result
> > grid)
> >
> > If i execute the statement
> >
> > select * from Project
> >
> > I get the following error (and no record shown in the result grid)
> >
> > Server: Msg 248, Level 16, State 1, Line 1
> > The conversion of the nvarchar value '3123456789' overflowed an int
> > column.
> > Maximum integer value exceeded.
> >
> >
> > Why''
> >
> > My SQL Server version is (according to Select @.@.Version)
> >
> >
> > Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> > Dec 17 2002 14:22:05
> > Copyright (c) 1988-2003 Microsoft Corporation
> > Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> >
> > IMO, using Select * should be functionally equal to a Select statement
> > listing all fields in the table, but it is not.
> >
> > Best regards,
> >
> > Benny Tordrup
> >
> >
>

Bug in SP4

Hi,
I met a bug in SP4. I did the same test on two machines. One is a windows
2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2000
develope SP3. Both machines can't run any jobs by sp_start_job stored
procedure after installation of SP4. Same to sp_stop_job. The error is:
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 61
The specified @.job_name ('test1') does not exist.
Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
in SP4.
Bill
Bill,
I just tested this on w2000, SP4, and was able to start a job. Is it maybe
that you were using a non-sysadmin login, so didn't have access to the
msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
then from sysjobs to see.
"Bill Wang" wrote:

> Hi,
> I met a bug in SP4. I did the same test on two machines. One is a windows
> 2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2000
> develope SP3. Both machines can't run any jobs by sp_start_job stored
> procedure after installation of SP4. Same to sp_stop_job. The error is:
> Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> Line 61
> The specified @.job_name ('test1') does not exist.
> Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
> in SP4.
> Bill
|||Thanks, Mary.
I tested the select from sysjobs and sysjobs_view. No permission issues on
these two table/view. Itested SP4 with sa. Before installation of SP4, I can
run these stored procedure, then install SP4 and run it with same user. It
failed with the error.
Did you install other hotfix after SP4 ?
Thanks!
Bill
"Mary" wrote:
[vbcol=seagreen]
> Bill,
> I just tested this on w2000, SP4, and was able to start a job. Is it maybe
> that you were using a non-sysadmin login, so didn't have access to the
> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
> then from sysjobs to see.
> "Bill Wang" wrote:
|||Sorry, if you create a new job after SP4, it's OK. But you can't run any jobs
by sp_start_job on old jobs created on SP3.
Bill
"Mary" wrote:
[vbcol=seagreen]
> Bill,
> I just tested this on w2000, SP4, and was able to start a job. Is it maybe
> that you were using a non-sysadmin login, so didn't have access to the
> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
> then from sysjobs to see.
> "Bill Wang" wrote:
|||I tried this using Dev Edition on XP and didn't see this behaviour. After
upgrading from SP3 to SP4 calls to sp_start_job either from another job or
Query Analyzer ran fine (all jobs owned by sa). What collation is your
sevrer and are your jobs owned by sa or another user? Are you calling
sp_start_job when logged in as a sysadmin?
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bill Wang" <BillWang@.discussions.microsoft.com> wrote in message
news:13316834-4E2C-4AF2-B838-1CD16C106C7B@.microsoft.com...[vbcol=seagreen]
> Sorry, if you create a new job after SP4, it's OK. But you can't run any
> jobs
> by sp_start_job on old jobs created on SP3.
> Bill
> "Mary" wrote:

Bug in SP4

Hi,
I met a bug in SP4. I did the same test on two machines. One is a windows
2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2000
develope SP3. Both machines can't run any jobs by sp_start_job stored
procedure after installation of SP4. Same to sp_stop_job. The error is:
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 61
The specified @.job_name ('test1') does not exist.
Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
in SP4.
BillBill,
I just tested this on w2000, SP4, and was able to start a job. Is it maybe
that you were using a non-sysadmin login, so didn't have access to the
msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
then from sysjobs to see.
"Bill Wang" wrote:
> Hi,
> I met a bug in SP4. I did the same test on two machines. One is a windows
> 2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2000
> develope SP3. Both machines can't run any jobs by sp_start_job stored
> procedure after installation of SP4. Same to sp_stop_job. The error is:
> Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> Line 61
> The specified @.job_name ('test1') does not exist.
> Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
> in SP4.
> Bill|||Thanks, Mary.
I tested the select from sysjobs and sysjobs_view. No permission issues on
these two table/view. Itested SP4 with sa. Before installation of SP4, I can
run these stored procedure, then install SP4 and run it with same user. It
failed with the error.
Did you install other hotfix after SP4 ?
Thanks!
Bill
"Mary" wrote:
> Bill,
> I just tested this on w2000, SP4, and was able to start a job. Is it maybe
> that you were using a non-sysadmin login, so didn't have access to the
> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
> then from sysjobs to see.
> "Bill Wang" wrote:
> > Hi,
> > I met a bug in SP4. I did the same test on two machines. One is a windows
> > 2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2000
> > develope SP3. Both machines can't run any jobs by sp_start_job stored
> > procedure after installation of SP4. Same to sp_stop_job. The error is:
> >
> > Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> > Line 61
> > The specified @.job_name ('test1') does not exist.
> >
> > Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
> > in SP4.
> >
> > Bill|||Sorry, if you create a new job after SP4, it's OK. But you can't run any jobs
by sp_start_job on old jobs created on SP3.
Bill
"Mary" wrote:
> Bill,
> I just tested this on w2000, SP4, and was able to start a job. Is it maybe
> that you were using a non-sysadmin login, so didn't have access to the
> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
> then from sysjobs to see.
> "Bill Wang" wrote:
> > Hi,
> > I met a bug in SP4. I did the same test on two machines. One is a windows
> > 2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2000
> > develope SP3. Both machines can't run any jobs by sp_start_job stored
> > procedure after installation of SP4. Same to sp_stop_job. The error is:
> >
> > Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> > Line 61
> > The specified @.job_name ('test1') does not exist.
> >
> > Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
> > in SP4.
> >
> > Bill|||I tried this using Dev Edition on XP and didn't see this behaviour. After
upgrading from SP3 to SP4 calls to sp_start_job either from another job or
Query Analyzer ran fine (all jobs owned by sa). What collation is your
sevrer and are your jobs owned by sa or another user? Are you calling
sp_start_job when logged in as a sysadmin?
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bill Wang" <BillWang@.discussions.microsoft.com> wrote in message
news:13316834-4E2C-4AF2-B838-1CD16C106C7B@.microsoft.com...
> Sorry, if you create a new job after SP4, it's OK. But you can't run any
> jobs
> by sp_start_job on old jobs created on SP3.
> Bill
> "Mary" wrote:
>> Bill,
>> I just tested this on w2000, SP4, and was able to start a job. Is it
>> maybe
>> that you were using a non-sysadmin login, so didn't have access to the
>> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view
>> and
>> then from sysjobs to see.
>> "Bill Wang" wrote:
>> > Hi,
>> > I met a bug in SP4. I did the same test on two machines. One is a
>> > windows
>> > 2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL
>> > Server 2000
>> > develope SP3. Both machines can't run any jobs by sp_start_job stored
>> > procedure after installation of SP4. Same to sp_stop_job. The error is:
>> >
>> > Server: Msg 14262, Level 16, State 1, Procedure
>> > sp_verify_job_identifiers,
>> > Line 61
>> > The specified @.job_name ('test1') does not exist.
>> >
>> > Both stored procedures run well in SP3, but not in SP4. I am sure it's
>> > a bug
>> > in SP4.
>> >
>> > Bill

Bug in SP4

Hi,
I met a bug in SP4. I did the same test on two machines. One is a windows
2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 200
0
develope SP3. Both machines can't run any jobs by sp_start_job stored
procedure after installation of SP4. Same to sp_stop_job. The error is:
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 61
The specified @.job_name ('test1') does not exist.
Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
in SP4.
BillBill,
I just tested this on w2000, SP4, and was able to start a job. Is it maybe
that you were using a non-sysadmin login, so didn't have access to the
msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
then from sysjobs to see.
"Bill Wang" wrote:

> Hi,
> I met a bug in SP4. I did the same test on two machines. One is a windo
ws
> 2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2
000
> develope SP3. Both machines can't run any jobs by sp_start_job stored
> procedure after installation of SP4. Same to sp_stop_job. The error is:
> Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> Line 61
> The specified @.job_name ('test1') does not exist.
> Both stored procedures run well in SP3, but not in SP4. I am sure it's a b
ug
> in SP4.
> Bill|||Thanks, Mary.
I tested the select from sysjobs and sysjobs_view. No permission issues on
these two table/view. Itested SP4 with sa. Before installation of SP4, I can
run these stored procedure, then install SP4 and run it with same user. It
failed with the error.
Did you install other hotfix after SP4 ?
Thanks!
Bill
"Mary" wrote:
[vbcol=seagreen]
> Bill,
> I just tested this on w2000, SP4, and was able to start a job. Is it mayb
e
> that you were using a non-sysadmin login, so didn't have access to the
> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view an
d
> then from sysjobs to see.
> "Bill Wang" wrote:
>|||Sorry, if you create a new job after SP4, it's OK. But you can't run any job
s
by sp_start_job on old jobs created on SP3.
Bill
"Mary" wrote:
[vbcol=seagreen]
> Bill,
> I just tested this on w2000, SP4, and was able to start a job. Is it mayb
e
> that you were using a non-sysadmin login, so didn't have access to the
> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view an
d
> then from sysjobs to see.
> "Bill Wang" wrote:
>|||I tried this using Dev Edition on XP and didn't see this behaviour. After
upgrading from SP3 to SP4 calls to sp_start_job either from another job or
Query Analyzer ran fine (all jobs owned by sa). What collation is your
sevrer and are your jobs owned by sa or another user? Are you calling
sp_start_job when logged in as a sysadmin?
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bill Wang" <BillWang@.discussions.microsoft.com> wrote in message
news:13316834-4E2C-4AF2-B838-1CD16C106C7B@.microsoft.com...[vbcol=seagreen]
> Sorry, if you create a new job after SP4, it's OK. But you can't run any
> jobs
> by sp_start_job on old jobs created on SP3.
> Bill
> "Mary" wrote:
>

Bug in indexed view?

We use SQL server 2000 sp3 database server in Windows 2000 Advanced server O
S.
In production environment, one of the fact tables in our datamart has over
150 mil rows. A indexed view is built upon this fact table for reporting
purposes.
The problem is that when queried for the same criteria, the indexed view
returns different result than the underlying table. This problem has occured
twice in a single month. The problem was resolved upon rebuilding the indexe
d
view.
FYI. We have lots of data inserts as part of overnight batch process.
Queries:
--
SELECT sum(value)
FROM <<FactTableName>> (NOLOCK)
WHERE portfolioid = 100
and cob = '30 Jan 2006'
OPTION (EXPAND VIEWS)
SELECT sum(value)
FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
WHERE portfolioid = 100
and cob = '30 Jan 2006'
Any help on this is appreciated.
--
CheersHi
Does the view based on a single table?
"Space_AD" <SpaceAD@.discussions.microsoft.com> wrote in message
news:43DC2E60-C5E6-4B31-8B2F-893453A3C14C@.microsoft.com...
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced server
> OS.
> In production environment, one of the fact tables in our datamart has over
> 150 mil rows. A indexed view is built upon this fact table for reporting
> purposes.
> The problem is that when queried for the same criteria, the indexed view
> returns different result than the underlying table. This problem has
> occured
> twice in a single month. The problem was resolved upon rebuilding the
> indexed
> view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
> --
> Cheers
>|||Yes, the indexed view is based on a single table.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW <<view>> WITH SCHEMABINDING
AS
SELECT <<col1>>
, <<col2>>
, <<col3>>
, <<col4>>
, <<col5>>
, <<col6>>
, SUM(<<numeric col1>> )
, SUM(<<numeric col2>> )
, COUNT_BIG(*)
FROM dbo.<<facttable>> AS pv
WHERE Balance = 1
GROUP
BY <<col1>>
, <<col2>>
, <<col3>>
, <<col4>>
, <<col5>>
, <<col6>>
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENTIF
IER,ANSI_NULLS
ON
SET NUMERIC_ROUNDABORT OFF
GO
if exists (select * from dbo.sysindexes where name = N'<<idxname>>' and
id = object_id(N'[dbo].[<<view>>]'))
DROP INDEX [dbo].[<<view>>].[<<idxname>>]
GO
CREATE UNIQUE CLUSTERED INDEX [<<idxname>>] ON [dbo].[<<view>
>] (
<<col1>>, <<col2>>, <<col3>>,<<col4>>, <<col5>>, <<col6>> )
GO
Thanks,
Arun|||Hi!
Try remove "nolock" hint from your queries.
Micle.
"Space_AD" <SpaceAD@.discussions.microsoft.com> wrote in message
news:43DC2E60-C5E6-4B31-8B2F-893453A3C14C@.microsoft.com...
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced server
> OS.
> In production environment, one of the fact tables in our datamart has over
> 150 mil rows. A indexed view is built upon this fact table for reporting
> purposes.
> The problem is that when queried for the same criteria, the indexed view
> returns different result than the underlying table. This problem has
> occured
> twice in a single month. The problem was resolved upon rebuilding the
> indexed
> view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
> --
> Cheers
>|||I have already tried it. It does not do any dirty reads..
FYI.. The bug has occured again today.
Cheers,
Arun|||CREATE CI on col1 and col2 ONLY
<arun.ns@.gmail.com> wrote in message
news:1138796568.572051.8640@.g49g2000cwa.googlegroups.com...
> Yes, the indexed view is based on a single table.
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW <<view>> WITH SCHEMABINDING
> AS
> SELECT <<col1>>
> , <<col2>>
> , <<col3>>
> , <<col4>>
> , <<col5>>
> , <<col6>>
> , SUM(<<numeric col1>> )
> , SUM(<<numeric col2>> )
> , COUNT_BIG(*)
> FROM dbo.<<facttable>> AS pv
> WHERE Balance = 1
> GROUP
> BY <<col1>>
> , <<col2>>
> , <<col3>>
> , <<col4>>
> , <<col5>>
> , <<col6>>
> GO
>
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENT
IFIER,ANSI_NULLS
> ON
> SET NUMERIC_ROUNDABORT OFF
> GO
> if exists (select * from dbo.sysindexes where name = N'<<idxname>>' and
> id = object_id(N'[dbo].[<<view>>]'))
> DROP INDEX [dbo].[<<view>>].[<<idxname>>]
> GO
> CREATE UNIQUE CLUSTERED INDEX [<<idxname>>] ON [dbo].[<<vie
w>>] (
> <<col1>>, <<col2>>, <<col3>>,<<col4>>, <<col5>>, <<col6>> )
> GO
> Thanks,
> Arun
>|||Space_AD wrote:
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced
> server OS.
> In production environment, one of the fact tables in our datamart has
> over 150 mil rows. A indexed view is built upon this fact table for
> reporting purposes.
> The problem is that when queried for the same criteria, the indexed
> view returns different result than the underlying table. This problem
> has occured twice in a single month. The problem was resolved upon
> rebuilding the indexed view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
And there is no additional criterium that limits records in the view?
Could your problem be caused by NULL values? Just some 0.01 cents...
robert|||The value column is not nullable.
Cheers,
Space_AD
"Robert Klemme" wrote:

> Space_AD wrote:
> And there is no additional criterium that limits records in the view?
> Could your problem be caused by NULL values? Just some 0.01 cents...
> robert
>|||We found that this problem occured after some deadlock in the underlying
tables.
But not sure how dead locks affect the indexed view .
Cheers,
Space_AD
"arun.ns@.gmail.com" wrote:

> Yes, the indexed view is based on a single table.
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW <<view>> WITH SCHEMABINDING
> AS
> SELECT <<col1>>
> , <<col2>>
> , <<col3>>
> , <<col4>>
> , <<col5>>
> , <<col6>>
> , SUM(<<numeric col1>> )
> , SUM(<<numeric col2>> )
> , COUNT_BIG(*)
> FROM dbo.<<facttable>> AS pv
> WHERE Balance = 1
> GROUP
> BY <<col1>>
> , <<col2>>
> , <<col3>>
> , <<col4>>
> , <<col5>>
> , <<col6>>
> GO
>
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENT
IFIER,ANSI_NULLS
> ON
> SET NUMERIC_ROUNDABORT OFF
> GO
> if exists (select * from dbo.sysindexes where name = N'<<idxname>>' and
> id = object_id(N'[dbo].[<<view>>]'))
> DROP INDEX [dbo].[<<view>>].[<<idxname>>]
> GO
> CREATE UNIQUE CLUSTERED INDEX [<<idxname>>] ON [dbo].[<<vie
w>>] (
> <<col1>>, <<col2>>, <<col3>>,<<col4>>, <<col5>>, <<col6>> )
> GO
> Thanks,
> Arun
>|||we have found that this problem occured just after a deadlocking scenario
involving the underlying table.
But not sure why this deadlock is causing incorrect data in indexed view.
Cheers,
Space_AD
"Space_AD" wrote:
[vbcol=seagreen]
> The value column is not nullable.
> --
> Cheers,
> Space_AD
>
> "Robert Klemme" wrote:
>

Bug in indexed view?

We use SQL server 2000 sp3 database server in Windows 2000 Advanced server OS.
In production environment, one of the fact tables in our datamart has over
150 mil rows. A indexed view is built upon this fact table for reporting
purposes.
The problem is that when queried for the same criteria, the indexed view
returns different result than the underlying table. This problem has occured
twice in a single month. The problem was resolved upon rebuilding the indexed
view.
FYI. We have lots of data inserts as part of overnight batch process.
Queries:
SELECT sum(value)
FROM <<FactTableName>> (NOLOCK)
WHERE portfolioid = 100
and cob = '30 Jan 2006'
OPTION (EXPAND VIEWS)
SELECT sum(value)
FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
WHERE portfolioid = 100
and cob = '30 Jan 2006'
Any help on this is appreciated.
Cheers
Hi
Does the view based on a single table?
"Space_AD" <SpaceAD@.discussions.microsoft.com> wrote in message
news:43DC2E60-C5E6-4B31-8B2F-893453A3C14C@.microsoft.com...
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced server
> OS.
> In production environment, one of the fact tables in our datamart has over
> 150 mil rows. A indexed view is built upon this fact table for reporting
> purposes.
> The problem is that when queried for the same criteria, the indexed view
> returns different result than the underlying table. This problem has
> occured
> twice in a single month. The problem was resolved upon rebuilding the
> indexed
> view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
> --
> Cheers
>
|||Yes, the indexed view is based on a single table.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW <<view>> WITH SCHEMABINDING
AS
SELECT<<col1>>
,<<col2>>
,<<col3>>
,<<col4>>
,<<col5>>
,<<col6>>
,SUM(<<numeric col1>>)
,SUM(<<numeric col2>>)
,COUNT_BIG(*)
FROM dbo.<<facttable>>AS pv
WHEREBalance = 1
GROUP
BY <<col1>>
,<<col2>>
,<<col3>>
,<<col4>>
,<<col5>>
,<<col6>>
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
ON
SET NUMERIC_ROUNDABORT OFF
GO
if exists (select * from dbo.sysindexes where name = N'<<idxname>>' and
id = object_id(N'[dbo].[<<view>>]'))
DROP INDEX [dbo].[<<view>>].[<<idxname>>]
GO
CREATE UNIQUE CLUSTERED INDEX [<<idxname>>] ON [dbo].[<<view>>] (
<<col1>>, <<col2>>, <<col3>>,<<col4>>, <<col5>>, <<col6>>)
GO
Thanks,
Arun
|||Hi!
Try remove "nolock" hint from your queries.
Micle.
"Space_AD" <SpaceAD@.discussions.microsoft.com> wrote in message
news:43DC2E60-C5E6-4B31-8B2F-893453A3C14C@.microsoft.com...
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced server
> OS.
> In production environment, one of the fact tables in our datamart has over
> 150 mil rows. A indexed view is built upon this fact table for reporting
> purposes.
> The problem is that when queried for the same criteria, the indexed view
> returns different result than the underlying table. This problem has
> occured
> twice in a single month. The problem was resolved upon rebuilding the
> indexed
> view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
> --
> Cheers
>
|||I have already tried it. It does not do any dirty reads..
FYI.. The bug has occured again today.
Cheers,
Arun
|||CREATE CI on col1 and col2 ONLY
<arun.ns@.gmail.com> wrote in message
news:1138796568.572051.8640@.g49g2000cwa.googlegrou ps.com...
> Yes, the indexed view is based on a single table.
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW <<view>> WITH SCHEMABINDING
> AS
> SELECT <<col1>>
> , <<col2>>
> , <<col3>>
> , <<col4>>
> , <<col5>>
> , <<col6>>
> , SUM(<<numeric col1>>)
> , SUM(<<numeric col2>>)
> , COUNT_BIG(*)
> FROM dbo.<<facttable>> AS pv
> WHERE Balance = 1
> GROUP
> BY <<col1>>
> , <<col2>>
> , <<col3>>
> , <<col4>>
> , <<col5>>
> , <<col6>>
> GO
>
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
> ON
> SET NUMERIC_ROUNDABORT OFF
> GO
> if exists (select * from dbo.sysindexes where name = N'<<idxname>>' and
> id = object_id(N'[dbo].[<<view>>]'))
> DROP INDEX [dbo].[<<view>>].[<<idxname>>]
> GO
> CREATE UNIQUE CLUSTERED INDEX [<<idxname>>] ON [dbo].[<<view>>] (
> <<col1>>, <<col2>>, <<col3>>,<<col4>>, <<col5>>, <<col6>>)
> GO
> Thanks,
> Arun
>
|||Space_AD wrote:
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced
> server OS.
> In production environment, one of the fact tables in our datamart has
> over 150 mil rows. A indexed view is built upon this fact table for
> reporting purposes.
> The problem is that when queried for the same criteria, the indexed
> view returns different result than the underlying table. This problem
> has occured twice in a single month. The problem was resolved upon
> rebuilding the indexed view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
And there is no additional criterium that limits records in the view?
Could your problem be caused by NULL values? Just some 0.01 cents...
robert
|||The value column is not nullable.
Cheers,
Space_AD
"Robert Klemme" wrote:

> Space_AD wrote:
> And there is no additional criterium that limits records in the view?
> Could your problem be caused by NULL values? Just some 0.01 cents...
> robert
>
|||We found that this problem occured after some deadlock in the underlying
tables.
But not sure how dead locks affect the indexed view .
Cheers,
Space_AD
"arun.ns@.gmail.com" wrote:

> Yes, the indexed view is based on a single table.
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW <<view>> WITH SCHEMABINDING
> AS
> SELECT<<col1>>
> ,<<col2>>
> ,<<col3>>
> ,<<col4>>
> ,<<col5>>
> ,<<col6>>
> ,SUM(<<numeric col1>>)
> ,SUM(<<numeric col2>>)
> ,COUNT_BIG(*)
> FROM dbo.<<facttable>>AS pv
> WHEREBalance = 1
> GROUP
> BY <<col1>>
> ,<<col2>>
> ,<<col3>>
> ,<<col4>>
> ,<<col5>>
> ,<<col6>>
> GO
>
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
> ON
> SET NUMERIC_ROUNDABORT OFF
> GO
> if exists (select * from dbo.sysindexes where name = N'<<idxname>>' and
> id = object_id(N'[dbo].[<<view>>]'))
> DROP INDEX [dbo].[<<view>>].[<<idxname>>]
> GO
> CREATE UNIQUE CLUSTERED INDEX [<<idxname>>] ON [dbo].[<<view>>] (
> <<col1>>, <<col2>>, <<col3>>,<<col4>>, <<col5>>, <<col6>>)
> GO
> Thanks,
> Arun
>
|||we have found that this problem occured just after a deadlocking scenario
involving the underlying table.
But not sure why this deadlock is causing incorrect data in indexed view.
Cheers,
Space_AD
"Space_AD" wrote:
[vbcol=seagreen]
> The value column is not nullable.
> --
> Cheers,
> Space_AD
>
> "Robert Klemme" wrote:

Bug in indexed view?

We use SQL server 2000 sp3 database server in Windows 2000 Advanced server OS.
In production environment, one of the fact tables in our datamart has over
150 mil rows. A indexed view is built upon this fact table for reporting
purposes.
The problem is that when queried for the same criteria, the indexed view
returns different result than the underlying table. This problem has occured
twice in a single month. The problem was resolved upon rebuilding the indexed
view.
FYI. We have lots of data inserts as part of overnight batch process.
Queries:
--
SELECT sum(value)
FROM <<FactTableName>> (NOLOCK)
WHERE portfolioid = 100
and cob = '30 Jan 2006'
OPTION (EXPAND VIEWS)
SELECT sum(value)
FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
WHERE portfolioid = 100
and cob = '30 Jan 2006'
Any help on this is appreciated.
--
CheersHi
Does the view based on a single table?
"Space_AD" <SpaceAD@.discussions.microsoft.com> wrote in message
news:43DC2E60-C5E6-4B31-8B2F-893453A3C14C@.microsoft.com...
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced server
> OS.
> In production environment, one of the fact tables in our datamart has over
> 150 mil rows. A indexed view is built upon this fact table for reporting
> purposes.
> The problem is that when queried for the same criteria, the indexed view
> returns different result than the underlying table. This problem has
> occured
> twice in a single month. The problem was resolved upon rebuilding the
> indexed
> view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
> --
> Cheers
>|||Yes, the indexed view is based on a single table.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW <<view>> WITH SCHEMABINDING
AS
SELECT <<col1>>
, <<col2>>
, <<col3>>
, <<col4>>
, <<col5>>
, <<col6>>
, SUM(<<numeric col1>>)
, SUM(<<numeric col2>>)
, COUNT_BIG(*)
FROM dbo.<<facttable>> AS pv
WHERE Balance = 1
GROUP
BY <<col1>>
, <<col2>>
, <<col3>>
, <<col4>>
, <<col5>>
, <<col6>>
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
ON
SET NUMERIC_ROUNDABORT OFF
GO
if exists (select * from dbo.sysindexes where name = N'<<idxname>>' and
id = object_id(N'[dbo].[<<view>>]'))
DROP INDEX [dbo].[<<view>>].[<<idxname>>]
GO
CREATE UNIQUE CLUSTERED INDEX [<<idxname>>] ON [dbo].[<<view>>] (
<<col1>>, <<col2>>, <<col3>>,<<col4>>, <<col5>>, <<col6>>)
GO
Thanks,
Arun|||Hi!
Try remove "nolock" hint from your queries.
Micle.
"Space_AD" <SpaceAD@.discussions.microsoft.com> wrote in message
news:43DC2E60-C5E6-4B31-8B2F-893453A3C14C@.microsoft.com...
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced server
> OS.
> In production environment, one of the fact tables in our datamart has over
> 150 mil rows. A indexed view is built upon this fact table for reporting
> purposes.
> The problem is that when queried for the same criteria, the indexed view
> returns different result than the underlying table. This problem has
> occured
> twice in a single month. The problem was resolved upon rebuilding the
> indexed
> view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
> --
> Cheers
>|||I have already tried it. It does not do any dirty reads..
FYI.. The bug has occured again today.
Cheers,
Arun|||CREATE CI on col1 and col2 ONLY
<arun.ns@.gmail.com> wrote in message
news:1138796568.572051.8640@.g49g2000cwa.googlegroups.com...
> Yes, the indexed view is based on a single table.
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW <<view>> WITH SCHEMABINDING
> AS
> SELECT <<col1>>
> , <<col2>>
> , <<col3>>
> , <<col4>>
> , <<col5>>
> , <<col6>>
> , SUM(<<numeric col1>>)
> , SUM(<<numeric col2>>)
> , COUNT_BIG(*)
> FROM dbo.<<facttable>> AS pv
> WHERE Balance = 1
> GROUP
> BY <<col1>>
> , <<col2>>
> , <<col3>>
> , <<col4>>
> , <<col5>>
> , <<col6>>
> GO
>
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
> ON
> SET NUMERIC_ROUNDABORT OFF
> GO
> if exists (select * from dbo.sysindexes where name = N'<<idxname>>' and
> id = object_id(N'[dbo].[<<view>>]'))
> DROP INDEX [dbo].[<<view>>].[<<idxname>>]
> GO
> CREATE UNIQUE CLUSTERED INDEX [<<idxname>>] ON [dbo].[<<view>>] (
> <<col1>>, <<col2>>, <<col3>>,<<col4>>, <<col5>>, <<col6>>)
> GO
> Thanks,
> Arun
>|||Space_AD wrote:
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced
> server OS.
> In production environment, one of the fact tables in our datamart has
> over 150 mil rows. A indexed view is built upon this fact table for
> reporting purposes.
> The problem is that when queried for the same criteria, the indexed
> view returns different result than the underlying table. This problem
> has occured twice in a single month. The problem was resolved upon
> rebuilding the indexed view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
And there is no additional criterium that limits records in the view?
Could your problem be caused by NULL values? Just some 0.01 cents...
robert|||The value column is not nullable.
--
Cheers,
Space_AD
"Robert Klemme" wrote:
> Space_AD wrote:
> > We use SQL server 2000 sp3 database server in Windows 2000 Advanced
> > server OS.
> >
> > In production environment, one of the fact tables in our datamart has
> > over 150 mil rows. A indexed view is built upon this fact table for
> > reporting purposes.
> >
> > The problem is that when queried for the same criteria, the indexed
> > view returns different result than the underlying table. This problem
> > has occured twice in a single month. The problem was resolved upon
> > rebuilding the indexed view.
> >
> > FYI. We have lots of data inserts as part of overnight batch process.
> >
> > Queries:
> > --
> > SELECT sum(value)
> > FROM <<FactTableName>> (NOLOCK)
> > WHERE portfolioid = 100
> > and cob = '30 Jan 2006'
> > OPTION (EXPAND VIEWS)
> >
> > SELECT sum(value)
> > FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> > WHERE portfolioid = 100
> > and cob = '30 Jan 2006'
> >
> > Any help on this is appreciated.
> And there is no additional criterium that limits records in the view?
> Could your problem be caused by NULL values? Just some 0.01 cents...
> robert
>|||we have found that this problem occured just after a deadlocking scenario
involving the underlying table.
But not sure why this deadlock is causing incorrect data in indexed view.
--
Cheers,
Space_AD
"Space_AD" wrote:
> The value column is not nullable.
> --
> Cheers,
> Space_AD
>
> "Robert Klemme" wrote:
> > Space_AD wrote:
> > > We use SQL server 2000 sp3 database server in Windows 2000 Advanced
> > > server OS.
> > >
> > > In production environment, one of the fact tables in our datamart has
> > > over 150 mil rows. A indexed view is built upon this fact table for
> > > reporting purposes.
> > >
> > > The problem is that when queried for the same criteria, the indexed
> > > view returns different result than the underlying table. This problem
> > > has occured twice in a single month. The problem was resolved upon
> > > rebuilding the indexed view.
> > >
> > > FYI. We have lots of data inserts as part of overnight batch process.
> > >
> > > Queries:
> > > --
> > > SELECT sum(value)
> > > FROM <<FactTableName>> (NOLOCK)
> > > WHERE portfolioid = 100
> > > and cob = '30 Jan 2006'
> > > OPTION (EXPAND VIEWS)
> > >
> > > SELECT sum(value)
> > > FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> > > WHERE portfolioid = 100
> > > and cob = '30 Jan 2006'
> > >
> > > Any help on this is appreciated.
> >
> > And there is no additional criterium that limits records in the view?
> > Could your problem be caused by NULL values? Just some 0.01 cents...
> >
> > robert
> >
> >

BUG - SQL Agent Jobs - SP3 - Multiple Steps

I have come across what seems to be a bug in SQL 2K SP3.
When I have a job with multiple job steps and I right-click to choose which step I want to start with, the job steps are in no particular order.
I am also using an ActiveX script that uses SQLDMO to script out the jobs to a text file. When the job is scripted, it puts the job steps in what seems like alphabetical order by step name and not in step number order. If you try to run this script to c
reate the job, it will fail because it requires the job steps to be in step number order.
I have talked to others that have used this script prior to SP3 so it seems to only have occurred in this Service Pack.
I just wanted to get this information out there so it can be addressed and corrected in either a patch or at least the next SP.
This is indeed a known bug for SQL2000. Its because sp_help_jobstep doesn't
contain an order by when returning results IIRC. Generally you get the right
result but without the order by its not guaranteed
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"hawg" <anonymous@.discussions.microsoft.com> wrote in message
news:6115B24E-5496-49CB-ABCC-864C14E56DC1@.microsoft.com...
> I have come across what seems to be a bug in SQL 2K SP3.
> When I have a job with multiple job steps and I right-click to choose
which step I want to start with, the job steps are in no particular order.
> I am also using an ActiveX script that uses SQLDMO to script out the jobs
to a text file. When the job is scripted, it puts the job steps in what
seems like alphabetical order by step name and not in step number order. If
you try to run this script to create the job, it will fail because it
requires the job steps to be in step number order.
> I have talked to others that have used this script prior to SP3 so it
seems to only have occurred in this Service Pack.
> I just wanted to get this information out there so it can be addressed and
corrected in either a patch or at least the next SP.

BUG - SQL Agent Jobs - SP3 - Multiple Steps

I have come across what seems to be a bug in SQL 2K SP3.
When I have a job with multiple job steps and I right-click to choose which
step I want to start with, the job steps are in no particular order.
I am also using an ActiveX script that uses SQLDMO to script out the jobs to
a text file. When the job is scripted, it puts the job steps in what seems
like alphabetical order by step name and not in step number order. If you
try to run this script to c
reate the job, it will fail because it requires the job steps to be in step
number order.
I have talked to others that have used this script prior to SP3 so it seems
to only have occurred in this Service Pack.
I just wanted to get this information out there so it can be addressed and c
orrected in either a patch or at least the next SP.This is indeed a known bug for SQL2000. Its because sp_help_jobstep doesn't
contain an order by when returning results IIRC. Generally you get the right
result but without the order by its not guaranteed
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"hawg" <anonymous@.discussions.microsoft.com> wrote in message
news:6115B24E-5496-49CB-ABCC-864C14E56DC1@.microsoft.com...
> I have come across what seems to be a bug in SQL 2K SP3.
> When I have a job with multiple job steps and I right-click to choose
which step I want to start with, the job steps are in no particular order.
> I am also using an ActiveX script that uses SQLDMO to script out the jobs
to a text file. When the job is scripted, it puts the job steps in what
seems like alphabetical order by step name and not in step number order. If
you try to run this script to create the job, it will fail because it
requires the job steps to be in step number order.
> I have talked to others that have used this script prior to SP3 so it
seems to only have occurred in this Service Pack.
> I just wanted to get this information out there so it can be addressed and
corrected in either a patch or at least the next SP.

Buffer overrun detected

When I run SQL SP4 in a SQL Server (SQL SP3) running on a Windows 2000
Cluster (Active/Active), I receive the next message:
Buffer Overrun detected!
A buffer overrun has been detected which has corrupted the programs internal
state. The program cannot safely continue execution and must now be
terminated.
In a cluster I have two instances. The first instance installed perfect but
in second instance have a problem describe above.
Thank youHi, we've just come across this too. Has anyone got a solution?
BruceB
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1757341.html|||Hi
You may want to call PSS on this one.
John
"Mario Felix Filho" <Mario Felix Filho@.discussions.microsoft.com> wrote in
message news:DA97BB4C-309E-4CDF-A1F2-A2B4851D264F@.microsoft.com...
> When I run SQL SP4 in a SQL Server (SQL SP3) running on a Windows 2000
> Cluster (Active/Active), I receive the next message:
> Buffer Overrun detected!
> A buffer overrun has been detected which has corrupted the programs
> internal
> state. The program cannot safely continue execution and must now be
> terminated.
> In a cluster I have two instances. The first instance installed perfect
> but
> in second instance have a problem describe above.
> Thank you
>

Buffer overrun detected

When I run SQL SP4 in a SQL Server (SQL SP3) running on a Windows 2000
Cluster (Active/Active), I receive the next message:
Buffer Overrun detected!
A buffer overrun has been detected which has corrupted the programs internal
state. The program cannot safely continue execution and must now be
terminated.
In a cluster I have two instances. The first instance installed perfect but
in second instance have a problem describe above.
Thank you
Hi, we've just come across this too. Has anyone got a solution?|||Hi
You may want to call PSS on this one.
John
"Mario Felix Filho" <Mario Felix Filho@.discussions.microsoft.com> wrote in
message news:DA97BB4C-309E-4CDF-A1F2-A2B4851D264F@.microsoft.com...
> When I run SQL SP4 in a SQL Server (SQL SP3) running on a Windows 2000
> Cluster (Active/Active), I receive the next message:
> Buffer Overrun detected!
> A buffer overrun has been detected which has corrupted the programs
> internal
> state. The program cannot safely continue execution and must now be
> terminated.
> In a cluster I have two instances. The first instance installed perfect
> but
> in second instance have a problem describe above.
> Thank you
>

Buffer overrun detected

When I run SQL SP4 in a SQL Server (SQL SP3) running on a Windows 2000
Cluster (Active/Active), I receive the next message:
Buffer Overrun detected!
A buffer overrun has been detected which has corrupted the programs internal
state. The program cannot safely continue execution and must now be
terminated.
In a cluster I have two instances. The first instance installed perfect but
in second instance have a problem describe above.
Thank youHi
You may want to call PSS on this one.
John
"Mario Felix Filho" <Mario Felix Filho@.discussions.microsoft.com> wrote in
message news:DA97BB4C-309E-4CDF-A1F2-A2B4851D264F@.microsoft.com...
> When I run SQL SP4 in a SQL Server (SQL SP3) running on a Windows 2000
> Cluster (Active/Active), I receive the next message:
> Buffer Overrun detected!
> A buffer overrun has been detected which has corrupted the programs
> internal
> state. The program cannot safely continue execution and must now be
> terminated.
> In a cluster I have two instances. The first instance installed perfect
> but
> in second instance have a problem describe above.
> Thank you
>

BPA Installation Error

I am trying to install BPA on a W2K workstation that has SQL 2000 Personal
Edition SP3. Just befor completing the installation, I get a message box
that says
"One or more rules could not be installed. There is an error in XML document
(55, 15). Invalid SQL Server edition"
I tried using the local server for repository and also tried a SQL 2K
Enterprise Edition, SP 3 running on a W2K Advanced Server. I am getting the
same error.
What am I missing ?
Thanks for any help or any pointers I can get.
Sathish V
Hey Sathish, when you get those error there should be a box below that that
allow you to check in and out of that criteria.
"Sathish V" wrote:

> I am trying to install BPA on a W2K workstation that has SQL 2000 Personal
> Edition SP3. Just befor completing the installation, I get a message box
> that says
> "One or more rules could not be installed. There is an error in XML document
> (55, 15). Invalid SQL Server edition"
> I tried using the local server for repository and also tried a SQL 2K
> Enterprise Edition, SP 3 running on a W2K Advanced Server. I am getting the
> same error.
> What am I missing ?
> Thanks for any help or any pointers I can get.
> Sathish V
|||Thanks Garry. I will check it out let you know.
"Garry D" wrote:
[vbcol=seagreen]
> Hey Sathish, when you get those error there should be a box below that that
> allow you to check in and out of that criteria.
> "Sathish V" wrote:
|||I was having the same problem. I solved it by:
Removing BPA in Control Panel/Add Remove Programs
Removing the BPA database from my SQL Server
Removing the BPA directory in c:\Program Files
After doing all these, it installed fine. Now to try it...
"Sathish V" wrote:

> I am trying to install BPA on a W2K workstation that has SQL 2000 Personal
> Edition SP3. Just befor completing the installation, I get a message box
> that says
> "One or more rules could not be installed. There is an error in XML document
> (55, 15). Invalid SQL Server edition"
> I tried using the local server for repository and also tried a SQL 2K
> Enterprise Edition, SP 3 running on a W2K Advanced Server. I am getting the
> same error.
> What am I missing ?
> Thanks for any help or any pointers I can get.
> Sathish V
|||Sathish, Mike
Let us know if this issue has been resolved
Thanks
Sethu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Mike W." <Mike W.@.discussions.microsoft.com> wrote in message
news:0669F4C0-04FA-4201-9E47-1432B89DBF54@.microsoft.com...[vbcol=seagreen]
>I was having the same problem. I solved it by:
> Removing BPA in Control Panel/Add Remove Programs
> Removing the BPA database from my SQL Server
> Removing the BPA directory in c:\Program Files
> After doing all these, it installed fine. Now to try it...
> "Sathish V" wrote:

Both versions of SQL Server on one server

Can I install SQL Server 7.0 SP2 as a Default version and
SQL Server 2000 SP3 as a named version on a WIndows 2000
SP3 server ?
We will run Connect-Care (Customer-First) Application
against SQL Server 7.0 version and Solomon 5.5 against SQL
Server 2000 version.
Anybody see any problem with this ?
Thanks for any help.
Rick,
Yep, should work fine. I have found it preferable to install SQL Server
7.0 first, then install SQL 2000 as a named instance. Remember to
configure your memory settings so that they aren't competing with each
other for RAM.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rick wrote:
> Can I install SQL Server 7.0 SP2 as a Default version and
> SQL Server 2000 SP3 as a named version on a WIndows 2000
> SP3 server ?
> We will run Connect-Care (Customer-First) Application
> against SQL Server 7.0 version and Solomon 5.5 against SQL
> Server 2000 version.
> Anybody see any problem with this ?
> Thanks for any help.
|||I hope that installing service packs should not be a
problem....

>--Original Message--
>Rick,
>Yep, should work fine. I have found it preferable to
install SQL Server
>7.0 first, then install SQL 2000 as a named instance.
Remember to
>configure your memory settings so that they aren't
competing with each[vbcol=seagreen]
>other for RAM.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602m.html
>
>Rick wrote:
and[vbcol=seagreen]
2000[vbcol=seagreen]
SQL
>.
>
|||Rick,
No problem! You can even have two instances of SQL Server 2000 running
at different service pack levels. (Not that you'd want to though).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rick wrote:[vbcol=seagreen]
> I hope that installing service packs should not be a
> problem....
>
>
> install SQL Server
>
> Remember to
>
> competing with each
>
> and
>
> 2000
>
> SQL

Both versions of SQL Server on one server

Can I install SQL Server 7.0 SP2 as a Default version and
SQL Server 2000 SP3 as a named version on a WIndows 2000
SP3 server '
We will run Connect-Care (Customer-First) Application
against SQL Server 7.0 version and Solomon 5.5 against SQL
Server 2000 version.
Anybody see any problem with this '
Thanks for any help.Check out
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/instsql/in_runsetup_0svo.asp
Peter
"Government does not solve problems; it subsidizes them."
Ronald Reagan
>--Original Message--
>Can I install SQL Server 7.0 SP2 as a Default version and
>SQL Server 2000 SP3 as a named version on a WIndows 2000
>SP3 server '
>We will run Connect-Care (Customer-First) Application
>against SQL Server 7.0 version and Solomon 5.5 against
SQL
>Server 2000 version.
>Anybody see any problem with this '
>Thanks for any help.
>.
>|||Rick,
Yep, should work fine. I have found it preferable to install SQL Server
7.0 first, then install SQL 2000 as a named instance. Remember to
configure your memory settings so that they aren't competing with each
other for RAM.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rick wrote:
> Can I install SQL Server 7.0 SP2 as a Default version and
> SQL Server 2000 SP3 as a named version on a WIndows 2000
> SP3 server '
> We will run Connect-Care (Customer-First) Application
> against SQL Server 7.0 version and Solomon 5.5 against SQL
> Server 2000 version.
> Anybody see any problem with this '
> Thanks for any help.|||I hope that installing service packs should not be a
problem....
>--Original Message--
>Rick,
>Yep, should work fine. I have found it preferable to
install SQL Server
>7.0 first, then install SQL 2000 as a named instance.
Remember to
>configure your memory settings so that they aren't
competing with each
>other for RAM.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602m.html
>
>Rick wrote:
>> Can I install SQL Server 7.0 SP2 as a Default version
and
>> SQL Server 2000 SP3 as a named version on a WIndows
2000
>> SP3 server '
>> We will run Connect-Care (Customer-First) Application
>> against SQL Server 7.0 version and Solomon 5.5 against
SQL
>> Server 2000 version.
>> Anybody see any problem with this '
>> Thanks for any help.
>.
>|||Rick,
No problem! You can even have two instances of SQL Server 2000 running
at different service pack levels. (Not that you'd want to though).
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rick wrote:
> I hope that installing service packs should not be a
> problem....
>
>>--Original Message--
>>Rick,
>>Yep, should work fine. I have found it preferable to
> install SQL Server
>>7.0 first, then install SQL 2000 as a named instance.
> Remember to
>>configure your memory settings so that they aren't
> competing with each
>>other for RAM.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602m.html
>>
>>Rick wrote:
>>Can I install SQL Server 7.0 SP2 as a Default version
> and
>>SQL Server 2000 SP3 as a named version on a WIndows
> 2000
>>SP3 server '
>>We will run Connect-Care (Customer-First) Application
>>against SQL Server 7.0 version and Solomon 5.5 against
> SQL
>>Server 2000 version.
>>Anybody see any problem with this '
>>Thanks for any help.
>>.