Solved

Strange SQL Update Issue

Posted on 2015-02-11
13
152 Views
Last Modified: 2015-02-17
I am trying to run a simple update statement on a database.  Here is the update:

UPDATE dbo.TkprBudget
SET tkprbudget.TBBudHrsWrk = '2'
WHERE tbemp = '130' and tbprdyear = '2015' and TBPrdNbr = '1'

When I run it I get these errors:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '+'.
Msg 208, Level 16, State 0, Line 1
Invalid object name '##XML'.

I have no clue what this is talking about.  There is no + sign and I dont know what object ##XML is referring to either.  Am I missing something obvious here?  This is SQL 2012.  Thank you for your help.
0
Comment
Question by:mattpayne59
  • 4
  • 3
  • 2
  • +2
13 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40604934
What is the relevance of Access to this question?

How are you running the update query?
0
 

Author Comment

by:mattpayne59
ID: 40604976
Sorry... Not sure how access got in there as a topic.  This is straight sql.

I am just running the query in the query window in SQL management studio.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40604995
Running [ only & just the displayed query ] in the query window in SQL management studio

I have no idea, sorry.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40605101
Check if in that query window exists more code. If so, select only the statement that you want to run and press F5 or Execute button.
0
 

Author Comment

by:mattpayne59
ID: 40605389
It only has that code. I commented out the rest. I also connected using a 2008 management studio and copied that code in only to the query and I got the same issue. It is very strange.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40605396
Then check if the TkprBudget has any update trigger.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 11

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 166 total points
ID: 40605469
Get a Profiler trace with statement level details, and investigate if any cascading triggers and/or stored procedures are being executed as a result of this UPDATE statement. Chances are that some of that logic is using a temporary table with the name - ##XML and has overloads on the + sign (or is attempting to concatenate 2 strings) - which is where the failure is.
0
 

Author Comment

by:mattpayne59
ID: 40605627
I see the statement being executed in Profiler but I dont see anything else executing around it.  No stored procedures or triggers.
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 167 total points
ID: 40605652
Isn't that easy to see it in the Profiler. Use SSMS and check for the existence of triggers in the table.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 167 total points
ID: 40605884
Run this command from within the db:

EXEC sp_helptrigger 'dbo.TkprBudget'
0
 

Author Comment

by:mattpayne59
ID: 40612674
Definitely some triggers... I dont think this is going to be possible with a simple update query then.  I will go another route.  Thank you for the help everyone.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40614443
You can always code a "bypass" in the trigger(s), to have it(them) exit without doing anything when you explicitly tell the trigger not to.  CONTEXT_INFO is the easiest way to do that.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Mirror and Replication 5 26
Distinct values from two tables 14 20
SQL Script to find duplicates 16 20
Join vs where 2 0
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

747 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

11 Experts available now in Live!

Get 1:1 Help Now