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

Filter delete rows on subscriber

How does SQL server replication delete rows on the client that no longer
matches the filter? I need this to decide if I want to add a new table into
replication.
TIA.
Rajesh
There is a table with a large number of rows (5-6 million), with only a few
rows (2000-3000) replicated to each client. Each client needs to get a
different set of rows.
I want to know whether:
1. The filter is applied on the subscriber to delete the rows?
2. Or the IDs to be deleted is sent to the subscriber by the publisher?
3. Or the IDs on the subscriber is sent to the publisher, for which the
publisher responds with the IDs that should no longer be available in the
subscriber.
Due to the large number of rows, I'm just worried that #2 and #3 will lead
to a lot of inefficeincy. And if that is the case, I would rather sync this
data outside of replication.
Rajesh
"Rajesh" wrote:

> How does SQL server replication delete rows on the client that no longer
> matches the filter? I need this to decide if I want to add a new table into
> replication.
> TIA.
> Rajesh
sql

FILEGROUP Ful;l ...

I am aware that a Full Filegroup will prohibit INSERTS
into the DB BUT ... Will this also prevent UPDATE and
DELETE operations?
ThanksIt won't prevent DELETE. It might prevent UPDATE if:
The update need to allocate more space on some page (if you expend a column
so it won't fit on the old page so the update need to be performed
internally as a delete followed by an insert).
There is no page with free space to hold the new to of the pages that the
table is currently using.
There is no extent with free pages of the extents that the tables is using.
There are no un-allocated extents in the filegroup.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jay Kusch" <Jay.Kusch@.mm-games.com> wrote in message
news:145c201c3fa0b$36ff9690$a301280a@.phx.gbl...
> I am aware that a Full Filegroup will prohibit INSERTS
> into the DB BUT ... Will this also prevent UPDATE and
> DELETE operations?
> Thanks
>
>

filegroup

currently we have a lot of filegroup and i would like reduce this filegroup
to 4 filegroup how cand move data from one to other and how delete dis
filegroup when is empty.
thnks
Alf
You can move data to a different filegroup by rebuilding the clustered index
on a new filegroup, or if you dont have a clustered index, you can build one
and then drop it just to move the data.
Alternatively you can use the design table option in Enterprise manager Open
the database you are using, click on tables in the left paine. Right click on
the table you want and choose design table. Open the table and index
properties. You can change the file group the table is a part of from here.
When you close the window it will move the table, if it's a large table it
may take a while.
When you have moved all your tables you can use the 'Alter database' command
to remove the filegroup. there is a parameter 'remove filegroup'
As you noted the filegroup must be empty first.
You can also use Enterprise Manager, right click the database and go to
properties. there is an option on the filegroups window to delete filegroups.
Again it must be empty.
Hope this helps
John
"alf" wrote:

> currently we have a lot of filegroup and i would like reduce this filegroup
> to 4 filegroup how cand move data from one to other and how delete dis
> filegroup when is empty.
> thnks
>
>
|||"alf" <em_alf@.hotmail.com> wrote in message
news:%23saTLW29EHA.2608@.TK2MSFTNGP10.phx.gbl...
> currently we have a lot of filegroup and i would like reduce this
> filegroup to 4 filegroup how cand move data from one to other and how
> delete dis filegroup when is empty.
> thnks
>
Read up on filegroups in the Books Online.
I think you can do this by using the DBCC SHRINKFILE commands with the
EMPTYFILE switch turned on. This should move data to other files in the
filegroup. Once the file has been emptied and marked as empty, you can
remove it.
You may need to run ALTER TABLE commands to move affected tables off of the
filegroup to somewhere else. Once your filegroup's files are completely
empty, you can use ALTER DATABASE ... REMOVEFILEGROUP to remove the
filegroup.
HTH
Rick Sawtell
MCT, MCSD, MCDBA

filegroup

currently we have a lot of filegroup and i would like reduce this filegroup
to 4 filegroup how cand move data from one to other and how delete dis
filegroup when is empty.
thnksAlf
You can move data to a different filegroup by rebuilding the clustered index
on a new filegroup, or if you dont have a clustered index, you can build one
and then drop it just to move the data.
Alternatively you can use the design table option in Enterprise manager Open
the database you are using, click on tables in the left paine. Right click on
the table you want and choose design table. Open the table and index
properties. You can change the file group the table is a part of from here.
When you close the window it will move the table, if it's a large table it
may take a while.
When you have moved all your tables you can use the 'Alter database' command
to remove the filegroup. there is a parameter 'remove filegroup'
As you noted the filegroup must be empty first.
You can also use Enterprise Manager, right click the database and go to
properties. there is an option on the filegroups window to delete filegroups.
Again it must be empty.
Hope this helps
John
"alf" wrote:
> currently we have a lot of filegroup and i would like reduce this filegroup
> to 4 filegroup how cand move data from one to other and how delete dis
> filegroup when is empty.
> thnks
>
>|||"alf" <em_alf@.hotmail.com> wrote in message
news:%23saTLW29EHA.2608@.TK2MSFTNGP10.phx.gbl...
> currently we have a lot of filegroup and i would like reduce this
> filegroup to 4 filegroup how cand move data from one to other and how
> delete dis filegroup when is empty.
> thnks
>
Read up on filegroups in the Books Online.
I think you can do this by using the DBCC SHRINKFILE commands with the
EMPTYFILE switch turned on. This should move data to other files in the
filegroup. Once the file has been emptied and marked as empty, you can
remove it.
You may need to run ALTER TABLE commands to move affected tables off of the
filegroup to somewhere else. Once your filegroup's files are completely
empty, you can use ALTER DATABASE ... REMOVEFILEGROUP to remove the
filegroup.
HTH
Rick Sawtell
MCT, MCSD, MCDBA

filegroup

currently we have a lot of filegroup and i would like reduce this filegroup
to 4 filegroup how cand move data from one to other and how delete dis
filegroup when is empty.
thnksAlf
You can move data to a different filegroup by rebuilding the clustered index
on a new filegroup, or if you dont have a clustered index, you can build one
and then drop it just to move the data.
Alternatively you can use the design table option in Enterprise manager Open
the database you are using, click on tables in the left paine. Right click o
n
the table you want and choose design table. Open the table and index
properties. You can change the file group the table is a part of from here.
When you close the window it will move the table, if it's a large table it
may take a while.
When you have moved all your tables you can use the 'Alter database' command
to remove the filegroup. there is a parameter 'remove filegroup'
As you noted the filegroup must be empty first.
You can also use Enterprise Manager, right click the database and go to
properties. there is an option on the filegroups window to delete filegroups
.
Again it must be empty.
Hope this helps
John
"alf" wrote:

> currently we have a lot of filegroup and i would like reduce this filegrou
p
> to 4 filegroup how cand move data from one to other and how delete dis
> filegroup when is empty.
> thnks
>
>|||"alf" <em_alf@.hotmail.com> wrote in message
news:%23saTLW29EHA.2608@.TK2MSFTNGP10.phx.gbl...
> currently we have a lot of filegroup and i would like reduce this
> filegroup to 4 filegroup how cand move data from one to other and how
> delete dis filegroup when is empty.
> thnks
>
Read up on filegroups in the Books Online.
I think you can do this by using the DBCC SHRINKFILE commands with the
EMPTYFILE switch turned on. This should move data to other files in the
filegroup. Once the file has been emptied and marked as empty, you can
remove it.
You may need to run ALTER TABLE commands to move affected tables off of the
filegroup to somewhere else. Once your filegroup's files are completely
empty, you can use ALTER DATABASE ... REMOVEFILEGROUP to remove the
filegroup.
HTH
Rick Sawtell
MCT, MCSD, MCDBA

File System Task exhibits strange behaviour ?

Hi All,

I've been doing some further testing on SSIS and come across what I consider strange behaviour of the FileSystemTask.

This is set to delete a file, using the file name input from a variable.

If the file doesn't exists I would expect the task to fail, but instead it does not. It succeeds ! I would expect this to fail if the file it attempts to delete is not found.

Can anyone duplicate this strange behaviour ?

Regards,

P R W.

I concur. It does not fail if the file does not exist, and I think it should, or rather it shoudl have the option to do so. I can see why it would also be very desirable to not fail, so for me an option to determine failure on file not found.

If you need it to fail, then a quick workaround for your package would be to add a second File task, and choose the Set attributes mode. That will fail if the file is not found.

|||

P R W wrote:

Hi All,

I've been doing some further testing on SSIS and come across what I consider strange behaviour of the FileSystemTask.

This is set to delete a file, using the file name input from a variable.

If the file doesn't exists I would expect the task to fail, but instead it does not. It succeeds ! I would expect this to fail if the file it attempts to delete is not found.

Can anyone duplicate this strange behaviour ?

Regards,

P R W.

Perhaps submit it as a bug at connect.microsoft.com?

-Jamie

|||

Hi Jamie,

I did a bit of testing with the FST.

If a file does not exist when copying, moving or renaming a file. The FST fails as expected with 'Could not find file....'

When deleting a file with FST though, if the file does not exist, the task succeeds.

This doesn't appear consistent to me and I have submitted as a bug at your suggestion.

Regards,

P R W.

|||

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=250600

Voted and Validated.

|||

Can I add to the strange behaviour of FST? I have the exact same task I added to a Foreach Loop and it just won't execute. I have it on Success for one task and on Success and Expresssion on another. The task is enabled. There are also no errors. The Foreach loop continues without the task running? I don't get it.

Cory

File System Task exhibits strange behaviour ?

Hi All,

I've been doing some further testing on SSIS and come across what I consider strange behaviour of the FileSystemTask.

This is set to delete a file, using the file name input from a variable.

If the file doesn't exists I would expect the task to fail, but instead it does not. It succeeds ! I would expect this to fail if the file it attempts to delete is not found.

Can anyone duplicate this strange behaviour ?

Regards,

P R W.

I concur. It does not fail if the file does not exist, and I think it should, or rather it shoudl have the option to do so. I can see why it would also be very desirable to not fail, so for me an option to determine failure on file not found.

If you need it to fail, then a quick workaround for your package would be to add a second File task, and choose the Set attributes mode. That will fail if the file is not found.

|||

P R W wrote:

Hi All,

I've been doing some further testing on SSIS and come across what I consider strange behaviour of the FileSystemTask.

This is set to delete a file, using the file name input from a variable.

If the file doesn't exists I would expect the task to fail, but instead it does not. It succeeds ! I would expect this to fail if the file it attempts to delete is not found.

Can anyone duplicate this strange behaviour ?

Regards,

P R W.

Perhaps submit it as a bug at connect.microsoft.com?

-Jamie

|||

Hi Jamie,

I did a bit of testing with the FST.

If a file does not exist when copying, moving or renaming a file. The FST fails as expected with 'Could not find file....'

When deleting a file with FST though, if the file does not exist, the task succeeds.

This doesn't appear consistent to me and I have submitted as a bug at your suggestion.

Regards,

P R W.

|||

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=250600

Voted and Validated.

|||

Can I add to the strange behaviour of FST? I have the exact same task I added to a Foreach Loop and it just won't execute. I have it on Success for one task and on Success and Expresssion on another. The task is enabled. There are also no errors. The Foreach loop continues without the task running? I don't get it.

Cory

File system Task -Delete Directory Content

This delete's the directory content as well as sub directory content! Is their a way i can just set it to delete the files in the said directory and not sub directories?Do you mean the FIle System Task? File System Object was a feature of WSH and used a lot in DTS, not SSIS.|||Yes i do mean the file system task in ssis. It has an option to delete directory content that deletes subdirevtory content as well. I just want to delete the files in the directory and not the sub directory. Can this task do this or do i have to revert to writing script using the File System Object to do this?|||You could use a For Each Loop to iterate the files in the directory and then the File System Task to delete them individually.
|||

Mike Tomkies wrote:

Yes i do mean the file system task in ssis. It has an option to delete directory content that deletes subdirevtory content as well. I just want to delete the files in the directory and not the sub directory. Can this task do this or do i have to revert to writing script using the File System Object to do this?

Use a foreach loop to spin through the directory of your choosing. This will allow you to use a wildcard to get at the files you are specifically interested in deleting (even if it's *.*). Then inside the foreach loop, use a File System Task set to "Delete File". Pass in the file name from the foreach loop and you should be set.|||This for each loo how will it read the file names to pass to the task to delete?|||

Mike Tomkies wrote:

This for each loo how will it read the file names to pass to the task to delete?

It's built to do that. Click on the Collection tab and the enumerator should be set to "Foreach File Enumerator". From here, just give it a base directory and let it go to work.|||Thanks that is what i need.|||Think it would of been easy if The File system Task had the Traverse SubFolders option as in the ForEachLoop Container.

File system Object -Delete Directory Content

This delete's the directory content as well as sub directory content! Is their a way i can just set it to delete the files in the said directory and not sub directories?Do you mean the FIle System Task? File System Object was a feature of WSH and used a lot in DTS, not SSIS.|||Yes i do mean the file system task in ssis. It has an option to delete directory content that deletes subdirevtory content as well. I just want to delete the files in the directory and not the sub directory. Can this task do this or do i have to revert to writing script using the File System Object to do this?|||You could use a For Each Loop to iterate the files in the directory and then the File System Task to delete them individually.
|||

Mike Tomkies wrote:

Yes i do mean the file system task in ssis. It has an option to delete directory content that deletes subdirevtory content as well. I just want to delete the files in the directory and not the sub directory. Can this task do this or do i have to revert to writing script using the File System Object to do this?

Use a foreach loop to spin through the directory of your choosing. This will allow you to use a wildcard to get at the files you are specifically interested in deleting (even if it's *.*). Then inside the foreach loop, use a File System Task set to "Delete File". Pass in the file name from the foreach loop and you should be set.|||This for each loo how will it read the file names to pass to the task to delete?|||

Mike Tomkies wrote:

This for each loo how will it read the file names to pass to the task to delete?

It's built to do that. Click on the Collection tab and the enumerator should be set to "Foreach File Enumerator". From here, just give it a base directory and let it go to work.|||Thanks that is what i need.|||Think it would of been easy if The File system Task had the Traverse SubFolders option as in the ForEachLoop Container.

File Permission

m using File system task and want to delete some files in a directory but.. its giving me an error "Access to the file is denied" Pls tell me what is the access that I have to give to the filefolder?Start Menu, Help & Support. Search for "File and folder permissions". The Delete right comes with Full Controll and Modify

File Operations

I need to know, how to rename a file, how to delete a file. how to create a file, all programmatically in Vista.

Thanks in advance,

Frank

If your question is specific to SSIS, you can use the File System Task. If this is a general Vista question, you might want to try another forum.