0

So we have this software that queries for content of Catalogue:

10:55:36,193 [ INFO] [rest.CatalogProvider:232] GET_CATALOG: {
  Id: '*',
  Title: '*',
  Clip: '*',
  IniDate: '*',
  EndDate: '*',
  DurMin: '*',
  DurMax: '*',
  Store: '*',
  FilterConfig: '*',
  FilterNoStore: '*',
  InfoStore: '*',
  Channel: '*',
  Group: '*',
  Type: '*',
  Validated: '*',
  SourceType: '*',
  NumResults: '*',
  StartIndex: '*',
  Order: '*'
}

This terminates in error:

10:55:36,285 [ WARN] [util.JDBCExceptionReporter:233] SQL Error: 207, SQLState: S0001
10:55:36,285 [ERROR] [util.JDBCExceptionReporter:234] Invalid column name 'Locked'.
10:55:36,286 [ERROR] [service.CatalogService:434] getCatalog Exception could not execute query

However I am quite sure we do not have a column name Locked. Where is this 'locked' coming from?

The server is SQL Server 2008.

6
  • Can you find the SQL query that is produced and gives you this error? Nov 11, 2017 at 11:09
  • 1
    The name locked is either referenced in the query being sent to SQL Server or referenced in a programmability object (e.g. trigger) being executed. Start with a trace of batch and rpc starting events to rule out the first case. The name suggests it may be used for concurrency - perhaps there's an ORM lay or similar injecting it.
    – Dan Guzman
    Nov 11, 2017 at 11:09
  • @ypercubeᵀᴹ: this is not possible or I dont know how to do it. It is a java app that is already running, I dont have access to the source code
    – Okrx
    Nov 11, 2017 at 11:28
  • @DanGuzman Hmm.. I am not a developero or DB admin, you sound like you are one. I dont know what you mean by saying "trace of batch and rpc..:". Could you please explain? I appreciate all your help.
    – Okrx
    Nov 11, 2017 at 11:29
  • 2
    You can follow Dan's advice, that will help you find more details about the issue. But I don't see how it will help solving the problem. This should be a support ticket to the company/developer which provided the software. With the debug info, they should identify the cause quickly. And since you don't have access to source code, they are the only ones to fix this (unless you consider desperate measures like reverse-engineering the application). Nov 11, 2017 at 11:47

1 Answer 1

3

The name locked is either referenced in the query being sent to SQL Server or referenced in a programmability object (e.g. trigger) being executed. I suggest you start with a trace of batch and rpc starting events to rule out the first case. The name lockedsuggests it may be used for application level concurrency so perhaps there's an ORM lay or similar injecting it.

The trace can be done using the legacy Profiler/SQL Trace or an Extended Events Trace. Below is an example XE trace that filters by application name to reduce the noise or you could run an unfiltered one on a test box.

CREATE EVENT SESSION [batch_and_rpc_completed] ON SERVER 
ADD EVENT sqlserver.rpc_starting(
    ACTION(package0.event_sequence,sqlserver.session_id)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],N'YourApplication'))),
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(package0.event_sequence,sqlserver.session_id)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],N'YourApplication')))
ADD TARGET package0.event_file(SET filename=N'batch_and_rpc_completed')
WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF);

EDIT:

Here's another trace that captures only error_reported events. This is a SQL 2014+ version gleaned from the Brent Ozar's SQL 2012 version of this trace @sp_BlitzErik referenced in his comment.

CREATE EVENT SESSION failed_queries ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.database_id, sqlserver.username)
    WHERE ([severity]> 10))
ADD TARGET package0.asynchronous_file_target(SET filename = 'failed_queries' ,max_file_size = 5,max_rollover_files = 5)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);
2
  • 1
    There's a pretty good XE session definition for failing queries over here. Nov 11, 2017 at 13:27
  • @sp_BlitzErik, thanks. I added that to my answer.
    – Dan Guzman
    Nov 11, 2017 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.