March 28, 2012 at 4:06 pm
Alright, that is, officially, nasty code. The dynamic column/values list build avoids the possibilities of future schema change, but you haven't dealt, I don't believe, with all possible data types that can be used. You're also relying on a permanent index position of a table in a collection which just rubs me horribly the wrong way from an assurity standpoint.
That being a personal gripe and not necessary here because of some success in a schema that doesn't constantly change is a different story.
The fact that you're gapping on the @@IDENTITY values that are failing your secondary check shows that the insert into statement is not syntactically inaccurate. It might fail for a hundred other reasons, but it doesn't fail parsing.
Doing a Dirty Read (NOLOCK) could easily miss 0.0001% of records due to page splits. You need to do repeatable read checks to find the row with 100% assurance you'll locate it at all times it ever exists. However, you mention you've gone in multiple times and the record is constantly missing, so that's most likely not the problem, either, as long as you've confirmed this with an assured read, which it sounds like you have.
So, previous discussion from Lynn and Jared covered and your answers included...
Some of this looks like Entity Framework, are you standing your code on top of that or some other ORM?
From what I read, it sounds like you've confirmed there are both no data constraints on this table and it uses no other tables for foreign key/reference constraints. This should be doublechecked. If you can pull the full script from the database for XDispTrips table that would help. You'll need to make sure you have indexes and constraints turned on in scripting options in the interface.
I agree with the others. If you're not standing on ORM/EF you want to be using a procedure here. If you are you'll want your team to eventually work out the workarounds to dynamic functionality. It gives you better error control for database level errors during usage time without having to return to the code and round trip again for each corrective measure/test/error containment. There's a few other reasons but that's your best one in this case. Also, it lets your database folks who may eventually modify the schema actually know there's a dependency out there. 🙂
Past that, once schema and functionality are assured, it needs to be explored at an os level to determine if your disk drivers or other problems are being reported at a driver/hardware level and aren't forcing SQL to complain to you in real-time because your query writes to memory, not disk. At least, not immediately except for the log file. Even then it could be disk cache.
If you find any indicators of the above, I'd begin doing a corruption check on the database itself. Can you locate one of your DBAs and find out what the maintenance options being run against your database during downtime windows are? Statistics, DBCheck, that kind of thing? They might be running certain components on backups on other systems since they can be heavy handed and don't necessarily have to be run on the prime server.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 28, 2012 at 4:13 pm
Stephanie Giovannini (3/28/2012)
There are some issues with the VB.NET code.
I'll leave the .NET code discussion to you, Stephanie. I dabble, if someone more experienced with it has arrived I'll step aside on that. 🙂
I did manage to submit a SQL string that would produce the error. The string looked like this:
"BEGIN TRANSACTION INSERT something (col) VALUES (0) SELECT @@IDENTITY ROLLBACK"
That would never succeed. It would always increment and rollback immediately, but you do bring up a good point. The existing code (that's commented out) would need to be much more robust for error controls in-line for it to behave the expected way, and would need to be in the SQL code.
If you're controlling transactions from the app and I'm not understanding that, realize you have just created a worst-case scenario for troubleshooting a SQL problem for a DBA or DB Dev. Application controlled transactions should be shot and buried, dug up 4 days later and left to soak in holy water, then dropped in a volcano.
I didn't see any indicators that you were doing this here, but if you are, I would recommend removing them and allowing your database to handle database transactions. It's quite possible they just never closed and were forced to rollback when the connection was swept, among another hundred reasons you don't want to control transactions from an external source.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 28, 2012 at 4:18 pm
Craig, thank you very much. You gave us a ton of stuff to chew on. The script of the table is below. One thing we did plan to do was to change the primary key on trp_nbr (identity field) to be clustered.
USE [TDS]
GO
/****** Object: Table [dbo].[XDispTrips] Script Date: 03/28/2012 15:13:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[XDispTrips](
[trp_nbr] [int] IDENTITY(1,1) NOT NULL,
[trp_grouped_with] [int] NOT NULL,
[trp_pos_in_group] [tinyint] NOT NULL,
[trp_nbr_in_group] [tinyint] NOT NULL,
[trp_fl_id] [char](1) NOT NULL,
[trp_trip_type] [varchar](2) NOT NULL,
[trp_res_type] [char](1) NOT NULL,
[trp_pay_type] [char](1) NOT NULL,
[trp_share_type] [char](1) NOT NULL,
[trp_general_cmnt] [varchar](255) NOT NULL,
[trp_pkup_loc_id] [varchar](11) NOT NULL,
[trp_pkup_adr_cmnt] [varchar](60) NOT NULL,
[trp_pkup_str_nbr] [int] NOT NULL,
[trp_pkup_str_nbr_suf] [char](1) NOT NULL,
[trp_pkup_str_name] [varchar](40) NOT NULL,
[trp_pkup_apt_room] [varchar](5) NOT NULL,
[trp_pkup_city] [varchar](3) NOT NULL,
[trp_pkup_state] [varchar](2) NOT NULL,
[trp_pkup_zn_nbr] [smallint] NOT NULL,
[trp_pkup_lon] [int] NOT NULL,
[trp_pkup_lat] [int] NOT NULL,
[trp_pkup_map_coord] [varchar](8) NOT NULL,
[trp_pkup_ph_nbr] [varchar](10) NOT NULL,
[trp_pkup_ph_ext] [varchar](5) NOT NULL,
[trp_pass_id] [varchar](11) NOT NULL,
[trp_pass_first_name] [varchar](20) NOT NULL,
[trp_pass_last_name] [varchar](20) NOT NULL,
[trp_dest_loc_id] [varchar](11) NOT NULL,
[trp_dest_adr_cmnt] [varchar](60) NOT NULL,
[trp_dest_str_nbr] [int] NOT NULL,
[trp_dest_str_nbr_suf] [char](1) NOT NULL,
[trp_dest_str_name] [varchar](40) NOT NULL,
[trp_dest_apt_room] [varchar](5) NOT NULL,
[trp_dest_city] [varchar](3) NOT NULL,
[trp_dest_state] [varchar](2) NOT NULL,
[trp_dest_zn_nbr] [smallint] NOT NULL,
[trp_dest_lon] [int] NOT NULL,
[trp_dest_lat] [int] NOT NULL,
[trp_dest_map_coord] [varchar](8) NOT NULL,
[trp_dest_ph_nbr] [varchar](10) NOT NULL,
[trp_dest_ph_ext] [varchar](5) NOT NULL,
[trp_pers_rqst] [char](1) NOT NULL,
[trp_pers_vh_nbr] [smallint] NOT NULL,
[trp_pers_dr_id] [int] NOT NULL,
[trp_vh_attr_flag] [char](1) NOT NULL,
[trp_vh_attr] [varchar](16) NOT NULL,
[trp_dr_attr_flag] [char](1) NOT NULL,
[trp_dr_attr] [char](16) NOT NULL,
[trp_priority] [smallint] NOT NULL,
[trp_priority_status] [smallint] NOT NULL,
[trp_charge_nbr] [varchar](10) NOT NULL,
[trp_requested_by] [varchar](20) NOT NULL,
[trp_person_to_see] [varchar](20) NOT NULL,
[trp_pod_required] [char](1) NOT NULL,
[trp_in_user_id] [smallint] NOT NULL,
[trp_in_dt_tm] [datetime] NOT NULL,
[trp_last_chng_user_id] [smallint] NOT NULL,
[trp_orig_due_dt_tm] [datetime] NOT NULL,
[trp_due_dt_tm] [datetime] NOT NULL,
[trp_dispd_dt_tm] [datetime] NOT NULL,
[trp_on_site_dt_tm] [datetime] NOT NULL,
[trp_pkup_dt_tm] [datetime] NOT NULL,
[trp_close_dt_tm] [datetime] NOT NULL,
[trp_appt_dt_tm] [datetime] NOT NULL,
[trp_cc_approval] [varchar](10) NOT NULL,
[trp_cc_deny_reason] [varchar](50) NOT NULL,
[trp_cc_auth_dt_tm] [datetime] NOT NULL,
[trp_cc_appr_user_id] [smallint] NOT NULL,
[trp_cc_appr_amt] [float] NOT NULL,
[trp_sto_trp_nbr] [int] NOT NULL,
[trp_vh_nbr] [smallint] NOT NULL,
[trp_dr_id] [int] NOT NULL,
[trp_man_disp_user_id] [smallint] NOT NULL,
[trp_odometer_start] [int] NOT NULL,
[trp_odometer_end] [int] NOT NULL,
[trp_cx_client_field1] [varchar](12) NOT NULL,
[trp_cx_client_field2] [varchar](12) NOT NULL,
[trp_cx_client_field3] [varchar](30) NOT NULL,
[trp_cx_client_field4] [varchar](30) NOT NULL,
[trp_cx_client_field5] [varchar](30) NOT NULL,
[trp_cx_client_field6] [varchar](30) NOT NULL,
[trp_status] [varchar](10) NOT NULL,
[trp_est_fare] [float] NOT NULL,
[trp_act_fare] [float] NOT NULL,
[trp_est_miles] [float] NOT NULL,
[trp_act_miles] [float] NOT NULL,
[trp_est_time] [smallint] NOT NULL,
[trp_act_time] [smallint] NOT NULL,
[trp_est_orig_fee_amt] [float] NOT NULL,
[trp_est_mile_amt] [float] NOT NULL,
[trp_est_time_amt] [float] NOT NULL,
[trp_co_pay_amt] [float] NOT NULL,
[trp_agent_to_disp] [varchar](255) NOT NULL,
[trp_outsrc_fl_id] [char](1) NOT NULL,
[trp_outsrc_vh_nbr] [smallint] NOT NULL,
[trp_outsourced] [char](1) NOT NULL,
[trp_resp_party] [char](1) NOT NULL,
[trp_pkup_zip_code] [varchar](5) NOT NULL,
[trp_dest_zip_code] [varchar](5) NOT NULL,
[trp_fixed_rate] [char](1) NOT NULL,
[trp_cc_dcc_nbr] [varchar](100) NOT NULL,
[trp_pkup_leg] [smallint] NOT NULL,
[trp_drop_leg] [smallint] NOT NULL,
[trp_cl_vch_mile_amt] [float] NOT NULL,
[trp_cl_vch_fare_amt] [float] NOT NULL,
[trp_cl_vch_wait_time] [smallint] NOT NULL,
[trp_cl_vch_wait_time_amt] [float] NOT NULL,
[trp_cl_vch_amt1] [float] NOT NULL,
[trp_cl_vch_amt2] [float] NOT NULL,
[trp_cl_vch_amt3] [float] NOT NULL,
[trp_cl_vch_amt4] [float] NOT NULL,
[trp_dr_vch_mile_amt] [float] NOT NULL,
[trp_dr_vch_fare_amt] [float] NOT NULL,
[trp_dr_vch_wait_time_amt] [float] NOT NULL,
[trp_dr_vch_amt1] [float] NOT NULL,
[trp_dr_vch_amt2] [float] NOT NULL,
[trp_dr_vch_amt3] [float] NOT NULL,
[trp_dr_vch_amt4] [float] NOT NULL,
[trp_cc_tip_amt] [float] NOT NULL,
[trp_vip_tag] [varchar](1) NOT NULL,
[trp_nbr_escorts] [smallint] NOT NULL,
[trp_confirm_user_id] [smallint] NOT NULL,
[trp_confirm_dt_tm] [datetime] NOT NULL,
[trp_ext_trp_nbr] [int] NOT NULL,
[trp_ext_co_nbr] [smallint] NOT NULL,
[trp_insrc_trp_nbr] [int] NOT NULL,
[trp_ext_charge_nbr] [varchar](10) NOT NULL,
[trp_charge_desc] [varchar](25) NOT NULL,
[trp_dr_msg] [varchar](36) NOT NULL,
[trp_zn_desc] [varchar](20) NOT NULL,
[trp_pkup_county_code] [smallint] NOT NULL,
[trp_dest_county_code] [smallint] NOT NULL,
[trp_vt_code] [smallint] NOT NULL,
[trp_cl_vch_amt5] [float] NOT NULL,
[trp_cl_vch_amt6] [float] NOT NULL,
[trp_cl_vch_amt7] [float] NOT NULL,
[trp_cl_vch_amt8] [float] NOT NULL,
CONSTRAINT [PK_XDispTrips] PRIMARY KEY NONCLUSTERED
(
[trp_nbr] 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]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Ties to XDispCounties table..' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'XDispTrips', @level2type=N'COLUMN',@level2name=N'trp_pkup_county_code'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Ties to XDispCounties table..' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'XDispTrips', @level2type=N'COLUMN',@level2name=N'trp_dest_county_code'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Ties to VEHTYPE!vt_code This field can be set if the passenger specifically requests a specific type of vehicle.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'XDispTrips', @level2type=N'COLUMN',@level2name=N'trp_vt_code'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Voucher Amount field #5' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'XDispTrips', @level2type=N'COLUMN',@level2name=N'trp_cl_vch_amt5'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Voucher Amount field #6' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'XDispTrips', @level2type=N'COLUMN',@level2name=N'trp_cl_vch_amt6'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Voucher Amount field #7' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'XDispTrips', @level2type=N'COLUMN',@level2name=N'trp_cl_vch_amt7'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Voucher Amount field #8' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'XDispTrips', @level2type=N'COLUMN',@level2name=N'trp_cl_vch_amt8'
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_grouped_with] DEFAULT (0) FOR [trp_grouped_with]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_pos_in_group] DEFAULT (0) FOR [trp_pos_in_group]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_nbr_in_group] DEFAULT (0) FOR [trp_nbr_in_group]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_fleet] DEFAULT (' ') FOR [trp_fl_id]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_extended_type] DEFAULT (' ') FOR [trp_trip_type]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_res_type] DEFAULT (' ') FOR [trp_res_type]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_pay_type] DEFAULT (' ') FOR [trp_pay_type]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_share_type] DEFAULT (' ') FOR [trp_share_type]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_general_cmnt] DEFAULT (' ') FOR [trp_general_cmnt]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_pkup_loc_id] DEFAULT (' ') FOR [trp_pkup_loc_id]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_pckup_adr_cmnt] DEFAULT (' ') FOR [trp_pkup_adr_cmnt]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_pkup_str_nbr] DEFAULT (0) FOR [trp_pkup_str_nbr]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_pkup_str_nbr_suf] DEFAULT (' ') FOR [trp_pkup_str_nbr_suf]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_pckup_str_name] DEFAULT (' ') FOR [trp_pkup_str_name]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_pckup_apt_room] DEFAULT (' ') FOR [trp_pkup_apt_room]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_pckup_city] DEFAULT (' ') FOR [trp_pkup_city]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_pkup_state] DEFAULT (' ') FOR [trp_pkup_state]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_pckup_zone] DEFAULT (0) FOR [trp_pkup_zn_nbr]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_pkup_lon] DEFAULT (0) FOR [trp_pkup_lon]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_pkup_lat] DEFAULT (0) FOR [trp_pkup_lat]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_pkup_pmap] DEFAULT (' ') FOR [trp_pkup_map_coord]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_phone_nbr] DEFAULT (' ') FOR [trp_pkup_ph_nbr]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_phone_ext] DEFAULT (' ') FOR [trp_pkup_ph_ext]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_pass_id] DEFAULT (' ') FOR [trp_pass_id]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_pass_first_name] DEFAULT (' ') FOR [trp_pass_first_name]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_pass_last_name_1] DEFAULT (' ') FOR [trp_pass_last_name]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_dest_loc_id] DEFAULT (' ') FOR [trp_dest_loc_id]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_dest_adr_cmnt] DEFAULT (' ') FOR [trp_dest_adr_cmnt]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_dest_str_nbr] DEFAULT (' ') FOR [trp_dest_str_nbr]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_tr_dest_str_nbr_suf] DEFAULT (' ') FOR [trp_dest_str_nbr_suf]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_dest_str_name] DEFAULT (' ') FOR [trp_dest_str_name]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_dest_apt_room] DEFAULT (' ') FOR [trp_dest_apt_room]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_dest_city] DEFAULT (' ') FOR [trp_dest_city]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_dest_state] DEFAULT (' ') FOR [trp_dest_state]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_dest_zone] DEFAULT (0) FOR [trp_dest_zn_nbr]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_dest_lon] DEFAULT (0) FOR [trp_dest_lon]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_dest_lat] DEFAULT (0) FOR [trp_dest_lat]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_dest_map_coord] DEFAULT (' ') FOR [trp_dest_map_coord]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_dest_ph_nbr] DEFAULT (' ') FOR [trp_dest_ph_nbr]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_dest_ph_ext] DEFAULT (' ') FOR [trp_dest_ph_ext]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_pers_rqst] DEFAULT (' ') FOR [trp_pers_rqst]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_personal_veh] DEFAULT (0) FOR [trp_pers_vh_nbr]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_pers_dr_id] DEFAULT (0) FOR [trp_pers_dr_id]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_veh_attr_flag] DEFAULT ('N') FOR [trp_vh_attr_flag]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_veh_attr] DEFAULT (' ') FOR [trp_vh_attr]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_drv_attr_flag] DEFAULT ('N') FOR [trp_dr_attr_flag]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_drv_attr] DEFAULT (' ') FOR [trp_dr_attr]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_priority] DEFAULT (0) FOR [trp_priority]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_priority_status] DEFAULT (0) FOR [trp_priority_status]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_charge_nbr] DEFAULT (' ') FOR [trp_charge_nbr]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_requested_by] DEFAULT (' ') FOR [trp_requested_by]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_person_to_see] DEFAULT (' ') FOR [trp_person_to_see]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_pod_required] DEFAULT ('N') FOR [trp_pod_required]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_in_sm_id] DEFAULT (0) FOR [trp_in_user_id]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_in_dt_tm] DEFAULT ('1/1/1900 12:00:00 AM') FOR [trp_in_dt_tm]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_last_changed_by] DEFAULT (0) FOR [trp_last_chng_user_id]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trip_orig_due_dt_tm] DEFAULT ('1/1/1900 12:00:00 AM') FOR [trp_orig_due_dt_tm]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_due_dt_tm] DEFAULT ('1/1/1900 12:00:00 AM') FOR [trp_due_dt_tm]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_dispd_dt_tm] DEFAULT ('1/1/1900 12:00:00 AM') FOR [trp_dispd_dt_tm]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_on_site_dt_tm] DEFAULT ('1/1/1900 12:00:00AM') FOR [trp_on_site_dt_tm]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_pckup_dt_tm] DEFAULT ('1/1/1900 12:00:00 AM') FOR [trp_pkup_dt_tm]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_close_dt_tm] DEFAULT ('1/1/1900 12:00:00 AM') FOR [trp_close_dt_tm]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_appt_dt_tm] DEFAULT ('12/31/2069 23:59:59') FOR [trp_appt_dt_tm]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_approve_code] DEFAULT (' ') FOR [trp_cc_approval]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cc_deny_reason] DEFAULT (' ') FOR [trp_cc_deny_reason]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cc_auth_dt_tm] DEFAULT ('1/1/1900 12:00:00AM') FOR [trp_cc_auth_dt_tm]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cc_appr_sm_id] DEFAULT (0) FOR [trp_cc_appr_user_id]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cc_appr_amt] DEFAULT (0.0) FOR [trp_cc_appr_amt]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_subsc_call_nbr] DEFAULT (0) FOR [trp_sto_trp_nbr]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_veh_nbr] DEFAULT (0) FOR [trp_vh_nbr]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_drv_id] DEFAULT (0) FOR [trp_dr_id]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_dispatched_by] DEFAULT (0) FOR [trp_man_disp_user_id]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_odometer_start] DEFAULT (0) FOR [trp_odometer_start]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_odometer_end] DEFAULT (0) FOR [trp_odometer_end]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_ac_addt1_info1] DEFAULT (' ') FOR [trp_cx_client_field1]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_ac_addt1_info2] DEFAULT (' ') FOR [trp_cx_client_field2]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cx_client_field3] DEFAULT (' ') FOR [trp_cx_client_field3]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cx_client_field4] DEFAULT (' ') FOR [trp_cx_client_field4]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cx_client_field5] DEFAULT (' ') FOR [trp_cx_client_field5]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cx_client_field6] DEFAULT (' ') FOR [trp_cx_client_field6]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_status] DEFAULT (' ') FOR [trp_status]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_est_fare] DEFAULT (0.0) FOR [trp_est_fare]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_act_fare] DEFAULT (0.0) FOR [trp_act_fare]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_cl_est_miles] DEFAULT (0.0) FOR [trp_est_miles]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_act_miles] DEFAULT (0.0) FOR [trp_act_miles]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_est_time] DEFAULT (0) FOR [trp_est_time]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_act_time] DEFAULT (0) FOR [trp_act_time]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_orig_fee] DEFAULT (0.0) FOR [trp_est_orig_fee_amt]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_mile_amt] DEFAULT (0.0) FOR [trp_est_mile_amt]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_est_time_amt] DEFAULT (0.0) FOR [trp_est_time_amt]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_co_pay_amt] DEFAULT (0.0) FOR [trp_co_pay_amt]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_agent_to_disp] DEFAULT (' ') FOR [trp_agent_to_disp]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_outsrc_fleet] DEFAULT (' ') FOR [trp_outsrc_fl_id]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_outsrc_vh_nbr] DEFAULT (0) FOR [trp_outsrc_vh_nbr]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_outsourced] DEFAULT ('N') FOR [trp_outsourced]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_resp_party] DEFAULT ('C') FOR [trp_resp_party]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_pkup_zip_code] DEFAULT (' ') FOR [trp_pkup_zip_code]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_dest_zip_code] DEFAULT (' ') FOR [trp_dest_zip_code]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_fixed_rate] DEFAULT ('N') FOR [trp_fixed_rate]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF__XDISPTRIP__trp_c__1E56AEDC] DEFAULT (' ') FOR [trp_cc_dcc_nbr]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_pkup_leg] DEFAULT (0) FOR [trp_pkup_leg]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_drop_leg] DEFAULT (0) FOR [trp_drop_leg]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cl_vch_mile_amt] DEFAULT (0) FOR [trp_cl_vch_mile_amt]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cl_vch_fare_amt] DEFAULT (0) FOR [trp_cl_vch_fare_amt]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cl_vch_wait_time] DEFAULT (0) FOR [trp_cl_vch_wait_time]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cl_vch_wait_time_amt] DEFAULT (0) FOR [trp_cl_vch_wait_time_amt]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cl_vch_amt1] DEFAULT (0) FOR [trp_cl_vch_amt1]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cl_vch_amt2] DEFAULT (0) FOR [trp_cl_vch_amt2]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cl_vch_amt3] DEFAULT (0) FOR [trp_cl_vch_amt3]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cl_vch_amt4] DEFAULT (0) FOR [trp_cl_vch_amt4]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_dr_vch_mile_amount] DEFAULT (0) FOR [trp_dr_vch_mile_amt]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_dr_vch_fare_amount] DEFAULT (0) FOR [trp_dr_vch_fare_amt]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_dr_vch_wait_time_amt] DEFAULT (0) FOR [trp_dr_vch_wait_time_amt]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_dr_vch_amt1] DEFAULT (0) FOR [trp_dr_vch_amt1]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_dr_vch_amt2] DEFAULT (0) FOR [trp_dr_vch_amt2]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_dr_vch_amt3] DEFAULT (0) FOR [trp_dr_vch_amt3]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_dr_vch_amt4] DEFAULT (0) FOR [trp_dr_vch_amt4]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cc_tip_amt] DEFAULT (0) FOR [trp_cc_tip_amt]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_vip_tag] DEFAULT ('N') FOR [trp_vip_tag]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_nbr_escorts] DEFAULT (0) FOR [trp_nbr_escorts]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_confirm_user_id] DEFAULT (0) FOR [trp_confirm_user_id]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_confirm_dt_tm] DEFAULT ('1/1/1900') FOR [trp_confirm_dt_tm]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_ext_trp_nbr] DEFAULT (0) FOR [trp_ext_trp_nbr]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_ext_co_nbr] DEFAULT (0) FOR [trp_ext_co_nbr]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_insrc_trp_nbr] DEFAULT (0) FOR [trp_insrc_trp_nbr]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_ext_charge_nbr] DEFAULT (' ') FOR [trp_ext_charge_nbr]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF__XDispTrip__trp_c__797DC0D8] DEFAULT (' ') FOR [trp_charge_desc]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF__XDispTrip__trp_d__7A71E511] DEFAULT (' ') FOR [trp_dr_msg]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF__XDispTrip__trp_z__7B66094A] DEFAULT (' ') FOR [trp_zn_desc]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF__XDispTrip__trp_p__19E06EC9] DEFAULT (0) FOR [trp_pkup_county_code]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF__XDispTrip__trp_d__1AD49302] DEFAULT (0) FOR [trp_dest_county_code]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF__XDispTrip__trp_v__6B657702] DEFAULT ((-1)) FOR [trp_vt_code]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cl_vch_amt5] DEFAULT (0) FOR [trp_cl_vch_amt5]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cl_vch_amt6] DEFAULT (0) FOR [trp_cl_vch_amt6]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cl_vch_amt7] DEFAULT (0) FOR [trp_cl_vch_amt7]
GO
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [DF_XDispTrips_trp_cl_vch_amt8] DEFAULT (0) FOR [trp_cl_vch_amt8]
GO
USE [TDS]
GO
/****** Object: Index [cl_indx1] Script Date: 03/28/2012 15:16:11 ******/
CREATE NONCLUSTERED INDEX [cl_indx1] ON [dbo].[XDispTrips]
(
[trp_due_dt_tm] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
USE [TDS]
GO
/****** Object: Index [cl_indx10] Script Date: 03/28/2012 15:16:28 ******/
CREATE NONCLUSTERED INDEX [cl_indx10] ON [dbo].[XDispTrips]
(
[trp_in_user_id] ASC,
[trp_in_dt_tm] ASC
)
INCLUDE ( [trp_fl_id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
USE [TDS]
GO
/****** Object: Index [cl_indx2] Script Date: 03/28/2012 15:16:44 ******/
CREATE NONCLUSTERED INDEX [cl_indx2] ON [dbo].[XDispTrips]
(
[trp_ext_trp_nbr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
USE [TDS]
GO
/****** Object: Index [cl_indx4] Script Date: 03/28/2012 15:17:08 ******/
CREATE NONCLUSTERED INDEX [cl_indx4] ON [dbo].[XDispTrips]
(
[trp_charge_nbr] ASC,
[trp_due_dt_tm] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
USE [TDS]
GO
/****** Object: Index [cl_indx7] Script Date: 03/28/2012 15:17:20 ******/
CREATE NONCLUSTERED INDEX [cl_indx7] ON [dbo].[XDispTrips]
(
[trp_vh_nbr] ASC,
[trp_due_dt_tm] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
USE [TDS]
GO
/****** Object: Index [cl_indx8] Script Date: 03/28/2012 15:17:31 ******/
CREATE NONCLUSTERED INDEX [cl_indx8] ON [dbo].[XDispTrips]
(
[trp_fl_id] ASC,
[trp_due_dt_tm] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
USE [TDS]
GO
/****** Object: Index [cl_indx9] Script Date: 03/28/2012 15:17:43 ******/
CREATE NONCLUSTERED INDEX [cl_indx9] ON [dbo].[XDispTrips]
(
[trp_fl_id] ASC,
[trp_status] ASC,
[trp_pkup_zn_nbr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
USE [TDS]
GO
/****** Object: Index [PK_XDispTrips] Script Date: 03/28/2012 15:17:56 ******/
ALTER TABLE [dbo].[XDispTrips] ADD CONSTRAINT [PK_XDispTrips] PRIMARY KEY NONCLUSTERED
(
[trp_nbr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
March 28, 2012 at 4:26 pm
i'm already leaning towards a not null constraint violation; every column in the table is defined as not null, so if the entity framework creates a command with a null param, null gets sent...default constraints are for when the column is not referenced in the insert statement.
is it alreayd handling values with single quotes in them correctly/ so if i send O'Brian, it doesn't crash the statement constructed?
Lowell
March 28, 2012 at 4:41 pm
I would be surprised that NULLs are a problem but we will see that in our trace of the INSERT statement that results in missing record. Yes, "O'Brien" is handled just fine by our code - I just added a transaction for O'Brien and it saved just fine. I guess our code is good for something... 🙂
March 28, 2012 at 4:42 pm
eshulman (3/28/2012)
Craig, thank you very much. You gave us a ton of stuff to chew on. The script of the table is below. One thing we did plan to do was to change the primary key on trp_nbr (identity field) to be clustered.
Did the schema review.
Heap with a dozen or so generic indexes, all nonclustered. All fields (except Identity) are given a default (unless I'm blind, I went through them in inches), majority are 0 or a single blank space (not a 0 length string). All fields are NOT NULL. Table has no external references and does not have restrictive constraints.
Alright, splits rarely if ever occur in a heap but I recommend getting yourself, as you mentioned, a solid clustered index either way. You're hotspotting this way too if memory serves but I'll have to look it up.
Most likely failure cause: Values too large for the SMALLINT/VARCHAR(10)(25)(etc) that exist and you're getting truncation or overflow errors.
Does this table have an audit-structure built into it somehow, even somewhere in the N-Tier? I'm curious to see if the missing records got to the audit and what they have to say about this data, and if we could rebuild the exact statement from that and see what results.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 28, 2012 at 5:05 pm
Thank you Craig - the thing that is really bizarre is that there are instances where our software tells the user that save failed and to save again - the save succeeds the second time around all the time - which would not be the case if some values were wrong.
Anyway, we are adding code to capture the offending INSERT statement AND the subsequent succesful INSERT statement that follows it to see what is going on.
thank you very much for your time...
March 28, 2012 at 5:35 pm
eshulman (3/28/2012)
Thank you Craig - the thing that is really bizarre is that there are instances where our software tells the user that save failed and to save again - the save succeeds the second time around all the time - which would not be the case if some values were wrong.
Agreed, but those are probably for different errors. Connection failures, timeouts waiting for locks, things like that. To really thoroughly go through this I'd pretty much have to be onsite getting into error logs all over the place and discussing design philosophy with the original coders.
Anyway, we are adding code to capture the offending INSERT statement AND the subsequent succesful INSERT statement that follows it to see what is going on.
That will help tremendously. If nothing else, it will help to show there are no schema driven errors causing any issues. With that out of the way you can concentrate on the esoteric possibilities like bad RAM, disk driver issues, connections not using transaction correctly, and other "Who'd think to look at THAT?!" type of issues.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 30, 2012 at 10:50 am
Ok, we captured both the failed and the immediately following successful insert statements and rediously compared each field and each value and the two are absolutely identical.
Since last post we have changed the compatibility from 2000 to 2008 and switched cluster nodes. We have also made the primary identity key on this table clustered and rebuilt all the indexes. It appears that we are having the same issue in a couple other relatively high volume tables.
Somebody along the line mentioned DTC and it sometimes deciding to roll transactions back. Is there a way to track that somehow? We dont even need DTC as we do not have any distributed databases but when we tried to turn it off (about a week ago or so) it shut down SQL Server agent (which we do need) and a couple days after we turned it off our SQL Server froze with DTC errors in the log so we switched nodes and turned DTC back on.
03/30/12 08:54:32A|-1|6156:SaveError. TDS. To prevent STATUS RED issue. Missing iRecNbr=18685204; UserId=1038|1038|00-1A-A0-07-5F-A2|C:\PROGRAM FILES\NTS_CASH\|TDS v1.0|TableClass.Save.Steve||
03/30/12 08:54:32A|-1|6156:SaveError. FAILED INSERT=INSERT INTO XDispTrips (trp_grouped_with, trp_pos_in_group, trp_nbr_in_group, trp_fl_id, trp_trip_type, trp_res_type, trp_pay_type, trp_share_type, trp_general_cmnt, trp_pkup_loc_id, trp_pkup_adr_cmnt, trp_pkup_str_nbr, trp_pkup_str_nbr_suf, trp_pkup_str_name, trp_pkup_apt_room, trp_pkup_city, trp_pkup_state, trp_pkup_zn_nbr, trp_pkup_lon, trp_pkup_lat, trp_pkup_map_coord, trp_pkup_ph_nbr, trp_pkup_ph_ext, trp_pass_id, trp_pass_first_name, trp_pass_last_name, trp_dest_loc_id, trp_dest_adr_cmnt, trp_dest_str_nbr, trp_dest_str_nbr_suf, trp_dest_str_name, trp_dest_apt_room, trp_dest_city, trp_dest_state, trp_dest_zn_nbr, trp_dest_lon, trp_dest_lat, trp_dest_map_coord, trp_dest_ph_nbr, trp_dest_ph_ext, trp_pers_rqst, trp_pers_vh_nbr, trp_pers_dr_id, trp_vh_attr_flag, trp_vh_attr, trp_dr_attr_flag, trp_dr_attr, trp_priority, trp_charge_nbr, trp_requested_by, trp_person_to_see, trp_pod_required, trp_in_user_id, trp_in_dt_tm, trp_last_chng_user_id, trp_orig_due_dt_tm, trp_due_dt_tm, trp_dispd_dt_tm, trp_on_site_dt_tm, trp_pkup_dt_tm, trp_close_dt_tm, trp_appt_dt_tm, trp_cc_approval, trp_cc_deny_reason, trp_cc_auth_dt_tm, trp_cc_appr_user_id, trp_cc_appr_amt, trp_sto_trp_nbr, trp_vh_nbr, trp_dr_id, trp_man_disp_user_id, trp_odometer_start, trp_odometer_end, trp_cx_client_field1, trp_cx_client_field2, trp_cx_client_field3, trp_cx_client_field4, trp_cx_client_field5, trp_cx_client_field6, trp_est_fare, trp_act_fare, trp_est_miles, trp_act_miles, trp_est_time, trp_act_time, trp_est_orig_fee_amt, trp_est_mile_amt, trp_est_time_amt, trp_co_pay_amt, trp_agent_to_disp, trp_outsrc_fl_id, trp_outsrc_vh_nbr, trp_outsourced, trp_resp_party, trp_pkup_zip_code, trp_dest_zip_code, trp_fixed_rate, trp_cc_dcc_nbr, trp_pkup_leg, trp_drop_leg, trp_cl_vch_mile_amt, trp_cl_vch_fare_amt, trp_cl_vch_wait_time, trp_cl_vch_wait_time_amt, trp_cl_vch_amt1, trp_cl_vch_amt2, trp_cl_vch_amt3, trp_cl_vch_amt4, trp_dr_vch_mile_amt, trp_dr_vch_fare_amt, trp_dr_vch_wait_time_amt, trp_dr_vch_amt1, trp_dr_vch_amt2, trp_dr_vch_amt3, trp_dr_vch_amt4, trp_cc_tip_amt, trp_vip_tag, trp_nbr_escorts, trp_confirm_user_id, trp_confirm_dt_tm, trp_ext_trp_nbr, trp_ext_co_nbr, trp_insrc_trp_nbr, trp_ext_charge_nbr, trp_charge_desc, trp_dr_msg, trp_zn_desc, trp_pkup_county_code, trp_dest_county_code, trp_vt_code, trp_cl_vch_amt5, trp_cl_vch_amt6, trp_cl_vch_amt7, trp_cl_vch_amt8) VALUES (0, 0, 0, 'N', 'P', '', '', '', 'M/ENT', '8113', 'WAFFLE HOUSE', 816, '', 'MURFREESBORO PIKE', '', 'NAS', 'TN', 615, -86723759, 36135025, '', '6159744263', '', '', 'PIER', '', '', '', 0, '', '', '', '', '', 0, 0, 0, '', '', '', '', 0, 0, 'N', 'NNNNNNNNNNNNNNNN', 'N', 'NNNNNNNNNNNNNNNN', 25, '', '', '', 'N', 1038, '03/30/2012 10:54:31', 0, '03/30/2012 10:54:00', '03/30/2012 10:54:00', '01/01/1900 00:00:00', '01/01/1900 00:00:00', '01/01/1900 00:00:00', '01/01/1900 00:00:00', '01/01/1900 00:00:00', '', '', '01/01/1900 00:00:00', 0, 0, 0, 0, 0, 0, 0, 0, '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '', 'N', 0, '', 'P', '37217', '', 'N', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 'N', 0, 0, '01/01/1900 00:00:00', 0, 0, 0, '', '', '', 'NASHVILLE-37217', 37, 0, -1, 0, 0, 0, 0) SELECT @@IDENTITY|1038|00-1A-A0-07-5F-A2|C:\PROGRAM FILES\NTS_CASH\|TDS v1.0|TableClass.Save.Steve||
03/30/12 08:54:35A|-1|6156:SaveError. SUCCESS INSERT=INSERT INTO XDispTrips(trp_grouped_with, trp_pos_in_group, trp_nbr_in_group, trp_fl_id, trp_trip_type, trp_res_type, trp_pay_type, trp_share_type, trp_general_cmnt, trp_pkup_loc_id, trp_pkup_adr_cmnt, trp_pkup_str_nbr, trp_pkup_str_nbr_suf, trp_pkup_str_name, trp_pkup_apt_room, trp_pkup_city, trp_pkup_state, trp_pkup_zn_nbr, trp_pkup_lon, trp_pkup_lat, trp_pkup_map_coord, trp_pkup_ph_nbr, trp_pkup_ph_ext, trp_pass_id, trp_pass_first_name, trp_pass_last_name, trp_dest_loc_id, trp_dest_adr_cmnt, trp_dest_str_nbr, trp_dest_str_nbr_suf, trp_dest_str_name, trp_dest_apt_room, trp_dest_city, trp_dest_state, trp_dest_zn_nbr, trp_dest_lon, trp_dest_lat, trp_dest_map_coord, trp_dest_ph_nbr, trp_dest_ph_ext, trp_pers_rqst, trp_pers_vh_nbr, trp_pers_dr_id, trp_vh_attr_flag, trp_vh_attr, trp_dr_attr_flag, trp_dr_attr, trp_priority, trp_charge_nbr, trp_requested_by, trp_person_to_see, trp_pod_required, trp_in_user_id, trp_in_dt_tm, trp_last_chng_user_id, trp_orig_due_dt_tm, trp_due_dt_tm, trp_dispd_dt_tm, trp_on_site_dt_tm, trp_pkup_dt_tm, trp_close_dt_tm, trp_appt_dt_tm, trp_cc_approval, trp_cc_deny_reason, trp_cc_auth_dt_tm, trp_cc_appr_user_id, trp_cc_appr_amt, trp_sto_trp_nbr, trp_vh_nbr, trp_dr_id, trp_man_disp_user_id, trp_odometer_start, trp_odometer_end, trp_cx_client_field1, trp_cx_client_field2, trp_cx_client_field3, trp_cx_client_field4, trp_cx_client_field5, trp_cx_client_field6, trp_est_fare, trp_act_fare, trp_est_miles, trp_act_miles, trp_est_time, trp_act_time, trp_est_orig_fee_amt, trp_est_mile_amt, trp_est_time_amt, trp_co_pay_amt, trp_agent_to_disp, trp_outsrc_fl_id, trp_outsrc_vh_nbr, trp_outsourced, trp_resp_party, trp_pkup_zip_code, trp_dest_zip_code, trp_fixed_rate, trp_cc_dcc_nbr, trp_pkup_leg, trp_drop_leg, trp_cl_vch_mile_amt, trp_cl_vch_fare_amt, trp_cl_vch_wait_time, trp_cl_vch_wait_time_amt, trp_cl_vch_amt1, trp_cl_vch_amt2, trp_cl_vch_amt3, trp_cl_vch_amt4, trp_dr_vch_mile_amt, trp_dr_vch_fare_amt, trp_dr_vch_wait_time_amt, trp_dr_vch_amt1, trp_dr_vch_amt2, trp_dr_vch_amt3, trp_dr_vch_amt4, trp_cc_tip_amt, trp_vip_tag, trp_nbr_escorts, trp_confirm_user_id, trp_confirm_dt_tm, trp_ext_trp_nbr, trp_ext_co_nbr, trp_insrc_trp_nbr, trp_ext_charge_nbr, trp_charge_desc, trp_dr_msg, trp_zn_desc, trp_pkup_county_code, trp_dest_county_code, trp_vt_code, trp_cl_vch_amt5, trp_cl_vch_amt6, trp_cl_vch_amt7, trp_cl_vch_amt8) VALUES (0, 0, 0, 'N', 'P', '', '', '', 'M/ENT', '8113', 'WAFFLE HOUSE', 816, '', 'MURFREESBORO PIKE', '', 'NAS', 'TN', 615, -86723759, 36135025, '', '6159744263', '', '', 'PIER', '', '', '', 0, '', '', '', '', '', 0, 0, 0, '', '', '', '', 0, 0, 'N', 'NNNNNNNNNNNNNNNN', 'N', 'NNNNNNNNNNNNNNNN', 25, '', '', '', 'N', 1038, '03/30/2012 10:54:34', 0, '03/30/2012 10:54:00', '03/30/2012 10:54:00', '01/01/1900 00:00:00', '01/01/1900 00:00:00', '01/01/1900 00:00:00', '01/01/1900 00:00:00', '01/01/1900 00:00:00', '', '', '01/01/1900 00:00:00', 0, 0, 0, 0, 0, 0, 0, 0, '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '', 'N', 0, '', 'P', '37217', '', 'N', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 'N', 0, 0, '01/01/1900 00:00:00', 0, 0, 0, '', '', '', 'NASHVILLE-37217', 37, 0, -1, 0, 0, 0, 0) SELECT @@IDENTITY|1038|00-1A-A0-07-5F-A2|C:\PROGRAM FILES\NTS_CASH\|TDS v1.0|TableClass::Save::Steve||
March 30, 2012 at 2:45 pm
eshulman (3/30/2012)
Ok, we captured both the failed and the immediately following successful insert statements and rediously compared each field and each value and the two are absolutely identical.
Except for a minor difference in a timestamp, and a space after xDISPTrips before the (, I agree with you. Neither of those modifications will make a difference to success.
Since last post we have changed the compatibility from 2000 to 2008 and switched cluster nodes. We have also made the primary identity key on this table clustered and rebuilt all the indexes. It appears that we are having the same issue in a couple other relatively high volume tables.
Now, that's interesting. And there's nothing in the SQL Server logs showing any errors or issues? How about the application logs on the OS on that server/node?
Somebody along the line mentioned DTC and it sometimes deciding to roll transactions back. Is there a way to track that somehow?
Not that I know of, but DTC shouldn't be involved here. It gets involved when the engine opens a transaction (internally occurs immediately before the Insert Statement, and commits immediately after, as an 'implicit transaction') and then needs to go somewhere else for more stuff.
We dont even need DTC as we do not have any distributed databases but when we tried to turn it off (about a week ago or so) it shut down SQL Server agent (which we do need) and a couple days after we turned it off our SQL Server froze with DTC errors in the log so we switched nodes and turned DTC back on.
... Hrm. All these tables are in a single database, correct? Have your DBA's confirmed that CHECKDB shows no issues during your maintenance cycles?
It's not the failure due to timeout or whatnot that concerns me, it's the lack of error reporting. Past that, I'd personally move all these changes down to the SQL Level in Procs and build my own error components in an environment I was more familiar in (SQL, not VB.NET). I'm afraid my lack of familiarity with the calling system will stymie many experts that could help you otherwise.
I personally, because the nearly exact same code is succesful vs. non-successful, would be looking towards communication errors, timeouts, and things of that nature. An error that would actually never reach the SQL Server, thus not coughing up an error from the connection, but one that happened 'quietly' without the connection receiving an internal failure, but one that missed making it altogether.
Have you looked over the error logs on app/OS from the .NET app's home server, or is this a distributed software internally that's not run from a service? Are there any layers/tiers/services between the app and the database that could be reviewed for a lack of error forwarding?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 30, 2012 at 3:13 pm
All tables are in the same database.
Just ran CHECKDB and all is fine.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'TDS'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
We will try the TRY CATCH and see if we can catch the errors.
Thank you for your time.
March 30, 2012 at 3:44 pm
eshulman (3/30/2012)
Thank you for your time.
Not a problem. I'm going to try to call in some VB coding backup here, this went beyond my skillset I fear.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 31, 2012 at 8:10 am
We might have stumbled onto something - found some suspicious transaction definition in our web service declarations. We will try something different on Monday morning and if it works we will post the answer to this riddle.
Thank you again, you have been way generous with our time.
March 31, 2012 at 11:21 am
eshulman (3/31/2012)
We might have stumbled onto something - found some suspicious transaction definition in our web service declarations. We will try something different on Monday morning and if it works we will post the answer to this riddle.Thank you again, you have been way generous with our time.
While you're in there "trying things", I noticed in the two insert statements that you're still using @@IDENTITY. That really does need to be fixed to make the code more bullet proof.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 2, 2012 at 11:54 am
What is your suggestion to fixing the SELECT @@IDENTITY that we are using?
Ok, well, we rolled out the following change in our webservices and so far it is looking good - no lost records caught for the last 5 hours. This might be it.
what we had - '<WebMethod(TransactionOption:=TransactionOption.RequiresNew)> _
what we have now - <WebMethod()>
Found the default…
Property Value
The transaction support of an XML Web service method. The default is Disabled.
Remarks
XML Web service methods can only participate as the root object in a transaction, due to the stateless nature of the HTTP protocol. XML Web service methods can invoke COM objects that participate in the same transaction as the XML Web service method, if the COM object is marked to run within a transaction in the Component Services administrative tool. If an XML Web service method with a TransactionOption property of Required or RequiresNew invokes another XML Web service method with a TransactionOption property of Required or RequiresNew, each XML Web service method participates in its own transaction, because an XML Web service method can only act as the root object in a transaction.
ItemDescription
DisabledIndicates that the XML Web service method does not run within the scope of a transaction. When a request is processed, the XML Web service method is executed without a transaction.
[WebMethod(TransactionOption= TransactionOption.Disabled)]
Viewing 15 posts - 46 through 60 (of 67 total)
You must be logged in to reply to this topic. Login to reply