Need to collect serverinfo

Is there a way that I can run some batch file or query to go out to all SQL
Servers and collect some ServerInfo such as Windows Version/Service Pack,
SQL Version/Service Pack,CPU ( Number and Type) ,Server Model(HP,Dell),
Memory , Disk Space, NIC settingg(IP address)etc and stick it into a table
for reporting. The more info the better..
Hello,
The below extended proc will give info regarding OS, SQL Server, Memory and
CPU.
Master..xp_msver
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23cHiWIOQHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Is there a way that I can run some batch file or query to go out to all
> SQL Servers and collect some ServerInfo such as Windows Version/Service
> Pack, SQL Version/Service Pack,CPU ( Number and Type) ,Server
> Model(HP,Dell), Memory , Disk Space, NIC settingg(IP address)etc and stick
> it into a table for reporting. The more info the better..
>
sql

Need to collect serverinfo

Is there a way that I can run some batch file or query to go out to all SQL
Servers and collect some ServerInfo such as Windows Version/Service Pack,
SQL Version/Service Pack,CPU ( Number and Type) ,Server Model(HP,Dell),
Memory , Disk Space, NIC settingg(IP address)etc and stick it into a table
for reporting. The more info the better..Hello,
The below extended proc will give info regarding OS, SQL Server, Memory and
CPU.
Master..xp_msver
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23cHiWIOQHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Is there a way that I can run some batch file or query to go out to all
> SQL Servers and collect some ServerInfo such as Windows Version/Service
> Pack, SQL Version/Service Pack,CPU ( Number and Type) ,Server
> Model(HP,Dell), Memory , Disk Space, NIC settingg(IP address)etc and stick
> it into a table for reporting. The more info the better..
>

Need to collect serverinfo

Is there a way that I can run some batch file or query to go out to all SQL
Servers and collect some ServerInfo such as Windows Version/Service Pack,
SQL Version/Service Pack,CPU ( Number and Type) ,Server Model(HP,Dell),
Memory , Disk Space, NIC settingg(IP address)etc and stick it into a table
for reporting. The more info the better..Hello,
The below extended proc will give info regarding OS, SQL Server, Memory and
CPU.
Master..xp_msver
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23cHiWIOQHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Is there a way that I can run some batch file or query to go out to all
> SQL Servers and collect some ServerInfo such as Windows Version/Service
> Pack, SQL Version/Service Pack,CPU ( Number and Type) ,Server
> Model(HP,Dell), Memory , Disk Space, NIC settingg(IP address)etc and stick
> it into a table for reporting. The more info the better..
>

Need to boot connections to my database

Hi,
We have a batch file that runs every night that drops a database, and restores
it to "baseline" version. But sometimes this fails because users have gone
home and left SQL Suery Analyser connected to the database, which makes the
drop fail.
I need to somehow "kill" all connections to the database - is there a way
to do this in a batch file? I tried stopping and restarting the sqlserver
service on the PC, but that doesn't seem to do it.
Thanks!
Matt
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Then Restore
THEN back to MULTI_USER
HAve a look at ALTER DATABASE syntax in SQL Books On Line
HTH. Ryan
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.co m...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>
|||matt
ALTER DATABASE ... SET SINGLE_USER
go
ALTER DATABASE ... SET MULTI_USER
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.co m...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>
|||Thanks to both of you - that works perfectly
Matt.
[vbcol=seagreen]
> matt
> ALTER DATABASE ... SET SINGLE_USER
> go
> ALTER DATABASE ... SET MULTI_USER
> "matt roberts" <mattwoberts@.gmail.com> wrote in message
> news:6ae5d004cb228c82777fb4f8119@.news.microsoft.co m...

Need to boot connections to my database

Hi,
We have a batch file that runs every night that drops a database, and restor
es
it to "baseline" version. But sometimes this fails because users have gone
home and left SQL Suery Analyser connected to the database, which makes the
drop fail.
I need to somehow "kill" all connections to the database - is there a way
to do this in a batch file? I tried stopping and restarting the sqlserver
service on the PC, but that doesn't seem to do it.
Thanks!
MattALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Then Restore
THEN back to MULTI_USER
HAve a look at ALTER DATABASE syntax in SQL Books On Line
HTH. Ryan
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>|||matt
ALTER DATABASE ... SET SINGLE_USER
go
ALTER DATABASE ... SET MULTI_USER
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>|||Thanks to both of you - that works perfectly
Matt.
[vbcol=seagreen]
> matt
> ALTER DATABASE ... SET SINGLE_USER
> go
> ALTER DATABASE ... SET MULTI_USER
> "matt roberts" <mattwoberts@.gmail.com> wrote in message
> news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
>

Need to boot connections to my database

Hi,
We have a batch file that runs every night that drops a database, and restores
it to "baseline" version. But sometimes this fails because users have gone
home and left SQL Suery Analyser connected to the database, which makes the
drop fail.
I need to somehow "kill" all connections to the database - is there a way
to do this in a batch file? I tried stopping and restarting the sqlserver
service on the PC, but that doesn't seem to do it.
Thanks!
MattALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Then Restore
THEN back to MULTI_USER
HAve a look at ALTER DATABASE syntax in SQL Books On Line
--
HTH. Ryan
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>|||matt
ALTER DATABASE ... SET SINGLE_USER
go
ALTER DATABASE ... SET MULTI_USER
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>

Need Steps to upgrade SQL Yukon 9.00.852 (Intel X86) to 9.00.1399.06

Hi SQL DBA,s

What are the step to upgrade ?

Microsoft SQL Server Yukon - 9.00.852 (Intel X86) version To
Sql 2005 Standard {9.00.1399.06}.

When I tries to backup and restore from this 9.00.852 version to Sql 2005 Standard {9.00.1399.06}.
It is giving me an error that this version is not compatible.

What we need to do know ?

Also let me know where I can download SQL Server Yukon - 9.00.852 (Intel X86) version so I can do the test on test box.

Thanks In advance.

You can try exporting and importing the data to a .sql file.sql

Need infos regarding install of SQL Express 2005 SP1

Hi,

A little technical question. If I want to install SQL Express 2005 SP1, do I have to uninstall current version first?

Thanks in advance,

Stphane

No, you apply a service pack to an existing installation.|||

Hi again,

Thanks for the info. Maybe a last question. Considering the fact that a SP1 has also been issued for Visual Studio 2005, must I first install the SP1 for VS 2005 before installing SP1 for SQL Express 2005 or it doesn't matter?

Thanks for your help,

Stphane

|||

It doesn't matter.

Mike

|||

SQL Server and Visual Studio are separate products. You do not have to apply SP1 to them in any particular order, nor do you have to apply it to both (though it is a good idea.)

Please indicate if your question has been answered properly. Thanks,

|||

Hi again,

I just finished install VS 2005 Sp1. Quite long, especially the section where it was evaluating the pre-requisites, but everything completed without error. It detected the appropriate version of VS2005 I was using. Now I'm ready to apply SP1 to SQL Server Express 2005. Before going on, I really need to clarify those things.

Considering the fact that the version of SQL Server Express 2005 actually present on my system is the one I originally installed from VS2005 Standard disks, what is the appropriate install sequence I should take?:

1- If I want to install "Microsoft SQL Server 2005 Express Edition with Advanced Services", do I have to uninstall the SQL Server Express 2005 version actually installed on my machine first or do I simply install the new one over it?

ref: http://www.microsoft.com/downloads/details.aspx?familyid=4C6BA9FD-319A-4887-BC75-3B02B5E48A40&displaylang=en

2- I presume that I can then install "Microsoft SQL Server 2005 Express Edition Toolkit" without problem. Anything I should take care of before install?

ref: http://www.microsoft.com/downloads/details.aspx?FamilyID=3c856b93-369f-4c6f-9357-c35384179543&DisplayLang=en

3- Finally, I presume that I can apply SP1 "Microsoft SQL Server 2005 Express Edition (SP1)" without any problem. Is the following link the appropriate version to apply or should I apply another one?

ref: http://www.microsoft.com/downloads/details.aspx?familyid=11350B1F-8F44-4DB6-B542-4A4B869C2FF1&displaylang=en

Thanks for your help,

Stphane

|||

Stphane,

Your sequence of steps should be work ok.

|||

Hi Arnie,

Just to be sure. Must I uninstall current version of SQL Express before going on with others listed in previous message?

Thanks for your help, really appreciated!

Stphane

|||Unless you are using a beta version, you do not need to unInstall before applying the service pack.|||

Hi again,

For applying SP1, I was aware that there was no need for uninstalling as I already applied SP1 over Visual Studio 2005. In fact, my question was more about the installation of "Microsoft SQL Server 2005 Express Edition with Advanced Services" over actual SQL Express installed on my machine. Can I install it over actual version or must I first uninstall current SQL Express version installed?

Thanks again,

Stphane

|||

The 'Advanced Services" edition is the same version of SQL 2005 Express, and it includes additional functionality, including Management Studio Express.

Again, you do NOT have to uninstall before adding a service pack.

(Now is that complete enough for you to indicate you have been responded and answered?

|||

Hi again,

This last question was not about SP1. Actually the SQL Server 2005 Express version I have is the one installed directly from Visual Studio 2005 Standard Edition disks. I would like before applying SP1 to install instead version/package "Microsoft SQL Server 2005 Express Edition with Advanced Services". During your last posts, you seemed to talk only about SP1. Again, "Microsoft SQL Server 2005 Express Edition with Advanced Services" is NOT installed but I would like to install it before applying SP1.

That is why I asked again, and still ask, in order to install "Microsoft SQL Server 2005 Express Edition with Advanced Services" over current version I have of SQL Express, do I have to uninstall SQL Express version currently installed first?

After this new install will be done, THEN I know I will be able to install SP1 without touching anything, the same way I already did so far for VS 2005.

Sorry for misunderstanding, but I want to be sure before screwing up anything.

Stphane

|||

And as I have replied many, many times: NO, you do not have to uninstall your current version of SQL 2005 Express. When you start up the "Advanced Services" edition, you will be asked if you want to install a new instance, or just install the new advanced services and Management Studio Express.

Please, give it a rest. Go make your installation. Go with Peace and Florish.

|||

Hi,

Really sorry to bug you but I never went through this install so far. Having known from start that install process would have asked me this question would have made things clear in my mind. Now, with that last detail, YES, we can close the subject.

Thanks for your patience,

Stphane

|||Cool, you gave yourself credit for answering your own question!

NEED HELP:cannot run sql server 7.0 on windows 98

Hello !
I've installed SQL Server 7 Desktop on my computer (Windows 98).
(French version)
It has been complicated and long to get the SQL Server Services list
installed with SQL Server Services Manager. But I finally got it !!
I thought that whis this SQL Server was full ready to start.
But now, when I try to start SQL Server, it tries to start, but stop
immediatly.
When I try to open a SQL Server group with Enterprise Manager, it
returns me an error "ConnectionOpen (RPCopen...)". If SQL Server was
ready to start, it would open automatically.
So that's obvious SQL Server is not correctly installed...
I installed SQL Server 7.0 once two years ago and I did it
successfully whith a same configuration (Desktop 7.0 with Windows 98).
But I remember it had been difficult. And I didn't save anything about
what I did.
What do I have to do to complete SQL Server installation '?
Thanks for your help !So you start the SQL Server service and it stops again immediately? Then
check the SQL Server errorlog file and eventlog for error messages.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"bilou" <loghara@.yahoo.fr> wrote in message
news:9aac58ce.0311130811.33e86ea3@.posting.google.com...
> Hello !
> I've installed SQL Server 7 Desktop on my computer (Windows 98).
> (French version)
> It has been complicated and long to get the SQL Server Services list
> installed with SQL Server Services Manager. But I finally got it !!
> I thought that whis this SQL Server was full ready to start.
> But now, when I try to start SQL Server, it tries to start, but stop
> immediatly.
> When I try to open a SQL Server group with Enterprise Manager, it
> returns me an error "ConnectionOpen (RPCopen...)". If SQL Server was
> ready to start, it would open automatically.
> So that's obvious SQL Server is not correctly installed...
> I installed SQL Server 7.0 once two years ago and I did it
> successfully whith a same configuration (Desktop 7.0 with Windows 98).
> But I remember it had been difficult. And I didn't save anything about
> what I did.
> What do I have to do to complete SQL Server installation '?
> Thanks for your help !

Need help writing a trigger

Please find the necessary SQL scripts to generate a small version of my database and some data at the bottom of this post.

Here's a short description of what the database is all about: It's a project tracking and management system. Contracts go into the tblDeals table. Because each project may be different in nature, project phases are defined in tblPhaseType and tblPhase tables. The table used to keep track of what's going on is the tblProduction table.

Here's what I need to do. When a project is completed -- meaning it has gone through all the phases that it needs to go through -- I want a trigger to fire up and change the contract status in the tblDeals table to "Completed" whose value is 1. When a new contract gets entered into the table, the Contract Status is set to 5 by default which means "In Progress" -- as defined in tblContractStatus. The tricky part is that because, each project is different and has different number of phases, the trigger has to make sure that all the phases have been submitted into the tblProduction table for that particular deal.

I'd really appreciate some help here. Thanks in advance for all your help.

------------
Here's the script
------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblCompany]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblCompany
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblContractStatus]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblContractStatus
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblPhaseType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblPhaseType
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblPhase_tblPhaseType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblPhase] DROP CONSTRAINT FK_tblPhase_tblPhaseType
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblProduction_tblDeals]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblDeals
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblProduction_tblPhase]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblPhase
GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblProduction]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblProduction]
GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDeals]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDeals]
GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPhase]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPhase]
GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCompany]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCompany]
GO
/****** Object: Table [dbo].[tblContractStatus] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblContractStatus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblContractStatus]
GO
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPhaseType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPhaseType]
GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/20/2003 11:30:50 AM ******/
CREATE TABLE [dbo].[tblCompany] (
 [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
 [CompanyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblContractStatus] Script Date: 11/20/2003 11:30:50 AM ******/
CREATE TABLE [dbo].[tblContractStatus] (
 [StatusID] [tinyint] IDENTITY (1, 1) NOT NULL ,
 [Status] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/20/2003 11:30:51 AM ******/
CREATE TABLE [dbo].[tblPhaseType] (
 [PhaseTypeID] [tinyint] IDENTITY (1, 1) NOT NULL ,
 [Desription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/20/2003 11:30:51 AM ******/
CREATE TABLE [dbo].[tblDeals] (
 [DealID] [int] IDENTITY (1, 1) NOT NULL ,
 [CompanyID] [int] NOT NULL ,
 [DealDate] [smalldatetime] NOT NULL ,
 [PhaseTypeID] [tinyint] NOT NULL ,
 [CashAmount] [smallmoney] NOT NULL ,
 [StatusID] [tinyint] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/20/2003 11:30:52 AM ******/
CREATE TABLE [dbo].[tblPhase] (
 [PhaseID] [tinyint] IDENTITY (1, 1) NOT NULL ,
 [PhaseTypeID] [tinyint] NOT NULL ,
 [PhaseDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [PhasePercentage] [float] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/20/2003 11:30:52 AM ******/
CREATE TABLE [dbo].[tblProduction] (
 [TransactionID] [int] IDENTITY (1, 1) NOT NULL ,
 [DealID] [int] NOT NULL ,
 [PhaseID] [tinyint] NOT NULL ,
 [TransactionTimeStamp] [smalldatetime] NOT NULL ,
 [Comments] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCompany] WITH NOCHECK ADD
 CONSTRAINT [PK_tblCompany] PRIMARY KEY CLUSTERED
 (
 [CompanyID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblContractStatus] WITH NOCHECK ADD
 CONSTRAINT [PK_tblContractStatus] PRIMARY KEY CLUSTERED
 (
 [StatusID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPhaseType] WITH NOCHECK ADD
 CONSTRAINT [PK_tblPhaseType] PRIMARY KEY CLUSTERED
 (
 [PhaseTypeID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDeals] WITH NOCHECK ADD
 CONSTRAINT [PK_tblDeals] PRIMARY KEY CLUSTERED
 (
 [DealID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPhase] WITH NOCHECK ADD
 CONSTRAINT [PK_tblPhase] PRIMARY KEY CLUSTERED
 (
 [PhaseID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblProduction] WITH NOCHECK ADD
 CONSTRAINT [PK_tblProduction] PRIMARY KEY CLUSTERED
 (
 [TransactionID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDeals] ADD
 CONSTRAINT [DF_tblDeals_StatusID] DEFAULT (5) FOR [StatusID]
GO
ALTER TABLE [dbo].[tblProduction] ADD
 CONSTRAINT [DF_tblProduction_TransactionTimeStamp] DEFAULT (getdate()) FOR [TransactionTimeStamp]
GO
ALTER TABLE [dbo].[tblDeals] ADD
 CONSTRAINT [FK_tblDeals_tblCompany] FOREIGN KEY
 (
 [CompanyID]
 ) REFERENCES [dbo].[tblCompany] (
 [CompanyID]
 ),
 CONSTRAINT [FK_tblDeals_tblContractStatus] FOREIGN KEY
 (
 [StatusID]
 ) REFERENCES [dbo].[tblContractStatus] (
 [StatusID]
 ),
 CONSTRAINT [FK_tblDeals_tblPhaseType] FOREIGN KEY
 (
 [PhaseTypeID]
 ) REFERENCES [dbo].[tblPhaseType] (
 [PhaseTypeID]
 )
GO
ALTER TABLE [dbo].[tblPhase] ADD
 CONSTRAINT [FK_tblPhase_tblPhaseType] FOREIGN KEY
 (
 [PhaseTypeID]
 ) REFERENCES [dbo].[tblPhaseType] (
 [PhaseTypeID]
 )
GO
ALTER TABLE [dbo].[tblProduction] ADD
 CONSTRAINT [FK_tblProduction_tblDeals] FOREIGN KEY
 (
 [DealID]
 ) REFERENCES [dbo].[tblDeals] (
 [DealID]
 ),
 CONSTRAINT [FK_tblProduction_tblPhase] FOREIGN KEY
 (
 [PhaseID]
 ) REFERENCES [dbo].[tblPhase] (
 [PhaseID]
 )
GO
exec sp_addextendedproperty N'MS_Description', N'Identifier', N'user', N'dbo', N'table', N'tblContractStatus', N'column', N'StatusID'
GO
exec sp_addextendedproperty N'MS_Description', N'Description', N'user', N'dbo', N'table', N'tblContractStatus', N'column', N'Status'
GO
exec sp_addextendedproperty N'MS_Description', N'Determines the type of phase structure this deal will go through', N'user', N'dbo', N'table', N'tblDeals', N'column', N'PhaseTypeID'
GO
exec sp_addextendedproperty N'MS_Description', N'Identifies the current status of deal', N'user', N'dbo', N'table', N'tblDeals', N'column', N'StatusID'
GO
exec sp_addextendedproperty N'MS_Description', N'Determines the percentage value of the phase', N'user', N'dbo', N'table', N'tblPhase', N'column', N'PhasePercentage'
GO
exec sp_addextendedproperty N'MS_Description', null, N'user', N'dbo', N'table', N'tblProduction', N'column', N'TransactionTimeStamp'
GO

------------
And here's some data
------------

INSERT INTO [tblPhaseType] ([Desription])VALUES('TV Commercial - 4 Phases')
INSERT INTO [tblPhaseType] ([Desription])VALUES('Full Campaign - 6 Phases')
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Customer Info',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Write script',2.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Shoot',3.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Edit commercial',2.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Customer info',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Write script',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Design print ad',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Shoot',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Edit',2.000000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Publish',2.000000000000000e-001)
INSERT INTO [tblContractStatus] ([Status])VALUES('Completed')
INSERT INTO [tblContractStatus] ([Status])VALUES('Hold')
INSERT INTO [tblContractStatus] ([Status])VALUES('Collections')
INSERT INTO [tblContractStatus] ([Status])VALUES('Legal')
INSERT INTO [tblContractStatus] ([Status])VALUES('In Progress')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Johnny''s Remodeling')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Perfect Cut Lawncare')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Useless Ideas Unlimited')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Try-It-Again, Inc.')
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(1,'Aug 5 2003 12:00:00:000AM',1,120.0000,5)
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(2,'Sep 9 2003 12:00:00:000AM',2,150.0000,5)
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(3,'Sep 10 2003 12:00:00:000AM',2,130.0000,5)
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(4,'Nov 20 2003 12:00:00:000AM',1,190.0000,5)
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,1,'Nov 10 2003 10:23:00:000AM','Received company logo')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,2,'Nov 10 2003 10:23:00:000AM','Finished writing script')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,5,'Nov 10 2003 10:23:00:000AM','Just received company info')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,7,'Nov 10 2003 10:24:00:000AM','Finished designing ad copy')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,3,'Nov 20 2003 11:29:00:000AM','Did more work')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,4,'Nov 20 2003 11:29:00:000AM','Finally finished the job')

OK,
The trigger should be placed in the table TransactionTimeStamp.
The Trigger Fires on Insert

The SQL should look Like This

Declare
 @.phasesDone int,
 @.phasesNeeded int,
 @.dealid int,
 @.phaseid int,
 @.phasetypeid int
Select @.dealid = dealid, @.phaseid = phaseid from inserted
Select @.phasesdone = count(*) from tblProduction where dealid = @.dealid and phaseid = @.phaseid
Select @.phasesneeded = count(*) from tblphase INNER JOIN tbldeals on tblphase.phasetypeid = tbldeals.phasetypeid where tbldeals.id = @.dealid
If @.phasesdone = @.phasesneeded
begin
 update tbldeals set statusid = 1 where id = @.dealid
end

that shoul work i'm quite sure.
hope this helps you.|||Hi Misiu,

Thanks for the help. I'm getting an error. I think it has somehting to do with getting the PhaseID and the DealID from the "inserted". For some reason I've never been able to get this to work for me. Is there anything I need to do i.e. activate, some kind of setting or something -- so that I can get data from the inserted?|||What error do you get?
As I know you don't have to activate any setting.