Error reattaching database following it being marked as Suspect

I am using SQL Server 2000 with SP3 (product version
8.00.760).
A database in this installation was marked as 'SUSPECT'
whilst I was excuting a DELETE SQL statement using the SQL
Query Analyzer tool.
Using Enterprise Manager I was subsequently unable to
access the database, and so thought the easiest option
would be to restore it from a BAK file that had been
created a few hours earlier as data modiefied in the time
between was not vital. I detached the database before
attemping this, and then discovered the only backup
available is a differential one and it will not restore.
Therefore, I was hoping to reattached the database and
reset the 'SUSPECT' flag, so I could at least access the
data again. However, when I attempt to attach the MDF
file (the associated LDF file is in the same location) I
get the following errors:
Error 9001: The log for database 'MY_DATABASE' is
not available.
and:
Attaching the database has failed.
I have also tried attaching the database using the
sp_attach_single_file_db stored procdure:
EXEC sp_attach_single_file_db @.dbname
= 'MY_DATABASE',
@.physname = 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\MY_DATABASE_Data.MDF'
which return the following error message:
Server: Msg 9001, Level 21, State 4, Line 1
The log for database 'ORBIT_REPORTS' is not
available.
And also tried using the sp_attach_db stored procedure:
EXEC sp_attach_db @.dbname = 'MY_DATABASE',
@.filename1 = 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\MY_DATABASE_Data.MDF',
@.filename2 = 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\MY_DATABASE_Log.LDF'
but still get the following error:
Server: Msg 9001, Level 21, State 1, Line 1
The log for database 'ORBIT_REPORTS' is not
available.
Both files _do_ exist in the directory:
'C:\Program Files\Microsoft SQL Server\MSSQL\Data\
Can anyone help?If you have a diff backup you should have a copy of a full backup (since
diff backup wont start until it knows a full backup exists).
Is the database called Orbits_reports?
If you have SQL 2K...right click database -all tasks and attach DB...does
the files come up as red? Have you got the correct permissions for a file?
Have you renamed a file?
Regards
Z
"Ian Munday" <ian.munday@.uk.pwc.com> wrote in message
news:078201c37c64$18bdc690$a001280a@.phx.gbl...
> I am using SQL Server 2000 with SP3 (product version
> 8.00.760).
> A database in this installation was marked as 'SUSPECT'
> whilst I was excuting a DELETE SQL statement using the SQL
> Query Analyzer tool.
> Using Enterprise Manager I was subsequently unable to
> access the database, and so thought the easiest option
> would be to restore it from a BAK file that had been
> created a few hours earlier as data modiefied in the time
> between was not vital. I detached the database before
> attemping this, and then discovered the only backup
> available is a differential one and it will not restore.
> Therefore, I was hoping to reattached the database and
> reset the 'SUSPECT' flag, so I could at least access the
> data again. However, when I attempt to attach the MDF
> file (the associated LDF file is in the same location) I
> get the following errors:
> Error 9001: The log for database 'MY_DATABASE' is
> not available.
> and:
> Attaching the database has failed.
>
> I have also tried attaching the database using the
> sp_attach_single_file_db stored procdure:
> EXEC sp_attach_single_file_db @.dbname
> = 'MY_DATABASE',
> @.physname = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\MY_DATABASE_Data.MDF'
> which return the following error message:
> Server: Msg 9001, Level 21, State 4, Line 1
> The log for database 'ORBIT_REPORTS' is not
> available.
> And also tried using the sp_attach_db stored procedure:
> EXEC sp_attach_db @.dbname = 'MY_DATABASE',
> @.filename1 = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\MY_DATABASE_Data.MDF',
> @.filename2 = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\MY_DATABASE_Log.LDF'
> but still get the following error:
> Server: Msg 9001, Level 21, State 1, Line 1
> The log for database 'ORBIT_REPORTS' is not
> available.
> Both files _do_ exist in the directory:
> 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\
>
> Can anyone help?

error Openrowset

my DMX query is:

INSERT INTO [Analyze Shipments]
(
SKIP,
[Store Id],
[Vendor Name],
[Product Type],
[Days Since Last Shipped],
[Quantity]
)
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\data mining va XML\0212025_0212279\DATABASE\Access\SavingsMart\SavingsMart.mdb;Persist Security Info=False',
'SELECT DISTINCT
"vw_Shipments"."ShipmentID"
AS "Shipment Id",
"vw_Shipments"."DaysSinceLastShipped"
AS "Days Since Last Shipped",
"vw_Shipments"."StoreID"
AS "Store Id",
"vw_Shipments"."Quantity"
AS "Quantity",
"vw_Shipments"."VendorName"
AS "Vendor Name",
"vw_Shipments"."ProductType"
AS "Product Type"
FROM "vw_Shipments"')

and error is:

Error (Data mining): This server is not configured to support the 'microsoft.jet.oledb.4.0' provider used in the OPENROWSET clause.

Can you help me,please?

Moving to DM forum.

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

|||

You should make sure that:

- Ad hoc open rowset queries are supported by the server (the AllowAdHocOpenRowsetQueries server property)

- the specified OLE DB provider, Microsoft.Jet.OLEDB.4.0 is included in the list of supported providers (the AllowedProvidersInOpenRowset server property)

More details: http://msdn2.microsoft.com/en-us/library/ms175443.aspx

|||

thank you.

I checked AllowAdHocOpenRowsetQueries and AllowedProvidersInOpenRowset is SQLOLEDB.1
but run my DMX query:

INSERT INTO [Analyze Shipments]
(
SKIP,
[Store Id],
[Vendor Name],
[Product Type],
[Days Since Last Shipped],
[Quantity]
)
OPENROWSET('SQLOLEDB.1','Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;
Initial Catalog=Mart;Data Source=hong',
'SELECT DISTINCT
"vw_Shipments"."ShipmentID"
AS "Shipment Id",
"vw_Shipments"."DaysSinceLastShipped"
AS "Days Since Last Shipped",
"vw_Shipments"."StoreID"
AS "Store Id",
"vw_Shipments"."Quantity"
AS "Quantity",
"vw_Shipments"."VendorName"
AS "Vendor Name",
"vw_Shipments"."ProductType"
AS "Product Type"
FROM "vw_Shipments"')

and error is:
Errors in the OLAP storage engine: An error occurred while processing the '~CaseDetail ~MG' partition of the '~CaseDetail ~MG' measure group for the 'Analyze Shipments_Structure ~MC' cube from the data2 database.

can you help me?

|||

INSERT INTO does column mapping by ordinal, i.e. the order of the columns in the INSERT INTO clause has to be the same as in the source data query. In your DMX statement the order is:

SKIP,
[Store Id],
[Vendor Name],
[Product Type],
[Days Since Last Shipped],
[Quantity]

In your source data query, the order is

ShipmentID
DaysSinceLastShipped
StoreID
Quantity
VendorName
ProductType

which means you are doing things like putting the Vendor Name into the Days Since Last Shipped column, which would give type conversion errors - if you're lucky. If you were unlucky, and you happened to mismatch columns to places where they actually fit, it would have worked and you would have a meaningless model.

sql