June 3, 2009 at 2:50 pm
You can you show me what you mean.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].sprUpdateReOpenedTicket') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].sprUpdateReOpenedTicket
GO
CREATE PROCEDURE [dbo].sprUpdateReOpenedTicket
(
@ticketID int = null,
@ticketType nvarchar(50) = null,
@ticketStatus nvarchar(50) = null,
@ticketSeverity nvarchar(50) = null,
@shortDescription nvarchar(1000) = null,
@externalTicket nvarchar(1000) = null,
@reasonMenu nvarchar(50) = null,
@problemNote ntext = null,
@solutionNote ntext = null,
@actionNote ntext = null,
@followupNote ntext = null,
@companiesFlag bit = null,
@agentCreated nvarchar(50) = null,
@dateCreated datetime = null,
@dateLastModified datetime = null,
@agentClosed nvarchar(50) = null,
@dateClosed datetime = null,
@agentFollowup nvarchar(50) = null,
@followupBy datetime = null,
@deletedFlag bit = null,
@ticketTypeOld nvarchar(50) = null,
@assignedTo varchar(50) = null,
@requestType varchar(50) = null,
@serviceImpact varchar(50) = null,
@dateTimeAssigned datetime = null,
@priority varchar(50) = null,
@dateAssigned datetime = null,
@trackIt varchar(50) = null,
@contactMethod varchar(50) = null,
@locationSIMs varchar(50) = null,
@userid nvarchar(50) = null,
@ticketApplicationType nvarchar(50) = null,
@accountID int = null,
@resultCode INT OUTPUT,
@resultMessage NVARCHAR(200) OUTPUT
)
AS
BEGIN
DECLARE @Rows int
BEGIN TRANSACTION
BEGIN TRY
UPDATE [dbo].[tbl_tickets]
SET
[TicketType] = @ticketType,
[TicketStatus] = @ticketStatus,
[TicketSeverity] = @ticketSeverity,
[ShortDescription] = @shortDescription,
[ExternalTicket] = @externalTicket,
[ReasonMenu] = @reasonMenu,
[ProblemNote] = @problemNote,
[SolutionNote] = @solutionNote,
[ActionNote] = @actionNote,
[FollowupNote] = @followupNote,
[CompaniesFlag] = @companiesFlag,
[AgentCreated] = @agentCreated,
[DateCreated] = @dateCreated,
[DateLastModified] = @dateLastModified,
[AgentClosed] = @agentClosed,
[DateClosed] = @dateClosed,
[AgentFollowup] = @agentFollowup,
[FollowupBy] = @followupBy,
[DeletedFlag] = @deletedFlag,
[TicketTypeOld] = @ticketTypeOld,
[AssignedTo] = @assignedTo,
[RequestType] = @requestType,
[ServiceImpact] = @serviceImpact,
[DateTimeAssigned] = @dateTimeAssigned,
[Priority] = @priority,
[DateAssigned] = @dateAssigned,
[TrackIt] = @trackIt,
[ContactMethod] = @contactMethod,
[LocationSIMs] = @locationSIMs,
[UserID] = @userid,
[TicketApplicationType] = @ticketApplicationType,
[AccountID] = @accountID
WHERE [TicketID] = @ticketID
SELECT @Rows = @@ROWCOUNT
,@resultCode = 0
,@resultMessage = 'ticketID ' + CAST(@ticketID AS NVARCHAR(200))
if (@Rows > 0)
begin
INSERT INTO [dbo].[tbl_ticket_history]
([TicketID]
,[Note]
,[DateCreated]
,[AgentCreated]
,[POCIDCreated]
,[CustomerVisible]
,[UpdateAlertRequired]
,[UpdateAlertSent]
,[DateUpdateAlertSent])
VALUES
( @ticketID
,Cast(@actionNote as nvarchar(max))
,GETDATE()
,@agentCreated
,null
,0
,null
,null
,null)
end
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 1 ROLLBACK
SET @ticketID = 0
SET @resultCode = 1
SET @resultMessage = 'ticketID ' + CAST(@ticketID AS NVARCHAR(200))
+ CAST(ERROR_NUMBER() AS NVARCHAR(200)) + '. ' + char(13) + char(10)
+ ERROR_MESSAGE() + '. ' + char(13) + char(10)
+ CAST(ERROR_LINE() AS NVARCHAR(200)) + '. ' + char(13) + char(10)
+ ERROR_PROCEDURE() + '. ' + char(13) + char(10)
+ CAST(ERROR_STATE() AS NVARCHAR(200)) + '. ' + char(13) + char(10)
END CATCH
COMMIT TRAN
END
GO
June 3, 2009 at 3:00 pm
mathieu_cupryk (6/3/2009)
You can you show me what you mean.
Move the COMMIT TRANSACTION so that it's just before the END TRY. I just noticed there's already a rollback in the catch, so that's not necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 3, 2009 at 3:05 pm
Is this what you mean?
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].sprUpdateReOpenedTicket') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].sprUpdateReOpenedTicket
GO
CREATE PROCEDURE [dbo].sprUpdateReOpenedTicket
(
@ticketID int = null,
@ticketType nvarchar(50) = null,
@ticketStatus nvarchar(50) = null,
@ticketSeverity nvarchar(50) = null,
@shortDescription nvarchar(1000) = null,
@externalTicket nvarchar(1000) = null,
@reasonMenu nvarchar(50) = null,
@problemNote ntext = null,
@solutionNote ntext = null,
@actionNote ntext = null,
@followupNote ntext = null,
@companiesFlag bit = null,
@agentCreated nvarchar(50) = null,
@dateCreated datetime = null,
@dateLastModified datetime = null,
@agentClosed nvarchar(50) = null,
@dateClosed datetime = null,
@agentFollowup nvarchar(50) = null,
@followupBy datetime = null,
@deletedFlag bit = null,
@ticketTypeOld nvarchar(50) = null,
@assignedTo varchar(50) = null,
@requestType varchar(50) = null,
@serviceImpact varchar(50) = null,
@dateTimeAssigned datetime = null,
@priority varchar(50) = null,
@dateAssigned datetime = null,
@trackIt varchar(50) = null,
@contactMethod varchar(50) = null,
@locationSIMs varchar(50) = null,
@userid nvarchar(50) = null,
@ticketApplicationType nvarchar(50) = null,
@accountID int = null,
@resultCode INT OUTPUT,
@resultMessage NVARCHAR(200) OUTPUT
)
AS
BEGIN
DECLARE @Rows int
BEGIN TRANSACTION
BEGIN TRY
UPDATE [dbo].[tbl_tickets]
SET
[TicketType] = @ticketType,
[TicketStatus] = @ticketStatus,
[TicketSeverity] = @ticketSeverity,
[ShortDescription] = @shortDescription,
[ExternalTicket] = @externalTicket,
[ReasonMenu] = @reasonMenu,
[ProblemNote] = @problemNote,
[SolutionNote] = @solutionNote,
[ActionNote] = @actionNote,
[FollowupNote] = @followupNote,
[CompaniesFlag] = @companiesFlag,
[AgentCreated] = @agentCreated,
[DateCreated] = @dateCreated,
[DateLastModified] = @dateLastModified,
[AgentClosed] = @agentClosed,
[DateClosed] = @dateClosed,
[AgentFollowup] = @agentFollowup,
[FollowupBy] = @followupBy,
[DeletedFlag] = @deletedFlag,
[TicketTypeOld] = @ticketTypeOld,
[AssignedTo] = @assignedTo,
[RequestType] = @requestType,
[ServiceImpact] = @serviceImpact,
[DateTimeAssigned] = @dateTimeAssigned,
[Priority] = @priority,
[DateAssigned] = @dateAssigned,
[TrackIt] = @trackIt,
[ContactMethod] = @contactMethod,
[LocationSIMs] = @locationSIMs,
[UserID] = @userid,
[TicketApplicationType] = @ticketApplicationType,
[AccountID] = @accountID
WHERE [TicketID] = @ticketID
SELECT @Rows = @@ROWCOUNT
,@resultCode = 0
,@resultMessage = 'ticketID ' + CAST(@ticketID AS NVARCHAR(200))
if (@Rows > 0)
begin
INSERT INTO [dbo].[tbl_ticket_history]
([TicketID]
,[Note]
,[DateCreated]
,[AgentCreated]
,[POCIDCreated]
,[CustomerVisible]
,[UpdateAlertRequired]
,[UpdateAlertSent]
,[DateUpdateAlertSent])
VALUES
( @ticketID
,Cast(@actionNote as nvarchar(max))
,GETDATE()
,@agentCreated
,null
,0
,null
,null
,null)
end
COMMIT TRAN
END TRY
BEGIN CATCH
SET @ticketID = 0
SET @resultCode = 1
SET @resultMessage = 'ticketID ' + CAST(@ticketID AS NVARCHAR(200))
+ CAST(ERROR_NUMBER() AS NVARCHAR(200)) + '. ' + char(13) + char(10)
+ ERROR_MESSAGE() + '. ' + char(13) + char(10)
+ CAST(ERROR_LINE() AS NVARCHAR(200)) + '. ' + char(13) + char(10)
+ ERROR_PROCEDURE() + '. ' + char(13) + char(10)
+ CAST(ERROR_STATE() AS NVARCHAR(200)) + '. ' + char(13) + char(10)
IF @@TRANCOUNT > 1 ROLLBACK
END CATCH
END
GO
June 3, 2009 at 3:17 pm
That looks OK. Does it run?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 3, 2009 at 4:42 pm
It fails 🙁
U are right breajk, but I still get the error
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
CREATE PROCEDURE [dbo].sprUpdateOpenTicket
(
@ticketID int = null,
@ticketType nvarchar(50) = null,
@ticketStatus nvarchar(50) = null,
@ticketSeverity nvarchar(50) = null,
@shortDescription nvarchar(1000) = null,
@externalTicket nvarchar(1000) = null,
@reasonMenu nvarchar(50) = null,
@problemNote ntext = null,
@solutionNote ntext = null,
@actionNote ntext = null,
@followupNote ntext = null,
@companiesFlag bit = null,
@agentCreated nvarchar(50) = null,
@dateCreated datetime = null,
@dateLastModified datetime = null,
@agentClosed nvarchar(50) = null,
@dateClosed datetime = null,
@agentFollowup nvarchar(50) = null,
@followupBy datetime = null,
@deletedFlag bit = null,
@ticketTypeOld nvarchar(50) = null,
@assignedTo varchar(50) = null,
@requestType varchar(50) = null,
@serviceImpact varchar(50) = null,
@dateTimeAssigned datetime = null,
@priority varchar(50) = null,
@dateAssigned datetime = null,
@trackIt varchar(50) = null,
@contactMethod varchar(50) = null,
@locationSIMs varchar(50) = null,
@userid nvarchar(50) = null,
@ticketApplicationType nvarchar(50) = null,
@accountID int = null,
@resultCode INT OUTPUT,
@resultMessage NVARCHAR(200) OUTPUT
)
AS
BEGIN
DECLARE @Rows int
BEGIN TRANSACTION
BEGIN TRY
UPDATE [dbo].[tbl_tickets]
SET
[TicketType] = @ticketType,
[TicketStatus] = @ticketStatus,
[TicketSeverity] = @ticketSeverity,
[ShortDescription] = @shortDescription,
[ExternalTicket] = @externalTicket,
[ReasonMenu] = @reasonMenu,
[ProblemNote] = @problemNote,
[SolutionNote] = @solutionNote,
[ActionNote] = @actionNote,
[FollowupNote] = @followupNote,
[CompaniesFlag] = @companiesFlag,
[AgentCreated] = @agentCreated,
[DateCreated] = @dateCreated,
[DateLastModified] = @dateLastModified,
[AgentClosed] = @agentClosed,
[DateClosed] = @dateClosed,
[AgentFollowup] = @agentFollowup,
[FollowupBy] = @followupBy,
[DeletedFlag] = @deletedFlag,
[TicketTypeOld] = @ticketTypeOld,
[AssignedTo] = @assignedTo,
[RequestType] = @requestType,
[ServiceImpact] = @serviceImpact,
[DateTimeAssigned] = @dateTimeAssigned,
[Priority] = @priority,
[DateAssigned] = @dateAssigned,
[TrackIt] = @trackIt,
[ContactMethod] = @contactMethod,
[LocationSIMs] = @locationSIMs,
[UserID] = @userid,
[TicketApplicationType] = @ticketApplicationType,
[AccountID] = @accountID
WHERE [TicketID] = @ticketID
SELECT @Rows = @@ROWCOUNT
,@resultCode = 0
,@resultMessage = 'ticketID ' + CAST(@ticketID AS NVARCHAR(200))
if (@Rows > 0)
begin
INSERT INTO [dbo].[tbl_ticket_history]
([TicketID]
,[Note]
,[DateCreated]
,[AgentCreated]
,[POCIDCreated]
,[CustomerVisible]
,[UpdateAlertRequired]
,[UpdateAlertSent]
,[DateUpdateAlertSent])
VALUES
( @ticketID
,Cast(@actionNote as nvarchar(max))
,GETDATE()
,@agentCreated
,null
,0
,null
,null
,null)
end
COMMIT TRAN
END TRY
BEGIN CATCH
SET @ticketID = 0
SET @resultCode = 1
SET @resultMessage = 'ticketID ' + CAST(@ticketID AS NVARCHAR(200))
+ CAST(ERROR_NUMBER() AS NVARCHAR(200)) + '. ' + char(13) + char(10)
+ ERROR_MESSAGE() + '. ' + char(13) + char(10)
+ CAST(ERROR_LINE() AS NVARCHAR(200)) + '. ' + char(13) + char(10)
+ ERROR_PROCEDURE() + '. ' + char(13) + char(10)
+ CAST(ERROR_STATE() AS NVARCHAR(200)) + '. ' + char(13) + char(10)
IF @@TRANCOUNT > 1 ROLLBACK
END CATCH
END
GO
June 3, 2009 at 5:24 pm
What does your code for calling the stored procedure look like?
-
June 3, 2009 at 5:39 pm
mathieu_cupryk (6/3/2009)
I am missing something in the following:
F EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].sprUpdateReOpenedTicket') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].sprUpdateReOpenedTicket
GO
CREATE PROCEDURE [dbo].sprUpdateReOpenedTicket
(
@ticketID int = null,
@ticketType nvarchar(50) = null,
@ticketStatus nvarchar(50) = null,
@ticketSeverity nvarchar(50) = null,
@shortDescription nvarchar(1000) = null,
@externalTicket nvarchar(1000) = null,
@reasonMenu nvarchar(50) = null,
@problemNote ntext = null,
@solutionNote ntext = null,
@actionNote ntext = null,
@followupNote ntext = null,
@companiesFlag bit = null,
@agentCreated nvarchar(50) = null,
@dateCreated datetime = null,
@dateLastModified datetime = null,
@agentClosed nvarchar(50) = null,
@dateClosed datetime = null,
@agentFollowup nvarchar(50) = null,
@followupBy datetime = null,
@deletedFlag bit = null,
@ticketTypeOld nvarchar(50) = null,
@assignedTo varchar(50) = null,
@requestType varchar(50) = null,
@serviceImpact varchar(50) = null,
@dateTimeAssigned datetime = null,
@priority varchar(50) = null,
@dateAssigned datetime = null,
@trackIt varchar(50) = null,
@contactMethod varchar(50) = null,
@locationSIMs varchar(50) = null,
@userid nvarchar(50) = null,
@ticketApplicationType nvarchar(50) = null,
@accountID int = null,
@resultCode INT OUTPUT,
@resultMessage NVARCHAR(200) OUTPUT
)
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
UPDATE
[dbo].[tbl_tickets]
SET
[TicketType] = @ticketType,
[TicketStatus] = @ticketStatus,
[TicketSeverity] = @ticketSeverity,
[ShortDescription] = @shortDescription,
[ExternalTicket] = @externalTicket,
[ReasonMenu] = @reasonMenu,
[ProblemNote] = @problemNote,
[SolutionNote] = @solutionNote,
[ActionNote] = @actionNote,
[FollowupNote] = @followupNote,
[CompaniesFlag] = @companiesFlag,
[AgentCreated] = @agentCreated,
[DateCreated] = @dateCreated,
[DateLastModified] = @dateLastModified,
[AgentClosed] = @agentClosed,
[DateClosed] = @dateClosed,
[AgentFollowup] = @agentFollowup,
[FollowupBy] = @followupBy,
[DeletedFlag] = @deletedFlag,
[TicketTypeOld] = @ticketTypeOld,
[AssignedTo] = @assignedTo,
[RequestType] = @requestType,
[ServiceImpact] = @serviceImpact,
[DateTimeAssigned] = @dateTimeAssigned,
[Priority] = @priority,
[DateAssigned] = @dateAssigned,
[TrackIt] = @trackIt,
[ContactMethod] = @contactMethod,
[LocationSIMs] = @locationSIMs,
[UserID] = @userid,
[TicketApplicationType] = @ticketApplicationType,
[AccountID] = @accountID
WHERE
[TicketID] = @ticketID
SET @resultCode = 0
SET @resultMessage = 'ticketID ' + CAST(@ticketID AS NVARCHAR(200))
if (@@ROWCOUNT>0)
begin
INSERT INTO [dbo].[tbl_ticket_history]
([TicketID]
,[Note]
,[DateCreated]
,[AgentCreated]
,[POCIDCreated]
,[CustomerVisible]
,[UpdateAlertRequired]
,[UpdateAlertSent]
,[DateUpdateAlertSent])
VALUES
( @ticketID
,Cast(@actionNote as nvarchar(max))
,GETDATE()
,@agentCreated
,null
,0
,null
,null
,null)
end
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 1 ROLLBACK
SET @ticketID = 0
SET @resultCode = 1
SET @resultMessage = 'ticketID ' + CAST(@ticketID AS NVARCHAR(200))
+ ERROR_NUMBER() + '. ' + char(13) + char(10)
+ ERROR_MESSAGE() + '. ' + char(13) + char(10)
+ ERROR_LINE() + '. ' + char(13) + char(10)
+ ERROR_PROCEDURE() + '. ' + char(13) + char(10)
+ ERROR_STATE() + '. ' + char(13) + char(10)
END CATCH
COMMIT TRAN
END
GO
I appreciate any help.
In the Catch block, you are added the ERROR_NUMBER() to @resultMessage. Since ERROR_NUMBER() is an integer, it is trying to convert the string to an int. Since the string contains "ticketID", the conversion fails and generates the error that you see.
Edit: Sigh... I should have read at least the first page of posts since others had already posted this.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2009 at 6:33 pm
I forget to create a foreign key constraint.
When I am doing an update into tbl_tickets the primary PK must be the same as the primary key in the table tbl_tickets_history the ticketID is a foreign key into the table tbk_ticket_history.
CREATE TABLE [dbo].[tbl_tickets](
[TicketID] [int] IDENTITY(1,1) NOT NULL,
[TicketNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TicketType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TicketStatus] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TicketSeverity] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShortDescription] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ExternalTicket] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ReasonMenu] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProblemNote] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SolutionNote] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ActionNote] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FollowupNote] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CompaniesFlag] [bit] NULL,
[AgentCreated] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateCreated] [datetime] NULL,
[DateLastModified] [datetime] NULL,
[AgentClosed] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateClosed] [datetime] NULL,
[AgentFollowup] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FollowupBy] [datetime] NULL,
[DeletedFlag] [bit] NULL CONSTRAINT [DF_tbl_tickets_DeletedFlag] DEFAULT ((0)),
[TicketTypeOld] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AssignedTo] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RequestType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServiceImpact] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateTimeAssigned] [datetime] NULL,
[Priority] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateAssigned] [datetime] NULL,
[TrackIt] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContactMethod] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocationSIMs] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TicketApplicationType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AccountID] [int] NULL,
CONSTRAINT [PK_tbl_ticket] PRIMARY KEY NONCLUSTERED
(
[TicketID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[tbl_ticket_history](
[TicketHistoryID] [int] IDENTITY(1,1) NOT NULL,
[TicketID] [int] NULL,
[Note] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateCreated] [datetime] NULL,
[AgentCreated] [int] NULL,
[POCIDCreated] [int] NULL,
[CustomerVisible] [bit] NULL CONSTRAINT [DF_tbl_ticket_history_CustomerVisible] DEFAULT ((0)),
[UpdateAlertRequired] [bit] NULL,
[UpdateAlertSent] [bit] NULL,
[DateUpdateAlertSent] [datetime] NULL,
CONSTRAINT [PK_tbl_ticket_history] PRIMARY KEY CLUSTERED
(
[TicketHistoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
June 3, 2009 at 6:50 pm
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tbl_ticket_history_tbl_tickets]') AND parent_object_id = OBJECT_ID(N'[dbo].[tbl_ticket_history]'))
ALTER TABLE [dbo].[tbl_ticket_history] DROP CONSTRAINT [FK_tbl_ticket_history_tbl_tickets]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_ticket_history]') AND type in (N'U'))
DROP TABLE [dbo].[tbl_ticket_history]
GO
CREATE TABLE [dbo].[tbl_ticket_history](
[TicketHistoryID] [int] IDENTITY(1,1) NOT NULL,
[TicketID] [int] NOT NULL,
[Note] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateCreated] [datetime] NOT NULL CONSTRAINT [DF_tbl_ticket_history_DateCreated] DEFAULT (getdate()),
[AgentCreated] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[POCIDCreated] [int] NULL,
[CustomerVisible] [bit] NOT NULL CONSTRAINT [DF_tbl_ticket_history_CustomerVisible] DEFAULT ((0)),
[UpdateAlertRequired] [bit] NULL,
[UpdateAlertSent] [bit] NULL,
[DateUpdateAlertSent] [datetime] NULL,
CONSTRAINT [PK_tbl_ticket_history] PRIMARY KEY CLUSTERED
(
[TicketHistoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_ticket_history] WITH CHECK ADD CONSTRAINT [FK_tbl_ticket_history_tbl_tickets] FOREIGN KEY([TicketID])
REFERENCES [dbo].[tbl_tickets] ([TicketID])
GO
ALTER TABLE [dbo].[tbl_ticket_history] CHECK CONSTRAINT [FK_tbl_ticket_history_tbl_tickets]
It works.... I was walking outside and it came to me.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply