Easy question. I lost some of my sql and my stored proc is not working.

• peerless

  Ten Centuries

  Points: 1114

  More actions

  June 3, 2009 at 2:50 pm

  #1004296

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 3, 2009 at 3:00 pm

  #1004297

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• peerless

  Ten Centuries

  Points: 1114

  More actions

  June 3, 2009 at 3:05 pm

  #1004302

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 3, 2009 at 3:17 pm

  #1004309

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• peerless

  Ten Centuries

  Points: 1114

  More actions

  June 3, 2009 at 4:42 pm

  #1004342

  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

• Jason-

  SSCrazy

  Points: 2795

  More actions

  June 3, 2009 at 5:24 pm

  #1004348

  What does your code for calling the stored procedure look like?

  -

• WayneS

  SSC Guru

  Points: 95460

  More actions

  June 3, 2009 at 5:39 pm

  #1004352

  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

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• peerless

  Ten Centuries

  Points: 1114

  More actions

  June 3, 2009 at 6:33 pm

  #1004368

  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]

• peerless

  Ten Centuries

  Points: 1114

  More actions

  June 3, 2009 at 6:50 pm

  #1004372

  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.