I really hope someone out there can help me with this one... My company
recently acquired another company that was using SQL Server 7. The machine
that the database was housed on began to have hard drive issues. We were
able to extract the mdf and ldf files before the hard drives crashed
completely. When attempting to load these files into a SQL Server 2k
database, we hit a wall. SQL Server 2k refuses to attach the mdf file,
saying that the file is not an mdf file. We also received a similar error
in Query Analyzer, using both sp_attach_db and sp_attach_single_file_db;
this error claimed that the file name might be incorrect.
After this failure, we created a new database, stopped the services, and
switched the mdf files with the new databases. This brought the database up
in suspect mode. We were then able to switch it to suspect/emergency mode.
From here, we attempted a dbcc checkdb, which errored out with an version
error. We also attempted to import the data from the new emergency db into
a newer clean db. This failed with a login error, even though we were
logged in under sa.
I realize that we will probably not be able to retrieve all of the records
from the corrupt file, but does anyone know a way to access any of the data?
Any ideas or suggestions are welcome.
P.S. all of the servers that we manage are backed up daily... just trying
to fix another company's error
Thanks,
JoeOne of the things you might try to do is to attach it to a SQL 7 server..
Then the upgrade will not have to occur...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joe" <joe@.nospam.com> wrote in message
news:uFAJlKCwDHA.1888@.TK2MSFTNGP10.phx.gbl...
> I really hope someone out there can help me with this one... My company
> recently acquired another company that was using SQL Server 7. The
machine
> that the database was housed on began to have hard drive issues. We were
> able to extract the mdf and ldf files before the hard drives crashed
> completely. When attempting to load these files into a SQL Server 2k
> database, we hit a wall. SQL Server 2k refuses to attach the mdf file,
> saying that the file is not an mdf file. We also received a similar error
> in Query Analyzer, using both sp_attach_db and sp_attach_single_file_db;
> this error claimed that the file name might be incorrect.
> After this failure, we created a new database, stopped the services, and
> switched the mdf files with the new databases. This brought the database
up
> in suspect mode. We were then able to switch it to suspect/emergency
mode.
> From here, we attempted a dbcc checkdb, which errored out with an version
> error. We also attempted to import the data from the new emergency db
into
> a newer clean db. This failed with a login error, even though we were
> logged in under sa.
> I realize that we will probably not be able to retrieve all of the records
> from the corrupt file, but does anyone know a way to access any of the
data?
> Any ideas or suggestions are welcome.
>
> P.S. all of the servers that we manage are backed up daily... just
trying
> to fix another company's error
> Thanks,
> Joe
>|||Wayne,
I think your system clock or time zone is a bit off..., not sure if you have
noticed.
--
- Anith
( Please reply to newsgroups only )|||thanks... my region was wrong... sorry.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joe" <joe@.nospam.com> wrote in message
news:uFAJlKCwDHA.1888@.TK2MSFTNGP10.phx.gbl...
> I really hope someone out there can help me with this one... My company
> recently acquired another company that was using SQL Server 7. The
machine
> that the database was housed on began to have hard drive issues. We were
> able to extract the mdf and ldf files before the hard drives crashed
> completely. When attempting to load these files into a SQL Server 2k
> database, we hit a wall. SQL Server 2k refuses to attach the mdf file,
> saying that the file is not an mdf file. We also received a similar error
> in Query Analyzer, using both sp_attach_db and sp_attach_single_file_db;
> this error claimed that the file name might be incorrect.
> After this failure, we created a new database, stopped the services, and
> switched the mdf files with the new databases. This brought the database
up
> in suspect mode. We were then able to switch it to suspect/emergency
mode.
> From here, we attempted a dbcc checkdb, which errored out with an version
> error. We also attempted to import the data from the new emergency db
into
> a newer clean db. This failed with a login error, even though we were
> logged in under sa.
> I realize that we will probably not be able to retrieve all of the records
> from the corrupt file, but does anyone know a way to access any of the
data?
> Any ideas or suggestions are welcome.
>
> P.S. all of the servers that we manage are backed up daily... just
trying
> to fix another company's error
> Thanks,
> Joe
>|||I'm with Wayne. A secondary option can be to try
http://www.officerecovery.com/mssql/, not sure how much help such a tool can
be, though.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:OSKheOCwDHA.1680@.TK2MSFTNGP12.phx.gbl...
> One of the things you might try to do is to attach it to a SQL 7 server..
> Then the upgrade will not have to occur...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Joe" <joe@.nospam.com> wrote in message
> news:uFAJlKCwDHA.1888@.TK2MSFTNGP10.phx.gbl...
> > I really hope someone out there can help me with this one... My company
> > recently acquired another company that was using SQL Server 7. The
> machine
> > that the database was housed on began to have hard drive issues. We
were
> > able to extract the mdf and ldf files before the hard drives crashed
> > completely. When attempting to load these files into a SQL Server 2k
> > database, we hit a wall. SQL Server 2k refuses to attach the mdf file,
> > saying that the file is not an mdf file. We also received a similar
error
> > in Query Analyzer, using both sp_attach_db and sp_attach_single_file_db;
> > this error claimed that the file name might be incorrect.
> >
> > After this failure, we created a new database, stopped the services, and
> > switched the mdf files with the new databases. This brought the
database
> up
> > in suspect mode. We were then able to switch it to suspect/emergency
> mode.
> > From here, we attempted a dbcc checkdb, which errored out with an
version
> > error. We also attempted to import the data from the new emergency db
> into
> > a newer clean db. This failed with a login error, even though we were
> > logged in under sa.
> >
> > I realize that we will probably not be able to retrieve all of the
records
> > from the corrupt file, but does anyone know a way to access any of the
> data?
> > Any ideas or suggestions are welcome.
> >
> >
> > P.S. all of the servers that we manage are backed up daily... just
> trying
> > to fix another company's error
> >
> > Thanks,
> >
> > Joe
> >
> >
>sql
Showing posts with label file. Show all posts
Showing posts with label file. Show all posts
Thursday, March 29, 2012
Monday, March 26, 2012
Help with BCP!
Hi,
I am creating a text file using bcp in SQL2000. When I open the text
file which was created the =A3 sign has been replaced with something
else. WHen I look at the results in a query, the pound sign is there,
it's only when bcp'd to a text file.
It is running on Windows 2003 Server.
ANy help would be appreciated.This is a char, varchar, or text code page issue. The ascii value for pPound
sterling is 163m, which is greater than 127. If you don't want any
conversation, try to use -C RAW on the bcp command line.
Linchi
"nomad" wrote:
> Hi,
> I am creating a text file using bcp in SQL2000. When I open the text
> file which was created the ￡ sign has been replaced with something
> else. WHen I look at the results in a query, the pound sign is there,
> it's only when bcp'd to a text file.
> It is running on Windows 2003 Server.
> ANy help would be appreciated.
>|||On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> This is a char, varchar, or text code page issue. The ascii value for pPo=
und
> sterling is 163m, which is greater than 127. If you don't want any
> conversation, try to use -C RAW on the bcp command line.
> Hi,
Thanks for your response. I have tried setting the '=A3' to CHAR(163)
within the view, and the BCP is set to -c but still no joy. Could it
be something to do with the character set of the Server? as I am in
the UK, but it seems not to recognise '=A3', as if it is in America
ascii set.[vbcol=seagreen]
> Linchi
> "nomad" wrote:
>
>
>|||On 26 Jun, 13:43, nomad <d.bedg...@.ntlworld.com> wrote:[vbcol=seagreen]
> On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
>
Pound[vbcol=seagreen]
> Thanks for your response. I have tried setting the '=A3' to CHAR(163)
> within the view, and the BCP is set to -c but still no joy. Could it
> be something to do with the character set of the Server? as I am in
> the UK, but it seems not to recognise '=A3', as if it is in America
> ascii set.
>
>
>
>
>
Linchi, scrap that last reply, I wasn't adding -C RAW coorectly. It
worked a dream. Thank you very much for your answer. Much
appreciated.
I am creating a text file using bcp in SQL2000. When I open the text
file which was created the =A3 sign has been replaced with something
else. WHen I look at the results in a query, the pound sign is there,
it's only when bcp'd to a text file.
It is running on Windows 2003 Server.
ANy help would be appreciated.This is a char, varchar, or text code page issue. The ascii value for pPound
sterling is 163m, which is greater than 127. If you don't want any
conversation, try to use -C RAW on the bcp command line.
Linchi
"nomad" wrote:
> Hi,
> I am creating a text file using bcp in SQL2000. When I open the text
> file which was created the ￡ sign has been replaced with something
> else. WHen I look at the results in a query, the pound sign is there,
> it's only when bcp'd to a text file.
> It is running on Windows 2003 Server.
> ANy help would be appreciated.
>|||On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> This is a char, varchar, or text code page issue. The ascii value for pPo=
und
> sterling is 163m, which is greater than 127. If you don't want any
> conversation, try to use -C RAW on the bcp command line.
> Hi,
Thanks for your response. I have tried setting the '=A3' to CHAR(163)
within the view, and the BCP is set to -c but still no joy. Could it
be something to do with the character set of the Server? as I am in
the UK, but it seems not to recognise '=A3', as if it is in America
ascii set.[vbcol=seagreen]
> Linchi
> "nomad" wrote:
>
>
>|||On 26 Jun, 13:43, nomad <d.bedg...@.ntlworld.com> wrote:[vbcol=seagreen]
> On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
>
Pound[vbcol=seagreen]
> Thanks for your response. I have tried setting the '=A3' to CHAR(163)
> within the view, and the BCP is set to -c but still no joy. Could it
> be something to do with the character set of the Server? as I am in
> the UK, but it seems not to recognise '=A3', as if it is in America
> ascii set.
>
>
>
>
>
Linchi, scrap that last reply, I wasn't adding -C RAW coorectly. It
worked a dream. Thank you very much for your answer. Much
appreciated.
Help with BCP!
Hi,
I am creating a text file using bcp in SQL2000. When I open the text
file which was created the =A3 sign has been replaced with something
else. WHen I look at the results in a query, the pound sign is there,
it's only when bcp'd to a text file.
It is running on Windows 2003 Server.
ANy help would be appreciated.This is a char, varchar, or text code page issue. The ascii value for pPound
sterling is 163m, which is greater than 127. If you don't want any
conversation, try to use -C RAW on the bcp command line.
Linchi
"nomad" wrote:
> Hi,
> I am creating a text file using bcp in SQL2000. When I open the text
> file which was created the £ sign has been replaced with something
> else. WHen I look at the results in a query, the pound sign is there,
> it's only when bcp'd to a text file.
> It is running on Windows 2003 Server.
> ANy help would be appreciated.
>|||On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> This is a char, varchar, or text code page issue. The ascii value for pPo=und
> sterling is 163m, which is greater than 127. If you don't want any
> conversation, try to use -C RAW on the bcp command line.
> Hi,
Thanks for your response. I have tried setting the '=A3' to CHAR(163)
within the view, and the BCP is set to -c but still no joy. Could it
be something to do with the character set of the Server? as I am in
the UK, but it seems not to recognise '=A3', as if it is in America
ascii set.
> Linchi
> "nomad" wrote:
> > Hi,
> > I am creating a text file using bcp in SQL2000. When I open the text
> > file which was created the =A3 sign has been replaced with something
> > else. WHen I look at the results in a query, the pound sign is there,
> > it's only when bcp'd to a text file.
> > It is running on Windows 2003 Server.
> > ANy help would be appreciated.|||On 26 Jun, 13:43, nomad <d.bedg...@.ntlworld.com> wrote:
> On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
> > This is a char, varchar, or text code page issue. The ascii value for p=Pound
> > sterling is 163m, which is greater than 127. If you don't want any
> > conversation, try to use -C RAW on the bcp command line.
> > Hi,
> Thanks for your response. I have tried setting the '=A3' to CHAR(163)
> within the view, and the BCP is set to -c but still no joy. Could it
> be something to do with the character set of the Server? as I am in
> the UK, but it seems not to recognise '=A3', as if it is in America
> ascii set.
> > Linchi
> > "nomad" wrote:
> > > Hi,
> > > I am creating a text file using bcp in SQL2000. When I open the text
> > > file which was created the =A3 sign has been replaced with something
> > > else. WHen I look at the results in a query, the pound sign is there,
> > > it's only when bcp'd to a text file.
> > > It is running on Windows 2003 Server.
> > > ANy help would be appreciated.
Linchi, scrap that last reply, I wasn't adding -C RAW coorectly. It
worked a dream. Thank you very much for your answer. Much
appreciated.
I am creating a text file using bcp in SQL2000. When I open the text
file which was created the =A3 sign has been replaced with something
else. WHen I look at the results in a query, the pound sign is there,
it's only when bcp'd to a text file.
It is running on Windows 2003 Server.
ANy help would be appreciated.This is a char, varchar, or text code page issue. The ascii value for pPound
sterling is 163m, which is greater than 127. If you don't want any
conversation, try to use -C RAW on the bcp command line.
Linchi
"nomad" wrote:
> Hi,
> I am creating a text file using bcp in SQL2000. When I open the text
> file which was created the £ sign has been replaced with something
> else. WHen I look at the results in a query, the pound sign is there,
> it's only when bcp'd to a text file.
> It is running on Windows 2003 Server.
> ANy help would be appreciated.
>|||On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> This is a char, varchar, or text code page issue. The ascii value for pPo=und
> sterling is 163m, which is greater than 127. If you don't want any
> conversation, try to use -C RAW on the bcp command line.
> Hi,
Thanks for your response. I have tried setting the '=A3' to CHAR(163)
within the view, and the BCP is set to -c but still no joy. Could it
be something to do with the character set of the Server? as I am in
the UK, but it seems not to recognise '=A3', as if it is in America
ascii set.
> Linchi
> "nomad" wrote:
> > Hi,
> > I am creating a text file using bcp in SQL2000. When I open the text
> > file which was created the =A3 sign has been replaced with something
> > else. WHen I look at the results in a query, the pound sign is there,
> > it's only when bcp'd to a text file.
> > It is running on Windows 2003 Server.
> > ANy help would be appreciated.|||On 26 Jun, 13:43, nomad <d.bedg...@.ntlworld.com> wrote:
> On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
> > This is a char, varchar, or text code page issue. The ascii value for p=Pound
> > sterling is 163m, which is greater than 127. If you don't want any
> > conversation, try to use -C RAW on the bcp command line.
> > Hi,
> Thanks for your response. I have tried setting the '=A3' to CHAR(163)
> within the view, and the BCP is set to -c but still no joy. Could it
> be something to do with the character set of the Server? as I am in
> the UK, but it seems not to recognise '=A3', as if it is in America
> ascii set.
> > Linchi
> > "nomad" wrote:
> > > Hi,
> > > I am creating a text file using bcp in SQL2000. When I open the text
> > > file which was created the =A3 sign has been replaced with something
> > > else. WHen I look at the results in a query, the pound sign is there,
> > > it's only when bcp'd to a text file.
> > > It is running on Windows 2003 Server.
> > > ANy help would be appreciated.
Linchi, scrap that last reply, I wasn't adding -C RAW coorectly. It
worked a dream. Thank you very much for your answer. Much
appreciated.
Help with BAK file
Hi all,
I'm not an expert with SQL2000 and need some help. I have a full backup "BAK
file" that was produced from an SQL2000 server. Is there any way that I can
use that file to rebuild that database on another SQL2000 server?
Thanks,
Clausdid you try to force the restore over the existing database?
cjobes wrote:
> Thanks for the quick answer.
> Well, I tried everything I can think of. I first created a Database with the
> same name as the database on the old server. Then I tried to restore, first
> selecting Device and pointing to the BAK file. I got a message "Device
> activation error. The physical file name xxx maybe incorect. File
> xxx_Data.mdf cannot be restored to yyy_Data.mdf. Use WITH MOVE to identify a
> valid location for the file." The same error msg follows with regard to the
> xxx_log.ldf file.
> Any idea?
> Claus
> "ilovesql" <ilovesql@.hotmail.com> wrote in message
> news:eFS3oBiSDHA.1724@.TK2MSFTNGP10.phx.gbl...
>>Sure, that what backups are for.
>>If the backup was created using single backup file then restore is even
>>easier.
>>To speedup restore and avoid network traffic, in case your database is
> big,
>>then copy the backup file to 2nd server and use the restore command.
>>One issue that you will face after restoring from different server is
>>security that you can deal with later, once restore is completed.
>>"cjobes" <cjobes@.nova-tech.org> wrote in message
>>news:#HHTC0hSDHA.632@.tk2msftngp13.phx.gbl...
>>Hi all,
>>I'm not an expert with SQL2000 and need some help. I have a full backup
>>"BAK
>>file" that was produced from an SQL2000 server. Is there any way that I
>>can
>>use that file to rebuild that database on another SQL2000 server?
>>Thanks,
>>Claus
>>
>>
>|||Yes I did check the checkbox.
"Maria" <maria.belli@.*nospam*rfsworld.com> wrote in message
news:%236X%232MiSDHA.1688@.TK2MSFTNGP11.phx.gbl...
> did you try to force the restore over the existing database?
> cjobes wrote:
> > Thanks for the quick answer.
> > Well, I tried everything I can think of. I first created a Database with
the
> > same name as the database on the old server. Then I tried to restore,
first
> > selecting Device and pointing to the BAK file. I got a message "Device
> > activation error. The physical file name xxx maybe incorect. File
> > xxx_Data.mdf cannot be restored to yyy_Data.mdf. Use WITH MOVE to
identify a
> > valid location for the file." The same error msg follows with regard to
the
> > xxx_log.ldf file.
> >
> > Any idea?
> > Claus
> >
> > "ilovesql" <ilovesql@.hotmail.com> wrote in message
> > news:eFS3oBiSDHA.1724@.TK2MSFTNGP10.phx.gbl...
> >
> >>Sure, that what backups are for.
> >>If the backup was created using single backup file then restore is even
> >>easier.
> >>To speedup restore and avoid network traffic, in case your database is
> >
> > big,
> >
> >>then copy the backup file to 2nd server and use the restore command.
> >>
> >>One issue that you will face after restoring from different server is
> >>security that you can deal with later, once restore is completed.
> >>
> >>"cjobes" <cjobes@.nova-tech.org> wrote in message
> >>news:#HHTC0hSDHA.632@.tk2msftngp13.phx.gbl...
> >>
> >>Hi all,
> >>
> >>I'm not an expert with SQL2000 and need some help. I have a full backup
> >>
> >>"BAK
> >>
> >>file" that was produced from an SQL2000 server. Is there any way that I
> >>
> >>can
> >>
> >>use that file to rebuild that database on another SQL2000 server?
> >>
> >>Thanks,
> >>Claus
> >>
> >>
> >>
> >>
> >
> >
>|||Have a look at the following KB articles
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/default.aspx?scid=kb;EN-US;221465
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"cjobes" <cjobes@.nova-tech.org> wrote in message
news:eQK1KTiSDHA.2480@.tk2msftngp13.phx.gbl...
Yes I did check the checkbox.
"Maria" <maria.belli@.*nospam*rfsworld.com> wrote in message
news:%236X%232MiSDHA.1688@.TK2MSFTNGP11.phx.gbl...
> did you try to force the restore over the existing database?
> cjobes wrote:
> > Thanks for the quick answer.
> > Well, I tried everything I can think of. I first created a Database with
the
> > same name as the database on the old server. Then I tried to restore,
first
> > selecting Device and pointing to the BAK file. I got a message "Device
> > activation error. The physical file name xxx maybe incorect. File
> > xxx_Data.mdf cannot be restored to yyy_Data.mdf. Use WITH MOVE to
identify a
> > valid location for the file." The same error msg follows with regard to
the
> > xxx_log.ldf file.
> >
> > Any idea?
> > Claus
> >
> > "ilovesql" <ilovesql@.hotmail.com> wrote in message
> > news:eFS3oBiSDHA.1724@.TK2MSFTNGP10.phx.gbl...
> >
> >>Sure, that what backups are for.
> >>If the backup was created using single backup file then restore is even
> >>easier.
> >>To speedup restore and avoid network traffic, in case your database is
> >
> > big,
> >
> >>then copy the backup file to 2nd server and use the restore command.
> >>
> >>One issue that you will face after restoring from different server is
> >>security that you can deal with later, once restore is completed.
> >>
> >>"cjobes" <cjobes@.nova-tech.org> wrote in message
> >>news:#HHTC0hSDHA.632@.tk2msftngp13.phx.gbl...
> >>
> >>Hi all,
> >>
> >>I'm not an expert with SQL2000 and need some help. I have a full backup
> >>
> >>"BAK
> >>
> >>file" that was produced from an SQL2000 server. Is there any way that I
> >>
> >>can
> >>
> >>use that file to rebuild that database on another SQL2000 server?
> >>
> >>Thanks,
> >>Claus
> >>
> >>
> >>
> >>
> >
> >
>|||Thanks for the help. This was exactly what I was looking for and it worked.
Claus
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ePqDfmiSDHA.2256@.TK2MSFTNGP11.phx.gbl...
> Have a look at the following KB articles
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;221465
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "cjobes" <cjobes@.nova-tech.org> wrote in message
> news:eQK1KTiSDHA.2480@.tk2msftngp13.phx.gbl...
> Yes I did check the checkbox.
>
> "Maria" <maria.belli@.*nospam*rfsworld.com> wrote in message
> news:%236X%232MiSDHA.1688@.TK2MSFTNGP11.phx.gbl...
> > did you try to force the restore over the existing database?
> >
> > cjobes wrote:
> > > Thanks for the quick answer.
> > > Well, I tried everything I can think of. I first created a Database
with
> the
> > > same name as the database on the old server. Then I tried to restore,
> first
> > > selecting Device and pointing to the BAK file. I got a message "Device
> > > activation error. The physical file name xxx maybe incorect. File
> > > xxx_Data.mdf cannot be restored to yyy_Data.mdf. Use WITH MOVE to
> identify a
> > > valid location for the file." The same error msg follows with regard
to
> the
> > > xxx_log.ldf file.
> > >
> > > Any idea?
> > > Claus
> > >
> > > "ilovesql" <ilovesql@.hotmail.com> wrote in message
> > > news:eFS3oBiSDHA.1724@.TK2MSFTNGP10.phx.gbl...
> > >
> > >>Sure, that what backups are for.
> > >>If the backup was created using single backup file then restore is
even
> > >>easier.
> > >>To speedup restore and avoid network traffic, in case your database is
> > >
> > > big,
> > >
> > >>then copy the backup file to 2nd server and use the restore command.
> > >>
> > >>One issue that you will face after restoring from different server is
> > >>security that you can deal with later, once restore is completed.
> > >>
> > >>"cjobes" <cjobes@.nova-tech.org> wrote in message
> > >>news:#HHTC0hSDHA.632@.tk2msftngp13.phx.gbl...
> > >>
> > >>Hi all,
> > >>
> > >>I'm not an expert with SQL2000 and need some help. I have a full
backup
> > >>
> > >>"BAK
> > >>
> > >>file" that was produced from an SQL2000 server. Is there any way that
I
> > >>
> > >>can
> > >>
> > >>use that file to rebuild that database on another SQL2000 server?
> > >>
> > >>Thanks,
> > >>Claus
> > >>
> > >>
> > >>
> > >>
> > >
> > >
> >
>
>
I'm not an expert with SQL2000 and need some help. I have a full backup "BAK
file" that was produced from an SQL2000 server. Is there any way that I can
use that file to rebuild that database on another SQL2000 server?
Thanks,
Clausdid you try to force the restore over the existing database?
cjobes wrote:
> Thanks for the quick answer.
> Well, I tried everything I can think of. I first created a Database with the
> same name as the database on the old server. Then I tried to restore, first
> selecting Device and pointing to the BAK file. I got a message "Device
> activation error. The physical file name xxx maybe incorect. File
> xxx_Data.mdf cannot be restored to yyy_Data.mdf. Use WITH MOVE to identify a
> valid location for the file." The same error msg follows with regard to the
> xxx_log.ldf file.
> Any idea?
> Claus
> "ilovesql" <ilovesql@.hotmail.com> wrote in message
> news:eFS3oBiSDHA.1724@.TK2MSFTNGP10.phx.gbl...
>>Sure, that what backups are for.
>>If the backup was created using single backup file then restore is even
>>easier.
>>To speedup restore and avoid network traffic, in case your database is
> big,
>>then copy the backup file to 2nd server and use the restore command.
>>One issue that you will face after restoring from different server is
>>security that you can deal with later, once restore is completed.
>>"cjobes" <cjobes@.nova-tech.org> wrote in message
>>news:#HHTC0hSDHA.632@.tk2msftngp13.phx.gbl...
>>Hi all,
>>I'm not an expert with SQL2000 and need some help. I have a full backup
>>"BAK
>>file" that was produced from an SQL2000 server. Is there any way that I
>>can
>>use that file to rebuild that database on another SQL2000 server?
>>Thanks,
>>Claus
>>
>>
>|||Yes I did check the checkbox.
"Maria" <maria.belli@.*nospam*rfsworld.com> wrote in message
news:%236X%232MiSDHA.1688@.TK2MSFTNGP11.phx.gbl...
> did you try to force the restore over the existing database?
> cjobes wrote:
> > Thanks for the quick answer.
> > Well, I tried everything I can think of. I first created a Database with
the
> > same name as the database on the old server. Then I tried to restore,
first
> > selecting Device and pointing to the BAK file. I got a message "Device
> > activation error. The physical file name xxx maybe incorect. File
> > xxx_Data.mdf cannot be restored to yyy_Data.mdf. Use WITH MOVE to
identify a
> > valid location for the file." The same error msg follows with regard to
the
> > xxx_log.ldf file.
> >
> > Any idea?
> > Claus
> >
> > "ilovesql" <ilovesql@.hotmail.com> wrote in message
> > news:eFS3oBiSDHA.1724@.TK2MSFTNGP10.phx.gbl...
> >
> >>Sure, that what backups are for.
> >>If the backup was created using single backup file then restore is even
> >>easier.
> >>To speedup restore and avoid network traffic, in case your database is
> >
> > big,
> >
> >>then copy the backup file to 2nd server and use the restore command.
> >>
> >>One issue that you will face after restoring from different server is
> >>security that you can deal with later, once restore is completed.
> >>
> >>"cjobes" <cjobes@.nova-tech.org> wrote in message
> >>news:#HHTC0hSDHA.632@.tk2msftngp13.phx.gbl...
> >>
> >>Hi all,
> >>
> >>I'm not an expert with SQL2000 and need some help. I have a full backup
> >>
> >>"BAK
> >>
> >>file" that was produced from an SQL2000 server. Is there any way that I
> >>
> >>can
> >>
> >>use that file to rebuild that database on another SQL2000 server?
> >>
> >>Thanks,
> >>Claus
> >>
> >>
> >>
> >>
> >
> >
>|||Have a look at the following KB articles
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/default.aspx?scid=kb;EN-US;221465
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"cjobes" <cjobes@.nova-tech.org> wrote in message
news:eQK1KTiSDHA.2480@.tk2msftngp13.phx.gbl...
Yes I did check the checkbox.
"Maria" <maria.belli@.*nospam*rfsworld.com> wrote in message
news:%236X%232MiSDHA.1688@.TK2MSFTNGP11.phx.gbl...
> did you try to force the restore over the existing database?
> cjobes wrote:
> > Thanks for the quick answer.
> > Well, I tried everything I can think of. I first created a Database with
the
> > same name as the database on the old server. Then I tried to restore,
first
> > selecting Device and pointing to the BAK file. I got a message "Device
> > activation error. The physical file name xxx maybe incorect. File
> > xxx_Data.mdf cannot be restored to yyy_Data.mdf. Use WITH MOVE to
identify a
> > valid location for the file." The same error msg follows with regard to
the
> > xxx_log.ldf file.
> >
> > Any idea?
> > Claus
> >
> > "ilovesql" <ilovesql@.hotmail.com> wrote in message
> > news:eFS3oBiSDHA.1724@.TK2MSFTNGP10.phx.gbl...
> >
> >>Sure, that what backups are for.
> >>If the backup was created using single backup file then restore is even
> >>easier.
> >>To speedup restore and avoid network traffic, in case your database is
> >
> > big,
> >
> >>then copy the backup file to 2nd server and use the restore command.
> >>
> >>One issue that you will face after restoring from different server is
> >>security that you can deal with later, once restore is completed.
> >>
> >>"cjobes" <cjobes@.nova-tech.org> wrote in message
> >>news:#HHTC0hSDHA.632@.tk2msftngp13.phx.gbl...
> >>
> >>Hi all,
> >>
> >>I'm not an expert with SQL2000 and need some help. I have a full backup
> >>
> >>"BAK
> >>
> >>file" that was produced from an SQL2000 server. Is there any way that I
> >>
> >>can
> >>
> >>use that file to rebuild that database on another SQL2000 server?
> >>
> >>Thanks,
> >>Claus
> >>
> >>
> >>
> >>
> >
> >
>|||Thanks for the help. This was exactly what I was looking for and it worked.
Claus
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ePqDfmiSDHA.2256@.TK2MSFTNGP11.phx.gbl...
> Have a look at the following KB articles
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;221465
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "cjobes" <cjobes@.nova-tech.org> wrote in message
> news:eQK1KTiSDHA.2480@.tk2msftngp13.phx.gbl...
> Yes I did check the checkbox.
>
> "Maria" <maria.belli@.*nospam*rfsworld.com> wrote in message
> news:%236X%232MiSDHA.1688@.TK2MSFTNGP11.phx.gbl...
> > did you try to force the restore over the existing database?
> >
> > cjobes wrote:
> > > Thanks for the quick answer.
> > > Well, I tried everything I can think of. I first created a Database
with
> the
> > > same name as the database on the old server. Then I tried to restore,
> first
> > > selecting Device and pointing to the BAK file. I got a message "Device
> > > activation error. The physical file name xxx maybe incorect. File
> > > xxx_Data.mdf cannot be restored to yyy_Data.mdf. Use WITH MOVE to
> identify a
> > > valid location for the file." The same error msg follows with regard
to
> the
> > > xxx_log.ldf file.
> > >
> > > Any idea?
> > > Claus
> > >
> > > "ilovesql" <ilovesql@.hotmail.com> wrote in message
> > > news:eFS3oBiSDHA.1724@.TK2MSFTNGP10.phx.gbl...
> > >
> > >>Sure, that what backups are for.
> > >>If the backup was created using single backup file then restore is
even
> > >>easier.
> > >>To speedup restore and avoid network traffic, in case your database is
> > >
> > > big,
> > >
> > >>then copy the backup file to 2nd server and use the restore command.
> > >>
> > >>One issue that you will face after restoring from different server is
> > >>security that you can deal with later, once restore is completed.
> > >>
> > >>"cjobes" <cjobes@.nova-tech.org> wrote in message
> > >>news:#HHTC0hSDHA.632@.tk2msftngp13.phx.gbl...
> > >>
> > >>Hi all,
> > >>
> > >>I'm not an expert with SQL2000 and need some help. I have a full
backup
> > >>
> > >>"BAK
> > >>
> > >>file" that was produced from an SQL2000 server. Is there any way that
I
> > >>
> > >>can
> > >>
> > >>use that file to rebuild that database on another SQL2000 server?
> > >>
> > >>Thanks,
> > >>Claus
> > >>
> > >>
> > >>
> > >>
> > >
> > >
> >
>
>
Friday, March 23, 2012
Help with aggregate query - Cant get it right!
Hi,
I have we have a client who gives their invoices in a flat file format, we import it into a SQL Server table.
Nothing is normalized – everything is repeated in every record. The fields are:
customerNumber
Invoice_number
PO_number
Qty
Description
Line_number
Line_total
Freight
Tax
Invoice_date
So an if an order has 10 line items, the header information (invoice number, PO number, ivoice date) are repeated on each of the lines
I am writing a query to show the following
Order number, Invoice total, Date
select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice_date from invoices group by invoice_no, Invoice, customerNumber
This works great - for each invoice I get the invoice number, InvoiceTotal, and Date
Then I was asked to add the PO Number – this is where I can't get it right.
When I added "PO_number" to the query, I got two lines for each invoice
select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice,PO_number from invoices group by invoice_no, Invoice, Sold_To_Cust_No,PO_number
Please help - I need to end up with: invoice_no, invoiceTotal, customerNumber, Invoice_date and PO_number (sequence does not matter)
Thanks
Hello my friend,
I see the problem. You want to get a field value without it causing other data to repeat. The solution is to use a function to get the one value and put this in the query like so: -
CREATE FUNCTION fn_GetPONumberByInvoice
(
@.InvoiceNumber AS VARCHAR(50) -- if it is an INT or whatever then change this line
)
RETURNS VARCHAR(80)
AS
BEGIN
DECLARE @.PONumber AS VARCHAR(80)
SET @.PONumber = (SELECT TOP 1 PO_Number FROM Invoices WHERE Invoice_Number = @.InvoiceNumber)
RETURN @.PONumber
END
Then in your query use the following: -
SELECT invoice_no,
sum(line_total + freight + tax) as invoiceTotal, customerNumber,
Invoice_date,
dbo.fn_GetPONumberByInvoice(invoice_no) AS 'PO_Number'
FROM invoices
GROUP BY invoice_no, Invoice, customerNumber
Kind regards
Scotty
|||
Scotty, thanks for your reply.
I am not well versed in SQL, so I have a question:
How can I use the query you wrote to create a view? I did not mention that earlier (sorry), but the objective is to create a view with header information for each invoice (invoice number, date, total amount and PO number)
Hope you can help.
Again, thanks.
|||Hello again my friend,
You should be able to do it like this: -
CREATE VIEW vw_Invoices
AS
SELECT invoice_no,
sum(line_total + freight + tax) as invoiceTotal, customerNumber,
Invoice_date,
dbo.fn_GetPONumberByInvoice(invoice_no) AS 'PO_Number'
FROM invoices
GROUP BY invoice_no, Invoice, customerNumber
Kind regards
Scotty
|||
Scotty, THANK YOU.
Monday, March 19, 2012
help with a max length table constraint
Table File_Paths (physical system file paths)
The columns represent parts of the path.
How can I set up a constraint that the total concatenated length of all the columns within a row is less than 260 chars?
Thanks
ALTER TABLE SomeTable WITH NOCHECK
ADD CONSTRAINT SomeCheck CHECK (LEN(Col1+ co2 + col 3) <= 260)
HTH, Jens Suessmeyer.
|||Right on target. Thanks
For some reason, when it gets saved a bunch of unnecessary parentheses both square and round get added.
The square brackets help if there are white spaces but the program just forces them. Likewise with the round the len() function did not need the items enclosed individually either.
Is there wa way to turn that off in SSMS?
|||The database engine modifies expressions specified in constraints, defaults, computed column etc. There is no way to control this behavior or suppress it. This is even more so in SQL Server 2005. So you should not rely on the scripting for your DDL. Instead it is better to maintain the scripts yourself in source code control system.|||Thanks.
A newbie needs hand holding and the GUI does that. I am sure eventually I will get more independent but until then...
Umachandar Jayachandran - MS wrote:
you should not rely on the scripting for your DDL. Instead it is better to maintain the scripts yourself in source code control system.
Let me get this straight:
After I execute the script, the only other time I need it is if I want to modify it or reuse it on another server. Otherwise, it is for informational purposes. For the info to be effective, the DDL code would have to be broken down into smaller files for granularity and a huge effort to duplicate the tree-like organization. No automation at all. And all this while the same already exists, but the code is munged.
What is it with MS and code munging by force? Didn't they get enough complaints about mutilating html/aspnet markup in VS1.x? That was a sheer nightmare!
What good does it do to add over 40 unnecessary bracket chars in just one line of code?
</rant>
Help with a loop.
I am writing a script that will go through all the database files on a
server, collect the file sizes and return the values in a single
table. This script works for the most part, but there is an instance
when the script fails to collect the information properly. When there
are two or more data files the script only reports the first one twice.
Can someone take a look at this loop and tell me where the error is?
Thanks
-Matt-
/ ****************************************
**********
Script to calculate information about the Data Files
****************************************
**********/
DECLARE @.dbname varchar(50)
DECLARE @.string varchar(250)
SET @.string = ''
Declare @.rows int
CREATE TABLE #dbcc_showfilestats (
fileid tinyint,
FileGroup1 tinyint,
TotalExtents1 decimal (28, 2),
UsedExtents1 decimal (28, 2),
Name varchar(50),
FileName sysname )
CREATE TABLE #dbstats (
DB_Name varchar(50),
DB_Total_Size_in_MB decimal (28, 2),
DB_Used_Size_in_MB decimal (28, 2),
DB_Free_Size_in_MB decimal (28, 2),
DB_Percent_Used decimal (28, 2))
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
-- Collects all the DB name
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @.dbname
WHILE (@.@.fetch_status = 0)
BEGIN
SET @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS'
INSERT #dbcc_showfilestats
EXEC (@.string)
SELECT * FROM #dbcc_showfilestats -- Debug
SELECT @.rows = count(*) from #dbcc_showfilestats
While @.rows > 0
BEGIN
INSERT #dbstats (DB_Name, DB_Total_Size_in_MB, DB_Used_Size_in_MB,
DB_Free_Size_in_MB, DB_Percent_Used)
SELECT @.dbname,
DB_Total_Size_in_MB = sum(TotalExtents1)*65536.0/1048576.0,
DB_Used_Size_in_MB = sum(UsedExtents1)*65536.0/1048576.0,
DB_Free_Size_in_MB =
sum(TotalExtents1-UsedExtents1)*65536.0/1048576.0,
DB_Percent_Used = sum(UsedExtents1/TotalExtents1)*100
FROM #dbcc_showfilestats
SELECT * FROM #dbstats
SET @.rows = @.rows - 1
END
TRUNCATE TABLE #dbcc_showfilestats
FETCH NEXT FROM dbnames_cursor INTO @.dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
SELECT * FROM #dbstats --Debug
DROP TABLE #dbstats --Debug
DROP TABLE #dbcc_showfilestats --DebugYou are selecting the same rows from #dbcc_showfilestats
every time through your 'while' loop.
Add
id int identity(1,1)
to your #dbcc_showfilestats table and change
FROM #dbcc_showfilestats
to
FROM #dbcc_showfilestats where id=@.rows|||Hi Matthew,
In addition to correctly adding a unique integer to distinguish rows in
your temp table as Mark has suggested, you may want to look at using
another temp table to loop through rather than using a cursor.
Cursors are very memory heavy in comparison to a looped through temp
table.
So instead your loop (in pseudo) would look more like:
-- SET UP 'CURSOR' TABLE
SELECT name INTO #databases FROM master..sysdatabases
-- DEFINE LOOPING PARAMETER
DECLARE @.unqName nvarchar(4000)
-- SELECT LOOPING PARAMETER
SELECT @.unqName = name FROM #databases
-- ENTER WHILE LOOP
WHILE LEN(@.unqName) > 0
BEGIN
-- PERFORM LOOP CODE
--DELETE ROW FROM LOOPING TABLE #databases
DELETE FROM #databases WHERE name = @.unqName
SELECT @.unqName = '' -- CLEAR VARIABLE
SELECT @.unqName = name FROM #databases
END
This will make a big difference in large looping scenarios - just try
it out.
Andrew La Grange
Business Artists
http://www.businessartists.co.za|||By doing the SUM(...), which is an aggregate function, you are only
saying you want 1 row.
What do you really want, the size and usage of each file? Or the size
of the entire database?
-Jeff|||If you want the entire database, then there is no need for a loop use
the following:
SELECT * FROM #dbcc_showfilestats -- Debug
INSERT #dbstats (DB_Name, DB_Total_Size_in_MB,
DB_Used_Size_in_MB,
DB_Free_Size_in_MB, DB_Percent_Used)
SELECT @.dbname,
DB_Total_Size_in_MB =
sum(TotalExtents1)*65536.0/1048576.0,
DB_Used_Size_in_MB =
sum(UsedExtents1)*65536.0/1048576.0,
DB_Free_Size_in_MB =
sum(TotalExtents1-UsedExtents1)*65536.0/1048576.0,
DB_Percent_Used =
(sum(UsedExtents1)/sum(TotalExtents1))*100
FROM #dbcc_showfilestats
SELECT * FROM #dbstats
TRUNCATE TABLE #dbcc_showfilestats
server, collect the file sizes and return the values in a single
table. This script works for the most part, but there is an instance
when the script fails to collect the information properly. When there
are two or more data files the script only reports the first one twice.
Can someone take a look at this loop and tell me where the error is?
Thanks
-Matt-
/ ****************************************
**********
Script to calculate information about the Data Files
****************************************
**********/
DECLARE @.dbname varchar(50)
DECLARE @.string varchar(250)
SET @.string = ''
Declare @.rows int
CREATE TABLE #dbcc_showfilestats (
fileid tinyint,
FileGroup1 tinyint,
TotalExtents1 decimal (28, 2),
UsedExtents1 decimal (28, 2),
Name varchar(50),
FileName sysname )
CREATE TABLE #dbstats (
DB_Name varchar(50),
DB_Total_Size_in_MB decimal (28, 2),
DB_Used_Size_in_MB decimal (28, 2),
DB_Free_Size_in_MB decimal (28, 2),
DB_Percent_Used decimal (28, 2))
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
-- Collects all the DB name
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @.dbname
WHILE (@.@.fetch_status = 0)
BEGIN
SET @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS'
INSERT #dbcc_showfilestats
EXEC (@.string)
SELECT * FROM #dbcc_showfilestats -- Debug
SELECT @.rows = count(*) from #dbcc_showfilestats
While @.rows > 0
BEGIN
INSERT #dbstats (DB_Name, DB_Total_Size_in_MB, DB_Used_Size_in_MB,
DB_Free_Size_in_MB, DB_Percent_Used)
SELECT @.dbname,
DB_Total_Size_in_MB = sum(TotalExtents1)*65536.0/1048576.0,
DB_Used_Size_in_MB = sum(UsedExtents1)*65536.0/1048576.0,
DB_Free_Size_in_MB =
sum(TotalExtents1-UsedExtents1)*65536.0/1048576.0,
DB_Percent_Used = sum(UsedExtents1/TotalExtents1)*100
FROM #dbcc_showfilestats
SELECT * FROM #dbstats
SET @.rows = @.rows - 1
END
TRUNCATE TABLE #dbcc_showfilestats
FETCH NEXT FROM dbnames_cursor INTO @.dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
SELECT * FROM #dbstats --Debug
DROP TABLE #dbstats --Debug
DROP TABLE #dbcc_showfilestats --DebugYou are selecting the same rows from #dbcc_showfilestats
every time through your 'while' loop.
Add
id int identity(1,1)
to your #dbcc_showfilestats table and change
FROM #dbcc_showfilestats
to
FROM #dbcc_showfilestats where id=@.rows|||Hi Matthew,
In addition to correctly adding a unique integer to distinguish rows in
your temp table as Mark has suggested, you may want to look at using
another temp table to loop through rather than using a cursor.
Cursors are very memory heavy in comparison to a looped through temp
table.
So instead your loop (in pseudo) would look more like:
-- SET UP 'CURSOR' TABLE
SELECT name INTO #databases FROM master..sysdatabases
-- DEFINE LOOPING PARAMETER
DECLARE @.unqName nvarchar(4000)
-- SELECT LOOPING PARAMETER
SELECT @.unqName = name FROM #databases
-- ENTER WHILE LOOP
WHILE LEN(@.unqName) > 0
BEGIN
-- PERFORM LOOP CODE
--DELETE ROW FROM LOOPING TABLE #databases
DELETE FROM #databases WHERE name = @.unqName
SELECT @.unqName = '' -- CLEAR VARIABLE
SELECT @.unqName = name FROM #databases
END
This will make a big difference in large looping scenarios - just try
it out.
Andrew La Grange
Business Artists
http://www.businessartists.co.za|||By doing the SUM(...), which is an aggregate function, you are only
saying you want 1 row.
What do you really want, the size and usage of each file? Or the size
of the entire database?
-Jeff|||If you want the entire database, then there is no need for a loop use
the following:
SELECT * FROM #dbcc_showfilestats -- Debug
INSERT #dbstats (DB_Name, DB_Total_Size_in_MB,
DB_Used_Size_in_MB,
DB_Free_Size_in_MB, DB_Percent_Used)
SELECT @.dbname,
DB_Total_Size_in_MB =
sum(TotalExtents1)*65536.0/1048576.0,
DB_Used_Size_in_MB =
sum(UsedExtents1)*65536.0/1048576.0,
DB_Free_Size_in_MB =
sum(TotalExtents1-UsedExtents1)*65536.0/1048576.0,
DB_Percent_Used =
(sum(UsedExtents1)/sum(TotalExtents1))*100
FROM #dbcc_showfilestats
SELECT * FROM #dbstats
TRUNCATE TABLE #dbcc_showfilestats
Help with a Log File Query.
Two questions
First, I need to insert the dbid into the table so I can cross
reference the log files with other data. That being said, I can't
seem to get the update field to work properly. As always, it is most
likely something pretty obvious that I am missing.
The second question. The "dbcc sqlperf(logspace) with no_infomsgs"
command returns the entire spaced used by all the log files attached to
that database. is there a command that will break it into the component
parts?
update #logstats set DBID=(select DBID
from master..sysdatabases where name = #logstats.DB_Name)
where exists (select * from master..sysdatabases where name =
#logstats.DB_Name)
"Matthew" <
MKruer@.gmail.com>
wrote in message
news:1143043015.896576.256210@.v46g2000cwv.googlegroups.com...
>
Two questions
>
>
First, I need to insert the dbid into the table so I can cross
>
reference the log files with other data. That being said, I can't
>
seem to get the update field to work properly. As always, it is most
>
likely something pretty obvious that I am missing.
>
>
The second question. The "dbcc sqlperf(logspace) with no_infomsgs"
>
command returns the entire spaced used by all the log files attached to
>
that database. is there a command that will break it into the component
>
parts?
>
>
>
First, I need to insert the dbid into the table so I can cross
reference the log files with other data. That being said, I can't
seem to get the update field to work properly. As always, it is most
likely something pretty obvious that I am missing.
The second question. The "dbcc sqlperf(logspace) with no_infomsgs"
command returns the entire spaced used by all the log files attached to
that database. is there a command that will break it into the component
parts?
Mattewcode:
/ ****************************************
**********
Script to calculate information about the Log Files
****************************************
**********/
CREATE TABLE #dbcc_sqlperf (
DB_Name varchar(50),
Log_Size decimal (28, 5),
Log_Used_Percent decimal (28, 5),
Status tinyint )
CREATE TABLE #logstats (
DBID tinyint,
DB_Name varchar(50),
Log_Total_Size_in_MB decimal (28, 2),
Log_Used_Size_in_MB decimal (28, 2),
Log_Free_Size_in_MB decimal (28, 2),
Log_Percent_Used decimal (28, 2))
INSERT #dbcc_sqlperf EXEC ('dbcc sqlperf(logspace) with no_infomsgs')
SELECT * FROM #dbcc_sqlperf --Debug
INSERT #logstats (DBID, DB_Name, Log_Total_Size_in_MB,
Log_Used_Size_in_MB, Log_Free_Size_in_MB, Log_Percent_Used)
SELECT DB_Name = DB_Name,
Log_Total_Size_in_MB = log_size,
Log_Used_Size_in_MB = sum
(log_size*(log_used_percent/100)),
Log_Free_Size_in_MB = sum (log_size
-(log_size*(log_used_percent/100))),
Log_Percent_Used = log_used_percent
FROM #dbcc_sqlperf
GROUP BY Log_Name, Log_Size, Log_Used_Percent, Status
update #logstats (DBID)
Select dbid = DBID
from master..sysdatabases where name = #logstats.DB_Name
SELECT * FROM #logstats --Debug
DROP TABLE #logstats
DROP TABLE #dbcc_sqlperf
update #logstats set DBID=(select DBID
from master..sysdatabases where name = #logstats.DB_Name)
where exists (select * from master..sysdatabases where name =
#logstats.DB_Name)
"Matthew" <
MKruer@.gmail.com>
wrote in message
news:1143043015.896576.256210@.v46g2000cwv.googlegroups.com...
>
Two questions
>
>
First, I need to insert the dbid into the table so I can cross
>
reference the log files with other data. That being said, I can't
>
seem to get the update field to work properly. As always, it is most
>
likely something pretty obvious that I am missing.
>
>
The second question. The "dbcc sqlperf(logspace) with no_infomsgs"
>
command returns the entire spaced used by all the log files attached to
>
that database. is there a command that will break it into the component
>
parts?
>
>
code:
>
/ ****************************************
**********
>
Script to calculate information about the Log Files
>
****************************************
**********/
>
>
CREATE TABLE #dbcc_sqlperf (
>
DB_Name varchar(50),
>
Log_Size decimal (28, 5),
>
Log_Used_Percent decimal (28, 5),
>
Status tinyint )
>
>
CREATE TABLE #logstats (
>
DBID tinyint,
>
DB_Name varchar(50),
>
Log_Total_Size_in_MB decimal (28, 2),
>
Log_Used_Size_in_MB decimal (28, 2),
>
Log_Free_Size_in_MB decimal (28, 2),
>
Log_Percent_Used decimal (28, 2))
>
>
INSERT #dbcc_sqlperf EXEC ('dbcc sqlperf(logspace) with no_infomsgs')
>
>
SELECT * FROM #dbcc_sqlperf --Debug
>
>
INSERT #logstats (DBID, DB_Name, Log_Total_Size_in_MB,
>
Log_Used_Size_in_MB, Log_Free_Size_in_MB, Log_Percent_Used)
>
SELECT DB_Name = DB_Name,
>
Log_Total_Size_in_MB = log_size,
>
Log_Used_Size_in_MB = sum
>
(log_size*(log_used_percent/100)),
>
Log_Free_Size_in_MB = sum (log_size
>
-(log_size*(log_used_percent/100))),
>
Log_Percent_Used = log_used_percent
>
FROM #dbcc_sqlperf
>
GROUP BY Log_Name, Log_Size, Log_Used_Percent, Status
>
>
update #logstats (DBID)
>
Select dbid = DBID
>
from master..sysdatabases where name = #logstats.DB_Name
>
>
SELECT * FROM #logstats --Debug
>
>
DROP TABLE #logstats
>
DROP TABLE #dbcc_sqlperf
>
>
Monday, March 12, 2012
Help With a Calculated Member Calculation
Hello,
I have a fact file that includes a measure called Credit Hours. I also have a dimension called DimTerm what includes time data for two terms: 05Fall and 06Fall. My goal is to get a number change between 05 and 06 terms. So I created a calculated member with the logic below but my percentage change is always %0.00. Maybe someone knows a way of doing this through MDX which would allow me to connct to Term values?
([Measures].[Credit Hours] - [Measures].[Credit Hours]) / [Measures].[Credit Hours]
Thanks!
Take a look at the ParallelPeriod function. Your calculated member will look something like
([Credit Hours] - ([Credit Hours], ParallelPeriod(...))) / [Credit Hours]
Help with a "select"
All of my customers are in a single data file, with one record per customer for each year that the customer bought something. (If customer A bought something in 1993, he would have a customer record in the file for that year. If he did not buy something in 2004 he would have no customer record for 2004 in the file.)
I want to "select" customer records from the file for which there is a 1993 record, but no 2004 record.
What would the select syntax be like?
Thanks in advance.Roughly:
Select * from Customers where Year(Fieldname) = '1993' and Customers.Cust_ID not in(select Cust_ID from Customers where Year(Fieldname) = '2004')
I want to "select" customer records from the file for which there is a 1993 record, but no 2004 record.
What would the select syntax be like?
Thanks in advance.Roughly:
Select * from Customers where Year(Fieldname) = '1993' and Customers.Cust_ID not in(select Cust_ID from Customers where Year(Fieldname) = '2004')
Help with "Problem generating manifest" error
Hi!
I'm using VS 2005 SP 1 on my first project with CE. I've recently run into the problem that if I do anything with my SDF file in Visual Studio I get the following error when I run the project (in debug mode from VS):
The process cannot access the file 'C:\xxx\MySDF.sdf' because it is being used by another process.
The solution is to close the project and reopen it however this is getting painful. Anyone else seen this problem?
Thanks!
This problem hasn't gone away....was hoping someone else has seen it and has a solution.
Thanks!
|||Do you have the connection open in Server Explorer or any other place. Basically as long as there is some connection open to that database, you can not delete it. However, you can open multiple connection to the same database. We support multiple connecitons.
Thanks,
Laxmi
Help with "Problem generating manifest" error
Hi!
I'm using VS 2005 SP 1 on my first project with CE. I've recently run into the problem that if I do anything with my SDF file in Visual Studio I get the following error when I run the project (in debug mode from VS):
The process cannot access the file 'C:\xxx\MySDF.sdf' because it is being used by another process.
The solution is to close the project and reopen it however this is getting painful. Anyone else seen this problem?
Thanks!
This problem hasn't gone away....was hoping someone else has seen it and has a solution.
Thanks!
|||Do you have the connection open in Server Explorer or any other place. Basically as long as there is some connection open to that database, you can not delete it. However, you can open multiple connection to the same database. We support multiple connecitons.
Thanks,
Laxmi
Friday, March 9, 2012
Help w/ Not enough storage is available to complete this operation.
I've got a delimited text file that aprox 5.5GB that I'm trying to
load into a table on SQL 2000 (Windows Server 2003) and I keep getting
the error "Not enough storage is available to complete this
operation."
Changing the commit size doesn't seem to make a difference, but when I
changed a couple the the varchar field sizes from less then 4 to 5,
helped. I checked with the admin on the server and was told all SP
and hotfixes were loaded.
The dang file used to load fine on SQL 7 (Windows Adv Server 2000)
when I used to run the same DTS there so it can't be a problem with
the text file.
I'm at a loss on what to try next. Anyone got any suggestions? Below
is the error log.
************************************************** **************************************************
The execution of the following DTS Package failed:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Package failed because Step
'DTSStep_DTSDataPumpTask_1' failed.
Error code: 80040428
\Error Help File:sqldts80.hlp
Error Help Context ID:700
Package Name: ADDRESS_TRANS_LOAD
Package Description: (null)
Package ID: {40416C9A-D5FA-4898-B8D6-4C7BE4359405}
Package Version: {DD97E07A-D00A-4B7D-8D57-BB0369932439}
Package Execution Lineage: {316E7F9E-E7B3-472B-A285-F38972E0FA11}
Executed On: PSQL0V2436771
Executed By: ADDR_jobs
Execution Started: 7/27/2007 11:19:02 PM
Execution Completed: 7/28/2007 3:13:07 AM
Total Execution Time: 14042.532 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS)
Package
Step Error Description:Not enough storage is available to complete
this operation.
(Microsoft Data Transformation Services (DTS) Data Pump (8007000e):
Not enough storage is available to complete this operation.
)
Step Error code: 8007000E
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Step Execution Started: 7/27/2007 11:19:03 PM
Step Execution Completed: 7/28/2007 3:13:07 AM
Total Step Execution Time: 14041.591 seconds
Progress count in Step: 8055000
Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 7/27/2007 11:19:02 PM
Step Execution Completed: 7/27/2007 11:19:03 PM
Total Step Execution Time: 0.591 seconds
Progress count in Step: 0
************************************************** **************************************************
BTW, it's not a limitation on the size of my DB.. I've still got over
20 GB of unused space allocated to me on the server farm.
BULK INSERT doesn't seem to work either. It says it completed
successfully but nothing ever gets inserted. Below is the BULK INSERT
command I was using... What the heck can be causing my problem?
************************************************** **************************************************
BULK INSERT address_trans
FROM '\\VPMY024210\WebData$\Proddata\address_trans.txt'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '~'
ROWTERMINATOR = '\r\n'
)
************************************************** **************************************************
I really need to get this data loaded... Someone PLEASE HELP!!
> BTW, it's not a limitation on the size of my DB.. I've still got over
> 20 GB of unused space allocated to me on the server farm.
How big is the file? How much space is available on C: and the data drive?
How much RAM is available? Have you tried loading from a local drive
instead of a \\share\? Your description sounds like you are using a hosted
SQL Server provided by someone. If so, have you brought the error to their
attention?
Aaron Bertrand
SQL Server MVP
|||On Jul 28, 1:43 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> How big is the file? How much space is available on C: and the data drive?
> How much RAM is available? Have you tried loading from a local drive
> instead of a \\share\? Your description sounds like you are using a hosted
> SQL Server provided by someone. If so, have you brought the error to their
> attention?
> --
> Aaron Bertrand
> SQL Server MVP
The file is 5.5GB and I've got 25GB allocated (Used 5GB) on the SQL
server. On the shared drive, where the file is located, I've got 70GB
allocated (used 20GB, leaving 50GB fee). I have no idea on the amount
of RAM is available on the SQL server, but on a guess, it's gotta have
over 8GB. 8GB of ram is their minimum spec for a server. I haven't
loaded from a local drive, due to user/security issues.
As for speaking with the admin, it wasn't much help. All I was told
was that all Hotfixes and SP were installed and to play with the
commit size.
load into a table on SQL 2000 (Windows Server 2003) and I keep getting
the error "Not enough storage is available to complete this
operation."
Changing the commit size doesn't seem to make a difference, but when I
changed a couple the the varchar field sizes from less then 4 to 5,
helped. I checked with the admin on the server and was told all SP
and hotfixes were loaded.
The dang file used to load fine on SQL 7 (Windows Adv Server 2000)
when I used to run the same DTS there so it can't be a problem with
the text file.
I'm at a loss on what to try next. Anyone got any suggestions? Below
is the error log.
************************************************** **************************************************
The execution of the following DTS Package failed:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Package failed because Step
'DTSStep_DTSDataPumpTask_1' failed.
Error code: 80040428
\Error Help File:sqldts80.hlp
Error Help Context ID:700
Package Name: ADDRESS_TRANS_LOAD
Package Description: (null)
Package ID: {40416C9A-D5FA-4898-B8D6-4C7BE4359405}
Package Version: {DD97E07A-D00A-4B7D-8D57-BB0369932439}
Package Execution Lineage: {316E7F9E-E7B3-472B-A285-F38972E0FA11}
Executed On: PSQL0V2436771
Executed By: ADDR_jobs
Execution Started: 7/27/2007 11:19:02 PM
Execution Completed: 7/28/2007 3:13:07 AM
Total Execution Time: 14042.532 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS)
Package
Step Error Description:Not enough storage is available to complete
this operation.
(Microsoft Data Transformation Services (DTS) Data Pump (8007000e):
Not enough storage is available to complete this operation.
)
Step Error code: 8007000E
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Step Execution Started: 7/27/2007 11:19:03 PM
Step Execution Completed: 7/28/2007 3:13:07 AM
Total Step Execution Time: 14041.591 seconds
Progress count in Step: 8055000
Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 7/27/2007 11:19:02 PM
Step Execution Completed: 7/27/2007 11:19:03 PM
Total Step Execution Time: 0.591 seconds
Progress count in Step: 0
************************************************** **************************************************
BTW, it's not a limitation on the size of my DB.. I've still got over
20 GB of unused space allocated to me on the server farm.
BULK INSERT doesn't seem to work either. It says it completed
successfully but nothing ever gets inserted. Below is the BULK INSERT
command I was using... What the heck can be causing my problem?
************************************************** **************************************************
BULK INSERT address_trans
FROM '\\VPMY024210\WebData$\Proddata\address_trans.txt'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '~'
ROWTERMINATOR = '\r\n'
)
************************************************** **************************************************
I really need to get this data loaded... Someone PLEASE HELP!!
> BTW, it's not a limitation on the size of my DB.. I've still got over
> 20 GB of unused space allocated to me on the server farm.
How big is the file? How much space is available on C: and the data drive?
How much RAM is available? Have you tried loading from a local drive
instead of a \\share\? Your description sounds like you are using a hosted
SQL Server provided by someone. If so, have you brought the error to their
attention?
Aaron Bertrand
SQL Server MVP
|||On Jul 28, 1:43 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> How big is the file? How much space is available on C: and the data drive?
> How much RAM is available? Have you tried loading from a local drive
> instead of a \\share\? Your description sounds like you are using a hosted
> SQL Server provided by someone. If so, have you brought the error to their
> attention?
> --
> Aaron Bertrand
> SQL Server MVP
The file is 5.5GB and I've got 25GB allocated (Used 5GB) on the SQL
server. On the shared drive, where the file is located, I've got 70GB
allocated (used 20GB, leaving 50GB fee). I have no idea on the amount
of RAM is available on the SQL server, but on a guess, it's gotta have
over 8GB. 8GB of ram is their minimum spec for a server. I haven't
loaded from a local drive, due to user/security issues.
As for speaking with the admin, it wasn't much help. All I was told
was that all Hotfixes and SP were installed and to play with the
commit size.
Help w/ Not enough storage is available to complete this operation.
I've got a delimited text file that aprox 5.5GB that I'm trying to
load into a table on SQL 2000 (Windows Server 2003) and I keep getting
the error "Not enough storage is available to complete this
operation."
Changing the commit size doesn't seem to make a difference, but when I
changed a couple the the varchar field sizes from less then 4 to 5,
helped. I checked with the admin on the server and was told all SP
and hotfixes were loaded.
The dang file used to load fine on SQL 7 (Windows Adv Server 2000)
when I used to run the same DTS there so it can't be a problem with
the text file.
I'm at a loss on what to try next. Anyone got any suggestions? Below
is the error log.
****************************************************************************************************
The execution of the following DTS Package failed:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Package failed because Step
'DTSStep_DTSDataPumpTask_1' failed.
Error code: 80040428
\Error Help File:sqldts80.hlp
Error Help Context ID:700
Package Name: ADDRESS_TRANS_LOAD
Package Description: (null)
Package ID: {40416C9A-D5FA-4898-B8D6-4C7BE4359405}
Package Version: {DD97E07A-D00A-4B7D-8D57-BB0369932439}
Package Execution Lineage: {316E7F9E-E7B3-472B-A285-F38972E0FA11}
Executed On: PSQL0V2436771
Executed By: ADDR_jobs
Execution Started: 7/27/2007 11:19:02 PM
Execution Completed: 7/28/2007 3:13:07 AM
Total Execution Time: 14042.532 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS)
Package
Step Error Description:Not enough storage is available to complete
this operation.
(Microsoft Data Transformation Services (DTS) Data Pump (8007000e):
Not enough storage is available to complete this operation.
)
Step Error code: 8007000E
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Step Execution Started: 7/27/2007 11:19:03 PM
Step Execution Completed: 7/28/2007 3:13:07 AM
Total Step Execution Time: 14041.591 seconds
Progress count in Step: 8055000
Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 7/27/2007 11:19:02 PM
Step Execution Completed: 7/27/2007 11:19:03 PM
Total Step Execution Time: 0.591 seconds
Progress count in Step: 0
****************************************************************************************************
BTW, it's not a limitation on the size of my DB.. I've still got over
20 GB of unused space allocated to me on the server farm.
BULK INSERT doesn't seem to work either. It says it completed
successfully but nothing ever gets inserted. Below is the BULK INSERT
command I was using... What the heck can be causing my problem?
****************************************************************************************************
BULK INSERT address_trans
FROM '\\VPMY024210\WebData$\Proddata\address_trans.txt'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '~'
ROWTERMINATOR = '\r\n'
)
****************************************************************************************************
I really need to get this data loaded... Someone PLEASE HELP!!> BTW, it's not a limitation on the size of my DB.. I've still got over
> 20 GB of unused space allocated to me on the server farm.
How big is the file? How much space is available on C: and the data drive?
How much RAM is available? Have you tried loading from a local drive
instead of a \\share\? Your description sounds like you are using a hosted
SQL Server provided by someone. If so, have you brought the error to their
attention?
--
Aaron Bertrand
SQL Server MVP|||On Jul 28, 1:43 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > BTW, it's not a limitation on the size of my DB.. I've still got over
> > 20 GB of unused space allocated to me on the server farm.
> How big is the file? How much space is available on C: and the data drive?
> How much RAM is available? Have you tried loading from a local drive
> instead of a \\share\? Your description sounds like you are using a hosted
> SQL Server provided by someone. If so, have you brought the error to their
> attention?
> --
> Aaron Bertrand
> SQL Server MVP
The file is 5.5GB and I've got 25GB allocated (Used 5GB) on the SQL
server. On the shared drive, where the file is located, I've got 70GB
allocated (used 20GB, leaving 50GB fee). I have no idea on the amount
of RAM is available on the SQL server, but on a guess, it's gotta have
over 8GB. 8GB of ram is their minimum spec for a server. I haven't
loaded from a local drive, due to user/security issues.
As for speaking with the admin, it wasn't much help. All I was told
was that all Hotfixes and SP were installed and to play with the
commit size.
load into a table on SQL 2000 (Windows Server 2003) and I keep getting
the error "Not enough storage is available to complete this
operation."
Changing the commit size doesn't seem to make a difference, but when I
changed a couple the the varchar field sizes from less then 4 to 5,
helped. I checked with the admin on the server and was told all SP
and hotfixes were loaded.
The dang file used to load fine on SQL 7 (Windows Adv Server 2000)
when I used to run the same DTS there so it can't be a problem with
the text file.
I'm at a loss on what to try next. Anyone got any suggestions? Below
is the error log.
****************************************************************************************************
The execution of the following DTS Package failed:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Package failed because Step
'DTSStep_DTSDataPumpTask_1' failed.
Error code: 80040428
\Error Help File:sqldts80.hlp
Error Help Context ID:700
Package Name: ADDRESS_TRANS_LOAD
Package Description: (null)
Package ID: {40416C9A-D5FA-4898-B8D6-4C7BE4359405}
Package Version: {DD97E07A-D00A-4B7D-8D57-BB0369932439}
Package Execution Lineage: {316E7F9E-E7B3-472B-A285-F38972E0FA11}
Executed On: PSQL0V2436771
Executed By: ADDR_jobs
Execution Started: 7/27/2007 11:19:02 PM
Execution Completed: 7/28/2007 3:13:07 AM
Total Execution Time: 14042.532 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS)
Package
Step Error Description:Not enough storage is available to complete
this operation.
(Microsoft Data Transformation Services (DTS) Data Pump (8007000e):
Not enough storage is available to complete this operation.
)
Step Error code: 8007000E
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Step Execution Started: 7/27/2007 11:19:03 PM
Step Execution Completed: 7/28/2007 3:13:07 AM
Total Step Execution Time: 14041.591 seconds
Progress count in Step: 8055000
Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 7/27/2007 11:19:02 PM
Step Execution Completed: 7/27/2007 11:19:03 PM
Total Step Execution Time: 0.591 seconds
Progress count in Step: 0
****************************************************************************************************
BTW, it's not a limitation on the size of my DB.. I've still got over
20 GB of unused space allocated to me on the server farm.
BULK INSERT doesn't seem to work either. It says it completed
successfully but nothing ever gets inserted. Below is the BULK INSERT
command I was using... What the heck can be causing my problem?
****************************************************************************************************
BULK INSERT address_trans
FROM '\\VPMY024210\WebData$\Proddata\address_trans.txt'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '~'
ROWTERMINATOR = '\r\n'
)
****************************************************************************************************
I really need to get this data loaded... Someone PLEASE HELP!!> BTW, it's not a limitation on the size of my DB.. I've still got over
> 20 GB of unused space allocated to me on the server farm.
How big is the file? How much space is available on C: and the data drive?
How much RAM is available? Have you tried loading from a local drive
instead of a \\share\? Your description sounds like you are using a hosted
SQL Server provided by someone. If so, have you brought the error to their
attention?
--
Aaron Bertrand
SQL Server MVP|||On Jul 28, 1:43 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > BTW, it's not a limitation on the size of my DB.. I've still got over
> > 20 GB of unused space allocated to me on the server farm.
> How big is the file? How much space is available on C: and the data drive?
> How much RAM is available? Have you tried loading from a local drive
> instead of a \\share\? Your description sounds like you are using a hosted
> SQL Server provided by someone. If so, have you brought the error to their
> attention?
> --
> Aaron Bertrand
> SQL Server MVP
The file is 5.5GB and I've got 25GB allocated (Used 5GB) on the SQL
server. On the shared drive, where the file is located, I've got 70GB
allocated (used 20GB, leaving 50GB fee). I have no idea on the amount
of RAM is available on the SQL server, but on a guess, it's gotta have
over 8GB. 8GB of ram is their minimum spec for a server. I haven't
loaded from a local drive, due to user/security issues.
As for speaking with the admin, it wasn't much help. All I was told
was that all Hotfixes and SP were installed and to play with the
commit size.
Help w/ Not enough storage is available to complete this operation.
I've got a delimited text file that aprox 5.5GB that I'm trying to
load into a table on SQL 2000 (Windows Server 2003) and I keep getting
the error "Not enough storage is available to complete this
operation."
Changing the commit size doesn't seem to make a difference, but when I
changed a couple the the varchar field sizes from less then 4 to 5,
helped. I checked with the admin on the server and was told all SP
and hotfixes were loaded.
The dang file used to load fine on SQL 7 (Windows Adv Server 2000)
when I used to run the same DTS there so it can't be a problem with
the text file.
I'm at a loss on what to try next. Anyone got any suggestions? Below
is the error log.
****************************************
************************************
************************
The execution of the following DTS Package failed:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Package failed because Step
'DTSStep_DTSDataPumpTask_1' failed.
Error code: 80040428
\Error Help File:sqldts80.hlp
Error Help Context ID:700
Package Name: ADDRESS_TRANS_LOAD
Package Description: (null)
Package ID: {40416C9A-D5FA-4898-B8D6-4C7BE4359405}
Package Version: {DD97E07A-D00A-4B7D-8D57-BB0369932439}
Package Execution Lineage: {316E7F9E-E7B3-472B-A285-F38972E0FA11}
Executed On: PSQL0V2436771
Executed By: ADDR_jobs
Execution Started: 7/27/2007 11:19:02 PM
Execution Completed: 7/28/2007 3:13:07 AM
Total Execution Time: 14042.532 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS)
Package
Step Error Description:Not enough storage is available to complete
this operation.
(Microsoft Data Transformation Services (DTS) Data Pump (8007000e):
Not enough storage is available to complete this operation.
)
Step Error code: 8007000E
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Step Execution Started: 7/27/2007 11:19:03 PM
Step Execution Completed: 7/28/2007 3:13:07 AM
Total Step Execution Time: 14041.591 seconds
Progress count in Step: 8055000
Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 7/27/2007 11:19:02 PM
Step Execution Completed: 7/27/2007 11:19:03 PM
Total Step Execution Time: 0.591 seconds
Progress count in Step: 0
****************************************
************************************
************************
BTW, it's not a limitation on the size of my DB.. I've still got over
20 GB of unused space allocated to me on the server farm.
BULK INSERT doesn't seem to work either. It says it completed
successfully but nothing ever gets inserted. Below is the BULK INSERT
command I was using... What the heck can be causing my problem?
****************************************
************************************
************************
BULK INSERT address_trans
FROM '\\VPMY024210\WebData$\Proddata\address_
trans.txt'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '~'
ROWTERMINATOR = '\r\n'
)
****************************************
************************************
************************
I really need to get this data loaded... Someone PLEASE HELP!!> BTW, it's not a limitation on the size of my DB.. I've still got over
> 20 GB of unused space allocated to me on the server farm.
How big is the file? How much space is available on C: and the data drive?
How much RAM is available? Have you tried loading from a local drive
instead of a \\share\? Your description sounds like you are using a hosted
SQL Server provided by someone. If so, have you brought the error to their
attention?
Aaron Bertrand
SQL Server MVP|||On Jul 28, 1:43 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> How big is the file? How much space is available on C: and the data drive
?
> How much RAM is available? Have you tried loading from a local drive
> instead of a \\share\? Your description sounds like you are using a hoste
d
> SQL Server provided by someone. If so, have you brought the error to thei
r
> attention?
> --
> Aaron Bertrand
> SQL Server MVP
The file is 5.5GB and I've got 25GB allocated (Used 5GB) on the SQL
server. On the shared drive, where the file is located, I've got 70GB
allocated (used 20GB, leaving 50GB fee). I have no idea on the amount
of RAM is available on the SQL server, but on a guess, it's gotta have
over 8GB. 8GB of ram is their minimum spec for a server. I haven't
loaded from a local drive, due to user/security issues.
As for speaking with the admin, it wasn't much help. All I was told
was that all Hotfixes and SP were installed and to play with the
commit size.
load into a table on SQL 2000 (Windows Server 2003) and I keep getting
the error "Not enough storage is available to complete this
operation."
Changing the commit size doesn't seem to make a difference, but when I
changed a couple the the varchar field sizes from less then 4 to 5,
helped. I checked with the admin on the server and was told all SP
and hotfixes were loaded.
The dang file used to load fine on SQL 7 (Windows Adv Server 2000)
when I used to run the same DTS there so it can't be a problem with
the text file.
I'm at a loss on what to try next. Anyone got any suggestions? Below
is the error log.
****************************************
************************************
************************
The execution of the following DTS Package failed:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Package failed because Step
'DTSStep_DTSDataPumpTask_1' failed.
Error code: 80040428
\Error Help File:sqldts80.hlp
Error Help Context ID:700
Package Name: ADDRESS_TRANS_LOAD
Package Description: (null)
Package ID: {40416C9A-D5FA-4898-B8D6-4C7BE4359405}
Package Version: {DD97E07A-D00A-4B7D-8D57-BB0369932439}
Package Execution Lineage: {316E7F9E-E7B3-472B-A285-F38972E0FA11}
Executed On: PSQL0V2436771
Executed By: ADDR_jobs
Execution Started: 7/27/2007 11:19:02 PM
Execution Completed: 7/28/2007 3:13:07 AM
Total Execution Time: 14042.532 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS)
Package
Step Error Description:Not enough storage is available to complete
this operation.
(Microsoft Data Transformation Services (DTS) Data Pump (8007000e):
Not enough storage is available to complete this operation.
)
Step Error code: 8007000E
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Step Execution Started: 7/27/2007 11:19:03 PM
Step Execution Completed: 7/28/2007 3:13:07 AM
Total Step Execution Time: 14041.591 seconds
Progress count in Step: 8055000
Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 7/27/2007 11:19:02 PM
Step Execution Completed: 7/27/2007 11:19:03 PM
Total Step Execution Time: 0.591 seconds
Progress count in Step: 0
****************************************
************************************
************************
BTW, it's not a limitation on the size of my DB.. I've still got over
20 GB of unused space allocated to me on the server farm.
BULK INSERT doesn't seem to work either. It says it completed
successfully but nothing ever gets inserted. Below is the BULK INSERT
command I was using... What the heck can be causing my problem?
****************************************
************************************
************************
BULK INSERT address_trans
FROM '\\VPMY024210\WebData$\Proddata\address_
trans.txt'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '~'
ROWTERMINATOR = '\r\n'
)
****************************************
************************************
************************
I really need to get this data loaded... Someone PLEASE HELP!!> BTW, it's not a limitation on the size of my DB.. I've still got over
> 20 GB of unused space allocated to me on the server farm.
How big is the file? How much space is available on C: and the data drive?
How much RAM is available? Have you tried loading from a local drive
instead of a \\share\? Your description sounds like you are using a hosted
SQL Server provided by someone. If so, have you brought the error to their
attention?
Aaron Bertrand
SQL Server MVP|||On Jul 28, 1:43 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> How big is the file? How much space is available on C: and the data drive
?
> How much RAM is available? Have you tried loading from a local drive
> instead of a \\share\? Your description sounds like you are using a hoste
d
> SQL Server provided by someone. If so, have you brought the error to thei
r
> attention?
> --
> Aaron Bertrand
> SQL Server MVP
The file is 5.5GB and I've got 25GB allocated (Used 5GB) on the SQL
server. On the shared drive, where the file is located, I've got 70GB
allocated (used 20GB, leaving 50GB fee). I have no idea on the amount
of RAM is available on the SQL server, but on a guess, it's gotta have
over 8GB. 8GB of ram is their minimum spec for a server. I haven't
loaded from a local drive, due to user/security issues.
As for speaking with the admin, it wasn't much help. All I was told
was that all Hotfixes and SP were installed and to play with the
commit size.
Help Using VBScript in an ActiveX task in DTS Package
Hi,
Thanks for reading.
I am creating a DTS package to import a .txt file into sql. I have everything in place, but the text file needs to have the last record deleted before the import. I need help with this part
I would like to delete the last record from a fixed width text file before I import it into sql. The number of rows will vary from file to file.
Can any one offer suggestions on the best way to do this.
I understand that I have to use the FSO to open and read the file, but I am not sure the best way to proceed after that.
Thanks in advance,
SteveThere are a couple easy ways to do this that I can think of:
1) Open the file up before import and delete the last record, then import to SQL Server.
2) Import to a temporary table that has a IDENTITY field in it, then delete the row with the highest value, then import to normal table.
3) If you want to delete the last line because it's an abnormal line (not a suitable record to go into the db), then just allow a certain number of errors. This way it'll basically error out without inserting the line.
The 1st solution needs the VBScript you're looking for. The problem I think with that is that the TextStream Object that you're looking for is a forward only object. This means that you'd have to open it, read each line at a time keeping track of which line you were on with some sort of local variable, then identify when you've reached the end of the file. Then you'd have to close the file, open it again, then read the file till you got to the last line (which you'd now know was the last line because of your local variable(s) that you initialized last time. Then you could delete that line. Here's the link for documentationhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/jsobjtextstream.asp
The 2nd solution doesn't involve any VBScript, and would probably be simpler to explain and troubleshoot.
The 3rd solution was just a possible guess at what you're trying to do.
David
Wednesday, March 7, 2012
Help Updating Tables From TAB File
Hi All
Im Really New To SQL Server and need to do the folowing - if anyone can help out i will be VERY happy :)
i have a single table in sql server containing products im selling
withing that table is 2 fields that i need to be able to update without changing any of the other fields in that line. (i need to be able to update price and stock field)
there is a field that is unique (field with the barcode)
the data i need to import is from a TAB text file which contains the barcode, stock and price field
hope this makes sense and if anyone can help thats great
cheers
BenHowdy
You can use DTS to import data which will suck data in from the tab file. However, unless you have a bit of experience with SQL its probably worth trying this on some dummy tables to get it right.
You could use DTS to import the tab data into a new table. Then write some code to work your way through the new table to update the existing production table.
If you wanted to update a column based on a unique column value in a table you could use:
Update <tabel_name>
set <column_to_be_updated> = '<some_value>'
where <unique_column> = '<some_value>'
i.e.
update table_product_data
set saleprice = '100'
where barcode = '097364543'
This ensures only the saleprice column in the table is changed where the barcode column = some unique value. That way you can selectively target a row in a particular column based on a value in the barcode column on the same row. Make sure that the barcode column ( or which ever column you use as the primary key for the table ) has unique values, otherwise you may get multiple rows in the saleprice column being updated at the same time.
Post back if probs.
Cheers,
SG.|||thanks for the reply
the problem is i am cool getting it to update 1 record its getting it to run thro a massive tab file and updating about 1500 prices
any suggestions for a thick person ?
cheers
ben|||right i have realised how to do this now - if anyone can give me some example code for in sql to loop thro i have made it import into a new table and i under stand the code you put before but im not sure how to make it loop thro
hope that makes sense!
cheers
ben|||Hi there!
Here I can give you a little example code. Let's say that:
1. "original" - is the table in the database
2. "new" - is the table which has been filled with the data out of the TAB file
update original
set original.stock = new.stock,
original.price = new.price
from original
,new
where original.bar_code_id = new.bar_code_id
Hope that helps you further. If not, post a reply!
Greetings,
Carsten
NOTE: This will not bring any new bar_code_id into "original"! It just updates existing id's|||thats great thanks
will that loop thro each result in the table new ?
thanks for the help
Ben|||you both rule!
its all working now:D:D - you cant believe how happy i am
please both check you private messages
thansk ben
Im Really New To SQL Server and need to do the folowing - if anyone can help out i will be VERY happy :)
i have a single table in sql server containing products im selling
withing that table is 2 fields that i need to be able to update without changing any of the other fields in that line. (i need to be able to update price and stock field)
there is a field that is unique (field with the barcode)
the data i need to import is from a TAB text file which contains the barcode, stock and price field
hope this makes sense and if anyone can help thats great
cheers
BenHowdy
You can use DTS to import data which will suck data in from the tab file. However, unless you have a bit of experience with SQL its probably worth trying this on some dummy tables to get it right.
You could use DTS to import the tab data into a new table. Then write some code to work your way through the new table to update the existing production table.
If you wanted to update a column based on a unique column value in a table you could use:
Update <tabel_name>
set <column_to_be_updated> = '<some_value>'
where <unique_column> = '<some_value>'
i.e.
update table_product_data
set saleprice = '100'
where barcode = '097364543'
This ensures only the saleprice column in the table is changed where the barcode column = some unique value. That way you can selectively target a row in a particular column based on a value in the barcode column on the same row. Make sure that the barcode column ( or which ever column you use as the primary key for the table ) has unique values, otherwise you may get multiple rows in the saleprice column being updated at the same time.
Post back if probs.
Cheers,
SG.|||thanks for the reply
the problem is i am cool getting it to update 1 record its getting it to run thro a massive tab file and updating about 1500 prices
any suggestions for a thick person ?
cheers
ben|||right i have realised how to do this now - if anyone can give me some example code for in sql to loop thro i have made it import into a new table and i under stand the code you put before but im not sure how to make it loop thro
hope that makes sense!
cheers
ben|||Hi there!
Here I can give you a little example code. Let's say that:
1. "original" - is the table in the database
2. "new" - is the table which has been filled with the data out of the TAB file
update original
set original.stock = new.stock,
original.price = new.price
from original
,new
where original.bar_code_id = new.bar_code_id
Hope that helps you further. If not, post a reply!
Greetings,
Carsten
NOTE: This will not bring any new bar_code_id into "original"! It just updates existing id's|||thats great thanks
will that loop thro each result in the table new ?
thanks for the help
Ben|||you both rule!
its all working now:D:D - you cant believe how happy i am
please both check you private messages
thansk ben
help to remove old .bak file
I have an old .bak file that is not being removed via our maitenenance plan
backups. When I try to manually remove it, it says 'file in use'. How do
determine what is holding this file open?
The last time the job ran it failed.
We run daily backups and I see current backups and am able to remove some of
the more recent ones manually.
Take a look at EM - Current Activity to see if the backup step is still
there. Try KILLing it if it is.
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.
|||Thanks for the suggestion, but I already tried that. There was only tran log
going on for different db. I killed it anyway just to make sure and tried to
remove file but still same error. My maintenance window is not until
weekend and I am trying to avoid bouncing server. I can manually clean up
files until then but would prefer if there is another solution. Is there
any what to determine what is holding the file open?
"Jerry Spivey" wrote:
> Take a look at EM - Current Activity to see if the backup step is still
> there. Try KILLing it if it is.
> HTH
> Jerry
> "DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
> news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>
>
|||Depending on how the maint plans are implemented you might check the
Processes tab in Task Manager. Also, do you need to bounce the server or
just the SQL Server/Agent services? Also, why not wait til off-peak hours
to stop - delete the file?
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...[vbcol=seagreen]
> Thanks for the suggestion, but I already tried that. There was only tran
> log
> going on for different db. I killed it anyway just to make sure and tried
> to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean
> up
> files until then but would prefer if there is another solution. Is
> there
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
|||I believe that http://www.sysinternals.com/ has tools for that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...[vbcol=seagreen]
> Thanks for the suggestion, but I already tried that. There was only tran log
> going on for different db. I killed it anyway just to make sure and tried to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean up
> files until then but would prefer if there is another solution. Is there
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
|||looks at NTHandle from http://www.Sysinternals.com
cheerrs,
Andy
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.
backups. When I try to manually remove it, it says 'file in use'. How do
determine what is holding this file open?
The last time the job ran it failed.
We run daily backups and I see current backups and am able to remove some of
the more recent ones manually.
Take a look at EM - Current Activity to see if the backup step is still
there. Try KILLing it if it is.
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.
|||Thanks for the suggestion, but I already tried that. There was only tran log
going on for different db. I killed it anyway just to make sure and tried to
remove file but still same error. My maintenance window is not until
weekend and I am trying to avoid bouncing server. I can manually clean up
files until then but would prefer if there is another solution. Is there
any what to determine what is holding the file open?
"Jerry Spivey" wrote:
> Take a look at EM - Current Activity to see if the backup step is still
> there. Try KILLing it if it is.
> HTH
> Jerry
> "DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
> news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>
>
|||Depending on how the maint plans are implemented you might check the
Processes tab in Task Manager. Also, do you need to bounce the server or
just the SQL Server/Agent services? Also, why not wait til off-peak hours
to stop - delete the file?
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...[vbcol=seagreen]
> Thanks for the suggestion, but I already tried that. There was only tran
> log
> going on for different db. I killed it anyway just to make sure and tried
> to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean
> up
> files until then but would prefer if there is another solution. Is
> there
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
|||I believe that http://www.sysinternals.com/ has tools for that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...[vbcol=seagreen]
> Thanks for the suggestion, but I already tried that. There was only tran log
> going on for different db. I killed it anyway just to make sure and tried to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean up
> files until then but would prefer if there is another solution. Is there
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
|||looks at NTHandle from http://www.Sysinternals.com
cheerrs,
Andy
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.
help to remove old .bak file
I have an old .bak file that is not being removed via our maitenenance plan
backups. When I try to manually remove it, it says 'file in use'. How do
determine what is holding this file open?
The last time the job ran it failed.
We run daily backups and I see current backups and am able to remove some of
the more recent ones manually.Take a look at EM - Current Activity to see if the backup step is still
there. Try KILLing it if it is.
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.|||Thanks for the suggestion, but I already tried that. There was only tran lo
g
going on for different db. I killed it anyway just to make sure and tried t
o
remove file but still same error. My maintenance window is not until
weekend and I am trying to avoid bouncing server. I can manually clean up
files until then but would prefer if there is another solution. Is there
any what to determine what is holding the file open?
"Jerry Spivey" wrote:
> Take a look at EM - Current Activity to see if the backup step is still
> there. Try KILLing it if it is.
> HTH
> Jerry
> "DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
> news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>
>|||Depending on how the maint plans are implemented you might check the
Processes tab in Task Manager. Also, do you need to bounce the server or
just the SQL Server/Agent services? Also, why not wait til off-peak hours
to stop - delete the file?
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...[vbcol=seagreen]
> Thanks for the suggestion, but I already tried that. There was only tran
> log
> going on for different db. I killed it anyway just to make sure and tried
> to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean
> up
> files until then but would prefer if there is another solution. Is
> there
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
>|||I believe that http://www.sysinternals.com/ has tools for that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...[vbcol=seagreen]
> Thanks for the suggestion, but I already tried that. There was only tran
log
> going on for different db. I killed it anyway just to make sure and tried
to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean u
p
> files until then but would prefer if there is another solution. Is the
re
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
>|||looks at NTHandle from http://www.Sysinternals.com
cheerrs,
Andy
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.
backups. When I try to manually remove it, it says 'file in use'. How do
determine what is holding this file open?
The last time the job ran it failed.
We run daily backups and I see current backups and am able to remove some of
the more recent ones manually.Take a look at EM - Current Activity to see if the backup step is still
there. Try KILLing it if it is.
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.|||Thanks for the suggestion, but I already tried that. There was only tran lo
g
going on for different db. I killed it anyway just to make sure and tried t
o
remove file but still same error. My maintenance window is not until
weekend and I am trying to avoid bouncing server. I can manually clean up
files until then but would prefer if there is another solution. Is there
any what to determine what is holding the file open?
"Jerry Spivey" wrote:
> Take a look at EM - Current Activity to see if the backup step is still
> there. Try KILLing it if it is.
> HTH
> Jerry
> "DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
> news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>
>|||Depending on how the maint plans are implemented you might check the
Processes tab in Task Manager. Also, do you need to bounce the server or
just the SQL Server/Agent services? Also, why not wait til off-peak hours
to stop - delete the file?
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...[vbcol=seagreen]
> Thanks for the suggestion, but I already tried that. There was only tran
> log
> going on for different db. I killed it anyway just to make sure and tried
> to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean
> up
> files until then but would prefer if there is another solution. Is
> there
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
>|||I believe that http://www.sysinternals.com/ has tools for that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...[vbcol=seagreen]
> Thanks for the suggestion, but I already tried that. There was only tran
log
> going on for different db. I killed it anyway just to make sure and tried
to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean u
p
> files until then but would prefer if there is another solution. Is the
re
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
>|||looks at NTHandle from http://www.Sysinternals.com
cheerrs,
Andy
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.
help to remove old .bak file
I have an old .bak file that is not being removed via our maitenenance plan
backups. When I try to manually remove it, it says 'file in use'. How do
determine what is holding this file open?
The last time the job ran it failed.
We run daily backups and I see current backups and am able to remove some of
the more recent ones manually.Take a look at EM - Current Activity to see if the backup step is still
there. Try KILLing it if it is.
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.|||Thanks for the suggestion, but I already tried that. There was only tran log
going on for different db. I killed it anyway just to make sure and tried to
remove file but still same error. My maintenance window is not until
weekend and I am trying to avoid bouncing server. I can manually clean up
files until then but would prefer if there is another solution. Is there
any what to determine what is holding the file open?
"Jerry Spivey" wrote:
> Take a look at EM - Current Activity to see if the backup step is still
> there. Try KILLing it if it is.
> HTH
> Jerry
> "DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
> news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
> >I have an old .bak file that is not being removed via our maitenenance plan
> > backups. When I try to manually remove it, it says 'file in use'. How
> > do
> > determine what is holding this file open?
> >
> > The last time the job ran it failed.
> >
> > We run daily backups and I see current backups and am able to remove some
> > of
> > the more recent ones manually.
>
>|||Depending on how the maint plans are implemented you might check the
Processes tab in Task Manager. Also, do you need to bounce the server or
just the SQL Server/Agent services? Also, why not wait til off-peak hours
to stop - delete the file?
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...
> Thanks for the suggestion, but I already tried that. There was only tran
> log
> going on for different db. I killed it anyway just to make sure and tried
> to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean
> up
> files until then but would prefer if there is another solution. Is
> there
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
>> Take a look at EM - Current Activity to see if the backup step is still
>> there. Try KILLing it if it is.
>> HTH
>> Jerry
>> "DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
>> news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>> >I have an old .bak file that is not being removed via our maitenenance
>> >plan
>> > backups. When I try to manually remove it, it says 'file in use'.
>> > How
>> > do
>> > determine what is holding this file open?
>> >
>> > The last time the job ran it failed.
>> >
>> > We run daily backups and I see current backups and am able to remove
>> > some
>> > of
>> > the more recent ones manually.
>>|||I believe that http://www.sysinternals.com/ has tools for that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...
> Thanks for the suggestion, but I already tried that. There was only tran log
> going on for different db. I killed it anyway just to make sure and tried to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean up
> files until then but would prefer if there is another solution. Is there
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
>> Take a look at EM - Current Activity to see if the backup step is still
>> there. Try KILLing it if it is.
>> HTH
>> Jerry
>> "DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
>> news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>> >I have an old .bak file that is not being removed via our maitenenance plan
>> > backups. When I try to manually remove it, it says 'file in use'. How
>> > do
>> > determine what is holding this file open?
>> >
>> > The last time the job ran it failed.
>> >
>> > We run daily backups and I see current backups and am able to remove some
>> > of
>> > the more recent ones manually.
>>|||looks at NTHandle from http://www.Sysinternals.com
cheerrs,
Andy
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.
backups. When I try to manually remove it, it says 'file in use'. How do
determine what is holding this file open?
The last time the job ran it failed.
We run daily backups and I see current backups and am able to remove some of
the more recent ones manually.Take a look at EM - Current Activity to see if the backup step is still
there. Try KILLing it if it is.
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.|||Thanks for the suggestion, but I already tried that. There was only tran log
going on for different db. I killed it anyway just to make sure and tried to
remove file but still same error. My maintenance window is not until
weekend and I am trying to avoid bouncing server. I can manually clean up
files until then but would prefer if there is another solution. Is there
any what to determine what is holding the file open?
"Jerry Spivey" wrote:
> Take a look at EM - Current Activity to see if the backup step is still
> there. Try KILLing it if it is.
> HTH
> Jerry
> "DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
> news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
> >I have an old .bak file that is not being removed via our maitenenance plan
> > backups. When I try to manually remove it, it says 'file in use'. How
> > do
> > determine what is holding this file open?
> >
> > The last time the job ran it failed.
> >
> > We run daily backups and I see current backups and am able to remove some
> > of
> > the more recent ones manually.
>
>|||Depending on how the maint plans are implemented you might check the
Processes tab in Task Manager. Also, do you need to bounce the server or
just the SQL Server/Agent services? Also, why not wait til off-peak hours
to stop - delete the file?
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...
> Thanks for the suggestion, but I already tried that. There was only tran
> log
> going on for different db. I killed it anyway just to make sure and tried
> to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean
> up
> files until then but would prefer if there is another solution. Is
> there
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
>> Take a look at EM - Current Activity to see if the backup step is still
>> there. Try KILLing it if it is.
>> HTH
>> Jerry
>> "DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
>> news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>> >I have an old .bak file that is not being removed via our maitenenance
>> >plan
>> > backups. When I try to manually remove it, it says 'file in use'.
>> > How
>> > do
>> > determine what is holding this file open?
>> >
>> > The last time the job ran it failed.
>> >
>> > We run daily backups and I see current backups and am able to remove
>> > some
>> > of
>> > the more recent ones manually.
>>|||I believe that http://www.sysinternals.com/ has tools for that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...
> Thanks for the suggestion, but I already tried that. There was only tran log
> going on for different db. I killed it anyway just to make sure and tried to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean up
> files until then but would prefer if there is another solution. Is there
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
>> Take a look at EM - Current Activity to see if the backup step is still
>> there. Try KILLing it if it is.
>> HTH
>> Jerry
>> "DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
>> news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>> >I have an old .bak file that is not being removed via our maitenenance plan
>> > backups. When I try to manually remove it, it says 'file in use'. How
>> > do
>> > determine what is holding this file open?
>> >
>> > The last time the job ran it failed.
>> >
>> > We run daily backups and I see current backups and am able to remove some
>> > of
>> > the more recent ones manually.
>>|||looks at NTHandle from http://www.Sysinternals.com
cheerrs,
Andy
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.
Subscribe to:
Posts (Atom)