Solved

invalid column name in SQL 2008

Posted on 2014-01-08
8
1,314 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
Comment Utility
An excellent idea would be to post the T-SQL that this calls.
0
 

Author Comment

by:quaybj
Comment Utility
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
Comment Utility
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
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
HI Sharath,
no error was thrown after your suggested query.
q
0
 

Accepted Solution

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

Author Closing Comment

by:quaybj
Comment Utility
I figured it out myself.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

772 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

12 Experts available now in Live!

Get 1:1 Help Now