[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Debugging complex SQL query

Posted on 2011-04-20
13
Medium Priority
?
279 Views
Last Modified: 2012-05-11
I have a fairly complex INSERT INTO query in SQL Server 2008. The query fails on violation of a foreign key constraint. However, I have no insight into what exactly is happening.

I'm using Management Studio Express. Is there some way to debug this? Query Analyzer seems a likely tool but apparently is not included with the Express version.
0
Comment
Question by:BlearyEye
12 Comments
 
LVL 6

Expert Comment

by:LCSandman8301
ID: 35435138
you can get the management studio download from here:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=08e52ac2-1d62-45f6-9a4a-4b76a8564a2b

it looks a little different from Query analyzer in Sql 2000 but it is rather simple after you get used to it.
0
 

Expert Comment

by:DataField
ID: 35435417
Also, it sounds like you the table you are inserting into has a foriegn key constraint setup on it.
(Go to managment studio -> expand the table that you are trying to insert into under Constraints)
Make sure the value you are inserting is also included in the table that you have a contraint setup on
0
 
LVL 1

Author Comment

by:BlearyEye
ID: 35436010
LCSandman8301: I have Management Studio already; see below.


DataField:I know what the problem is--trying to insert a value in one table whose constraint is that it must exist in another table--but I don't know what the actual value is. I need some way to step thru the execution of the query and check the values being used; just like I would in a language debugger.

Microsoft SQL Server Management Studio						10.0.1600.22 ((SQL_PreRelease).080709-1414 )
Microsoft Data Access Components (MDAC)						6.0.6002.18005 (lh_sp2rtm.090410-1830)
Microsoft MSXML						3.0 4.0 5.0 6.0 
Microsoft Internet Explorer						8.0.6001.18999
Microsoft .NET Framework						2.0.50727.4206
Operating System						6.0.6002

Open in new window

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 35437953
>>I need some way to step thru the execution of the query and check the values being used; just like I would in a language debugger.<<
I am afraid that is not how SQL Server operates.  What you need to do is insert the results into a table that you can then compare to the intended target.

0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35438442
Altova  and other 3party tool has debugger.
0
 
LVL 1

Author Comment

by:BlearyEye
ID: 35701182
So, other than buying a 3rd party debugger, the answer is "no, it can't be done". Right?
0
 
LVL 1

Author Comment

by:BlearyEye
ID: 36121225
I've requested that this question be deleted for the following reason:

haven't received useful feedback &nbsp;...
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 2000 total points
ID: 36119216
>> the answer is "no, it can't be done". Right? <<
I am glad you finally got the picture.  See here from the EE Guidelines:
http://www.experts-exchange.com/help.jsp#hs=29&hi=405
The correct answer to some questions is "You can't do that."
Sometimes, you will get an answer that isn't what you want to read, but it still may be the correct answer, and you should award points to the Expert that gave you that answer.


0
 
LVL 1

Author Comment

by:BlearyEye
ID: 36121227
will reconsider cancellation ...
0
 
LVL 1

Author Comment

by:BlearyEye
ID: 36121231
acperkins: this would have been simpler (and more pleasant) if you'd just answered my last post in May.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36121375
>>this would have been simpler (and more pleasant) if you'd just answered my last post in May. <<
What difference would it have made?  I had already explained that "is not how SQL Server operates" and you agreed with me.  What else could have been said?
0
 
LVL 1

Author Comment

by:BlearyEye
ID: 36122485
I asked a question to make sure I understood the situation. You could have just said, "Yes, that's right".

Anyway, since we do seem to have an answer, I'll go ahead and assign points.

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

831 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