Re: Audit of sql server
From: Keith Kratochvil (sqlguy.back2u_at_comcast.net)
Date: 10/14/04
- Next message: Patrick: "Re: "DataSet" input parametes to Stored Procedures"
- Previous message: Jacco Schalkwijk: "Re: Convert float time to date/time?"
- In reply to: moondaddy: "Re: Audit of sql server"
- Next in thread: Bonj: "Re: Audit of sql server"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 14 Oct 2004 11:15:30 -0500
Good luck! Hopefully the NOEXEC will help.
The DTS database transfer makes sense now. My suggestion: lots of
documentation. Store your changes within VSS (or another version control
software) that can be usd to track changes so that they can be applied to
your databases.
--
Keith
"moondaddy" <moondaddy@nospam.com> wrote in message
news:%23IoWKSgsEHA.636@TK2MSFTNGP09.phx.gbl...
> Thanks Keith that was some good information. I like the idea of writing a
> tool using SET NOEXEC. Regarding the backup-restore. That's what I used
to
> do. however, my databases are hosted remotely on shared servers and I
don't
> have access to restoring databases. So I'm forced to using DTS. and when
I
> found these issues with DTS, it seems like a good idea to clean them up
> anyway, therefore, I'm still looking for a way to cleanup all the little
> issues. Additionally, some of these prevent the maintenance plans from
> working and maintenance plans don't give any useful information when a job
> fails.
>
> --
> moondaddy@nospam.com
> "Keith Kratochvil" <sqlguy.back2u@comcast.net> wrote in message
> news:emdmdIgsEHA.3324@TK2MSFTNGP15.phx.gbl...
> > >Now when I want to export it to
> > another server the DTS will fail due to some kind of data integrity
> > conflict
> > such as a required relationship is missing a value, or maybe even
> > something
> > like a table was removed and orphaned a foreign key on another table. <
> >
> > Why are you using DTS to move your database?
> > BACKUP and RESTORE or sp_detach_db (file copy) and sp_attach_db are much
> > easier.
> >
> > Regarding stored procedures referencing tables that do not exist
anymore,
> > SQL Server will not check on this for you. It might be something that a
> > third party app could handle. One SQL Server option that comes to mind
is
> > that you coudl SET NOEXEC on and then try to execute your stored
> > procedures.
> >
> > From Books Online:
> > SET NOEXEC
> > Compiles each query but does not execute it.
> >
> > Syntax
> > SET NOEXEC { ON | OFF }
> >
> > Remarks
> > When SET NOEXEC is ON, Microsoft® SQL ServerT compiles each batch of
> > Transact-SQL statements but does not execute them. When SET NOEXEC is
OFF,
> > all batches are executed after compilation.
> >
> > The execution of statements in SQL Server consists of two phases:
> > compilation and execution. This setting is useful for having SQL Server
> > validate the syntax and object names in Transact-SQL code when
executing.
> > It
> > is also useful for debugging statements that would usually be part of a
> > larger batch of statements.
> >
> > The setting of SET NOEXEC is set at execute or run time and not at parse
> > time.
> >
> > Permissions
> > SET NOEXEC permissions default to all users.
> >
> >
> >
> > --
> > Keith
> >
> >
> > "moondaddy" <moondaddy@nospam.com> wrote in message
> > news:O1hHr8fsEHA.3984@TK2MSFTNGP09.phx.gbl...
> >> I've been working on a database for over a year now and it has about
200
> >> tables, lots of 'many to many' relationships, and about 20 mb of data.
> > Over
> >> the past year its evolved quite a bit. Now when I want to export it to
> >> another server the DTS will fail due to some kind of data integrity
> > conflict
> >> such as a required relationship is missing a value, or maybe even
> > something
> >> like a table was removed and orphaned a foreign key on another table.
> > Right
> >> now the only way I can find these is to run the DTS and then when it
> > fails,
> >> hope that there's enough information in the error msg to find the
problem
> >> and fix it. This process can take for ever if there's a lot of error
and
> >> I'm running DTS to a remote server across the internet.
> >>
> >> Obviously sql server is smart enough to know something is wrong during
> >> the
> >> DTS, so is there something I can run that will do an audit on the
entire
> > db
> >> and list all the problems? It would also be great if there was
something
> >> that would also go through all the stored procedures and view and tell
me
> >> where there are references to table names or column names that don't
> >> exist
> >> anymore.
> >>
> >> Can anyone recommend how to do this?
> >>
> >> Thanks.
> >>
> >> --
> >> moondaddy@nospam.com
> >>
> >>
> >
>
>
- Next message: Patrick: "Re: "DataSet" input parametes to Stored Procedures"
- Previous message: Jacco Schalkwijk: "Re: Convert float time to date/time?"
- In reply to: moondaddy: "Re: Audit of sql server"
- Next in thread: Bonj: "Re: Audit of sql server"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
