moving public folders

moving public folders

we have had some abuse of our public folders and now we have around 20,000 
public folders.  i am supposed to write a utility that uses a hashing 
algorithim and moves these 20,000 folders to 100 sub folders based on that 
hashing routine.  i have done some research and what i have done is a vb 
program that will run on exchange, using ado and cdoex.  i have been able to 
create a sqlquery like this

strQuery = "SELECT ""DAV:displayname"", ""DAV:contentclass"", ""DAV:href"""
strQuery = strQuery & " FROM SCOPE('shallow traversal of """ & strFolderUrl 
& """')"
strQuery = strQuery & " WHERE ""DAV:isfolder"" = True AND "
strQuery = strQuery & """DAV:ishidden"" = False"
strQuery = strQuery & " ORDER BY ""DAV:displayname"""

that returns all my public folders, my test public folder has 1000 sub 
folder and the actual one will have 20,000.  however, i would like to 
select, let's say thousands of records, at a time or in my case all the 
public folders that start with 1, then 2, then 3, etc.  so i am adding the 
following to my query but it says it is incompatible. by the way, our public 
folders are named like 10393RSWEB, number and name combinations.

strQuery = strQuery & " WHERE ""DAV:displayname"" LIKE ""1%""

my questions to you:
1. am i going the right path creating this using vb, ado, and cdo?
2. if i am ok then how can i process a sub set of these folders at a time?

thank you
RedMoosh

date: Tue, 20 Feb 2007 14:00:18 -0500 author: RedMoosh me@you

Re: moving public folders

Just a correction:
the error message is "Submitted SQL statement was incomplete."
I am locally logged in as an administrator to the domain and if i omit the 
LIKE operator then everything works fine.
and my query has only is

strQuery = "SELECT ""DAV:displayname"", ""DAV:contentclass"", ""DAV:href"""
strQuery = strQuery & " FROM SCOPE('shallow traversal of """ & strFolderUrl 
& """')"
strQuery = strQuery & " WHERE ""DAV:isfolder"" = True AND "
strQuery = strQuery & """DAV:ishidden"" = False AND "
strQuery = strQuery & """DAV:displayname"" LIKE ""%1"""
strQuery = strQuery & " ORDER BY ""DAV:displayname"""



"RedMoosh" <me@you> wrote in message 
news:u3rJzESVHHA.4872@TK2MSFTNGP03.phx.gbl...
> we have had some abuse of our public folders and now we have around 20,000 
> public folders.  i am supposed to write a utility that uses a hashing 
> algorithim and moves these 20,000 folders to 100 sub folders based on that 
> hashing routine.  i have done some research and what i have done is a vb 
> program that will run on exchange, using ado and cdoex.  i have been able 
> to create a sqlquery like this
>
> strQuery = "SELECT ""DAV:displayname"", ""DAV:contentclass"", 
> ""DAV:href"""
> strQuery = strQuery & " FROM SCOPE('shallow traversal of """ & 
> strFolderUrl & """')"
> strQuery = strQuery & " WHERE ""DAV:isfolder"" = True AND "
> strQuery = strQuery & """DAV:ishidden"" = False"
> strQuery = strQuery & " ORDER BY ""DAV:displayname"""
>
> that returns all my public folders, my test public folder has 1000 sub 
> folder and the actual one will have 20,000.  however, i would like to 
> select, let's say thousands of records, at a time or in my case all the 
> public folders that start with 1, then 2, then 3, etc.  so i am adding the 
> following to my query but it says it is incompatible. by the way, our 
> public folders are named like 10393RSWEB, number and name combinations.
>
> strQuery = strQuery & " WHERE ""DAV:displayname"" LIKE ""1%""
>
> my questions to you:
> 1. am i going the right path creating this using vb, ado, and cdo?
> 2. if i am ok then how can i process a sub set of these folders at a time?
>
> thank you
> RedMoosh
>

date: Tue, 20 Feb 2007 15:59:37 -0500 author: RedMoosh me@you

Re: moving public folders

You need to put the string your searching for in single quotes and if your 
using wildcards you need to begin and end with the % character eg like 
'%1%'

Cheers
Glen

"RedMoosh" <me@you> wrote in message 
news:u1QzeHTVHHA.3652@TK2MSFTNGP04.phx.gbl...
> Just a correction:
> the error message is "Submitted SQL statement was incomplete."
> I am locally logged in as an administrator to the domain and if i omit the 
> LIKE operator then everything works fine.
> and my query has only is
>
> strQuery = "SELECT ""DAV:displayname"", ""DAV:contentclass"", 
> ""DAV:href"""
> strQuery = strQuery & " FROM SCOPE('shallow traversal of """ & 
> strFolderUrl & """')"
> strQuery = strQuery & " WHERE ""DAV:isfolder"" = True AND "
> strQuery = strQuery & """DAV:ishidden"" = False AND "
> strQuery = strQuery & """DAV:displayname"" LIKE ""%1"""
> strQuery = strQuery & " ORDER BY ""DAV:displayname"""
>
>
>
> "RedMoosh" <me@you> wrote in message 
> news:u3rJzESVHHA.4872@TK2MSFTNGP03.phx.gbl...
>> we have had some abuse of our public folders and now we have around 
>> 20,000 public folders.  i am supposed to write a utility that uses a 
>> hashing algorithim and moves these 20,000 folders to 100 sub folders 
>> based on that hashing routine.  i have done some research and what i have 
>> done is a vb program that will run on exchange, using ado and cdoex.  i 
>> have been able to create a sqlquery like this
>>
>> strQuery = "SELECT ""DAV:displayname"", ""DAV:contentclass"", 
>> ""DAV:href"""
>> strQuery = strQuery & " FROM SCOPE('shallow traversal of """ & 
>> strFolderUrl & """')"
>> strQuery = strQuery & " WHERE ""DAV:isfolder"" = True AND "
>> strQuery = strQuery & """DAV:ishidden"" = False"
>> strQuery = strQuery & " ORDER BY ""DAV:displayname"""
>>
>> that returns all my public folders, my test public folder has 1000 sub 
>> folder and the actual one will have 20,000.  however, i would like to 
>> select, let's say thousands of records, at a time or in my case all the 
>> public folders that start with 1, then 2, then 3, etc.  so i am adding 
>> the following to my query but it says it is incompatible. by the way, our 
>> public folders are named like 10393RSWEB, number and name combinations.
>>
>> strQuery = strQuery & " WHERE ""DAV:displayname"" LIKE ""1%""
>>
>> my questions to you:
>> 1. am i going the right path creating this using vb, ado, and cdo?
>> 2. if i am ok then how can i process a sub set of these folders at a 
>> time?
>>
>> thank you
>> RedMoosh
>>
>
>

date: Wed, 21 Feb 2007 11:48:15 +1100 author: Glen Scales [MVP]

Re: moving public folders

thanks so much.
that worked.
i actually ended up using '%1' and gave me all the folders starting with 1.


"Glen Scales [MVP]"  wrote in message 
news:%23Q$R7HVVHHA.4568@TK2MSFTNGP02.phx.gbl...
> You need to put the string your searching for in single quotes and if your 
> using wildcards you need to begin and end with the % character eg like 
> '%1%'
>
> Cheers
> Glen
>
> "RedMoosh" <me@you> wrote in message 
> news:u1QzeHTVHHA.3652@TK2MSFTNGP04.phx.gbl...
>> Just a correction:
>> the error message is "Submitted SQL statement was incomplete."
>> I am locally logged in as an administrator to the domain and if i omit 
>> the LIKE operator then everything works fine.
>> and my query has only is
>>
>> strQuery = "SELECT ""DAV:displayname"", ""DAV:contentclass"", 
>> ""DAV:href"""
>> strQuery = strQuery & " FROM SCOPE('shallow traversal of """ & 
>> strFolderUrl & """')"
>> strQuery = strQuery & " WHERE ""DAV:isfolder"" = True AND "
>> strQuery = strQuery & """DAV:ishidden"" = False AND "
>> strQuery = strQuery & """DAV:displayname"" LIKE ""%1"""
>> strQuery = strQuery & " ORDER BY ""DAV:displayname"""
>>
>>
>>
>> "RedMoosh" <me@you> wrote in message 
>> news:u3rJzESVHHA.4872@TK2MSFTNGP03.phx.gbl...
>>> we have had some abuse of our public folders and now we have around 
>>> 20,000 public folders.  i am supposed to write a utility that uses a 
>>> hashing algorithim and moves these 20,000 folders to 100 sub folders 
>>> based on that hashing routine.  i have done some research and what i 
>>> have done is a vb program that will run on exchange, using ado and 
>>> cdoex.  i have been able to create a sqlquery like this
>>>
>>> strQuery = "SELECT ""DAV:displayname"", ""DAV:contentclass"", 
>>> ""DAV:href"""
>>> strQuery = strQuery & " FROM SCOPE('shallow traversal of """ & 
>>> strFolderUrl & """')"
>>> strQuery = strQuery & " WHERE ""DAV:isfolder"" = True AND "
>>> strQuery = strQuery & """DAV:ishidden"" = False"
>>> strQuery = strQuery & " ORDER BY ""DAV:displayname"""
>>>
>>> that returns all my public folders, my test public folder has 1000 sub 
>>> folder and the actual one will have 20,000.  however, i would like to 
>>> select, let's say thousands of records, at a time or in my case all the 
>>> public folders that start with 1, then 2, then 3, etc.  so i am adding 
>>> the following to my query but it says it is incompatible. by the way, 
>>> our public folders are named like 10393RSWEB, number and name 
>>> combinations.
>>>
>>> strQuery = strQuery & " WHERE ""DAV:displayname"" LIKE ""1%""
>>>
>>> my questions to you:
>>> 1. am i going the right path creating this using vb, ado, and cdo?
>>> 2. if i am ok then how can i process a sub set of these folders at a 
>>> time?
>>>
>>> thank you
>>> RedMoosh
>>>
>>
>>
>
>

date: Tue, 20 Feb 2007 23:35:33 -0500 author: RedMoosh me@you

Re: moving public folders

i meant '1%' gave me all the folders starting with 1.


"RedMoosh" <me@you> wrote in message 
news:u$s6OGXVHHA.4632@TK2MSFTNGP04.phx.gbl...
> thanks so much.
> that worked.
> i actually ended up using '%1' and gave me all the folders starting with 
> 1.
>
>
> "Glen Scales [MVP]"  wrote in message 
> news:%23Q$R7HVVHHA.4568@TK2MSFTNGP02.phx.gbl...
>> You need to put the string your searching for in single quotes and if 
>> your using wildcards you need to begin and end with the % character eg 
>> like '%1%'
>>
>> Cheers
>> Glen
>>
>> "RedMoosh" <me@you> wrote in message 
>> news:u1QzeHTVHHA.3652@TK2MSFTNGP04.phx.gbl...
>>> Just a correction:
>>> the error message is "Submitted SQL statement was incomplete."
>>> I am locally logged in as an administrator to the domain and if i omit 
>>> the LIKE operator then everything works fine.
>>> and my query has only is
>>>
>>> strQuery = "SELECT ""DAV:displayname"", ""DAV:contentclass"", 
>>> ""DAV:href"""
>>> strQuery = strQuery & " FROM SCOPE('shallow traversal of """ & 
>>> strFolderUrl & """')"
>>> strQuery = strQuery & " WHERE ""DAV:isfolder"" = True AND "
>>> strQuery = strQuery & """DAV:ishidden"" = False AND "
>>> strQuery = strQuery & """DAV:displayname"" LIKE ""%1"""
>>> strQuery = strQuery & " ORDER BY ""DAV:displayname"""
>>>
>>>
>>>
>>> "RedMoosh" <me@you> wrote in message 
>>> news:u3rJzESVHHA.4872@TK2MSFTNGP03.phx.gbl...
>>>> we have had some abuse of our public folders and now we have around 
>>>> 20,000 public folders.  i am supposed to write a utility that uses a 
>>>> hashing algorithim and moves these 20,000 folders to 100 sub folders 
>>>> based on that hashing routine.  i have done some research and what i 
>>>> have done is a vb program that will run on exchange, using ado and 
>>>> cdoex.  i have been able to create a sqlquery like this
>>>>
>>>> strQuery = "SELECT ""DAV:displayname"", ""DAV:contentclass"", 
>>>> ""DAV:href"""
>>>> strQuery = strQuery & " FROM SCOPE('shallow traversal of """ & 
>>>> strFolderUrl & """')"
>>>> strQuery = strQuery & " WHERE ""DAV:isfolder"" = True AND "
>>>> strQuery = strQuery & """DAV:ishidden"" = False"
>>>> strQuery = strQuery & " ORDER BY ""DAV:displayname"""
>>>>
>>>> that returns all my public folders, my test public folder has 1000 sub 
>>>> folder and the actual one will have 20,000.  however, i would like to 
>>>> select, let's say thousands of records, at a time or in my case all the 
>>>> public folders that start with 1, then 2, then 3, etc.  so i am adding 
>>>> the following to my query but it says it is incompatible. by the way, 
>>>> our public folders are named like 10393RSWEB, number and name 
>>>> combinations.
>>>>
>>>> strQuery = strQuery & " WHERE ""DAV:displayname"" LIKE ""1%""
>>>>
>>>> my questions to you:
>>>> 1. am i going the right path creating this using vb, ado, and cdo?
>>>> 2. if i am ok then how can i process a sub set of these folders at a 
>>>> time?
>>>>
>>>> thank you
>>>> RedMoosh
>>>>
>>>
>>>
>>
>>
>
>

date: Tue, 20 Feb 2007 23:38:09 -0500 author: RedMoosh me@you