maintaining/altering database

Our database(s) are all over the place - no documentation - lot's of
rubbish and unused stuff.
I'm managing a project focusing on data quality that covers code
changes, alterations to DTS packages, schema changes etc etc.
What I'd like to do is see where the bit I want to change is being
used.
that might mean what stored procs use a field and what sprocs use that
sproc.
maybe it's which dts packages use a sproc (and again up the
hieararchy)
The list is a long one but basically I need to know what the effects
are of changes.
Is there a tool out there that lets me navigate a database to that
level of detail - I understand something along the same lines is
available for MS Access but I can't find it for SQL Server.
ThanksRed-Gate has a pretty cool tool called SQL Dependency Tracker that you
should check out:
http://www.red-gate.com/products/SQ...acker/index.htm
There's also a stored proc in SQL Server called sp_depends which queries
an internal view called sysdepends. You can also check that out if you
want to spend $0 on new software. A warning: sysdepends is not that
reliable, or at least it hasn't been for me in the past. I don't really
know if SQL Server 2005 makes it more useful than it was in 2000. Does
anyone on the group know if there have been any significant enhancements
to sysdepends in 2005?
oliver wrote:
> Our database(s) are all over the place - no documentation - lot's of
> rubbish and unused stuff.
> I'm managing a project focusing on data quality that covers code
> changes, alterations to DTS packages, schema changes etc etc.
> What I'd like to do is see where the bit I want to change is being
> used.
> that might mean what stored procs use a field and what sprocs use that
> sproc.
> maybe it's which dts packages use a sproc (and again up the
> hieararchy)
> The list is a long one but basically I need to know what the effects
> are of changes.
> Is there a tool out there that lets me navigate a database to that
> level of detail - I understand something along the same lines is
> available for MS Access but I can't find it for SQL Server.
> Thanks
>|||On 26 Feb, 22:54, David Markle <new...@.markleconsulting.c0m> wrote:
> Red-Gate has a pretty cool tool called SQL Dependency Tracker that you
> should check out:
> http://www.red-gate.com/products/SQ...acker/index.htm
> There's also a stored proc in SQL Server called sp_depends which queries
> an internal view called sysdepends. You can also check that out if you
> want to spend $0 on new software. A warning: sysdepends is not that
> reliable, or at least it hasn't been for me in the past. I don't really
> know if SQL Server 2005 makes it more useful than it was in 2000. Does
> anyone on the group know if there have been any significant enhancements
> to sysdepends in 2005?
>
> oliver wrote:
>
>
>
> - Show quoted text -
it's not really fully featured enough I don' think - money's not
really too much of an issue - surely there something a bit more meaty?

maintaining/altering database

Our database(s) are all over the place - no documentation - lot's of
rubbish and unused stuff.
I'm managing a project focusing on data quality that covers code
changes, alterations to DTS packages, schema changes etc etc.
What I'd like to do is see where the bit I want to change is being
used.
that might mean what stored procs use a field and what sprocs use that
sproc.
maybe it's which dts packages use a sproc (and again up the
hieararchy)
The list is a long one but basically I need to know what the effects
are of changes.
Is there a tool out there that lets me navigate a database to that
level of detail - I understand something along the same lines is
available for MS Access but I can't find it for SQL Server.
Thanks
Red-Gate has a pretty cool tool called SQL Dependency Tracker that you
should check out:
http://www.red-gate.com/products/SQL_Dependency_Tracker/index.htm
There's also a stored proc in SQL Server called sp_depends which queries
an internal view called sysdepends. You can also check that out if you
want to spend $0 on new software. A warning: sysdepends is not that
reliable, or at least it hasn't been for me in the past. I don't really
know if SQL Server 2005 makes it more useful than it was in 2000. Does
anyone on the group know if there have been any significant enhancements
to sysdepends in 2005?
oliver wrote:
> Our database(s) are all over the place - no documentation - lot's of
> rubbish and unused stuff.
> I'm managing a project focusing on data quality that covers code
> changes, alterations to DTS packages, schema changes etc etc.
> What I'd like to do is see where the bit I want to change is being
> used.
> that might mean what stored procs use a field and what sprocs use that
> sproc.
> maybe it's which dts packages use a sproc (and again up the
> hieararchy)
> The list is a long one but basically I need to know what the effects
> are of changes.
> Is there a tool out there that lets me navigate a database to that
> level of detail - I understand something along the same lines is
> available for MS Access but I can't find it for SQL Server.
> Thanks
>
|||On 26 Feb, 22:54, David Markle <new...@.markleconsulting.c0m> wrote:
> Red-Gate has a pretty cool tool called SQL Dependency Tracker that you
> should check out:
> http://www.red-gate.com/products/SQL_Dependency_Tracker/index.htm
> There's also a stored proc in SQL Server called sp_depends which queries
> an internal view called sysdepends. You can also check that out if you
> want to spend $0 on new software. A warning: sysdepends is not that
> reliable, or at least it hasn't been for me in the past. I don't really
> know if SQL Server 2005 makes it more useful than it was in 2000. Does
> anyone on the group know if there have been any significant enhancements
> to sysdepends in 2005?
>
> oliver wrote:
>
>
> - Show quoted text -
it's not really fully featured enough I don' think - money's not
really too much of an issue - surely there something a bit more meaty?

maintaining/altering database

Our database(s) are all over the place - no documentation - lot's of
rubbish and unused stuff.
I'm managing a project focusing on data quality that covers code
changes, alterations to DTS packages, schema changes etc etc.
What I'd like to do is see where the bit I want to change is being
used.
that might mean what stored procs use a field and what sprocs use that
sproc.
maybe it's which dts packages use a sproc (and again up the
hieararchy)
The list is a long one but basically I need to know what the effects
are of changes.
Is there a tool out there that lets me navigate a database to that
level of detail - I understand something along the same lines is
available for MS Access but I can't find it for SQL Server.
ThanksRed-Gate has a pretty cool tool called SQL Dependency Tracker that you
should check out:
http://www.red-gate.com/products/SQL_Dependency_Tracker/index.htm
There's also a stored proc in SQL Server called sp_depends which queries
an internal view called sysdepends. You can also check that out if you
want to spend $0 on new software. A warning: sysdepends is not that
reliable, or at least it hasn't been for me in the past. I don't really
know if SQL Server 2005 makes it more useful than it was in 2000. Does
anyone on the group know if there have been any significant enhancements
to sysdepends in 2005?
oliver wrote:
> Our database(s) are all over the place - no documentation - lot's of
> rubbish and unused stuff.
> I'm managing a project focusing on data quality that covers code
> changes, alterations to DTS packages, schema changes etc etc.
> What I'd like to do is see where the bit I want to change is being
> used.
> that might mean what stored procs use a field and what sprocs use that
> sproc.
> maybe it's which dts packages use a sproc (and again up the
> hieararchy)
> The list is a long one but basically I need to know what the effects
> are of changes.
> Is there a tool out there that lets me navigate a database to that
> level of detail - I understand something along the same lines is
> available for MS Access but I can't find it for SQL Server.
> Thanks
>|||On 26 Feb, 22:54, David Markle <new...@.markleconsulting.c0m> wrote:
> Red-Gate has a pretty cool tool called SQL Dependency Tracker that you
> should check out:
> http://www.red-gate.com/products/SQL_Dependency_Tracker/index.htm
> There's also a stored proc in SQL Server called sp_depends which queries
> an internal view called sysdepends. You can also check that out if you
> want to spend $0 on new software. A warning: sysdepends is not that
> reliable, or at least it hasn't been for me in the past. I don't really
> know if SQL Server 2005 makes it more useful than it was in 2000. Does
> anyone on the group know if there have been any significant enhancements
> to sysdepends in 2005?
>
> oliver wrote:
> > Our database(s) are all over the place - no documentation - lot's of
> > rubbish and unused stuff.
> > I'm managing a project focusing on data quality that covers code
> > changes, alterations to DTS packages, schema changes etc etc.
> > What I'd like to do is see where the bit I want to change is being
> > used.
> > that might mean what stored procs use a field and what sprocs use that
> > sproc.
> > maybe it's which dts packages use a sproc (and again up the
> > hieararchy)
> > The list is a long one but basically I need to know what the effects
> > are of changes.
> > Is there a tool out there that lets me navigate a database to that
> > level of detail - I understand something along the same lines is
> > available for MS Access but I can't find it for SQL Server.
> > Thanks- Hide quoted text -
> - Show quoted text -
it's not really fully featured enough I don' think - money's not
really too much of an issue - surely there something a bit more meaty?

Maintaining Variable After EXEC

Hello,

I am fairly new at stored procedures. I have created some that will
go through a table and return a start date and an end date that is
dependent upon the fiscal period you want, but I then need to use
those dates in another stored procedure to retrieve the information I
need. My stored procedure looks like this.

================================================== ====================

CREATE PROCEDURE dbo.R920ExtTotal
@.MthsBack Decimal OUTPUT
AS

DECLARE @.sSQL AS NVARCHAR(255), @.StartDate as SMALLDATETIME, @.EndDate
as SMALLDATETIME

Exec @.StartDate = GetMthStart @.MthsBack

Exec @.EndDate = GetMthEnd @.MthsBack

SET @.sSQL = 'Select count(extension) as Total From r920f00 Where
([date] BETWEEN "' +
CONVERT(nvarchar, @.StartDate) +
'" and "' +
CONVERT(nvarchar, @.EndDate) +
'")'

Select @.sSQL

EXEC (@.sSQL)

Return
GO

================================================== ===============

The problem is my variables @.StartDate and @.EndDate do not retain
their values after the EXEC statement and revert to 01/01/1900. How
can I get around this problem?

Thanks!!!!

ChipVariables are only available in the scope within which they are declared. If
you need to move values out of a stored procedure, you must use output
parameters for all of them. Also, why are you building dynamic SQL in your
procedure? You can use the variables directly in SQL queries - there is no
need to convert them to strings, and insert them into a SQL string.

On 6 Jan 2004 11:05:01 -0800, cmayan@.lesliecontrols.com (Chip Mayan) wrote:

>Hello,
>I am fairly new at stored procedures. I have created some that will
>go through a table and return a start date and an end date that is
>dependent upon the fiscal period you want, but I then need to use
>those dates in another stored procedure to retrieve the information I
>need. My stored procedure looks like this.
>================================================== ====================
>CREATE PROCEDURE dbo.R920ExtTotal
>@.MthsBack Decimal OUTPUT
>AS
>DECLARE @.sSQL AS NVARCHAR(255), @.StartDate as SMALLDATETIME, @.EndDate
>as SMALLDATETIME
>Exec @.StartDate = GetMthStart @.MthsBack
>Exec @.EndDate = GetMthEnd @.MthsBack
>SET @.sSQL = 'Select count(extension) as Total From r920f00 Where
>([date] BETWEEN "' +
>CONVERT(nvarchar, @.StartDate) +
>'" and "' +
>CONVERT(nvarchar, @.EndDate) +
>'")'
>Select @.sSQL
>EXEC (@.sSQL)
>Return
>GO
>================================================== ===============
>The problem is my variables @.StartDate and @.EndDate do not retain
>their values after the EXEC statement and revert to 01/01/1900. How
>can I get around this problem?
>Thanks!!!!
>Chip|||Chip Mayan (cmayan@.lesliecontrols.com) writes:
> I am fairly new at stored procedures. I have created some that will
> go through a table and return a start date and an end date that is
> dependent upon the fiscal period you want, but I then need to use
> those dates in another stored procedure to retrieve the information I
> need. My stored procedure looks like this.
>================================================== ====================
> CREATE PROCEDURE dbo.R920ExtTotal
> @.MthsBack Decimal OUTPUT
> AS
> DECLARE @.sSQL AS NVARCHAR(255), @.StartDate as SMALLDATETIME, @.EndDate
> as SMALLDATETIME
> Exec @.StartDate = GetMthStart @.MthsBack
> Exec @.EndDate = GetMthEnd @.MthsBack
> SET @.sSQL = 'Select count(extension) as Total From r920f00 Where
> ([date] BETWEEN "' +
> CONVERT(nvarchar, @.StartDate) +
> '" and "' +
> CONVERT(nvarchar, @.EndDate) +
> '")'
> Select @.sSQL
> EXEC (@.sSQL)
> Return
> GO

I'm afraid that there are a couple of errors or strange things in this
procedure.

First: there is absolutely no reason to use dynamic SQL here. Just write:

SELKCT count(extension) AS Total
FROM r920f00
WHERE [date] BETWEEN @.StartDate AND @.EndDate

Second: the calls to set @.StartDate and @.EndDate looks funny. If
GetMthStart and GetMthEnd are user-defined functions it would be alright,
but you indicated that they were stored procedures. The return value from
a stored procedure is always an integer value, so you cannot return a
date here. And I would strongly recommend you to use return values solely
for indication of success/failure (with 0 meaning success, and about
everything else meaning failure.) So you would have to make the output
parameters:

EXEC GetMthStart @.MthsBack, @.StartDate OUTPUT

Third: the @.MthsBack parameter is declared as output, but you never assign
it any value, you only seem to use it for input.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Maintaining user formatting in multiline textbox

In a multiline textbox, how do you maintain paragraph breaks that users may create (like what you see on this forum)?

Are the returns replaced by something else just before being saved to the database? Any assistance would be appreciated!

MichaelWhat I have done with classic ASP is replace all vbCRLF with <BR>. I believe it is the same thing with ASP.NET.

Terri

maintaining unique keys when offline

If you have a "Orders" table that is being sync'd to subscribers that are ocassionaly offline, and the subscribers add rows to their local Orders table. When they go online to sync with the published "Orders" table, how do you handle keeping the "OrderId" field unique?

Example:

Both salespeople sync the following data down:

OrderId Desc

1 Order 1

2 Test Order

Both salespeople go offline and add orders

Salesperson 1 adds:

OrderId Desc

3 Joes Order

Salesperson 2 adds:

OrderId Desc

3 Kathys Order

Now, when they go back online, they both will sync their orders up to the main database and they both have the OrderId of 3.

The main problem with using an int identity as a primary key is that it gets assigned by the database on insert; as you're discovering, assigning it outside of the database creates key collisions.

There are several different approaches you can use. All of them have problems:

1) If the row contains some combination of values that are always unique, use this combination as the primary key.

Problems with this approach: Not always possible. If the table's going to be referenced as part of a foreign-key relationship, you have to replicate all of the parts of the key in the referencing rows.

2) Use a composite primary key with two columns, or a primary key that's a concatenation of two values. One is a token that's unique to each process that's creating records; the second is a sequentially-assigned value that each process is responsible for assigning. In your example, you'd use the salesperson as the token. So you could either make salesperson, order id the primary key, or you could create nvarchar keys like "joe:1".

Problems with this approach: The token has to be invariant, i.e. changing the salesperson on the order can create key collisions. The database can no longer assign primary keys, since the PKs are being assigned offline, outside of the database.

3) Use a primary key of type uniqueidentifier (i.e.a GUID). All GUIDs are unique, so you'll never have a key collision.

Problems with this approach: your identifier won't be usable by humans. Sorting by GUIDs is useless. GUIDs use 32 bytes of storage, as opposed to 4 for int.

4) Use a temporary local key (using either of the above 2 approaches) when creating records on the client, and assign the real key when the row is inserted.

Problems with this approach: The client won't automatically know what the keys are of the rows it just inserted into the database, and will have to re-query the database to get their values.

|||

SQL replication also has the ability to assign identity ranges to subscribers. You might want to look into it as a solution. The only problem is in a high subscriber, high volume scenario is assigning an appropriate range to each subscriber.

I looked into this solution for the company I currently work for.We decided using a GUID was a much better solution.It eliminates the need to monitor the identity ranges.

maintaining triggers on initial snapshot...

I have transactional replication setup with an updatable subscriber. I want
the trigger on the publisher to transfer over to the destination during the
initial snapshot (the drop/create is where I lose the trigger). In the GUI,
properties of the publisher, Articles tab, clicking the elispse then on the
Snapshot tab, there is a checkbox 'User Triggers'. But when I check this and
reinitialize the snapshot and subscriber, the table get dropped and
recreated on the subscriber (as it should) but again WITHOUT the triggers
that are on the publisher.
Any help is greatly appreciated.
Thanks.
Appendage to initial post...
So i discovered the @.schema_option = 0x100 Replicates user triggers on a
table article, if defined.
and when i script out my publisher i see
@.schema_option = 0x000000000000CEF3 and
@.schema_option = 0x000000000000CFF3
(the first one is for a table that does not have the user triggers option
set and the second one does so it makes sense that one has E and the other
F. But this is still not working.
On the publisher I have a trigger:
CREATE TRIGGER tr_CustomerAsset_BreakoutClassifyFieldsAndMatchEqu ipment ON
dbo.CustomerAsset
but after snapshot, this trigger is not at the subscriber.
Thanks everyone!
"Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
news:eoOv3bOZHHA.4264@.TK2MSFTNGP05.phx.gbl...
>I have transactional replication setup with an updatable subscriber. I want
>the trigger on the publisher to transfer over to the destination during the
>initial snapshot (the drop/create is where I lose the trigger). In the GUI,
>properties of the publisher, Articles tab, clicking the elispse then on the
>Snapshot tab, there is a checkbox 'User Triggers'. But when I check this
>and reinitialize the snapshot and subscriber, the table get dropped and
>recreated on the subscriber (as it should) but again WITHOUT the triggers
>that are on the publisher.
> Any help is greatly appreciated.
> Thanks.
>
|||This should work. I would try to re-apply them using a post snapshot script.
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
"Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
news:eoOv3bOZHHA.4264@.TK2MSFTNGP05.phx.gbl...
>I have transactional replication setup with an updatable subscriber. I want
>the trigger on the publisher to transfer over to the destination during the
>initial snapshot (the drop/create is where I lose the trigger). In the GUI,
>properties of the publisher, Articles tab, clicking the elispse then on the
>Snapshot tab, there is a checkbox 'User Triggers'. But when I check this
>and reinitialize the snapshot and subscriber, the table get dropped and
>recreated on the subscriber (as it should) but again WITHOUT the triggers
>that are on the publisher.
> Any help is greatly appreciated.
> Thanks.
>

Maintaining the database

If a database is keeping larger and larger in size, and having more than 1M
records.
How to improve the search speed in that database, even I did index the key..
Thanks
Indexing is the key (no pun intended) to search performance. However, you
also need to make sure query expressions are sargable and the query is well
written.
Perhaps someone can offer specific suggestions if you post the CREATE TABLE,
indexes, query and sample data.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Michael Lam" <[nospam]lamsoft@.lamsoft.net> wrote in message
news:uLjaiGfeIHA.4312@.TK2MSFTNGP03.phx.gbl...
> If a database is keeping larger and larger in size, and having more than
> 1M
> records.
> How to improve the search speed in that database, even I did index the
> key..
> Thanks
>

Maintaining the database

If a database is keeping larger and larger in size, and having more than 1M
records.
How to improve the search speed in that database, even I did index the key..
ThanksIndexing is the key (no pun intended) to search performance. However, you
also need to make sure query expressions are sargable and the query is well
written.
Perhaps someone can offer specific suggestions if you post the CREATE TABLE,
indexes, query and sample data.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Michael Lam" <[nospam]lamsoft@.lamsoft.net> wrote in message
news:uLjaiGfeIHA.4312@.TK2MSFTNGP03.phx.gbl...
> If a database is keeping larger and larger in size, and having more than
> 1M
> records.
> How to improve the search speed in that database, even I did index the
> key..
> Thanks
>

Maintaining SQL data on a remote host.

Hi,
What is the preferred way to maintain SQL tables on a remote host?
I am a newbie to building ASP.NET websites on a remote host.
A stumbling point has been the maintenance of SQL tables on the remote host.
I understand about doing complete backup and restores,
but I am seeking a quicker way to maintain individual files.
I would like to click and edit but instead am going through the following 30+ clicks.
Is there a easier way?
Thanks.

For example, what I do now to build a new data table for a hosted website.
1) Design table
1a) Name
1b) Fields & Types

2) SQL Server Management Studio Express (assuming existing database)
2a) Select Database & Tables
2b) Add new table
2c) Add fields, Key must be INT for ACCESS
2d) Save as (Name_Table)

3) MS Access (requires ODBC to be setup first through the Windows control panel)
3a) Tables / New / Link / ODBC /Machine_Data_Source
3b) Pick table
3c) Edit data, as needed

4) To transfer data, first:
Select the database in the VWD solution explorer,
then right-click and select the new "Publish to Provider"
4a) Database Publishing Wizard
4b) Choose table to script a backup from
4c) Build script & Copy

5) Start Ipswitch FTP ( this step can be rplaced by 6e below)
5a) locate folder & sql script file and choose destination directory
5b) Transfer file

6) Login to remote host host (1and1)
6a) MS SQL Administration
6b) Admin (MyLittleTools Admin)
6c) Tools
6d) Quey Analyser
6e) Paste script (from step 4)
6f) Submit (Run)
6g) Verify table built

FYI: Script to build and populate the new table "Name_Table"
Built by step 4c above, pasted into remote Hosts Query Analyzer by step 6e above.

/****** Object: Table [dbo].[Name_Table] Script Date: 10/28/2007 18:03:58 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Name_Table]') AND type in (N'U'))
DROP TABLE [dbo].[Name_Table]
GO
/****** Object: Table [dbo].[Name_Table] Script Date: 10/28/2007 18:03:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Name_Table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Name_Table](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Acsz] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fax] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Name_Table_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =

ON)
)
END
GO
INSERT [dbo].[Name_Table] ([ID], [Name], [Address], [City], [State], [Zip], [Acsz], [Phone], [Fax]) VALUES (1, NULL, NULL,

NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Name_Table] ([ID], [Name], [Address], [City], [State], [Zip], [Acsz], [Phone], [Fax]) VALUES (2, NULL, NULL,

NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Name_Table] ([ID], [Name], [Address], [City], [State], [Zip], [Acsz], [Phone], [Fax]) VALUES (3, N'Third

name', NULL, NULL, NULL, NULL, NULL, NULL, NULL)

You should take a good look at SSIS (Sql Server Integration Services).

It's quite nice and made for moving data from one database to another.

Another option is to create a database link from one server to another and use t-sql to move the data. Would recommend using SSIS if possible.

Maintaining Security

I am a beginer in SQL Server. I have developed a simple accounting application in VB and SQL. Now I have successfully completed my application. Now I want to deploy it to my client. So I installed SQl Server and required VB components in the clients computer. I also created 'sa' login and secret password only know by me. I thought my data in that clients computer was full safe but later on i found that we can also connect to the sql server using the NT administrative account and easily change the data of the database. So now I am worried that if someone enters and access the clients computer with administrator's password then he/she can change my data resulting the corruption of the data. So is there any way that I can prevent the access the database to the client with the NT administrative account or any way 2 track the way the data changed?

I am probably missing something from your application description; please correct me if any of my assumptions is wrong. I will list a few assumptions based on my understanding and try to answer your question and you some recommendations based on them:

* There is one SQL Server 2005 machine that can be remotely access by multiple clients running your application.

* You have a hardcoded SA password embedded in your application

I want to start by pointing out that by default members of the Windows Administrators group on the machine running SQL Server will have access to via this membership. You can remove such privilege to prevent an accidental access by removing the BUILTIN\Administrators login from SQL Server: DROP LOGIN [BUILTIN\Administrators]

It is important to remark that this will prevent the Windows administrators from connecting to SQL Server 2005 when the server is running normally, but any local administrator will be able to connect to SQL Server 2005 by starting the server in single-user-mode, this is allowed for system maintenance and to prevent accidental lockouts from the system. It is also important to note that it is pretty much impossible to stop an adversary that already has full access to the SQL Server 2005 machine as system administrator.

The next thing to note, and that hopefully is something I misunderstood is the hardcoded SA password in your application. I strongly recommend against such practice as an adversary can easily recover the plaintext password from your client and compromise the data. Even more, the account you are using in this case is SA; I strongly recommend using the “least privilege principle” and define roles for your application, based on the most common tasks. Probably you have administrative tasks (create users, tables, etc.), read-write tasks (update, inserts, etc.) and read-only tasks (search and read data, but not write). I would recommend creating different principals for such tasks and grant only the required privileges for each principal type, restricting access to the most privileged accounts (such as SA) only to users that require such high privileges.

Additionally I want to remind you of a new feature in SQL Server 2005 that may affect your design in case you use hardcoded passwords: password policy. SQL Server 2005 running on Windows 2003 can take advantage of the Windows password policies and use them in SQL logins (including SA); these password policies allows a domain/machine administrator to define password complexity, life-time, lockout policies, etc. in a centralized way. When running on Windows 2003, SQL Server will use this new feature by default, but you can explicitly turn it off for any given SQL account. It is highly recommended to take advantage of this feature especially for SA, but if you have hardcoded passwords (or passwords that are difficult to update in your app) you will probably have to disable the password policy for the accounts you use in your app.

Detect unexpected value changes is a little tricky. One possibility here may be to use a certificate to sign the data and store the signature along with the data; this way your application can verify that the signature is valid before consuming the data.

I hope this information will be useful, let us know if you have further questions or/and feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||Thanks. As I am only concern abt the protection of my data I wanna suggestion on "how do I safe gaurd my data" so only the login used by me or my program can change/add/remove the data from my database not other users from any other program. And I also wanna know that whether can I track the activities of third user who changes the data in my database.|||

You might want to take a look at a thread that is related to your question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=371562&SiteID=1.

You can only achieve what you are asking if you have full control over the machine where your program is installed. Otherwise, you could write your program, so you could detect if someone changed the data, but you would not be able to prevent such changes. In other words, there is no way to prevent corruption of your data by a third party.

Thanks
Laurentiu

Maintaining Replication

I am trying to maintain replication from an app I have written.
Occasionally, a subscription will have a problem and error out. When the
subscription is restarted, it often will not start resynching until if feels
like it.
What I am looking to do is check the status of all subscriptions and start
synchronization for those that have stopped. Also, I want to do this without
resorting to EM.
Thanks,
Austin
Are you using SQL Server 2000 or SQL Server 2005.
2005 has a rich interface called RMO that you can use to build monitoring
applications, In 2000 you can use DMO to but to a lesser extent in terms of
functionality,
"Austin Summers" wrote:

> I am trying to maintain replication from an app I have written.
> Occasionally, a subscription will have a problem and error out. When the
> subscription is restarted, it often will not start resynching until if feels
> like it.
> What I am looking to do is check the status of all subscriptions and start
> synchronization for those that have stopped. Also, I want to do this without
> resorting to EM.
> Thanks,
> --
> Austin
|||You can have a replication alert fire a job on agent failure, or add a 4th
job step to fire when the agent fails, or even have the third job step loop
back to the first job step on step 3 job failure.
Depending on your app you can build logic in here to decide on how to
respond to different classes of errors. For instance if you are using
ActiveX scripts use the status message and parse it looking for errors.
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
"Austin Summers" <AustinSummers@.discussions.microsoft.com> wrote in message
news:4DD99011-3B98-49DE-BCDE-D818F964D209@.microsoft.com...
>I am trying to maintain replication from an app I have written.
> Occasionally, a subscription will have a problem and error out. When the
> subscription is restarted, it often will not start resynching until if
> feels
> like it.
> What I am looking to do is check the status of all subscriptions and start
> synchronization for those that have stopped. Also, I want to do this
> without
> resorting to EM.
> Thanks,
> --
> Austin
|||Hilary,
How do you modify the agent steps at subscription setup? I am using
sp_addmergesubscription to create the subscription. Is there another sp I
can run to make further changes?
Primarily, this will affect MSDE users who disconnect a laptop and then
reconnect needing their data synched both ways. Also, I am finding that if
the publisher cannot find a subscriber, it varies greatly how long it will
keep searching. One test kept trying for a week, another test stopped after
15 minutes. Is there a stored procedure that will startup bi-directional
synchronization?
Thanks,
Austin
"Hilary Cotter" wrote:

> You can have a replication alert fire a job on agent failure, or add a 4th
> job step to fire when the agent fails, or even have the third job step loop
> back to the first job step on step 3 job failure.
> Depending on your app you can build logic in here to decide on how to
> respond to different classes of errors. For instance if you are using
> ActiveX scripts use the status message and parse it looking for errors.
> --
> 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
> "Austin Summers" <AustinSummers@.discussions.microsoft.com> wrote in message
> news:4DD99011-3B98-49DE-BCDE-D818F964D209@.microsoft.com...
>
>
|||You can't, you could manually create a profile and then point it at this
profile. ActiveX controls allow you to do this. Have a look at sp_start_job
to start the replication process.
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
"Austin Summers" <AustinSummers@.discussions.microsoft.com> wrote in message
news:89C88E48-F1C9-4B2F-8867-8803D7CB022D@.microsoft.com...[vbcol=seagreen]
> Hilary,
> How do you modify the agent steps at subscription setup? I am using
> sp_addmergesubscription to create the subscription. Is there another sp I
> can run to make further changes?
> Primarily, this will affect MSDE users who disconnect a laptop and then
> reconnect needing their data synched both ways. Also, I am finding that
> if
> the publisher cannot find a subscriber, it varies greatly how long it will
> keep searching. One test kept trying for a week, another test stopped
> after
> 15 minutes. Is there a stored procedure that will startup bi-directional
> synchronization?
> Thanks,
> --
> Austin
>
> "Hilary Cotter" wrote:
|||Hilary,
Thanks, sp_start_job works nicely. But, I can't seem to find a status flag
that tells me if a subscription has stopped. Does one exist or should I just
run the restart job whether it is needed or not (and simply trap the error)?
Also, I am getting different behavior from subscriptions setup the same way.
On one, the users takes home their laptop and the agent keeps checking all
night and reconnects when the user returns in the morning. On my other
subscription, it tries to reconnect the default 10 times and then quits. I
can't find where that setting is.
Thanks,
Austin
"Hilary Cotter" wrote:

> You can't, you could manually create a profile and then point it at this
> profile. ActiveX controls allow you to do this. Have a look at sp_start_job
> to start the replication process.
> --
> 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
> "Austin Summers" <AustinSummers@.discussions.microsoft.com> wrote in message
> news:89C88E48-F1C9-4B2F-8867-8803D7CB022D@.microsoft.com...
>
>

Maintaining Replication

Is there any nightly maintenance tasks I can run that will keep my
replication going smooth? I remember reading somewhere about re-indexing
replication tables, but could somebody offer some more insight as to what I
could do to keep everything afloat?
Best Regards,
Brad
There is no real maintenance you can do. You might want to check for
expiring subscriptions, query the history tables for this info.
For merge replication you may wish to reindex contents, tombstone, and
genhistory.
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
"Brad M." <anonymous@.discussions.microsoft.com> wrote in message
news:%23inwUB2pFHA.1096@.TK2MSFTNGP11.phx.gbl...
> Is there any nightly maintenance tasks I can run that will keep my
> replication going smooth? I remember reading somewhere about re-indexing
> replication tables, but could somebody offer some more insight as to what
I
> could do to keep everything afloat?
> Best Regards,
> Brad
>
|||I have always found replication (snapshot & transactional) to be a very
fussy animal. It is fine if it works however if it comes across
something it doesn't like that takes system resources (i.e. - db
backup, index rebuild, etc) it stops working and sulks like a petulant
child!
To solve most of these replication "tantrums" I have used the Query
timeout setting on the Distribution Agent and set it to 30 mins (or
more) and basically letting the system sort it self out and telling
replication to wait then try again - bit like giving a child a
lollipop ;o)
Obviously everyone has a different replication systems but I have found
that this has worked well for me.
Rgds,
qh

Maintaining Remote SQL Database

Hi,
I have a sql2k server hosted by my ISP. I need to get the database from
my local development server installed on the sql2k server maintained by
my ISP.
My ISP has told me that there is no enterprise manager access to the
server, any changes to the database need to be done through script or
some other means.
Can anyone provide some guidance on the best practices/methods for doing
this, the thought of manually creating all the tables/relationships/sp's
etc.. on the other machine sounds quite a complex process.
Thanks in advance,
Kieran
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!For the initial database create they will probably allow you to backup your
database and they will restore it. alternitavly use sp_detach_db and they
can attach.
For updates to procs etc you can issue CREATE / DROP / ALTER statements
through Query Analyser or send the .SQL scripts to the ISP
--
HTH
Ryan Waight, MCDBA, MCSE
"Kieran" <anonymous@.devdex.com> wrote in message
news:eePAoX%23kDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a sql2k server hosted by my ISP. I need to get the database from
> my local development server installed on the sql2k server maintained by
> my ISP.
> My ISP has told me that there is no enterprise manager access to the
> server, any changes to the database need to be done through script or
> some other means.
> Can anyone provide some guidance on the best practices/methods for doing
> this, the thought of manually creating all the tables/relationships/sp's
> etc.. on the other machine sounds quite a complex process.
> Thanks in advance,
> Kieran
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Kieran <anonymous@.devdex.com> wrote in message news:<eePAoX#kDHA.1948@.TK2MSFTNGP12.phx.gbl>...
> My ISP has told me that there is no enterprise manager access to the
> server, any changes to the database need to be done through script or
> some other means.
> Can anyone provide some guidance on the best practices/methods for doing
> this, the thought of manually creating all the tables/relationships/sp's
> etc.. on the other machine sounds quite a complex process.
Hi
A web-based administration tool like myLittleAdmin can save you a lot
of time and work. More info, demo and free trial on
http://www.mylittletools.net/mla_sql
Best regards
el.c.

Maintaining partitioned views

Hello,

I have a large set of data that I have set up as a partitioned view.
The view is partitioned by a datetime column and the individual tables
each represent one month's worth of data. I need to keep at least two
year's worth of data at all times, but after two years I can archive
the data. A sample of the code used is below. It is simplified for
space reasons.

My question is, how do other people maintain the database in this type
of scenario? I could create all of the tables necessary for the next
year and then go through that at the end of each year (archive tables
over two years, add new tables, and change the view), but I was also
thinking that I might be able to write a stored procedure that runs
once a month and does all three of those tasks automatically. It seems
like a lot of dynamic SQL code though for something like that.
Alternatively, I could write VB code to handle it in a DTS package.
So, my question again is, how are others doing it? Any suggestions?

Thanks!
-Tom.

CREATE TABLE [dbo].[Station_Events_200401] (
[event_time] [datetime] NOT NULL ,
[another_column] [char] (8) NOT NULL )
GO

CREATE TABLE [dbo].[Station_Events_200402] (
[event_time] [datetime] NOT NULL ,
[another_column] [char] (8) NOT NULL )
GO

CREATE VIEW Station_Events
AS
SELECT event_time,
another_column
FROM Station_Events_200401
UNION ALL
SELECT event_time,
another_column
FROM Station_Events_200402
GO"Thomas R. Hummel" <tom_hummel@.hotmail.com> wrote in message
news:a2c0eeb8.0402060727.55312a15@.posting.google.c om...
> Hello,
> I have a large set of data that I have set up as a partitioned view.
> The view is partitioned by a datetime column and the individual tables
> each represent one month's worth of data. I need to keep at least two
> year's worth of data at all times, but after two years I can archive
> the data. A sample of the code used is below. It is simplified for
> space reasons.
> My question is, how do other people maintain the database in this type
> of scenario? I could create all of the tables necessary for the next
> year and then go through that at the end of each year (archive tables
> over two years, add new tables, and change the view), but I was also
> thinking that I might be able to write a stored procedure that runs
> once a month and does all three of those tasks automatically. It seems
> like a lot of dynamic SQL code though for something like that.
> Alternatively, I could write VB code to handle it in a DTS package.
> So, my question again is, how are others doing it? Any suggestions?
> Thanks!
> -Tom.
> CREATE TABLE [dbo].[Station_Events_200401] (
> [event_time] [datetime] NOT NULL ,
> [another_column] [char] (8) NOT NULL )
> GO
> CREATE TABLE [dbo].[Station_Events_200402] (
> [event_time] [datetime] NOT NULL ,
> [another_column] [char] (8) NOT NULL )
> GO
> CREATE VIEW Station_Events
> AS
> SELECT event_time,
> another_column
> FROM Station_Events_200401
> UNION ALL
> SELECT event_time,
> another_column
> FROM Station_Events_200402
> GO

I would use a DTS package - the first steps to create the objects
dynamically, then later steps to call stored procedures to actually move the
data. But that's more or less a personal preference, and you could do
everything in a stored procedure as well. Assuming there are no major
reasons to use one solution or another, it probably depends on what is most
transparent and easiest to maintain for you and your organization.

Simon|||Without dynamic SQL the view will be a problem, but I think you can easily
handle the archiving.

Within a stored procedure you can create a new copy of your table "template"
like this:

CREATE TABLE [dbo].[Station_Events_Template] ( [event_time] [datetime] NOT
NULL , [another_column] [char] (8) NOT NULL )

Then insert the rows into this table for the appropriate time period.

INSERT INTO Station_Events_Template
SELECT * FROM [your "active" table] WHERE event_time BETWEEN @.thisdate AND
@.thatdate

After the insert you can rename the template using the sp_rename procedure.
You will need a variable for the new table name first:
DECLARE @.NewName varchar(25)

SELECT @.NewName = 'Station_Events_' + CONVERT(char(6), @.MonthEndDate,
112) -- returns date in format "YYYYMM"

EXEC sp_rename 'Station_Events_Template', @.NewName

Hope this is helpful.

"Thomas R. Hummel" <tom_hummel@.hotmail.com> wrote in message
news:a2c0eeb8.0402060727.55312a15@.posting.google.c om...
> Hello,
> I have a large set of data that I have set up as a partitioned view.
> The view is partitioned by a datetime column and the individual tables
> each represent one month's worth of data. I need to keep at least two
> year's worth of data at all times, but after two years I can archive
> the data. A sample of the code used is below. It is simplified for
> space reasons.
> My question is, how do other people maintain the database in this type
> of scenario? I could create all of the tables necessary for the next
> year and then go through that at the end of each year (archive tables
> over two years, add new tables, and change the view), but I was also
> thinking that I might be able to write a stored procedure that runs
> once a month and does all three of those tasks automatically. It seems
> like a lot of dynamic SQL code though for something like that.
> Alternatively, I could write VB code to handle it in a DTS package.
> So, my question again is, how are others doing it? Any suggestions?
> Thanks!
> -Tom.
> CREATE TABLE [dbo].[Station_Events_200401] (
> [event_time] [datetime] NOT NULL ,
> [another_column] [char] (8) NOT NULL )
> GO
> CREATE TABLE [dbo].[Station_Events_200402] (
> [event_time] [datetime] NOT NULL ,
> [another_column] [char] (8) NOT NULL )
> GO
> CREATE VIEW Station_Events
> AS
> SELECT event_time,
> another_column
> FROM Station_Events_200401
> UNION ALL
> SELECT event_time,
> another_column
> FROM Station_Events_200402
> GO|||Thank you both for your suggestions! I always seem to overlook using
sp_rename as part of my bag of tricks. I'll give that some
consideration, but right now I'm leaning towards a DTS package using
VB to generate the SQL code.

Thanks,
-Tom.

Maintaining custom colors when exporting to Excel

I am using RGB HEX #s in my report for some colors. It shows up fine when rendering to a browser, however when exporting to Excel my custom colors don't come over. I've tried creating a report.xlt file with my custom colors in it, but when I export to Excel it uses the standard Excel palette instead of the custom palette in my report.xlt.

Any ideas on how to retain my custom colors when exporting to Excel?

I faced similar issue, it seems custom colors are not supported in excel.

maintaining atomicity

Hello Friends,
Iam new to this sql server arena. I have implemented a procedure which does a series of insert and update statements and all of this statements must be implemented all at once or none. But if I got error in some statements , the rest of the statements are been executed. Please suggest me a way or code snippet to achieve atomicity in a sqlserver procedure.

regards,
Ch.Praveen Kumar.Use transactions:

begin tran

insert ...

if @.@.error<>0 begin
rollback tran
RAISERROR ...
return
end

update ...

if @.@.error<>0 begin
rollback tran
RAISERROR ...
return
end

commit tran|||Thank You For Your Reply. I Will Implement It.|||Hi,
I got a small doubt here. Whenever an error encounters, how to display the sqlserver error message rather than displaying our custom message with raiseerror. Is there any way to get the error message from sql server if an error encounters such as foriegn key violation. (invalid references to pk in mastertable)|||The problem that I see is that errors come back to the client application in a "stream", much like TDS or STDOUT provide. Transact-SQL sees the most recent error, the client sees every error. Unfortunately, the formatted error message (including things like object names) is only passed to the client, I don't think that Transact-SQL can even access it (although ADO can).

-PatP

Maintaining a one-to-one match on related tables

I have a series of tables already containing data. The first table is a list
of employees. The next is a list of team names.
There is a Primary Key for each employee which relates to a foreign key in
the team names.
The employee table would have
pkid
FirstName
LastName
fkOfficeID
department
The team table would have only
fkEmployeeID
TeamName
Now, I know how to generate a SELECT set with a LEFT JOIN and a search for
NULL in the TeamName to generate a list of employees that do not have a team
name assigned to them, however. Not every employee would have a coresponding
record in the team table, only certain offices have teams.
What is the statement that would:
"INSERT and UPDATE a specific team name record in the team table for
each corresponding record in the employee table for any employee who's
"Office ID" is 5 department is Accounting and is missing a record in the
team table."
I could VB my way through this by
Creating the list of all offices that have teams
Creating the SELECT set mentioned above and whittling it down to
those employees in each office who are in Accounting
IF NOT EXISTS-ing my way through the team list and adding the
appopriate team record
But, is there an easier way? Is it a two step process, one to determine the
missing records in the one-to-one relationship where needed and then do the
team table update?
Julianand the vb programmers here wonder why vb makes me pull my hair out... ;)
insert into team (fkEmployeeID, TeamName)
select e.pkid, 'Office 5 Accounting Team 1'
from employee e
where fkofficeid=5
and department='Accounting'
and not exists (select * from team where fkemployeeid=e.pkid)
not sure what you need to update...
stjulian wrote:
> I have a series of tables already containing data. The first table is a li
st
> of employees. The next is a list of team names.
> There is a Primary Key for each employee which relates to a foreign key in
> the team names.
> The employee table would have
> pkid
> FirstName
> LastName
> fkOfficeID
> department
> The team table would have only
> fkEmployeeID
> TeamName
>
> Now, I know how to generate a SELECT set with a LEFT JOIN and a search for
> NULL in the TeamName to generate a list of employees that do not have a te
am
> name assigned to them, however. Not every employee would have a corespondi
ng
> record in the team table, only certain offices have teams.
> What is the statement that would:
> "INSERT and UPDATE a specific team name record in the team table for
> each corresponding record in the employee table for any employee who's
> "Office ID" is 5 department is Accounting and is missing a record in the
> team table."
> I could VB my way through this by
> Creating the list of all offices that have teams
> Creating the SELECT set mentioned above and whittling it down to
> those employees in each office who are in Accounting
> IF NOT EXISTS-ing my way through the team list and adding the
> appopriate team record
> But, is there an easier way? Is it a two step process, one to determine th
e
> missing records in the one-to-one relationship where needed and then do th
e
> team table update?
> Julian
>
>|||I think like a VB programmer, you know, linearly. What I love about you guys
in SQL is your way of looking at data in 3 dimensions. Folding it on itself
(using table aliases).
Should the line be "from employee AS e" ?
I should take a class on query design.
I'll give it a try tomorrow. Keep an eye on this thread.
Julian
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:eETCybEAGHA.1268@.TK2MSFTNGP11.phx.gbl...
> and the vb programmers here wonder why vb makes me pull my hair out... ;)
> insert into team (fkEmployeeID, TeamName)
> select e.pkid, 'Office 5 Accounting Team 1'
> from employee e
> where fkofficeid=5
> and department='Accounting'
> and not exists (select * from team where fkemployeeid=e.pkid)
>
> not sure what you need to update...
> stjulian wrote:|||Although the issues are 2D, thinking 3D is more impressive. :)
ML
http://milambda.blogspot.com/|||I prefer thinking of it as
VB = "For each x, do this"
SQL = "For all x's, do this"
the AS is optional in aliasing a table or column.
my personal preference is to include AS for column aliases and not for
table aliases.
stjulian wrote:
> I think like a VB programmer, you know, linearly. What I love about you gu
ys
> in SQL is your way of looking at data in 3 dimensions. Folding it on itsel
f
> (using table aliases).
> Should the line be "from employee AS e" ?
> I should take a class on query design.
> I'll give it a try tomorrow. Keep an eye on this thread.
> Julian
>
> "Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
> news:eETCybEAGHA.1268@.TK2MSFTNGP11.phx.gbl...
>

Maintaining a log for the users connected to sql server db.

Hi !

I need to maintain a record such as how many time any user (e.g, sa) connects to the sql server. Means whenever any person is connecting to the database through application or directly, then i need to know that through which sql user(e.g sa), any body connected.

Regards,
Shabber Abbas Rizvi.create server side trace file for security Audit events.For more information visit

http://search.atomz.com/search/?sp-q=trace&sp-a=sp10010351

Maintaining a Date/Time Last Updated Value

Using SQL Server 2000...
In support of a Web site's product catalogs; we will have at least two
tables [Products] and [ProductCatalogs].
The application needs to show the Date/Time any given product catalog was
last updated. For purposes of this "last updated date/time" value, we
consider the catalog as having been updated when [anything about the
catalog, itself] has been updated OR [any items presented in the catalog]
have been updated. This means one Date/Time value when either (1) the given
catalog's row in [ProductCatalogs] or (2) any associated rows in [Products]
have been updated. We do not need a complete update history - we only want
to know the last date/time an update happened to either the given catalog or
to any of the items presented in the catalog (and not any "per item" update
date/time).
One solution would be to have a column in [ProductCatalogs] of the DateTime
data type. The value in this column gets maintained by triggers on both
[Products] and [ProductCatalogs].
While that solution would work, I'd appreciate your perspective, opinions,
and alternatives - if any - before I go off and implement this.
Thanks!As far as I can see, you only need one datetime field on the table
ProductCatalogs. If you have only one way of updating your data (through
stored procedure) I would recommend implementing this logic in the stored
procedure. If not (or you think that someone could change data without using
sp) you would need to implement triggers.
MC
"Jeff" <Jeff@.NoSpam.com> wrote in message
news:u87GGjH9FHA.3020@.TK2MSFTNGP09.phx.gbl...
> Using SQL Server 2000...
> In support of a Web site's product catalogs; we will have at least two
> tables [Products] and [ProductCatalogs].
> The application needs to show the Date/Time any given product catalog was
> last updated. For purposes of this "last updated date/time" value, we
> consider the catalog as having been updated when [anything about the
> catalog, itself] has been updated OR [any items presented in the catalog]
> have been updated. This means one Date/Time value when either (1) the
> given catalog's row in [ProductCatalogs] or (2) any associated rows in
> [Products] have been updated. We do not need a complete update history -
> we only want to know the last date/time an update happened to either the
> given catalog or to any of the items presented in the catalog (and not any
> "per item" update date/time).
> One solution would be to have a column in [ProductCatalogs] of the
> DateTime data type. The value in this column gets maintained by triggers
> on both [Products] and [ProductCatalogs].
> While that solution would work, I'd appreciate your perspective, opinions,
> and alternatives - if any - before I go off and implement this.
> Thanks!
>