MSSQL 2000: How can we track table/sp/function changes?

My company would like to start keeping track of everytime a table, stored procedure, or function is changed.
What is the best way to do this?Option 1)
Create a database for storing metadata on all the other databases. Nightly, run a query that loops across all database and records all the objects along with their checksum values, and notes any objects who's checksum value has changed.

Option 2)
Upgrade to SQL Server 2005 and create database triggers to log changes to objects.|||sourcegears vault, visual sourcesafe. nothing makes it into our software\database builds that is not in our sourcecode management software.|||Revoke sysadmin/DBO rights from everyone but the DBAs.|||As suggested you cna take help of Visual Sourcesafe or PVCS in order to take care of object changes.

Msg 6501

I am unable to create assemblies in SQL Server 2005. Everytime I try to do so I get the dreaded "Msg 6501... could not open the physical" error.

Here is the compile syntax I'm using from within Eclipse 3.2.0, which invokes C# .NET, to create my .dll:

/target:library ${resource_name} /reference:"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlaccess.dll"

I then try to create my my assembly:

create assembly match

from

'C:\Documents and Settings\jeff seeman\workspace\mycsharp\new.dll'

GO

Boom! Bad old Msg 6501. Any suggestions?

Jeff Seeman

What is the full error message?

The last part of it should tell you the specific reason it failed ("The system cannot find the path specified.", "Access Denied", etc.). Since you've probably verified the file exists, I'm guessing it's an Access Denied error, meaning that the account SQL Server is running under does not have access to the file. You should be able to fix this easily by modifying the ACL on the directory or moving the file into a directory that SQL Server is able to access.

Steven

Msg 512 on cursor

Everytime I execute this procedure it gives a msg 512 subquery returns more then one value. Can someone just explain why or what I'm doing wrong? Thanks in advance

Here is my code:

create procedure insert_sku_info
AS
Declare
@.method varchar(40),
@.sku int,
@.location varchar(40)

Declare insert_cur Cursor For
select a.method, s.sku, s.location
from archive_sku a INNER JOIN sku s on a.sku = s.sku
and a.location = s.location

Open insert_cur

Fetch Next from insert_cur
Into @.method, @.sku, @.location

While (@.@.Fetch_Status = 0)
Begin

print @.method + @.sku + @.location

Update sku
set method = @.method
where sku = @.sku
and location = @.location

Fetch Next from insert_cur
Into @.method, @.sku, @.location

End
close insert_cur
deallocate insert_curNever mind I figured it out.

That print statement I had listing all the variables was the problem.

Thanks anyway.