bad results from linked server to oracle

Using an openquery select statement against a linked server to oracle is
consistently yielding an incomplete result set. Using the Oracle Client, 225
records are returned, yet the SQL Server 2000 linked server returns only 126
records. Both statements were run using the same security credentials. The
linked server is connected to Oracle via odbc and as a test, i ran the same
statement against the same ODBC DSN in Access and did get the correct results.
Therefore the problem does not appear to be with the ODBC driver but with the
linked server itself.
Here is a representation of the tests i have done thus far:
in Sql server i am accessing the linked server using the OPENQUERY statement
as follows:
SELECT *
FROM OPENQUERY(oracledb, 'SELECT *
FROM ORACLETABLE') Rowset_1
running this statement has yielded 126 records
from ms access using the same ODBC system dsn i am getting 225 records with
this statement:SELECT *
FROM ORACLETABLE
and from the SQL Plus Oracle client i am getting 225 records with the
following statement:SELECT *
FROM ORACLETABLE;
That is a bit weird. Have you tried using a different table? OPENROWSET
(as a test)? Drop/recreate the LS to Oracle?
Might look here as well:
How to set up and troubleshoot a linked server to Oracle in SQL Server
http://support.microsoft.com/default...b;en-us;280106
HTH
Jerry
"inteldsn via droptable.com" <u15033@.uwe> wrote in message
news:56172ad97fe9e@.uwe...
> Using an openquery select statement against a linked server to oracle is
> consistently yielding an incomplete result set. Using the Oracle Client,
> 225
> records are returned, yet the SQL Server 2000 linked server returns only
> 126
> records. Both statements were run using the same security credentials. The
> linked server is connected to Oracle via odbc and as a test, i ran the
> same
> statement against the same ODBC DSN in Access and did get the correct
> results.
> Therefore the problem does not appear to be with the ODBC driver but with
> the
> linked server itself.
> Here is a representation of the tests i have done thus far:
> in Sql server i am accessing the linked server using the OPENQUERY
> statement
> as follows:
> SELECT *
> FROM OPENQUERY(oracledb, 'SELECT *
> FROM ORACLETABLE') Rowset_1
> running this statement has yielded 126 records
> from ms access using the same ODBC system dsn i am getting 225 records
> with
> this statement:SELECT *
> FROM ORACLETABLE
> and from the SQL Plus Oracle client i am getting 225 records with the
> following statement:SELECT *
> FROM ORACLETABLE;
|||good suggestion. i did look at that msdn article earlier, i have not been
able to check all of the tables yet, but so far this behavior is not on all
of the tables. update from testing: if i run select * from openquery ('select
count(*) from oracletable') recordset_1 I do get the magic number 225.
I have also queried using a where statement for one of the missing records
and the missing record DOES return. so why it does not return using select *
is the mystery.
Jerry Spivey wrote:
>That is a bit weird. Have you tried using a different table? OPENROWSET
>(as a test)? Drop/recreate the LS to Oracle?
>Might look here as well:
>How to set up and troubleshoot a linked server to Oracle in SQL Server
>http://support.microsoft.com/default...b;en-us;280106
>HTH
>Jerry
|||NULLs on the other end? Might try COUNT(COLUMN) just to see what it
returns. I'd be looking at the column values for a row that doesn't come
back without the WHERE as compared to one that does.
HTH
Jerry
"inteldsn via droptable.com" <u15033@.uwe> wrote in message
news:56175b9b94e48@.uwe...[vbcol=seagreen]
> good suggestion. i did look at that msdn article earlier, i have not been
> able to check all of the tables yet, but so far this behavior is not on
> all
> of the tables. update from testing: if i run select * from openquery
> ('select
> count(*) from oracletable') recordset_1 I do get the magic number 225.
> I have also queried using a where statement for one of the missing records
> and the missing record DOES return. so why it does not return using select
> *
> is the mystery.
>
> Jerry Spivey wrote:
|||Jerry,
no nulls on the other end. SELECT *
FROM OPENQUERY(oracledb, 'SELECTcount (*)
FROM ORACLETABLE') Rowset_1 yields a result of 255 just as it should.
if i put a where clause on it, i can get any of the 255 actual records in the
table, but i can't get them all from a general select statement.
Jerry Spivey wrote:[vbcol=seagreen]
>NULLs on the other end? Might try COUNT(COLUMN) just to see what it
>returns. I'd be looking at the column values for a row that doesn't come
>back without the WHERE as compared to one that does.
>HTH
>Jerry
>[quoted text clipped - 19 lines]
|||TJI: What is the possibility that the table on the Oracle side does not have
a unique key and what you are seeing are the unique records?
I have seen that OpenQuery/OLEDB has a preference for unique identifiers.
Joseph R.P. Maloney, CSP,CCP,CDP
"inteldsn via droptable.com" wrote:

> Jerry,
> no nulls on the other end. SELECT *
> FROM OPENQUERY(oracledb, 'SELECTcount (*)
> FROM ORACLETABLE') Rowset_1 yields a result of 255 just as it should.
> if i put a where clause on it, i can get any of the 255 actual records in the
> table, but i can't get them all from a general select statement.
>
> Jerry Spivey wrote:
>
|||the results list is missing 99 unique new records, so that is not the answer.
WORKAROUND: MS techs were unable to solve this either, but what we did do was
to abandon using the provider for odbc to create the linked server. Here's
what i have learned so far. using the microsoft provider for odbc driver to
create the linked server, the linked server fails to provide consistently
correct results. I switched to using the provider for ole db for oracle and i
am now getting good results. However, you have to download the provider for
ole db from the oracle site in order to install this and get it working if
your oracle installation is anything more recent than 8.2. there are other
dependencies requiring additional software and configuration if you need to
use distributed transactions as well.
so basically, this is a flaw in the way sql server 2000 handles linked
servers created through the odbc provider.
jrpm wrote:[vbcol=seagreen]
>TJI: What is the possibility that the table on the Oracle side does not have
>a unique key and what you are seeing are the unique records?
>I have seen that OpenQuery/OLEDB has a preference for unique identifiers.
>[quoted text clipped - 17 lines]
Message posted via http://www.droptable.com
|||UGH!!! I should have asked you that. I had an issue using the ODBC driver
for ORACLE (similar but not the same) at a submarine base consult one time.
I dropped the LS and recreated it using the OLE-DB provider for ORACLE and
it worked fine.
Sorry didn't think of that sooner.
Jerry
"inteldsn via droptable.com" <u15033@.uwe> wrote in message
news:5623fa2fc9ebd@.uwe...
> the results list is missing 99 unique new records, so that is not the
> answer.
>
> WORKAROUND: MS techs were unable to solve this either, but what we did do
> was
> to abandon using the provider for odbc to create the linked server. Here's
> what i have learned so far. using the microsoft provider for odbc driver
> to
> create the linked server, the linked server fails to provide consistently
> correct results. I switched to using the provider for ole db for oracle
> and i
> am now getting good results. However, you have to download the provider
> for
> ole db from the oracle site in order to install this and get it working
> if
> your oracle installation is anything more recent than 8.2. there are other
> dependencies requiring additional software and configuration if you need
> to
> use distributed transactions as well.
> so basically, this is a flaw in the way sql server 2000 handles linked
> servers created through the odbc provider.
> jrpm wrote:
>
> --
> Message posted via http://www.droptable.com

bad results from linked server to oracle

Using an openquery select statement against a linked server to oracle is
consistently yielding an incomplete result set. Using the Oracle Client, 225
records are returned, yet the SQL Server 2000 linked server returns only 126
records. Both statements were run using the same security credentials. The
linked server is connected to Oracle via odbc and as a test, i ran the same
statement against the same ODBC DSN in Access and did get the correct result
s.
Therefore the problem does not appear to be with the ODBC driver but with th
e
linked server itself.
Here is a representation of the tests i have done thus far:
in Sql server i am accessing the linked server using the OPENQUERY statement
as follows:
SELECT *
FROM OPENQUERY(oracledb, 'SELECT *
FROM ORACLETABLE') Rowset_1
running this statement has yielded 126 records
from ms access using the same ODBC system dsn i am getting 225 records with
this statement:SELECT *
FROM ORACLETABLE
and from the SQL Plus Oracle client i am getting 225 records with the
following statement:SELECT *
FROM ORACLETABLE;That is a bit weird. Have you tried using a different table? OPENROWSET
(as a test)? Drop/recreate the LS to Oracle?
Might look here as well:
How to set up and troubleshoot a linked server to Oracle in SQL Server
http://support.microsoft.com/defaul...kb;en-us;280106
HTH
Jerry
"inteldsn via droptable.com" <u15033@.uwe> wrote in message
news:56172ad97fe9e@.uwe...
> Using an openquery select statement against a linked server to oracle is
> consistently yielding an incomplete result set. Using the Oracle Client,
> 225
> records are returned, yet the SQL Server 2000 linked server returns only
> 126
> records. Both statements were run using the same security credentials. The
> linked server is connected to Oracle via odbc and as a test, i ran the
> same
> statement against the same ODBC DSN in Access and did get the correct
> results.
> Therefore the problem does not appear to be with the ODBC driver but with
> the
> linked server itself.
> Here is a representation of the tests i have done thus far:
> in Sql server i am accessing the linked server using the OPENQUERY
> statement
> as follows:
> SELECT *
> FROM OPENQUERY(oracledb, 'SELECT *
> FROM ORACLETABLE') Rowset_1
> running this statement has yielded 126 records
> from ms access using the same ODBC system dsn i am getting 225 records
> with
> this statement:SELECT *
> FROM ORACLETABLE
> and from the SQL Plus Oracle client i am getting 225 records with the
> following statement:SELECT *
> FROM ORACLETABLE;|||good suggestion. i did look at that msdn article earlier, i have not been
able to check all of the tables yet, but so far this behavior is not on all
of the tables. update from testing: if i run select * from openquery ('selec
t
count(*) from oracletable') recordset_1 I do get the magic number 225.
I have also queried using a where statement for one of the missing records
and the missing record DOES return. so why it does not return using select *
is the mystery.
Jerry Spivey wrote:
>That is a bit weird. Have you tried using a different table? OPENROWSET
>(as a test)? Drop/recreate the LS to Oracle?
>Might look here as well:
>How to set up and troubleshoot a linked server to Oracle in SQL Server
>http://support.microsoft.com/defaul...kb;en-us;280106
>HTH
>Jerry|||NULLs on the other end? Might try COUNT(COLUMN) just to see what it
returns. I'd be looking at the column values for a row that doesn't come
back without the WHERE as compared to one that does.
HTH
Jerry
"inteldsn via droptable.com" <u15033@.uwe> wrote in message
news:56175b9b94e48@.uwe...[vbcol=seagreen]
> good suggestion. i did look at that msdn article earlier, i have not been
> able to check all of the tables yet, but so far this behavior is not on
> all
> of the tables. update from testing: if i run select * from openquery
> ('select
> count(*) from oracletable') recordset_1 I do get the magic number 225.
> I have also queried using a where statement for one of the missing records
> and the missing record DOES return. so why it does not return using select
> *
> is the mystery.
>
> Jerry Spivey wrote:|||Jerry,
no nulls on the other end. SELECT *
FROM OPENQUERY(oracledb, 'SELECTcount (*)
FROM ORACLETABLE') Rowset_1 yields a result of 255 just as it should.
if i put a where clause on it, i can get any of the 255 actual records in th
e
table, but i can't get them all from a general select statement.
Jerry Spivey wrote:[vbcol=seagreen]
>NULLs on the other end? Might try COUNT(COLUMN) just to see what it
>returns. I'd be looking at the column values for a row that doesn't come
>back without the WHERE as compared to one that does.
>HTH
>Jerry
>[quoted text clipped - 19 lines]|||TJI: What is the possibility that the table on the Oracle side does not have
a unique key and what you are seeing are the unique records?
I have seen that OpenQuery/OLEDB has a preference for unique identifiers.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"inteldsn via droptable.com" wrote:

> Jerry,
> no nulls on the other end. SELECT *
> FROM OPENQUERY(oracledb, 'SELECTcount (*)
> FROM ORACLETABLE') Rowset_1 yields a result of 255 just as it should.
> if i put a where clause on it, i can get any of the 255 actual records in
the
> table, but i can't get them all from a general select statement.
>
> Jerry Spivey wrote:
>|||the results list is missing 99 unique new records, so that is not the answer
.
WORKAROUND: MS techs were unable to solve this either, but what we did do wa
s
to abandon using the provider for odbc to create the linked server. Here's
what i have learned so far. using the microsoft provider for odbc driver to
create the linked server, the linked server fails to provide consistently
correct results. I switched to using the provider for ole db for oracle and
i
am now getting good results. However, you have to download the provider for
ole db from the oracle site in order to install this and get it working if
your oracle installation is anything more recent than 8.2. there are other
dependencies requiring additional software and configuration if you need to
use distributed transactions as well.
so basically, this is a flaw in the way sql server 2000 handles linked
servers created through the odbc provider.
jrpm wrote:[vbcol=seagreen]
>TJI: What is the possibility that the table on the Oracle side does not hav
e
>a unique key and what you are seeing are the unique records?
>I have seen that OpenQuery/OLEDB has a preference for unique identifiers.
>[quoted text clipped - 17 lines]
Message posted via http://www.droptable.com|||UGH!!! I should have asked you that. I had an issue using the ODBC driver
for ORACLE (similar but not the same) at a submarine base consult one time.
I dropped the LS and recreated it using the OLE-DB provider for ORACLE and
it worked fine.
Sorry didn't think of that sooner.
Jerry
"inteldsn via droptable.com" <u15033@.uwe> wrote in message
news:5623fa2fc9ebd@.uwe...
> the results list is missing 99 unique new records, so that is not the
> answer.
>
> WORKAROUND: MS techs were unable to solve this either, but what we did do
> was
> to abandon using the provider for odbc to create the linked server. Here's
> what i have learned so far. using the microsoft provider for odbc driver
> to
> create the linked server, the linked server fails to provide consistently
> correct results. I switched to using the provider for ole db for oracle
> and i
> am now getting good results. However, you have to download the provider
> for
> ole db from the oracle site in order to install this and get it working
> if
> your oracle installation is anything more recent than 8.2. there are other
> dependencies requiring additional software and configuration if you need
> to
> use distributed transactions as well.
> so basically, this is a flaw in the way sql server 2000 handles linked
> servers created through the odbc provider.
> jrpm wrote:
>
> --
> Message posted via http://www.droptable.com

bad results from linked server to oracle

Using an openquery select statement against a linked server to oracle is
consistently yielding an incomplete result set. Using the Oracle Client, 225
records are returned, yet the SQL Server 2000 linked server returns only 126
records. Both statements were run using the same security credentials. The
linked server is connected to Oracle via odbc and as a test, i ran the same
statement against the same ODBC DSN in Access and did get the correct results.
Therefore the problem does not appear to be with the ODBC driver but with the
linked server itself.
Here is a representation of the tests i have done thus far:
in Sql server i am accessing the linked server using the OPENQUERY statement
as follows:
SELECT *
FROM OPENQUERY(oracledb, 'SELECT *
FROM ORACLETABLE') Rowset_1
running this statement has yielded 126 records
from ms access using the same ODBC system dsn i am getting 225 records with
this statement:SELECT *
FROM ORACLETABLE
and from the SQL Plus Oracle client i am getting 225 records with the
following statement:SELECT *
FROM ORACLETABLE;That is a bit weird. Have you tried using a different table? OPENROWSET
(as a test)? Drop/recreate the LS to Oracle?
Might look here as well:
How to set up and troubleshoot a linked server to Oracle in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;280106
HTH
Jerry
"inteldsn via SQLMonster.com" <u15033@.uwe> wrote in message
news:56172ad97fe9e@.uwe...
> Using an openquery select statement against a linked server to oracle is
> consistently yielding an incomplete result set. Using the Oracle Client,
> 225
> records are returned, yet the SQL Server 2000 linked server returns only
> 126
> records. Both statements were run using the same security credentials. The
> linked server is connected to Oracle via odbc and as a test, i ran the
> same
> statement against the same ODBC DSN in Access and did get the correct
> results.
> Therefore the problem does not appear to be with the ODBC driver but with
> the
> linked server itself.
> Here is a representation of the tests i have done thus far:
> in Sql server i am accessing the linked server using the OPENQUERY
> statement
> as follows:
> SELECT *
> FROM OPENQUERY(oracledb, 'SELECT *
> FROM ORACLETABLE') Rowset_1
> running this statement has yielded 126 records
> from ms access using the same ODBC system dsn i am getting 225 records
> with
> this statement:SELECT *
> FROM ORACLETABLE
> and from the SQL Plus Oracle client i am getting 225 records with the
> following statement:SELECT *
> FROM ORACLETABLE;|||good suggestion. i did look at that msdn article earlier, i have not been
able to check all of the tables yet, but so far this behavior is not on all
of the tables. update from testing: if i run select * from openquery ('select
count(*) from oracletable') recordset_1 I do get the magic number 225.
I have also queried using a where statement for one of the missing records
and the missing record DOES return. so why it does not return using select *
is the mystery.
Jerry Spivey wrote:
>That is a bit weird. Have you tried using a different table? OPENROWSET
>(as a test)? Drop/recreate the LS to Oracle?
>Might look here as well:
>How to set up and troubleshoot a linked server to Oracle in SQL Server
>http://support.microsoft.com/default.aspx?scid=kb;en-us;280106
>HTH
>Jerry|||NULLs on the other end? Might try COUNT(COLUMN) just to see what it
returns. I'd be looking at the column values for a row that doesn't come
back without the WHERE as compared to one that does.
HTH
Jerry
"inteldsn via SQLMonster.com" <u15033@.uwe> wrote in message
news:56175b9b94e48@.uwe...
> good suggestion. i did look at that msdn article earlier, i have not been
> able to check all of the tables yet, but so far this behavior is not on
> all
> of the tables. update from testing: if i run select * from openquery
> ('select
> count(*) from oracletable') recordset_1 I do get the magic number 225.
> I have also queried using a where statement for one of the missing records
> and the missing record DOES return. so why it does not return using select
> *
> is the mystery.
>
> Jerry Spivey wrote:
>>That is a bit weird. Have you tried using a different table? OPENROWSET
>>(as a test)? Drop/recreate the LS to Oracle?
>>Might look here as well:
>>How to set up and troubleshoot a linked server to Oracle in SQL Server
>>http://support.microsoft.com/default.aspx?scid=kb;en-us;280106
>>HTH
>>Jerry|||Jerry,
no nulls on the other end. SELECT *
FROM OPENQUERY(oracledb, 'SELECTcount (*)
FROM ORACLETABLE') Rowset_1 yields a result of 255 just as it should.
if i put a where clause on it, i can get any of the 255 actual records in the
table, but i can't get them all from a general select statement.
Jerry Spivey wrote:
>NULLs on the other end? Might try COUNT(COLUMN) just to see what it
>returns. I'd be looking at the column values for a row that doesn't come
>back without the WHERE as compared to one that does.
>HTH
>Jerry
>> good suggestion. i did look at that msdn article earlier, i have not been
>> able to check all of the tables yet, but so far this behavior is not on
>[quoted text clipped - 19 lines]
>>Jerry|||TJI: What is the possibility that the table on the Oracle side does not have
a unique key and what you are seeing are the unique records?
I have seen that OpenQuery/OLEDB has a preference for unique identifiers.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"inteldsn via SQLMonster.com" wrote:
> Jerry,
> no nulls on the other end. SELECT *
> FROM OPENQUERY(oracledb, 'SELECTcount (*)
> FROM ORACLETABLE') Rowset_1 yields a result of 255 just as it should.
> if i put a where clause on it, i can get any of the 255 actual records in the
> table, but i can't get them all from a general select statement.
>
> Jerry Spivey wrote:
> >NULLs on the other end? Might try COUNT(COLUMN) just to see what it
> >returns. I'd be looking at the column values for a row that doesn't come
> >back without the WHERE as compared to one that does.
> >
> >HTH
> >
> >Jerry
> >> good suggestion. i did look at that msdn article earlier, i have not been
> >> able to check all of the tables yet, but so far this behavior is not on
> >[quoted text clipped - 19 lines]
> >>
> >>Jerry
>|||the results list is missing 99 unique new records, so that is not the answer.
WORKAROUND: MS techs were unable to solve this either, but what we did do was
to abandon using the provider for odbc to create the linked server. Here's
what i have learned so far. using the microsoft provider for odbc driver to
create the linked server, the linked server fails to provide consistently
correct results. I switched to using the provider for ole db for oracle and i
am now getting good results. However, you have to download the provider for
ole db from the oracle site in order to install this and get it working if
your oracle installation is anything more recent than 8.2. there are other
dependencies requiring additional software and configuration if you need to
use distributed transactions as well.
so basically, this is a flaw in the way sql server 2000 handles linked
servers created through the odbc provider.
jrpm wrote:
>TJI: What is the possibility that the table on the Oracle side does not have
>a unique key and what you are seeing are the unique records?
>I have seen that OpenQuery/OLEDB has a preference for unique identifiers.
>> Jerry,
>[quoted text clipped - 17 lines]
>> >>
>> >>Jerry
Message posted via http://www.sqlmonster.com|||UGH!!! I should have asked you that. I had an issue using the ODBC driver
for ORACLE (similar but not the same) at a submarine base consult one time.
I dropped the LS and recreated it using the OLE-DB provider for ORACLE and
it worked fine.
Sorry didn't think of that sooner.
Jerry
"inteldsn via SQLMonster.com" <u15033@.uwe> wrote in message
news:5623fa2fc9ebd@.uwe...
> the results list is missing 99 unique new records, so that is not the
> answer.
>
> WORKAROUND: MS techs were unable to solve this either, but what we did do
> was
> to abandon using the provider for odbc to create the linked server. Here's
> what i have learned so far. using the microsoft provider for odbc driver
> to
> create the linked server, the linked server fails to provide consistently
> correct results. I switched to using the provider for ole db for oracle
> and i
> am now getting good results. However, you have to download the provider
> for
> ole db from the oracle site in order to install this and get it working
> if
> your oracle installation is anything more recent than 8.2. there are other
> dependencies requiring additional software and configuration if you need
> to
> use distributed transactions as well.
> so basically, this is a flaw in the way sql server 2000 handles linked
> servers created through the odbc provider.
> jrpm wrote:
>>TJI: What is the possibility that the table on the Oracle side does not
>>have
>>a unique key and what you are seeing are the unique records?
>>I have seen that OpenQuery/OLEDB has a preference for unique identifiers.
>> Jerry,
>>[quoted text clipped - 17 lines]
>> >>
>> >>Jerry
>
> --
> Message posted via http://www.sqlmonster.com

Backup yielding Operatin System Error 33?

A little help please?
I have a 14.2 GB database that has stopped backing up and yields the
following errors in the Event log and SQL Server Log respectively:
Application Event Log
18210 :
BackupMedium::ReportIoError: write failure on backup device 'C:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
Operating system error 33(The process cannot access the file because another
process has locked a portion of the file.).
SQL Server Error Log:
Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
Is there any reasonable way to fix this?
Thanks in advance for your help.I've seen that message when trying to backup to a compressed volume.
If that's the case, this may help :-
http://support.microsoft.com/?id=231347
--
HTH. Ryan
"Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
>A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
> Operating system error 33(The process cannot access the file because
> another
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft
> SQL
> Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>|||Dave,
You can try to identify the process that has locked the file. It is
possible that a third party software like tape backup has acquired a read
lock on the file.
1) use "net file " command to see open files
"net file id /close " to close a open file
2) Use third party tools like filemon from sysinternals to do the same
You can also see if there are no active sql server processes or agent jobs
that may be accessing the file.
Another workaround is to rename the existing file and try the backup again
assuming that you are able to rename and there is space for additional backup.
"Dave Smith" wrote:
> A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
> Operating system error 33(The process cannot access the file because another
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>|||Way to go Ryan!!
That was exactly the problem. Out MSSQL\BACKUP folder was set to be
compressed.
FYI, the job does a DBCC CHECKDB, Full Backup, and Verify. It used to fail
after 38 to 44 minutes. it just succeeded in 18:03!!
YOU are the man!
Thanks a bunch,
Dave Smith
"Ryan" wrote:
> I've seen that message when trying to backup to a compressed volume.
> If that's the case, this may help :-
> http://support.microsoft.com/?id=231347
> --
> HTH. Ryan
>
> "Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
> news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
> >A little help please?
> >
> > I have a 14.2 GB database that has stopped backing up and yields the
> > following errors in the Event log and SQL Server Log respectively:
> >
> > Application Event Log
> > 18210 :
> > BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> > Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
> > Operating system error 33(The process cannot access the file because
> > another
> > process has locked a portion of the file.).
> >
> > SQL Server Error Log:
> > Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> > 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> > Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> > m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft
> > SQL
> > Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
> >
> > Is there any reasonable way to fix this?
> >
> > Thanks in advance for your help.
> >
> >
>
>

Backup yielding Operatin System Error 33?

A little help please?
I have a 14.2 GB database that has stopped backing up and yields the
following errors in the Event log and SQL Server Log respectively:
Application Event Log
18210 :
BackupMedium::ReportIoError: write failure on backup device 'C:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Ma
nual.bak'.
Operating system error 33(The process cannot access the file because another
process has locked a portion of the file.).
SQL Server Error Log:
Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\enDataWH_db_20060104
2204.BAK
Is there any reasonable way to fix this?
Thanks in advance for your help.I've seen that message when trying to backup to a compressed volume.
If that's the case, this may help :-
http://support.microsoft.com/?id=231347
HTH. Ryan
"Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
>A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Ma
nual.bak'.
> Operating system error 33(The process cannot access the file because
> another
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft
> SQL
> Server\MSSQL\BACKUP\enDataWH_db_20060104
2204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>|||Dave,
You can try to identify the process that has locked the file. It is
possible that a third party software like tape backup has acquired a read
lock on the file.
1) use "net file " command to see open files
"net file id /close " to close a open file
2) Use third party tools like filemon from sysinternals to do the same
You can also see if there are no active sql server processes or agent jobs
that may be accessing the file.
Another workaround is to rename the existing file and try the backup again
assuming that you are able to rename and there is space for additional backu
p.
"Dave Smith" wrote:

> A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Ma
nual.bak'.
> Operating system error 33(The process cannot access the file because anoth
er
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQ
L
> Server\MSSQL\BACKUP\enDataWH_db_20060104
2204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>|||Way to go Ryan!!
That was exactly the problem. Out MSSQL\BACKUP folder was set to be
compressed.
FYI, the job does a DBCC CHECKDB, Full Backup, and Verify. It used to fail
after 38 to 44 minutes. it just succeeded in 18:03!!
YOU are the man!
Thanks a bunch,
Dave Smith
"Ryan" wrote:

> I've seen that message when trying to backup to a compressed volume.
> If that's the case, this may help :-
> http://support.microsoft.com/?id=231347
> --
> HTH. Ryan
>
> "Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
> news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
>
>

Backup yielding Operatin System Error 33?

A little help please?
I have a 14.2 GB database that has stopped backing up and yields the
following errors in the Event log and SQL Server Log respectively:
Application Event Log
18210 :
BackupMedium::ReportIoError: write failure on backup device 'C:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
Operating system error 33(The process cannot access the file because another
process has locked a portion of the file.).
SQL Server Error Log:
Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
Is there any reasonable way to fix this?
Thanks in advance for your help.
I've seen that message when trying to backup to a compressed volume.
If that's the case, this may help :-
http://support.microsoft.com/?id=231347
HTH. Ryan
"Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
>A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
> Operating system error 33(The process cannot access the file because
> another
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft
> SQL
> Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>
|||Dave,
You can try to identify the process that has locked the file. It is
possible that a third party software like tape backup has acquired a read
lock on the file.
1) use "net file " command to see open files
"net file id /close " to close a open file
2) Use third party tools like filemon from sysinternals to do the same
You can also see if there are no active sql server processes or agent jobs
that may be accessing the file.
Another workaround is to rename the existing file and try the backup again
assuming that you are able to rename and there is space for additional backup.
"Dave Smith" wrote:

> A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
> Operating system error 33(The process cannot access the file because another
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>
|||Way to go Ryan!!
That was exactly the problem. Out MSSQL\BACKUP folder was set to be
compressed.
FYI, the job does a DBCC CHECKDB, Full Backup, and Verify. It used to fail
after 38 to 44 minutes. it just succeeded in 18:03!!
YOU are the man!
Thanks a bunch,
Dave Smith
"Ryan" wrote:

> I've seen that message when trying to backup to a compressed volume.
> If that's the case, this may help :-
> http://support.microsoft.com/?id=231347
> --
> HTH. Ryan
>
> "Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
> news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
>
>