?
Solved

invalid column name in SQL 2008

Posted on 2014-01-08
8
Medium Priority
?
1,515 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 66

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 25

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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
 
LVL 41

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

801 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