"Tree View" with a SQL Server Database

We need to present hierarchical data on a web page, the same way the
tree view shows files in Windows Explorer. Here's the catch: that
tree view needs to be bound to a SQL Server database. How can this be
done?Simplist is an adjacency list. Store the parent ID along with each record,
also store the "full path" in a text string, such as 0001/0004/0007/0002
(the second node of the seventh node of the 4th node of the 1st node of the
tree). I use a function to generate a number for the string (below)
because each "path node" has to be the same length in order to perform a
correct "select" of the tree order. You also need a "sibling number" if you
want to order your nodes in an arbitrary way.

ALTER FUNCTION dbo.func_Get_Padded_Number8
(
@.number INTEGER
)
RETURNS VARCHAR(8)
AS
BEGIN
DECLARE @.Value VARCHAR(8)
DECLARE @.Length INTEGER

/*
Convert the number and get its string length
*/

SET @.Value = CONVERT ( VARCHAR ( 8 ), @.number )
SET @.Length = LEN ( @.Value )

/*
If the length is less than 8, pad it
*/
IF LEN ( @.Value ) < 8
BEGIN
SET @.Value = REPLACE ( SPACE ( 8 - @.Length ), ' ', '0' ) + @.Value
END

RETURN @.Value
END

Adding a new node is simple given the parent, you just find the highest
sibling number of the parent and increment it then insert a node, building
the path string by taking the parent and adding "/00n" (where n is the
sibling number). Listing all of the nodes in the tree structure can be done
easily like this:

SELECT dbo.Adjacency.ID, (a unique ID)
dbo.Adjacency.ID_Parent, (the unique ID of the parent of
this node)
dbo.Adjacency.ID_Index, (the sibling number of this child)
LEN(dbo.Adjacency.Path) / 9 AS Depth, (the depth of the
node, useful for building your tree structure afterwards)
FROM dbo.Adjacency
ORDER BY dbo.Adjacency.Path

Hope this helps some.

<imani_technology_spam@.yahoo.com> wrote in message
news:8be6e8.0312030710.3b40bc89@.posting.google.com ...
> We need to present hierarchical data on a web page, the same way the
> tree view shows files in Windows Explorer. Here's the catch: that
> tree view needs to be bound to a SQL Server database. How can this be
> done?|||This helps a lot. Thanks. I wonder if there is a practical graphical
solution in addition to the text-based solution?

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in message news:<bqn6dt$o7l$1$830fa7b3@.news.demon.co.uk>...
> Simplist is an adjacency list. Store the parent ID along with each record,
> also store the "full path" in a text string, such as 0001/0004/0007/0002
> (the second node of the seventh node of the 4th node of the 1st node of the
> tree). I use a function to generate a number for the string (below)
> because each "path node" has to be the same length in order to perform a
> correct "select" of the tree order. You also need a "sibling number" if you
> want to order your nodes in an arbitrary way.
> ALTER FUNCTION dbo.func_Get_Padded_Number8
> (
> @.number INTEGER
> )
> RETURNS VARCHAR(8)
> AS
> BEGIN
> DECLARE @.Value VARCHAR(8)
> DECLARE @.Length INTEGER
> /*
> Convert the number and get its string length
> */
> SET @.Value = CONVERT ( VARCHAR ( 8 ), @.number )
> SET @.Length = LEN ( @.Value )
> /*
> If the length is less than 8, pad it
> */
> IF LEN ( @.Value ) < 8
> BEGIN
> SET @.Value = REPLACE ( SPACE ( 8 - @.Length ), ' ', '0' ) + @.Value
> END
> RETURN @.Value
> END
> Adding a new node is simple given the parent, you just find the highest
> sibling number of the parent and increment it then insert a node, building
> the path string by taking the parent and adding "/00n" (where n is the
> sibling number). Listing all of the nodes in the tree structure can be done
> easily like this:
> SELECT dbo.Adjacency.ID, (a unique ID)
> dbo.Adjacency.ID_Parent, (the unique ID of the parent of
> this node)
> dbo.Adjacency.ID_Index, (the sibling number of this child)
> LEN(dbo.Adjacency.Path) / 9 AS Depth, (the depth of the
> node, useful for building your tree structure afterwards)
> FROM dbo.Adjacency
> ORDER BY dbo.Adjacency.Path
> Hope this helps some.
> <imani_technology_spam@.yahoo.com> wrote in message
> news:8be6e8.0312030710.3b40bc89@.posting.google.com ...
> > We need to present hierarchical data on a web page, the same way the
> > tree view shows files in Windows Explorer. Here's the catch: that
> > tree view needs to be bound to a SQL Server database. How can this be
> > done?|||Hi

I posted this a short time ago!
http://tinyurl.com/xw5s

John

<imani_technology_spam@.yahoo.com> wrote in message
news:8be6e8.0312050907.644b5abe@.posting.google.com ...
> This helps a lot. Thanks. I wonder if there is a practical graphical
> solution in addition to the text-based solution?
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:<bqn6dt$o7l$1$830fa7b3@.news.demon.co.uk>...
> > Simplist is an adjacency list. Store the parent ID along with each
record,
> > also store the "full path" in a text string, such as 0001/0004/0007/0002
> > (the second node of the seventh node of the 4th node of the 1st node of
the
> > tree). I use a function to generate a number for the string (below)
> > because each "path node" has to be the same length in order to perform a
> > correct "select" of the tree order. You also need a "sibling number" if
you
> > want to order your nodes in an arbitrary way.
> > ALTER FUNCTION dbo.func_Get_Padded_Number8
> > (
> > @.number INTEGER
> > )
> > RETURNS VARCHAR(8)
> > AS
> > BEGIN
> > DECLARE @.Value VARCHAR(8)
> > DECLARE @.Length INTEGER
> > /*
> > Convert the number and get its string length
> > */
> > SET @.Value = CONVERT ( VARCHAR ( 8 ), @.number )
> > SET @.Length = LEN ( @.Value )
> > /*
> > If the length is less than 8, pad it
> > */
> > IF LEN ( @.Value ) < 8
> > BEGIN
> > SET @.Value = REPLACE ( SPACE ( 8 - @.Length ), ' ', '0' ) +
@.Value
> > END
> > RETURN @.Value
> > END
> > Adding a new node is simple given the parent, you just find the highest
> > sibling number of the parent and increment it then insert a node,
building
> > the path string by taking the parent and adding "/00n" (where n is the
> > sibling number). Listing all of the nodes in the tree structure can be
done
> > easily like this:
> > SELECT dbo.Adjacency.ID, (a unique ID)
> > dbo.Adjacency.ID_Parent, (the unique ID of the parent
of
> > this node)
> > dbo.Adjacency.ID_Index, (the sibling number of this
child)
> > LEN(dbo.Adjacency.Path) / 9 AS Depth, (the depth of
the
> > node, useful for building your tree structure afterwards)
> > FROM dbo.Adjacency
> > ORDER BY dbo.Adjacency.Path
> > Hope this helps some.
> > <imani_technology_spam@.yahoo.com> wrote in message
> > news:8be6e8.0312030710.3b40bc89@.posting.google.com ...
> > > We need to present hierarchical data on a web page, the same way the
> > > tree view shows files in Windows Explorer. Here's the catch: that
> > > tree view needs to be bound to a SQL Server database. How can this be
> > > done?

"Remove files older than" doesn't work

Hi,
My company is using MS SQL 7. There is a database
maintenance plan to do the backup. It sets the "Remove
files older than 1 day". It used to work properly.
Last week, I suddenly found that this remove function
didn't work, and the files remain there for a whole week
until the disk full and backup fail. I don't know what
happen. I try to solve it by restart the server (Windows
2000 server), and change the setting from "1 day" to "23
hours", it totally doesn't work. Now I have to manually
delete the old file every day.
Anybody have the solution?
Thanks in advance.
ToddBelow KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Todd Chen" <powerful_tech@.yahoo.com> wrote in message
news:039901c39fa0$65479680$a001280a@.phx.gbl...
> Hi,
> My company is using MS SQL 7. There is a database
> maintenance plan to do the backup. It sets the "Remove
> files older than 1 day". It used to work properly.
> Last week, I suddenly found that this remove function
> didn't work, and the files remain there for a whole week
> until the disk full and backup fail. I don't know what
> happen. I try to solve it by restart the server (Windows
> 2000 server), and change the setting from "1 day" to "23
> hours", it totally doesn't work. Now I have to manually
> delete the old file every day.
> Anybody have the solution?
> Thanks in advance.
> Todd|||Wow, reply so fast.
Thanks a lot. Will try.
Todd
>--Original Message--
>Below KB might help:
>http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;303292&Product=
=3Dsql2k
>
>Also, check out below great troubleshooting suggestions
from Bill H at MS:
>
>-- Log files don't delete --
>This is likely to be either a permissions problem or a
sharing violation
>problem. The maintenance plan is run as a job, and jobs
are run by the
>SQLServerAgent service.
>Permissions:
>1. Determine the startup account for the SQLServerAgent
service
>(Start|Programs|Administrative
tools|Services|SQLServerAgent|Startup). This
>account is the security context for jobs, and thus the
maintenance plan.
>2. If SQLServerAgent is started using LocalSystem (as
opposed to a domain
>account) then skip step 3.
>3. On that box, log onto NT as that account. Using
Explorer, attempt to
>delete an expired backup. If that succeeds then go to
Sharing Violation
>section.
>4. Log onto NT with an account that is an administrator
and use Explorer to
>look at the Properties|Security of the folder (where the
backups reside)
>and ensure the SQLServerAgent startup account has Full
Control. If the
>SQLServerAgent startup account is LocalSystem, then the
account to consider
>is SYSTEM.
>5. In NT, if an account is a member of an NT group, and if
that group has
>Access is Denied, then that account will have Access is
Denied, even if
>that account is also a member of the Administrators group.
Thus you may
>need to check group permissions (if the Startup Account is
a member of a
>group).
>6. Keep in mind that permissions (by default) are
inherited from a parent
>folder. Thus, if the backups are stored in C:\bak, and if
someone had
>denied permission to the SQLServerAgent startup account
for C:\, then
>C:\bak will inherit access is denied.
>Sharing violation:
>This is likely to be rooted in a timing issue, with the
most likely cause
>being another scheduled process (such as NT Backup or
Anti-Virus software)
>having the backup file open at the time when the
SQLServerAgent (i.e., the
>maintenance plan job) tried to delete it.
>1. Download filemon and handle from www.sysinternals.com.
>2. I am not sure whether filemon can be scheduled, or you
might be able to
>use NT scheduling services to start filemon just before
the maintenance
>plan job is started, but the filemon log can become very
large, so it would
>be best to start it some short time before the maintenance
plan starts.
>3. Inspect the filemon log for another process that has
that backup file
>open (if your lucky enough to have started filemon before
this other
>process grabs the backup folder), and inspect the log for
the results when
>the SQLServerAgent agent attempts to open that same file.
>4. Schedule the job or that other process to do their work
at different
>times.
>5. You can use the handle utility if you are around at the
time when the
>job is scheduled to run.
>If the backup files are going to a \\share or a mapped
drive (as opposed to
>local drive), then you will need to modify the above (with
respect to where
>the tests and utilities are run).
>Finally, inspection of the maintenance plan's history
report might be
>useful.
>Thanks,
>Bill Hollinshead
>Microsoft, SQL Server
>
>
>-- >Tibor Karaszi, SQL Server MVP
>Archive at:
http://groups.google.com/groups?oi=3Ddjq&as_ugroup=3Dmicrosoft.public.sql=
server
>
>"Todd Chen" <powerful_tech@.yahoo.com> wrote in message
>news:039901c39fa0$65479680$a001280a@.phx.gbl...
>> Hi,
>> My company is using MS SQL 7. There is a database
>> maintenance plan to do the backup. It sets the "Remove
>> files older than 1 day". It used to work properly.
>> Last week, I suddenly found that this remove function
>> didn't work, and the files remain there for a whole week
>> until the disk full and backup fail. I don't know what
>> happen. I try to solve it by restart the server (Windows
>> 2000 server), and change the setting from "1 day" to "23
>> hours", it totally doesn't work. Now I have to manually
>> delete the old file every day.
>> Anybody have the solution?
>> Thanks in advance.
>> Todd
>
>.
>

"Remove files older than" does not work

Hi.
The Database Maintenance Plan that is configured to backup
the DB every day as the "Remove files older than" option
set to 1 days.
The old files are no being removed. The backups are
working and placed in the specified folder. The "Backup
file extension:" is "bak". What am I missing?
Thanx.Does
BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or ME
Computers
http://support.microsoft.com/default.aspx?scid=kb;en-us;278667
apply?
--
Jacco Schalkwijk
SQL Server MVP
"Hector" <anonymous@.discussions.microsoft.com> wrote in message
news:2176001c45ab8$54616d90$a401280a@.phx.gbl...
> Hi.
> The Database Maintenance Plan that is configured to backup
> the DB every day as the "Remove files older than" option
> set to 1 days.
> The old files are no being removed. The backups are
> working and placed in the specified folder. The "Backup
> file extension:" is "bak". What am I missing?
> Thanx.|||The server runs Windows 2000 (SP4).
I might be missing part of your response. It's sort of
distored.
Thanx!
>--Original Message--
>Does
>BUG: Sqlmaint Does Not Delete Expired Backup Files on
Windows 95, 98 or ME
>Computers
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;278667
>apply?
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Hector" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2176001c45ab8$54616d90$a401280a@.phx.gbl...
>> Hi.
>> The Database Maintenance Plan that is configured to
backup
>> the DB every day as the "Remove files older than" option
>> set to 1 days.
>> The old files are no being removed. The backups are
>> working and placed in the specified folder. The "Backup
>> file extension:" is "bak". What am I missing?
>> Thanx.
>
>.
>|||I'm sorry. Now I understood your message. I'm reading
the article now.
Thanx again.
>--Original Message--
>Does
>BUG: Sqlmaint Does Not Delete Expired Backup Files on
Windows 95, 98 or ME
>Computers
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;278667
>apply?
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Hector" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2176001c45ab8$54616d90$a401280a@.phx.gbl...
>> Hi.
>> The Database Maintenance Plan that is configured to
backup
>> the DB every day as the "Remove files older than" option
>> set to 1 days.
>> The old files are no being removed. The backups are
>> working and placed in the specified folder. The "Backup
>> file extension:" is "bak". What am I missing?
>> Thanx.
>
>.
>|||I reviewed the article and it doesn't apply. We have SQL
Server 2000 running on a Windows 2000 Server.
>--Original Message--
>Does
>BUG: Sqlmaint Does Not Delete Expired Backup Files on
Windows 95, 98 or ME
>Computers
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;278667
>apply?
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Hector" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2176001c45ab8$54616d90$a401280a@.phx.gbl...
>> Hi.
>> The Database Maintenance Plan that is configured to
backup
>> the DB every day as the "Remove files older than" option
>> set to 1 days.
>> The old files are no being removed. The backups are
>> working and placed in the specified folder. The "Backup
>> file extension:" is "bak". What am I missing?
>> Thanx.
>
>.
>|||Below KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hector" <anonymous@.discussions.microsoft.com> wrote in message
news:217f301c45ac5$cfffc700$a501280a@.phx.gbl...
> I reviewed the article and it doesn't apply. We have SQL
> Server 2000 running on a Windows 2000 Server.
>
> >--Original Message--
> >Does
> >BUG: Sqlmaint Does Not Delete Expired Backup Files on
> Windows 95, 98 or ME
> >Computers
> >http://support.microsoft.com/default.aspx?scid=kb;en-
> us;278667
> >apply?
> >
> >--
> >Jacco Schalkwijk
> >SQL Server MVP
> >
> >
> >"Hector" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2176001c45ab8$54616d90$a401280a@.phx.gbl...
> >> Hi.
> >>
> >> The Database Maintenance Plan that is configured to
> backup
> >> the DB every day as the "Remove files older than" option
> >> set to 1 days.
> >>
> >> The old files are no being removed. The backups are
> >> working and placed in the specified folder. The "Backup
> >> file extension:" is "bak". What am I missing?
> >>
> >> Thanx.
> >
> >
> >.
> >|||I've recently taken over management of an SQL 2000 server also o
Windows 2000 server (and relatively new to SQL) and have exactly th
same problem.
Read through this thread but as yet no success to resolving th
problem.
Hector - did you get this resolved?
Anyone have any other ideas not suggested here?
Regards,
Glen.
Did you ever get a fix?
Hector wrote:
> *Hi.
> The Database Maintenance Plan that is configured to backup
> the DB every day as the "Remove files older than" option
> set to 1 days.
> The old files are no being removed. The backups are
> working and placed in the specified folder. The "Backup
> file extension:" is "bak". What am I missing?
> Thanx.
-
Glen Sutti
----
Posted via http://www.webservertalk.co
----
View this thread: http://www.webservertalk.com/message273097.htm|||I don't have access to the old thread, but perhaps below might help?
Below KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Glen Suttie" <Glen.Suttie.1hdx2u@.mail.webservertalk.com> wrote in message
news:Glen.Suttie.1hdx2u@.mail.webservertalk.com...
> I've recently taken over management of an SQL 2000 server also on
> Windows 2000 server (and relatively new to SQL) and have exactly the
> same problem.
> Read through this thread but as yet no success to resolving the
> problem.
> Hector - did you get this resolved?
> Anyone have any other ideas not suggested here?
> Regards,
> Glen.
>
>
> Did you ever get a fix?
> Hector wrote:
>> *Hi.
>> The Database Maintenance Plan that is configured to backup
>> the DB every day as the "Remove files older than" option
>> set to 1 days.
>> The old files are no being removed. The backups are
>> working and placed in the specified folder. The "Backup
>> file extension:" is "bak". What am I missing?
>> Thanx. *
>
> --
> Glen Suttie
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message273097.html
>

"Remove files older than" does not work

Hi.
The Database Maintenance Plan that is configured to backup
the DB every day as the "Remove files older than" option
set to 1 days.
The old files are no being removed. The backups are
working and placed in the specified folder. The "Backup
file extension:" is "bak". What am I missing?
Thanx.
Does
BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or ME
Computers
http://support.microsoft.com/default...b;en-us;278667
apply?
Jacco Schalkwijk
SQL Server MVP
"Hector" <anonymous@.discussions.microsoft.com> wrote in message
news:2176001c45ab8$54616d90$a401280a@.phx.gbl...
> Hi.
> The Database Maintenance Plan that is configured to backup
> the DB every day as the "Remove files older than" option
> set to 1 days.
> The old files are no being removed. The backups are
> working and placed in the specified folder. The "Backup
> file extension:" is "bak". What am I missing?
> Thanx.
|||The server runs Windows 2000 (SP4).
I might be missing part of your response. It's sort of
distored.
Thanx!

>--Original Message--
>Does
>BUG: Sqlmaint Does Not Delete Expired Backup Files on
Windows 95, 98 or ME
>Computers
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;278667
>apply?
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Hector" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2176001c45ab8$54616d90$a401280a@.phx.gbl...
backup
>
>.
>
|||I'm sorry. Now I understood your message. I'm reading
the article now.
Thanx again.

>--Original Message--
>Does
>BUG: Sqlmaint Does Not Delete Expired Backup Files on
Windows 95, 98 or ME
>Computers
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;278667
>apply?
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Hector" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2176001c45ab8$54616d90$a401280a@.phx.gbl...
backup
>
>.
>
|||I reviewed the article and it doesn't apply. We have SQL
Server 2000 running on a Windows 2000 Server.

>--Original Message--
>Does
>BUG: Sqlmaint Does Not Delete Expired Backup Files on
Windows 95, 98 or ME
>Computers
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;278667
>apply?
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Hector" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2176001c45ab8$54616d90$a401280a@.phx.gbl...
backup
>
>.
>
|||Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hector" <anonymous@.discussions.microsoft.com> wrote in message
news:217f301c45ac5$cfffc700$a501280a@.phx.gbl...[vbcol=seagreen]
> I reviewed the article and it doesn't apply. We have SQL
> Server 2000 running on a Windows 2000 Server.
>
> Windows 95, 98 or ME
> us;278667
> message
> backup
|||I've recently taken over management of an SQL 2000 server also on
Windows 2000 server (and relatively new to SQL) and have exactly the
same problem.
Read through this thread but as yet no success to resolving the
problem.
Hector - did you get this resolved?
Anyone have any other ideas not suggested here?
Regards,
Glen.
Did you ever get a fix?
Hector wrote:
> *Hi.
> The Database Maintenance Plan that is configured to backup
> the DB every day as the "Remove files older than" option
> set to 1 days.
> The old files are no being removed. The backups are
> working and placed in the specified folder. The "Backup
> file extension:" is "bak". What am I missing?
> Thanx. *
Glen Suttie
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message273097.html
|||I don't have access to the old thread, but perhaps below might help?
Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Glen Suttie" <Glen.Suttie.1hdx2u@.mail.webservertalk.com> wrote in message
news:Glen.Suttie.1hdx2u@.mail.webservertalk.com...
> I've recently taken over management of an SQL 2000 server also on
> Windows 2000 server (and relatively new to SQL) and have exactly the
> same problem.
> Read through this thread but as yet no success to resolving the
> problem.
> Hector - did you get this resolved?
> Anyone have any other ideas not suggested here?
> Regards,
> Glen.
>
>
> Did you ever get a fix?
> Hector wrote:
>
> --
> Glen Suttie
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message273097.html
>

"Remove files older than" does not work

Hi.
The Database Maintenance Plan that is configured to backup
the DB every day as the "Remove files older than" option
set to 1 days.
The old files are no being removed. The backups are
working and placed in the specified folder. The "Backup
file extension:" is "bak". What am I missing?
Thanx.Does
BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or ME
Computers
http://support.microsoft.com/defaul...kb;en-us;278667
apply?
Jacco Schalkwijk
SQL Server MVP
"Hector" <anonymous@.discussions.microsoft.com> wrote in message
news:2176001c45ab8$54616d90$a401280a@.phx
.gbl...
> Hi.
> The Database Maintenance Plan that is configured to backup
> the DB every day as the "Remove files older than" option
> set to 1 days.
> The old files are no being removed. The backups are
> working and placed in the specified folder. The "Backup
> file extension:" is "bak". What am I missing?
> Thanx.|||The server runs Windows 2000 (SP4).
I might be missing part of your response. It's sort of
distored.
Thanx!

>--Original Message--
>Does
>BUG: Sqlmaint Does Not Delete Expired Backup Files on
Windows 95, 98 or ME
>Computers
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;278667
>apply?
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Hector" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2176001c45ab8$54616d90$a401280a@.phx
.gbl...
backup[vbcol=seagreen]
>
>.
>|||I'm sorry. Now I understood your message. I'm reading
the article now.
Thanx again.

>--Original Message--
>Does
>BUG: Sqlmaint Does Not Delete Expired Backup Files on
Windows 95, 98 or ME
>Computers
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;278667
>apply?
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Hector" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2176001c45ab8$54616d90$a401280a@.phx
.gbl...
backup[vbcol=seagreen]
>
>.
>|||I reviewed the article and it doesn't apply. We have SQL
Server 2000 running on a Windows 2000 Server.

>--Original Message--
>Does
>BUG: Sqlmaint Does Not Delete Expired Backup Files on
Windows 95, 98 or ME
>Computers
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;278667
>apply?
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Hector" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2176001c45ab8$54616d90$a401280a@.phx
.gbl...
backup[vbcol=seagreen]
>
>.
>|||Below KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hector" <anonymous@.discussions.microsoft.com> wrote in message
news:217f301c45ac5$cfffc700$a501280a@.phx
.gbl...[vbcol=seagreen]
> I reviewed the article and it doesn't apply. We have SQL
> Server 2000 running on a Windows 2000 Server.
>
> Windows 95, 98 or ME
> us;278667
> message
> backup|||I've recently taken over management of an SQL 2000 server also on Windows 20
00 server (and relatively new to SQL) and have exactly the same problem.
Read through this thread but as yet no success to resolving the problem.
Hector - did you get this resolved?
Anyone have any other ideas not suggested here?
Regards,
Glen.
Did you ever get a fix?

quote:

---

Originally posted by Hector
Hi.
The Database Maintenance Plan that is configured to backup
the DB every day as the "Remove files older than" option
set to 1 days.
The old files are no being removed. The backups are
working and placed in the specified folder. The "Backup
file extension:" is "bak". What am I missing?
Thanx.

---

|||I don't have access to the old thread, but perhaps below might help?
Below KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Glen Suttie" <Glen.Suttie.1hdx2u@.mail.webservertalk.com> wrote in message
news:Glen.Suttie.1hdx2u@.mail.webservertalk.com...
> I've recently taken over management of an SQL 2000 server also on
> Windows 2000 server (and relatively new to SQL) and have exactly the
> same problem.
> Read through this thread but as yet no success to resolving the
> problem.
> Hector - did you get this resolved?
> Anyone have any other ideas not suggested here?
> Regards,
> Glen.
>
>
> Did you ever get a fix?
> Hector wrote:
>
> --
> Glen Suttie
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message273097.html
>

"Filling in the gaps" with a single-line query

Hi,
I've got the following scenario:
Files are being stored in a database, with a number of name-value
pairs associated with each file. This happens by storing the files in
one table (File), the list of property names in another table
(FileMetaDataSchema) and the values of properties in a third table
(FileMetaData), which references both File and FileMetaDataSchema.
The frontend of my application assumes there is a record for each
property of each file in the FileMetaData table, even if the value is
an empty string. In other words, if i have a list of 3 properties and
2 files, FileMetaData will contain 6 records.
Due to a bug in the system, this does not always happen. Suppose one
adds a new property and neglects to insert the "blank" records for the
new properties for all the files, or a new file is added, but the
associated meta-data records are not... (why and how this happens is
not the topic of discussion, so don't worry about that).
I have written a sql script to insert all the missing "blank" records,
but I feel it is very clumsy and intuitively, I just know there must
be a simpler way, my knowledge is just too limited. What it does is,
it iterates (using cursors) through all the files and all the
properties, checks if there is a record for each combination FileID
and FileMetaDataSchemaID and if not, it inserts one. I am looking for
a better way out of curiosity, for my own benefit.
Here's the script and thanks for any input:
DECLARE MetadataSchemaCursor CURSOR FOR
SELECT FileMetaDataSchemaID FROM FileMetaDataSchema
DECLARE @.FileMetaDataSchemaID INT,
@.FileID INT
OPEN MetadataSchemaCursor
FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
DECLARE FileCursor CURSOR FOR
SELECT FileID FROM [File]
OPEN FileCursor
FETCH NEXT FROM FileCursor INTO @.FileID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
IF NOT EXISTS (SELECT 1 FROM FileMetaData WHERE FileID = @.FileID AND
FileMetaDataSchemaID = @.FileMetaDataSchemaID)
INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID,
PropertyValue) SELECT @.FileID, @.FileMetaDataSchemaID, ''
FETCH NEXT FROM FileCursor INTO @.FileID
END
CLOSE FileCursor
DEALLOCATE FileCursor
FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
END
CLOSE MetadataSchemaCursor
DEALLOCATE MetadataSchemaCursor>I just know there must
>be a simpler way, my knowledge is just too limited.
You are correct, there is a simpler way.
INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID, PropertyValue)
SELECT A.FileID, B.FileMetaDataSchemaID, ''
FROM [File] as A
CROSS
JOIN FileMetaDataSchema as B
WHERE NOT EXISTS
(select * from FileMetaData as X
where A.FileID = X.FileID
and B.FileMetaDataSchemaID = X.FileMetaDataSchemaID)
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:48:53 -0800, "Velislav" <vgebrev@.gmail.com> wrote:
>Hi,
>I've got the following scenario:
>Files are being stored in a database, with a number of name-value
>pairs associated with each file. This happens by storing the files in
>one table (File), the list of property names in another table
>(FileMetaDataSchema) and the values of properties in a third table
>(FileMetaData), which references both File and FileMetaDataSchema.
>The frontend of my application assumes there is a record for each
>property of each file in the FileMetaData table, even if the value is
>an empty string. In other words, if i have a list of 3 properties and
>2 files, FileMetaData will contain 6 records.
>Due to a bug in the system, this does not always happen. Suppose one
>adds a new property and neglects to insert the "blank" records for the
>new properties for all the files, or a new file is added, but the
>associated meta-data records are not... (why and how this happens is
>not the topic of discussion, so don't worry about that).
>I have written a sql script to insert all the missing "blank" records,
>but I feel it is very clumsy and intuitively, I just know there must
>be a simpler way, my knowledge is just too limited. What it does is,
>it iterates (using cursors) through all the files and all the
>properties, checks if there is a record for each combination FileID
>and FileMetaDataSchemaID and if not, it inserts one. I am looking for
>a better way out of curiosity, for my own benefit.
>Here's the script and thanks for any input:
>DECLARE MetadataSchemaCursor CURSOR FOR
> SELECT FileMetaDataSchemaID FROM FileMetaDataSchema
>DECLARE @.FileMetaDataSchemaID INT,
> @.FileID INT
>OPEN MetadataSchemaCursor
>FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
>WHILE (@.@.FETCH_STATUS = 0)
>BEGIN
> DECLARE FileCursor CURSOR FOR
> SELECT FileID FROM [File]
> OPEN FileCursor
> FETCH NEXT FROM FileCursor INTO @.FileID
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> IF NOT EXISTS (SELECT 1 FROM FileMetaData WHERE FileID = @.FileID AND
>FileMetaDataSchemaID = @.FileMetaDataSchemaID)
> INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID,
>PropertyValue) SELECT @.FileID, @.FileMetaDataSchemaID, ''
> FETCH NEXT FROM FileCursor INTO @.FileID
> END
> CLOSE FileCursor
> DEALLOCATE FileCursor
>FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
>END
>CLOSE MetadataSchemaCursor
>DEALLOCATE MetadataSchemaCursor|||On Feb 22, 5:42 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> You are correct, there is a simpler way.
> INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID, PropertyValue)
> SELECT A.FileID, B.FileMetaDataSchemaID, ''
> FROM [File] as A
> CROSS
> JOIN FileMetaDataSchema as B
> WHERE NOT EXISTS
> (select * from FileMetaData as X
> where A.FileID = X.FileID
> and B.FileMetaDataSchemaID = X.FileMetaDataSchemaID)
> Roy Harvey
> Beacon Falls, CT
>
Thank you :)
Note to self - look up cross joins.

"Error!" msg during "setup support files" installation

Windows 2003 Server. Asp.Net 1.1. Sql Server 2000 Sp3a.
"Error!" msg during "setup support files" installation.
Unable to install Reporting Services. Found a couple of users experiencing
the same problem, but no solution :( Any ideas?
/ChrisFirst, check the error log in Event Viewer and see if you can find any
EventID number for your error. That can pinpoint what the error really is.
If it's error 25619, then check if the DTC service is running? Go check in
services.msc for "Distributed Transaction Coordinator".
Even if it says "started", it might not be. I've had some troubles with
installing RS, and the DTC was the culprit.
It's been discussed in this thread:
http://groups.google.com/groups?hl=en&lr=&threadm=89FD4D17-1F6A-4BCF-A15E-CF098C5A68D7%40microsoft.com&rnum=1
In this thread, Pitroda asks about a password for Network Service. Just
leave the password blank, at least that worked for me.
Kaisa M. Lindahl
"Chris Leaf" <ChrisLeaf@.discussions.microsoft.com> wrote in message
news:E6F567CB-08C9-4B49-8544-9DB32826CC09@.microsoft.com...
> Windows 2003 Server. Asp.Net 1.1. Sql Server 2000 Sp3a.
> "Error!" msg during "setup support files" installation.
> Unable to install Reporting Services. Found a couple of users experiencing
> the same problem, but no solution :( Any ideas?
> /Chris|||Event log says:
"Product: Microsoft SQL Server 2000 Reporting Services Enterprise Edition --
Please install SQL Server by running setup.exe."
and
"Product: Microsoft SQL Server 2000 Reporting Services Enterprise Edition --
Installation failed."
install log ends with:
<Func Name='SqlSetupFilesComp::Install'>
<Func Name='BootstrapPackageLogic'>
<Func Name='InstallBootstrapPackage'>
/I "D:\Setup\bootmsi.dat" REBOOT="ReallySuppress"
ADDLOCAL="BootstrapFiles,WatsonFiles,ParsFiles1033,HelpFiles1028,HelpFiles1031,HelpFiles1033,HelpFiles1036,HelpFiles1040,HelpFiles1041,HelpFiles1042,HelpFiles2052,HelpFiles3082,WatsonFiles1033" /qn
<EndFunc Name='InstallBootstrapPackage' Return='1603' GetLastError='183'>
<EndFunc Name='BootstrapPackageLogic' Return='1603' GetLastError='183'>
SqlSetupFilesComp BootstrapPackageLogic() failed : (1603)
{n:\rosetta\dev\src\everettsetup\sqlcu\dll\customcomps.cpp:565}
SCU_SetupMgr::svc() caught exception: SqlSetupFilesComp
BootstrapPackageLogic() failed : (1603)
{n:\rosetta\dev\src\everettsetup\sqlcu\dll\customcomps.cpp:565}. SetupMgr:
state=ERROR, cancel_state=0, is_done=0, ActionRequired=1, NeedReboot=0,
custom_props={AutoStart=true
HandleReboots=false
ModuleDir=D:
QuietMode=false
Unattended=false
WorkDir=D:
}} {n:\rosetta\dev\src\everettsetup\sqlcu\dll\scusetupmgr.cpp:428}
ScuProgressDlg::SetupFinished()
ScuProgressDlg::DialogProc() installation done... waiting for setup mgr
<EndFunc Name='UpdateComponents' Return='1602' GetLastError='0'>
DTC is running..
Thanks for your response.
"Kaisa M. Lindahl" wrote:
> First, check the error log in Event Viewer and see if you can find any
> EventID number for your error. That can pinpoint what the error really is.
> If it's error 25619, then check if the DTC service is running? Go check in
> services.msc for "Distributed Transaction Coordinator".
> Even if it says "started", it might not be. I've had some troubles with
> installing RS, and the DTC was the culprit.
> It's been discussed in this thread:
> http://groups.google.com/groups?hl=en&lr=&threadm=89FD4D17-1F6A-4BCF-A15E-CF098C5A68D7%40microsoft.com&rnum=1
> In this thread, Pitroda asks about a password for Network Service. Just
> leave the password blank, at least that worked for me.
> Kaisa M. Lindahl
> "Chris Leaf" <ChrisLeaf@.discussions.microsoft.com> wrote in message
> news:E6F567CB-08C9-4B49-8544-9DB32826CC09@.microsoft.com...
> > Windows 2003 Server. Asp.Net 1.1. Sql Server 2000 Sp3a.
> >
> > "Error!" msg during "setup support files" installation.
> > Unable to install Reporting Services. Found a couple of users experiencing
> > the same problem, but no solution :( Any ideas?
> >
> > /Chris
>
>

"Database diagram support object cannot be installed...

...because this database does not have a valid owner. To continue, first use
the Files page of the Database Properties dialog box or the ALTER
AUTHORIZATION statement to set the database owner to a valid login, then add
the database diagram support objects. "
I am getting the previous error when trying to create diagrams in a
database. The database was originally created with a domain user account, but
I ran the script "EXEC sp_changedbowner 'sa'" to change the owner to sa.
Still having problems.
Does anyone know how I could resolve this issue?Are you on SP1? If not, check the compatability mode of the database, it
needs to be set to 90 in RTM for diagrams to work
select compatibility_level
from sys.databases
where name = 'database name'
To alter it use sp_dbcmptlevel e.g.
EXEC sp_dbcmptlevel 'pubs', 90
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:100CF59A-E171-46B7-969A-18DF27AF8AEE@.microsoft.com...
> ...because this database does not have a valid owner. To continue, first
> use
> the Files page of the Database Properties dialog box or the ALTER
> AUTHORIZATION statement to set the database owner to a valid login, then
> add
> the database diagram support objects. "
> I am getting the previous error when trying to create diagrams in a
> database. The database was originally created with a domain user account,
> but
> I ran the script "EXEC sp_changedbowner 'sa'" to change the owner to sa.
> Still having problems.
> Does anyone know how I could resolve this issue?|||That was it! Thank you!
"Jasper Smith" wrote:
> Are you on SP1? If not, check the compatability mode of the database, it
> needs to be set to 90 in RTM for diagrams to work
> select compatibility_level
> from sys.databases
> where name = 'database name'
> To alter it use sp_dbcmptlevel e.g.
> EXEC sp_dbcmptlevel 'pubs', 90
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:100CF59A-E171-46B7-969A-18DF27AF8AEE@.microsoft.com...
> > ...because this database does not have a valid owner. To continue, first
> > use
> > the Files page of the Database Properties dialog box or the ALTER
> > AUTHORIZATION statement to set the database owner to a valid login, then
> > add
> > the database diagram support objects. "
> >
> > I am getting the previous error when trying to create diagrams in a
> > database. The database was originally created with a domain user account,
> > but
> > I ran the script "EXEC sp_changedbowner 'sa'" to change the owner to sa.
> > Still having problems.
> >
> > Does anyone know how I could resolve this issue?
>
>

"Database diagram support object cannot be installed...

...because this database does not have a valid owner. To continue, first us
e
the Files page of the Database Properties dialog box or the ALTER
AUTHORIZATION statement to set the database owner to a valid login, then add
the database diagram support objects. "
I am getting the previous error when trying to create diagrams in a
database. The database was originally created with a domain user account, bu
t
I ran the script "EXEC sp_changedbowner 'sa'" to change the owner to sa.
Still having problems.
Does anyone know how I could resolve this issue?Are you on SP1? If not, check the compatability mode of the database, it
needs to be set to 90 in RTM for diagrams to work
select compatibility_level
from sys.databases
where name = 'database name'
To alter it use sp_dbcmptlevel e.g.
EXEC sp_dbcmptlevel 'pubs', 90
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:100CF59A-E171-46B7-969A-18DF27AF8AEE@.microsoft.com...
> ...because this database does not have a valid owner. To continue, first
> use
> the Files page of the Database Properties dialog box or the ALTER
> AUTHORIZATION statement to set the database owner to a valid login, then
> add
> the database diagram support objects. "
> I am getting the previous error when trying to create diagrams in a
> database. The database was originally created with a domain user account,
> but
> I ran the script "EXEC sp_changedbowner 'sa'" to change the owner to sa.
> Still having problems.
> Does anyone know how I could resolve this issue?|||That was it! Thank you!
"Jasper Smith" wrote:

> Are you on SP1? If not, check the compatability mode of the database, it
> needs to be set to 90 in RTM for diagrams to work
> select compatibility_level
> from sys.databases
> where name = 'database name'
> To alter it use sp_dbcmptlevel e.g.
> EXEC sp_dbcmptlevel 'pubs', 90
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:100CF59A-E171-46B7-969A-18DF27AF8AEE@.microsoft.com...
>
>

"CREATE TABLE" for files *.dbf with a custom float column

How to create dbase file based on SQL-92 which have a FLOAT column like
float(14,2):
=> xxxxxxxxxxxxxx,xx <=
Thanks in advance,
PatriceN(14,2)
"news.microsoft.com" <reprotechnic@.wanadoo.fr> escribi en el mensaje
news:u37grkt#DHA.2072@.TK2MSFTNGP11.phx.gbl...
> How to create dbase file based on SQL-92 which have a FLOAT column like
> float(14,2):
> => xxxxxxxxxxxxxx,xx <=
> Thanks in advance,
> Patrice
>|||this solution doesn't run. it makes a float column of (20,4).
With more precision, i test this with odbc.net from Framework.NET v1.1
"Luis Camacho" <luibrac@.yahoo.com.ar> a crit dans le message de
news:%23VMvv5K$DHA.1036@.TK2MSFTNGP10.phx.gbl...
> N(14,2)
> "news.microsoft.com" <reprotechnic@.wanadoo.fr> escribi en el mensaje
> news:u37grkt#DHA.2072@.TK2MSFTNGP11.phx.gbl...
>|||N(14,2): This does not work and gives a syntax error on command.
AndreB.
"Luis Camacho" <luibrac@.yahoo.com.ar> a crit dans le message de
news:%23VMvv5K$DHA.1036@.TK2MSFTNGP10.phx.gbl...
> N(14,2)
> "news.microsoft.com" <reprotechnic@.wanadoo.fr> escribi en el mensaje
> news:u37grkt#DHA.2072@.TK2MSFTNGP11.phx.gbl...
>