Occasional mysterious time outs on SQL Server db table

Question

Post reply

Occasional mysterious time outs on SQL Server db table

joshua.james.1979

SSC Veteran

Points: 211
More actions
September 14, 2017 at 12:36 am

#340049

Hi there,
We have users in the field who use a mobile web GIS application (using Java) which use forms to edit/update spatial data.
There is one particular table that occasionally locks up for hours and nobody can edit or insert either via this application, the desktop GIS or SQL Server.
We can't even disable a trigger on this table. Everything times out/freezes. There is plently of memory, no errors in the log, no locks to be found and everything looks fine but it isn't.
It generally just starts working after a few hours but sometimes we reboot the server SQL Server is running on.
Any ideas of how to troubleshoot this type of situation? Has anyone had a similar problem?
Thanks,
Josh

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 1

Can you post all the relevant DDL for the table, indices, triggers etc. and also the relevant database settings?
😎

Arsh SSCertifiable Points: 6345 More actions · Answer 2

joshua.james.1979 - Thursday, September 14, 2017 12:36 AM
Hi there,
We have users in the field who use a mobile web GIS application (using Java) which use forms to edit/update spatial data.
There is one particular table that occasionally locks up for hours and nobody can edit or insert either via this application, the desktop GIS or SQL Server.
We can't even disable a trigger on this table. Everything times out/freezes. There is plently of memory, no errors in the log, no locks to be found and everything looks fine but it isn't.
It generally just starts working after a few hours but sometimes we reboot the server SQL Server is running on.
Any ideas of how to troubleshoot this type of situation? Has anyone had a similar problem?
Thanks,
Josh

Time outs are also symptoms of stale statistics . Check if the stats are updated . What strategy do you follow for stats maintenance ?

Sergiy SSC Guru Points: 110206 More actions · Answer 3

You mentioned "edit/update spatial data."

And then you said "no locks".

Sorry, it cannot be true.

It simply not possible.

UPDATEs always incure locks. Always.

Even if you have (NOLOCK) hint in the statement - it's ignored and exclusive locks are applied anyway.

What you describe appears to be a deadlocking issue.

And, quite possible, due to the way Java tools access the data, you may have deadlocking on schema.

I would suggest to look for BEGIN TRANSACTION instructions in Java.

If a connection with explicit transaction executed an update statement and failed to COMMIT (lost connection, timed out, etc.) then the locks will be held forever, until all open connections are rolled back due to the server restart.

Look for locks, X and Sch-M ones in particular.

_____________
Code for TallyGenerator

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 4

Try using the 1222 traceflag to get any deadlock information into the error log
😎

DBCC TRACEON(1222,-1)

joshua.james.1979 SSC Veteran Points: 211 More actions · Answer 5

The index scan looks ok, the estimated number of rows is exactly the same count as actual number of rows.
Now when I say it is a Java application, it is Geomedia Smart Client, which the user connects with a JNLP. This contains a few Java plugins but the overall application is delivered through a web HTTP connection.
What's the easiest way to get the DDL's? Is sp_help 'table' enough?

Thanks,

Josh

Arsh SSCertifiable Points: 6345 More actions · Answer 6

What is the mode of stats update is your DB configured for ? Even if its AUTO , the synchronous update might stall the query execution until the stats are updated first and then the query resumes . If this time increase , it causes the timeouts .. which becomes a case to go for Asynchronous update . I would also recommend to check the waits stats of the IO latches.

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 7

joshua.james.1979 - Thursday, September 14, 2017 5:16 PM
The index scan looks ok, the estimated number of rows is exactly the same count as actual number of rows.
Now when I say it is a Java application, it is Geomedia Smart Client, which the user connects with a JNLP. This contains a few Java plugins but the overall application is delivered through a web HTTP connection.
What's the easiest way to get the DDL's? Is sp_help 'table' enough?
Thanks,
Josh

Still waiting for the basic information: "Can you post all the relevant DDL for the table, indices, triggers etc. and also the relevant database settings?"
😎
Without the information, anything posted will be pure speculations!

Did you enable the TF 1222?

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 8

Sergiy - Thursday, September 14, 2017 4:52 AM
UPDATEs always incure locks. Always.Even if you have (NOLOCK) hint in the statement - it's ignored and exclusive locks are applied anyway.

All statements issue some sort of locks, even if it's a share mode lock. The way NOLOCK works is that the statement with the hint still takes out locks on the rows / pages / tables being queried, but this statement ignores locks held by other sessions statements.
https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/

Mr. Brian Gale SSC-Insane Points: 24670 More actions · Answer 9

joshua.james.1979 - Thursday, September 14, 2017 5:16 PM
The index scan looks ok, the estimated number of rows is exactly the same count as actual number of rows.
Now when I say it is a Java application, it is Geomedia Smart Client, which the user connects with a JNLP. This contains a few Java plugins but the overall application is delivered through a web HTTP connection.
What's the easiest way to get the DDL's? Is sp_help 'table' enough?
Thanks,
Josh

What they are looking for is the code required to re-generate the table and its structure. So, if you can script out the table and triggers and post that here, we can try to help.

As for a few other things you can check, when it gets stuck like that, you can run DBCC OPENTRAN (while connected to the database) to see a list of open transactions. More information about DBCC OPENTRAN can be found here: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-opentran-transact-sql
using DBCC OPENTRAN along with EXEC sp_who2 can help find out which connection is causing the blocking if it is due to a stuck transaction.

If it is due to a deadlock, then traceflag 1222 (as Eirikur Eiriksson pointed out) will be helpful.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

joshua.james.1979 SSC Veteran Points: 211 More actions · Answer 10

Ok, here is the table creation script.

CREATE TABLE [dbo].[AREG_GMSC](

[GMSC_Key] [int] IDENTITY(1,1) NOT NULL,

[Txn_Date] [datetime] NULL,

[Date_Processed_MPSC] [datetime] NULL,

[ID_COUNTER] [int] NULL,

[ADDRESS1] [nvarchar](90) NULL,

[ADDRESS2] [nvarchar](90) NULL,

[ADDRESS3] [nvarchar](90) NULL,

[LOCALITY] [nvarchar](90) NULL,

[ASSET_DESCRIPTION] [nvarchar](90) NULL,

[ASSET_NAME] [nvarchar](66) NULL,

[COMMENT] [nvarchar](255) NULL,

[CONDITION] [float] NULL,

[CONTROL] [nvarchar](50) NULL,

[DATEINSP] [datetime] NULL,

[DATEINST] [datetime] NULL,

[FEATURE] [nvarchar](55) NULL,

[INSPECTOR] [nvarchar](50) NULL,

[JOBNO] [int] NULL,

[MAPREF] [nvarchar](7) NULL,

[MATERIAL] [nvarchar](50) NULL,

[MPSNO] [nvarchar](50) NULL,

[PHOTOCODE] [nvarchar](255) NULL,

[POLETYPE] [nvarchar](50) NULL,

[TYPE] [nvarchar](60) NULL,

[TYPE2] [nvarchar](60) NULL,

[TYPE3] [nvarchar](60) NULL,

[HEIGHT] [float] NULL,

[LENGTH] [float] NULL,

[WIDTH] [float] NULL,

[float] NULL,

[COVER] [float] NULL,

[DEPTH] [float] NULL,

[DISPOSED] [bit] NULL,

[DISPOSAL_DATE] [datetime] NULL,

[DWG_NO] [nvarchar](50) NULL,

[DEBTYPE] [nvarchar](255) NULL,

[ABAND] [bit] NULL,

[ACCESS] [nvarchar](255) NULL,

[ACTION] [nvarchar](255) NULL,

[FLOW] [bit] NULL,

[LIDTYPE] [nvarchar](50) NULL,

[VERIFIED] [bit] NULL,

[VER_COMM] [nvarchar](50) NULL,

[PIT_NO] [nvarchar](55) NULL,

[PIT_SIZE] [nvarchar](255) NULL,

[BACKFILL] [nvarchar](255) NULL,

[BACKEND_ERROR_TYPE] [int] NULL,

[US_PIT2] [int] NULL,

[DIA_WIDTH] [float] NULL,

[Geometry] [varbinary](max) NULL,

[STATUS] [smallint] NULL,

[STATUS_COMMENT] [nvarchar](max) NULL,

[PARENT_NUMBER] [nvarchar](50) NULL,

[CHILD_G1] [nvarchar](50) NULL,

[CHILD_G2] [nvarchar](50) NULL,

[Geometry_SPA] [geometry] NULL,

[INSPECTION_STATUS] [nvarchar](10) NULL,

[TEXT1] [nvarchar](50) NULL,

[TEXT2] [nvarchar](50) NULL,

[TEXT3] [nvarchar](50) NULL,

[DS_PIT] [nvarchar](10) NULL,

[US_PIT] [nvarchar](10) NULL,

[ABANDONED_ALT] [smallint] NULL,

[VARCHAR_1] [nvarchar](255) NULL,

[VARCHAR_2] [nvarchar](255) NULL,

[VARCHAR_3] [nvarchar](255) NULL,

[VARCHAR_4] [nvarchar](255) NULL,

[VARCHAR_5] [nvarchar](255) NULL,

[VARCHAR_6] [nvarchar](255) NULL,

[VARCHAR_7] [nvarchar](255) NULL,

[VARCHAR_8] [nvarchar](255) NULL,

[VARCHAR_9] [nvarchar](255) NULL,

[VARCHAR_10] [nvarchar](255) NULL,

[VARCHAR_11] [nvarchar](255) NULL,

[VARCHAR_12] [nvarchar](255) NULL,

[VARCHAR_13] [nvarchar](255) NULL,

[VARCHAR_14] [nvarchar](255) NULL,

[VARCHAR_15] [nvarchar](255) NULL,

[INT_1] [smallint] NULL,

[INT_2] [smallint] NULL,

[INT_3] [smallint] NULL,

[INT_4] [smallint] NULL,

[INT_5] [smallint] NULL,

[INT_6] [smallint] NULL,

[INT_7] [smallint] NULL,

[INT_8] [smallint] NULL,

[INT_9] [smallint] NULL,

[INT_10] [smallint] NULL,

[DATE_1] [datetime] NULL,

[DATE_2] [datetime] NULL,

[BIT_1] [smallint] NULL,

[BIT_2] [smallint] NULL,

[BIT_3] [smallint] NULL,

[BIT_4] [smallint] NULL,

[BIT_5] [smallint] NULL,

[BIT_6] [smallint] NULL,

[BIT_7] [smallint] NULL,

[BIT_8] [smallint] NULL,

[BIT_9] [smallint] NULL,

[BIT_10] [smallint] NULL,

[BIT_11] [smallint] NULL,

[BIT_12] [smallint] NULL,

[BIT_13] [smallint] NULL,

[BIT_14] [smallint] NULL,

[BIT_15] [smallint] NULL,

[TEXT11] [nvarchar](50) NULL,

[TEXT21] [nvarchar](50) NULL,

[TEXT31] [nvarchar](50) NULL,

[TEXT4] [nvarchar](50) NULL,

[TEXT5] [nvarchar](50) NULL,

[TEXT6] [nvarchar](50) NULL,

[TEXT7] [nvarchar](50) NULL,

[TEXT8] [nvarchar](50) NULL,

[TEXT9] [nvarchar](50) NULL,

[TEXT10] [nvarchar](50) NULL,

[COMM1] [nvarchar](255) NULL,

[COMM2] [nvarchar](255) NULL,

[COMM3] [nvarchar](255) NULL,

[COMM4] [nvarchar](255) NULL,

[COMM5] [nvarchar](255) NULL,

[COMM6] [nvarchar](255) NULL,

[COMM7] [nvarchar](255) NULL,

[COMM8] [nvarchar](255) NULL,

[COMM9] [nvarchar](255) NULL,

[COMM10] [nvarchar](255) NULL,

[OLD_ID_COUNTER] [int] NULL,

[ASSET_PROCESS_ID] [int] NULL,

[DISPOSAL_JOBNO] [int] NULL,

[DATE_3] [datetime] NULL,

[DATE_4] [datetime] NULL,

[CATCH_NO] [int] NULL,

PRIMARY KEY CLUSTERED

(

[GMSC_Key] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[AREG_GMSC] ADD CONSTRAINT [DF_AREG_GMSC_DISPOSED] DEFAULT ((0)) FOR [DISPOSED]

GO

ALTER TABLE [dbo].[AREG_GMSC] ADD CONSTRAINT [DF_AREG_GMSC_ABANDONED] DEFAULT ((0)) FOR [ABAND]

GO

ALTER TABLE [dbo].[AREG_GMSC] ADD CONSTRAINT [DF_AREG_GMSC_FLOW] DEFAULT ((0)) FOR [FLOW]

GO

ALTER TABLE [dbo].[AREG_GMSC] ADD CONSTRAINT [DF_AREG_GMSC_VERIFIED] DEFAULT ((0)) FOR [VERIFIED]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Null not yet Inspected, "1" New Asset just Inspected, "2" Been Inspected' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AREG_GMSC', @level2type=N'COLUMN',@level2name=N'INSPECTION_STATUS'

GO

By the way there are probably about 120 views to this table.

joshua.james.1979 SSC Veteran Points: 211 More actions · Answer 11

Here are the triggers.

USE [Assets_GMSC_Prod]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[AREG_GMSC_Bin_Ins]

ON [dbo].[AREG_GMSC]

AFTER INSERT,UPDATE

AS

IF EXISTS (SELECT * FROM INSERTED WHERE FEATURE IN ('Bin'))

BEGIN

SET NOCOUNT ON;

DECLARE @ERROR_COMP_ID varchar(100)

SET @ERROR_COMP_ID = 'Error on Insert '

BEGIN TRY

UPDATE p

SET PRECINCT = (Case When

p.Geometry_SPA.STWithin(b.Geometry_SPA) = 1 then 'SP' Else 'SL' End)

FROM vw_bin p

left join [Infrastructure].[dbo].[SIM_Shopping_Precinct] b

ON p.Geometry_SPA.STWithin(b.Geometry_SPA) = 1

WHERE p.GMSC_Key in (SELECT GMSC_Key FROM INSERTED)

END TRY

BEGIN CATCH

SET @ERROR_COMP_ID+= CONVERT(varchar(100),@@ERROR)

PRINT @ERROR_COMP_ID

ROLLBACK TRANSACTION

END CATCH

END

GO

USE [Assets_GMSC_Prod]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[AREG_GMSC_Field_Ins]

ON [dbo].[AREG_GMSC]

AFTER INSERT

AS

BEGIN

SET NOCOUNT ON;

DECLARE @ERROR_COMP_ID varchar(100)

SET @ERROR_COMP_ID = 'Error on Insert '

BEGIN TRY

IF NOT EXISTS (SELECT * FROM INSERTED

WHERE ([BIT_7] IS NULL OR [BIT_7] = 0) AND ([BIT_8] IS NULL OR [BIT_8] = 0) AND ([BIT_9] IS NULL OR [BIT_9] = 0) AND ([BIT_10] IS NULL OR [BIT_10] = 0) AND ([BIT_11] IS NULL OR [BIT_11] = 0) )

INSERT INTO [tbl_Asset_Inspection] ( [GMSC_Key],[ID_Counter] ,[Inspection_Date] ,[Inspected_By],[Inspection_Comments] ,[Defect_Code],[Defect_Comment],[Merit_ID],[Unacceptable_Standard],[Maintained_Inappropriately],[Utilised_Inappropriately],[ConditionRate_8or_Above],[BeyondLife_ofType],[Outside_Intervention_Lvls] ,[Reactive_Inspection],[Programmed_Inspection],[Capital_Inspection],[Condition_Inspection],[Audit_Inspection],[Physical_Condition],[Functionality],[Compliance],[Safety_Liability],[Serviceability],[Level_of_Defect],[Useful_Life]

)

SELECT [GMSC_Key],[ID_COUNTER],[DATEINSP],[INSPECTOR] ,[VARCHAR_1] ,[VARCHAR_2],[VARCHAR_3],[VARCHAR_4],[BIT_1],[BIT_2],[BIT_3],[BIT_4],[BIT_5],[BIT_6],[BIT_7],[BIT_8],[BIT_9],[BIT_10],[BIT_11],[INT_1],[INT_2],[INT_3],[INT_4],[INT_5],[INT_6],[INT_7]

FROM INSERTED

END TRY

BEGIN CATCH

SET @ERROR_COMP_ID+= CONVERT(varchar(100),@@ERROR)

PRINT @ERROR_COMP_ID

ROLLBACK TRANSACTION

END CATCH

END

GO

USE [Assets_GMSC_Prod]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[AREG_GMSC_Field_Upd]

ON [dbo].[AREG_GMSC]

AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

DECLARE @ERROR_COMP_ID varchar(100)

SET @ERROR_COMP_ID = 'Error on Update '

BEGIN TRY

IF NOT EXISTS (SELECT * FROM INSERTED

WHERE ([BIT_7] IS NULL OR [BIT_7] = 0) AND ([BIT_8] IS NULL OR [BIT_8] = 0) AND ([BIT_9] IS NULL OR [BIT_9] = 0) AND ([BIT_10] IS NULL OR [BIT_10] = 0) AND ([BIT_11] IS NULL OR [BIT_11] = 0) )

INSERT INTO [tbl_Asset_Inspection] ( [GMSC_Key],[ID_Counter] ,[Inspection_Date] ,[Inspected_By],[Inspection_Comments] ,[Defect_Code],[Defect_Comment],[Merit_ID],[Unacceptable_Standard],[Maintained_Inappropriately],[Utilised_Inappropriately],[ConditionRate_8or_Above],[BeyondLife_ofType],[Outside_Intervention_Lvls] ,[Reactive_Inspection],[Programmed_Inspection],[Capital_Inspection],[Condition_Inspection],[Audit_Inspection],[Physical_Condition],[Functionality],[Compliance],[Safety_Liability],[Serviceability],[Level_of_Defect],[Useful_Life]

)

SELECT [GMSC_Key],[ID_COUNTER],[DATEINSP],[INSPECTOR] ,[VARCHAR_1] ,[VARCHAR_2],[VARCHAR_3],[VARCHAR_4],[BIT_1],[BIT_2],[BIT_3],[BIT_4],[BIT_5],[BIT_6],[BIT_7],[BIT_8],[BIT_9],[BIT_10],[BIT_11],[INT_1],[INT_2],[INT_3],[INT_4],[INT_5],[INT_6],[INT_7]

FROM INSERTED

END TRY

BEGIN CATCH

SET @ERROR_COMP_ID+= CONVERT(varchar(100),@@ERROR)

PRINT @ERROR_COMP_ID

ROLLBACK TRANSACTION

END CATCH

END

GO

USE [Assets_GMSC_Prod]

GO

/****** Object: Trigger [dbo].[AREG_GMSC_Geometry_SPA_ins] Script Date: 18/09/2017 9:02:20 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[AREG_GMSC_Geometry_SPA_ins] ON [dbo].[AREG_GMSC] AFTER INSERT AS BEGIN SET NOCOUNT ON; IF EXISTS (SELECT NULL FROM INSERTED WHERE INSERTED.[Geometry_SPA] IS NULL AND INSERTED.[Geometry] IS NOT NULL) BEGIN RAISERROR ('Unsupported. Cannot specify value for GDO column only, native column value must also be provided.', 0, 1) ROLLBACK TRANSACTION END END

GO

USE [Assets_GMSC_Prod]

GO

/****** Object: Trigger [dbo].[AREG_GMSC_Geometry_SPA_upd] Script Date: 18/09/2017 9:02:32 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[AREG_GMSC_Geometry_SPA_upd] ON [dbo].[AREG_GMSC] AFTER UPDATE AS BEGIN SET NOCOUNT ON; IF UPDATE([Geometry_SPA]) BEGIN IF NOT UPDATE([Geometry]) BEGIN UPDATE [AREG_GMSC] SET [Geometry] = NULL WHERE EXISTS (SELECT NULL FROM INSERTED WHERE INSERTED.[GMSC_Key] = [AREG_GMSC].[GMSC_Key]) END END ELSE IF UPDATE([Geometry]) BEGIN RAISERROR ('Unsupported. Cannot specify value for GDO column only, native column value must also be provided.', 0, 1) ROLLBACK TRANSACTION END END

GO

USE [Assets_GMSC_Prod]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[Bin_BBQ_update_ini]

ON [dbo].[AREG_GMSC]

AFTER INSERT,UPDATE

AS

IF EXISTS (SELECT * FROM inserted WHERE FEATURE IN ('Bin','Barbeque','Bin Cage','Facility Bin'))

BEGIN

SET NOCOUNT ON;

INSERT INTO [Assets_GMSC_Prod].[dbo].[tbl_Bin_BBQ_History]

(ADDRESS1, ADDRESS2, ADDRESS3, ASSET_DESCRIPTION, ASSET_NAME, ASSET_PROCESS_ID,

BIT_1, BIT_11, BIT_12, BIT_13, BIT_14, BIT_15, COMM1, COMM2 , COMM3, COMM4, COMM5, COMMENT,

CONDITION, CONTROL, DATE_1 , DATEINSP, DATEINST, DISPOSAL_DATE, DISPOSAL_JOBNO,

DISPOSED, FEATURE, Geometry, Geometry_SPA, GMSC_Key, HEIGHT, ID_COUNTER,

INSPECTION_STATUS, INSPECTOR, INT_1, INT_2, INT_3, INT_5, INT_6, INT_10, INT_8, INT_4, JOBNO, LOCALITY,

MAPREF, MATERIAL, MPSNO, OLD_ID_COUNTER, PHOTOCODE, STATUS, STATUS_COMMENT,

TEXT11, TEXT21, TEXT31, TEXT1, TEXT2, TEXT3, TEXT4, TEXT5, TEXT6, Txn_Date, TYPE, TYPE2, TYPE3,

VARCHAR_7, VARCHAR_8, VER_COMM)

SELECT p.ADDRESS1, p.ADDRESS2, p.ADDRESS3, p.ASSET_DESCRIPTION, p.ASSET_NAME, p.ASSET_PROCESS_ID,

p.BIT_1, p.BIT_11, p.BIT_12, p.BIT_13, p.BIT_14, p.BIT_15, p.COMM1, p.COMM2 , p.COMM3, p.COMM4, p.COMM5, p.COMMENT,

p.CONDITION, p.CONTROL, p.DATE_1 , p.DATEINSP, p.DATEINST, p.DISPOSAL_DATE, p.DISPOSAL_JOBNO,

p.DISPOSED, p.FEATURE, p.Geometry, p.Geometry_SPA, p.GMSC_Key, p.HEIGHT, p.ID_COUNTER,

p.INSPECTION_STATUS, p.INSPECTOR, p.INT_1, p.INT_2, p.INT_3, p.INT_5, p.INT_6, p.INT_10, p.INT_8, p.INT_4, p.JOBNO, p.LOCALITY,

p.MAPREF, p.MATERIAL, p.MPSNO, p.OLD_ID_COUNTER, p.PHOTOCODE, p.STATUS, p.STATUS_COMMENT,

p.TEXT11, p.TEXT21, p.TEXT31, p.TEXT1, p.TEXT2, p.TEXT3, p.TEXT4, p.TEXT5, p.TEXT6, p.Txn_Date, p.TYPE, p.TYPE2, p.TYPE3,

p.VARCHAR_7, p.VARCHAR_8, p.VER_COMM

FROM AREG_GMSC as p

LEFT OUTER JOIN tbl_Bin_BBQ_History as e

ON e.GMSC_Key = p.GMSC_Key

where p.Txn_Date is not null and p.FEATURE in ('Bin','Barbeque','Bin Cage','Facility Bin') and e.GMSC_Key is null

declare @var1 datetime;

select @var1 = MAX(Txn_Date)--,

from [Assets_GMSC_Prod].[dbo].[tbl_Bin_BBQ_History]

--***********************--