1

I have had an odd error I cannot explain. Basically, I am running a query to my SQL database using excel and am having non-existent data pop up when it comes to a very particular order in my database.

Here is a simple query surrounding this order:

select * from OR200100 where OR200100.OR20001='0000793605'

Here is the output in EXCEL enter image description here

And here is the same output in SQL enter image description here

what is happening here? How could the same query generate 2 different results?

Improve this question
13
  • Holy crap, those look like Scala tables. Are you sure the queries are the same? Is there really only one record in the OR200100 table for that order number? Commented Apr 26, 2013 at 14:24
  • 1
    Your SQL result is missing the row with a value of 4 for OR20002 - can you check if it's supposed to be there? Commented Apr 26, 2013 at 14:24
  • 2
    This is a long shot, but did you SET ROWCOUNT 1 in your SSMS window before running the query? Commented Apr 26, 2013 at 15:35
  • 1
    @Hexxxer Please execute the following query from both Excel and SSMS and show us the results: select COUNT(*) from OR200100 where OR200100.OR20001='0000793605' Commented Apr 27, 2013 at 14:10
  • 1
    @Hexxxer Yes, but in theory it's still possible that you SET ROWCOUNT 1 previously and forgot to reset it, or set it in the Query Options in SSMS. It would explain why SQL Server only returns one row instead of two. But please execute the COUNT(*) query suggested by @RBarryYoung. Commented Apr 29, 2013 at 17:13

3 Answers 3

Reset to default
1

Run SQL Server Profiler against the database if you can, then compare the output to the sql query that you are running in ssms.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I can't confirm this until next week, looks like I am locked out of this instances admin rights.
1

OK, so it's SQL Server then, that's important because different SQL products can have very different idiosyncrasies and controls.

The next things to check are these:

  1. Is OR200100 a Table or a View? If it's a view then post it's code.

  2. Are you using the same Login/account from both Excel and SSMS?

  3. Are you sure that you are connecting to the same Server and Database? SSMS tells you what you are connected to, but client apps like Excel do not and it is very common for this type of problem to be caused by the app connecting to a Dev or QA version of the database. See here for some of the different ways that this can happen:

Improve this answer

3 Comments

OR200100 is a table, they are both the same login and there is only one server and one database to connect to. I am 200% positive
OK, I would lean towards #3 then.
best I can tell it is none of these.
0

So I had a very similar problem, my query was grouping by week numbers. What I found was that one of the queries had set datefirst 5 set whilst the other didn't. I guess the key thing here is make sure, if you are using any SET operations in your ssms queries, these are identical to those in the Excel query string.

Improve this answer

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.