'Invalid Object Name' with AlphaDAO and SQLServer 2008 when edit date col

[RichCPT]

Most of the time, I get an 'Invalid Object Name' error displayed in my web grid when I try to save changes to a date column.

I turned on sql tracing and depending on which SELECT statement Alpha decides to use after updating the record, it either codes the table name as [dbo.rgRateGroup] or dbo.rgRateGroup

SQL Server2008 R2 SP1, accepts brackets like this: [dbo].[rgRateGroup], not [dbo.rgRateGroup], or no [] if reserved words and spaces are not used.

How can I get A5 to either properly use [] or to not use them at all? Or how can I get SQL to understand A5's syntax?

Alpha Five Version 10.5 Build 4369, System AddIns Build 3712

-Rich Fulham
p.s. The bad statement that A5 is sending to SQL is:

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,
N'@P1 float',
N'SELECT * FROM [dbo.RateGroup] dbo_RateGroup WHERE rgRateGroup_SysID = @P1',
29
select @p1

The good statement that A5 sometimes sends after the update is:

declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,
N'@P1 float',
N'SELECT rgRateGroup_SysID, rgDataset_GUID, rgRateGroup_RecordStatus,
rgRateGroup_Description, rgRecAdd_Person_SysID, rgRecAdd_Date,
rgRecChg_Person_SysID, rgRecChg_Date
FROM dbo.RateGroup dbo_RateGroup
WHERE rgRateGroup_SysID = @P1',9
select @p1

My table is defined like this:
/****** Object: Table [dbo].[RateGroup] Script Date: 10/20/2011 12:24:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[RateGroup](
[rgRateGroup_SysID] [int] IDENTITY(1,1) NOT NULL,
[rgDataset_GUID] [uniqueidentifier] NOT NULL,
[rgRateGroup_RecordStatus] [char](1) NOT NULL,
[rgRateGroup_Description] [varchar](20) NOT NULL,
[rgRecAdd_Person_SysID] [int] NOT NULL,
[rgRecAdd_Date] [datetime] NULL,
[rgRecChg_Person_SysID] [int] NOT NULL,
[rgRecChg_Date] [datetime] NULL,
CONSTRAINT [PK_RateGroup] PRIMARY KEY CLUSTERED
(
[rgRateGroup_SysID] 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

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'A=Available; R=Reserved (currently not used); U=Unavailable' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RateGroup', @level2type=N'COLUMN',@level2name=N'rgRateGroup_RecordStatus'
GO

ALTER TABLE [dbo].[RateGroup] ADD CONSTRAINT [DF_RateGroup_RateGroup_RecordStatus] DEFAULT ('A') FOR [rgRateGroup_RecordStatus]
GO

ALTER TABLE [dbo].[RateGroup] ADD CONSTRAINT [DF_RateGroup_RecAddID] DEFAULT ((0)) FOR [rgRecAdd_Person_SysID]
GO

ALTER TABLE [dbo].[RateGroup] ADD CONSTRAINT [DF_RateGroup_RecChgID] DEFAULT ((0)) FOR [rgRecChg_Person_SysID]
GO

[ben3456]

Re: 'Invalid Object Name' with AlphaDAO and SQLServer 2008 when edit date col

I had just found this to be the case as well with my setup - the problem being tied to the date column. I would also appreciate any suggestions.

[RichCPT]

Re: 'Invalid Object Name' with AlphaDAO and SQLServer 2008 when edit date col

Okay, so only one other person has seen this problem? Does A5 normally have no problem working with Sql 2008 R2?

I can only think of two explanations then:
1) For some unknown reason A5 sometimes generates a completely different SELECT statement after doing an UPDATE statement and for some reason A5 does not like my table name and decides to put [] around the entire table name -- perhaps because a reserved word is embedded in my table name. "GROUP" is contained in my table name, "rgRateGroup". Or, perhaps sometimes it does not like the column name that the previous UPDATE statement affected and for 'good measure' decides it doesn't like the table name either. I've seen A5 have problems when my table names begin with the letters "SYS" -- I will try renaming my table, if that does not work I will rename the columns.

2) Perhaps there is some attribute about my sql database that A5 sometimes picks up on and then decides to put [] around table names?

P.S. I tried installing A5V11, trial edition, but it could not get past my intermediate login A5w form, so I don't know if A11 fixes the square brackets around table name problem.
-- Rich CPT

[RichCPT]

Re: 'Invalid Object Name' with AlphaDAO and SQLServer 2008 when edit date col

I cloned my SQL table "rgRateGroup" to a new name, "rgRateGrp". And setup a new component to use it. Now that the reserved word, "GROUP", is not embedded in the table name the occasional, "Invalid Object Name", sql error message seems to have gone away, yeah. I also removed the underscores in my column names, but I don't think that affected the error. I will post this as another question.
- Rich CPT

[RichCPT]

Re: 'Invalid Object Name' with AlphaDAO and SQLServer 2008 when edit date col

Sorry, I spoke too soon. The 'Invalid Object Name' error came back. I guess it did not have anything to do with the word "Group" being in the table name. It is still a mystery why Alpha sometimes uses a bad SELECT statement after doing an UPDATE.

[lvasic]

Re: 'Invalid Object Name' with AlphaDAO and SQLServer 2008 when edit date col

Did you ever find a resolution? The issues is with the brackets and the owner name. If you move the brackets or use brackets without the owner name "DBO" then it works. I'm now having this issue with the list control and detail view.