There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
SQL Server Takes too long to execute a script to create DB and Stored Procedures
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 !
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 !
Asked:
-
4
-
3
2- +1
5 Solutions
Try to Run SQL Profiler when running the script. Add all relevant events, do not forget to monitor the data and log file growths.
reboot the database into single user mode and run it, you might be getting a locking problem
with other processes.
Jay
with other processes.
Jay
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
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
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
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
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!
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!
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_Entr
ALTER TABLE [dbo].[TT_EntryLog] DROP CONSTRAINT FK_EntryLog_Categories
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Entr
ALTER TABLE [dbo].[TT_EntryLog] DROP CONSTRAINT FK_EntryLog_ProjectMembers
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Cate
ALTER TABLE [dbo].[TT_Categories] DROP CONSTRAINT FK_Categories_Projects
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Role
ALTER TABLE [dbo].[TT_ProjectMembers] DROP CONSTRAINT FK_Roles_Projects
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_User
ALTER TABLE [dbo].[TT_Users] DROP CONSTRAINT FK_Users_Roles
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Work
ALTER TABLE [dbo].[TT_ProjectMembers] DROP CONSTRAINT FK_WorksOn_Users
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Proj
ALTER TABLE [dbo].[TT_Projects] DROP CONSTRAINT FK_Projects_Users
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddP
drop procedure [dbo].[TT_AddProject]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddP
drop procedure [dbo].[TT_AddProjectMember
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddT
drop procedure [dbo].[TT_AddTimeEntry]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddU
drop procedure [dbo].[TT_AddUser]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Dele
drop procedure [dbo].[TT_DeleteProject]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Dele
drop procedure [dbo].[TT_DeleteTimeEntry]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Dele
drop procedure [dbo].[TT_DeleteUser]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetM
drop procedure [dbo].[TT_GetManagerProjec
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetP
drop procedure [dbo].[TT_GetProject]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetT
drop procedure [dbo].[TT_GetTimeEntry]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetU
drop procedure [dbo].[TT_GetUserByUserNam
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetU
drop procedure [dbo].[TT_GetUserDisplayNa
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListAllProjects]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListAllRoles]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListCategories]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListCategoriesBy
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListManagers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListMembers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListProjects]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListProjectsByID
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListProjectsWith
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListResourceByID
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListTimeEntries]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListTimeEntriesB
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListTimeEntriesB
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListUserTimeSumm
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListUsers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Upda
drop procedure [dbo].[TT_UpdateProject]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Upda
drop procedure [dbo].[TT_UpdateTimeEntry]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Upda
drop procedure [dbo].[TT_UpdateUser]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_User
drop procedure [dbo].[TT_UserLogin]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Cate
drop table [dbo].[TT_Categories]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Entr
drop table [dbo].[TT_EntryLog]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Proj
drop table [dbo].[TT_ProjectMembers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Proj
drop table [dbo].[TT_Projects]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Role
drop table [dbo].[TT_Roles]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_User
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_
[Abbreviation] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_
[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_
[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_
[Description] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_
[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_
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TT_Users] (
[DisplayName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[Password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[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]
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_ProjectMember
(
[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
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
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_ListProjectsWithMembers
(
@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.ProjectI
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.ProjectI
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
ManagerUserID, TT_Categories.Abbreviation
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
ManagerUserID, TT_Categories.Abbreviation
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_ListTimeEntriesByCatego
(
@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_Project
INNER JOIN TT_Projects ON TT_ProjectMembers.ProjectI
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
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_Entr
ALTER TABLE [dbo].[TT_EntryLog] DROP CONSTRAINT FK_EntryLog_Categories
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Entr
ALTER TABLE [dbo].[TT_EntryLog] DROP CONSTRAINT FK_EntryLog_ProjectMembers
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Cate
ALTER TABLE [dbo].[TT_Categories] DROP CONSTRAINT FK_Categories_Projects
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Role
ALTER TABLE [dbo].[TT_ProjectMembers] DROP CONSTRAINT FK_Roles_Projects
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_User
ALTER TABLE [dbo].[TT_Users] DROP CONSTRAINT FK_Users_Roles
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Work
ALTER TABLE [dbo].[TT_ProjectMembers] DROP CONSTRAINT FK_WorksOn_Users
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Proj
ALTER TABLE [dbo].[TT_Projects] DROP CONSTRAINT FK_Projects_Users
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddP
drop procedure [dbo].[TT_AddProject]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddP
drop procedure [dbo].[TT_AddProjectMember
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddT
drop procedure [dbo].[TT_AddTimeEntry]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddU
drop procedure [dbo].[TT_AddUser]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Dele
drop procedure [dbo].[TT_DeleteProject]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Dele
drop procedure [dbo].[TT_DeleteTimeEntry]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Dele
drop procedure [dbo].[TT_DeleteUser]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetM
drop procedure [dbo].[TT_GetManagerProjec
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetP
drop procedure [dbo].[TT_GetProject]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetT
drop procedure [dbo].[TT_GetTimeEntry]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetU
drop procedure [dbo].[TT_GetUserByUserNam
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetU
drop procedure [dbo].[TT_GetUserDisplayNa
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListAllProjects]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListAllRoles]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListCategories]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListCategoriesBy
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListManagers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListMembers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListProjects]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListProjectsByID
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListProjectsWith
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListResourceByID
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListTimeEntries]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListTimeEntriesB
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListTimeEntriesB
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListUserTimeSumm
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_List
drop procedure [dbo].[TT_ListUsers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Upda
drop procedure [dbo].[TT_UpdateProject]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Upda
drop procedure [dbo].[TT_UpdateTimeEntry]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Upda
drop procedure [dbo].[TT_UpdateUser]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_User
drop procedure [dbo].[TT_UserLogin]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Cate
drop table [dbo].[TT_Categories]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Entr
drop table [dbo].[TT_EntryLog]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Proj
drop table [dbo].[TT_ProjectMembers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Proj
drop table [dbo].[TT_Projects]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Role
drop table [dbo].[TT_Roles]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_User
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_
[Abbreviation] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_
[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_
[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_
[Description] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_
[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_
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TT_Users] (
[DisplayName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[Password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[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]
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_ProjectMember
(
[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
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
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_ListProjectsWithMembers
(
@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.ProjectI
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.ProjectI
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
ManagerUserID, TT_Categories.Abbreviation
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
ManagerUserID, TT_Categories.Abbreviation
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_ListTimeEntriesByCatego
(
@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_Project
INNER JOIN TT_Projects ON TT_ProjectMembers.ProjectI
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
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
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
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 !
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 !
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
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