Showing posts with label create. Show all posts
Showing posts with label create. Show all posts

Monday, March 26, 2012

Error 823 with msdb

When I try to create a new Maintenance Plan or revise an existing one I
get the following error:
Error: 823, Severity: 24, State: 2
Microsoft SQL-DMO (ODBC SQL State:HY000)
Error 823: I/O error (torn page) detected during read at offsett
0x000000000f0000 in file 'd:\sqldata\MSSQL\data\msdbdata.mdf'.
There are no known disk problems that I can detect. The existing
Maintenance Plans run fine. I do not have a backup of msdb prior to when
this problem started (I have no idea of when it started, it only appears
when I try to modify the maintenance plan).
I can easily recreate the maintenance plans but I need to fix the problem
with msdb first. Can msdb be repaired, can I empty it to start fresh, can I
use the msdb from an original install'
Any suggestions are greatly appreciated.
Thanks
Russ StevensI just tried to repair msdb
DBCC CHECKDB ('msdb', REPAIR_ALLOW_DATA_LOSS)
It fixed 6 errors - I now have a bigger mess <g> - am getting the following
error (and other strange errors) when I try to modify a Maintenance Plan;
Error 170: Line 1: Incorrect syntax near ','.
I need a clean working msdb - appreciate any help.
Thanks
Russ Stevens|||Anytime you run a dbcc checkdb with repair_allow_data_loss you risk data
integrity. there is no way to know what kind of data is lost etc. In
your case, you have to recreate the maintenance plan, which should not
be a big issue. In fact if that is the only problem that you get after
the repair, you should be happy.
Alternatively you can always restore from backup
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Russell Stevens wrote:
> I just tried to repair msdb
> DBCC CHECKDB ('msdb', REPAIR_ALLOW_DATA_LOSS)
> It fixed 6 errors - I now have a bigger mess <g> - am getting the followin
g
> error (and other strange errors) when I try to modify a Maintenance Plan;
> Error 170: Line 1: Incorrect syntax near ','.
> I need a clean working msdb - appreciate any help.
> Thanks
> Russ Stevens
>|||Yih-Yoon,
<<In your case, you have to recreate the maintenance plan, which should not
be a big issue. In fact if that is the only problem that you get after
the repair, you should be happy.>>
No - I am not happy <g> - I couldn't create a maintenance plan before the
repair or after the repair. I can't add a plan or revise a plan. Until msdb
is fixed, I am dead in the water (the existing plans still run fine). And I
can't restore from an msdb backup as I evidently don't have one that was
made before the problem started (I only keep a few weeks).
Looks like I need to rebuild the master and start over. It just seems that
MS should have a way of rebuilding just msdb without having to rebuild the
master.
Thanks
Russ Stevens|||There is a way to rebuild msdb from scratch. May not be a supported one.
Try this on a test server before doing this on a production server
Start SQL Server from the command prompt recover just the master
database, such as sqlservr.exe -c -f -T3608 -T4022
you can then connect to sql server from query analzyer (-f means yo
uwill only get single connection), then you can delete MSDB database.
There is a script in mssql\install folder called instmsdb.sql. This is
the script to recreate MSDB database.
I have to warn you this is probably not a supported solution by
Microsoft. I am not responsible for any damage But if you are
thinking of rebuilding master database anyway, why not give this a try?
the idea is test it thoroughly and make sure it test in your development
server.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Russell Stevens wrote:
> Yih-Yoon,
> <<In your case, you have to recreate the maintenance plan, which should no
t
> be a big issue. In fact if that is the only problem that you get after
> the repair, you should be happy.>>
> No - I am not happy <g> - I couldn't create a maintenance plan before the
> repair or after the repair. I can't add a plan or revise a plan. Until msd
b
> is fixed, I am dead in the water (the existing plans still run fine). And
I
> can't restore from an msdb backup as I evidently don't have one that was
> made before the problem started (I only keep a few weeks).
> Looks like I need to rebuild the master and start over. It just seems that
> MS should have a way of rebuilding just msdb without having to rebuild the
> master.
> Thanks
> Russ Stevens
>|||Yih-Yoon,
I suspect the instmsdb.sql script is also used by the rebuildm.exe
utility. I didn't want to chance it. I rebuilt everything, am back on line
(after an all nighter).
Needless to say I was quite amazed that after rebuilding, my sa password
had been set to a blank - no message, no warning, I was just open to the
world. How can Microsoft miss something like that?
Thanks for your help.
Russ Stevens|||A rebuild of Master will recreate the structure and base data, but not
repopulate existing data. I would have tried the other solution first.
Drop and rerun the instmsdb.sql script. Then the risk was limited to just
msdb, which contains far less useful information than master.
Also, a good installation recommendation is to always set a responsible
backup strategy for your system databases. School of hard knocks works, but
it is an expensive lesson.
Sincerely,
Anthony Thomas
"Russell Stevens" <rastevens@.aol.com> wrote in message
news:%23tlm6PWAFHA.3504@.TK2MSFTNGP12.phx.gbl...
Yih-Yoon,
I suspect the instmsdb.sql script is also used by the rebuildm.exe
utility. I didn't want to chance it. I rebuilt everything, am back on line
(after an all nighter).
Needless to say I was quite amazed that after rebuilding, my sa password
had been set to a blank - no message, no warning, I was just open to the
world. How can Microsoft miss something like that?
Thanks for your help.
Russ Stevens

Error 823 with msdb

When I try to create a new Maintenance Plan or revise an existing one I
get the following error:
Error: 823, Severity: 24, State: 2
Microsoft SQL-DMO (ODBC SQL State:HY000)
Error 823: I/O error (torn page) detected during read at offsett
0x000000000f0000 in file 'd:\sqldata\MSSQL\data\msdbdata.mdf'.
There are no known disk problems that I can detect. The existing
Maintenance Plans run fine. I do not have a backup of msdb prior to when
this problem started (I have no idea of when it started, it only appears
when I try to modify the maintenance plan).
I can easily recreate the maintenance plans but I need to fix the problem
with msdb first. Can msdb be repaired, can I empty it to start fresh, can I
use the msdb from an original install?
Any suggestions are greatly appreciated.
Thanks
Russ Stevens
I just tried to repair msdb
DBCC CHECKDB ('msdb', REPAIR_ALLOW_DATA_LOSS)
It fixed 6 errors - I now have a bigger mess <g> - am getting the following
error (and other strange errors) when I try to modify a Maintenance Plan;
Error 170: Line 1: Incorrect syntax near ','.
I need a clean working msdb - appreciate any help.
Thanks
Russ Stevens
|||Anytime you run a dbcc checkdb with repair_allow_data_loss you risk data
integrity. there is no way to know what kind of data is lost etc. In
your case, you have to recreate the maintenance plan, which should not
be a big issue. In fact if that is the only problem that you get after
the repair, you should be happy.
Alternatively you can always restore from backup
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Russell Stevens wrote:
> I just tried to repair msdb
> DBCC CHECKDB ('msdb', REPAIR_ALLOW_DATA_LOSS)
> It fixed 6 errors - I now have a bigger mess <g> - am getting the following
> error (and other strange errors) when I try to modify a Maintenance Plan;
> Error 170: Line 1: Incorrect syntax near ','.
> I need a clean working msdb - appreciate any help.
> Thanks
> Russ Stevens
>
|||Yih-Yoon,
<<In your case, you have to recreate the maintenance plan, which should not
be a big issue. In fact if that is the only problem that you get after
the repair, you should be happy.>>
No - I am not happy <g> - I couldn't create a maintenance plan before the
repair or after the repair. I can't add a plan or revise a plan. Until msdb
is fixed, I am dead in the water (the existing plans still run fine). And I
can't restore from an msdb backup as I evidently don't have one that was
made before the problem started (I only keep a few weeks).
Looks like I need to rebuild the master and start over. It just seems that
MS should have a way of rebuilding just msdb without having to rebuild the
master.
Thanks
Russ Stevens
|||There is a way to rebuild msdb from scratch. May not be a supported one.
Try this on a test server before doing this on a production server
Start SQL Server from the command prompt recover just the master
database, such as sqlservr.exe -c -f -T3608 -T4022
you can then connect to sql server from query analzyer (-f means yo
uwill only get single connection), then you can delete MSDB database.
There is a script in mssql\install folder called instmsdb.sql. This is
the script to recreate MSDB database.
I have to warn you this is probably not a supported solution by
Microsoft. I am not responsible for any damage But if you are
thinking of rebuilding master database anyway, why not give this a try?
the idea is test it thoroughly and make sure it test in your development
server.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Russell Stevens wrote:
> Yih-Yoon,
> <<In your case, you have to recreate the maintenance plan, which should not
> be a big issue. In fact if that is the only problem that you get after
> the repair, you should be happy.>>
> No - I am not happy <g> - I couldn't create a maintenance plan before the
> repair or after the repair. I can't add a plan or revise a plan. Until msdb
> is fixed, I am dead in the water (the existing plans still run fine). And I
> can't restore from an msdb backup as I evidently don't have one that was
> made before the problem started (I only keep a few weeks).
> Looks like I need to rebuild the master and start over. It just seems that
> MS should have a way of rebuilding just msdb without having to rebuild the
> master.
> Thanks
> Russ Stevens
>
|||Yih-Yoon,
I suspect the instmsdb.sql script is also used by the rebuildm.exe
utility. I didn't want to chance it. I rebuilt everything, am back on line
(after an all nighter).
Needless to say I was quite amazed that after rebuilding, my sa password
had been set to a blank - no message, no warning, I was just open to the
world. How can Microsoft miss something like that?
Thanks for your help.
Russ Stevens
|||A rebuild of Master will recreate the structure and base data, but not
repopulate existing data. I would have tried the other solution first.
Drop and rerun the instmsdb.sql script. Then the risk was limited to just
msdb, which contains far less useful information than master.
Also, a good installation recommendation is to always set a responsible
backup strategy for your system databases. School of hard knocks works, but
it is an expensive lesson.
Sincerely,
Anthony Thomas

"Russell Stevens" <rastevens@.aol.com> wrote in message
news:%23tlm6PWAFHA.3504@.TK2MSFTNGP12.phx.gbl...
Yih-Yoon,
I suspect the instmsdb.sql script is also used by the rebuildm.exe
utility. I didn't want to chance it. I rebuilt everything, am back on line
(after an all nighter).
Needless to say I was quite amazed that after rebuilding, my sa password
had been set to a blank - no message, no warning, I was just open to the
world. How can Microsoft miss something like that?
Thanks for your help.
Russ Stevens

Error 823 with msdb

When I try to create a new Maintenance Plan or revise an existing one I
get the following error:
Error: 823, Severity: 24, State: 2
Microsoft SQL-DMO (ODBC SQL State:HY000)
Error 823: I/O error (torn page) detected during read at offsett
0x000000000f0000 in file 'd:\sqldata\MSSQL\data\msdbdata.mdf'.
There are no known disk problems that I can detect. The existing
Maintenance Plans run fine. I do not have a backup of msdb prior to when
this problem started (I have no idea of when it started, it only appears
when I try to modify the maintenance plan).
I can easily recreate the maintenance plans but I need to fix the problem
with msdb first. Can msdb be repaired, can I empty it to start fresh, can I
use the msdb from an original install'
Any suggestions are greatly appreciated.
Thanks
Russ StevensI just tried to repair msdb
DBCC CHECKDB ('msdb', REPAIR_ALLOW_DATA_LOSS)
It fixed 6 errors - I now have a bigger mess <g> - am getting the following
error (and other strange errors) when I try to modify a Maintenance Plan;
Error 170: Line 1: Incorrect syntax near ','.
I need a clean working msdb - appreciate any help.
Thanks
Russ Stevens|||Anytime you run a dbcc checkdb with repair_allow_data_loss you risk data
integrity. there is no way to know what kind of data is lost etc. In
your case, you have to recreate the maintenance plan, which should not
be a big issue. In fact if that is the only problem that you get after
the repair, you should be happy.
Alternatively you can always restore from backup :)
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Russell Stevens wrote:
> I just tried to repair msdb
> DBCC CHECKDB ('msdb', REPAIR_ALLOW_DATA_LOSS)
> It fixed 6 errors - I now have a bigger mess <g> - am getting the following
> error (and other strange errors) when I try to modify a Maintenance Plan;
> Error 170: Line 1: Incorrect syntax near ','.
> I need a clean working msdb - appreciate any help.
> Thanks
> Russ Stevens
>|||Yih-Yoon,
<<In your case, you have to recreate the maintenance plan, which should not
be a big issue. In fact if that is the only problem that you get after
the repair, you should be happy.>>
No - I am not happy <g> - I couldn't create a maintenance plan before the
repair or after the repair. I can't add a plan or revise a plan. Until msdb
is fixed, I am dead in the water (the existing plans still run fine). And I
can't restore from an msdb backup as I evidently don't have one that was
made before the problem started (I only keep a few weeks).
Looks like I need to rebuild the master and start over. It just seems that
MS should have a way of rebuilding just msdb without having to rebuild the
master.
Thanks
Russ Stevens|||There is a way to rebuild msdb from scratch. May not be a supported one.
Try this on a test server before doing this on a production server
Start SQL Server from the command prompt recover just the master
database, such as sqlservr.exe -c -f -T3608 -T4022
you can then connect to sql server from query analzyer (-f means yo
uwill only get single connection), then you can delete MSDB database.
There is a script in mssql\install folder called instmsdb.sql. This is
the script to recreate MSDB database.
I have to warn you this is probably not a supported solution by
Microsoft. I am not responsible for any damage :) But if you are
thinking of rebuilding master database anyway, why not give this a try?
the idea is test it thoroughly and make sure it test in your development
server.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Russell Stevens wrote:
> Yih-Yoon,
> <<In your case, you have to recreate the maintenance plan, which should not
> be a big issue. In fact if that is the only problem that you get after
> the repair, you should be happy.>>
> No - I am not happy <g> - I couldn't create a maintenance plan before the
> repair or after the repair. I can't add a plan or revise a plan. Until msdb
> is fixed, I am dead in the water (the existing plans still run fine). And I
> can't restore from an msdb backup as I evidently don't have one that was
> made before the problem started (I only keep a few weeks).
> Looks like I need to rebuild the master and start over. It just seems that
> MS should have a way of rebuilding just msdb without having to rebuild the
> master.
> Thanks
> Russ Stevens
>|||Yih-Yoon,
I suspect the instmsdb.sql script is also used by the rebuildm.exe
utility. I didn't want to chance it. I rebuilt everything, am back on line
(after an all nighter).
Needless to say I was quite amazed that after rebuilding, my sa password
had been set to a blank - no message, no warning, I was just open to the
world. How can Microsoft miss something like that?
Thanks for your help.
Russ Stevens|||A rebuild of Master will recreate the structure and base data, but not
repopulate existing data. I would have tried the other solution first.
Drop and rerun the instmsdb.sql script. Then the risk was limited to just
msdb, which contains far less useful information than master.
Also, a good installation recommendation is to always set a responsible
backup strategy for your system databases. School of hard knocks works, but
it is an expensive lesson.
Sincerely,
Anthony Thomas
"Russell Stevens" <rastevens@.aol.com> wrote in message
news:%23tlm6PWAFHA.3504@.TK2MSFTNGP12.phx.gbl...
Yih-Yoon,
I suspect the instmsdb.sql script is also used by the rebuildm.exe
utility. I didn't want to chance it. I rebuilt everything, am back on line
(after an all nighter).
Needless to say I was quite amazed that after rebuilding, my sa password
had been set to a blank - no message, no warning, I was just open to the
world. How can Microsoft miss something like that?
Thanks for your help.
Russ Stevenssql

Thursday, March 22, 2012

error 8198

sql2k sp3
win2k sp4
Im trying to create a Pull Subscription for replication and get "Error 8198:
Could not obtain information about Windows NT group/ user 'domain/ user'".
I read this:
http://support.microsoft.com/?kbid=834124
But both boxes are in the same domain and the same subnet. Both use the same
domain admin account for SQL Server and the Agent accounts.
I also read this:
http://support.microsoft.com/?kbid=838460
But Im not about to install sp4 Beta on anything.
Is there anything else I can do?
TIA, ChrisR
right click on your agent and make the agent owner sa. Restart your job.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <noemail@.bla.com> wrote in message
news:OZAuC21SFHA.2256@.tk2msftngp13.phx.gbl...
> sql2k sp3
> win2k sp4
> Im trying to create a Pull Subscription for replication and get "Error
8198:
> Could not obtain information about Windows NT group/ user 'domain/ user'".
> I read this:
> http://support.microsoft.com/?kbid=834124
> But both boxes are in the same domain and the same subnet. Both use the
same
> domain admin account for SQL Server and the Agent accounts.
> I also read this:
> http://support.microsoft.com/?kbid=838460
> But Im not about to install sp4 Beta on anything.
> Is there anything else I can do?
> TIA, ChrisR
>
|||Which Agent are you referring too? All of the jobs are owned by SA.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23RPeZF2SFHA.3544@.TK2MSFTNGP12.phx.gbl...
> right click on your agent and make the agent owner sa. Restart your job.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OZAuC21SFHA.2256@.tk2msftngp13.phx.gbl...
> 8198:
> same
>

error 8198

sql2k sp3
win2k sp4
Im trying to create a Pull Subscription for replication and get "Error 8198:
Could not obtain information about Windows NT group/ user 'domain/ user'".
I read this:
http://support.microsoft.com/?kbid=834124
But both boxes are in the same domain and the same subnet. Both use the same
domain admin account for SQL Server and the Agent accounts.
I also read this:
http://support.microsoft.com/?kbid=838460
But Im not about to install sp4 Beta on anything.
Is there anything else I can do?
TIA, ChrisRright click on your agent and make the agent owner sa. Restart your job.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <noemail@.bla.com> wrote in message
news:OZAuC21SFHA.2256@.tk2msftngp13.phx.gbl...
> sql2k sp3
> win2k sp4
> Im trying to create a Pull Subscription for replication and get "Error
8198:
> Could not obtain information about Windows NT group/ user 'domain/ user'".
> I read this:
> http://support.microsoft.com/?kbid=834124
> But both boxes are in the same domain and the same subnet. Both use the
same
> domain admin account for SQL Server and the Agent accounts.
> I also read this:
> http://support.microsoft.com/?kbid=838460
> But Im not about to install sp4 Beta on anything.
> Is there anything else I can do?
> TIA, ChrisR
>|||Which Agent are you referring too? All of the jobs are owned by SA.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23RPeZF2SFHA.3544@.TK2MSFTNGP12.phx.gbl...
> right click on your agent and make the agent owner sa. Restart your job.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OZAuC21SFHA.2256@.tk2msftngp13.phx.gbl...
> 8198:
> same
>sql

error 8198

sql2k sp3
win2k sp4
Im trying to create a Pull Subscription for replication and get "Error 8198:
Could not obtain information about Windows NT group/ user 'domain/ user'".
I read this:
http://support.microsoft.com/?kbid=834124
But both boxes are in the same domain and the same subnet. Both use the same
domain admin account for SQL Server and the Agent accounts.
I also read this:
http://support.microsoft.com/?kbid=838460
But Im not about to install sp4 Beta on anything.
Is there anything else I can do?
TIA, ChrisR
right click on your agent and make the agent owner sa. Restart your job.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <noemail@.bla.com> wrote in message
news:OZAuC21SFHA.2256@.tk2msftngp13.phx.gbl...
> sql2k sp3
> win2k sp4
> Im trying to create a Pull Subscription for replication and get "Error
8198:
> Could not obtain information about Windows NT group/ user 'domain/ user'".
> I read this:
> http://support.microsoft.com/?kbid=834124
> But both boxes are in the same domain and the same subnet. Both use the
same
> domain admin account for SQL Server and the Agent accounts.
> I also read this:
> http://support.microsoft.com/?kbid=838460
> But Im not about to install sp4 Beta on anything.
> Is there anything else I can do?
> TIA, ChrisR
>
|||Which Agent are you referring too? All of the jobs are owned by SA.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23RPeZF2SFHA.3544@.TK2MSFTNGP12.phx.gbl...
> right click on your agent and make the agent owner sa. Restart your job.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OZAuC21SFHA.2256@.tk2msftngp13.phx.gbl...
> 8198:
> same
>

error 8198

sql2k sp3
win2k sp4
Im trying to create a Pull Subscription for replication and get "Error 8198:
Could not obtain information about Windows NT group/ user 'domain/ user'".
I read this:
http://support.microsoft.com/?kbid=834124
But both boxes are in the same domain and the same subnet. Both use the same
domain admin account for SQL Server and the Agent accounts.
I also read this:
http://support.microsoft.com/?kbid=838460
But Im not about to install sp4 Beta on anything.
Is there anything else I can do?
TIA, ChrisRright click on your agent and make the agent owner sa. Restart your job.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <noemail@.bla.com> wrote in message
news:OZAuC21SFHA.2256@.tk2msftngp13.phx.gbl...
> sql2k sp3
> win2k sp4
> Im trying to create a Pull Subscription for replication and get "Error
8198:
> Could not obtain information about Windows NT group/ user 'domain/ user'".
> I read this:
> http://support.microsoft.com/?kbid=834124
> But both boxes are in the same domain and the same subnet. Both use the
same
> domain admin account for SQL Server and the Agent accounts.
> I also read this:
> http://support.microsoft.com/?kbid=838460
> But Im not about to install sp4 Beta on anything.
> Is there anything else I can do?
> TIA, ChrisR
>|||Which Agent are you referring too? All of the jobs are owned by SA.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23RPeZF2SFHA.3544@.TK2MSFTNGP12.phx.gbl...
> right click on your agent and make the agent owner sa. Restart your job.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OZAuC21SFHA.2256@.tk2msftngp13.phx.gbl...
>> sql2k sp3
>> win2k sp4
>> Im trying to create a Pull Subscription for replication and get "Error
> 8198:
>> Could not obtain information about Windows NT group/ user 'domain/
>> user'".
>> I read this:
>> http://support.microsoft.com/?kbid=834124
>> But both boxes are in the same domain and the same subnet. Both use the
> same
>> domain admin account for SQL Server and the Agent accounts.
>> I also read this:
>> http://support.microsoft.com/?kbid=838460
>> But Im not about to install sp4 Beta on anything.
>> Is there anything else I can do?
>> TIA, ChrisR
>>
>

Error 8152: "String or binary data would be truncated"

I've been working with a sample database that the company is using for testing purposes. I (we) did not create the database - it was sent over to us by another company. I'm still a rank newbie at working with MS SQL Server, though I've worked with Access and MySQL in the past.
There is one table that contains bank information. At the moment, it is filled with information on imaginary (fake) banks. I need to change one record so that it contains the information of a real bank the company is using.
The problem is, I am unable to touch anything within this table. Any attempt to make changes gives me an error prompt that reads "String or binary data would be truncated". I ran the profiler, and it shows an Exception - Error: 8152 Severity 16 State 2.
Furthermore, I also get an error prompt stating: "The value you entered is not consistent with the data type or length of this column".
I've checked and checked again, and as far as I can tell, the value I entered _is_ consistent with the data type/length of the column.
I can make changes perfectly fine on the other tables in the database. Only this one table gives me trouble.
Could anyone shed some light on why exactly this is occurring, and why only on this one table?
Thank you :)
in order to shed some light, you would need to show us the schema and the statement you're issuing that's causing the failure.

also check if there are triggers on the table, if so, you should check out the code of those as well.|||Greg - I just had to say thanks. I've been trying to figure out my own similar problem for (way too many) hours. When you mentioned "triggers" a light went on and led me to the source of the problem - a field updated by a trigger was indeed too small. Now I can get some sleep. THANK-YOU!
Randy|||

Make sure that [Table] and ]TableX] are the same type and lenght.

I was getting the same error and I found out that the filed length was different in one table then another table that had the foreign key of the first table.

For example accountnumber field in the statement table was 400 but in statementx table it was set to 255. After I changed the accountnumber field in the statementx table to 400 the problem fixed.

|||

Hi Greg,

Have you ever seen this error occur on few (not all) records that been tried?

Here's the trigger code that get's called to archive the original data after it's been changed:

CREATE TRIGGER dbo.tuASSIGNMENT
ON dbo.ASSIGNMENT
AFTER UPDATE, DELETE AS

INSERT ASSIGNMENTHISTORY(assignmentID,
assignmentName,
assignmentDesc,
assigneeID,
assignDate,
dueDate,
completeDate,
crUser,
updUser,
updDate,
disposition)

SELECT assignmentID,
assignmentName,
assignmentDesc,
assigneeID,
assignDate,
dueDate,
completeDate,
crUser,
user,
getdate(),
disposition
FROM deleted

And here's the stored procedure code that works on all cases when executed directly on the SQL Server box, but blows up with the error occasionally when called with the same parameters from an Access 2002 form. All of the parmeter values are passed and read okay.

CREATE PROCEDURE dbo.updPROJECTtoNewDirector
(@.oldID int,
@.newID int,
@.assmtID int)

AS

UPDATE ASSIGNMENT
SET assigneeID = @.newID
WHERE assigneeID = @.oldID
AND assignmentID = @.assmtID

Thanks,

Mark

|||

In this case, it seems that Access 2002/VBA was having trouble with the trigger's behavior. When I diabled the trigger, all of the records that were showing up with the Error 8152 were suddenly updating ok. Bizarre behavior, but that's Access VBA for ya!

Happy New Year to all!

Error 8152: "String or binary data would be truncated"

I've been working with a sample database that the company is using for testing purposes. I (we) did not create the database - it was sent over to us by another company. I'm still a rank newbie at working with MS SQL Server, though I've worked with Access and MySQL in the past.
There is one table that contains bank information. At the moment, it is filled with information on imaginary (fake) banks. I need to change one record so that it contains the information of a real bank the company is using.
The problem is, I am unable to touch anything within this table. Any attempt to make changes gives me an error prompt that reads "String or binary data would be truncated". I ran the profiler, and it shows an Exception - Error: 8152 Severity 16 State 2.
Furthermore, I also get an error prompt stating: "The value you entered is not consistent with the data type or length of this column".
I've checked and checked again, and as far as I can tell, the value I entered _is_ consistent with the data type/length of the column.
I can make changes perfectly fine on the other tables in the database. Only this one table gives me trouble.
Could anyone shed some light on why exactly this is occurring, and why only on this one table?
Thank you :)in order to shed some light, you would need to show us the schema and the statement you're issuing that's causing the failure.

also check if there are triggers on the table, if so, you should check out the code of those as well.|||Greg - I just had to say thanks. I've been trying to figure out my own similar problem for (way too many) hours. When you mentioned "triggers" a light went on and led me to the source of the problem - a field updated by a trigger was indeed too small. Now I can get some sleep. THANK-YOU!
Randy|||

Make sure that [Table] and ]TableX] are the same type and lenght.

I was getting the same error and I found out that the filed length was different in one table then another table that had the foreign key of the first table.

For example accountnumber field in the statement table was 400 but in statementx table it was set to 255. After I changed the accountnumber field in the statementx table to 400 the problem fixed.

|||

Hi Greg,

Have you ever seen this error occur on few (not all) records that been tried?

Here's the trigger code that get's called to archive the original data after it's been changed:

CREATE TRIGGER dbo.tuASSIGNMENT
ON dbo.ASSIGNMENT
AFTER UPDATE, DELETE AS

INSERT ASSIGNMENTHISTORY(assignmentID,
assignmentName,
assignmentDesc,
assigneeID,
assignDate,
dueDate,
completeDate,
crUser,
updUser,
updDate,
disposition)

SELECT assignmentID,
assignmentName,
assignmentDesc,
assigneeID,
assignDate,
dueDate,
completeDate,
crUser,
user,
getdate(),
disposition
FROM deleted

And here's the stored procedure code that works on all cases when executed directly on the SQL Server box, but blows up with the error occasionally when called with the same parameters from an Access 2002 form. All of the parmeter values are passed and read okay.

CREATE PROCEDURE dbo.updPROJECTtoNewDirector
(@.oldID int,
@.newID int,
@.assmtID int)

AS

UPDATE ASSIGNMENT
SET assigneeID = @.newID
WHERE assigneeID = @.oldID
AND assignmentID = @.assmtID

Thanks,

Mark

|||

In this case, it seems that Access 2002/VBA was having trouble with the trigger's behavior. When I diabled the trigger, all of the records that were showing up with the Error 8152 were suddenly updating ok. Bizarre behavior, but that's Access VBA for ya!

Happy New Year to all!

Error 8152: "String or binary data would be truncated"

I've been working with a sample database that the company is using for testing purposes. I (we) did not create the database - it was sent over to us by another company. I'm still a rank newbie at working with MS SQL Server, though I've worked with Access and MySQL in the past.
There is one table that contains bank information. At the moment, it is filled with information on imaginary (fake) banks. I need to change one record so that it contains the information of a real bank the company is using.
The problem is, I am unable to touch anything within this table. Any attempt to make changes gives me an error prompt that reads "String or binary data would be truncated". I ran the profiler, and it shows an Exception - Error: 8152 Severity 16 State 2.
Furthermore, I also get an error prompt stating: "The value you entered is not consistent with the data type or length of this column".
I've checked and checked again, and as far as I can tell, the value I entered _is_ consistent with the data type/length of the column.
I can make changes perfectly fine on the other tables in the database. Only this one table gives me trouble.
Could anyone shed some light on why exactly this is occurring, and why only on this one table?
Thank you :)
in order to shed some light, you would need to show us the schema and the statement you're issuing that's causing the failure.

also check if there are triggers on the table, if so, you should check out the code of those as well.|||Greg - I just had to say thanks. I've been trying to figure out my own similar problem for (way too many) hours. When you mentioned "triggers" a light went on and led me to the source of the problem - a field updated by a trigger was indeed too small. Now I can get some sleep. THANK-YOU!
Randy|||

Make sure that [Table] and ]TableX] are the same type and lenght.

I was getting the same error and I found out that the filed length was different in one table then another table that had the foreign key of the first table.

For example accountnumber field in the statement table was 400 but in statementx table it was set to 255. After I changed the accountnumber field in the statementx table to 400 the problem fixed.

|||

Hi Greg,

Have you ever seen this error occur on few (not all) records that been tried?

Here's the trigger code that get's called to archive the original data after it's been changed:

CREATE TRIGGER dbo.tuASSIGNMENT
ON dbo.ASSIGNMENT
AFTER UPDATE, DELETE AS

INSERT ASSIGNMENTHISTORY(assignmentID,
assignmentName,
assignmentDesc,
assigneeID,
assignDate,
dueDate,
completeDate,
crUser,
updUser,
updDate,
disposition)

SELECT assignmentID,
assignmentName,
assignmentDesc,
assigneeID,
assignDate,
dueDate,
completeDate,
crUser,
user,
getdate(),
disposition
FROM deleted

And here's the stored procedure code that works on all cases when executed directly on the SQL Server box, but blows up with the error occasionally when called with the same parameters from an Access 2002 form. All of the parmeter values are passed and read okay.

CREATE PROCEDURE dbo.updPROJECTtoNewDirector
(@.oldID int,
@.newID int,
@.assmtID int)

AS

UPDATE ASSIGNMENT
SET assigneeID = @.newID
WHERE assigneeID = @.oldID
AND assignmentID = @.assmtID

Thanks,

Mark

|||

In this case, it seems that Access 2002/VBA was having trouble with the trigger's behavior. When I diabled the trigger, all of the records that were showing up with the Error 8152 were suddenly updating ok. Bizarre behavior, but that's Access VBA for ya!

Happy New Year to all!

Error 8152

Hi,
I have the following problem. I create a small request to truncate a column
(see below). When I run it, I get an error 8152. I don't understand why.
I have no null value into the table.
I would appreciate any help
tia
Samuel
ALTER TABLE [dbo].[ArticleNumber] ALTER COLUMN [Number] [varchar] (35)
COLLATE Latin1_General_CI_AS NOT NULLError 8152 ("String or binary data would be truncated.") indicates that you
have data in the column that is longer than the length you are trying to set
it to, i.e. longer than 35 characters.
Do
SELECT MAX(LEN(Number)) FROM ArticleNumber
to find out how long the values in that column are.
Jacco Schalkwijk
SQL Server MVP
"Samuel Bernard" <SamuelBernard@.discussions.microsoft.com> wrote in message
news:0F9B9A47-B83C-43B0-A6C1-F6D4EE61C56E@.microsoft.com...
> Hi,
> I have the following problem. I create a small request to truncate a
> column
> (see below). When I run it, I get an error 8152. I don't understand why.
> I have no null value into the table.
> I would appreciate any help
> tia
> Samuel
> ALTER TABLE [dbo].[ArticleNumber] ALTER COLUMN [Number] [varchar] (35)
> COLLATE Latin1_General_CI_AS NOT NULL|||Hi
What data type is the column now?
The error message indicates that there is data, with lergth exceeding 35, in
the column and SQL Server will not just throw the extra data away.
Run something like this frist to remove the extra characters (NOTE: You
loose data by doing this, so only run it after careful consideration and
testing)
Update ArticleNumber
SET Number = SUBSTRING(Number, 1, 35)
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Samuel Bernard" wrote:

> Hi,
> I have the following problem. I create a small request to truncate a colum
n
> (see below). When I run it, I get an error 8152. I don't understand why.
> I have no null value into the table.
> I would appreciate any help
> tia
> Samuel
> ALTER TABLE [dbo].[ArticleNumber] ALTER COLUMN [Number] [varchar] (35)
> COLLATE Latin1_General_CI_AS NOT NULL|||Hi Jacco,
Thanks you for your quick and GOOD answer.
"Jacco Schalkwijk" wrote:

> Error 8152 ("String or binary data would be truncated.") indicates that yo
u
> have data in the column that is longer than the length you are trying to s
et
> it to, i.e. longer than 35 characters.
> Do
> SELECT MAX(LEN(Number)) FROM ArticleNumber
> to find out how long the values in that column are.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Samuel Bernard" <SamuelBernard@.discussions.microsoft.com> wrote in messag
e
> news:0F9B9A47-B83C-43B0-A6C1-F6D4EE61C56E@.microsoft.com...
>
>

Error 8115

While trying to create script for database following error appeared:
An error occurrred during scripting.
Error 8115: Arithmatic overflow error converting expression to data type int
.
Database is SQL 7 and the Enterpriser Manager I am using is SQL 2000.
Any help will be appreciatedHave you tried to create the script using a 7.0 Enterprise Manager Client?
"SB138" <SB138@.discussions.microsoft.com> wrote in message
news:C43596C9-7FF0-4E1C-93B8-2840F29DA0F2@.microsoft.com...
> While trying to create script for database following error appeared:
> An error occurrred during scripting.
> Error 8115: Arithmatic overflow error converting expression to data type
int.
> Database is SQL 7 and the Enterpriser Manager I am using is SQL 2000.
> Any help will be appreciated|||I have not tried yet, but I can generate script for another database residin
g
on same sql 7 server, using SQL 2000 client.
"Gregory A. Larsen" wrote:

> Have you tried to create the script using a 7.0 Enterprise Manager Client?
> "SB138" <SB138@.discussions.microsoft.com> wrote in message
> news:C43596C9-7FF0-4E1C-93B8-2840F29DA0F2@.microsoft.com...
> int.
>
>sql

Error 8115

While trying to create script for database following error appeared:
An error occurrred during scripting.
Error 8115: Arithmatic overflow error converting expression to data type int.
Database is SQL 7 and the Enterpriser Manager I am using is SQL 2000.
Any help will be appreciated
Have you tried to create the script using a 7.0 Enterprise Manager Client?
"SB138" <SB138@.discussions.microsoft.com> wrote in message
news:C43596C9-7FF0-4E1C-93B8-2840F29DA0F2@.microsoft.com...
> While trying to create script for database following error appeared:
> An error occurrred during scripting.
> Error 8115: Arithmatic overflow error converting expression to data type
int.
> Database is SQL 7 and the Enterpriser Manager I am using is SQL 2000.
> Any help will be appreciated
|||I have not tried yet, but I can generate script for another database residing
on same sql 7 server, using SQL 2000 client.
"Gregory A. Larsen" wrote:

> Have you tried to create the script using a 7.0 Enterprise Manager Client?
> "SB138" <SB138@.discussions.microsoft.com> wrote in message
> news:C43596C9-7FF0-4E1C-93B8-2840F29DA0F2@.microsoft.com...
> int.
>
>

Wednesday, March 21, 2012

Error 7405 with a linked server and stored procedure

I'm trying to create a stored procecudure using the openquery command to an linked ODBC server.

Everytime I try to save the procedure I get "error 7405: Heterogenous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection."

I've checked everywhere and set these options on, yet it still doesn't work.

Any ideas?

It's a simple procedure and when I type the sql into query analyzer it works.

HELP?

Here is the procedure.
===============================
CREATE PROCEDURE [dbo].[stpDKLink] AS

select * from openquery(dk, 'select * from pub.invoice')

GO
===============================Yes, SQLServer error message is a bit misleading in this case. Try this out:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[stpDKLink] AS
select * from openquery(dk, 'select * from pub.invoice')
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOsql

Monday, March 19, 2012

Error 7302

Hello All -

I am getting the following error after I create a linked
server using Enterprise Manager on an SQL2K machine:

Error 7302: Could not create an instance of OLE DB
provider 'SQLOLEDB' error trace [Non-interface error: CoCreate of DSO for SQLOLEDB returned 0x80004002].

The linked server is created OK, this error comes up when
trying to browse tables/views.

I have ServicePack3.

Thanks!Audrey

What is the linked server type?

J|||Make sure that the OLE DB
provider 'SQLOLEDB file is registered correctly. Use RegSvr32.exe to register the Microsoft OLE DB provider for that provider. If the registration fails, reinstall your MDAC components. For more information about MDAC, refer to this Web site:

http://microsoft.com/data/
Here is registry key refers to the provider name :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Providers\ProviderName|||Originally posted by SQLSurfer
Audrey

What is the linked server type?

J

Linked server type is MSSQL2000.|||Originally posted by Satya
Make sure that the OLE DB
provider 'SQLOLEDB file is registered correctly. Use RegSvr32.exe to register the Microsoft OLE DB provider for that provider. If the registration fails, reinstall your MDAC components. For more information about MDAC, refer to this Web site:

http://microsoft.com/data/
Here is registry key refers to the provider name :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Providers\ProviderName

Registration passes correctly, but she does not help.

Sunday, March 11, 2012

Error 5177 attempting to create database

Compaq server with two channel controler, channel 1 is D:
(67GB)drive channel 2 is E: (67GB). Both RAID 1+0. SQL
does not show the E: drive and generates a 5177 error when
attempting to create database. Operating system on C:
mirrored on two drives.
Operating system Windows 2000 Server, SQL 2000 updated to
SP2. Currently version of Best MAS 500 v6.2 won't work
with SP3.
Problem began when one of the database logfiles which had
been operating for 8 months expanded to consume 50GB of
remaining space on the drive. Attempted to Shrink but
didn't work. Removed the databse and attmepted to
reinstall with data on D: and logs on E: but constantly
run into the error.
Was able to successfully add the database and logs by
keeping both on the D: drive. That's not how we want it
configured.Here's a KB article that may be of help:
http://support.microsoft.com/default.aspx?kbid=239759
I assume that the E partition does have space free, and
you can write to it outside of SQL. Be sure to check the
Event log for possible I/O related problems, as this
could be hardware related. If you see general I/O
errors, you'll probably want to contact your hardware
vendor.
You probably already know this by now, but to avoid the
log eating all of the space, be sure to make regular
backups of the log. A full database backup does NOT
clean out the log file. The easiest way of doing this is
to set up a SQL Maintenance plan to backup the log and
maintain a set history of backups (e.g., 2 weeks worth).
Hope that helps.
>--Original Message--
>Compaq server with two channel controler, channel 1 is
D:
>(67GB)drive channel 2 is E: (67GB). Both RAID 1+0. SQL
>does not show the E: drive and generates a 5177 error
when
>attempting to create database. Operating system on C:
>mirrored on two drives.
>Operating system Windows 2000 Server, SQL 2000 updated
to
>SP2. Currently version of Best MAS 500 v6.2 won't work
>with SP3.
>Problem began when one of the database logfiles which
had
>been operating for 8 months expanded to consume 50GB of
>remaining space on the drive. Attempted to Shrink but
>didn't work. Removed the databse and attmepted to
>reinstall with data on D: and logs on E: but constantly
>run into the error.
>Was able to successfully add the database and logs by
>keeping both on the D: drive. That's not how we want it
>configured.
>.
>

Friday, March 9, 2012

Error 5123: CREATE FILE encountered operating system error 32

I'm moving a database from Server A to Server B. When I tried to restore
from the .BAK file in Enterprise Manager I got an error message about "Device
activation error. The physical filename...maybe incorrect. File...cannot be
restored to...Use WITH MOVE to identify a valid location for the file."
So I tried to restore from Query Analyzer with...
RESTORE FILELISTONLY
FROM disk = 'C:\Dennis\OCT2004_POST_INVENTORY.BAK'
RESTORE DATABASE USQL_Data
FROM disk = 'C:\Dennis\OCT2004_POST_INVENTORY.BAK'
WITH NORECOVERY,
MOVE 'usql_data' TO 'C:\Dennis\FY2004.mdf',
MOVE 'usql_log' TO 'C:\Dennis\FY2004.ldf'
The 2 files are physically restored as named in the MOVE statement. In
Enterprise Manager the restored database name does not show up in the console
root. I tried to attach the restored database but get an error message
"Error 5123: CREATE FILE encountered operating system error 32..."
What do I need to do to use the restored database in Enterprise Manager?
Dennis
> The 2 files are physically restored as named in the MOVE statement. In
> Enterprise Manager the restored database name does not show up in the console
> root.
Are you saying that the restore executed without error message but you didn't see the database in
EM? If so, it is most likely that you need to do refresh in EM to see the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:A47A4F39-4343-4BBE-AC3B-65CEBD957D6A@.microsoft.com...
> I'm moving a database from Server A to Server B. When I tried to restore
> from the .BAK file in Enterprise Manager I got an error message about "Device
> activation error. The physical filename...maybe incorrect. File...cannot be
> restored to...Use WITH MOVE to identify a valid location for the file."
> So I tried to restore from Query Analyzer with...
> RESTORE FILELISTONLY
> FROM disk = 'C:\Dennis\OCT2004_POST_INVENTORY.BAK'
> RESTORE DATABASE USQL_Data
> FROM disk = 'C:\Dennis\OCT2004_POST_INVENTORY.BAK'
> WITH NORECOVERY,
> MOVE 'usql_data' TO 'C:\Dennis\FY2004.mdf',
> MOVE 'usql_log' TO 'C:\Dennis\FY2004.ldf'
> The 2 files are physically restored as named in the MOVE statement. In
> Enterprise Manager the restored database name does not show up in the console
> root. I tried to attach the restored database but get an error message
> "Error 5123: CREATE FILE encountered operating system error 32..."
> What do I need to do to use the restored database in Enterprise Manager?
> --
> Dennis
|||Yes. The restore executed without error from Query Analyzer, but the
database didn't show up in EM console root after the restore. I tried a
refresh. Still didn't show up. I did get the restore to work through EM
since my first post. I copied and pasted the path from Windows Explorer
rather than typing it and it worked.
Dennis
"Tibor Karaszi" wrote:

> Are you saying that the restore executed without error message but you didn't see the database in
> EM? If so, it is most likely that you need to do refresh in EM to see the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
> news:A47A4F39-4343-4BBE-AC3B-65CEBD957D6A@.microsoft.com...
>
>

Error 5123: CREATE FILE encountered operating system error 32

I'm moving a database from Server A to Server B. When I tried to restore
from the .BAK file in Enterprise Manager I got an error message about "Devic
e
activation error. The physical filename...maybe incorrect. File...cannot b
e
restored to...Use WITH MOVE to identify a valid location for the file."
So I tried to restore from Query Analyzer with...
RESTORE FILELISTONLY
FROM disk = 'C:\Dennis\OCT2004_POST_INVENTORY.BAK'
RESTORE DATABASE USQL_Data
FROM disk = 'C:\Dennis\OCT2004_POST_INVENTORY.BAK'
WITH NORECOVERY,
MOVE 'usql_data' TO 'C:\Dennis\FY2004.mdf',
MOVE 'usql_log' TO 'C:\Dennis\FY2004.ldf'
The 2 files are physically restored as named in the MOVE statement. In
Enterprise Manager the restored database name does not show up in the consol
e
root. I tried to attach the restored database but get an error message
"Error 5123: CREATE FILE encountered operating system error 32..."
What do I need to do to use the restored database in Enterprise Manager?
Dennis> The 2 files are physically restored as named in the MOVE statement. In
> Enterprise Manager the restored database name does not show up in the cons
ole
> root.
Are you saying that the restore executed without error message but you didn'
t see the database in
EM? If so, it is most likely that you need to do refresh in EM to see the da
tabase.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:A47A4F39-4343-4BBE-AC3B-65CEBD957D6A@.microsoft.com...
> I'm moving a database from Server A to Server B. When I tried to restore
> from the .BAK file in Enterprise Manager I got an error message about "Dev
ice
> activation error. The physical filename...maybe incorrect. File...cannot
be
> restored to...Use WITH MOVE to identify a valid location for the file."
> So I tried to restore from Query Analyzer with...
> RESTORE FILELISTONLY
> FROM disk = 'C:\Dennis\OCT2004_POST_INVENTORY.BAK'
> RESTORE DATABASE USQL_Data
> FROM disk = 'C:\Dennis\OCT2004_POST_INVENTORY.BAK'
> WITH NORECOVERY,
> MOVE 'usql_data' TO 'C:\Dennis\FY2004.mdf',
> MOVE 'usql_log' TO 'C:\Dennis\FY2004.ldf'
> The 2 files are physically restored as named in the MOVE statement. In
> Enterprise Manager the restored database name does not show up in the cons
ole
> root. I tried to attach the restored database but get an error message
> "Error 5123: CREATE FILE encountered operating system error 32..."
> What do I need to do to use the restored database in Enterprise Manager?
> --
> Dennis|||Yes. The restore executed without error from Query Analyzer, but the
database didn't show up in EM console root after the restore. I tried a
refresh. Still didn't show up. I did get the restore to work through EM
since my first post. I copied and pasted the path from Windows Explorer
rather than typing it and it worked.
--
Dennis
"Tibor Karaszi" wrote:

> Are you saying that the restore executed without error message but you did
n't see the database in
> EM? If so, it is most likely that you need to do refresh in EM to see the
database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
> news:A47A4F39-4343-4BBE-AC3B-65CEBD957D6A@.microsoft.com...
>
>

Error 5123: CREATE FILE encountered operating system error 32

I'm moving a database from Server A to Server B. When I tried to restore
from the .BAK file in Enterprise Manager I got an error message about "Device
activation error. The physical filename...maybe incorrect. File...cannot be
restored to...Use WITH MOVE to identify a valid location for the file."
So I tried to restore from Query Analyzer with...
RESTORE FILELISTONLY
FROM disk = 'C:\Dennis\OCT2004_POST_INVENTORY.BAK'
RESTORE DATABASE USQL_Data
FROM disk = 'C:\Dennis\OCT2004_POST_INVENTORY.BAK'
WITH NORECOVERY,
MOVE 'usql_data' TO 'C:\Dennis\FY2004.mdf',
MOVE 'usql_log' TO 'C:\Dennis\FY2004.ldf'
The 2 files are physically restored as named in the MOVE statement. In
Enterprise Manager the restored database name does not show up in the console
root. I tried to attach the restored database but get an error message
"Error 5123: CREATE FILE encountered operating system error 32..."
What do I need to do to use the restored database in Enterprise Manager?
--
Dennis> The 2 files are physically restored as named in the MOVE statement. In
> Enterprise Manager the restored database name does not show up in the console
> root.
Are you saying that the restore executed without error message but you didn't see the database in
EM? If so, it is most likely that you need to do refresh in EM to see the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:A47A4F39-4343-4BBE-AC3B-65CEBD957D6A@.microsoft.com...
> I'm moving a database from Server A to Server B. When I tried to restore
> from the .BAK file in Enterprise Manager I got an error message about "Device
> activation error. The physical filename...maybe incorrect. File...cannot be
> restored to...Use WITH MOVE to identify a valid location for the file."
> So I tried to restore from Query Analyzer with...
> RESTORE FILELISTONLY
> FROM disk = 'C:\Dennis\OCT2004_POST_INVENTORY.BAK'
> RESTORE DATABASE USQL_Data
> FROM disk = 'C:\Dennis\OCT2004_POST_INVENTORY.BAK'
> WITH NORECOVERY,
> MOVE 'usql_data' TO 'C:\Dennis\FY2004.mdf',
> MOVE 'usql_log' TO 'C:\Dennis\FY2004.ldf'
> The 2 files are physically restored as named in the MOVE statement. In
> Enterprise Manager the restored database name does not show up in the console
> root. I tried to attach the restored database but get an error message
> "Error 5123: CREATE FILE encountered operating system error 32..."
> What do I need to do to use the restored database in Enterprise Manager?
> --
> Dennis|||Yes. The restore executed without error from Query Analyzer, but the
database didn't show up in EM console root after the restore. I tried a
refresh. Still didn't show up. I did get the restore to work through EM
since my first post. I copied and pasted the path from Windows Explorer
rather than typing it and it worked.
--
Dennis
"Tibor Karaszi" wrote:
> > The 2 files are physically restored as named in the MOVE statement. In
> > Enterprise Manager the restored database name does not show up in the console
> > root.
> Are you saying that the restore executed without error message but you didn't see the database in
> EM? If so, it is most likely that you need to do refresh in EM to see the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
> news:A47A4F39-4343-4BBE-AC3B-65CEBD957D6A@.microsoft.com...
> > I'm moving a database from Server A to Server B. When I tried to restore
> > from the .BAK file in Enterprise Manager I got an error message about "Device
> > activation error. The physical filename...maybe incorrect. File...cannot be
> > restored to...Use WITH MOVE to identify a valid location for the file."
> >
> > So I tried to restore from Query Analyzer with...
> > RESTORE FILELISTONLY
> > FROM disk = 'C:\Dennis\OCT2004_POST_INVENTORY.BAK'
> >
> > RESTORE DATABASE USQL_Data
> > FROM disk = 'C:\Dennis\OCT2004_POST_INVENTORY.BAK'
> > WITH NORECOVERY,
> > MOVE 'usql_data' TO 'C:\Dennis\FY2004.mdf',
> > MOVE 'usql_log' TO 'C:\Dennis\FY2004.ldf'
> >
> > The 2 files are physically restored as named in the MOVE statement. In
> > Enterprise Manager the restored database name does not show up in the console
> > root. I tried to attach the restored database but get an error message
> > "Error 5123: CREATE FILE encountered operating system error 32..."
> >
> > What do I need to do to use the restored database in Enterprise Manager?
> >
> > --
> > Dennis
>
>

Error 5123

I detached a database and moved it to another folder. I am trying to
re-attach the database and am getting 'Error 5123: CREATE FILE encountered
operating system error 32(The process cannot access the file because it is
being used by another process.) while attempting to open or create the
physical file...
Is there a file configuration setting I can change to eliminate this error
message? I've done this many times without problem and had no indication of
any process using this database when I detached it. Any helpful suggestions
to be able to attach this database will be appreciated. Thank you.
DanHi,
It can be our Backup process or Virus software accessing the file. Please
verify.
Thanks
Hari
SQL Server MVP
"Dan Slaby" <dls@.newsgroup.nospam> wrote in message
news:OKxEEK$jFHA.3960@.TK2MSFTNGP12.phx.gbl...
>I detached a database and moved it to another folder. I am trying to
>re-attach the database and am getting 'Error 5123: CREATE FILE encountered
>operating system error 32(The process cannot access the file because it is
>being used by another process.) while attempting to open or create the
>physical file...
> Is there a file configuration setting I can change to eliminate this error
> message? I've done this many times without problem and had no indication
> of any process using this database when I detached it. Any helpful
> suggestions to be able to attach this database will be appreciated. Thank
> you.
> Dan
>|||Dan,
I am having the exact same problem. I loaded a utility from
www.sysinternals.com that told me that it is indeed the SQL Server process
locking the .mdf file. I, however, have been unable to correct the problem.
I'm thinking about stopping the SQL Server, renaming the .mdf files,
restarting the server, then attaching the DBs.
I believe I locked the DB file accidentally before I detached by issuing a
DBCC OpenTran(<DBName> ). Now I can't reattach because of the file lock.
Leslie
"Dan Slaby" wrote:

> I detached a database and moved it to another folder. I am trying to
> re-attach the database and am getting 'Error 5123: CREATE FILE encountered
> operating system error 32(The process cannot access the file because it i
s
> being used by another process.) while attempting to open or create the
> physical file...
> Is there a file configuration setting I can change to eliminate this error
> message? I've done this many times without problem and had no indication o
f
> any process using this database when I detached it. Any helpful suggestion
s
> to be able to attach this database will be appreciated. Thank you.
> Dan
>
>