October 3, 2007 at 8:54 am
We are in the process of upgrading one of our sql server instances to 2005. We have a process that moves data from one instance to another - the source of this data will remain on sql server 2000 for the time being. The following code produces the following error:
CODE:
TRUNCATE TABLE PS_BMC_FE_ACCT_SEC
INSERT PS_BMC_FE_ACCT_SEC
(BMC_FE_TBL_ID,
BMC_FE_NAME,
BMC_FE_MASK_START,
BMC_FE_MASK_END)
SELECT
ISNULL(B.GL7ACCOUNTSECURITYID, ' '),
A.NAME,
ISNULL(B.STARTACCOUNTMASK, ' '),
ISNULL(B.ENDACCOUNTMASK, ' ')
FROM REMUS.FE72PRD.dbo.USERS A,
REMUS.FE72PRD.dbo.GL7ACCOUNTSECURITY B
WHERE A.ACCOUNTSECURITY = 0
AND A.NAME NOT IN ('report1','report2')
AND B.USERSID =* A.USERSID
AND EXISTS (SELECT 'X'
FROM REMUS.FE72PRD.dbo.SECURITYUSERS7 C
WHERE C.USERSID = A.USERSID
AND (C.SECURITYGROUPS7ID = 5
OR C.SECURITYGROUPS7ID = 6
OR C.SECURITYGROUPS7ID = 8
OR C.SECURITYGROUPS7ID = 9))
ERROR:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'aptransfirstfield'.
I feel sure that there is a syntax difference in sql server 2005 but I am not sure what it is. Any ideas?
October 3, 2007 at 9:09 am
I see no reference in the insert statement to the field that is causing the error.
For this reason, I suspect there may be a trigger on the insert? is there? I'd consider setting up a trace for this insert and see which command is actually causing the error.
October 3, 2007 at 9:16 am
There is no trigger involved here.
October 3, 2007 at 9:43 am
Would you please post the DDL for the both the source and destination tables?
😎
October 3, 2007 at 10:27 am
CREATE TABLE [dbo].[PS_BMC_FE_ACCT_SEC](
[BMC_FE_TBL_ID] [decimal](10, 0) NOT NULL,
[BMC_FE_NAME] [char](20) COLLATE Latin1_General_BIN NOT NULL,
[BMC_FE_MASK_START] [char](30) COLLATE Latin1_General_BIN NOT NULL,
[BMC_FE_MASK_END] [char](30) COLLATE Latin1_General_BIN NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[SECURITYUSERS7] (
[SECURITYUSERS7ID] [int] IDENTITY (1, 1) NOT NULL ,
[SECURITYGROUPS7ID] [int] NOT NULL ,
[USERSID] [int] NOT NULL ,
[SEQUENCE] [int] NOT NULL
) ON [PRIMARY]
October 3, 2007 at 10:32 am
CREATE TABLE [dbo].[GL7ACCOUNTSECURITY] (
[GL7ACCOUNTSECURITYID] [int] IDENTITY (1, 1) NOT NULL ,
[USERSID] [int] NOT NULL ,
[GL7STARTACCTSID] [int] NULL ,
[STARTACCOUNTMASK] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ENDACCOUNTMASK] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QUERIES7ID] [int] NULL ,
[GL7GENERALINFOID] [int] NOT NULL ,
[GL7ENDACCTSID] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[USERS] (
[USERSID] [int] IDENTITY (1, 1) NOT NULL ,
[USEAUTOCOMPLETE] [smallint] NOT NULL ,
[SUPERVISOR] [smallint] NOT NULL ,
[USESHORTFUNDDESC] [smallint] NOT NULL ,
[NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PASSWORD] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DESCRIPTION] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WP_NAME] [smallint] NULL ,
[WP_PATH] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WP_DATA_PATH] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPREADSHEET_NAME] [smallint] NOT NULL ,
[SPREADSHEET_PATH] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPREADSHEET_DATA_PATH] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GRAPH_TYPE] [smallint] NOT NULL ,
[ENTERKEY] [smallint] NOT NULL ,
[CONFIRMDUPS] [smallint] NOT NULL ,
[SHOWCREDITS] [smallint] NOT NULL ,
[SHOWCURRENCY] [smallint] NOT NULL ,
[SHOWCOMMAS] [smallint] NOT NULL ,
[SHOWDECIMALS] [smallint] NOT NULL ,
[SHOWPERCENT] [smallint] NOT NULL ,
[POSITIVEBALANCE] [smallint] NULL ,
[SMARTPROMPT] [smallint] NOT NULL ,
[ACCTPROMPTCHAR] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACCOUNTSECURITY] [smallint] NULL ,
[CHANGEPASSWORD] [smallint] NOT NULL ,
[inactive] [smallint] NOT NULL ,
[PHONEFORMAT] [smallint] NOT NULL ,
[WEBBROWSER] [smallint] NOT NULL ,
[WEBBROWSERPATH] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXCELPRESENT] [smallint] NOT NULL ,
[OUTPUTFILEPATH] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REQUIREDDIFFCOLOR] [smallint] NOT NULL ,
[REQUIREDCOLOR] [int] NULL ,
[AWARDSITEID] [int] NULL ,
[WORDPRESENT] [smallint] NOT NULL ,
[REQUIREDDISPLAYAS] [smallint] NULL ,
[AWARDBUDGETACCOUNTS] [smallint] NULL ,
[BANKACCOUNTS] [smallint] NULL ,
[HASSUPERVISORRIGHTS] [smallint] NOT NULL ,
[DEFAULTCHECKFORMAT] [smallint] NULL ,
[APTRANSFIRSTFIELD] [smallint] NOT NULL ,
[ARTRANSFIRSTFIELD] [smallint] NOT NULL ,
[PROJECTSECURITY] [int] NULL ,
[RELEASENUMBER] [numeric](3, 2) NULL ,
[OUTLOOK_NAME] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CANCHANGEOPTIONS] [smallint] NOT NULL ,
[CURRENTTIP] [int] NULL ,
[SHOWPAGEGRAPHICS] [int] NOT NULL ,
[SHOWSAVEMESSAGE] [int] NOT NULL ,
[SHOWTOOLTIPS] [int] NOT NULL ,
[SHOWSHELLICONS] [int] NOT NULL ,
[SHOWTIPOFDAY] [int] NOT NULL ,
[SPREADLOCKCOLOR] [int] NULL ,
[UIREQFIELDCOLOR] [int] NULL ,
[UIFLYCOLOR] [int] NULL ,
[USECODESLONGDESCRIPTION] [smallint] NOT NULL ,
[WELCOMESCREENBITMASK] [int] NULL ,
[GLACCOUNTMASKOPTION] [int] NOT NULL ,
[GLACCOUNTMASK] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SHELLSTARTPAGE] [int] NULL ,
[SYSTEMUSERFLAG] [int] NOT NULL ,
[VIEWONLY] [smallint] NOT NULL ,
[UITYPE] [smallint] NOT NULL ,
[ONLINEPRODUCTS] [int] NULL ,
[EMPLOYEESECURITY] [int] NULL ,
[HASFAWEBSUPERVISORRIGHTS] [boolean] NOT NULL ,
[LOGON] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USEEEFE7AUTHENTICATION] [smallint] NOT NULL ,
[USEWINDOWSAUTHENTICATION] [smallint] NOT NULL
) ON [PRIMARY]
October 3, 2007 at 10:38 am
What platform is the database Remus located?
October 3, 2007 at 10:41 am
Remus is a sql server 2000 instance.
October 3, 2007 at 10:44 am
the use of the =* operator and the boolean datatype are throwing me here. Neither are supported in SQL Server.
=* is non-ansi.
Do you have database compatibility level set? if so, what is the setting?
October 3, 2007 at 10:56 am
The compatibility level is set to 8 (2000) for both servers.
October 3, 2007 at 11:02 am
there must be something we don't know about here. I set my compat level to 8, recreated the tables and ran the code with no issue.
I strongly suggest a trace on your end.
October 3, 2007 at 11:33 am
I just noticed that if I do a straight select on the users table i get the same error message.
October 3, 2007 at 11:40 am
How about: script the existing user table and recreate using a different table name. what happens when you use the new table?
are there any "check constraints"?
when you script the table, make sure to script everything, permissions, keys and restraints.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply