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.

MSSearch.exe error window: "Tracer"

My company has a client using SQLServer 2000 SP3a.
Every so often (approx. once a week), the full-text indexing will fail on
one of our database tables. We are unsure why, but have noticed some rather
bizarre behavior associated with this failure. (Normally, we restart the
MSSearch service and everything works again.)
Just today, the client called to tell us it didn't work. So, using
LogMeIn.com, I controlled their server computer to see what was going on.
Immediately I noticed a messagebox window with a title of "Tracer" and text
of "Release products should NOT call tracer!". The only button was "OK".
I didn't know for sure where this message was coming from, so I used
SysInternals' Process Explorer to list the processes and their window titles.
Lo and behold, MSSearch.exe was the process tied to "Tracer".
Obviously, this is a SQL Server bug, but I've not found reference to it in
any online searches. Has anyone seen this? Is it fixable by upgrading to
SP4 (this client is still using NT4, though).
Thanks for any information.
John,
Basic troubleshooting of SQL Server 2000 and MSSearch is somewhat of a black
art (as I can attest to), but the first place you should look for
information on what is causing the full-text indexing failure is the
server's Application event log as this is the only place where errors,
warning and information events are recorded for the MSSearch service. Look
for all related "Microsoft Search" or MssCi source events (errors, warning
and information) near the time of the failure. This will be the best source
of info as to what is causing the problem.
While I've never have used LogMeIn.com, I have known Microsoft to attach
debuggers to both SQL Server and the MSSearch.exe to identify bugs. If you
believe this to be a bug, then I'd highly recommend that you open a support
case with Microsoft PSS SQL Server support and get this resolved for your
client.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John" <John@.discussions.microsoft.com> wrote in message
news:FAE1B226-FF90-41EC-9465-575285E412FF@.microsoft.com...
> My company has a client using SQLServer 2000 SP3a.
> Every so often (approx. once a week), the full-text indexing will fail on
> one of our database tables. We are unsure why, but have noticed some
> rather
> bizarre behavior associated with this failure. (Normally, we restart the
> MSSearch service and everything works again.)
> Just today, the client called to tell us it didn't work. So, using
> LogMeIn.com, I controlled their server computer to see what was going on.
> Immediately I noticed a messagebox window with a title of "Tracer" and
> text
> of "Release products should NOT call tracer!". The only button was "OK".
> I didn't know for sure where this message was coming from, so I used
> SysInternals' Process Explorer to list the processes and their window
> titles.
> Lo and behold, MSSearch.exe was the process tied to "Tracer".
> Obviously, this is a SQL Server bug, but I've not found reference to it in
> any online searches. Has anyone seen this? Is it fixable by upgrading to
> SP4 (this client is still using NT4, though).
> Thanks for any information.
|||We have the same thing happening sparodically at one of clients as well, The Tracer message pops up, the fulltext search quits working, if we restart mssearch it starts working again.
Only real difference I see, is that our client is running Small Bussiness Server, (Windows 2000, SP4) and I was blaming it on that, since they have all kinds of strange limitations they try to put in it.
I am switching it to update at 4AM each day instead of the continuas background update, and see if that makes a difference.
It only causes trouble when indexing 1 of the two tables we are indexing. The table has 550,000 items and a 5303 MB catalog, with a key count 6 million.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

MSSearch for words between two other words

I've been asked to find a way of doing some complex searches against a
SQL Server 2000 database using MSSearch.
The company I'm contracting to wants to know if there is a way to
perform a search such as:
"sql" within x number of words of "database" (x could be any number)
I'm not that versed with any more than the basic searches within
MSSearch, so this one's a stumper for me. Any help on this would be
appreciated.
They are using ColdFusion to develop the interface between the web and
the database.
Thanks,
CalgaryDataGrl
Hey Calgary Data Girl - I used to live in Calgary - born in Edmonton, and
lived in the nwt for some time!
To answer your question - no, they do have the near operator but it doesn't
define how near. Other search products (like Indexing Services) does allow
you to define the separation in terms of number of words, lines, paragraphs,
pages and chapters.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<calgarydatagrl@.gmail.com> wrote in message
news:1169569044.310656.316520@.s48g2000cws.googlegr oups.com...
> I've been asked to find a way of doing some complex searches against a
> SQL Server 2000 database using MSSearch.
> The company I'm contracting to wants to know if there is a way to
> perform a search such as:
> "sql" within x number of words of "database" (x could be any number)
> I'm not that versed with any more than the basic searches within
> MSSearch, so this one's a stumper for me. Any help on this would be
> appreciated.
> They are using ColdFusion to develop the interface between the web and
> the database.
> Thanks,
> CalgaryDataGrl
>

MSDTC unavailable / JoinTransaction returned 0x8004d01c

Hi! We have 2 database server. 1 at our company, while the other with a web
hosting company.
We wrote stored procedures to update data from tables in our company's
server to the web hosting company's server. Out of 13 of these stored
procedures, 5 failed, giving me the error message that looks like the one
below,
Server: Msg 8501, Level 16, State 1, Procedure sp_ExportCalendar, Line 9
MSDTC on server 'ORION' is unavailable.
Server: Msg 7391, Level 16, State 1, Procedure sp_ExportCalendar, Line 9
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d01c].
Can anyone help me explain what the error mean? I can post stored procedure
if required. Thanks.Hi this is one of the stored procedures that doesn't work. tsp is my user
name, server name.
CREATE PROCEDURE sp_ExportSysConf AS
BEGIN DISTRIBUTED TRANSACTION
IF EXISTS (SELECT * FROM [SysConf])
BEGIN
--Step1: Remove records that do not exist in CIM
DELETE FROM [tsp].[tsp].[tsp].[SysConf]
FROM [tsp].[tsp].[tsp].[SysConf] as C2
WHERE NOT EXISTS (SELECT [C1].[ConfName], [C1].[ConfValue],
[C2].[LastUpdate] FROM [SMSBASE].[DBO].[SysConf] as [C1] where
[C1].[ConfName] = [C2].[ConfName] and [C1].[ConfValue] = [C2].[ConfValue])
IF (@.@.error = 0)
BEGIN
-- no error --> Step 2: Insert new records from CIM
INSERT [tsp].[tsp].[tsp].[SysConf]([ConfName], [ConfValue])
SELECT [ConfName],[ConfValue]
FROM [SMSBASE].[DBO].[SysConf] as C2
WHERE NOT EXISTS (SELECT * FROM [tsp].[tsp].[tsp].[SysConf] as [C1]
where [C1].[ConfName] = [C2].[ConfName] and [C1].[ConfValue] =
[C2].[ConfValue])
IF (@.@.error = 0)
-- no error -> commit
COMMIT TRANSACTION
ELSE
-- error occurred -> rollback
ROLLBACK TRANSACTION
END
ELSE ROLLBACK TRANSACTION
END
ELSE ROLLBACK TRANSACTION
GO