Error restoring a database

Please that anybody help me.

I have a phisical device called 'respaldo.bak' inside this file there are three backups of three different databases, The first one is database called 'innovasoft' the second one is 'modelo_de_datos' and the last one is 'Inversiones'.

If I perform a restore of the first one there is no problem, but, when i try to restore the second o third database i get the following error:

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'COMPUTO'. (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.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

El archivo lógico 'INNOVASOFT_Datos' no es parte de la base de datos 'PROBANDO'. Use RESTORE FILELISTONLY para enumerar los nombres de los archivos lógicos.
Fin anómalo de RESTORE DATABASE. (Microsoft SQL Server, Error: 3234)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3234&LinkId=20476

BUTTONS:

OK

I have to say that i want to restore the database with a different name that's why in the error says 'PROBANDO' , i'm really concern about this problem because it means that i have no backups for my data. I used to use this procedure on sql server 2000 every single day without problems, but on sql server 2005 it doesn't work properly.

Please help me.

Thanks in advance.

Make sure you restore with a file destination plus move.

Here is a quick demo showing how it all works.

Code Snippet

--create 3 test dbs
use master
go
create database db1
go
create database db2
go
create database db3
go

--add dummy data to db2
exec db2..sp_executesql N'select db_name() [db] into db'
go

--backup dbs to disk
backup database db1
to disk='c:\dbs.bak'
with init
go
backup database db2
to disk='c:\dbs.bak'
go
backup database db3
to disk='c:\dbs.bak'
go

--restore db2 to new db
restore database db2_2
from disk='c:\dbs.bak'
with file=2,replace,
move 'db2' to 'c:\db2_2.mdf',
move 'db2_log' to 'c:\db2_2.ldf'
go

--check to make sure we actually restore db2
exec db2_2..sp_executesql N'select * from db'
go

--clean up
drop database db2_2, db3, db2, db1
go

|||

Hi, sorry that i answer until today. Well this was what i done

backup database modelo_de_datos

to disk='d:\datos.bak'

with init

go

backup database inversiones

to disk='d:\datos.bak'

go

backup database innovasoft

to disk='d:\datos.bak'

go

--restore inversiones to new db

restore database db2_2

from disk='d:\datos.bak'

with file=2,replace,

move 'inversiones' to 'd:\database\db2_2.mdf',

move 'inversiones_log' to 'd:\database\db2_2.ldf'

go

There was no problem with the backup commands but with the restore part the result was:

Msg 3234, Level 16, State 2, Line 1

El archivo lógico 'inversiones' no es parte de la base de datos 'db2_2'. Use RESTORE FILELISTONLY para enumerar los nombres de los archivos lógicos.

Msg 3013, Level 16, State 1, Line 1

Fin anómalo de RESTORE DATABASE.

As you can see, the problem is exactly the same i gave when doing the task with the Management Studio

Thanks in advance

|||

I think the data & log filenames may not have the standard naming conventions. Test this out.

Code Snippet

create database db4
on
(
name = db4_data,
filename = 'c:\db4_data.mdf',
size = 10,
maxsize = 40,
filegrowth = 5
)
log on
(
name = db4log_log,
filename = 'c:\db4_log.ldf',
size = 10,
maxsize = 40,
filegrowth = 5
)

backup database db4
to disk='c:\dbs1.bak'
with init
go

RESTORE FILELISTONLY FROM DISK = 'c:\dbs1.bak'
WITH FILE=1;

restore database db4_2
from disk='c:\dbs1.bak'
with file=1,replace,
move 'db2' to 'c:\db4_2.mdf',
move 'db2_log' to 'c:\db4_2.ldf'
go

It throws similar error what you are getting now. Above post from oj, works correctly, all you need to do get the filenames from the backup with the below code and substitute in the backup command.

Code Snippet

RESTORE FILELISTONLY FROM DISK = 'c:\dbs.bak'
WITH FILE=2;

|||

You need to get the correct logical names for the invensiones database.

Do:

Code Snippet

restore filelistonly from disk='d:\datos.bak' with file=2

Once you have the correct logical names you then can move the data files to new location.

|||

Thank you guys for your useful help.

One more question. This works excellent using transact sql is it possible to do the same using Management Studio. Because the problem began when i tried to run the restore under Management Studio.,

Than you.,

|||

Sure. You would want to make sure the logical names are valid. In fact, you should see the same t-sql statement if you launch profiler and watch it.

Error restoring a database

Please that anybody help me.

I have a phisical device called 'respaldo.bak' inside this file there are three backups of three different databases, The first one is database called 'innovasoft' the second one is 'modelo_de_datos' and the last one is 'Inversiones'.

If I perform a restore of the first one there is no problem, but, when i try to restore the second o third database i get the following error:

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'COMPUTO'. (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.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

El archivo lógico 'INNOVASOFT_Datos' no es parte de la base de datos 'PROBANDO'. Use RESTORE FILELISTONLY para enumerar los nombres de los archivos lógicos.
Fin anómalo de RESTORE DATABASE. (Microsoft SQL Server, Error: 3234)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3234&LinkId=20476

BUTTONS:

OK

I have to say that i want to restore the database with a different name that's why in the error says 'PROBANDO' , i'm really concern about this problem because it means that i have no backups for my data. I used to use this procedure on sql server 2000 every single day without problems, but on sql server 2005 it doesn't work properly.

Please help me.

Thanks in advance.

Make sure you restore with a file destination plus move.

Here is a quick demo showing how it all works.

Code Snippet

--create 3 test dbs
use master
go
create database db1
go
create database db2
go
create database db3
go

--add dummy data to db2
exec db2..sp_executesql N'select db_name() [db] into db'
go

--backup dbs to disk
backup database db1
to disk='c:\dbs.bak'
with init
go
backup database db2
to disk='c:\dbs.bak'
go
backup database db3
to disk='c:\dbs.bak'
go

--restore db2 to new db
restore database db2_2
from disk='c:\dbs.bak'
with file=2,replace,
move 'db2' to 'c:\db2_2.mdf',
move 'db2_log' to 'c:\db2_2.ldf'
go

--check to make sure we actually restore db2
exec db2_2..sp_executesql N'select * from db'
go

--clean up
drop database db2_2, db3, db2, db1
go

|||

Hi, sorry that i answer until today. Well this was what i done

backup database modelo_de_datos

to disk='d:\datos.bak'

with init

go

backup database inversiones

to disk='d:\datos.bak'

go

backup database innovasoft

to disk='d:\datos.bak'

go

--restore inversiones to new db

restore database db2_2

from disk='d:\datos.bak'

with file=2,replace,

move 'inversiones' to 'd:\database\db2_2.mdf',

move 'inversiones_log' to 'd:\database\db2_2.ldf'

go

There was no problem with the backup commands but with the restore part the result was:

Msg 3234, Level 16, State 2, Line 1

El archivo lógico 'inversiones' no es parte de la base de datos 'db2_2'. Use RESTORE FILELISTONLY para enumerar los nombres de los archivos lógicos.

Msg 3013, Level 16, State 1, Line 1

Fin anómalo de RESTORE DATABASE.

As you can see, the problem is exactly the same i gave when doing the task with the Management Studio

Thanks in advance

|||

I think the data & log filenames may not have the standard naming conventions. Test this out.

Code Snippet

create database db4
on
(
name = db4_data,
filename = 'c:\db4_data.mdf',
size = 10,
maxsize = 40,
filegrowth = 5
)
log on
(
name = db4log_log,
filename = 'c:\db4_log.ldf',
size = 10,
maxsize = 40,
filegrowth = 5
)

backup database db4
to disk='c:\dbs1.bak'
with init
go

RESTORE FILELISTONLY FROM DISK = 'c:\dbs1.bak'
WITH FILE=1;

restore database db4_2
from disk='c:\dbs1.bak'
with file=1,replace,
move 'db2' to 'c:\db4_2.mdf',
move 'db2_log' to 'c:\db4_2.ldf'
go

It throws similar error what you are getting now. Above post from oj, works correctly, all you need to do get the filenames from the backup with the below code and substitute in the backup command.

Code Snippet

RESTORE FILELISTONLY FROM DISK = 'c:\dbs.bak'
WITH FILE=2;

|||

You need to get the correct logical names for the invensiones database.

Do:

Code Snippet

restore filelistonly from disk='d:\datos.bak' with file=2

Once you have the correct logical names you then can move the data files to new location.

|||

Thank you guys for your useful help.

One more question. This works excellent using transact sql is it possible to do the same using Management Studio. Because the problem began when i tried to run the restore under Management Studio.,

Than you.,

|||

Sure. You would want to make sure the logical names are valid. In fact, you should see the same t-sql statement if you launch profiler and watch it.

Error restoring a database

Please that anybody help me.

I have a phisical device called 'respaldo.bak' inside this file there are three backups of three different databases, The first one is database called 'innovasoft' the second one is 'modelo_de_datos' and the last one is 'Inversiones'.

If I perform a restore of the first one there is no problem, but, when i try to restore the second o third database i get the following error:

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'COMPUTO'. (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.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

El archivo lógico 'INNOVASOFT_Datos' no es parte de la base de datos 'PROBANDO'. Use RESTORE FILELISTONLY para enumerar los nombres de los archivos lógicos.
Fin anómalo de RESTORE DATABASE. (Microsoft SQL Server, Error: 3234)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3234&LinkId=20476

BUTTONS:

OK

I have to say that i want to restore the database with a different name that's why in the error says 'PROBANDO' , i'm really concern about this problem because it means that i have no backups for my data. I used to use this procedure on sql server 2000 every single day without problems, but on sql server 2005 it doesn't work properly.

Please help me.

Thanks in advance.

Make sure you restore with a file destination plus move.

Here is a quick demo showing how it all works.

Code Snippet

--create 3 test dbs
use master
go
create database db1
go
create database db2
go
create database db3
go

--add dummy data to db2
exec db2..sp_executesql N'select db_name() [db] into db'
go

--backup dbs to disk
backup database db1
to disk='c:\dbs.bak'
with init
go
backup database db2
to disk='c:\dbs.bak'
go
backup database db3
to disk='c:\dbs.bak'
go

--restore db2 to new db
restore database db2_2
from disk='c:\dbs.bak'
with file=2,replace,
move 'db2' to 'c:\db2_2.mdf',
move 'db2_log' to 'c:\db2_2.ldf'
go

--check to make sure we actually restore db2
exec db2_2..sp_executesql N'select * from db'
go

--clean up
drop database db2_2, db3, db2, db1
go

|||

Hi, sorry that i answer until today. Well this was what i done

backup database modelo_de_datos

to disk='d:\datos.bak'

with init

go

backup database inversiones

to disk='d:\datos.bak'

go

backup database innovasoft

to disk='d:\datos.bak'

go

--restore inversiones to new db

restore database db2_2

from disk='d:\datos.bak'

with file=2,replace,

move 'inversiones' to 'd:\database\db2_2.mdf',

move 'inversiones_log' to 'd:\database\db2_2.ldf'

go

There was no problem with the backup commands but with the restore part the result was:

Msg 3234, Level 16, State 2, Line 1

El archivo lógico 'inversiones' no es parte de la base de datos 'db2_2'. Use RESTORE FILELISTONLY para enumerar los nombres de los archivos lógicos.

Msg 3013, Level 16, State 1, Line 1

Fin anómalo de RESTORE DATABASE.

As you can see, the problem is exactly the same i gave when doing the task with the Management Studio

Thanks in advance

|||

I think the data & log filenames may not have the standard naming conventions. Test this out.

Code Snippet

create database db4
on
(
name = db4_data,
filename = 'c:\db4_data.mdf',
size = 10,
maxsize = 40,
filegrowth = 5
)
log on
(
name = db4log_log,
filename = 'c:\db4_log.ldf',
size = 10,
maxsize = 40,
filegrowth = 5
)

backup database db4
to disk='c:\dbs1.bak'
with init
go

RESTORE FILELISTONLY FROM DISK = 'c:\dbs1.bak'
WITH FILE=1;

restore database db4_2
from disk='c:\dbs1.bak'
with file=1,replace,
move 'db2' to 'c:\db4_2.mdf',
move 'db2_log' to 'c:\db4_2.ldf'
go

It throws similar error what you are getting now. Above post from oj, works correctly, all you need to do get the filenames from the backup with the below code and substitute in the backup command.

Code Snippet

RESTORE FILELISTONLY FROM DISK = 'c:\dbs.bak'
WITH FILE=2;

|||

You need to get the correct logical names for the invensiones database.

Do:

Code Snippet

restore filelistonly from disk='d:\datos.bak' with file=2

Once you have the correct logical names you then can move the data files to new location.

|||

Thank you guys for your useful help.

One more question. This works excellent using transact sql is it possible to do the same using Management Studio. Because the problem began when i tried to run the restore under Management Studio.,

Than you.,

|||

Sure. You would want to make sure the logical names are valid. In fact, you should see the same t-sql statement if you launch profiler and watch it.

error querying SQL Server from web page

We have a problem with an asp file retrieving data from SQL Server v7 databa
ses in
our test environment. The asp file opens a recordset to get a list of active
project
databases from another database table. The asp file loops through the record
set and
builds two SQL strings using the loop's current project database name to ret
rieve
counts from each project database. The counts are displayed in a table on a
web page.
Each SQL string is used to open a recordset against the loop's current proje
ct database.
The asp file worked fine until recently. Two new project databases were crea
ted by
backing up an existing database and restoring it with the new project names.
There
is a flag that we turn on to activate a project database. As soon as the new
projects
were turned on, the asp file failed with OLE DB error 0x80040E21. The error
message
indicated that the failure occurred when trying to open the first of the two
recordsets
associated with the two SQL strings that were being created. When we turned
off the new
projects, then everything worked fine again.
We started a trace in SQL Server and found that the SQL string being execute
d changed
when we added one of the new projects.
The SQL string for the old projects looked like:
N'SELECT Sum(StudentCount) FROM server.projectdb.dbo.Sheets WHERE...
in which I have used 'server' to represent the SQL Server name and 'projectd
b' to
represent the project database name. The server name is always the same; onl
y the
project database name changes.
When one of the new projects was reached in the loop, the SQL string started
out the
same as above. But at some point the query changes to:
N'select * from SELECT Sum(StudentCount) FROM server.projectdb.dbo.Sheets WH
ERE...
The query tanks and so does the asp file. A developer searched for postings
relating to the
OLE DB error and saw something about NULLs. Since it was possible for the qu
ery to return NULL,
he tried changing the query in the asp file to:
N'SELECT Sum(isNull(StudentCount, 0)) FROM server.projectdb.dbo.Sheets WHERE
..
This fixed the problem as the query succeeded for all of the project databas
es, old and new.
The question is why this fixed the problem and how did the original query pi
ck up the additional
text "select * from". Because -
1. Upon checking the project databases, there were old project databases tha
t returned NULLs to the
queries. These project databases didn't have a problem when queried by the o
riginal asp file.
2. The new project databases were loaded with data so that they didn't retur
n NULLs. They still
caused the old asp file to fail.
3. In case the problem was with NULLs returned by an old project database pr
eceding or following
the new project database we varied which project databases were turned on. T
his didn't affect the
results at all as the asp file always failed when it came to a new project d
atabase.
So the isNull function somehow cleared up the problem even though the proble
m doesn't appear to
relate to whether NULLs are returned by the original queries.
Other things we tried that didn't help -
1. scripting the database install rather than backup\restore
2. logging on to the SQL Server box directly and installing the new database
rather than doing it
remotely with a SQL Server client
The web server and the database server are both running Windows 2000 Server
5.0.2195 SP4 Build 2195.
The SQL Server version is 7.00.624.
Can someone shed some light on this?That is a very interesting problem... I am curious, does the problem go
away if you do NOT use the 4 part name... SQL may do some strange things
when using the 4-part name because the data could be coming from a linked
server...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Pat Hurst" <anonymous@.discussions.microsoft.com> wrote in message
news:98D3FE8E-D08A-4C8D-8614-8261C877687A@.microsoft.com...
> We have a problem with an asp file retrieving data from SQL Server v7
databases in
> our test environment. The asp file opens a recordset to get a list of
active project
> databases from another database table. The asp file loops through the
recordset and
> builds two SQL strings using the loop's current project database name to
retrieve
> counts from each project database. The counts are displayed in a table on
a web page.
> Each SQL string is used to open a recordset against the loop's current
project database.
> The asp file worked fine until recently. Two new project databases were
created by
> backing up an existing database and restoring it with the new project
names. There
> is a flag that we turn on to activate a project database. As soon as the
new projects
> were turned on, the asp file failed with OLE DB error 0x80040E21. The
error message
> indicated that the failure occurred when trying to open the first of the
two recordsets
> associated with the two SQL strings that were being created. When we
turned off the new
> projects, then everything worked fine again.
> We started a trace in SQL Server and found that the SQL string being
executed changed
> when we added one of the new projects.
> The SQL string for the old projects looked like:
> N'SELECT Sum(StudentCount) FROM server.projectdb.dbo.Sheets WHERE...
> in which I have used 'server' to represent the SQL Server name and
'projectdb' to
> represent the project database name. The server name is always the same;
only the
> project database name changes.
> When one of the new projects was reached in the loop, the SQL string
started out the
> same as above. But at some point the query changes to:
> N'select * from SELECT Sum(StudentCount) FROM server.projectdb.dbo.Sheets
WHERE...
> The query tanks and so does the asp file. A developer searched for
postings relating to the
> OLE DB error and saw something about NULLs. Since it was possible for the
query to return NULL,
> he tried changing the query in the asp file to:
> N'SELECT Sum(isNull(StudentCount, 0)) FROM server.projectdb.dbo.Sheets
WHERE...
> This fixed the problem as the query succeeded for all of the project
databases, old and new.
> The question is why this fixed the problem and how did the original query
pick up the additional
> text "select * from". Because -
> 1. Upon checking the project databases, there were old project databases
that returned NULLs to the
> queries. These project databases didn't have a problem when queried by the
original asp file.
> 2. The new project databases were loaded with data so that they didn't
return NULLs. They still
> caused the old asp file to fail.
> 3. In case the problem was with NULLs returned by an old project database
preceding or following
> the new project database we varied which project databases were turned on.
This didn't affect the
> results at all as the asp file always failed when it came to a new project
database.
> So the isNull function somehow cleared up the problem even though the
problem doesn't appear to
> relate to whether NULLs are returned by the original queries.
> Other things we tried that didn't help -
> 1. scripting the database install rather than backup\restore
> 2. logging on to the SQL Server box directly and installing the new
database rather than doing it
> remotely with a SQL Server client
> The web server and the database server are both running Windows 2000
Server 5.0.2195 SP4 Build 2195.
> The SQL Server version is 7.00.624.
> Can someone shed some light on this?|||May I congratulate you on your prescience. I removed the server name from th
e 2 queries in the asp file and omitted the IsNull function from both. The w
eb page came up without a hitch.
It appears that the code specifies the server name earlier in setting up a c
onnection object. This connection is used to open the 2 query recordsets so
I'm not sure why the server name was used in specifying the table in the que
ries. The developer who wro
te it is out of the office currently so I can't ask him.
Still I can't help wondering why only the two new databases created a proble
m.|||After tracking down a developer with access to the production system, I was
able to find out that production uses 2 database servers. The primary holds
the system's database and some of the project databases. The other holds a f
ew of the project databases
. The two are linked in SQL Server. So that's the rationale for four part na
mes.
Our test environment can only afford one database server so a four part name
is unnecessary as the projects are all on the server that the web page conn
ects to.

error querying SQL Server from web page

We have a problem with an asp file retrieving data from SQL Server v7 databases i
our test environment. The asp file opens a recordset to get a list of active project
databases from another database table. The asp file loops through the recordset and
builds two SQL strings using the loop's current project database name to retrieve
counts from each project database. The counts are displayed in a table on a web page.
Each SQL string is used to open a recordset against the loop's current project database
The asp file worked fine until recently. Two new project databases were created b
backing up an existing database and restoring it with the new project names. Ther
is a flag that we turn on to activate a project database. As soon as the new projects
were turned on, the asp file failed with OLE DB error 0x80040E21. The error message
indicated that the failure occurred when trying to open the first of the two recordsets
associated with the two SQL strings that were being created. When we turned off the new
projects, then everything worked fine again
We started a trace in SQL Server and found that the SQL string being executed changed
when we added one of the new projects
The SQL string for the old projects looked like
N'SELECT Sum(StudentCount) FROM server.projectdb.dbo.Sheets WHERE..
in which I have used 'server' to represent the SQL Server name and 'projectdb' to
represent the project database name. The server name is always the same; only the
project database name changes
When one of the new projects was reached in the loop, the SQL string started out th
same as above. But at some point the query changes to
N'select * from SELECT Sum(StudentCount) FROM server.projectdb.dbo.Sheets WHERE..
The query tanks and so does the asp file. A developer searched for postings relating to the
OLE DB error and saw something about NULLs. Since it was possible for the query to return NULL
he tried changing the query in the asp file to
N'SELECT Sum(isNull(StudentCount, 0)) FROM server.projectdb.dbo.Sheets WHERE..
This fixed the problem as the query succeeded for all of the project databases, old and new
The question is why this fixed the problem and how did the original query pick up the additiona
text "select * from". Because
1. Upon checking the project databases, there were old project databases that returned NULLs to the
queries. These project databases didn't have a problem when queried by the original asp file
2. The new project databases were loaded with data so that they didn't return NULLs. They still
caused the old asp file to fail
3. In case the problem was with NULLs returned by an old project database preceding or following
the new project database we varied which project databases were turned on. This didn't affect the
results at all as the asp file always failed when it came to a new project database
So the isNull function somehow cleared up the problem even though the problem doesn't appear to
relate to whether NULLs are returned by the original queries
Other things we tried that didn't help
1. scripting the database install rather than backup\restor
2. logging on to the SQL Server box directly and installing the new database rather than doing it
remotely with a SQL Server clien
The web server and the database server are both running Windows 2000 Server 5.0.2195 SP4 Build 2195
The SQL Server version is 7.00.624
Can someone shed some light on this?That is a very interesting problem... I am curious, does the problem go
away if you do NOT use the 4 part name... SQL may do some strange things
when using the 4-part name because the data could be coming from a linked
server...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Pat Hurst" <anonymous@.discussions.microsoft.com> wrote in message
news:98D3FE8E-D08A-4C8D-8614-8261C877687A@.microsoft.com...
> We have a problem with an asp file retrieving data from SQL Server v7
databases in
> our test environment. The asp file opens a recordset to get a list of
active project
> databases from another database table. The asp file loops through the
recordset and
> builds two SQL strings using the loop's current project database name to
retrieve
> counts from each project database. The counts are displayed in a table on
a web page.
> Each SQL string is used to open a recordset against the loop's current
project database.
> The asp file worked fine until recently. Two new project databases were
created by
> backing up an existing database and restoring it with the new project
names. There
> is a flag that we turn on to activate a project database. As soon as the
new projects
> were turned on, the asp file failed with OLE DB error 0x80040E21. The
error message
> indicated that the failure occurred when trying to open the first of the
two recordsets
> associated with the two SQL strings that were being created. When we
turned off the new
> projects, then everything worked fine again.
> We started a trace in SQL Server and found that the SQL string being
executed changed
> when we added one of the new projects.
> The SQL string for the old projects looked like:
> N'SELECT Sum(StudentCount) FROM server.projectdb.dbo.Sheets WHERE...
> in which I have used 'server' to represent the SQL Server name and
'projectdb' to
> represent the project database name. The server name is always the same;
only the
> project database name changes.
> When one of the new projects was reached in the loop, the SQL string
started out the
> same as above. But at some point the query changes to:
> N'select * from SELECT Sum(StudentCount) FROM server.projectdb.dbo.Sheets
WHERE...
> The query tanks and so does the asp file. A developer searched for
postings relating to the
> OLE DB error and saw something about NULLs. Since it was possible for the
query to return NULL,
> he tried changing the query in the asp file to:
> N'SELECT Sum(isNull(StudentCount, 0)) FROM server.projectdb.dbo.Sheets
WHERE...
> This fixed the problem as the query succeeded for all of the project
databases, old and new.
> The question is why this fixed the problem and how did the original query
pick up the additional
> text "select * from". Because -
> 1. Upon checking the project databases, there were old project databases
that returned NULLs to the
> queries. These project databases didn't have a problem when queried by the
original asp file.
> 2. The new project databases were loaded with data so that they didn't
return NULLs. They still
> caused the old asp file to fail.
> 3. In case the problem was with NULLs returned by an old project database
preceding or following
> the new project database we varied which project databases were turned on.
This didn't affect the
> results at all as the asp file always failed when it came to a new project
database.
> So the isNull function somehow cleared up the problem even though the
problem doesn't appear to
> relate to whether NULLs are returned by the original queries.
> Other things we tried that didn't help -
> 1. scripting the database install rather than backup\restore
> 2. logging on to the SQL Server box directly and installing the new
database rather than doing it
> remotely with a SQL Server client
> The web server and the database server are both running Windows 2000
Server 5.0.2195 SP4 Build 2195.
> The SQL Server version is 7.00.624.
> Can someone shed some light on this?|||May I congratulate you on your prescience. I removed the server name from the 2 queries in the asp file and omitted the IsNull function from both. The web page came up without a hitch
It appears that the code specifies the server name earlier in setting up a connection object. This connection is used to open the 2 query recordsets so I'm not sure why the server name was used in specifying the table in the queries. The developer who wrote it is out of the office currently so I can't ask him
Still I can't help wondering why only the two new databases created a problem.

Error publishing databases with not-server's default collation

Hello.
While trying to publish any database that uses a collation different from
server's default, I get this error in the step after selecting the articles
to be published:
Error 21423: You do not have sufficient privileges to view the publication
information. Your administrator may need to fix the PAL role on the publisher
for publication 'database_name'.
I've being trying to track down this error for a while now, and I've just
noticed it's related to the collation...
Any ideas about this?
Not sure if it's relevant here, but when I had a case-sensitive server
collation I noticed that passwords of logins became case sensitive on
authentication. I wonder if there is somehow a login/user case sensitivity
issue at work here also? Are the 2 collations the same in this regard or
different (a mixture of _CS and _CI)? Please can you post up the entire
collation names for the circumstance that doesn't work.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||The collations are:
Chinese_Taiwan_Stroke_BIN for the dbs that fails
and:
SQL_Latin1_General_CP1_CI_AS for the dbs that are sucessfuly published.
In any case, the passwords for the accounts are all lowercase...
Thanks for your support,
RODOLFO
"Paul Ibison" wrote:

> Not sure if it's relevant here, but when I had a case-sensitive server
> collation I noticed that passwords of logins became case sensitive on
> authentication. I wonder if there is somehow a login/user case sensitivity
> issue at work here also? Are the 2 collations the same in this regard or
> different (a mixture of _CS and _CI)? Please can you post up the entire
> collation names for the circumstance that doesn't work.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

error opening table in Enterprise MGR

I am getting an error message when I try to open (to view the rows of) any
table in any of the databases on a SQL 7 server from the server where the
databases actually reside- it says:
"An unexpected error happened during this operation. [Query] - Query
Designer encountered a query error: Unspecified error
This error is new; I am able to query and get results from the tables in
query analyzer and I am able to view the tables through Enterprise Manager
when I link to these databases from another computer. I restarted SQL server
without any luck. Do you think it needs to be reinstalled?
Check the following article which addresses the issue:
FIX: Enterprise Manager Returns Unexpected Error on Table or
View
http://support.microsoft.com/?id=273472
-Sue
On Wed, 26 Jan 2005 07:47:03 -0800, "PatW"
<PatW@.discussions.microsoft.com> wrote:

>I am getting an error message when I try to open (to view the rows of) any
>table in any of the databases on a SQL 7 server from the server where the
>databases actually reside- it says:
>"An unexpected error happened during this operation. [Query] - Query
>Designer encountered a query error: Unspecified error
>This error is new; I am able to query and get results from the tables in
>query analyzer and I am able to view the tables through Enterprise Manager
>when I link to these databases from another computer. I restarted SQL server
>without any luck. Do you think it needs to be reinstalled?
sql

error opening table in Enterprise MGR

I am getting an error message when I try to open (to view the rows of) any
table in any of the databases on a SQL 7 server from the server where the
databases actually reside- it says:
"An unexpected error happened during this operation. [Query] - Query
Designer encountered a query error: Unspecified error
This error is new; I am able to query and get results from the tables in
query analyzer and I am able to view the tables through Enterprise Manager
when I link to these databases from another computer. I restarted SQL serve
r
without any luck. Do you think it needs to be reinstalled?Check the following article which addresses the issue:
FIX: Enterprise Manager Returns Unexpected Error on Table or
View
http://support.microsoft.com/?id=273472
-Sue
On Wed, 26 Jan 2005 07:47:03 -0800, "PatW"
<PatW@.discussions.microsoft.com> wrote:

>I am getting an error message when I try to open (to view the rows of) any
>table in any of the databases on a SQL 7 server from the server where the
>databases actually reside- it says:
>"An unexpected error happened during this operation. [Query] - Query
>Designer encountered a query error: Unspecified error
>This error is new; I am able to query and get results from the tables in
>query analyzer and I am able to view the tables through Enterprise Manager
>when I link to these databases from another computer. I restarted SQL serv
er
>without any luck. Do you think it needs to be reinstalled?

error opening table in Enterprise MGR

I am getting an error message when I try to open (to view the rows of) any
table in any of the databases on a SQL 7 server from the server where the
databases actually reside- it says:
"An unexpected error happened during this operation. [Query] - Query
Designer encountered a query error: Unspecified error
This error is new; I am able to query and get results from the tables in
query analyzer and I am able to view the tables through Enterprise Manager
when I link to these databases from another computer. I restarted SQL server
without any luck. Do you think it needs to be reinstalled?Check the following article which addresses the issue:
FIX: Enterprise Manager Returns Unexpected Error on Table or
View
http://support.microsoft.com/?id=273472
-Sue
On Wed, 26 Jan 2005 07:47:03 -0800, "PatW"
<PatW@.discussions.microsoft.com> wrote:
>I am getting an error message when I try to open (to view the rows of) any
>table in any of the databases on a SQL 7 server from the server where the
>databases actually reside- it says:
>"An unexpected error happened during this operation. [Query] - Query
>Designer encountered a query error: Unspecified error
>This error is new; I am able to query and get results from the tables in
>query analyzer and I am able to view the tables through Enterprise Manager
>when I link to these databases from another computer. I restarted SQL server
>without any luck. Do you think it needs to be reinstalled?

error Msg 156 and msg 170

Hi!
I am trying to loop through all the databases using cursor.
Here is my stored proc
if exists (select [id] from master..sysobjects where [id] = OBJECT_ID
('master..temp_Assignments_file_count '))
DROP TABLE temp_Assignments_file_count
declare @.sql nvarchar(4000)
declare @.db varchar(300)
set @.db = 'master'
declare cDB cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.crdate >= '2007-10-01' and sdb.name like 'client_%'
ORDER BY name
CREATE TABLE temp_Assignments_file_count([Server Name]
nvarchar(40),
[Database Name]
nvarchar(100),
[Title] nvarchar(100),
[File Count] int,
[File Size (MB)] decimal(10,4),
)
open cDB
FETCH NEXT FROM cDB INTO @.db
while (@.@.fetch_status = 0)
begin
SET @.sql = 'SELECT @.@.SERVERNAME as ''[Server
Name]'', ' +
'''' + @.db + '''' + '
as ''[Database Name]'', ' +
'max(b.title) as ''[Title]'',' +
'count(*) as ''[File Count]'',' +
'round(cast(sum(length) as decimal)/1048576/1024,10) as
''[File Size]''' +
'from ' + @.db + '.dbo.filo_files a join (select b.id from ' +
@.db + 'dbo.filo_Matters b) on a.matterkey = b.id' +
'where a.id in (select distinct documentkey from ' + @.db +
'.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
'.dbo.filo_assignments' +
'where lastprocesskey in (select id from ' + @.db +
'.dbo.filo_processlog where task = ''Create Assignments'' and
starttime >= ''10/01/2007'' AND starttime <= ''09/30/2007'')))' +
'and a.id not in (select distinct documentkey from ' + @.db +
'.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
'.dbo.filo_assignments where lastprocesskey in' +
'(select id from ' + @.db + '.dbo.filo_processlog where task = ''Create Assignments'' and starttime < ''10/01/2007'')))'
INSERT temp_Assignments_file_count
EXEC sp_executesql @.sql
fetch cDB into @.db
end
close cDB
deallocate cDB
select * from temp_Assignments_file_count
I am getting the following error messages:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
(0 row(s) affected)
any help would be appreciated.
TGDo:
PRINT @.sql
before you try to execute what you have in the variable and you will find a lot of problems with the
query that you built. Based on that you can debug your code so it produces a valid query.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<jtammyg@.gmail.com> wrote in message news:1192645043.161544.136800@.v29g2000prd.googlegroups.com...
> Hi!
> I am trying to loop through all the databases using cursor.
> Here is my stored proc
> if exists (select [id] from master..sysobjects where [id] = OBJECT_ID
> ('master..temp_Assignments_file_count '))
> DROP TABLE temp_Assignments_file_count
>
> declare @.sql nvarchar(4000)
> declare @.db varchar(300)
>
> set @.db = 'master'
> declare cDB cursor for
> SELECT name from master..sysdatabases sdb
> WHERE sdb.crdate >= '2007-10-01' and sdb.name like 'client_%'
> ORDER BY name
>
> CREATE TABLE temp_Assignments_file_count([Server Name]
> nvarchar(40),
> [Database Name]
> nvarchar(100),
> [Title] nvarchar(100),
> [File Count] int,
> [File Size (MB)] decimal(10,4),
> )
>
> open cDB
> FETCH NEXT FROM cDB INTO @.db
> while (@.@.fetch_status = 0)
> begin
> SET @.sql = 'SELECT @.@.SERVERNAME as ''[Server
> Name]'', ' +
> '''' + @.db + '''' + '
> as ''[Database Name]'', ' +
> 'max(b.title) as ''[Title]'',' +
> 'count(*) as ''[File Count]'',' +
> 'round(cast(sum(length) as decimal)/1048576/1024,10) as
> ''[File Size]''' +
> 'from ' + @.db + '.dbo.filo_files a join (select b.id from ' +
> @.db + 'dbo.filo_Matters b) on a.matterkey = b.id' +
> 'where a.id in (select distinct documentkey from ' + @.db +
> '.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
> '.dbo.filo_assignments' +
> 'where lastprocesskey in (select id from ' + @.db +
> '.dbo.filo_processlog where task = ''Create Assignments'' and
> starttime >= ''10/01/2007'' AND starttime <= ''09/30/2007'')))' +
> 'and a.id not in (select distinct documentkey from ' + @.db +
> '.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
> '.dbo.filo_assignments where lastprocesskey in' +
> '(select id from ' + @.db + '.dbo.filo_processlog where task => ''Create Assignments'' and starttime < ''10/01/2007'')))'
>
> INSERT temp_Assignments_file_count
> EXEC sp_executesql @.sql
>
> fetch cDB into @.db
> end
> close cDB
> deallocate cDB
>
> select * from temp_Assignments_file_count
>
>
> I am getting the following error messages:
>
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> (0 row(s) affected)
>
> any help would be appreciated.
> TG
>

Error message when runnng Integrity Check in Maintenance Job

Hi,
I have configured my Database maintenance plans which include Integrity checks. On several databases I get the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode. Is this something I should worry about? I also run a DBCC CheckDB job and everything comes back fine. It looks as though their are
logins still to the database that make this job fail. I can turn off attempt to repair and don't get the errors.
Any thoughts or suggestions on this?
Thanks,
Warren
This is due to selecting the option on your integrity checks
for "attempt to repair any minor problems".
Attempting to repair any database requires it to be in
single user mode and the databases won't be able to always
be put into single user mode. If you have DBCC errors, it
isn't the best idea to have a maintenance plan set to
automatically repair the database. It's better to analyze
the situation further to make sure that hardware problems or
other issues aren't contributing to the database problems.
-Sue
On Wed, 7 Apr 2004 07:16:02 -0700, Warren
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>I have configured my Database maintenance plans which include Integrity checks. On several databases I get the error:
>[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode. Is this something I should worry about? I also run a DBCC CheckDB job and everything comes back fine. It looks as though their are
logins still to the database that make this job fail. I can turn off attempt to repair and don't get the errors.
>Any thoughts or suggestions on this?
>Thanks,
>Warren
|||Thanks Sue! I am not receiving any dbcc checkdb errors.
|||Then you're fine Warren. It's better to just monitor the
results of the DBCCs yourself so you would just want to
remove the option to repair on your integrity checks.
-Sue
On Wed, 7 Apr 2004 11:21:12 -0700, Warren
<anonymous@.discussions.microsoft.com> wrote:

>Thanks Sue! I am not receiving any dbcc checkdb errors.
|||You shouldn't automatically repair errors. You should always do root-cause
analysis of any errors returned by the integrity checks as they're usually
an indication that your hardware has a problem.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Warren" <anonymous@.discussions.microsoft.com> wrote in message
news:68146DFC-BB28-4D0F-B4D1-BA9358EA7A1F@.microsoft.com...
> Hi,
> I have configured my Database maintenance plans which include Integrity
checks. On several databases I get the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode. Is this something I
should worry about? I also run a DBCC CheckDB job and everything comes back
fine. It looks as though their are logins still to the database that make
this job fail. I can turn off attempt to repair and don't get the errors.
> Any thoughts or suggestions on this?
> Thanks,
> Warren
|||I have been having this same problem. Thanks for this very helpful advice. It is good to see that the experts are willing to help out us users!
Byron Vriend, R.P.F.
GIS Analyst
Weldwood of Canada
Hinton Division
Hinton, AB, Canada
"Paul S Randal [MS]" wrote:

> You shouldn't automatically repair errors. You should always do root-cause
> analysis of any errors returned by the integrity checks as they're usually
> an indication that your hardware has a problem.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Warren" <anonymous@.discussions.microsoft.com> wrote in message
> news:68146DFC-BB28-4D0F-B4D1-BA9358EA7A1F@.microsoft.com...
> checks. On several databases I get the error:
> processed. Database needs to be in single user mode. Is this something I
> should worry about? I also run a DBCC CheckDB job and everything comes back
> fine. It looks as though their are logins still to the database that make
> this job fail. I can turn off attempt to repair and don't get the errors.
>
>

Error message when runnng Integrity Check in Maintenance Job

Hi,
I have configured my Database maintenance plans which include Integrity chec
ks. On several databases I get the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not processed. Database needs to be in single user mode. Is this something
I should worry about? I also run a DBCC CheckDB job and everything comes ba
ck fine. It looks as though their are
logins still to the database that make this job fail. I can turn off attemp
t to repair and don't get the errors.
Any thoughts or suggestions on this?
Thanks,
WarrenThis is due to selecting the option on your integrity checks
for "attempt to repair any minor problems".
Attempting to repair any database requires it to be in
single user mode and the databases won't be able to always
be put into single user mode. If you have DBCC errors, it
isn't the best idea to have a maintenance plan set to
automatically repair the database. It's better to analyze
the situation further to make sure that hardware problems or
other issues aren't contributing to the database problems.
-Sue
On Wed, 7 Apr 2004 07:16:02 -0700, Warren
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>I have configured my Database maintenance plans which include Integrity che
cks. On several databases I get the error:
>[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not pro
cessed. Database needs to be in single user mode. Is this something I should worry
about? I also run a DBCC CheckDB job and everything comes back fine. It looks as t
hough their are
logins still to the database that make this job fail. I can turn off attempt to repair and
don't get the errors.
>Any thoughts or suggestions on this?
>Thanks,
>Warren|||Thanks Sue! I am not receiving any dbcc checkdb errors.|||Then you're fine Warren. It's better to just monitor the
results of the DBCCs yourself so you would just want to
remove the option to repair on your integrity checks.
-Sue
On Wed, 7 Apr 2004 11:21:12 -0700, Warren
<anonymous@.discussions.microsoft.com> wrote:

>Thanks Sue! I am not receiving any dbcc checkdb errors.|||You shouldn't automatically repair errors. You should always do root-cause
analysis of any errors returned by the integrity checks as they're usually
an indication that your hardware has a problem.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Warren" <anonymous@.discussions.microsoft.com> wrote in message
news:68146DFC-BB28-4D0F-B4D1-BA9358EA7A1F@.microsoft.com...
> Hi,
> I have configured my Database maintenance plans which include Integrity
checks. On several databases I get the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not[/c
olor]
processed. Database needs to be in single user mode. Is this something I
should worry about? I also run a DBCC CheckDB job and everything comes back
fine. It looks as though their are logins still to the database that make
this job fail. I can turn off attempt to repair and don't get the errors.
> Any thoughts or suggestions on this?
> Thanks,
> Warren|||I have been having this same problem. Thanks for this very helpful advice.
It is good to see that the experts are willing to help out us users!
Byron Vriend, R.P.F.
GIS Analyst
Weldwood of Canada
Hinton Division
Hinton, AB, Canada
"Paul S Randal [MS]" wrote:

> You shouldn't automatically repair errors. You should always do root-cause
> analysis of any errors returned by the integrity checks as they're usually
> an indication that your hardware has a problem.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Warren" <anonymous@.discussions.microsoft.com> wrote in message
> news:68146DFC-BB28-4D0F-B4D1-BA9358EA7A1F@.microsoft.com...
> checks. On several databases I get the error:
> processed. Database needs to be in single user mode. Is this something I
> should worry about? I also run a DBCC CheckDB job and everything comes ba
ck
> fine. It looks as though their are logins still to the database that make
> this job fail. I can turn off attempt to repair and don't get the errors.
>
>

Error message when runnng Integrity Check in Maintenance Job

Hi
I have configured my Database maintenance plans which include Integrity checks. On several databases I get the error
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode. Is this something I should worry about? I also run a DBCC CheckDB job and everything comes back fine. It looks as though their are logins still to the database that make this job fail. I can turn off attempt to repair and don't get the errors.
Any thoughts or suggestions on this
Thanks
WarrenThis is due to selecting the option on your integrity checks
for "attempt to repair any minor problems".
Attempting to repair any database requires it to be in
single user mode and the databases won't be able to always
be put into single user mode. If you have DBCC errors, it
isn't the best idea to have a maintenance plan set to
automatically repair the database. It's better to analyze
the situation further to make sure that hardware problems or
other issues aren't contributing to the database problems.
-Sue
On Wed, 7 Apr 2004 07:16:02 -0700, Warren
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>I have configured my Database maintenance plans which include Integrity checks. On several databases I get the error:
>[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode. Is this something I should worry about? I also run a DBCC CheckDB job and everything comes back fine. It looks as though their are logins still to the database that make this job fail. I can turn off attempt to repair and don't get the errors.
>Any thoughts or suggestions on this?
>Thanks,
>Warren|||Then you're fine Warren. It's better to just monitor the
results of the DBCCs yourself so you would just want to
remove the option to repair on your integrity checks.
-Sue
On Wed, 7 Apr 2004 11:21:12 -0700, Warren
<anonymous@.discussions.microsoft.com> wrote:
>Thanks Sue! I am not receiving any dbcc checkdb errors.|||You shouldn't automatically repair errors. You should always do root-cause
analysis of any errors returned by the integrity checks as they're usually
an indication that your hardware has a problem.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Warren" <anonymous@.discussions.microsoft.com> wrote in message
news:68146DFC-BB28-4D0F-B4D1-BA9358EA7A1F@.microsoft.com...
> Hi,
> I have configured my Database maintenance plans which include Integrity
checks. On several databases I get the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode. Is this something I
should worry about? I also run a DBCC CheckDB job and everything comes back
fine. It looks as though their are logins still to the database that make
this job fail. I can turn off attempt to repair and don't get the errors.
> Any thoughts or suggestions on this?
> Thanks,
> Warren|||I have been having this same problem. Thanks for this very helpful advice. It is good to see that the experts are willing to help out us users!
Byron Vriend, R.P.F.
GIS Analyst
Weldwood of Canada
Hinton Division
Hinton, AB, Canada
"Paul S Randal [MS]" wrote:
> You shouldn't automatically repair errors. You should always do root-cause
> analysis of any errors returned by the integrity checks as they're usually
> an indication that your hardware has a problem.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Warren" <anonymous@.discussions.microsoft.com> wrote in message
> news:68146DFC-BB28-4D0F-B4D1-BA9358EA7A1F@.microsoft.com...
> > Hi,
> >
> > I have configured my Database maintenance plans which include Integrity
> checks. On several databases I get the error:
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode. Is this something I
> should worry about? I also run a DBCC CheckDB job and everything comes back
> fine. It looks as though their are logins still to the database that make
> this job fail. I can turn off attempt to repair and don't get the errors.
> >
> > Any thoughts or suggestions on this?
> >
> > Thanks,
> >
> > Warren
>
>

Error Message

I have a Maintenance Plan set to run every night that Optimizes the databases, runs an Integrity check and then backs them up. Each job is staggered to run 1 hour apart. It has been running fine for months and now I get the following intermittent error during the "Reorganize data and index pages" part:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.

I know what database it is failing on but I run dbcc checkdb and find no errors. From what I have found on the web this error pertains to an index problem. The only indices on this database reside in the system tables. I have dropped the database and restored it but no help there. Any ideas?

Thanks
DonDo you have indexes on views or computed columns ? If so,
you will need to run dbcc dbreindex or indexdefrag. This occurs because sql server agent does not (by default) set arithabort and quoted_identifier on. So, you will need to create a job that sets these:

set arithabort on
set quoted_identifier on
dbcc checktable(tablename)|||I don't have any indexes on views or any user tables, just the default system tables with a fresh database. I'll look into the arithabort setting.
Thanks
Don|||Have you tried the dbcc commands ?