Error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options

Hi,
SQLServer 2000, using an OPENDATASOURCE command within a stored procedure to
access data on another Server running 2000. I get the following error, whe
n I exececute the Stored Procedure in Query Analyzer:
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
set for the connection. This ensures consistent query semantics. Enable the
se options and then reissue your query.
Interestingly, when I issue the identicl select statement within Query Analy
zer, it works fine.
I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
THanks for any help.
JimReview the information in BOL regarding "set ansi_nulls" - pay special
attention to the information about stored procedures. Then review the notes
for "create procedure" - it reiterates the previous point and adds some
additional information. Then - go fix your procedure. Note that using EM
to do this makes the process that much more difficult, since it tends to
hide important details (like this). Instead, use QA and a script to create
the procedure. Of course, you should be using scripts of some sort since
all code for the database (schema, stored procedures, UDFs, triggers, etc)
are as important to the entire system as your application code.
BTW - you want to set ansi_nulls ON, not OFF. Generally, something that is
"set" means it is set "on".|||Generally the error is due to needing to set the properties
when you create the stored procedure. Try recreating your
stored procedure using:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Thu, 21 Sep 2006 14:58:30 -0700, "Jim Fox"
<jim.fox@.emailhdi.com> wrote:

>Hi,
>SQLServer 2000, using an OPENDATASOURCE command within a stored procedure t
o access data on another Server running 2000. I get the following error, wh
en I exececute the Stored Procedure in Query Analyzer:
>Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to b
e set for the connection. This ensures consistent query semantics. Enable th
ese options and then reissue your query.
>Interestingly, when I issue the identicl select statement within Query Anal
yzer, it works fine.
>I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
>THanks for any help.
>Jim|||Thanks - Much appreciated!
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Review the information in BOL regarding "set ansi_nulls" - pay special
> attention to the information about stored procedures. Then review the
> notes for "create procedure" - it reiterates the previous point and adds
> some additional information. Then - go fix your procedure. Note that
> using EM to do this makes the process that much more difficult, since it
> tends to hide important details (like this). Instead, use QA and a script
> to create the procedure. Of course, you should be using scripts of some
> sort since all code for the database (schema, stored procedures, UDFs,
> triggers, etc) are as important to the entire system as your application
> code.
> BTW - you want to set ansi_nulls ON, not OFF. Generally, something that
> is "set" means it is set "on".
>|||Along these lines, I am calling a trigger that runs an
insert/update/delete on a linked server table, and am running into the
same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
trigger itself does not help out at all...
I've been on the phone with MS all day, and am looking for a fresh
perspective...
My trigger is this:
CREATE TRIGGER opsCompany_Insert ON [dbo].[RM00101]
FOR INSERT
AS
SET ANSI_DEFAULTS ON
-- SET THE DB NAME / DO THIS FOR EACH COMPANY DB --
DECLARE @.CompanyDB CHAR(5)
SELECT @.CompanyDB = (SELECT 'TWO')
-- Set Company Number
DECLARE @.CompanyNumber CHAR(15)
SELECT @.CompanyNumber = (SELECT CUSTNMBR FROM INSERTED)
-- Set Company Name
DECLARE @.CompanyName CHAR(65)
SELECT @.CompanyName = (SELECT CUSTNAME FROM INSERTED)
-- Set Currency ID
DECLARE @.CurrencyID CHAR(15)
SELECT @.CurrencyID = (SELECT CURNCYID FROM INSERTED)
-- Set GovernmentID
DECLARE @.GovernmentID CHAR(25)
SELECT @.GovernmentID = (SELECT TXRGNNUM FROM INSERTED)
-- DEX_ROW_ID
DECLARE @.MstrID INT
SELECT @.MstrID = (SELECT DEX_ROW_ID FROM INSERTED)
-- Push to Cranberry
INSERT INTO SQLSVR.TESTDATA.dbo.Company (GPCompanyID, CompanyNumber,
CompanyName, CurrencyID, GovernmentID, CompanyDB, AddedBy, AddedOn)
VALUES (@.MstrID, @.CompanyNumber, @.CompanyName, @.CurrencyID,
@.GovernmentID, @.CompanyDB, user, getdate())
I can run the trigger fine to a local database, but to the linked
server, I get the same 'Heterogeneous' error... I ahve set it up from
QA with
SET ANSI_NULLS, ANSI_WARNINGS ON
GO
Create Trigger ...
And also setting it within the trigger right after 'AS'
However still no luck...
Any thoughts?
Thanks!
Jim Fox wrote:[vbcol=seagreen]
> Thanks - Much appreciated!
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...|||> Along these lines, I am calling a trigger that runs an
> insert/update/delete on a linked server table, and am running into the
> same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
> trigger itself does not help out at all...
These are connection level settings - for the most part. Stored procedures
have their own wrinkle to this. Ultimately, the issue is the same. You
must use the appropriate connection-level settings for this architecture to
work. Ideally, your client application should be designed to enforce the
appropriate settings. If you can't do that, then the only other option
that I can see is to put your logic for accessing the remote DB into a
procedure. Your procedure must be created with the "sticky" settings that
are needed and can set the others that are needed within the body of the
procedure. I think that approach will work, but I've not investigated all
of the issues to know for certain. Note - your trigger code does not
support mult-row inserts, making the use of a stored procedure much easier
(and as technically flawed the trigger).
Some other alternatives you might want to consider.
* Some form of replication.
* Some form of asynchronous queueing of updates.sql

Error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options

Hi,
SQLServer 2000, using an OPENDATASOURCE command within a stored procedure to access data on another Server running 2000. I get the following error, when I exececute the Stored Procedure in Query Analyzer:
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
Interestingly, when I issue the identicl select statement within Query Analyzer, it works fine.
I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
THanks for any help.
Jim
Review the information in BOL regarding "set ansi_nulls" - pay special
attention to the information about stored procedures. Then review the notes
for "create procedure" - it reiterates the previous point and adds some
additional information. Then - go fix your procedure. Note that using EM
to do this makes the process that much more difficult, since it tends to
hide important details (like this). Instead, use QA and a script to create
the procedure. Of course, you should be using scripts of some sort since
all code for the database (schema, stored procedures, UDFs, triggers, etc)
are as important to the entire system as your application code.
BTW - you want to set ansi_nulls ON, not OFF. Generally, something that is
"set" means it is set "on".
|||Generally the error is due to needing to set the properties
when you create the stored procedure. Try recreating your
stored procedure using:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Thu, 21 Sep 2006 14:58:30 -0700, "Jim Fox"
<jim.fox@.emailhdi.com> wrote:

>Hi,
>SQLServer 2000, using an OPENDATASOURCE command within a stored procedure to access data on another Server running 2000. I get the following error, when I exececute the Stored Procedure in Query Analyzer:
>Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
>Interestingly, when I issue the identicl select statement within Query Analyzer, it works fine.
>I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
>THanks for any help.
>Jim
|||Thanks - Much appreciated!
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Review the information in BOL regarding "set ansi_nulls" - pay special
> attention to the information about stored procedures. Then review the
> notes for "create procedure" - it reiterates the previous point and adds
> some additional information. Then - go fix your procedure. Note that
> using EM to do this makes the process that much more difficult, since it
> tends to hide important details (like this). Instead, use QA and a script
> to create the procedure. Of course, you should be using scripts of some
> sort since all code for the database (schema, stored procedures, UDFs,
> triggers, etc) are as important to the entire system as your application
> code.
> BTW - you want to set ansi_nulls ON, not OFF. Generally, something that
> is "set" means it is set "on".
>
|||Along these lines, I am calling a trigger that runs an
insert/update/delete on a linked server table, and am running into the
same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
trigger itself does not help out at all...
I've been on the phone with MS all day, and am looking for a fresh
perspective...
My trigger is this:
CREATE TRIGGER opsCompany_Insert ON [dbo].[RM00101]
FOR INSERT
AS
SET ANSI_DEFAULTS ON
-- SET THE DB NAME / DO THIS FOR EACH COMPANY DB --
DECLARE @.CompanyDB CHAR(5)
SELECT @.CompanyDB = (SELECT 'TWO')
-- Set Company Number
DECLARE @.CompanyNumber CHAR(15)
SELECT @.CompanyNumber = (SELECT CUSTNMBR FROM INSERTED)
-- Set Company Name
DECLARE @.CompanyName CHAR(65)
SELECT @.CompanyName = (SELECT CUSTNAME FROM INSERTED)
-- Set Currency ID
DECLARE @.CurrencyID CHAR(15)
SELECT @.CurrencyID = (SELECT CURNCYID FROM INSERTED)
-- Set GovernmentID
DECLARE @.GovernmentID CHAR(25)
SELECT @.GovernmentID = (SELECT TXRGNNUM FROM INSERTED)
-- DEX_ROW_ID
DECLARE @.MstrID INT
SELECT @.MstrID = (SELECT DEX_ROW_ID FROM INSERTED)
-- Push to Cranberry
INSERT INTO SQLSVR.TESTDATA.dbo.Company (GPCompanyID, CompanyNumber,
CompanyName, CurrencyID, GovernmentID, CompanyDB, AddedBy, AddedOn)
VALUES (@.MstrID, @.CompanyNumber, @.CompanyName, @.CurrencyID,
@.GovernmentID, @.CompanyDB, user, getdate())
I can run the trigger fine to a local database, but to the linked
server, I get the same 'Heterogeneous' error... I ahve set it up from
QA with
SET ANSI_NULLS, ANSI_WARNINGS ON
GO
Create Trigger ...
And also setting it within the trigger right after 'AS'
However still no luck...
Any thoughts?
Thanks!
Jim Fox wrote:[vbcol=seagreen]
> Thanks - Much appreciated!
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...
|||> Along these lines, I am calling a trigger that runs an
> insert/update/delete on a linked server table, and am running into the
> same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
> trigger itself does not help out at all...
These are connection level settings - for the most part. Stored procedures
have their own wrinkle to this. Ultimately, the issue is the same. You
must use the appropriate connection-level settings for this architecture to
work. Ideally, your client application should be designed to enforce the
appropriate settings. If you can't do that, then the only other option
that I can see is to put your logic for accessing the remote DB into a
procedure. Your procedure must be created with the "sticky" settings that
are needed and can set the others that are needed within the body of the
procedure. I think that approach will work, but I've not investigated all
of the issues to know for certain. Note - your trigger code does not
support mult-row inserts, making the use of a stored procedure much easier
(and as technically flawed the trigger).
Some other alternatives you might want to consider.
* Some form of replication.
* Some form of asynchronous queueing of updates.

Error: fcb::close-flush: Operating system error 21(The device is not ready.) encountered

We are using sql server 2005 Enterprise Edition with service pack1

I got the following error messages in the SQL log

The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000090000 in file '....mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. fcb::close-flush: Operating system error 21(The device is not ready.) encountered.

I got these errors for about 2 hrs and after that I see these messages in the sql log

Starting up database ' ' 1 transactions rolled forward in database '' (). This is an informational message only. No user action is required. 0 transactions rolled back in database ' ' (). This is an informational message only. No user action is required. Recovery is writing a checkpoint in database ' ' ( ). This is an informational message only. No user action is required. CHECKDB for database '' finished without errors on (local time). This is an informational message only; no user action is required.

Can anyone please help me in troubleshooting this issue. Why this migh have happened.

any help would be appreciated.

Thanks

This sounds like an IO subsystem issue, i.e. SQL Server is having difficulty talking to one of your drives. Are you using direct attached storage or a SAN?|||

Thanks for the reply. We have Netapp SCSI disk storage. Could you please tell me how do I troubleshoot this so that I wont get such errors in future.

Thanks

|||Based on this and what you describe in your other post, you really ought to give Microsoft PSS a call.|||

Do read this kb before contacting PSS. You might be running on an unsupported device.

http://support.microsoft.com/kb/304261

Error: duplicate key row

What might cause the following error when inserting rows in tables that have primary keys defined as IDENTITY (1,1)?

duplicate key row in object 'aa' with unique index 'aa'

I have seen this happen frequently when data has been bulk loaded sometime in the past into a table that has an IDENTITY key. This is especially common when you have a test environment in which some production data was bulk loaded to give some "good test data." To me it means that an identity number has "already been used" as a record key.

Dave

|||The rows are inserted one at a time using the parent IDENTITY key as a FK constraint in the child table which also has an IDENTITY KEY. However, this scenario does not sound like it would cause an error.|||

Can you post the table structures? That would help us to see what your problem might be.

If you can post a few statements that cause the duplicates, that would even be better.

|||

If you have the property NOT FOR REPLICATION enabled for the identity column.

Merging changes would allow the exact id values to be inserted rather than a new value that would give the error if same id value exists in the participating server.

similarly, if you are trying to insert manually using SET IDENTITY_INSERT table ON...

error: Cursor not returned from query

I'm a really beginner about sql2000.
During my test I have created the following query. It's works ok until I
do't add the code included in section A, when I add it the i obtain the
error: Cursor not returned from query

Anyone can help me?

Thanks Carlo M.

set nocount on

IF OBJECT_ID('storico_big') IS NULL -- section A begin
create table storico_big( data datetime,
bcarrier varchar(20),
bda CHAR(30),
bzone char(50),
bdur int) ;
insert into storico_big -- section A
end

select top 10000
adetdate,bcarrier,bda,bzone,bdur
from pp_cdr (nolock)
where
adetdate < :data_fin and adetdate > :data_in order by adetdate
set nocount off

-- end of queryIW2FIV (carlo.merlini[NONROMPERE]@.libero.it) writes:
> I'm a really beginner about sql2000.
> During my test I have created the following query. It's works ok until I
> do't add the code included in section A, when I add it the i obtain the
> error: Cursor not returned from query

Apparently you are using some environment unknown to me. At least I
don't recognize the message.

> IF OBJECT_ID('storico_big') IS NULL -- section A begin
> create table storico_big( data datetime,
> bcarrier varchar(20),
> bda CHAR(30),
> bzone char(50),
> bdur int) ;
> insert into storico_big -- section A
> end

Since there are several apparent syntax errors here, it would have been nice
if you environment had returned the errors from SQL Server, rather than
bitching about the missing cursor.

It can be a good to run the query from Query Analyzer to get better
error diagnostics.

I don't really want to suggest a correction, because I can't understand
what you are trying to do. It appears that in the same batch that you first
want to create a table, insert into it, and then select data from another
table.

Possibly you want to insert the data from the SELECT statement into
storico_big, but in such case you should

1) get rid of that extraneous end
2) add an explicit column list to the INSERT statement.

However, I have a feeling that if you insert data into the table, the
client environment will still complain about a missing cursor...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> IW2FIV (carlo.merlini[NONROMPERE]@.libero.it) writes:
> > I'm a really beginner about sql2000.
> > During my test I have created the following query. It's works ok until I
> > do't add the code included in section A, when I add it the i obtain the
> > error: Cursor not returned from query
> Apparently you are using some environment unknown to me. At least I
> don't recognize the message.
I would suspect that it's some data layer (such as ADO, ADO.NET, DAO,
etc), which has at least two different methods of performing work in
the database - one for performing data retrieval and another (possibly
on another object, if the layer is object oriented) which allows data
manipulation.

For instance, ADO.NET has ExecuteReader and ExecuteNonQuery methods on
it's command object.

Damien|||Damien (Damien_The_Unbeliever@.hotmail.com) writes:
> I would suspect that it's some data layer (such as ADO, ADO.NET, DAO,
> etc), which has at least two different methods of performing work in
> the database - one for performing data retrieval and another (possibly
> on another object, if the layer is object oriented) which allows data
> manipulation.
> For instance, ADO.NET has ExecuteReader and ExecuteNonQuery methods on
> it's command object.

Obviously IWZFIV is not using ADO .Net. ADO .Net does work with cursors
at all, as far as a I know. A more substantial clue is the condition:

adetdate < :data_fin and adetdate > :data_in order by adetdate

Apparently IWZFIV is using some form of embedded SQL.

Anyway, I would not really describe ADO .Net as providing different methods
for different purposes. If all you want is minimalism, you can do every-
thing with ExecuteReader. The other methods, ExecuteNonQuery, ExecuteScalar
and DataAdapter.Fill can be seen as convenience methods implemented on
top of ExecuteReader. (OK, this is not really true. There are some
fine differences when there are multiple error messages and result sets
interleaved.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Error: Could not connect to database

The following error is encountered while trying to Host the site from IIS ..
" The Assembly Version ([ASSEMBLY VERSION]) does not match the Database Version ([DATABASE VERSION]).
"Error: Could not connect to the database specified in the connection string for SqlDataProvider. "

We have 2 different machines, over which we have 2 different SQL Server.
In Web.Config, if we give server=server1 it works well and there is no problem at all...
if we give server=server2, and executehttp://localhost/application, the above mentioned error occurs..

and yes the application is hosted on 3rd machine......

Can anyone throw some light on this regard.
Its urgent.

You're using DotNetNuke, right? Try this search on the Forums to see other threads on this topic:
http://forums.asp.net/search/SearchResults.aspx?q=ASSEMBLY+VERSION+match+DATABASE+VERSION&o=Relevance

Error: connection is busy with results for another hstmt

Good Afternoon. I have a Crystal report that accesses a sql 2000 server. W
hen I run the report twice with the same parrameters. I get the following e
rror:
crystal reports connection is busy with results for another hstmt
As I understand it, this has to do with multiple record sets, and something
to the effect that my report is not consuming everything and therefore leavi
ng a cursor.
And while I understand that for the most part, I can't even begin to think h
ow to troubleshoot the report and find the problem. It's a HUGE report with
excess of 30 subreports. It is designed to only return 1 record, with nume
rous fields from throughout
the database placed on the report. Like I said it has numerous subreports,
and all are linked to the same URN. Anyway, is there anything I can do shor
t of upgrading the server to 2005?
From http://www.developmentnow.com/g/111...server-odbc.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.comafter nearly 30 hours of searching, I finally posted my question here.
What's funny is that 2 hours later I found my answer:
http://support.microsoft.com/kb/173410
From http://www.developmentnow.com/group...
=995268
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

Error: connection is busy with results for another hstmt

Good Afternoon. I have a Crystal report that accesses a sql 2000 server. When I run the report twice with the same parrameters. I get the following error:
crystal reports connection is busy with results for another hstmt
As I understand it, this has to do with multiple record sets, and something to the effect that my report is not consuming everything and therefore leaving a cursor.
And while I understand that for the most part, I can't even begin to think how to troubleshoot the report and find the problem. It's a HUGE report with excess of 30 subreports. It is designed to only return 1 record, with numerous fields from throughout the database placed on the report. Like I said it has numerous subreports, and all are linked to the same URN. Anyway, is there anything I can do short of upgrading the server to 2005?
From http://www.developmentnow.com/g/111_2007_7_0_0_0/sql-server-odbc.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
after nearly 30 hours of searching, I finally posted my question here.
What's funny is that 2 hours later I found my answer:
http://support.microsoft.com/kb/173410
From http://www.developmentnow.com/groups/viewthread.aspx?newsgroupid=111&threadid=995268
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

Error: Changed language to ...

When I try to open some tables in a database using QueryAnalyzer I receive
the following error:
"[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language to
bulgarian".
And in result the table is not opened.
Bulgarian is my native language by the way.
I made the following experiment :
I created a new table with the same definition as one of the problem tables.
Then I inserted all records from the problem table in the new one. Then I
had no problems opening the new table using QueryAnalyzer.
Do you have any idea what the problem is?
Thank you!What language is SQL Server set to? And was it the same as to when you
created the table the first time?
Yovan
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:eVajZeDeDHA.2324@.TK2MSFTNGP11.phx.gbl...
> When I try to open some tables in a database using QueryAnalyzer I receive
> the following error:
> "[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language to
> bulgarian".
> And in result the table is not opened.
> Bulgarian is my native language by the way.
> I made the following experiment :
> I created a new table with the same definition as one of the problem
tables.
> Then I inserted all records from the problem table in the new one. Then I
> had no problems opening the new table using QueryAnalyzer.
> Do you have any idea what the problem is?
> Thank you!
>

Error: Backup Log terminating abnormally

hi
i am using sql server 2000. when i try to backup log file it throws out the following error and hence the second command that is dbcc shrinkfile never gets executed. if i restart sql server and re-run these commands ... no error pops up ... both the comma
nds get completed sccessfully and log file gets shrunk too.
these are the commands that i run
BACKUP LOG BAMPrimaryImport WITH TRUNCATE_ONLY
DBCC SHRINKFILE ( BAMPrimaryImport_log, 500 )
this is the error that i get
Server: Msg 3023, Level 16, State 3, Line 1
Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
I am quite sure that no other command is active at the time when i issue these commands, still i get error. any help will be appreciated.
regards
yashrah.
Hi
Can you check your SQL Error log for the message when this command failed,
also can you please provide the SQL Server 2000 Service pack level.
you can get the Service pack level by running select @.@.version from query
analyzer.
Also, do you have replication set up, if yes, what kind of replication?
3023 means, It thinks there is some concurrent database operation going on
that is blocking the ALTER DATABASE, because it is unable to acquire the
database lock.
Regards
Sadeesh
This posting is provided AS IS with no warranties, and confers no rights.
"Yashrah [Xavor]" <YashrahXavor@.discussions.microsoft.com> wrote in message
news:73E811CE-FCBF-4D29-9538-6D22F842A8EC@.microsoft.com...
> hi
> i am using sql server 2000. when i try to backup log file it throws out
> the following error and hence the second command that is dbcc shrinkfile
> never gets executed. if i restart sql server and re-run these commands ...
> no error pops up ... both the commands get completed sccessfully and log
> file gets shrunk too.
> these are the commands that i run
> BACKUP LOG BAMPrimaryImport WITH TRUNCATE_ONLY
> DBCC SHRINKFILE ( BAMPrimaryImport_log, 500 )
> this is the error that i get
> Server: Msg 3023, Level 16, State 3, Line 1
> Backup and file manipulation operations (such as ALTER DATABASE ADD FILE)
> on a database must be serialized. Reissue the statement after the current
> backup or file manipulation operation is completed.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP LOG is terminating abnormally.
> I am quite sure that no other command is active at the time when i issue
> these commands, still i get error. any help will be appreciated.
> regards
> yashrah.
|||thanx for ur concern...
SQL Error Log says
2004-07-22 02:40:23.10 backup BACKUP failed to complete the command exec shrink_bamprimaryimport_logfile
infact "shrink_bamprimaryimport_logfile" is my stored procedure that contains the two commands (backup log and dbcc shrinkfile) written below.
servie pack 3a is installed and no replication set up is there.
"Sadeesh[MSFT]" wrote:

> Hi
> Can you check your SQL Error log for the message when this command failed,
> also can you please provide the SQL Server 2000 Service pack level.
> you can get the Service pack level by running select @.@.version from query
> analyzer.
> Also, do you have replication set up, if yes, what kind of replication?
> 3023 means, It thinks there is some concurrent database operation going on
> that is blocking the ALTER DATABASE, because it is unable to acquire the
> database lock.
> Regards
> Sadeesh
> --
> This posting is provided AS IS with no warranties, and confers no rights.
> "Yashrah [Xavor]" <YashrahXavor@.discussions.microsoft.com> wrote in message
> news:73E811CE-FCBF-4D29-9538-6D22F842A8EC@.microsoft.com...
>
>

Error: Backup Log terminating abnormally

hi
i am using sql server 2000. when i try to backup log file it throws out the
following error and hence the second command that is dbcc shrinkfile never g
ets executed. if i restart sql server and re-run these commands ... no error
pops up ... both the comma
nds get completed sccessfully and log file gets shrunk too.
these are the commands that i run
BACKUP LOG BAMPrimaryImport WITH TRUNCATE_ONLY
DBCC SHRINKFILE ( BAMPrimaryImport_log, 500 )
this is the error that i get
Server: Msg 3023, Level 16, State 3, Line 1
Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on
a database must be serialized. Reissue the statement after the current back
up or file manipulation operation is completed.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
I am quite sure that no other command is active at the time when i issue the
se commands, still i get error. any help will be appreciated.
regards
yashrah.Hi
Can you check your SQL Error log for the message when this command failed,
also can you please provide the SQL Server 2000 Service pack level.
you can get the Service pack level by running select @.@.version from query
analyzer.
Also, do you have replication set up, if yes, what kind of replication?
3023 means, It thinks there is some concurrent database operation going on
that is blocking the ALTER DATABASE, because it is unable to acquire the
database lock.
Regards
Sadeesh
--
This posting is provided AS IS with no warranties, and confers no rights.
"Yashrah [Xavor]" <YashrahXavor@.discussions.microsoft.com> wrote in mess
age
news:73E811CE-FCBF-4D29-9538-6D22F842A8EC@.microsoft.com...
> hi
> i am using sql server 2000. when i try to backup log file it throws out
> the following error and hence the second command that is dbcc shrinkfile
> never gets executed. if i restart sql server and re-run these commands ...
> no error pops up ... both the commands get completed sccessfully and log
> file gets shrunk too.
> these are the commands that i run
> BACKUP LOG BAMPrimaryImport WITH TRUNCATE_ONLY
> DBCC SHRINKFILE ( BAMPrimaryImport_log, 500 )
> this is the error that i get
> Server: Msg 3023, Level 16, State 3, Line 1
> Backup and file manipulation operations (such as ALTER DATABASE ADD FILE)
> on a database must be serialized. Reissue the statement after the current
> backup or file manipulation operation is completed.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP LOG is terminating abnormally.
> I am quite sure that no other command is active at the time when i issue
> these commands, still i get error. any help will be appreciated.
> regards
> yashrah.|||thanx for ur concern...
SQL Error Log says
2004-07-22 02:40:23.10 backup BACKUP failed to complete the command exec
shrink_bamprimaryimport_logfile
infact "shrink_bamprimaryimport_logfile" is my stored procedure that contain
s the two commands (backup log and dbcc shrinkfile) written below.
servie pack 3a is installed and no replication set up is there.
"Sadeesh[MSFT]" wrote:

> Hi
> Can you check your SQL Error log for the message when this command failed,
> also can you please provide the SQL Server 2000 Service pack level.
> you can get the Service pack level by running select @.@.version from query
> analyzer.
> Also, do you have replication set up, if yes, what kind of replication?
> 3023 means, It thinks there is some concurrent database operation going on
> that is blocking the ALTER DATABASE, because it is unable to acquire the
> database lock.
> Regards
> Sadeesh
> --
> This posting is provided AS IS with no warranties, and confers no rights.
> "Yashrah [Xavor]" <YashrahXavor@.discussions.microsoft.com> wrote in me
ssage
> news:73E811CE-FCBF-4D29-9538-6D22F842A8EC@.microsoft.com...
>
>sql

Error: Attribute key cannot be found

Helo, I have the following problem

I have one fact-table in my cube
And I have one dimension table

When I process the cube I get the following error:

"Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_fbi_analytic_journals, Column: journals_key, Value: 306."

I understand the error, and I know why I get the error, but how can I solve the error

In the fact-table there is a record with key 306, but this key is not found in the dimension table. What I want is that in the cube are all the record that are in the fact-table with a reference in the dimension table, so the record with key 306 must not be in the cube

Anyone an idea ?

Thx

Look at your processing options. On the "Dimension key errors" tab select "Use custom error configuration" and change the setting to "Convert to unknown".

|||

I have this same situation. But where are the processing options found? I don't see it!

thx,

Marilyn

|||found it! thanks.|||

Is there a way to set the "Dimension key errors" to automatically use the 'Use Custom error configuration' without having to set it each time processing is performed?

thx,

-Marilyn

|||

It is also possible to set ErrorConfiguration on the partitoin, measure group or on the cube.

Take a look at the properties dialog for cube or partition in SQL Management Studio, you will find a Error Configuraiton tab there.

In BI Dev Studio look at the properties panel and find ErrorConfiguration property. Once you set it to "Custom", you will be able to set individual error configuration properties.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

right-click on the cube in the solution explorer pane.

select 'process...'

press the 'change settings' button.

select the 'dimension key errors' tab

select the radio button 'use custom error configuration'

|||

Your settings won't "stick" in the Analysis designer itself for some reason... you must create an Analysis Services Task in BI Studio to run against the cube you've created.

|||

Hello,

I get the same error msg but here's the situation

I create a new project, with a data source and a data source view with only one table

I create a dimension base on this table and try to process it

There's no cube, only a dimension and it failed with attribute key not found error

After many test I discover that the lines causing the error where random and the error line are same as those that work

I understand the reason why this error is thrown when a cube reference to an item that doesn't in the dimension but in my situation I really can't understand what happen !!

Can somebody help me with this one

thank you

Billy

|||make sure you process your dimensions before your CUBE.

Error: Attribute key cannot be found

Helo, I have the following problem

I have one fact-table in my cube
And I have one dimension table

When I process the cube I get the following error:

"Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_fbi_analytic_journals, Column: journals_key, Value: 306."

I understand the error, and I know why I get the error, but how can I solve the error

In the fact-table there is a record with key 306, but this key is not found in the dimension table. What I want is that in the cube are all the record that are in the fact-table with a reference in the dimension table, so the record with key 306 must not be in the cube

Anyone an idea ?

Thx

Look at your processing options. On the "Dimension key errors" tab select "Use custom error configuration" and change the setting to "Convert to unknown".

|||

I have this same situation. But where are the processing options found? I don't see it!

thx,

Marilyn

|||found it! thanks.|||

Is there a way to set the "Dimension key errors" to automatically use the 'Use Custom error configuration' without having to set it each time processing is performed?

thx,

-Marilyn

|||

It is also possible to set ErrorConfiguration on the partitoin, measure group or on the cube.

Take a look at the properties dialog for cube or partition in SQL Management Studio, you will find a Error Configuraiton tab there.

In BI Dev Studio look at the properties panel and find ErrorConfiguration property. Once you set it to "Custom", you will be able to set individual error configuration properties.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

right-click on the cube in the solution explorer pane.

select 'process...'

press the 'change settings' button.

select the 'dimension key errors' tab

select the radio button 'use custom error configuration'

|||

Your settings won't "stick" in the Analysis designer itself for some reason... you must create an Analysis Services Task in BI Studio to run against the cube you've created.

|||

Hello,

I get the same error msg but here's the situation

I create a new project, with a data source and a data source view with only one table

I create a dimension base on this table and try to process it

There's no cube, only a dimension and it failed with attribute key not found error

After many test I discover that the lines causing the error where random and the error line are same as those that work

I understand the reason why this error is thrown when a cube reference to an item that doesn't in the dimension but in my situation I really can't understand what happen !!

Can somebody help me with this one

thank you

Billy

|||make sure you process your dimensions before your CUBE.

Error: Attribute key cannot be found

Helo, I have the following problem

I have one fact-table in my cube
And I have one dimension table

When I process the cube I get the following error:

"Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_fbi_analytic_journals, Column: journals_key, Value: 306."

I understand the error, and I know why I get the error, but how can I solve the error

In the fact-table there is a record with key 306, but this key is not found in the dimension table. What I want is that in the cube are all the record that are in the fact-table with a reference in the dimension table, so the record with key 306 must not be in the cube

Anyone an idea ?

Thx

Look at your processing options. On the "Dimension key errors" tab select "Use custom error configuration" and change the setting to "Convert to unknown".

|||

I have this same situation. But where are the processing options found? I don't see it!

thx,

Marilyn

|||found it! thanks.|||

Is there a way to set the "Dimension key errors" to automatically use the 'Use Custom error configuration' without having to set it each time processing is performed?

thx,

-Marilyn

|||

It is also possible to set ErrorConfiguration on the partitoin, measure group or on the cube.

Take a look at the properties dialog for cube or partition in SQL Management Studio, you will find a Error Configuraiton tab there.

In BI Dev Studio look at the properties panel and find ErrorConfiguration property. Once you set it to "Custom", you will be able to set individual error configuration properties.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

right-click on the cube in the solution explorer pane.

select 'process...'

press the 'change settings' button.

select the 'dimension key errors' tab

select the radio button 'use custom error configuration'

|||

Your settings won't "stick" in the Analysis designer itself for some reason... you must create an Analysis Services Task in BI Studio to run against the cube you've created.

|||

Hello,

I get the same error msg but here's the situation

I create a new project, with a data source and a data source view with only one table

I create a dimension base on this table and try to process it

There's no cube, only a dimension and it failed with attribute key not found error

After many test I discover that the lines causing the error where random and the error line are same as those that work

I understand the reason why this error is thrown when a cube reference to an item that doesn't in the dimension but in my situation I really can't understand what happen !!

Can somebody help me with this one

thank you

Billy

|||make sure you process your dimensions before your CUBE.

Error: Attribute key cannot be found

Helo, I have the following problem

I have one fact-table in my cube
And I have one dimension table

When I process the cube I get the following error:

"Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_fbi_analytic_journals, Column: journals_key, Value: 306."

I understand the error, and I know why I get the error, but how can I solve the error

In the fact-table there is a record with key 306, but this key is not found in the dimension table. What I want is that in the cube are all the record that are in the fact-table with a reference in the dimension table, so the record with key 306 must not be in the cube

Anyone an idea ?

Thx

Look at your processing options. On the "Dimension key errors" tab select "Use custom error configuration" and change the setting to "Convert to unknown".

|||

I have this same situation. But where are the processing options found? I don't see it!

thx,

Marilyn

|||found it! thanks.|||

Is there a way to set the "Dimension key errors" to automatically use the 'Use Custom error configuration' without having to set it each time processing is performed?

thx,

-Marilyn

|||

It is also possible to set ErrorConfiguration on the partitoin, measure group or on the cube.

Take a look at the properties dialog for cube or partition in SQL Management Studio, you will find a Error Configuraiton tab there.

In BI Dev Studio look at the properties panel and find ErrorConfiguration property. Once you set it to "Custom", you will be able to set individual error configuration properties.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

right-click on the cube in the solution explorer pane.

select 'process...'

press the 'change settings' button.

select the 'dimension key errors' tab

select the radio button 'use custom error configuration'

|||

Your settings won't "stick" in the Analysis designer itself for some reason... you must create an Analysis Services Task in BI Studio to run against the cube you've created.

|||

Hello,

I get the same error msg but here's the situation

I create a new project, with a data source and a data source view with only one table

I create a dimension base on this table and try to process it

There's no cube, only a dimension and it failed with attribute key not found error

After many test I discover that the lines causing the error where random and the error line are same as those that work

I understand the reason why this error is thrown when a cube reference to an item that doesn't in the dimension but in my situation I really can't understand what happen !!

Can somebody help me with this one

thank you

Billy

|||make sure you process your dimensions before your CUBE.

Error: Attribute key cannot be found

Helo, I have the following problem

I have one fact-table in my cube
And I have one dimension table

When I process the cube I get the following error:

"Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_fbi_analytic_journals, Column: journals_key, Value: 306."

I understand the error, and I know why I get the error, but how can I solve the error

In the fact-table there is a record with key 306, but this key is not found in the dimension table. What I want is that in the cube are all the record that are in the fact-table with a reference in the dimension table, so the record with key 306 must not be in the cube

Anyone an idea ?

Thx

Look at your processing options. On the "Dimension key errors" tab select "Use custom error configuration" and change the setting to "Convert to unknown".

|||

I have this same situation. But where are the processing options found? I don't see it!

thx,

Marilyn

|||found it! thanks.|||

Is there a way to set the "Dimension key errors" to automatically use the 'Use Custom error configuration' without having to set it each time processing is performed?

thx,

-Marilyn

|||

It is also possible to set ErrorConfiguration on the partitoin, measure group or on the cube.

Take a look at the properties dialog for cube or partition in SQL Management Studio, you will find a Error Configuraiton tab there.

In BI Dev Studio look at the properties panel and find ErrorConfiguration property. Once you set it to "Custom", you will be able to set individual error configuration properties.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

right-click on the cube in the solution explorer pane.

select 'process...'

press the 'change settings' button.

select the 'dimension key errors' tab

select the radio button 'use custom error configuration'

|||

Your settings won't "stick" in the Analysis designer itself for some reason... you must create an Analysis Services Task in BI Studio to run against the cube you've created.

|||

Hello,

I get the same error msg but here's the situation

I create a new project, with a data source and a data source view with only one table

I create a dimension base on this table and try to process it

There's no cube, only a dimension and it failed with attribute key not found error

After many test I discover that the lines causing the error where random and the error line are same as those that work

I understand the reason why this error is thrown when a cube reference to an item that doesn't in the dimension but in my situation I really can't understand what happen !!

Can somebody help me with this one

thank you

Billy

|||make sure you process your dimensions before your CUBE.

sql

Error: Attribute key cannot be found

Helo, I have the following problem

I have one fact-table in my cube
And I have one dimension table

When I process the cube I get the following error:

"Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_fbi_analytic_journals, Column: journals_key, Value: 306."

I understand the error, and I know why I get the error, but how can I solve the error

In the fact-table there is a record with key 306, but this key is not found in the dimension table. What I want is that in the cube are all the record that are in the fact-table with a reference in the dimension table, so the record with key 306 must not be in the cube

Anyone an idea ?

Thx

Look at your processing options. On the "Dimension key errors" tab select "Use custom error configuration" and change the setting to "Convert to unknown".

|||

I have this same situation. But where are the processing options found? I don't see it!

thx,

Marilyn

|||found it! thanks.|||

Is there a way to set the "Dimension key errors" to automatically use the 'Use Custom error configuration' without having to set it each time processing is performed?

thx,

-Marilyn

|||

It is also possible to set ErrorConfiguration on the partitoin, measure group or on the cube.

Take a look at the properties dialog for cube or partition in SQL Management Studio, you will find a Error Configuraiton tab there.

In BI Dev Studio look at the properties panel and find ErrorConfiguration property. Once you set it to "Custom", you will be able to set individual error configuration properties.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

right-click on the cube in the solution explorer pane.

select 'process...'

press the 'change settings' button.

select the 'dimension key errors' tab

select the radio button 'use custom error configuration'

|||

Your settings won't "stick" in the Analysis designer itself for some reason... you must create an Analysis Services Task in BI Studio to run against the cube you've created.

|||

Hello,

I get the same error msg but here's the situation

I create a new project, with a data source and a data source view with only one table

I create a dimension base on this table and try to process it

There's no cube, only a dimension and it failed with attribute key not found error

After many test I discover that the lines causing the error where random and the error line are same as those that work

I understand the reason why this error is thrown when a cube reference to an item that doesn't in the dimension but in my situation I really can't understand what happen !!

Can somebody help me with this one

thank you

Billy

|||make sure you process your dimensions before your CUBE.

Error: 8624 Internal Query Processor Error: The query processor could not produce a query plan.

SQL Server 2005 9.0.3161 on Win 2k3 R2

I receive the following error:

"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."

I have traced this to an insert statement that executes as part of a stored procedure.

INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)

VALUES (@.journal_id, @.acct_id, @.acct_recv_id, @.amount)

There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.

Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).

I'd suggest calling PSS. It does sound like a bug here.

|||

I ran into this exact problem today, and it turned out to be related to a catalog being inconsistent, when we performed a migration from a SQL 2000 server to a SQL 2005 server, by restoring a backup . There was a foreign key constraint that was not functioning correctly following the migration and that was causing the error to show up.

We had restored the SQL 2000 backup to a 2005 server, then changed the compatability mode from 80 to 90, then updated statistics. We did not however, run the DBCC following all of that. We had run tests prior to the migration, but this hadn't showed up as an issue until the day of the migration. As a matter of fact, we had run DBCC's on the SQL 2000 database and things were fine. We checked the backup file, and that was fine. The problem was in some part of the page migrations that happen within the engine itself. Maybe this was a page alignment issue, whereby a given catalog page was in a decent state for migration when we were testing, but changed it's alignment on a given page between the time we tested and the time that we migrated. Who know....that's just my speculation.

The error from running a simple insert statement looks like this:

Msg 8624, Level 16, State 1, Line 1

Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

The error from the DBCC CHECKDB looks like this:

Msg 8992, Level 16, State 1, Line 1

Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=238623893,key_index_id=3) of row (object_id=951674438) in sys.foreign_keys does not have a matching row (object_id=238623893,index_id=3) in sys.indexes.

CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.

This lead us to the conclusion that we could drop the FK and recreate it, and have everything work. And, it did.

The moral of the story, like it's been said many times...run DBCC's after restores when going from SQL 2000 to SQL 2005.

Hope this helps someone else in the future.

-- Don

|||

I consulted tech support. It was finally classified as a bug. The database was in 80 compatibility and changing it to 90 made the problem go away. The other work around was to set arithabort on. This explained why it worked from management studio and not anywhere else. Apparently management studio has arithabort set to on by default. The following is the case closure confirmation from the MS engineer:

PROBLEM:

=======

An insert query to a table that has foreign key references cannot generate a plan with error 8624 when arithabort is set to be off.

Server: Msg 8624, Level 16, State 1, Line 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

AGREED UPON CRITERIA FOR RESOLUTION:

===========================

Investigate root cause

CAUSE:

=====

This issue appears to be related to the fact database compatibility is set to 80.I have filed a product bug for tracking purpose

RESOLUTION:

===========

Setting database compatibility to be 90 resolved the issue

Error: 8624 Internal Query Processor Error: The query processor could not produce a query plan.

SQL Server 2005 9.0.3161 on Win 2k3 R2

I receive the following error:

"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."

I have traced this to an insert statement that executes as part of a stored procedure.

INSERTINTO ledger (journal__id, account__id,account_recv_info__id,amount)

VALUES(@.journal_id, @.acct_id, @.acct_recv_id, @.amount)

There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.

Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).

I'd suggest calling PSS. It does sound like a bug here.

|||

I ran into this exact problem today, and it turned out to be related to a catalog being inconsistent, when we performed a migration from a SQL 2000 server to a SQL 2005 server, by restoring a backup . There was a foreign key constraint that was not functioning correctly following the migration and that was causing the error to show up.

We had restored the SQL 2000 backup to a 2005 server, then changed the compatability mode from 80 to 90, then updated statistics. We did not however, run the DBCC following all of that. We had run tests prior to the migration, but this hadn't showed up as an issue until the day of the migration. As a matter of fact, we had run DBCC's on the SQL 2000 database and things were fine. We checked the backup file, and that was fine. The problem was in some part of the page migrations that happen within the engine itself. Maybe this was a page alignment issue, whereby a given catalog page was in a decent state for migration when we were testing, but changed it's alignment on a given page between the time we tested and the time that we migrated. Who know....that's just my speculation.

The error from running a simple insert statement looks like this:

Msg 8624, Level 16, State 1, Line 1

Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

The error from the DBCC CHECKDB looks like this:

Msg 8992, Level 16, State 1, Line 1

Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=238623893,key_index_id=3) of row (object_id=951674438) in sys.foreign_keys does not have a matching row (object_id=238623893,index_id=3) in sys.indexes.

CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.

This lead us to the conclusion that we could drop the FK and recreate it, and have everything work. And, it did.

The moral of the story, like it's been said many times...run DBCC's after restores when going from SQL 2000 to SQL 2005.

Hope this helps someone else in the future.

-- Don

|||

I consulted tech support. It was finally classified as a bug. The database was in 80 compatibility and changing it to 90 made the problem go away. The other work around was to set arithabort on. This explained why it worked from management studio and not anywhere else. Apparently management studio has arithabort set to on by default. The following is the case closure confirmation from the MS engineer:

PROBLEM:

=======

An insert query to a table that has foreign key references cannot generate a plan with error 8624 when arithabort is set to be off.

Server: Msg 8624, Level 16, State 1, Line 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

AGREED UPON CRITERIA FOR RESOLUTION:

===========================

Investigate root cause

CAUSE:

=====

This issue appears to be related to the fact database compatibility is set to 80.I have filed a product bug for tracking purpose

RESOLUTION:

===========

Setting database compatibility to be 90 resolved the issue

Error: 5180 - Could not open FCB for invalid file ID #

When we do a DBCC SHRINKDATABASE <dbname>, 5 on one of our high volume
databases we get following errors:
- spid333 Error: 5180, Severity: 22, State: 1
- spid333 Could not open FCB for invalid file ID <#> in database '<dbname>'.
Is there anyone here that has a hint on what is causing this.
This error stated that the file was dropped or the database was corrupted.
- Please run DBCC SHRINKDATABASE again.
- If the error shows up again, please run DBCC CHECKDB to see if the
database is corrupted.
- If the database is not corrupted, then probably there is a bug in shrink
code, you can contact PSS for further assistant.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tor Lynge Olsen" <TorLyngeOlsen@.discussions.microsoft.com> wrote in message
news:A5E9AA7C-6A4D-4527-A9EC-687A15216E91@.microsoft.com...
> When we do a DBCC SHRINKDATABASE <dbname>, 5 on one of our high volume
> databases we get following errors:
> - spid333 Error: 5180, Severity: 22, State: 1
> - spid333 Could not open FCB for invalid file ID <#> in database
'<dbname>'.
> Is there anyone here that has a hint on what is causing this.
>
sql