Solved

SQL Server Takes too long to execute a script to create DB and Stored Procedures

Posted on 2004-09-13
10
278 Views
Last Modified: 2008-03-03
Hi Experts !

I Was Instaling an ASP.NET Application wich came with the scripts to set up the SQL Server Backend.
The Script called TimeTracker_CreateDB.sql is a simple script to create the DataBase, create Tables, create stored procedures , constraints and that's about it.
This query took more than 1 hour to run on the server. To check if the problem was on the server i tried it also on MSDE (local server) and results were as bad.

What could be the cause for this huge amount of time for a simple script ?

Thanks in advance !
0
Comment
Question by:ExpertAudit
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 26

Assisted Solution

by:Hilaire
Hilaire earned 100 total points
Comment Utility
It would help if you could post part of the script or give us more details.
You could obfuscate table/field names with a search/replace if it's copyrighted stuff.

Another option would be to open the SQL script in Query Analyser to narrow the scope and find out which statement(s) takes so long.

Just execute the script in smaller parts. Select / highlight each chunk in QA and press F5 to execute only the selected part. Do it until you find the statement that takes ages to run.

Maybe the initialisation script also contains some data : reference tables might be populated on a row-by-row mode using insert statements.
Maybe you could speed-up things by activating constraints and building indexes AFTER the tables are populated, if it's no the case in your current script.

Cheers

Hilaire

0
 
LVL 2

Assisted Solution

by:Kolya_Tchernitsky
Kolya_Tchernitsky earned 50 total points
Comment Utility
Try to Run SQL Profiler when running the script. Add all relevant events, do not forget to monitor the data and log file growths.
0
 
LVL 10

Assisted Solution

by:Jay Toops
Jay Toops earned 100 total points
Comment Utility
reboot the database into single user mode and run it, you might be getting a locking problem
with other processes.

Jay
0
 
LVL 1

Author Comment

by:ExpertAudit
Comment Utility
Thanks Hilaire for your fast help !
Your Post was very helpfull for future problems of the kind !
But i found the answear just a moment ago !  

The problem was not with Sql Server it was in my client machine (WinXP) (that runs Enterprise edition).
I did a remote loggin to the server (Windows 2003) (copied the sql scripts to a file share on the server) and runned the scripts locally with the query analyser wich was installed on the server and it ran super fast.

But the strange thing is that SQL Server Client Tools like Enterprise Edition and Query analyser should pass the whole script to the server at once and not like it was happening isnt' this true ? What could be the cause of this ?

Cheers,

Emanuel

0
 
LVL 10

Assisted Solution

by:Jay Toops
Jay Toops earned 100 total points
Comment Utility
This should only be a problem if
a) you have somthing like TRACING on.
b) you have a very slow network connection
c) the script is loading a LOT of data

otherwise is should be just as fast as "being there"
fyi: how large in terms of bytes is the script that creates the db?

Jay
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:ExpertAudit
Comment Utility
This place rules !
This is such a wonderfull comunity !

In these cases who should we give the points to ?
Your oppinions were all very valluable for debugging future problems like this one ...

Thanx!
0
 
LVL 1

Author Comment

by:ExpertAudit
Comment Utility
The Script is 42 Kb.
I Don't think the problem was with the network connection since i tried running this scrip in my deskop with MSDE.
About TRACING where can i check this ?

I'll post the scrip maybe the problem coud be in the type of scrip conflicting with something else :

Thanx !
____________________________
The script is this :
___________________________

USE master

CREATE DATABASE [TimeTracker]  
GO

exec sp_dboption N'TimeTracker', N'autoclose', N'false'
GO

exec sp_dboption N'TimeTracker', N'bulkcopy', N'false'
GO

exec sp_dboption N'TimeTracker', N'trunc. log', N'false'
GO

exec sp_dboption N'TimeTracker', N'torn page detection', N'false'
GO

exec sp_dboption N'TimeTracker', N'read only', N'false'
GO

exec sp_dboption N'TimeTracker', N'dbo use', N'false'
GO

exec sp_dboption N'TimeTracker', N'single', N'false'
GO

exec sp_dboption N'TimeTracker', N'autoshrink', N'false'
GO

exec sp_dboption N'TimeTracker', N'ANSI null default', N'false'
GO

exec sp_dboption N'TimeTracker', N'recursive triggers', N'false'
GO

exec sp_dboption N'TimeTracker', N'ANSI nulls', N'false'
GO

exec sp_dboption N'TimeTracker', N'concat null yields null', N'false'
GO

exec sp_dboption N'TimeTracker', N'cursor close on commit', N'false'
GO

exec sp_dboption N'TimeTracker', N'default to local cursor', N'false'
GO

exec sp_dboption N'TimeTracker', N'quoted identifier', N'false'
GO

exec sp_dboption N'TimeTracker', N'ANSI warnings', N'false'
GO

exec sp_dboption N'TimeTracker', N'auto create statistics', N'true'
GO

exec sp_dboption N'TimeTracker', N'auto update statistics', N'true'
GO

use [TimeTracker]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_EntryLog_Categories]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_EntryLog] DROP CONSTRAINT FK_EntryLog_Categories
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_EntryLog_ProjectMembers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_EntryLog] DROP CONSTRAINT FK_EntryLog_ProjectMembers
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Categories_Projects]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_Categories] DROP CONSTRAINT FK_Categories_Projects
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Roles_Projects]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_ProjectMembers] DROP CONSTRAINT FK_Roles_Projects
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Users_Roles]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_Users] DROP CONSTRAINT FK_Users_Roles
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_WorksOn_Users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_ProjectMembers] DROP CONSTRAINT FK_WorksOn_Users
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Projects_Users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_Projects] DROP CONSTRAINT FK_Projects_Users
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_AddProject]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddProjectMember]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_AddProjectMember]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddTimeEntry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_AddTimeEntry]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_AddUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_DeleteProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_DeleteProject]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_DeleteTimeEntry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_DeleteTimeEntry]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_DeleteUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_DeleteUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetManagerProjectCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_GetManagerProjectCount]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_GetProject]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetTimeEntry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_GetTimeEntry]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetUserByUserName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_GetUserByUserName]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetUserDisplayName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_GetUserDisplayName]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListAllProjects]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListAllProjects]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListAllRoles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListAllRoles]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListCategories]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListCategories]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListCategoriesByProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListCategoriesByProject]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListManagers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListManagers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListMembers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListMembers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListProjects]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListProjects]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListProjectsByIDs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListProjectsByIDs]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListProjectsWithMembership]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListProjectsWithMembership]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListResourceByIDs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListResourceByIDs]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListTimeEntries]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListTimeEntries]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListTimeEntriesByCategory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListTimeEntriesByCategory]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListTimeEntriesByUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListTimeEntriesByUsers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListUserTimeSummary]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListUserTimeSummary]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListUsers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_UpdateProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_UpdateProject]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_UpdateTimeEntry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_UpdateTimeEntry]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_UpdateUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_UpdateUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_UserLogin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_UserLogin]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TT_Categories]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_EntryLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TT_EntryLog]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ProjectMembers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TT_ProjectMembers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Projects]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TT_Projects]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Roles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TT_Roles]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TT_Users]
GO

CREATE TABLE [dbo].[TT_Categories] (
      [CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
      [ProjectID] [int] NOT NULL ,
      [Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Abbreviation] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [EstDuration] [decimal](10, 2) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TT_EntryLog] (
      [EntryLogID] [int] IDENTITY (1, 1) NOT NULL ,
      [Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Duration] [decimal](10, 2) NOT NULL ,
      [EntryDate] [smalldatetime] NOT NULL ,
      [ProjectID] [int] NOT NULL ,
      [UserID] [int] NOT NULL ,
      [CategoryID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TT_ProjectMembers] (
      [ProjectID] [int] NOT NULL ,
      [UserID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TT_Projects] (
      [ProjectID] [int] IDENTITY (1, 1) NOT NULL ,
      [Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Description] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ManagerUserID] [int] NULL ,
      [EstCompletionDate] [datetime] NULL ,
      [EstDuration] [decimal](10, 2) NULL ,
      [CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TT_Roles] (
      [RoleID] [int] NOT NULL ,
      [Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TT_Users] (
      [DisplayName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [UserID] [int] IDENTITY (1, 1) NOT NULL ,
      [UserName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [RoleID] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TT_Categories] WITH NOCHECK ADD
      CONSTRAINT [PK_Categories] PRIMARY KEY  CLUSTERED
      (
            [CategoryID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[TT_ProjectMembers] WITH NOCHECK ADD
      CONSTRAINT [PK_ProjectMembers] PRIMARY KEY  CLUSTERED
      (
            [ProjectID],
            [UserID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[TT_Roles] WITH NOCHECK ADD
      CONSTRAINT [PK_Roles] PRIMARY KEY  CLUSTERED
      (
            [RoleID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[TT_EntryLog] ADD
      CONSTRAINT [PK_Tasks] PRIMARY KEY  NONCLUSTERED
      (
            [EntryLogID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[TT_ProjectMembers] ADD
      CONSTRAINT [IX_Roles] UNIQUE  NONCLUSTERED
      (
            [ProjectID],
            [UserID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[TT_Projects] ADD
      CONSTRAINT [DF_Projects_CreationDate] DEFAULT (getdate()) FOR [CreationDate],
      CONSTRAINT [PK_Projects] PRIMARY KEY  NONCLUSTERED
      (
            [ProjectID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[TT_Users] ADD
      CONSTRAINT [PK_Users] PRIMARY KEY  NONCLUSTERED
      (
            [UserID]
      )  ON [PRIMARY] ,
      CONSTRAINT [IX_Users] UNIQUE  NONCLUSTERED
      (
            [UserName]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[TT_Categories] ADD
      CONSTRAINT [FK_Categories_Projects] FOREIGN KEY
      (
            [ProjectID]
      ) REFERENCES [dbo].[TT_Projects] (
            [ProjectID]
      ) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[TT_EntryLog] ADD
      CONSTRAINT [FK_EntryLog_Categories] FOREIGN KEY
      (
            [CategoryID]
      ) REFERENCES [dbo].[TT_Categories] (
            [CategoryID]
      ) ON DELETE CASCADE ,
      CONSTRAINT [FK_EntryLog_ProjectMembers] FOREIGN KEY
      (
            [ProjectID],
            [UserID]
      ) REFERENCES [dbo].[TT_ProjectMembers] (
            [ProjectID],
            [UserID]
      )
GO

ALTER TABLE [dbo].[TT_ProjectMembers] ADD
      CONSTRAINT [FK_Roles_Projects] FOREIGN KEY
      (
            [ProjectID]
      ) REFERENCES [dbo].[TT_Projects] (
            [ProjectID]
      ) ON DELETE CASCADE ,
      CONSTRAINT [FK_WorksOn_Users] FOREIGN KEY
      (
            [UserID]
      ) REFERENCES [dbo].[TT_Users] (
            [UserID]
      )
GO

ALTER TABLE [dbo].[TT_Projects] ADD
      CONSTRAINT [FK_Projects_Users] FOREIGN KEY
      (
            [ManagerUserID]
      ) REFERENCES [dbo].[TT_Users] (
            [UserID]
      )
GO

ALTER TABLE [dbo].[TT_Users] ADD
      CONSTRAINT [FK_Users_Roles] FOREIGN KEY
      (
            [RoleID]
      ) REFERENCES [dbo].[TT_Roles] (
            [RoleID]
      )
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE         PROCEDURE TT_AddProject
(
      @Name nvarchar(50),
      @Description nvarchar(1024),
      @ManagerUserID int,
      @EstCompletionDate datetime,
      @EstDuration int,
      @Members nvarchar(2000),
      @Categories nvarchar(4000)
)
AS

      DECLARE @Error int
      DECLARE @ProjectID int
      DECLARE @TempString varchar(4000)
      DECLARE @Temp nvarchar(4000)
      DECLARE @Count int
      DECLARE @TempTable TABLE(UserID int PRIMARY KEY)
      DECLARE @InnerTemp nvarchar(50)
      DECLARE @CatName varchar(20)
      DECLARE @Abbrev varchar(5)
      DECLARE @Duration decimal(10,2)
      DECLARE @InnerCount int

      BEGIN TRANSACTION

      INSERT INTO TT_Projects
      (
            [Name],
            [Description],
            ManagerUserID,
            EstCompletionDate,
            EstDuration,
            CreationDate
      )
      VALUES
      (  
            @Name,
            @Description,
            @ManagerUserID,
            @EstCompletionDate,
            @EstDuration,
            getdate()
      )

      SET @Error = @@ERROR
      IF @Error != 0 GOTO ERROR_HANDLER

      SET @ProjectID = @@Identity  

      SET @TempString = @Members

      SET @Count = CHARINDEX(',', @TempString)

      WHILE @Count > 0
      BEGIN
            SET @Temp = SUBSTRING(@TempString, 1, (@Count - 1))
            INSERT INTO @TempTable VALUES(CAST(@Temp AS int))
            SET @TempString = SUBSTRING(@TempString, (@Count + 1), (LEN(@TempString) - @Count))
            SET @Count = CHARINDEX(',', @TempString)
      END

      INSERT INTO @TempTable VALUES(CAST(@TempString AS int))

      INSERT INTO TT_ProjectMembers  
            SELECT @ProjectID, UserID FROM @TempTable

      SET @Error = @@ERROR
      IF @Error != 0 GOTO ERROR_HANDLER

      SET @TempString = @Categories
      
      SET @Count = CHARINDEX(';', @TempString)
      WHILE @Count > 0
      BEGIN
            SET @Temp = SUBSTRING(@TempString, 1, (@Count - 1))

            SET @InnerCount = CHARINDEX(',', @Temp)

            SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
            SET @CatName = @InnerTemp

            SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
            SET @InnerCount = CHARINDEX(',', @Temp)

            SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
            SET @Abbrev = @InnerTemp
                  
            SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
            SET @InnerCount = CHARINDEX(',', @Temp)

             SET @Duration =  CAST(@Temp AS int)

            INSERT INTO TT_Categories
            (
                  ProjectID,
                  [Name],
                  Abbreviation,
                  EstDuration
            )
            VALUES
            (  
                  @ProjectID,
                  @CatName,
                  @Abbrev,
                  @Duration
            )

            SET @Error = @@ERROR
            IF @Error != 0 GOTO ERROR_HANDLER
      
            SET @TempString = SUBSTRING(@TempString, (@Count + 1), (LEN(@TempString) - @Count))
            SET @Count = CHARINDEX(';', @TempString)

      END

      set @Temp =  @TempString
      SET @InnerCount = CHARINDEX(',', @Temp)

      SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
      SET @CatName = @InnerTemp

      SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
      SET @InnerCount = CHARINDEX(',', @Temp)

      SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
      SET @Abbrev = @InnerTemp

      SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
      SET @InnerCount = CHARINDEX(',', @Temp)

      SET @Duration =  CAST(@Temp AS int)

      INSERT INTO TT_Categories
      (
            ProjectID,
            [Name],
            Abbreviation,
            EstDuration
      )
      VALUES
      (  
            @ProjectID,
            @CatName,
            @Abbrev,
            @Duration
      )

      SET @Error = @@ERROR
      IF @Error != 0 GOTO ERROR_HANDLER

      COMMIT TRANSACTION

      SELECT @ProjectID AS ProjectID  

ERROR_HANDLER:
      IF @@TRANCOUNT != 0 ROLLBACK TRANSACTION
      RETURN @Error
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO








CREATE  PROCEDURE TT_AddProjectMember
(
      @ProjectID int,
      @UserID int
)
AS

INSERT INTO TT_ProjectMembers
(
      ProjectID,
      UserID
)
VALUES
(
      @ProjectID,
      @UserID
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO








CREATE   PROCEDURE TT_AddTimeEntry
(
      @UserID int,
      @ProjectID int,
      @CategoryID int,
      @EntryDate datetime,
      @Description nvarchar(255),
      @Duration decimal(10,2)
)
AS

INSERT INTO TT_EntryLog
(
      Description,
      Duration,
      EntryDate,
      ProjectID,
      UserID,      
      CategoryID
)
VALUES
(  
      
      @Description,
      @Duration,
      @EntryDate,
      @ProjectID,
      @UserID,
      @CategoryID      
)

SELECT
    @@Identity AS EntryLogID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO










CREATE     PROCEDURE TT_AddUser
(
      @UserName nvarchar(50),
      @Password nvarchar(50),
      @DisplayName nvarchar(50),
--      @LastName nvarchar(50),
--      @Email nvarchar(50),
--      @Telephone nvarchar(50),
      @RoleID int
)
AS

--Verify that the UserName is not being added to a UserName that already exists
IF Not Exists (SELECT UserName FROM TT_Users WHERE UserName=@UserName)
  BEGIN

      INSERT INTO TT_Users
      (
            UserName,
             Password,
             DisplayName,
--             LastName,
--             Email,
--             Telephone,
            RoleID
      )
      VALUES
      (  
            @UserName,
             @Password,
             @DisplayName,
--             @LastName,
--             @Email,
--             @Telephone,
            @RoleID
      )
      
      SELECT
          @@Identity AS UserID    
  END
ELSE
      SELECT -2 AS UserID

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO





CREATE PROCEDURE TT_DeleteProject
(
      @ProjectID int
)
AS

DELETE FROM
      TT_Projects

WHERE
      ProjectID = @ProjectID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO






CREATE PROCEDURE TT_DeleteTimeEntry
(
      @EntryLogID int
)
AS

DELETE FROM
      TT_EntryLog
      
WHERE
      EntryLogID = @EntryLogID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO






CREATE  PROCEDURE TT_DeleteUser
(
      @UserID int
)
AS

DELETE FROM
      TT_Users
      
WHERE
      UserID = @UserID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO






CREATE PROCEDURE TT_GetManagerProjectCount
      @UserID int
AS
      SELECT COUNT(*) FROM TT_Projects WHERE ManagerUserID = @UserID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO




SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO







CREATE  PROCEDURE TT_ListMembers
(
      @ProjectID int
)
AS

SELECT
      u.UserID, UserName
      
FROM
      TT_ProjectMembers pm
INNER JOIN
      TT_Users u
On
      u.UserID = pm.UserID
      
WHERE
      ProjectID = @ProjectID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO








CREATE    PROCEDURE TT_ListCategoriesByProject
(
    @ProjectID int
)
AS

    SELECT C.CategoryID,
          C.Name,
            C.Abbreviation AS CategoryShortName,
            EstDuration = CAST(Round(C.EstDuration,0) AS int),
            ISNULL(Sum(EL.Duration), 0) AS ActualHours
    FROM TT_Categories C
        LEFT OUTER JOIN TT_EntryLog EL
            ON C.CategoryID = EL.CategoryID
    WHERE C.ProjectID = @ProjectID
    GROUP BY C.CategoryID, C.Name, C.Abbreviation, C.EstDuration
   
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


CREATE   PROCEDURE TT_GetProject
(
      @ProjectID int
)
AS

SELECT
      Name, Description, ManagerUserID, EstCompletionDate, EstDuration = CAST(Round(EstDuration,0) AS int)

FROM
      TT_Projects

WHERE
      ProjectID = @ProjectID

exec TT_ListMembers @ProjectID
exec TT_ListCategoriesByProject @ProjectID

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO







CREATE  PROCEDURE TT_GetTimeEntry
(
    @EntryLogID int
)
AS

SELECT
    EL.Description,
    EL.Duration,
    EL.EntryDate,
    EL.ProjectID,
    EL.UserID,
    EL.CategoryID,
    P.Name AS ProjectName

FROM
    TT_EntryLog EL
        INNER JOIN TT_Projects P
            ON EL.ProjectID = P.ProjectID
   
WHERE
    EL.EntryLogID = @EntryLogID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO






CREATE  PROCEDURE TT_GetUserByUserName
(
      @UserName nvarchar(50)
)

AS
      
SELECT
      UserID,
      UserName,
      Password,
--       LastName,
--       Email,
--       Telephone,
      RoleID

FROM
      TT_Users WHERE UserName = @UserName

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create proc TT_GetUserDisplayName
      @Username nvarchar(50)
AS
      SELECT DisplayName FROM TT_Users
      WHERE UserName = @Username

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO







CREATE  PROCEDURE TT_ListAllProjects

AS

      SELECT ProjectID,
            Name as ProjectName,
            Description,
            ManagerUserID,
            EstCompletionDate,
            EstDuration
      FROM TT_Projects
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO





CREATE PROCEDURE TT_ListAllRoles

AS

SELECT
      RoleID,
       Name
FROM
      TT_Roles
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO






CREATE PROCEDURE TT_ListCategories
(
      @ProjectID int
)
AS

SELECT
      CategoryID, Name, Abbreviation, CAST(EstDuration AS int) EstDuration
      
FROM
      TT_Categories
      
WHERE
      ProjectID = @ProjectID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO










CREATE    PROCEDURE TT_ListManagers

AS

SELECT
      UserID, UserName, RoleID
      
FROM
      TT_Users
Where
      RoleID = 2
OR
      RoleID = 1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO







CREATE   PROCEDURE TT_ListProjects
(
      @UserID int,
      @RoleID int
)
AS

IF @RoleID = 1
BEGIN
      SELECT ProjectID,
            Name as ProjectName,
            Description,
            ManagerUserID,
            UserName,
            EstCompletionDate,
            EstDuration
      FROM
            TT_Projects
      INNER JOIN
            TT_Users
      ON
            ManagerUserID = UserID
END

ELSE IF @RoleID = 2
BEGIN
      SELECT ProjectID,
            Name as ProjectName,
            Description,
            ManagerUserID,
            UserName,
            EstCompletionDate,
            EstDuration
      FROM
            TT_Projects
      INNER JOIN
            TT_Users
      ON
            ManagerUserID = UserID
      WHERE ManagerUserID = @UserID
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO






CREATE  PROCEDURE TT_ListProjectsByIDs
(
    @ProjectIDs nvarchar(512),
    @UserID int
)
AS

    DECLARE @sql nvarchar(1024),
            @RoleID int
   
    SELECT @RoleID = RoleID
    FROM TT_Users
    WHERE UserID = @UserID;
   
    IF (@RoleID = 1)
        BEGIN
        SET @sql = 'SELECT P.ProjectID,'
        SET @sql = @sql + ' P.Name AS ProjectName,'
        SET @sql = @sql + ' P.EstCompletionDate,'
        SET @sql = @sql + ' P.EstDuration AS EstHours,'
        SET @sql = @sql + ' Sum(EL.Duration) AS ActualHours '
        SET @sql = @sql + 'FROM TT_Projects P INNER JOIN TT_EntryLog EL'
        SET @sql = @sql + ' ON P.ProjectID = EL.ProjectID '
        SET @sql = @sql + 'WHERE P.ProjectID IN (' + @ProjectIDs + ') '
        SET @sql = @sql + 'GROUP BY P.ProjectID, P.Name, P.EstCompletionDate, P.EstDuration'            
        END
    ELSE IF (@RoleID = 2)
        BEGIN
        SET @sql = 'SELECT P.ProjectID,'
        SET @sql = @sql + ' P.Name AS ProjectName,'
        SET @sql = @sql + ' P.EstCompletionDate,'
        SET @sql = @sql + ' P.EstDuration AS EstHours,'
        SET @sql = @sql + ' Sum(EL.Duration) AS ActualHours '
        SET @sql = @sql + 'FROM TT_Projects P INNER JOIN TT_EntryLog EL'
        SET @sql = @sql + ' ON P.ProjectID = EL.ProjectID '
        SET @sql = @sql + 'WHERE P.ProjectID IN (' + @ProjectIDs + ') '
        SET @sql = @sql + ' AND P.ManagerUserID = ' + CAST(@UserID AS nvarchar(20))
        SET @sql = @sql + 'GROUP BY P.ProjectID, P.Name, P.EstCompletionDate, P.EstDuration'            
        END
    ELSE
        BEGIN
        SET @sql = 'SELECT P.ProjectID,'
        SET @sql = @sql + ' P.Name AS ProjectName,'
        SET @sql = @sql + ' P.EstCompletionDate,'
        SET @sql = @sql + ' P.EstDuration AS EstHours,'
        SET @sql = @sql + ' P.EstDuration AS ActualHours '
        SET @sql = @sql + 'FROM TT_Projects P WHERE 1=0 '
        END
   
    EXEC sp_executesql @sql
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO





CREATE   PROCEDURE TT_ListProjectsWithMembership
(
      @QueryUserID int,
      @UserID int
)
AS

DECLARE @@QueryUserRoleID int
SELECT @@QueryUserRoleID = TT_Users.RoleID FROM TT_Users WHERE TT_Users.UserID = @QueryUserID

IF @@QueryUserRoleID = 1 OR @QueryUserID = @UserID
  BEGIN
      SELECT       TT_Projects.ProjectID,
            Name,
            Description,
            ManagerUserID,
            EstCompletionDate,
            EstDuration
      FROM TT_Projects
      INNER JOIN TT_ProjectMembers ON TT_ProjectMembers.ProjectID = TT_Projects.ProjectID
      WHERE UserID = @UserID
  END

IF @@QueryUserRoleID = 2
  BEGIN
      SELECT       TT_Projects.ProjectID,
            Name,
            Description,
            ManagerUserID,
            EstCompletionDate,
            EstDuration
      FROM TT_Projects
      INNER JOIN TT_ProjectMembers ON TT_ProjectMembers.ProjectID = TT_Projects.ProjectID
      WHERE UserID = @UserID AND ManagerUserID = @QueryUserID
  END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO





CREATE  PROCEDURE TT_ListResourceByIDs
(
    @UserIDs nvarchar(512),
    @StartDate DateTime,
    @EndDate DateTime
)
AS

    DECLARE @sql nvarchar(1024)
   
    SET @sql = 'SELECT U.UserID, U.UserName, '
    SET @sql = @sql + ' Sum(EL.Duration) AS totalHours'
    SET @sql = @sql + ' FROM TT_EntryLog EL INNER JOIN TT_Users U'
    SET @sql = @sql + ' ON EL.UserID = U.UserID '
    SET @sql = @sql + ' WHERE U.UserID IN (' + @UserIDs + ') '
    SET @sql = @sql + ' AND EL.EntryDate >= ' +CAST(@StartDate as nvarchar(20))+ ' AND EL.EntryDate <= ' + CAST(@EndDate as nvarchar(20))      
    SET @sql = @sql + ' GROUP BY U.UserID, U.UserName'

    EXEC sp_executesql @sql
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO









CREATE PROCEDURE TT_ListTimeEntries
(
      @QueryUserID int,
      @UserID int,
      @StartDate datetime,
      @EndDate datetime
)
AS
DECLARE @@QueryUserRoleID int

SELECT @@QueryUserRoleID = TT_Users.RoleID FROM TT_Users WHERE TT_Users.UserID = @QueryUserID

IF @@QueryUserRoleID = 1 or @QueryUserID = @UserID
      BEGIN
            SELECT
                  EntryLogID, TT_EntryLog.Description, Duration, EntryDate, TT_EntryLog.ProjectID AS ProjectID,
                  TT_EntryLog.CategoryID AS CategoryID, TT_Categories.Abbreviation AS CategoryName, TT_Projects.Name AS ProjectName,
                  ManagerUserID, TT_Categories.Abbreviation AS CatShortName
            FROM
                  TT_EntryLog
                        INNER JOIN
                          TT_Categories
                        ON
                          TT_EntryLog.CategoryID = TT_Categories.CategoryID
                        INNER JOIN
                          TT_Projects
                        ON
                          TT_EntryLog.ProjectID = TT_Projects.ProjectID      
            WHERE
                  UserID = @UserID
                  AND
                  Convert(nvarchar, EntryDate, 1) >= Convert(nvarchar, @StartDate, 1)
                  AND
                  Convert(nvarchar, EntryDate, 1) <= Convert(nvarchar, @EndDate, 1)
      END
ELSE IF @@QueryUserRoleID = 2
      BEGIN
            SELECT
                  EntryLogID, TT_EntryLog.Description, Duration, EntryDate, TT_EntryLog.ProjectID AS ProjectID,
                  TT_EntryLog.CategoryID AS CategoryID, TT_Categories.Abbreviation AS CategoryName, TT_Projects.Name AS ProjectName,
                  ManagerUserID, TT_Categories.Abbreviation AS CatShortName
            FROM
                  TT_EntryLog
                        INNER JOIN
                          TT_Categories
                        ON
                          TT_EntryLog.CategoryID = TT_Categories.CategoryID
                        INNER JOIN
                          TT_Projects
                        ON
                          TT_EntryLog.ProjectID = TT_Projects.ProjectID      
            WHERE
                  UserID = @UserID
                  AND
                  Convert(nvarchar, EntryDate, 1) >= Convert(nvarchar, @StartDate, 1)
                  AND
                  Convert(nvarchar, EntryDate, 1) <= Convert(nvarchar, @EndDate, 1)
                  AND
                  ManagerUserID = @QueryUserID
      END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO





CREATE  PROCEDURE TT_ListTimeEntriesByCategory
(
    @CategoryID int
)
AS

      SELECT
            U.UserName,
            U.UserID,
            temp.MinEntryDate,
            temp.MaxEntryDate,
            Sum(EL.Duration) AS Duration
      FROM TT_EntryLog EL
          INNER JOIN TT_Users U
              ON EL.UserID = U.UserID
          INNER JOIN (SELECT UserID, MIN(EntryDate) AS MinEntryDate, MAX(EntryDate) AS MaxEntryDate FROM TT_EntryLog GROUP BY UserID) AS temp
                ON temp.UserID = EL.UserID
      WHERE EL.CategoryID = @CategoryID
      GROUP BY U.UserName, U.UserID, temp.MinEntryDate, temp.MaxEntryDate
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO






CREATE   PROCEDURE TT_ListTimeEntriesByUsers
(
      @UserIDList nvarchar(255),
      @StartDate datetime,
      @EndDate datetime
)
AS

DECLARE
      @sSqlString nvarchar(512)

SET
      @sSqlString = 'SELECT EntryLogID, Description, Duration, EntryDate, ProjectID, UserID, CategoryID'
      
SET
      @sSqlString = @sSqlString + ' FROM TT_EntryLog WHERE UserID IN (' + @UserIDList + ')'

SET      
      @sSqlString = @sSqlString + ' and EntryDate >= '+ CAST(@StartDate AS nvarchar(24)) + ' and EntryDate <=  ' + CAST(@EndDate AS nvarchar(24))

SET
      @sSqlString = @sSqlString + ' GROUP BY UserID'

EXEC sp_executesql @sSqlString
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO






CREATE  PROCEDURE TT_ListUserTimeSummary
(
    @ManagerUserID int,
    @UserIDList nvarchar(512),
    @StartDate datetime,
    @EndDate datetime
)
AS

DECLARE
    @sSqlString nvarchar(1024),
    @sSubSql nvarchar(1024),
    @RoleID int
   
SELECT @RoleID = RoleID
FROM TT_Users
WHERE UserID = @ManagerUserID;

IF (@RoleID = 1)
    BEGIN
    SET @sSqlString = 'SELECT Sum(EL.Duration) TotalHours, U.UserID, U.UserName'
    SET @sSqlString = @sSqlString + ' FROM TT_EntryLog EL Inner Join TT_Users U On EL.UserID = U.UserID WHERE U.UserID IN (' + @UserIDList + ')'
    SET @sSqlString = @sSqlString + ' and EL.EntryDate >= @1 and EL.EntryDate <= @2  GROUP BY U.UserID, U.UserName'
    END
ELSE IF (@RoleID = 2)
    BEGIN  
    SET @sSubSql = 'SELECT PM.UserID FROM TT_Projects P INNER JOIN TT_ProjectMembers PM'
    SET @sSubSql = @sSubSql + ' ON P.ProjectID = PM.ProjectID WHERE P.ManagerUserID = @3 AND PM.UserID IN (' + @UserIDList + ')'
   
    SET @sSqlString = 'SELECT Sum(EL.Duration) TotalHours, U.UserID, U.UserName'
    SET @sSqlString = @sSqlString + ' FROM TT_EntryLog EL Inner Join TT_Users U On EL.UserID = U.UserID WHERE U.UserID IN (' + @sSubSql + ')'
    SET @sSqlString = @sSqlString + ' AND EL.ProjectID IN (SELECT ProjectID From TT_Projects Where ManagerUserID = @3) '
    SET @sSqlString = @sSqlString + ' and EL.EntryDate >= @1 and EL.EntryDate <= @2  GROUP BY U.UserID, U.UserName'
    END
ELSE
    SET @sSqlString = 'SELECT U.UserID AS TotalHours, U.UserID, U.UserName From TT_Users U Where 1=0'

EXEC sp_executesql @sSqlString, N'@1 datetime, @2 datetime, @3 int', @StartDate, @EndDate, @ManagerUserID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO







CREATE       PROCEDURE TT_ListUsers
(
      @UserID int,
      @RoleID int
)
AS

IF @RoleID = 1
      BEGIN
        SELECT
            UserID,
            UserName,
            TT_Users.RoleID,
            TT_Roles.Name 'RoleName'
        FROM
            TT_Users INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
      END
ELSE IF @RoleID = 2
      BEGIN
        SELECT DISTINCT
            TT_Users.UserID,
            TT_Users.UserName,
            TT_Users.RoleID,
            TT_Roles.Name 'RoleName'
        FROM TT_Users
        INNER JOIN TT_ProjectMembers ON TT_Users.UserID=TT_ProjectMembers.UserID
        INNER JOIN TT_Projects ON TT_ProjectMembers.ProjectID=TT_Projects.ProjectID
        INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
        WHERE      @UserID = TT_Projects.ManagerUserID OR @UserID = TT_Users.UserID

      END
ELSE
      SELECT
            UserID,
            UserName,
            TT_Users.RoleID,
            TT_Roles.Name 'RoleName'
      FROM
            TT_Users INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
      WHERE UserID = @UserID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO















CREATE          PROCEDURE TT_UpdateProject
(
      @ProjectID int,
      @Name nvarchar(50),
      @Description nvarchar(1024),
      @ManagerUserID int,
      @EstCompletionDate datetime,
      @EstDuration int,
      @SelectedMembers nvarchar(2000),
      @Categories nvarchar(4000)
)
AS


      DECLARE @Error int
      DECLARE @TempTable TABLE(UserID int PRIMARY KEY)
      DECLARE @TempString varchar(2000)
      DECLARE @Temp varchar(4000)
      DECLARE @Count int
      DECLARE @TempCatTable TABLE(CategoryID int primary key)
      DECLARE @InnerTemp nvarchar(50)
      DECLARE @CatID int
      DECLARE @CatName varchar(20)
      DECLARE @Abbrev varchar(5)
      DECLARE @Duration decimal(10,2)
      DECLARE @InnerCount int

      BEGIN TRANSACTION

      -- Update the Project
      UPDATE
            TT_Projects
      SET       
            Name=@Name,
                    Description = @Description,
            ManagerUserID = @ManagerUserID,
            EstCompletionDate = @EstCompletionDate,
            EstDuration = @EstDuration
            
      WHERE
            ProjectID = @ProjectID

      SET @Error = @@ERROR
      IF @Error != 0 GOTO ERROR_HANDLER

      SET @TempString = @SelectedMembers

      SET @Count = CHARINDEX(',', @TempString)

      WHILE @Count > 0
      BEGIN
            SET @Temp = SUBSTRING(@TempString, 1, (@Count - 1))
            INSERT INTO @TempTable VALUES(CAST(@Temp AS int))
            SET @TempString = SUBSTRING(@TempString, (@Count + 1), (LEN(@TempString) - @Count))
            SET @Count = CHARINDEX(',', @TempString)
      END

      INSERT INTO @TempTable VALUES(CAST(@TempString AS int))

      DELETE
            TT_ProjectMembers
      WHERE
            ProjectID = @ProjectID
      AND UserID NOT IN(SELECT UserID FROM @TempTable)

      INSERT INTO TT_ProjectMembers  
            SELECT @ProjectID, UserID FROM @TempTable WHERE UserID NOT IN
                  (SELECT UserID FROM TT_ProjectMembers WHERE ProjectID = @ProjectID)

      SET @Error = @@ERROR
      IF @Error != 0 GOTO ERROR_HANDLER

      SET @TempString = @Categories
      
      SET @Count = CHARINDEX(';', @TempString)
      WHILE @Count > 0
      BEGIN
            SET @Temp = SUBSTRING(@TempString, 1, (@Count - 1))

            SET @InnerCount = CHARINDEX(',', @Temp)

            SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
            SET @CatID = @InnerTemp

            SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
            SET @InnerCount = CHARINDEX(',', @Temp)

            SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
            SET @CatName = @InnerTemp

            SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
            SET @InnerCount = CHARINDEX(',', @Temp)

            SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
            SET @Abbrev = @InnerTemp
                  
            SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
            SET @InnerCount = CHARINDEX(',', @Temp)

             SET @Duration =  CAST(Round(@Temp,0) AS int)

            IF not exists(SELECT CategoryID from TT_Categories WHERE ProjectID = @ProjectID AND CategoryID = @CatID)
            BEGIN

                  INSERT INTO TT_Categories
                  (
                        ProjectID,
                        [Name],
                        Abbreviation,
                        EstDuration
                  )
                  VALUES
                  (  
                        @ProjectID,
                        @CatName,
                        @Abbrev,
                        @Duration
                  )

                  SET @Error = @@ERROR
                  IF @Error != 0 GOTO ERROR_HANDLER
                  
                  SELECT @CatID = @@IDENTITY
            
            END
            else
            BEGIN
                  UPDATE
                        TT_Categories
                  SET
                        [Name] = @CatName,
                        Abbreviation = @Abbrev,
                        EstDuration = @Duration
                  WHERE
                        ProjectID = @ProjectID
                  AND
                        CategoryID = @CatID

                  SET @Error = @@ERROR
                  IF @Error != 0 GOTO ERROR_HANDLER
            END
            
            INSERT INTO @TempCatTable VALUES(@CatID)
            
            SET @TempString = SUBSTRING(@TempString, (@Count + 1), (LEN(@TempString) - @Count))
            SET @Count = CHARINDEX(';', @TempString)
      END

      set @Temp =  @TempString
      SET @InnerCount = CHARINDEX(',', @Temp)

      SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
      SET @CatID = @InnerTemp

      SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
      SET @InnerCount = CHARINDEX(',', @Temp)

      SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
      SET @CatName = @InnerTemp

      SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
      SET @InnerCount = CHARINDEX(',', @Temp)

      SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
      SET @Abbrev = @InnerTemp

      SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
      SET @InnerCount = CHARINDEX(',', @Temp)

      SET @Duration =  CAST(Round(@Temp,0) AS int)

            IF not exists(SELECT CategoryID from TT_Categories WHERE ProjectID = @ProjectID AND CategoryID = @CatID)
            BEGIN
                  INSERT INTO TT_Categories
                  (
                        ProjectID,
                        [Name],
                        Abbreviation,
                        EstDuration
                  )
                  VALUES
                  (  
                        @ProjectID,
                        @CatName,
                        @Abbrev,
                        @Duration
                  )
            
                  SET @Error = @@ERROR
                  IF @Error != 0 GOTO ERROR_HANDLER
      
                  SELECT @CatID = @@IDENTITY
            
            END
            ELSE
            BEGIN
                  UPDATE
                        TT_Categories
                  SET
                        [Name] = @CatName,
                        Abbreviation = @Abbrev,
                        EstDuration = @Duration
                  WHERE
                        ProjectID = @ProjectID
                  AND
                        CategoryID = @CatID

                  SET @Error = @@ERROR
                  IF @Error != 0 GOTO ERROR_HANDLER
            END
            
            INSERT INTO @TempCatTable VALUES(@CatID)


      DELETE
            TT_Categories
      WHERE
            ProjectID = @ProjectID
      AND
            CategoryID NOT IN(SELECT CategoryID FROM @TempCatTable)

      SET @Error = @@ERROR
      IF @Error != 0 GOTO ERROR_HANDLER

      COMMIT TRANSACTION
      RETURN 0

ERROR_HANDLER:
      IF @@TRANCOUNT != 0 ROLLBACK TRANSACTION
      RETURN @Error


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO







CREATE  PROCEDURE TT_UpdateTimeEntry
(
      @EntryLogID int,
      @UserID int,
      @ProjectID int,
      @CategoryID int,
      @EntryDate datetime,
      @Description nvarchar(255),
      @Duration decimal(10,2)
)
AS

UPDATE
       TT_EntryLog
            SET       
                  UserID=@UserID,
                       ProjectID = @ProjectID,
                  CategoryID = @CategoryID,
                  EntryDate = @EntryDate,
                  Description = @Description,
                  Duration = @Duration
            
WHERE
      EntryLogID = @EntryLogID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO








CREATE   PROCEDURE TT_UpdateUser
(
      @UserID int,
      @UserName nvarchar(50),
      @Password nvarchar(50),
      @DisplayName nvarchar(50),
--      @FirstName nvarchar(50),
--      @LastName nvarchar(50),
--      @Email nvarchar(50),
--      @Telephone nvarchar(50),
      @RoleID int
)
AS

--Verify that the UserName is not being updated to a UserName that already exists
IF Not Exists (SELECT UserName FROM TT_Users WHERE UserName=@UserName AND UserID<>@UserID)
   BEGIN
      UPDATE
            TT_Users
            SET UserName=@UserName,
                  Password = @Password,
                  DisplayName = @DisplayName,
--                  FirstName=@FirstName,
--                    LastName=@LastName,
--                  Email = @Email,
--                  Telephone = @Telephone,
                    RoleID = @RoleID
      WHERE
            UserID=@UserID

      IF (@@Error<>0) GOTO ErrorHandler
      SELECT
            1 AS retval
   END
-- If UserName is not unique, indicate so with -2
ELSE
      SELECT
            -2 AS retval

IF @@Error<>0
      GOTO ErrorHandler
ELSE
   BEGIN
--      COMMIT TRANSACTION
      RETURN (0)
   END

ErrorHandler:
   BEGIN
--      ROLLBACK TRANSACTION
      RETURN (1)
   END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE  Procedure TT_UserLogin
    (
        @UserName  nvarchar(100),
        @Password nvarchar(50)
    )
    AS

    SELECT
        UserName

    FROM
        TT_Users

    WHERE
        UserName = @UserName
    AND
        Password = @Password


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

0
 
LVL 26

Accepted Solution

by:
Hilaire earned 100 total points
Comment Utility
Since you found a solution by yourself,
you can post a free question in the Community Support area,
to get this question closed and your points refunded.

http://www.experts-exchange.com/Community_Support/

If you think you got valuable input, you can split points between contributors.

In both cases, maybe you should leave this question open for a little while to see if it draws attention from others experts.

FYI I didn't see anything wrong with the script you posted.
Can you connect remotely to the newly created DB ?

Regards

Hilaire
0
 
LVL 1

Author Comment

by:ExpertAudit
Comment Utility
Hallo!

The problem was caused by a running process called run32dll.exe wich was consuming 99% of the CPU resources.

This process started with enterprise manager (I don't know why). The query was being processed very slowly because of  the obvious unavailable system resources!!! Dahh ! The instructions were being executed step by step !!

I killed the process, test it again and it all when right :-)

Sory for the inconvenience Folks !

Moral of the story: L.F.O. - Look for the Obvious !

I will split point between contributors.
Thanx !

0
 
LVL 10

Expert Comment

by:Jay Toops
Comment Utility
LOL --- thats the first thing i said...
:D ..

Jay
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now