filter for SQL commad ?

I dont know to write SQL command filter.
m_strQueryDelete.Format("DELETE FROM tab WHERE (Col1 = abc AND Col2
= abc AND ??)"
example: ODBC found 100 records.
I need to delete first 90 records and to leave last 10 records.
Thank you
Joseph
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Client-filte...ict259609.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=894697
Is there a unique field in the table, where you could write a query
like:
DELETE FROM TAB WHERE UNIQUE_FIELD IN
(SELECT TOP 90 UNIQUE_FIELD FROM TAB WHERE (COL1= 'ABC' AND COL2='ABC'
AND ??))
Good luck,
Tony Sebion
"Joseph" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_894697_c2b2ed0878532c267d72ee72a1e9aff5@.dbf orumz.com:

> I don't know to write SQL command filter.
> m_strQueryDelete.Format("DELETE FROM tab WHERE (Col1 = 'abc' AND Col2
> = 'abc' AND ??)"
> example: ODBC found 100 records.
> I need to delete first 90 records and to leave last 10 records.
> Thank you
> Joseph
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL: http://www.dbforumz.com/Client-filte...ict259609.html
> Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=894697
sql

Fill stops /timeouts

Dear Group,

I am tring to use a command that calls the server to fill an adapter, it
never seems to get to the adapter, command and the server either times out
or does not respond. The timeout is set at 10 hours. I am using Visual
Studio to acces MS SQL - Server.

I think I have all the rights and permissions set correctly. Also, I have
used this command to fill other adapters and tables.

Does anyone have a suggestion.

Jeff MagouirkJeff Magouirk (magouirkj@.njc.org) writes:
> I am tring to use a command that calls the server to fill an adapter, it
> never seems to get to the adapter, command and the server either times out
> or does not respond. The timeout is set at 10 hours. I am using Visual
> Studio to acces MS SQL - Server.
> I think I have all the rights and permissions set correctly. Also, I have
> used this command to fill other adapters and tables.
> Does anyone have a suggestion.

I am afraid that the amount of detail in your post is too small for a
good answer to be possible.

One trivial reason could be blocking. You can run sp_who to see if you
have any blocking. If there is a non-zero value in th Blk column, then
that process is blocking the spid for this row.

If the query really takes ten hours to run - then you have some tuning
to do!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

filestats

Hi ,
In 2k there is dbcc command dbcc showfilestats to see used & free extents in
filegroup , what is equivalent in Yukon or shall we continue using the same
..
Thanks
ARR
check out sys.dm.db.index_physical_stats in BooksOnLine.
Andrew J. Kelly SQL MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
> Hi ,
> In 2k there is dbcc command dbcc showfilestats to see used & free extents
> in
> filegroup , what is equivalent in Yukon or shall we continue using the
> same
> .
> Thanks
> ARR
>
|||sys.dm_db_index_physical_stats does not report per file does it? Only per
object.
select fileproperty('filename', 'SpaceUsed') reports the amount of space
allocated but not free.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
> check out sys.dm.db.index_physical_stats in BooksOnLine.
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
>
|||Sorry, the select fileproperty('filename', 'SpaceUsed') is the equivalent of
dbcc showfilestats column UsedExtents * 8
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> sys.dm_db_index_physical_stats does not report per file does it? Only per
> object.
> select fileproperty('filename', 'SpaceUsed') reports the amount of space
> allocated but not free.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
>
|||And the equivalent of TotalExtents can be found in: select size from
sys.database_files or select size from sys.master_files
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:eYFWV8uAFHA.3336@.TK2MSFTNGP11.phx.gbl...
> Sorry, the select fileproperty('filename', 'SpaceUsed') is the equivalent
> of dbcc showfilestats column UsedExtents * 8
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
>
|||Sorry I was thinking showcontig<g>.
Andrew J. Kelly SQL MVP
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> sys.dm_db_index_physical_stats does not report per file does it? Only per
> object.
> select fileproperty('filename', 'SpaceUsed') reports the amount of space
> allocated but not free.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
>
|||Last one to wrap this up, this would be the ultimate Yukon way of retrieving
the data today you get from showfilestats (which will remains an
undocumented DBCC command in Yukon as well).
select 'FileId' = file_id,
'FileGroup' = data_space_id,
'TotalExtents' = size/8,
'UsedExtents' = fileproperty (name, 'SpaceUsed')/8,
'Name' = name,
'FileName' = physical_name
from sys.database_files
where type = 0
and state = 0
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:es9H7AvAFHA.2192@.TK2MSFTNGP14.phx.gbl...
> And the equivalent of TotalExtents can be found in: select size from
> sys.database_files or select size from sys.master_files
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:eYFWV8uAFHA.3336@.TK2MSFTNGP11.phx.gbl...
>
|||Thanks a lot .
This helped me alot .
Thanks
ARR
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:#Jr$nhvAFHA.4044@.TK2MSFTNGP14.phx.gbl...
> Last one to wrap this up, this would be the ultimate Yukon way of
retrieving
> the data today you get from showfilestats (which will remains an
> undocumented DBCC command in Yukon as well).
> select 'FileId' = file_id,
> 'FileGroup' = data_space_id,
> 'TotalExtents' = size/8,
> 'UsedExtents' = fileproperty (name, 'SpaceUsed')/8,
> 'Name' = name,
> 'FileName' = physical_name
> from sys.database_files
> where type = 0
> and state = 0
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:es9H7AvAFHA.2192@.TK2MSFTNGP14.phx.gbl...
equivalent[vbcol=seagreen]
space[vbcol=seagreen]
the
>

filestats

Hi ,
In 2k there is dbcc command dbcc showfilestats to see used & free extents in
filegroup , what is equivalent in Yukon or shall we continue using the same
.
Thanks
ARRcheck out sys.dm.db.index_physical_stats in BooksOnLine.
Andrew J. Kelly SQL MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
> Hi ,
> In 2k there is dbcc command dbcc showfilestats to see used & free extents
> in
> filegroup , what is equivalent in Yukon or shall we continue using the
> same
> .
> Thanks
> ARR
>|||sys.dm_db_index_physical_stats does not report per file does it? Only per
object.
select fileproperty('filename', 'SpaceUsed') reports the amount of space
allocated but not free.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
> check out sys.dm.db.index_physical_stats in BooksOnLine.
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
>> Hi ,
>> In 2k there is dbcc command dbcc showfilestats to see used & free extents
>> in
>> filegroup , what is equivalent in Yukon or shall we continue using the
>> same
>> .
>> Thanks
>> ARR
>>
>|||Sorry, the select fileproperty('filename', 'SpaceUsed') is the equivalent of
dbcc showfilestats column UsedExtents * 8
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> sys.dm_db_index_physical_stats does not report per file does it? Only per
> object.
> select fileproperty('filename', 'SpaceUsed') reports the amount of space
> allocated but not free.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
>> check out sys.dm.db.index_physical_stats in BooksOnLine.
>>
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Aju" <ajuonline@.yahoo.com> wrote in message
>> news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
>> Hi ,
>> In 2k there is dbcc command dbcc showfilestats to see used & free
>> extents in
>> filegroup , what is equivalent in Yukon or shall we continue using the
>> same
>> .
>> Thanks
>> ARR
>>
>>
>|||And the equivalent of TotalExtents can be found in: select size from
sys.database_files or select size from sys.master_files
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:eYFWV8uAFHA.3336@.TK2MSFTNGP11.phx.gbl...
> Sorry, the select fileproperty('filename', 'SpaceUsed') is the equivalent
> of dbcc showfilestats column UsedExtents * 8
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
>> sys.dm_db_index_physical_stats does not report per file does it? Only per
>> object.
>> select fileproperty('filename', 'SpaceUsed') reports the amount of space
>> allocated but not free.
>> GertD@.SQLDev.Net
>> Please reply only to the newsgroups.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> You assume all risk for your use.
>> Copyright © SQLDev.Net 1991-2005 All rights reserved.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
>> check out sys.dm.db.index_physical_stats in BooksOnLine.
>>
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Aju" <ajuonline@.yahoo.com> wrote in message
>> news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
>> Hi ,
>> In 2k there is dbcc command dbcc showfilestats to see used & free
>> extents in
>> filegroup , what is equivalent in Yukon or shall we continue using the
>> same
>> .
>> Thanks
>> ARR
>>
>>
>>
>|||Sorry I was thinking showcontig<g>.
Andrew J. Kelly SQL MVP
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> sys.dm_db_index_physical_stats does not report per file does it? Only per
> object.
> select fileproperty('filename', 'SpaceUsed') reports the amount of space
> allocated but not free.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
>> check out sys.dm.db.index_physical_stats in BooksOnLine.
>>
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Aju" <ajuonline@.yahoo.com> wrote in message
>> news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
>> Hi ,
>> In 2k there is dbcc command dbcc showfilestats to see used & free
>> extents in
>> filegroup , what is equivalent in Yukon or shall we continue using the
>> same
>> .
>> Thanks
>> ARR
>>
>>
>|||Last one to wrap this up, this would be the ultimate Yukon way of retrieving
the data today you get from showfilestats (which will remains an
undocumented DBCC command in Yukon as well).
select 'FileId' = file_id,
'FileGroup' = data_space_id,
'TotalExtents' = size/8,
'UsedExtents' = fileproperty (name, 'SpaceUsed')/8,
'Name' = name,
'FileName' = physical_name
from sys.database_files
where type = 0
and state = 0
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:es9H7AvAFHA.2192@.TK2MSFTNGP14.phx.gbl...
> And the equivalent of TotalExtents can be found in: select size from
> sys.database_files or select size from sys.master_files
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:eYFWV8uAFHA.3336@.TK2MSFTNGP11.phx.gbl...
>> Sorry, the select fileproperty('filename', 'SpaceUsed') is the equivalent
>> of dbcc showfilestats column UsedExtents * 8
>> GertD@.SQLDev.Net
>> Please reply only to the newsgroups.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> You assume all risk for your use.
>> Copyright © SQLDev.Net 1991-2005 All rights reserved.
>> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
>> news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
>> sys.dm_db_index_physical_stats does not report per file does it? Only
>> per object.
>> select fileproperty('filename', 'SpaceUsed') reports the amount of space
>> allocated but not free.
>> GertD@.SQLDev.Net
>> Please reply only to the newsgroups.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> You assume all risk for your use.
>> Copyright © SQLDev.Net 1991-2005 All rights reserved.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
>> check out sys.dm.db.index_physical_stats in BooksOnLine.
>>
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Aju" <ajuonline@.yahoo.com> wrote in message
>> news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
>> Hi ,
>> In 2k there is dbcc command dbcc showfilestats to see used & free
>> extents in
>> filegroup , what is equivalent in Yukon or shall we continue using the
>> same
>> .
>> Thanks
>> ARR
>>
>>
>>
>>
>|||Thanks a lot .
This helped me alot .
Thanks
ARR
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:#Jr$nhvAFHA.4044@.TK2MSFTNGP14.phx.gbl...
> Last one to wrap this up, this would be the ultimate Yukon way of
retrieving
> the data today you get from showfilestats (which will remains an
> undocumented DBCC command in Yukon as well).
> select 'FileId' = file_id,
> 'FileGroup' = data_space_id,
> 'TotalExtents' = size/8,
> 'UsedExtents' = fileproperty (name, 'SpaceUsed')/8,
> 'Name' = name,
> 'FileName' = physical_name
> from sys.database_files
> where type = 0
> and state = 0
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:es9H7AvAFHA.2192@.TK2MSFTNGP14.phx.gbl...
> > And the equivalent of TotalExtents can be found in: select size from
> > sys.database_files or select size from sys.master_files
> >
> > GertD@.SQLDev.Net
> >
> > Please reply only to the newsgroups.
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > You assume all risk for your use.
> > Copyright © SQLDev.Net 1991-2005 All rights reserved.
> >
> > "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> > news:eYFWV8uAFHA.3336@.TK2MSFTNGP11.phx.gbl...
> >> Sorry, the select fileproperty('filename', 'SpaceUsed') is the
equivalent
> >> of dbcc showfilestats column UsedExtents * 8
> >>
> >> GertD@.SQLDev.Net
> >>
> >> Please reply only to the newsgroups.
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> You assume all risk for your use.
> >> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> >>
> >> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> >> news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> >> sys.dm_db_index_physical_stats does not report per file does it? Only
> >> per object.
> >> select fileproperty('filename', 'SpaceUsed') reports the amount of
space
> >> allocated but not free.
> >>
> >> GertD@.SQLDev.Net
> >>
> >> Please reply only to the newsgroups.
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> You assume all risk for your use.
> >> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> >>
> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> >> news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
> >> check out sys.dm.db.index_physical_stats in BooksOnLine.
> >>
> >>
> >>
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Aju" <ajuonline@.yahoo.com> wrote in message
> >> news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
> >> Hi ,
> >> In 2k there is dbcc command dbcc showfilestats to see used & free
> >> extents in
> >> filegroup , what is equivalent in Yukon or shall we continue using
the
> >> same
> >> .
> >>
> >> Thanks
> >> ARR
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>

filestats

Hi ,
In 2k there is dbcc command dbcc showfilestats to see used & free extents in
filegroup , what is equivalent in Yukon or shall we continue using the same
.
Thanks
ARRcheck out sys.dm.db.index_physical_stats in BooksOnLine.
Andrew J. Kelly SQL MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
> Hi ,
> In 2k there is dbcc command dbcc showfilestats to see used & free extents
> in
> filegroup , what is equivalent in Yukon or shall we continue using the
> same
> .
> Thanks
> ARR
>|||sys.dm_db_index_physical_stats does not report per file does it? Only per
object.
select fileproperty('filename', 'SpaceUsed') reports the amount of space
allocated but not free.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
> check out sys.dm.db.index_physical_stats in BooksOnLine.
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
>|||Sorry, the select fileproperty('filename', 'SpaceUsed') is the equivalent of
dbcc showfilestats column UsedExtents * 8
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> sys.dm_db_index_physical_stats does not report per file does it? Only per
> object.
> select fileproperty('filename', 'SpaceUsed') reports the amount of space
> allocated but not free.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
>|||And the equivalent of TotalExtents can be found in: select size from
sys.database_files or select size from sys.master_files
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:eYFWV8uAFHA.3336@.TK2MSFTNGP11.phx.gbl...
> Sorry, the select fileproperty('filename', 'SpaceUsed') is the equivalent
> of dbcc showfilestats column UsedExtents * 8
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
>|||Sorry I was thinking showcontig<g>.
Andrew J. Kelly SQL MVP
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> sys.dm_db_index_physical_stats does not report per file does it? Only per
> object.
> select fileproperty('filename', 'SpaceUsed') reports the amount of space
> allocated but not free.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
>|||Last one to wrap this up, this would be the ultimate Yukon way of retrieving
the data today you get from showfilestats (which will remains an
undocumented DBCC command in Yukon as well).
select 'FileId' = file_id,
'FileGroup' = data_space_id,
'TotalExtents' = size/8,
'UsedExtents' = fileproperty (name, 'SpaceUsed')/8,
'Name' = name,
'FileName' = physical_name
from sys.database_files
where type = 0
and state = 0
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:es9H7AvAFHA.2192@.TK2MSFTNGP14.phx.gbl...
> And the equivalent of TotalExtents can be found in: select size from
> sys.database_files or select size from sys.master_files
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:eYFWV8uAFHA.3336@.TK2MSFTNGP11.phx.gbl...
>|||Thanks a lot .
This helped me alot .
Thanks
ARR
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:#Jr$nhvAFHA.4044@.TK2MSFTNGP14.phx.gbl...
> Last one to wrap this up, this would be the ultimate Yukon way of
retrieving
> the data today you get from showfilestats (which will remains an
> undocumented DBCC command in Yukon as well).
> select 'FileId' = file_id,
> 'FileGroup' = data_space_id,
> 'TotalExtents' = size/8,
> 'UsedExtents' = fileproperty (name, 'SpaceUsed')/8,
> 'Name' = name,
> 'FileName' = physical_name
> from sys.database_files
> where type = 0
> and state = 0
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:es9H7AvAFHA.2192@.TK2MSFTNGP14.phx.gbl...
equivalent[vbcol=seagreen]
space[vbcol=seagreen]
the[vbcol=seagreen]
>

filelistonly vs verifyonly

Hello all. Does anyone know if a successful completion of a 'restore
filelistonly' command would indicate that a backup file is valid? I've
noticed some of our backup jobs failing during the verify phase of the
maintenenace plan because of network issues, and I'd like a quick way to
check if the backup is valid because some of the backup files take hours to
verify. I searched MS Support and they don't seem to have any info on this.

TWTech Witch (tech.witch@.gmail.NOSPAM.com) writes:
> Hello all. Does anyone know if a successful completion of a 'restore
> filelistonly' command would indicate that a backup file is valid? I've
> noticed some of our backup jobs failing during the verify phase of the
> maintenenace plan because of network issues, and I'd like a quick way to
> check if the backup is valid because some of the backup files take hours
> to verify. I searched MS Support and they don't seem to have any info
> on this.

I can't say for certain, but my gut feeling is that a "filelistonly" is
a far cry from verifying the entire backup. An OK FILELISTONLY will tell
you that the backup is not completely broken, but there might still
be occassional errors, because of bad disk sectors, network glitches (when
backing up to a file share), tape-drive glitches (when backing up to
tape).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Filegroups

i want to obtain information about all the filegroups for every database. is there a special command to obtain this information like the dbcc sqlper or something similar ??sp_helpfilegroup

File System Task - How to Move Directory Contents

Hi,

I want to move all files of one directory/folder into another usng SSIS, like in DOS Prompt we use the command,

Move d:\ftpSource\*.* d:\ETLSource

I have tried by creating a file system task, but there is no option for Move Directory Contents. Move File is not accepting wild cards.

Regards,

Imran.

Hi,

U can use 'move directory'. But both source and destination should have same root and if the destination folder already exists. Also it will work as a rename folder. So either the destination folder should not exist or u should set the 'overwrite destination' property to true which will delete the destination folder before renaming the source. U better do it thru 'File' object in Script task.

|||

Imran Shaikh wrote:

Hi,

I want to move all files of one directory/folder into another usng SSIS, like in DOS Prompt we use the command,

Move d:\ftpSource\*.* d:\ETLSource

I have tried by creating a file system task, but there is no option

for Move Directory Contents. Move File is not accepting wild cards.

Regards,

Imran.

I struggled with this myself, and in the end I had to do 2 file system operations:

(1) copy directory - from source to destination

(2) delete directory contents - from source

HTH|||

Hi,

Sorry I cant move directory, because it is published over ftp. The way I already have done is (foreach loop over directory...Move file), but it is slow. Thanks for the hint, Now I will try file Object in Script task.

Thanks

Imran.

file operations

I'm writing a proc to do tlog backups. I want to validate the path before
attempting to issue the backup command but I don't see any documented file
handling funtions in T-SQL. What's the best approach for this?
Thanks,
Bob Castleman
DBA PoseurImplement a DTS package that first uses the FSO.FileExists function via a
ActiveX Script task to verify the folder. If the task returns success, then
use a Execute SQL task to call your SP.
Function Main()
Main = DTSTaskExecResult_Failure
sCopyFrom = "c:\temp\xxx.tmp"
sCopyTo = "c:\temp\xxx.dat"
set FSO = CreateObject("Scripting.FileSystemObject")
if not FSO.FileExists( sCopyFrom ) then
exit function
end if
if FSO.FileExists( sCopyTo ) then
FSO.DeleteFile sCopyTo
end if
FSO.CopyFile sCopyFrom, sCopyTo
set FSO = nothing
Main = DTSTaskExecResult_Success
End Function
"Bob Castleman" <nomail@.here> wrote in message
news:e180EavYFHA.1028@.TK2MSFTNGP10.phx.gbl...
> I'm writing a proc to do tlog backups. I want to validate the path before
> attempting to issue the backup command but I don't see any documented file
> handling funtions in T-SQL. What's the best approach for this?
> Thanks,
> Bob Castleman
> DBA Poseur
>