Solved

invalid column name in SQL 2008

Posted on 2014-01-08
8
1,333 Views
Last Modified: 2014-01-27
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)
0
Comment
Question by:quaybj
8 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39766924
An excellent idea would be to post the T-SQL that this calls.
0
 

Author Comment

by:quaybj
ID: 39767815
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
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 39772317
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.
0
 

Author Comment

by:quaybj
ID: 39772630
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
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 40

Expert Comment

by:Sharath
ID: 39774319
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.
0
 

Author Comment

by:quaybj
ID: 39776302
HI Sharath,
no error was thrown after your suggested query.
q
0
 

Accepted Solution

by:
quaybj earned 0 total points
ID: 39802482
This turned out to be caused by a version mismatch between financial edge and webpurchashing
0
 

Author Closing Comment

by:quaybj
ID: 39811536
I figured it out myself.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now