invalid column name in SQL 2008

When attemptng to create a requisition in WebPurchasing (Blackbaud),  SQL 2008 throws this error: Invalid column name "LINEITEMTYPE'  The name does exist.  When one attempts to look into the shopping cart, one is logged out immediately with the same error.  The full error is below.  I don't know where to start to fix this.  WEbPurchasing is a Blackbaud product, based on Financial Edge.  We are running the latest versions.  The logon permissions, Financial Edge groups  or location make no difference.

1/8/2014 12:20:09 PMLog            Windows NT (Application)

Source            ASP.NET 2.0.50727.0
Category            Web Event
Event            2147484958
Computer            YAFEU.xxxxxx.org

Message
Event code: 100001

Event message: Invalid column name 'LINEITEMTYPE'.

Event time: 1/8/2014 12:20:09 PM

Event time (UTC): 1/8/2014 5:20:09 PM

Event ID: 55c00b86d94d45e28377da89690ba622

Event sequence: 3

Event occurrence: 2

Event detail code: 0

Application information:

    Application domain: /LM/W3SVC/1/ROOT/WebServ-2-130336751897550331

    Trust level: Full

    Application Virtual Path: /WebServ

    Application Path: C:\Program Files (x86)\Blackbaud\The Financial Edge WebPortal\WebService\

    Machine name: YAFEU

Process information:

    Process ID: 708

    Process name: w3wp.exe

    Account name: xxxxxxxx\administrator

Exception information:

    Exception type: SqlException

    Exception message: Invalid column name 'LINEITEMTYPE'.
Request information:

    Request URL:

    Request path:

    User host address:

    User:

    Is authenticated: False

    Authentication Type:

    Thread account name: xxxxxxxx\administrator

Thread information:

    Thread ID: 9

    Thread account name: xxxxxxxx\administrator

    Is impersonating: False

    Stack trace:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
   at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at Blackbaud.FEWebPortal.WebService.ShoppingInformationService.GetShoppingCartData(Int32 userProfileId, FEDataContext dataContext)
   at Blackbaud.FEWebPortal.WebService.ShoppingInformationService.GetShoppingCart(String sessionContext)
   at SyncInvokeGetShoppingCart(Object , Object[] , Object[] )
   at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
   at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage3(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage2(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage1(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)

Custom event details:

Exception : Invalid column name 'LINEITEMTYPE'.
Call Stack :    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
   at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at Blackbaud.FEWebPortal.WebService.ShoppingInformationService.GetShoppingCartData(Int32 userProfileId, FEDataContext dataContext)
   at Blackbaud.FEWebPortal.WebService.ShoppingInformationService.GetShoppingCart(String sessionContext)
   at SyncInvokeGetShoppingCart(Object , Object[] , Object[] )
   at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
   at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage3(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage2(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage1(MessageRpc& rpc)

   at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)
qAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
An excellent idea would be to post the T-SQL that this calls.
qAuthor Commented:
It's not my code , it's out of the Blackbaud box.  I do have access to the Studio Management Studio, but i would only be guessing at to what is calling what.  I am not  a SQL person.  And I can't really see myself changing the code.  I was wondering if a data type mismatch could be causing this.  Solving this may not work in this forum, unfortunately.


This is the .sql for a trigger named  TR_AP7LINEITEMS_INS_1.

USE [FE_Production]
GO
/****** Object:  Trigger [dbo].[TR_AP7LINEITEMS_Ins_1]    Script Date: 01/09/2014 07:40:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[TR_AP7LINEITEMS_Ins_1] on [dbo].[AP7LINEITEMS] for INSERT
as
set nocount on
declare @GBLSERIALNUM varchar(50)

select @GBLSERIALNUM = dbo.GetGlobal('GBLSERIALNUM')

update AP7LINEITEMS
set ImportId = @GBLSERIALNUM+'-'+ cast( t2.AP7LINEITEMSID as varchar)
from AP7LINEITEMS t1 INNER JOIN INSERTED t2
on t1.AP7LINEITEMSID = t2.AP7LINEITEMSID
where t2.IMPORTID is null
or dataLength(RTrim(t2.IMPORTID)) = 0
DBAduck - Ben MillerPrincipal ConsultantCommented:
It looks like the LINEITEMTYPE column may be reference in the function dbo.GetGlobal.

You can see if the column exists and where by using sys.columns

SELECT *
FROM sys.columns
WHERE name = 'LINEITEMTYPE'

Check and see which tables contain this column and see if it is referenced in the code.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

qAuthor Commented:
Thanks dbaduck..  

Found only one table AP7LINEITEMS with this column when i used your query;  found Red Gate SQL Search which gave the same results and one f(x) scalar function: AP7GetPOBalance - posted below.  I like this exploring (new stuff!), what's next?  I had not gotten to the Purchase order part yet, was just adding items to the requisition.  That comes before the PO can be created, and can actaully only be done in FE, not in this web interface.


create function AP7GetPOBalance(@ap7PurchaseOrdersId integer)
returns MONEY
-- Calculates Outstanding Balance of a Purchase Order
begin
  declare @POTotalRegularLineItems MONEY
  declare @POMiscLineItemAmount MONEY
  --1 - Regular Line Items
  select @POTotalRegularLineItems = coalesce(SUM((QUANTITYORDERED-(QUANTITYRECEIVED+QUANTITYCANCELLED))*UNITCOST),0) from AP7LINEITEMS where LINEITEMTYPE = 1 and DELETED = 0 and AP7PURCHASEORDERSID = @ap7PurchaseOrdersId
  --2 - Misc Line Items
  select @POMiscLineItemAmount = coalesce(SUM(EXTENDEDCOST-(QUANTITYRECEIVED+QUANTITYCANCELLED)),0) from AP7LINEITEMS where LINEITEMTYPE = 2 and DELETED = 0 and AP7PURCHASEORDERSID = @ap7PurchaseOrdersId
  return(@POTotalRegularLineItems+@POMiscLineItemAmount)
end

screen shot of select for column lineitemtype
SharathData EngineerCommented:
Can you execute this query and check if you are getting any error.
SELECT dbo.GetGlobal('GBLSERIALNUM')

Open in new window

If it throws any error, post the definition of the function dbo.GetGlobal.
qAuthor Commented:
HI Sharath,
no error was thrown after your suggested query.
q
qAuthor Commented:
This turned out to be caused by a version mismatch between financial edge and webpurchashing

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
qAuthor Commented:
I figured it out myself.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.