Re: Audit of sql server

From: moondaddy (moondaddy_at_nospam.com)
Date: 10/14/04 

Date: Thu, 14 Oct 2004 10:54:08 -0500

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
>>
>>
>

Relevant Pages

  • Re: Is that OK to run a windows application using a DTS task?
    ... through DTS and the form came up. ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... > execute a windows application which have a user interface awaitting user ...
    (microsoft.public.sqlserver.dts)
  • Re: Execute Persmission denied on object sp_OACreate
    ... > I logged in as that user, tried to execute the DTS ... which then launches the DTS package using the sp_OA* procs? ... account is used during proc execution. ... > proxy account to use in the Job Systems tab of SQL Server Agent ...
    (microsoft.public.sqlserver.security)
  • Re: Audit of sql server
    ... The DTS database transfer makes sense now. ... >> SQL Server will not check on this for you. ... >> Compiles each query but does not execute it. ...
    (microsoft.public.sqlserver.programming)
  • Re: delete some user from the public role
    ... really my problem is related with a DTS execution. ... and when I try to execute the DTS from the ... I don't like to take out this 'denied' to the public role, ... > Jasper Smith (SQL Server MVP) ...
    (microsoft.public.sqlserver.security)
  • Re: Audit of sql server
    ... You could try a 'transfer SQL Server objects' task? ... >> that you coudl SET NOEXEC on and then try to execute your stored ... >> Compiles each query but does not execute it. ...
    (microsoft.public.sqlserver.programming)
Loading